Database Technology Repeat CA Entity Relationship Diagram with SQL queries

By Hassnain Jamil -

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.

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?

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

Comments

Post a Comment

Popular posts from this blog

[SOLVED] Tape for a Turing Machine using Doubly-linked List in Java with full source code

By Anonymous -
Tape for a Turing Machine (Doubly-linked List) Short exercise in which you will code part of an existing project. In this case, you will be working with a doubly-linked list. To make this topic more interesting, the list that you work on will be part of a Turing Machine. A Turing Machine is a kind of very simple, abstract computing device that was introduced in the 1930's by Alan Turing as a way of studying computation and its limitations. The classes that you need for the lab are in a package named turing. You will define a new class named Tape in this package. You can find the Java source in the code directory. You should copy this directory into an Eclipse project. There will be errors, since the existing classes depend on the Tape class, which you have not yet written. Don't forget the Javadoc comments. A Class for Turing Machine Tapes A Turing machine works on a "tape" that is used for both input and output. The tape is made up of little squares called cell...
Read more

[SOLVED] ICT285 Databases TMA 2017 Assignment 1 | Relational Algebra, Database Design & Implementation

By Anonymous -
Relational Algebra A database records information about athletes competing at the Olympics. An athlete competes for a particular country in one or more events. Events take place at a scheduled day and time in a particular venue. The result (rank) is recorded for all athletes in the final of the event. The medal (gold, silver or bronze) is also recorded for the medal winners in the event. Note that we are not considering team sports or heats in this example – only individuals competing in the finals. The schema for this database is as follows: (Note that primary keys are shown underlined, foreign keys in bold). ATHLETE ( AthleteNo , AthleteName, CountryName) COUNTRY ( CountryName , NumberOfCompetitors) EVENT (EventName, ScheduledStart, VenueName ) VENUE (VenueName, City, Capacity) FINAL ( AthleteNo , EventName , Rank, Medal) Provide relational algebra (NOT SQL) queries to find the following informaList the name and country of all athletes. List the event name and scheduled...
Read more