1. Introduction to SQL (Structured Query Language)

DAT 702 - Week 2

Agenda

2. A simple explanation of the first 3 rules of Normalization (we will have more detail in week 3)

3. Let’s design our first database - The Flight Reservation System.

4. A sample exercise. (Refer to the CREATE TABLE example in MySQL DAT702 week 1 slides)

1. Introduction to SQL

2. Normalization

a) The first rule of Normalization states that a table cannot have multiple occurences of the same attribute or group of attributes. We will not allow “multiple dependancies.

(ex) [ Student#, Course#, Grade ] is o.k.

[ Student#, Course1#, Grade1, Course2#, Grade2 ] NOT o.k.

======

b) The second rule of Normalization states that each attribute in a table must depend on the entire Primary Key.

(ex) [ Invoice#, Part#, PartDescription, UnitPrice, QtyPurchased ] NOT o.k.

should be:

[ Part#, PartDescription, UnitPrice ]

[ Invoice#, Part#, QtyPurchased ]

That’s because PartDescription and UnitPrice are the same no matter which Invoice they are on. They do not depend on Invoice#

c) The third rule of Normalization states that we should not tolerate a table that is “hiding” inside another table.

(ex) [ Patient#, PatientName, FamilyDoctor#, FamDocName ]

should be:

[ Patient#, PatientName, FamilyDoctor#(FK) ]

[ FamilyDoctor#, FamDocName ]

3. Now let’s use these rules to help us design our first database:

Simple Flight Reservation System:

Exercise: Identify the tables required to support Air Ontario’s Reservation System

Air Ontario operates in Ontario, New York, and, Maine. As a product of the analysis, the following data items have been identified:

Data Item Example

1. Reservation I.D. ONT-47321

2. Flight I.D. 434

3. Date of Flight 2010/5/24

4. Departure Pearson – Terminal 3 – Gate 44

5. Arrival Sudbury – Gate 5

6. Leaving Time 11:00

7. Arrival Time 12:15

8. Customer Number qb-45137

9. Customer Name Mr. Bert Stein

10. Customer Address 101 Apple Rd.,Orchard, B.C. L5T4R3

11. Seat Assignment 11C

12. Sales Person Number 1140

13. Sales Person Name Mrs. Bianca Rodriguez

Now we can draw the ERD – Entity Relationship Diagram

Air Ontario Reservation System ERD

Notice how the value of the Foreign Key on one table points to the value of the Primary Key of the second table. The RESERVATION has the FightID only, the flight details are on the FLIGHT table! The same holds true for CUSTOMER and SALESPERSON.