A practical introduction using Oracle SQL Normalisation
Exercise 4
For each of the following statements decide which of the three normal forms would be applicable. 1. The non-key attributes depend on the whole (compound) key, not just part of it.
2. For each row of the table, there is a single entry in each column.
3. In a table in this form all non-key attributes are fully functionally dependent on the key.
Exercise 4 feedback
1) The non-key attributes depend on the whole
(compound) key, not just part of it
Second Normal Form 2) For each row of the table, there is a single entry in
each column
First Normal Form 3) In a table in this form all non-key attributes are fully
functionally dependent on the key
Third Normal Form
Exercise 5
A cycling organisation wishes to set up a database to record results for cycle races. The cyclists’ ID, name, address and phone no. are to be held. In addition, their cycling club ID and name and its address need to be recorded. The database will also need to hold all details of all the races that the cyclist takes part in. Each race has a unique number. Each cyclist can only belong to one club though clubs can have many cyclists.
An initial un-normalised relation is:
CYCLIST (Cyclist#, Surname, Forename, Cyclist address, Cyclist_phone#, Race#, Race name, Race type, Race result, Club#, Club name, Club address)
Convert this to 3NF using the normalisation template. A starting point is given in the table below.
Un-normalised form First normal form Second normal form Third normal form Cyclist_ID Surname Forename Cyclist_address Cyclist_phone# Race# Race_name Race_type Race result Club# Club_name Club_address
A practical introduction using Oracle SQL
83
Normalisation
Exercise 5 feedback
Un-normalised form First normal form Second normal form Third normal form
Cyclist ID Cyclist ID Cyclist ID Cyclist ID
Surname Surname Surname Surname
Forename Forename Forename Forename
Cyclist_address Cyclist_address Cyclist_address Cyclist_address
Cyclist_phone# Cyclist_phone# Cyclist_phone# Cyclist_phone#
Race# Club# Club# Club#
Race_name Club_name Club_name
Race_type Club_address Club_address Club#
Race result Club_name
Club# Cyclist ID Cyclist ID Club_address
Club_name Race# Race#
Club_address Race_name Race result Cyclist ID
Race_type Race#
Race result Race# Race result
Race_name
Race_type Race#
Race_name Race_type The repeating group race is broken out at 1NF.
The part-key dependent race details are broken out at 2NF. The non-key dependent club details are broken out at 3NF. This would give the following set of relations:
A practical introduction using Oracle SQL Normalisation
Exercise 6
Normalise the following:
1. EMPLOYEE (Employee Name, Address, Age, Department, Division)
2. An airline wishes to keep the following information about each of the flights made:
Fight Reference Number, Departure Date, Pilot No, Arrival Date, Aircraft ID, Flight Destination, Name of Pilot, Aircraft Name, Aircraft capacity, Aircraft Type, Aircraft max speed.
3. TEACHER (Teacher#, Teacher_Name, School_Ref, School_Name)
4. RACE(Race_ID, Competitor_ID, Competitor_Name, Position_Achieved, Race_Distance,)
5. CAR_RALLY_RESULT(Driver_ID, Driver_Name, Co_Driver_ID, Co_Driver_Name, Driver_Ranking, Rally_Name, {Stage_No, Stage_Time}
Note: Rally Cars compete in motor rallies. Each rally car has a driver and a co- driver. Each rally comprises a number of timed stages { }.
6. Derive a set of normalised relations (to 3NF) for the following data that is to form the basis of a database used to hold computer repair records.
Customer Number, Customer Name, Customer Address, Repair Date, PC ID, Make, Model, Technician Name, Technician Grade, Repair Cost.
Assume that each PC repair is assigned to one technician whose grade determines the rate to be charged for the work done. The database must be able to record a series of repairs carried out on the same PC over a period of time. The company identifies each PC with a single owner (the Customer).
Exercise 6 feedback
1. EMPLOYEE (Employee_Name, Address, Age, Department) DEPARTMENT (Department, Division)
2. PILOT (Pilot#, Pilot_Name)
AIRCRAFT(Aircraft_ID, Aircraft_Name, Aircraft_Type, Aircraft_Capacity, Aircraft_max _Speed) FLIGHT(Flight_Reference_No., Flight_Destination, Departure_Date, Arrival_Date, Aircraft_ID, Pilot#)
3. TEACHER(Teacher#, Teacher_Name, School_Ref )
SCHOOL(School_Ref, School_Name)
4. RACE (Race_ID, Race_Distance)
COMPETITOR (Competitor_ID,Competitor_Name)
RESULT (Race_ID, Competitor_ID, Position_Achieved) 5. DRIVER(Driver_ID, Driver_Name)
CO_DRIVER(Co_Driver_ID, Co_Driver_Name) RALLY(Rally_Name, Driver_ID, Co_Driver_ID)
CAR_RALLY_RESULT (Rally_Name, Driver_ID , Stage_No, Stage_Time) 6. PC_REPAIR (PC_ID,Repair_Date, Customer#, Technician_Name, Repair_Cost)
CUSTOMER (Customer#, Customer_Name, Customer_Address)
A practical introduction using Oracle SQL
85
Introduction to Oracle SQL
6 Introduction to Oracle SQL
On completion of this chapter you should be able to: • sign in and out of Oracle
• use a script file • create tables • insert data.
Introduction
You are now ready to implement physically a logical design, using a database management system (DBMS). There are a number of Relational Database Management Systems (RDBMS) in use; popular ones include Oracle, MySQL, Microsoft SQL Server and Microsoft Access.
In order to use a DBMS you need to be familiar with a data manipulation language. The most widely used one is the Structured Query Language, commonly referred to as SQL.
Although SQL has become a standard and is used by many DBMSs there are slight differences between them so some SQL code may not work on all systems without changes
Click on the ad to read more Click on the ad to read more Click on the ad to read more Click on the ad to read more Click on the ad to read more Click on the ad to read more Click on the ad to read more Click on the ad to read more Click on the ad to read more Click on the ad to read more Click on the ad to read more Click on the ad to read more Click on the ad to read more Click on the ad to read more Click on the ad to read more Click on the ad to read more Click on the ad to read more Click on the ad to read more Click on the ad to read more Click on the ad to read more Click on the ad to read more Click on the ad to read more Click on the ad to read more Click on the ad to read more Click on the ad to read more Click on the ad to read more
Maersk.com/Mitas
�e Graduate Programme for Engineers and Geoscientists