Hit234 Database Management Assignment 2 - School Of Engineer

Hit234 Database Management Assignment 2school of Engi

Hit234 Database Management Assignment 2school of Engi

Describe a scenario involving a hotel management system, including details about hotel attributes, employees, guests, reservations, rooms, and billing, along with business rules. Then, map the scenario's ER diagram to 3NF and normalization data schema, translating the ER diagram into a normalized table schema, ensuring it adheres to Second Normal Form and is free of transitive dependencies.

Paper For Above instruction

Introduction

Designing an effective hotel management database requires a comprehensive understanding of the core entities, their attributes, and the business rules governing their interactions. The case of a Hilton hotel chain situated in major cities around Darwin, Australia, offers an illustrative scenario to explore these principles. This paper outlines the scenario, constructs an ER diagram, and demonstrates the process of mapping the ER to a normalized database schema adhering to the Third Normal Form (3NF), thus ensuring data integrity, efficiency, and adherence to business rules.

Scenario Description

The Hilton hotel chain operates across multiple prominent locations in Darwin, providing lodging, hospitality, and related services. Each hotel is uniquely identified by attributes such as Hotel Code, Hotel Name, Hotel Number, and Location. The management employs multiple employees—each with an Employee ID, Name, Date of Birth, Phone, and assigned Room Category. Employees are tasked with managing specific rooms based on the Room Category attribute assigned to them.

Guests register at the hotel to make reservations for rooms of their choice. Each reservation includes Reservation No, Guest ID, Hotel Code, Room Type, and Room ID. Guests' demographic details include Guest ID, Name, Phone, Address, and assigned Room ID, linked via reservations. The system records billing details such as Invoice No, Guest ID, Room Charge, and Miscellaneous Charges, with billing linked to the guest's reservation and actual stay.

Business rules introduced into this scenario stipulate that each guest makes one reservation; each hotel can have many reservations and guests; multiple guests can occupy a single room concurrently; a room can host multiple guests over time; each room is managed by one employee; and every reservation corresponds to one room. The rules also state that guests pay bills based on the reserved room, and employees are assigned to manage specific rooms.

ER Diagram Construction

Using Chen and Crow's Foot Notation, the core entities are Hotel, Employee, Guest, Reservation, Room, and Billing. The relationships include:

  • Hotel has many Rooms; each Room belongs to one Hotel
  • Employee manages one or more Rooms
  • Guest makes Reservations; each Reservation is linked to one Guest and one Room
  • Room can accommodate many Guests over different time periods, with Reservations acting as the linking entity
  • Billing is associated with Reservations and Guests, recording charges

The ER diagram, therefore, captures relationships such as one-to-many between Hotel and Room, one-to-many between Employee and Room, many-to-many between Guest and Room through Reservation, and one-to-one between Reservation and Billing. The diagram ensures all business rules are represented clearly.

Normalization to 3NF & Data Schema Design

Initial Schema

  • Hotel(Hotel_Code, Hotel_Name, Hotel_Number, Location)
  • Employee(Employee_ID, Name, DOB, Phone, Managed_Room_ID)
  • Room(Room_ID, Hotel_Code, Room_Type, Room_Category)
  • Guest(Guest_ID, Name, Phone, Address)
  • Reservation(Reservation_No, Guest_ID, Hotel_Code, Room_ID, Room_Type)
  • Billing(Invoice_No, Guest_ID, Reservation_No, Room_Charge, Miscellaneous_Charge)

Functional Dependencies

  • Employee_ID → Name, DOB, Phone, Managed_Room_ID
  • Reservation_No → Guest_ID, Hotel_Code, Room_ID, Room_Type
  • Guest_ID → Name, Phone, Address
  • Invoice_No → Guest_ID, Reservation_No, Room_Charge, Miscellaneous_Charge

Normalization Process

Analysis of functional dependencies confirms that each table is in Second Normal Form (2NF) as all non-key attributes depend entirely on the primary key. To achieve 3NF, transitive dependencies are eliminated:

  • In the Hotel table, all attributes depend solely on the primary key Hotel_Code, satisfying 3NF.
  • In Employee, Employee_ID uniquely determines attributes, and Managed_Room_ID is dependent on Employee and not transitively on other attributes.
  • Room table's primary key is Room_ID, with attributes dependent solely on it.
  • Guest table's primary key is Guest_ID, fully determining Name, Phone, Address.
  • Reservation's primary key is Reservation_No; all attributes depend upon it without transitive dependencies.
  • Billing's primary key is Invoice_No, fully depending on it for Room Charges and guest details.

This schema satisfies 3NF, ensuring minimal redundancy and supporting efficient data operations.

Conclusion

The proposed scenario, ER diagram, and normalized data schema provide a robust foundation for a hotel management system. By adhering to normalization principles up to 3NF, the system avoids redundancy and anomalies, optimizes data integrity, and facilitates reliable reporting. Proper normalization, combined with a clear understanding of business rules, ensures that the hotel management database can support current operations and future scalability efficiently.

References

  • Elmasri, R., & Navathe, S. B. (2015). Fundamentals of Database Systems (7th ed.). Pearson.
  • Date, C. J. (2012). Database Design and Relational Theory: Normal Forms and All That Jazz. O'Reilly Media.
  • Coronel, C., & Morris, S. (2015). Database Systems: Design, Implementation, & Management (12th ed.). Cengage Learning.
  • Hoffer, J. A., Ramesh, V., & Topi, H. (2016). Modern Database Management (12th ed.). Pearson.
  • Lamb, J., & Kelly, T. (2018). Practical Database Design. IEEE Software, 35(4), 50-55.
  • Silberschatz, A., Korth, H. F., & Sudarshan, S. (2019). Database System Concepts (7th ed.). McGraw-Hill Education.
  • Rob, P., & Coronel, C. (2007). Database Systems: Design, Implementation, and Management. Cengage Learning.
  • Hughes, J., & Lemos, B. (2014). Normalization in Database Design. Journal of Data Management, 12(1), 23-29.
  • Ferrari, A., & Koti, S. (2020). Best Practices in Database Normalization. International Journal of Information Management, 50, 129-137.
  • O'Neil, P., & O'Neil, E. (2014). Database: Principles, Programming, and Performance. Morgan Kaufmann.

Related Assignments