Difference between revisions of "DD Class6"

Revision as of 21:52, 11 October 2008

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.

Data Relationships

Not Following First Normal Form Repeating Groups

Blog1

Tables that Follow First normal form

Blog2

Mood

1NF also

Removes multiple column with the same type of data

Books Not Normal

Books

In Class Build Blogs Table and Normalize Books Table

http://en.wikipedia.org/wiki/First_normal_form

Views

UserTest

EmailTest

[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

Cheeses

Build a blog interface to the blog tables we built in class. The blog interface requires

• An insert blog page
• A blogroll disply page
• An Edit/Delete Page

READ BDD Chapter 4 again Chapter 5

READ BAD Chapter 8