DD Class9
Contents
Primary Keys
The candidate key selected as being most important for identifying a body of information (an entity, object or record).
Normalization
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
ERD
http://en.wikipedia.org/wiki/Entity-relationship_model
tools
- http://staruml.sourceforge.net/en/ free and open source
- http://www.visual-paradigm.com/product/vpuml/ proprietary free community edition
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.
Review First Normal Form
Let build the example for the homework.
Build Interface to GameDB
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/ |
Tables Games, Developers, Websites, Platforms?
Build Views
Nesting Data Bound Controls
nesting controls
Datasource Controls can probably handle up to 70% of your data binding needs but they do have drawbacks. Consider the cheese database which uses an SQL view to join Cheese with Region, Consistency, and MilkType. Of course SQL view don't support updating and can lead to some bad data binding. we can fix a few of these problem with stored procedures.
Simple filter Demo in class
GridView Details View Master Child Relationship
http://iam.colum.edu/dd/classsource/data/CheeseDataBindingFull.aspx
source
Stored Procedures
http://iam.colum.edu/dd/classsource/data/sproc/sprocADO.aspx
Cheese browser assignment
Make a creative cheese browser from that tables in you db. Use a view to join that data from multiple tables..
Here's and example of a simple cheese shop browser app.
http://iam.colum.edu/dd/classsource/class8/CheeseShop/CheeseShop1.aspx
Homework
Make a creative cheese browser from that tables in you db. Use a view to join that data from multiple tables..
Examples
http://iam.colum.edu/dd/classsource/Data/GridAndDetails.aspx
http://iam.colum.edu/dd/classsource/Data/GridDataBound.aspx
http://iam.colum.edu/dd/classsource/Data/GridDetails.aspx
http://iam.colum.edu/dd/classsource/Data/GridEdit.aspx
http://iam.colum.edu/dd/classsource/Data/GridSelect.aspx
http://iam.colum.edu/dd/classsource/Data/GridSelectTheme.aspx