Difference between revisions of "DD Class6"

esse quam videri
Jump to: navigation, search
 
Line 1: Line 1:
 
[[Category:Data Design]]
 
[[Category:Data Design]]
 +
 +
 +
==Primary Keys==
 +
The candidate key selected as being most important for identifying a body of information (an entity, object or record).
 +
 +
==Normalization==
 +
Definition from foldoc
 +
 +
First Normal Form eliminates repeating groups by putting each into a separate table and connecting them with a one-to-many relationship.
 +
Not Following First Normal Form
 +
Blog
 +
 +
BlogID BlogText recCreationDate Mood
 +
1 Blog1 03/30/03 Happy
 +
2 Blog2 03/30/03 Happy
 +
3 Blog3 03/30/03 Sad
 +
4 Blog4 03/30/03 Happy
 +
5 Blog4 03/30/03 Mad
 +
 +
Tables that Follow First normal form
 +
 +
Blog
 +
 +
BlogID BlogText recCreationDate MoodID
 +
1 Blog1 03/30/03 1
 +
2 Blog2 03/30/03 1
 +
3 Blog3 03/30/03 2
 +
4 Blog4 03/30/03 1
 +
5 Blog4 03/30/03 3
 +
 +
Mood
 +
 +
MoodID MoodName 1 Happy
 +
2 Sad
 +
3 Mad
 +
4 Afraid
 +
 +
===Second Normal Form===
 +
 +
Second Normal Form eliminates functional dependencies on a partial key by putting the fields in a separate table from those that are dependent on the whole key. Must be in first normal form.
 +
Not Normal
 +
CustNum FirstName LastName Address City State ZIP
 +
1 John Doe 12 Main Street Sea Cliff NY 11579
 +
2 Alan Johnson 82 Evergreen Tr Sea Cliff NY 11579
 +
3 Beth Thompson 1912 NE 1st St Miami FL 33157
 +
4 Jacob Smith 142 Irish Way South Bend IN 46637
 +
5 Sue Ryan 412 NE 1st St Miami FL 33157
 +
Normal
 +
ZIP City State
 +
11579 Sea Cliff NY
 +
33157 Miami FL
 +
46637 South Bend IN
 +
 +
CustNum FirstName LastName Address ZIP
 +
1 John Doe 12 Main Street 11579
 +
2 Alan Johnson 82 Evergreen Tr 11579
 +
3 Beth Thompson 1912 NE 1st St 33157
 +
4 Jacob Smith 142 Irish Way 46637
 +
5 Sue Ryan 412 NE 1st St 33157
 +
 +
 +
===Third Normal Form===
 +
 +
Third Normal Form eliminates functional dependencies on non-key fields by putting them in a separate table. At this stage, all non-key fields are dependent on the key, the whole key and nothing but the key. Must be in second normal form.
 +
Not normal
 +
Company City State ZIP
 +
Acme Widgets New York NY 10169
 +
ABC Corporation Miami FL 33196
 +
XYZ, Inc. Columbia MD 21046
 +
Normal
 +
KompanyID KompanyName Zip 1 Acme Widgets 10169
 +
2 ABC Corporation 33196
 +
3 XYZ, Inc. 21046
 +
 +
Zip City 10169 New York
 +
33196 Miami
 +
21046 Columbia
 +
 +
StateID State 1 NY
 +
2 FL
 +
3 MD
 +
 +
===Fourth Normal Form===
 +
Fourth Normal Form
 +
:separates independent multi-valued facts stored in one table into separate tables.
 +
 +
===Fifth Normal Form===
 +
Fifth Normal Form
 +
:breaks out data redundancy that is not covered by any of the previous normal forms.
 +
 +
http://home.earthlink.net/~billkent/Doc/simple5.htm.
 +
 +
==Relationships==
 +
 +
one-to-many
 +
 +
Single primary key has many foreign keys. A person many have many email addresses.
 +
 +
one-to-one
 +
 +
A Single primary key may only have on foreign key. A sweater can only be one color.
 +
 +
many-to-many
 +
 +
A junction table is used to normalize data. A student may take many courses and a course may have many students.
 +
The relationship is created in the diagram too by dragging on primary key to the foreign key.
 +
Views
 +
UserTest
 +
UserID UserName LastLogon
 +
1 jmeyers 3/30/03
 +
2 bgates 4/1/03
 +
3 sjobs 4/2/03
 +
4 ltorvalds 4/3/03
 +
 +
EmailTest
 +
EmailID UserID EmailAddress Active displayEmail
 +
1 1 jeff@interactive.colum.edu 1 0
 +
2 1 only_a_test@hotmail.com 0 0
 +
3 2 bgates@microsoft.com 1 0
 +
 +
[INNER] JOIN
 +
 +
The INNER JOIN returns all rows from both tables where there is a match. If there are rows in User that do not have matches in Email, those rows will not be listed.
 +
 +
-- ANSI Style
 +
SELECT u.UserID,
 +
u.UserName,
 +
u.LastLogon,
 +
e.EmailAddress,
 +
e.active,
 +
e.displayEmail
 +
FROM UserTest u
 +
JOIN EmailTest e ON e.UserID = u.UserID
 +
 +
--Theta style
 +
SELECT u.UserID,
 +
u.UserName,
 +
u.LastLogon,
 +
e.EmailAddress,
 +
e.active,
 +
e.displayEmail
 +
FROM UserTest u,
 +
EmailTest e
 +
WHERE e.UserID = u.UserID
 +
 +
results
 +
 +
UserID      UserName            LastLogon                    EmailAddress                    active displayEmail
 +
----------- ------------------- ----------------------------- -------------------------------- ------ ------------
 +
1          jmeyers            2003-03-30 00:00:00.000      jeff@interactive.colum.edu      1      0
 +
1          jmeyers            2003-03-30 00:00:00.000      only_a_test@hotmail.com          0      0
 +
2          bgates              2003-04-01 00:00:00.000      bgates@microsoft.com            1      0
 +
 +
(3 row(s) affected)
 +
 +
LEFT [OUTER] JOIN
 +
 +
The LEFT JOIN returns all the rows from the first table (User), even if there are no matches in the second table (Email). If there are rows in User that do not have matches in Email, those rows also will be listed.
 +
 +
SELECT u.UserID,
 +
u.UserName,
 +
u.LastLogon,
 +
e.EmailAddress,
 +
e.active,
 +
e.displayEmail
 +
FROM UserTest u
 +
LEFT JOIN EmailTest e ON e.UserID = u.UserID
 +
 +
UserID      UserName    LastLogon                EmailAddress                active displayEmail
 +
----------- ------------ ------------------------- ---------------------------- ------ ------------
 +
1          jmeyers      2003-03-30 00:00:00.000  jeff@interactive.colum.edu  1      0
 +
1          jmeyers      2003-03-30 00:00:00.000  only_a_test@hotmail.com      0      0
 +
2          bgates      2003-04-01 00:00:00.000  bgates@microsoft.com        1      0
 +
3          sjobs        2003-04-02 00:00:00.000  NULL                        NULL  NULL
 +
4          ltorvalds    2003-04-03 00:00:00.000  NULL                        NULL  NULL
 +
 +
(5 row(s) affected)
 +
 +
RIGHT [OUTER] JOIN
 +
 +
The RIGHT JOIN returns all the rows from the second table (Email), even if there are no matches in the first table (User). If there had been any rows in Email that did not have matches in User, those rows also would have been listed.
 +
 +
SELECT u.UserID,
 +
u.UserName,
 +
u.LastLogon,
 +
e.EmailAddress,
 +
e.active,
 +
e.displayEmail
 +
FROM UserTest u
 +
RIGHT JOIN EmailTest e ON e.UserID = u.UserID
 +
 +
results
 +
 +
UserID      UserName            LastLogon                    EmailAddress                    active displayEmail
 +
----------- ------------------- ----------------------------- -------------------------------- ------ ------------
 +
1          jmeyers            2003-03-30 00:00:00.000      jeff@interactive.colum.edu      1      0
 +
1          jmeyers            2003-03-30 00:00:00.000      only_a_test@hotmail.com          0      0
 +
2          bgates              2003-04-01 00:00:00.000      bgates@microsoft.com            1      0
 +
 +
(3 row(s) affected)
 +
 +
Making views in enterprise manager is easy. Just add the tables you want to join and click. It does all the typing for you.

Revision as of 03:32, 26 February 2007


Primary Keys

The candidate key selected as being most important for identifying a body of information (an entity, object or record).

Normalization

Definition from foldoc

First Normal Form eliminates repeating groups by putting each into a separate table and connecting them with a one-to-many relationship. Not Following First Normal Form Blog

BlogID BlogText recCreationDate Mood 1 Blog1 03/30/03 Happy 2 Blog2 03/30/03 Happy 3 Blog3 03/30/03 Sad 4 Blog4 03/30/03 Happy 5 Blog4 03/30/03 Mad

Tables that Follow First normal form

Blog

BlogID BlogText recCreationDate MoodID 1 Blog1 03/30/03 1 2 Blog2 03/30/03 1 3 Blog3 03/30/03 2 4 Blog4 03/30/03 1 5 Blog4 03/30/03 3

Mood

MoodID MoodName 1 Happy 2 Sad 3 Mad 4 Afraid

Second Normal Form

Second Normal Form eliminates functional dependencies on a partial key by putting the fields in a separate table from those that are dependent on the whole key. Must be in first normal form. Not Normal CustNum FirstName LastName Address City State ZIP 1 John Doe 12 Main Street Sea Cliff NY 11579 2 Alan Johnson 82 Evergreen Tr Sea Cliff NY 11579 3 Beth Thompson 1912 NE 1st St Miami FL 33157 4 Jacob Smith 142 Irish Way South Bend IN 46637 5 Sue Ryan 412 NE 1st St Miami FL 33157 Normal ZIP City State 11579 Sea Cliff NY 33157 Miami FL 46637 South Bend IN

CustNum FirstName LastName Address ZIP 1 John Doe 12 Main Street 11579 2 Alan Johnson 82 Evergreen Tr 11579 3 Beth Thompson 1912 NE 1st St 33157 4 Jacob Smith 142 Irish Way 46637 5 Sue Ryan 412 NE 1st St 33157


Third Normal Form

Third Normal Form eliminates functional dependencies on non-key fields by putting them in a separate table. At this stage, all non-key fields are dependent on the key, the whole key and nothing but the key. Must be in second normal form. Not normal Company City State ZIP Acme Widgets New York NY 10169 ABC Corporation Miami FL 33196 XYZ, Inc. Columbia MD 21046 Normal KompanyID KompanyName Zip 1 Acme Widgets 10169 2 ABC Corporation 33196 3 XYZ, Inc. 21046

Zip City 10169 New York 33196 Miami 21046 Columbia

StateID State 1 NY 2 FL 3 MD

Fourth Normal Form

Fourth Normal Form

separates independent multi-valued facts stored in one table into separate tables.

Fifth Normal Form

Fifth Normal Form

breaks out data redundancy that is not covered by any of the previous normal forms.

http://home.earthlink.net/~billkent/Doc/simple5.htm.

Relationships

one-to-many

Single primary key has many foreign keys. A person many have many email addresses.

one-to-one

A Single primary key may only have on foreign key. A sweater can only be one color.

many-to-many

A junction table is used to normalize data. A student may take many courses and a course may have many students. The relationship is created in the diagram too by dragging on primary key to the foreign key. Views UserTest UserID UserName LastLogon 1 jmeyers 3/30/03 2 bgates 4/1/03 3 sjobs 4/2/03 4 ltorvalds 4/3/03

EmailTest EmailID UserID EmailAddress Active displayEmail 1 1 jeff@interactive.colum.edu 1 0 2 1 only_a_test@hotmail.com 0 0 3 2 bgates@microsoft.com 1 0

[INNER] JOIN

The INNER JOIN returns all rows from both tables where there is a match. If there are rows in User that do not have matches in Email, those rows will not be listed.

-- ANSI Style SELECT u.UserID, u.UserName, u.LastLogon, e.EmailAddress, e.active, e.displayEmail FROM UserTest u JOIN EmailTest e ON e.UserID = u.UserID

--Theta style SELECT u.UserID, u.UserName, u.LastLogon, e.EmailAddress, e.active, e.displayEmail FROM UserTest u, EmailTest e WHERE e.UserID = u.UserID

results

UserID UserName LastLogon EmailAddress active displayEmail

------------------- ----------------------------- -------------------------------- ------ ------------

1 jmeyers 2003-03-30 00:00:00.000 jeff@interactive.colum.edu 1 0 1 jmeyers 2003-03-30 00:00:00.000 only_a_test@hotmail.com 0 0 2 bgates 2003-04-01 00:00:00.000 bgates@microsoft.com 1 0

(3 row(s) affected)

LEFT [OUTER] JOIN

The LEFT JOIN returns all the rows from the first table (User), even if there are no matches in the second table (Email). If there are rows in User that do not have matches in Email, those rows also will be listed.

SELECT u.UserID, u.UserName, u.LastLogon, e.EmailAddress, e.active, e.displayEmail FROM UserTest u LEFT JOIN EmailTest e ON e.UserID = u.UserID

UserID UserName LastLogon EmailAddress active displayEmail

------------ ------------------------- ---------------------------- ------ ------------

1 jmeyers 2003-03-30 00:00:00.000 jeff@interactive.colum.edu 1 0 1 jmeyers 2003-03-30 00:00:00.000 only_a_test@hotmail.com 0 0 2 bgates 2003-04-01 00:00:00.000 bgates@microsoft.com 1 0 3 sjobs 2003-04-02 00:00:00.000 NULL NULL NULL 4 ltorvalds 2003-04-03 00:00:00.000 NULL NULL NULL

(5 row(s) affected)

RIGHT [OUTER] JOIN

The RIGHT JOIN returns all the rows from the second table (Email), even if there are no matches in the first table (User). If there had been any rows in Email that did not have matches in User, those rows also would have been listed.

SELECT u.UserID, u.UserName, u.LastLogon, e.EmailAddress, e.active, e.displayEmail FROM UserTest u RIGHT JOIN EmailTest e ON e.UserID = u.UserID

results

UserID UserName LastLogon EmailAddress active displayEmail

------------------- ----------------------------- -------------------------------- ------ ------------

1 jmeyers 2003-03-30 00:00:00.000 jeff@interactive.colum.edu 1 0 1 jmeyers 2003-03-30 00:00:00.000 only_a_test@hotmail.com 0 0 2 bgates 2003-04-01 00:00:00.000 bgates@microsoft.com 1 0

(3 row(s) affected)

Making views in enterprise manager is easy. Just add the tables you want to join and click. It does all the typing for you.

Retrieved from "https://imamp.colum.edu/mediawiki/index.php?title=DD_Class6&oldid=5363"