Question 1:A college keeps details about a student and the various modules the student studied. These details comprise
regno - registration number (Unique)
name - student name
addr - student address
Tno – Teacher ID
Tname - Teacher name
DC - diploma code
DN - diploma name
MC - module code
MN - module name
Grade - module exam Grade
DC -> DN
Tno -> Tname
Regno, MC -> Grade
name -> addr
mc -> mn
Reduce the relation DETAILS to BCNF
Solution：
DD(DC,DN) DC->DN
TT(Tno,Tname) Tno->Tname
RMG(regno,Mc,Grade) Regno,Mc->Grade
NA(name,addr) name->addr
MM(MC,MM) MC->MM
Question 2: Classify the following relations as UNNORMALISED, 1NF, 2NF or 3NF. If the
relation is not in 3NF, normalize the relation to 3NF.
EMPLOYEE(empno,empname,jobcode)
empno -> empname
empno -> jobcode
Solution:
3NF
Emp(empname, empno)
Job(jobcode, empno)
EMPLOYEE(empno,empname,(jobcode,years))
empno -> empname
empno,jobcode -> years
Solution:
UNNORMALISED:
EMPLOYEE(empno,empname,jobcode,jobdesc)
empno -> empname,jobcode
jobcode -> jobdesc
Solution:
2NF
Emp(empname, empno)
Job_info(jobcode, jobdesc)
Job(jobcode, empno)
EMPLOYEE(empno,empname,project,hoursworked)
empno -> empname
empno,project -> hoursworked
Solution:
1NF
Emp(empname, empno)
Info(hoursworked, project, empno)
Question 3: Identify any repeating groups and functional dependences in the PATIENT
relation. Show all the intermediate steps to derive the third normal form
for PATIENT.
PATIENT(patno,patname,gpno,gpname,appdate,consultant,conaddr,sample)
patno: Patient number
patname: Patient Name
gpno: General Practitioner Number
gpname: General Practitioner Name
appdate: Appointment Date
conaddr: Consultant Address
Solution:
1NF:
Patient (patno, patname, gpno, gpname)
Appt (patno, appdate, consultant, conaddr, sample)
2NF:
Patient (patno, patname, gpno, gpname)
Appt (patno, appdate, consultant, sample)
Consultant (consultant, conaddr)
3NF:
Patient (patno, patname, gpno)
GP (gpno, gpname)
App (patno, appdate, consultant, sample)
Consultant (consultant, conaddr)
Question 4: A software consulting firm wishes to keep the following data for an employee and costing database:
employee number
employee name
employee address
salary
current job code
job history (job promotion code + year)
office location
telephone number
project number
project name
task number
task name
project budget
task expenditure to date
department number
department name
There are none, one or more job promotion code/year entries per employee.
The office location uniquely depends on the telephone number, and there
may be more than one employee using the same telephone and more than one
telephone in the one office. Tasks are numbered uniquely only within each
project. An employee may be concurrently assigned to more than one project
and task, but belongs to one department. Reduce this data to third normal
form.
Solution:
1NF:
Employee(employee number, employee name, employee address, salary, current job code, job promotion code, year, office location, telephone number, project number, project name, task number, task name, project budget, task expenditure to date, department number, department name)
2NF:
Department(department number, department name)
Project(project number, project name, project budget)
Office(telephone number, office location)
Task(task number, task name, task expenditure to date, project number)
Employee(employee number, employee name, employee address, salary, current job code, job promotion code, year, telephone number, department number)
3NF:
Department(department number, department name)
Office(telephone number, office location)
Employee(employee number, employee name, employee address, salary, current job code, telephone number, department number)
Job_History(employee number, job promotion code, year)
Project(project number, project name, project budget)
Task(task number, task name, task expenditure to date, project number)
Employee_Task(employee number, task number)
