SQL database design for a Hotel Management System Assessment

Part 1 – Questions (1 mark): 

1. In logical database design, why there is a need to introduce a new relation for many- to-many relationship in ERD?
2. Describe how to convert a generalization/specialization in ERD into relations in GRD
3. What are user interface and system interface respectively? 
4. List at least 4 typical menu types and discuss their advantages and disadvantage
5. Write an SQL statement to create a view about the hotel numbers and hotel names
6. Write an SQL statement to insert the staff numbers and names into the guest table 

 Part 2 – Practices (1 mark):

 SAD – User Interface Design. Design a user interface for the final project (i.e., the MICT course project).

  Note: This user interface design will not be included in the final project.

However, it is important to gain some experience in user interface design as this is a key step to the success of an IT project.

Part 3 – Practices (1 mark):

 SQL – other statements in basic SQL 

1. Create a view to check the hotel name, room number and the guest names who booked the room;
2. Select everything from the view created above;
3.  Drop the above view;
4.  Create a view to check the hotel number and hotel names and the number of rooms they
5. Create a view to see the pair of room types and
6.  Create a view to see the distinct pair of room types and
7. Create an index of room types and prices on the room table;
8.  Drop the above index;
9.  Create an index of the guest names;
10.  Create a new table including hotel information (i.e., hotel names and numbers) from the room table;
11. Backup the room table;
12. Insert the staff numbers and names as the guest numbers and names into the guest table. If not successful, why?

 Note: for comparison purposes, you’d better keep all records of your SQL statements (simply save them in a separate .txt or .doc file).