Skip to main content

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

Image
By

Database Technology Repeat CA

ERD (Entity Relationship Diagram)

i. Identify each of the entities
ii. Identify2-3 attributes for each entity with a key attribute
iii. Name the relationships for each of the binary pairs
iv. Identify the degree of relationship for each of the binary pairs
v. Decompose any many to many  relationships
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.

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

Buy now

CONTACT DETAILS

For any other questions or other tasks please feel free to contact me
via email: mhassnainjamil@gmail.com
via WhatsApp: +92-324-7042178
via skype: hassnainjamil1

Store View:


Labels:

Comments

Post a Comment

Popular posts from this blog

The Zoo Management System - entity relationship diagram & MS Access Database

By
Zoo Management System - Project Details: You are the employee of a big, worldwide working Zoo Management Company. Your company is responsible for the Zoo management. Your boss thinks it would be a great idea to store all data for each Zoo in a brand new self-developed ZOO Management System. Up to now, the ZOO management company has maps of each ZOO available. Your boss knows that you took a course in introduction on an ERP system, so he asks you if you could help designing such a system. Each ZOO must have the same organizational structure, which should look like this: Each Zoo has a Zoo-Address. Each Zoo has many visitors (Visitor Ticket Process (VTP). Many Zoo-Attractions belong to a Zoo. Module 1: Entity Relationship Diagram Design a ER (entity-relationship) diagram for your ZOO Management System. Use the information provided below with the entities and its attributes. Put the entities in the correct relationship to each other (organizational structure). Module 2: DB Implem...
1 comment
Read more

EIT Knowledge and Innovative Community Scholarships has been announced

By
Admission Criteria To qualify for our programmes, applicants need to fulfill the admission requirements based on previous studies, English proficiency and relevant documentation. Previous Studies: A Completed Bachelor’s Degree In order to be admitted into a KIC InnoEnergy MSc programme, you must have completed a Bachelor’s degree encompassing a minimum of 180 ECTS credits or equivalent academic qualifications from an internationally recognized university. Please note that admissions depend on the specific BSc degree you hold for entry into the MSc programme you are interested in. Conditional Acceptance – Undergraduate Students in Final Year Students in their final year of undergraduate education may also apply and if expected to qualify, receive a conditional offer. If you have not completed your studies, please include a written statement from your university’s administration office (or equivalent department), confirming that you are enrolled in the final year of your study programme ...
Post a Comment
Read more

Human Physiology by Stuart Ira Fox [PDF] (12th edition) free download

By
21 comments
Read more