CS 580 Client-Server Programming

Spring Semester, 2005

Doc 18 SQL, Joins & Normalization

Contents

Some Data Modeling    

An Example    

Primary Key    

Indices    

Adding Values    

Office_Hours adding    

Getting Office Hours    

Joins    

Inner Join (or just Join)    

Outer Join    

Normalization    

First Normal Form (1NF)    

Second Normal Form (2NF)    

Third Normal Form (3NF)    

Other Normal Forms    

 

References

 

Oracle Design, Ensor & Stevenson, O'Reilly & Associates, Inc., 1997

 

MySQL On-line Manual   http://www.mysql.com/doc/en/Reference.html

 

PostgreSQL Commands http://www.postgresql.org/idocs/index.php?sql-commands.html

 

Copyright ©, All rights reserved. 2005 SDSU & Roger Whitney, 5500 Campanile Drive, San Diego, CA 92182-7700 USA.

OpenContent ( http://www.opencontent.org/opl.shtml ) license defines the copyright on this document.

 

 

Some Data Modeling

Terms

Entity

A distinct class of things about which something is known

 

Entity Occurrence

Particular instance of an entity class

 

In a database entity occurrences are records in a table

 

Attribute

An abstraction belonging to or characteristic of an entity

 

Primary Key (unique identifier)

An attribute (or set of attributes) that uniquely define an entity

 

Relationship

An abstraction belonging to or characteristic of two entities or parts together

 

 

Relational databases do not support pointers to entities

 

Foreign Key

A unique identifier in a record representing another record

 

 

 

Entity Relationship Diagram (ERD)

 

 

Entity (car) with:

Attributes (Color, make, model, serial number)

Primary key (serial number)

 

 

Relationship between Car and Person entities

Car must have one and only one owner

Person may own zero, one or more cars

Person can own many cars

 

 

An Example

Primary Key

 

A primary key is one that uniquely identifies a row in a table

 

 

A Silly Table

 

 

 

PostgreSQL Version

CREATE TABLE faculty (

   name  CHAR(20) NOT NULL,

   faculty_id  SERIAL   PRIMARY KEY

);

 

 

MySQL Version

CREATE TABLE faculty (

   name  CHAR(20) NOT NULL,

   faculty_id  INTEGER AUTO_INCREMENT   PRIMARY KEY

);

 

 

 

 

Indices

 

Indices make accessing faster

 

Primary keys automatically have an index

 

The CREATE INDEX command creates indices

 

CREATE INDEX faculty_name_key on faculty (name);

 

 

 

 

Adding Values

 

 

INSERT INTO  faculty ( name) VALUES    ('Whitney');

INSERT INTO  faculty ( name) VALUES    ('Beck');

INSERT INTO  faculty ( name) VALUES    ('Anantha');

INSERT INTO faculty ( name) VALUES ('Vinge');

 

select * from faculty;

 

Result

         name         | faculty_id

----------------------+-------------

 Whitney              |           1

 Beck                 |           2

 Anantha              |           3

 Vinge                |           4

(4 rows)

 

 

Note PostgreSQL allows one to drop the list of column names:

 

INSERT INTO faculty  VALUES ('Vinge');

 

 

 

A Second Table

PostgreSQL

CREATE TABLE office_hours (

    start_time    TIME NOT NULL,

    end_time    TIME NOT NULL,

    day    CHAR(3) NOT  NULL,

    faculty_id INTEGER REFERENCES faculty ,

    office_hour_id    SERIAL    PRIMARY KEY

);

 

MySQL

CREATE TABLE office_hours (

    start_time    TIME NOT NULL,

    end_time    TIME NOT NULL,

    day    CHAR(3) NOT  NULL,

    faculty_id INTEGER REFERENCES faculty ,

    office_hour_id INTEGER AUTO_INCREMENT PRIMARY KEY

);

 

faculty_id is a foreign key

 

REFERENCES faculty insures that only valid references are made

 

 

 

 

 

Office_Hours adding

Simple Insert

INSERT  

    INTO office_hours ( start_time, end_time, day, faculty_id )

    VALUES ( '10:00:00',  '11:00:00' ,  'Wed',  1 );

 

The problem is that we need to know the id for the faculty

 

Using Select

 

INSERT INTO

    office_hours  (start_time, end_time, day, faculty_id )

SELECT

    '8:00:00' AS start_time,

    '12:00:00' AS end_time,

    'Mon' AS day,

    faculty_id AS faculty_id

FROM

    faculty

WHERE

    name = 'Beck';

 

 

 

 

Getting Office Hours

 

SELECT

    name, start_time, end_time, day

FROM

    office_hours, faculty

WHERE

    faculty.faculty_id = office_hours.faculty_id;

 

 

 

 

 

Some Formatting

PostgreSQL only

 

SELECT

    name AS Instructor,

    TEXT(start_time) || ' to ' || TEXT(end_time) AS Time,

    day AS Day

FROM

    office_hours, faculty

WHERE

    faculty.faculty_id = office_hours.faculty_id

ORDER BY

    Name;

 

 

 

 

 

Some Selection

 

SELECT

    name, start_time, end_time, day

FROM

    office_hours, faculty

WHERE

    faculty.faculty_id = office_hours.faculty_id

 AND

    start_time > '09:00:00'

 AND

    end_time < '16:30:00'

ORDER BY

    Name;

 

 

 

Joins

 

People

 

 

Email_Addresses

 

 

The tables have a column in common as email_addresses.person_id refers to people.id. So we can create a new table by joining the two tables together on that column

 

 

 

Inner Join (or just Join)

 

Only uses entries linked in two tables

 

 

 

select

    first_name, last_name, user_name, host  

from

    people, email_addresses

where

    people.id = email_addresses.person_id;

 

or equivalently

 

select

    first_name, last_name, user_name, host  

from

    people inner join email_addresses

on

    (people.id = email_addresses.person_id);

 

 

 

Outer Join

 

Uses all entries from a table

 

Left Outer Join

 

Use all entries from the left table

 

 

 

select

    first_name, last_name, user_name, host  

from

    people left outer join email_addresses

on

    (people.id = email_addresses.person_id);

 

 

 

Right Outer Join

 

 

 

Use all entries from the right table

 

select

    first_name, last_name, user_name, host  

from

    people right outer join email_addresses

on

    (people.id = email_addresses.person_id);

 

 

 

A right outer join B & B left outer join A

 

The following two statements are equivalent

 

select

    first_name, last_name, user_name, host  

from

    people right outer join email_addresses

on

    (people.id = email_addresses.person_id);

 

select

    first_name, last_name, user_name, host  

from

    email_addresses left outer join people

on

    (people.id = email_addresses.person_id);

 

 

 

 

Normalization

 

Defined by Dr. E. F. Codd in 1970

 

Normal forms

Reduce redundant data and inconsistencies

 

First Normal Form (1NF)

 

An entity is in the first normal form when all its attributes are single valued

 

Example - Office Hours

 

 

 

What if someone has more than 3 office hours?

Wasted space for those that have fewer office hours

 

Not is 1NF since office hours are repeated

 

 

 

In 1NF Form

 

Faculty

 

 

Office Hours

 

 

 

 

Second Normal Form (2NF)

 

An entity is in the second normal form if:

 

1. It is in 1NF and

2. All non-key attributes must be fully dependent on the entire primary key

 

Example 1- CDs

 

Put your collection of CD in a database

 

 

 

Table is not in 2NF since different CDs

 

3. Can have the same artists

4. Can have same music type

 

 

 

Example 2- Course Schedule

 

 

 

At SDSU the schedule number uniquely identifies a course in a semester

 

So the term and schedule number uniquely identifies a course at SDSU

 

We can use term and schedule as the primary key

 

The table is in 1NF but not 2NF

 

Name, Time and Days are not fully dependent on the primary key

 

 

Schedule

 

Courses

 

Time

 

 

Term

 

 

 

Comments about Previous Slide

 

The schedule table is now in 2NF

 

What about the other tables?

 

If not how would you fix them?

 

Can you find a better way to decompose the original table?

 

 

Third Normal Form (3NF)

 

An entity is in third normal form if

 

5. It is in 2NF and

6. All non-key attributes must only be dependent on the primary key

 

Customer

 

 

State abbreviation depends on State Name

 

Table is not in 3NF

 

 

Other Normal Forms

 

7. Boyce-Codd normal form (BCNF)

8. Fourth normal form (4NF)

9. Fifth normal form (5NF)

 

These are beyond the scope of this course

 

See your local database course/textbook