CSG1207/CSI5135 Database Normalization Assignment: ERD and Design

This assignment solution focuses on database normalization, covering various normal forms (0NF, 1NF, 2NF, and 3NF) and Entity-Relationship Diagram (ERD) design. The solution addresses three tasks: the first involves normalizing a hotel booking database, the second focuses on an invoicing system, the third involves designing an internet service provider database, and the fourth task centers on a support system database. Both logical and physical ERDs are provided, along with detailed assumptions for each scenario. This assignment provides a comprehensive understanding of database design principles, relational model creation, and the application of normalization techniques to optimize database structures. The solution showcases how to create efficient and well-structured databases for different real-world scenarios, including hotels, invoicing, internet service providers, and support systems. This assignment is perfect for students looking to enhance their database design and normalization skills. The solution also includes physical ERDs and assumptions for all the database models.

CSG1207/CSI5135
Assignment 1
Student Name
Student Code
Instructor Name

Task 1
0NF
R1 = (customerPhone, customerName, room#, roomDescription {bookingID,
bookingDateTime, bookingDuration, bookingPaid, bookingNotes, staffID, staffName})
1NF
R1 = (customerPhone, customerName, roomID, room#, roomDescription {bookingID,
bookingDateTime, bookingDuration, bookingPaid, bookingNotes, staffID, staffName})
R11 = (customerPhone, customerName, room#, roomDescription)
R12 = (bookingID, customerPhone, bookingDateTime, bookingDuration, bookingPaid,
bookingNotes, staffID, staffName)
2NF
R11 = (customerPhone, customerName, room#, roomDescription)
R12 = (bookingID, customerPhone, bookingDateTime, bookingDuration, bookingPaid,
bookingNotes, staffID, staffName)
R121 = (bookingID, customerPhone, staffID, bookingDateTime, bookingDuration,
bookingPaid, bookingNotes)
R122 = (staffID, staffName)
3NF
R11 = (customerPhone, customerName, room#, roomDescription)
R111 = (customerPhone, customerName, room#)
R112 = (room#, roomDescription)
R121 = (bookingID, customerPhone, staffID, bookingDateTime, bookingDuration,
bookingPaid, bookingNotes)
R122 = (staffID, staffName)

Final Named Relations
Room (room#, roomDescription)
Customer (customerPhone, customerName, room#)
Staff (staffID, staffName)
Booking (bookingID, customerPhone, staffID, bookingDateTime, bookingDuration,
bookingPaid, bookingNotes))
Physical ERD
Assumptions
1. One customer can book multiple rooms
2. Staff can utilize bookingNotes for further reference
3. Customer Number will be unique

Task 2
0NF
R1 = (order#, orderDate, customer#, customerName, customerPhone, customerEmail, address,
deliveryNote {id#, item, description, qty, price, shipping})
1NF
R1 = (order#, orderDate, customer#, customerName, customerPhone, customerEmail, address,
deliveryNote {id#, item, description, qty, price, shipping})
R11 = (order#, orderDate, customer#, customerName, customerPhone, customerEmail, address,
deliveryNote)
R12 = (id#, order#, item, description, qty, price, shipping)
2NF
R11 = (order#, orderDate, customer#, customerName, customerPhone, customerEmail, address,
deliveryNote)
R12 = (id#, order#, item, description, qty, price, shipping)
R121 = (id#, order#, qty, shipping)
R122 = (id#, item, description, price)
3NF
R11 = (order#, orderDate, customer#, customerName, customerPhone, customerEmail, address,
deliveryNote)
R111 = (order#, orderDate, deliveryNote, customer#)
R112 = (customer#, customerName, customerPhone, customerEmail, address)
R121 = (id#, order#, qty, shipping)
R122 = (id#, item, description, price)
Final Named Relations
Invoice (order#, orderDate, deliveryNote, customer#)

Customer (customer#, customerName, customerPhone, customerEmail, address)
InvoiceItem (id#, order#, qty, shipping)
Item (id#, item, description, price)
Physical ERD
Assumptions
1. Customer may have 1 or multiple invoices.
2. Each invoice must have atleast 1 item.
3. Item may be a part of invoice or not.

Task 3
Logical ERD

Physical ERD
Assumptions
1. Each access type can have multiple plans but at the same time each plan uses single
access type.
2. Each customer have single internet plan.
3. Every staff member is allocated single level.
4. Each staff member can have multiple support jobs
5. Each customers can log multiple support jobs.

Task 4
Logical ERD

Physical ERD
Assumptions
1. Each staff member can only have one mentor at a time but mentor can mentor n number
of staff members.
2. Support job have the current status.
3. Single support job can associated with multiple notes.