INFO151 Assignment 4

1
INFO151 
Assignment 4 (30%)
1. BuildingWork Pte Ltd supplies workers to building projects. A table consists of the employee and
project from BuildingWork Pte Ltd is given below. Examine the table and the conditions carefully
then answer the questions below.
Assumptions and constraints: EMP_ID is unique for each worker. PROJ_ID is unique for each
project. A project can have more than one worker. Employee first name is not unique. The hourly
rate for a particular job type is fixed. A worker can work for more than one project but has only
one job type.
PROJ_ID PROJ_NAME EMP_ID EMP_FNAME JOB_TYPE HOUR_RATE
1135 Zulu 16 Smith H25 20
1135 Zulu 33 Smith H27 20
1188 Voyager 30 Jenny H26 30
1188 Voyager 16 Smith H25 20
(a) Discuss a potential update anomaly of the above table with an example. [2 marks]
(b) Construct the 1NF dependency diagram, name all the dependencies and indicate the
primary key. [8 marks]
PROJ_ID PROJ_NAME EMP_ID EMP_FNAME JOB_TYPE HOUR_RATE
(c) Convert the 1NF table to 3NF tables. You must show the progress from 2NF to 3NF. Indicate
all the PKs and FKs in the normalised tables. [10 marks]
2
2. An administrator uses a spreadsheet to record student information is shown in Table Q2 (in page
4). The following constraints are given:
• A student can only register for a single degree programme.
• A student can only change his degree programme after a full year.
• The course title can change after a full year.
(a) Convert the spreadsheet into a table containing attributes, then identify the primary key in
the table. [4 marks]
(b) Draw all the dependencies and identify the type of dependency for the 1NF table. [8 marks]
(c) Normalise the table into 3rd normal form. You must show the progress from 1NF to 2NF, and
then to 3NF. Indicate all the primary keys and foreign keys in the normalised tables. [8 marks]
(d) Write the DDL to create the normalised tables and indicate the sequence of creating the tables.
Provide a screenshot for each table you created. Use appropriate entity names for example
Student, Grade, etc. to represent the normalised tablesrespectively. Use only the affinity data
type for the attributes in the tables. [8 marks]
(e) Write the DML in a correct sequence to insert all the data shown in the sample spreadsheet
into the respective tables. Provide a screenshot of each table after the insert. Use only the
single insert method. [6 marks]
(f) Write the SQL to show only the name of the student who has the maximum number of “D”
grade. Use the table names you have created in (d). (Note: Your SQL may show no result with
the current data.) [6 marks]
3. A table to record the information of Dentists, Patients, and Appointmentsin a clinic is given below.
DentistNo DentistName PatientNo PatientName PatientPhone AppointmentDate AppointmentTime AppointmentFee
On a single day each patient can have more than one appointment. It is known that DentistNo is
unique for each dentist. PatientNo is unique for each patient. PatientNo must not be used as a
composite key. A patient is allowed to see any dentist.
(a) Use examples with data to illustrate why the above table is not a good database solution with
respect to insert, delete and update anomalies. [6 marks]
(b) What is the entity relationship between Dentist and Patient? Provide examples with data to
illustrate the relationship. [2 marks]
(c) Provide a normalised 3NF solution. You must show the progress from 1NF to 2NF, and then to
3NF. Indicate all the primary keys and foreign keys in the normalised tables. [10 marks]
(d) If the constraint changes such that a patient can only see a particular dentist, are there any
changes to the 3NF tables? Explain your answer to get the full mark. [2 marks]
3
Submission Instructions:
(1) Submit your assignment as a PDF file only (to avoid misalignment in the diagrams).
(2) Use VISIO, Draw.IO or other software to draw diagrams, hand drawn diagram will NOT be
accepted.
(3) For each question, you must clearly indicate the answer for each part, any part that is missing
will receive 0 marks.
(4) Any diagrams and representation method that are not the same as those taught in workshop
and lecture will receive 0 marks.
4
Table Q2 for question 2
Student ID Name Date of
Birth
Gender Address Phone Email Degree Year Course No Title Credits Grade
MI47007 James Boon 7/07/1977 Male 10 Downing Street,
Wellington
22007007 JB007@gmail.com Bachelor of
Commerce
2020 INFO151 Databases 15 A+
MI47007 James Boon 7/07/1977 Male 10 Downing Street,
Wellington
22007007 JB007@gmail.com Bachelor of
Commerce
2020 ECON130 Microeconomic Principles 15 A+
MI47007 James Boon 7/07/1977 Male 10 Downing Street,
Wellington
22007007 JB007@gmail.com Bachelor of
Commerce
2020 FCOM111 Government, Law and
Business
15 B+
MI47007 James Boon 7/07/1977 Male 10 Downing Street,
Wellington
22007007 JB007@gmail.com Bachelor of
Commerce
2020 INFO101 Foundations of Info Systems 15 C
MI47007 James Boon 7/07/1977 Male 10 Downing Street,
Wellington
22007007 JB007@gmail.com Bachelor of
Technology
2019 INFO141 Systems Analysis 15 C
MI47007 James Boon 7/07/1977 Male 10 Downing Street,
Wellington
22007007 JB007@gmail.com Bachelor of
Technology
2019 INFO151 Databases & SQL 15 WD
MI47007 James Boon 7/07/1977 Male 10 Downing Street,
Wellington
22007007 JB007@gmail.com Bachelor of
Technology
2019 MGMT101 Introduction to
Management
15 B+
MI47007 James Boon 7/07/1977 Male 10 Downing Street,
Wellington
22007007 JB007@gmail.com Bachelor of
Technology
2019 QUAN102 Statistics for Business 15 A+