Database Technology Repeat CA | ERD Entity Relationship Diagram, Normalization and SQL

Database Technology Repeat CA

ERD (Entity Relationship Diagram)


  1. i. Identify each of the entities
  2. ii. Identify2-3 attributes for each entity with a key attribute
  3. iii. Name the relationships for each of the binary pairs
  4. iv. Identify the degree of relationship for each of the binary pairs
  5. v. Decompose any many to many  relationships
  6. vi. Draw the completed Entity Relationship Model


A restaurant manager wishes to set up a database to record the details of its customers, reservations for tables in the restaurant and the employees assigned to tables.

Screenshot



For each customer the database will record -- a customer number (unique), name, address and phone number. The details to be recorded for each table include - table number (unique), capacity. Customers make reservations for tables. The requested time and date of the reservation is recorded along with a unique reservation number. Each reservation is made by one customer and books one table. A customer will make a number of reservations and each table can have many reservations. An employee may be assigned a number of tables to wait on and a given table is assigned one employee. The information to be stored on each employee includes –employee code (unique), name, address, phone number and social security number.

Normalisation

Patient Test Results

(Patient No, Test Code, Result, PatientName, Patient Address, Next of Kin ID, NextofKin Address)
For each patient the database records a result in a given test, a name, an address, and the name of their next of kin. The address of their next of kin is also recorded.

SQL 

The following tables form part of a database held in a relational DBMS.
Hotel (hotelNo, name, address)
Room (roomNo, hotelNo, type, price)
Booking (hotelNo, guestNo, dateFrom, dateTo, roomNo)
Guest (guestNo, name, address)
where Hotel contains hotel details and hotelNo is the primary key.

Room contains room details for each hotel and roomNo, hotelNo forms the primary key. Booking contains details of the bookings and the primary key comprises hHotelNo, guestNo and dateFrom. Guest contains guest details and guestNo is the primary key.

Construct an SQL statement to answer the following queries.


  1. (i) List full details of all hotels.
  2. (ii) List full details of all hotels in London.
  3. (iii) List the names and addresses of all guests in London, alphabetically ordered by name.
  4. (iv) List all double or family rooms with a price below £40.00 per night, in ascending order of price.
  5. (v) List the bookings for which no date_to has been specified.
  6. (vi) How many hotels are there?
  7. (vii) What is the average price of a room?
  8. (viii) What is the total revenue per night from all double rooms?
  9. (ix) List the price and type of all rooms at the Grosvenor Hotel.
  10. (x) List all guests currently staying at the Grosvenor Hotel.
  11. (xi) What is the total income from bookings for the Grosvenor Hotel today?
  12. (xii) List the number of rooms in each hotel.
  13. (xiii) List the number of rooms in each hotel with more than 100  rooms and located in London.
  14. (xiv) What is the most commonly booked room type for each hotel in London?


Get Project Solution Now

Comments

Post a Comment