August 1999
DB212 : DATABASE MANAGEMENT SYSTEMS

QUESTION 4

Total Marks: 20 Marks

 Click here to access other questions

SUGGESTED SOLUTIONS
Solutions and allocated marks are indicated in green.
Return to Question 4
A company keeps a database to record information about their employees : as well as personal details, they record information about any training courses attended and about which particular posts the employee has held within the company. A sample record from the database is given below.

 
Employee Record
Employee Name : Michael Chang             Employee Number : 1234
Employee Address : 20 Pierce Road      Date of Birth : 05/12/68
Courses Attended
Course Number Description Location Date Attended Pass Level
B12
B15
C12		 Business Acct
Info Tech
Commerce		 St Ann
St Jones
Seven Oaks		 11/10/94
12/11/93
19/11/93		 B
C
B
Post Information
Job Title Dept Code Dept Name Date Appointed Salary
Accounts Exec
Business Analyst		 A312
IT05		 Accounts
IT Services		 01/09/90
01/02/94		 1500.00
2500.00

 
(a) Normalize the employee record, showing clearly the UNF, 1NF, 2NF and 3NF. State any assumptions that you make. [12]
UNF

Employee (Employee#, Employee-name, Employee-address, Date-of-birth, {Course#, Course-description, Location, Date-attended, Pass-level}, {Job-title, Department-code, Department-name, Date-appointed, Salary} )

1NF

Employee(Employee#, Employee-name, Employee-address, Date-of-Birth)

Emp-Course (Employee#, Course#, Course-description, Location, Date-attended, Pass-level)

Emp-post (Employee#, Job-title, Department-code, Department-name, Date-appointed, Salary)

2NF

Employee (Employee#, Employee-name, Employee-address, Date-of-Birth)

Emp-course (Employee#, Course#, Location, Date-attended, Pass-level)

Course (Course#, Course-description)

Emp-post (Employee#, Job-title, Department-code, Department-name, Date-appointed, Salary)

3NF

Employee (Employee#, Employee-name, Employee-address, Date-of-Birth)

Emp-course (Employee#, Course#, Location, Date-attended, Pass-level)

Course (Course#, Course-description)

Emp-post (Employee#, Job-title, Department-code,Date-appointed, Salary)

Department (Department-code, Department-name)

 
(b) What is the objectives of Denormalisation? Why should it be carried out with great care? [2]
The objectives of denormalization is to reduce the number of physical databases tables to be accessed, by reducing the number of joins needed to derive the answer to a query.

However, denormalized rows are larger, which means that fewer rows can fit into a given secondary memory block and this may cause slower processing time.

 
(c) Describe three situations where denormalisation should be used. [6]
Three situations in which denormalization should be used are:
  • Two entities with one-to-one relationship. Even if one of the entities is an optional participant, if the matching entity exists most of the time, then it may be wise to combine two entities into one table.
  • A many-to-many relationship with nonkey attributes. Instead of having three tables joined together to extract data from two entities in the relationship, it may be advisable to combine columns from one of the entities into table representing the many-to-many relationship. This avoids one join in many queries.
  • Reference data. Suppose that in a one-to-many relationship, the reference data exists in an entity on the one-side of the relationship. We should then consider merging the two entities in this situation especially when there are very few instances of the entity on the many-side for each instance on the one-side.