Difference between revisions of "DD Class6"
(→Relationships) |
(→Normalization) |
||
| Line 12: | Line 12: | ||
Not Following First Normal Form | Not Following First Normal Form | ||
| + | [[Data Relationships]] | ||
Blog1 | Blog1 | ||
| Line 171: | Line 172: | ||
http://home.earthlink.net/~billkent/Doc/simple5.htm. | http://home.earthlink.net/~billkent/Doc/simple5.htm. | ||
| − | |||
| − | |||
==Views== | ==Views== | ||
Revision as of 04:10, 1 March 2007
Contents
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
Blog1
| 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
Blog2
| 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 | CityID |
|---|---|
| 10169 | 1 |
| 33196 | 2 |
| 21046 | 3 |
| CityID | City |
|---|---|
| 1 | New York |
| 2 | Miami |
| 3 | 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.
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 <sql> SELECT u.UserID, u.UserName, u.LastLogon, e.EmailAddress, e.active, e.displayEmail FROM UserTest u JOIN EmailTest e ON e.UserID = u.UserID </sql>
--Theta style <sql> SELECT u.UserID, u.UserName, u.LastLogon, e.EmailAddress, e.active, e.displayEmail FROM UserTest u, EmailTest e WHERE e.UserID = u.UserID </sql>
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.
<sql> 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 </sql>
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.
<sql> 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 </sql>
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.
