DD Class10

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.

Data Relationships

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
1	1	jeff@interactive.colum.edu	1
2	1	only_a_test@hotmail.com	0
3	2	bgates@microsoft.com	1

[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

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

An insert blog page. Don't worry about user names or authentications this is a very public blog more like a message board.
A blogroll display page. Be careful which control you use to display the data.

Extra Credit is an edit or delete page.

READ BDD Chapter 4 again Chapter 5

READ BAD Chapter 8