Difference between revisions of "DD Class6"
(→Home Work) |
(→Home Work) |
||
Line 229: | Line 229: | ||
|| Crumbly || Greek || Mix of Milks | || Crumbly || Greek || Mix of Milks | ||
|- | |- | ||
− | | Cheddar The most widely purchased and eaten cheese in the world. Cheddar cheeses were originally made in England, however today they are manufactured in many countries all over the world. || Semi-Hard ||England || Cows Milk} | + | | Cheddar || The most widely purchased and eaten cheese in the world. Cheddar cheeses were originally made in England, however today they are manufactured in many countries all over the world. || Semi-Hard ||England || Cows Milk} |
Build a blog interface to the blog tables we built in class. | Build a blog interface to the blog tables we built in class. |
Revision as of 21:51, 11 October 2008
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
Books
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 |
In Class Build Blogs Table and Normalize Books Table
http://en.wikipedia.org/wiki/First_normal_form
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. Make a UML Drawing for both tables.
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
CheeseName | CheeseDescription | RegionName | Consistency | MilkType |
---|---|---|---|---|
Argentinian Reggianito | The vast grazing pastures of Argentina revealed themselves to be ideal for immigrant Italians.. | Hard | Argentina | Goats Milk |
Feta | Feta is made in a traditional manner by a small family dairy in central Greece... | Crumbly | Greek | Mix of Milks |
Cheddar | The most widely purchased and eaten cheese in the world. Cheddar cheeses were originally made in England, however today they are manufactured in many countries all over the world. | Semi-Hard | England | Cows Milk}
Build a blog interface to the blog tables we built in class. The blog interface requires
READ BDD Chapter 4 again Chapter 5 READ BAD Chapter 8 |