Answer Happy

As a database developer at Orchid Data Development Company (ODD), you got a new project to develop a database system for a hotel named the Beachside Hotel. ODD Company conducted a meeting with the Beachside Hotel Manager and they required a System for their hotel. The Beachside Hotel Manager required a system that would allow the customer to search for a room, book a room, cancel a room and book additional service. In addition, the customer must pay online through the banking system to book any service at the hotel. To book a room at Beachside Hotel, the customer must have an account that includes a unique ID refers to his/her National ID. Also, first name, last name, phone number, birthdate, gender and address. Each customer has one or many payment card(s). Each payment card has an ID issue date an expiry date. The customer can book one or many rooms at the Beachside Hotel. The customer can choose the type of room he/she wants to book. The room types are single, double and family room. Each room contains a unique number, number of beds, room location, sea view option, a breakfast option and price per night. You also need to keep the customer's check in and check out date of the room, if the room is occupied, and the availability of the room In addition, there are extra facilities the customer can book in Beachside Hotel such as spa and gym. The customer can book one and only one spa service. Where the Spa includes spa service name, service price, opening time and closing time. Moreover, the customer can attend the gym and book various services at the gym. Many customers can book the Gym The Gym includes gym name, opening time, closing time, machine type, and subscription price and gym services. At the end, after creating the database system, you need to apply a password to have a secure database system. The hotel manager requested to perform the following operations using the database system: 1. Registration and deletion of customers' accounts 2. Adding, deleting and updating rooms, payment card and services 3. Updating the reservation status (Availability) for the rooms 4. Lists displaying the following: a. All unreserved rooms ordered by number of beds, in descending order b. All double and family rooms with a price below 1008 per night, in ascending order of price. c. The details of all rooms, including the name of the customer staying in the room, if the room is occupied. d. All spa services with name, gender and phone number of registered customers 5. Total revenue per night from all double rooms 6. Total number of customers who booked a single room with sea view option In addition, your manager asked you to suggest additional queries that may case the process of management and produce appropriate management information There is a list of rules and restrictions that must be applied to the developed database to ensure the accuracy of the data entered, which are as follows: 1. Do not include any room without specifying the number of beds 2. If the customer did not fill in the address, fill it with "Jordan" by default. 3. All customers must be over 18 years old 4. Fill in the room availability with "Available' or 'Unavailable option only. Also, the sea view option and breakfast option with "yes" or "no" options only.
Assignment activity and guidance The main task assigned to you is: Task 1: Designing the database system 1. Investigate the requirements of the system 2. Design the database system using the conceptual design (ER Model) 2.1 Identify the entities and attributes of the project 2.2 List the relationships and their types 2.3 Check whether the provided design is normalized. If not, normalize the database by removing the anomalies 3. Design the relational database which includes 3.1 List of tables mapped from the ERD 3.2 Database schema which includes the fields, datatypes and constraints 3.3 Specify identifiers (primary Key) and foreign keys datatypes 4. Design set of simple input and output interfaces for the project using Wireframe or any interface-designing tool. In addition, explain the validation mechanisms to validate data in the tables with examples. 5. Check whether your design is representing the identified user and system requirements to asses the effectiveness of your design Recommended Resources Please note that the resources listed are examples for you to use as a starting point in your research the list is not definitive. Elmasti. Fundementals of Database System. Tth Ed Learning Outcomes and Assessment Criteria Pass Merit Distinction LO1 Use an appropriate design tool to design a relational database system for a substantial problem P1 Design a relational M1 Produce a D1 Assess the database system using comprehensive design effectiveness of the design appropriate design for a fully functional in relation to user and tools and techniques, system which includes system requirements. containing at least interface and output four interrelated designs, data tables, with clear validations and data statements of user normalization. and system requirements.