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

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

By
21 comments
Read more

Sudoku Game Project in Java with full source code including GUI task

By
Introduction The purpose of the project is to try in practice the use of recursion and object-oriented program design. Please make sure to read this entire note before starting your work. Pay close attention to the sections on deadlines, deliverables, and exam rules. The problem Your task in this part of the project is to write a solver for Sudoku puzzles. Sudoku puzzles are a kind of crossword puzzles with numbers where the following two conditions have to be met: In each row or column, the nine numbers have to be from the set [1,2,3,4,5,6,7,8,9] and they must all be different.  For each of the nine non-overlapping 3x3 blocks, the nine numbers have to be from the set [1,2,3,4,5,6,7,8,9] and they ust all be different. The following two figures show a Sudoku puzzle and its solution. The input The input of your program, the Sudoku puzzles, is given in text files. More specifically, they are represented as nine lines of nine characters separated by spaces. The characters can be of two...
Post a Comment
Read more