[SOLVED] CS database computer architecture case study SQL FIT2094-FIT3171 Databases

FIT2094-FIT3171 Databases
Session 5 Tutorial Activities
NORMALISATION
FIT Database Teaching Team
Complete the week 3 session 5 activities:
5.1 Steps on Normalisation Tutor Explanation 5.1.1 Introduction
5.1.2 The Normalisation Process:
5.2 Multiple Forms Normalisation Part 1
5.3 Multiple Forms Normalisation Part 2
5.4 Additional Normalisation Exercise
FIT2094-FIT3171 2021 Summer B
FIT2094-FIT3171 Databases
Author: FIT Database Teaching Team
License: Copyright Monash University, unless otherwise stated. All Rights Reserved.
COPYRIGHT WARNING
Warning
This material is protected by copyright. For use within Monash University only. NOT FOR RESALE. Do not remove this notice.
Page 1 of 9

Important
Remember, before starting any tutorial activity which involves working with files, first use SQL
Developer to pull from the FIT GitLab server so as to ensure your local and server files are in sync. 5.1 Steps on Normalisation Tutor Explanation
5.1.1 Introduction
The normalisation process starts with identifying what the supplied form represents, the attributes on the form and the possible existence of any repeating groups. The starting set of attributes is called UNF (UnNormalized Form). Consider the following form:
dentist_no
dentist_name
patient_no
patient_name
appointment
surgeryroom_no
date
time
D1011
Tony Smith
P100
Gillian White
12-Sep-19
10:00
S15
D1011
Tony Smith
P105
Jill Bell
12-Sep-19
12:00
S15
D1024
Helen Pearson
P108
Ian MacKay
12-Sep-19
10:00
S10
D1024
Helen Pearson
P108
Ian MacKay
14-Sep-19
14:00
S10
D1032
Robin Plevin
P105
Jill Bell
14-Sep-19
16:30
S15
D1032
Robin Plevin
P110
John Walker
15-Sep-19
18:00
S13
This form shows multiple instances of the details about dental surgery appointment (APPOINTMENT for short) , as such the UNF is represented as:
APPOINTMENT(dentist_no, dentist_name, patient_no,patient_name, app_datetime, surgeryroom_no)
To complete the normalisation, you take the UNF as the starting point and follow the normalisation steps through to at least 3NF.
5.1.2 The Normalisation Process:
Legend: Primary Key is underlined.
When completing normalisation you must not add any new attributes, for example, surrogate keys (these are added to the final logical model post normalisation). Also during normalisation you must not exclude any attributes displayed on the provided form/s we wish to capture the full semantics of the form/s. Decisions about making an attribute derived or not need to be based on transaction loads and more advanced statistics which are not available at this stage of the design process. Such decisions are made after the logical model is implemented as part of the fine-tuning of the database.
You should complete your normalisations using either Google Docs or MS Word. If you are using Google Docs be sure to download your work as a MS Word file and save it in your working directory. Regularly push your work onto FITGitLab server to maintain the development history.
Page 2 of 9

UNF
APPOINTMENT(dentist_no, dentist_name, patient_no, patient_name, app_datetime, surgeryroom_no)
1NF
APPOINTMENT(dentist_no, dentist_name, patient_no, patient_name, app_datetime, surgeryroom_no)
*note that there are 3 candidate keys: (dentist_no, app_datetime), (patient_no, app_datetime) and (surgeryroom_no, app_datetime) and (dentist_no, app_datetime) is picked as PK
Partial dependencies: dentist_no dentist_name patient_no patient_name
*note that we use general definition, partial dependency is based on PK and all candidate keys
2NF
APPOINTMENT(dentist_no, patient_no, app_datetime, surgeryroom_no)
DENTIST(dentist_no, dentist_name)
PATIENT(patient_no, patient_name)
Transitive dependencies: No transitive dependency
3NF
There is no transitive dependency, the 3NF is the same as the 2NF. Note that you are required to show all forms, even if they are the same as a previous form.
APPOINTMENT(dentist_no, patient_no, app_datetime, surgeryroom_no) DENTIST(dentist_no, dentist_name)
PATIENT(patient_no, patient_name)
Full Dependencies:
dentist_no, app_datetime pat_no, surgeryroom_no dentist_no dentist_name
patient_no patient_name
Page 3 of 9

5.2 Multiple Forms Normalisation Part 1
Normalise the following forms describing a student/units system to 3NF. Remember you must show UNF, 1NF, 2NF, 3NF and amalgamate
Note:

To simplify a normalisation process that involves multiple forms, you should perform the normalisation one form at a time until all relations are in 3NF. Once you have done this process for all forms, consolidate the relations from the different forms (called attribute synthesis) by:
grouping together all relations with the same primary key, i.e representing the same entity.
choose a single name for synonyms. For example, mentor is the same as lecturer.
Normally we include all attributes in the form/report, but report date in following reports is not an attribute that should be included in the normalisation process since it only represents the date when the report was printed.
UNITS CURRENTLY APPROVED REPORT DATE:
22/03/2020
Unit Number
Unit Name
Unit Description
Unit Value
FIT9131
Programming Foundations
Introduction to programming
6
FIT9132
Introduction to Databases
Database Fundamentals
6
FIT9134
Computer Architecture and Operating Systems
Fundamentals of computer systems and the computing environment
6
FIT9135
Data Communications
Fundamentals of data and computer communications
6
* Unit value may be either 3, 6 or 12 points
LECTURER DETAILS REPORT DATE: 22/03/2020
LECTURERS NUMBER: 10234 LECTURERS NAME: GUISEPPE BLOGGS LECTURERS OFFICE No.: 169 LECTURERS PHONE No.: 99037111
UNIT ADVISER FOR:
UNIT NUMBER
UNIT NAME
FIT9131
Programming Foundations
FIT9134
Computer Architecture and Operating Systems
* A given unit may have several advisers
* Some lecturers share offices, although each has their own phone
Page 4 of 9

STUDENT DETAILS REPORT DATE:
22/03/2020
STUDENT No.: 12345678
STUDENT NAME: Poindexter Jones
STUDENT ADDRESS: 23 Wide Road, Caulfield, 3162 COURSE ENROLLED: MIT
MODE OF STUDY: On-Campus
MENTOR NUMBER: 10234 MENTOR NAME: Guiseppe Bloggs
ACADEMIC RECORD:
UNIT NUMBER
UNIT NAME
YEAR / SEMESTER
GRADE
FIT9131
Programming Foundations
2017/2
N
FIT9131
Programming Foundations
2018/1
D
FIT9132
Introduction to Databases
2018/1
D
* Grade may have the value N, P, C, D or HD
* Mode of Study must be On-campus (O) or Distance Education (D)
In order to add a student, the lecturer who advises this student must already exist in the database. No lecturer who advises any students may be deleted from the database. If the lecturer number of a lecturer is changed, then the number would be changed for each student advised by that lecturer.
Page 5 of 9

5.3 Multiple Forms Normalisation Part 2
Normalise the following forms to 3NF. Remember you must show UNF, 1NF, 2NF, 3NF and amalgamate (consolidate/attribute synthesis). To simplify a normalisation process that involves multiple forms, you should perform the normalisation one form at a time until all relations are in 3NF. Once you have done this process for all forms, consolidate the relations from the different forms (called attribute synthesis) by grouping together all relations with the same primary key, i.e representing the same entity.
The following forms are provided by the agency as a follow up of the property-rental case study discussed in Session 3 Tutorial. The agency indicates that tenant and owner names must be decomposed into title, given name and family name, and property address and owner address must be treated as simple attributes.
The agency records normal property maintenance. Maintenance costs are charged to the property owner. Below is a sample of property maintenance report prepared for the property owner:
PROPERTY MAINTENANCE REPORT
Property No: 1965
Property Address: Unit 1 100-102 Platypus St, Oakleigh, VIC, 3166
Owner No: 9321
Owner Name: Mrs. Lilian Potter
Owner Address: 14 Puffin Rd, Wantirna, VIC, 3152
MAINTENANCE RECORD:
DateTime
Description
Cost
2019-02-01 9:00
Roof leak
$854
2019-02-10 10:00
Lawn repair
$230
2019-02-10 14:00
Minor crack repair
$428
The agency also provides two samples of simplified property tenant ledgers (tenant payment history):
Note that the two property tenant ledgers shown are two examples of the same form, only one normalisation process that covers both samples is required.
PROPERTY TENANT LEDGER (Sample 1)
Property No: 1965
Property Address: Unit 1 100-102 Platypus St, Oakleigh, VIC, 3166 Lease Start Date: 2017-01-14
Weekly Rental Rate: $425
Bond: $1,842
Tenant No: 512736
Tenant Name: Mr. Johanston Karkov
PAYMENT RECORD:
Number
Date
Type
Amount
Paid By
80010
2017-01-10
Bond
$1,842
bank cheque
Page 6 of 9

80024
2017-01-10
Rental
$1,842
direct deposit
80501
2017-02-10
Rental
$1,842
bpay
80612
2017-02-28
Damage
$523
credit card
80615
2017-02-28
Damage
$312
credit card
80892
2017-03-12
Rental
$1,842
bpay
81073
2017-04-13
Rental
$1,842
credit card
PROPERTY TENANT LEDGER (Sample 2)
Property No: 1965
Property Address: Unit 1 100-102 Platypus St, Oakleigh, VIC, 3166 Lease Start Date: 2019-05-05
Weekly Rental Rate: $475
Bond: $2,059
Tenant No: 623124
Tenant Name: Ms. Kiky Longbottom
PAYMENT RECORD:
Number
Date
Type
Amount
Paid By
101829
2019-05-01
Bond
$2,059
bank cheque
101934
2019-05-03
Rental
$2,059
credit card
102104
2019-06-04
Rental
$2,059
credit card
Page 7 of 9

5.4 Additional Normalisation Exercise
A travel agency manages flight bookings for its clients. For each booking, the agency creates a booking itinerary. The following rules are applied to the itineraries:
A booking is made for a single person. Each booking is identified by a unique booking number. A person may have one or more bookings with the travel agency.
A booking may involve one or many flights. Each flight is identified by the flight number. A flight number is unique for a given flight route and time of the day. The flight number will be re-used across different days to represent the same scheduled trip. However, airlines use a different flight number to represent each scheduled trip on the same day. For example, flight number QF47 represents a flight which departs from Sydney and flies to Melbourne at 7:30 PM. Flight QF47 may, for example, be scheduled to fly on each day of the week.
The duration of the flight does not need to be kept in the database, it is only printed on the itinerary based on the calculation of the difference between the departure date/time and the arrival date/time.
SAMPLE ITINERARIES
Three sample itineraries are supplied below:
Booking Number: QFTR23 Client no: 123
Client Name: W.H Red
Flight Number Depart date
Depart time
Depart Airport code Depart Airport name Arrival date
Arrival time
Arrival Airport code Arrival Airport name Duration
Flight Number Depart date
Depart time
Depart Airport code Depart Airport name Arrival date
Arrival time
Arrival Airport code Arrival Airport name Duration
: QF9
: 12-July-2018 : 10 AM
: MEL
: Melbourne
: 12-July-2018 : 7:50 AM
: LAX
: Los Angeles :14hr50mins
: AA1
: 12-July-2018 :10AM
: LAX
: Los Angeles : 12-July-2018 : 11:05 AM
: SAN
: San Diego
: 1 hr 5 mins
–
Page 8 of 9

Booking Number: AXYT12 Client no: 345
Client Name: B.B Brown
Flight Number Depart date
Depart time
Depart Airport code Depart Airport name Arrival date
Arrival time
Arrival Airport code Arrival Airport name Duration
–
Booking Number: QFAB12 Client no: 123
Client Name: W.H Red
Flight Number: Depart date
Depart time
Depart Airport code Depart Airport name Arrival date
Arrival time
Arrival Airport code Arrival Airport name Duration
: QF47
: 21-Aug-2018 : 7:30 PM
: SYD
: Sydney
: 21-Aug-2018 : 8:50 PM
: MEL
: Melbourne :1hr20mins
: QF47
: 22-Aug-2018 : 7:30 PM
: SYD
: Sydney
: 22-Aug-2018 : 8:50 PM
: MEL
: Melbourne :1hr20mins
1. Represent this booking data in UNF. Note that the three itineraries shown are three examples of the same form, only one UNF is required that would cover all three examples.
2. Convert this UNF to first normal form (1NF) and show all dependencies via dependency diagrams. Continue the normalisation through to third normal form (3NF). Clearly write the relations in each step from the unnormalised form (UNF) to the third normal form (3NF). You may consolidate the relations after arriving at 3NF (if necessary).
Important
After you have completed your current lab activities, at the end of each session remember to add, commit and push any changes you have made to the FIT GitLab server.
You need to get into the habit of establishing this as a standard FIT2094-FIT3171 workflow Pull at the start of your working session, work on the activities you wish to/are able to complete during this session, add files (stage)/commit changes and then Push the changes back to the FIT GitLab server.
Remember you should also regularly use the Web UI (login to the web interface of the server) to check that your files are correctly being pushed.
Page 9 of 9