Normalisation Tutorial

1. A college keeps details about a student and the various modules the student studied. These details comprise
   • regno - registration number
   • n - student name
   • a - student address
   • tno - tutor number
   • tna - tutor name
   • dc - diploma code
   • dn - diploma name
   • mc - module code
   • mn - module name
   • res - module exam result
   where

   details(regno,n,a,tno,tna,dc,dn,(mc,mn,res))
     dc -> dn
     tno -> tna
     mc,mn -> res
     n -> a
     mc -> mn
   

   Reduce the relation DETAILS to third normal form.
   
   
   
2. Classify the following relations as either UNNORMALISED, 1NF, 2NF or 3NF. If the relation is not in 3NF, normalise the relation to 3NF.

   1.    EMPLOYEE(empno,empname,jobcode)
           empno -> empname
           empno -> jobcode
       

   2.    EMPLOYEE(empno,empname,(jobcode,years))
           empno -> empname
           empno,jobcode -> years
       

   3.    EMPLOYEE(empno,empname,jobcode,jobdesc)
           empno -> empname,jobcode
           jobcode -> jobdesc
       

   4.    EMPLOYEE(empno,empname,project,hoursworked)
           empno -> empname
           empno,project -> hoursworked
       

   
   
   
   

    

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	patname	gpno	gpname	appdate	consultant	conaddr	sample
   01027	Grist	919	Robinson	3/9/2004	Farnes	Acadia Rd	blood
   				20/12/2004	Farnes	Acadia Rd	none
   				10/10/2004	Edwards	Beech Ave	urine
   08023	Daniels	818	Seymour	3/9/2004	Farnes	Acadia Rd	none
   				3/9/2004	Russ	Fir St	sputum
   191146	Falken	717	Ibbotson	4/10/2004	Russ	Fir St	blood
   001239	Burgess	818	Seymour	5/6/2004	Russ	Fir St	sputum
   007249	Lynch	717	Ibbotson	9/11/2004	Edwards	Beach Ave	none

   
   
   
   
4. Reduce the following to BCNF, showing all the steps involved.

   Supplier(sno,sname,saddress,(partno, partdesc,(custid,custname,custaddr,quantity)))
     sno -> sname,saddr
     sno,partno -> partdesc
     sno,partno,custid -> quantity
     sname -> sno
     custid -> custname,custaddr
   

   Suppliers supply many parts to many customers. Each customer deals with only one supplier. Supplier names are unique. Customer names are not unique.
   
   
   
5. Normalise the following relation to 3NF showing all the steps involved.

   GP(gpno,cpname,gpadd,(patno,patname,patadd,patdob,(apptdate,apptime,diagnosis,treatment)))
     gpno -> gpname,gpadd
     patno -> patname,patadd, patdob
     patno,apptdate -> apptime,diagnosis
     diagnosis -> treatment
   

   
   
   
   

    

6. The table below shows an extract from a tour operator's data on travel agent bookings. Derive the third normal form of the data, showing all the intermediate steps.

   batchno	agentno	agent name	holiday code	cost	quantity booked	airport code	airport name
   1	76	Bairns travel	B563	363	10	1	Luton
   			B248	248	20	12	Edinburgh
   			B428	322	18	11	Glasgow
   2	142	Active Holidays	B563	363	15	1	Luton
   			C930	568	2	14	Newcastle
   			A270	972	1	14	Newcastle
   			B728	248	5	12	Edinburgh
   3	76	Bairns travel	C930	568	11	1	Luton
   			A430	279	15	11	Glasgow

   
   
   
   
7. 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 expendature to date
   • department number
   • department name
   There are none, one or mor ejob 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.