• No results found

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

Month 16