Difference between revisions of "DD Class6"
(→Home Work) |
|||
Line 319: | Line 319: | ||
Normalize these tables and build in you SQL Database | Normalize these tables and build in you SQL Database | ||
+ | '''Games''' | ||
{| class="wikitable" cellpadding="5" cellspacing="0" | {| class="wikitable" cellpadding="5" cellspacing="0" | ||
!GameTitle !! GameGenre !! DeveloperName !! Platform(s) !! Year !! DeveloperWebsite !! GameWebsite | !GameTitle !! GameGenre !! DeveloperName !! Platform(s) !! Year !! DeveloperWebsite !! GameWebsite | ||
Line 327: | Line 328: | ||
|- | |- | ||
| SimCity || Sim || Interplay || Dos || 1993 || http://www.interplay.com/ || http://www.maxis.com/ | | SimCity || Sim || Interplay || Dos || 1993 || http://www.interplay.com/ || http://www.maxis.com/ | ||
+ | |} | ||
+ | |||
+ | '''Cheeses Registration''' | ||
+ | {| class="wikitable" cellpadding="5" cellspacing="0" | ||
+ | !firstName !! lastName !! companyName !! jobTitle !! address !! city !! state !! zip !! country !! emailAddress !! phoneNumber !! FavoriteCheeseRegion !! LikesSoftCheese !! LikesSemiSoftCheese !! LikesSemiHardCheese !! LikesHardCheese | ||
|} | |} |
Revision as of 17:23, 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 Repeating Groups
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 |
1NF also
- Removes multiple column with the same type of data
Books Not Normal
Author | Title_01 | Pages_01 | Title_02 | Pages_02 | Title_03 | Pages_03 |
---|---|---|---|---|---|---|
Michael Allen Dymmoch | The Man Who Understood Cats | 256 | White Tiger | 320 | ||
Joseph Cancellaro | Exploring Sound Design for Interactive Media | 272 |
http://en.wikipedia.org/wiki/First_normal_form
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.
Home Work
Normalize these tables and build in you SQL Database
Games
GameTitle | GameGenre | DeveloperName | Platform(s) | Year | DeveloperWebsite | GameWebsite |
---|---|---|---|---|---|---|
Quake1 | FPS | id | Dos | 1996 | http://www.idsoftware.com/ | http://www.idsoftware.com/games/quake/quake/ |
Diablo | RPG | Blizzard | Windows 95 | 1996 | http://www.blizzard.com/ | http://www.blizzard.com/diablo/ |
SimCity | Sim | Interplay | Dos | 1993 | http://www.interplay.com/ | http://www.maxis.com/ |
Cheeses Registration
firstName | lastName | companyName | jobTitle | address | city | state | zip | country | emailAddress | phoneNumber | FavoriteCheeseRegion | LikesSoftCheese | LikesSemiSoftCheese | LikesSemiHardCheese | LikesHardCheese |
---|