ITS432 EXERCISE (NORMALIZATION)
QUESTION 1
The following question will be based on Figure 1.0
AB BISTRO RESTAURANT
(001812777-AB)
Lot No.18, Jalan LKP 7,
77000 Jasin
Melaka
Tel: 017-2345678
TABLE 30
No : 101
Date : 17/01/2019 11:23:24
Server : Nurul Najwa
--------------------------------------------------------------------------------------------------------
--
MenuID Menu Description Quantit Price(RM) Amount
y
M10 Tomyam Thai 1 RM 10.00 RM 10.00
M12 Daging Masak Pedas 1 RM 7.00 RM 7.00
M13 Kangkung Goreng 1 RM 3.00 RM 3.00
M14 Telur Bistik 1 RM 4.00 RM 4.00
D4 Teh Ais 2 RM 2.50 RM 5.00
D16 Milo Tabur 2 RM 2.50 RM 5.00
TOTAL : RM 34.00
Payment : RM 50.00
Balance : RM16.00
Figure 1.0 : AB Bistro Restaurant Receipt
a) State the normal form of the above receipt. Briefly explain your answer with reasons
b) Briefly explain the purpose of normalization in database design
c) Normalize relational schema in (b) until 3NF. Show all your work for each normalization stage
QUESTION 2
The following questions are based on the table structure and data shown in Table 1.0 which was
imported from an Excel spreadsheet
StaffID DentistNam PatientID PatientNam AppointmentDat SurgeryID SurgeryLeve
e e e l
S1010 Dr. Rayyan P100 Naufal 11-April-2017 S10 Low
P105 Alif 15-April-2017 S15 Risk
P100 Naufal 02-May-2017 S13 Medium
S1024 Dr. Naqeeb P108 Irfan 08-May-2017 S10 Low
P110 Qaisara 21-June-2017 S13 Medium
S1032 Dr. Fatin P105 Alif 15-July-2017 S15 Risk
P11o Qaisara 26-July-2017 S13 Medium
a) State the Normal Form of table 1.0. Explain your answer
b) Draw the dependency diagram and identify all transitive and partial dependencies (if any)
c) Decompose the table to the Third Normal Form (3NF). Show all your decompositions
d) Draw the relationship diagram based on the normalized tables in (c)
QUESTION 3
Answer the following questings based on the following relational schema
EXAMINATION (Subject_Code, Subject_Name, ExamRoom_Num, Hour_Start, Hour_End,
Exam_Date, {Invigilator_ID, Invigilator_Name}, Observer_ID, Observer_Name,
Observer_Position)
*observer : administration staff who checks the examination process to ensure it runs smoothly
Invigilator : A lecturer who monitor the examinations from start to finish in examination room
a) In what normal form is the relational scheme in? Give your reason (2 marks)
b) Identify the PK (2 marks)
c) Normalize the table in 3NF. Show all your works for each normalization stage (6 marks)