Consider the ER diagram shown in Figure 1 for part of a BANK database. Each bank can have multiple branches, and each branch can have multiple accounts and loans.
1. List the non-weak entity types in the ER diagram.
2. Is there a weak entity type? If so, give its name, its partial key, and its identifying relationship.
3. What constraints do the partial key and the identifying relationship of the weak entity type specify in this diagram?
4. Write concisely the user requirements that led to this ER schema design.
5. Suppose that every customer must have at least one account but is restricted to at most two loans at a time and that a bank branch cannot have more than 1000 loans. How can this be represented using (min, max) constraints?
Figure 1. ER diagram for the bank database
[16 marks] Question 2:
The following tables form part of a database held in a relational DBMS:
- Hotel (hotelNo, hoteiName, city)
- Room (roomNo, hotelNo, type, price)
- Booking (hotelNo, guestNo, dateFrom, dateTo, room No)
- Guest (guestNo, guestName, guestAddress)
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 bookings and (hotelNo, guestNo, dateFrom) forms the primary key.
Guest contains guest details and guestNo is the primary key.
Using the above Hotel database schema, write SQL queries to retrieve the followings:
1. List full details of all hotels.
2. List full details of all hotels in Jeddah.
3. List the names and addresses of all guests in Jeddah, alphabetically ordered by name.
4. List all double or family rooms with a price below 400 SAR per night, in ascending order of price.
5. List the bookings for which no dateTo has been specified.
6. How many hotels are there?
7. What is the average price of a room?
8. What is the total revenue per night from all double rooms?
9. How many guests have made bookings for August?
10. List the price and type of all rooms at the Hayat Hotel.
11. List all guests currently staying at the Hayat Hotel.
12. List the details of all rooms at the Hayat Hotel, including the name of the guest staying in the room, if the room is occupied.
13. What is the total income from bookings for the Hayat Hotel today?
14. List the rooms that are currently unoccupied at the Hayat Hotel.
15. List the number of rooms in each hotel.
16. List the number of rooms in each hotel in Dammam.
[40 marks] Question 3:
An EPC company requires an information system to track the professional development (PD) of its engineering and management team members. The personal information of each member needs to be stored (i.e., name, address, and contact information). A member is required to accumulate at least 100 PDHs (professional development hours) each year. PDHs are acquired through different types of activities. Formal activities include conferences, workshops, meetings, short courses, or meetings that target PD training. Each hour spent on any of these activities is equivalent to 1 PDH. Members need to file annually the number of PDHs they accumulated together with the type of activity they engaged in to acquire the PDH, its title, location, time, and the host organization.
The company delivers different activities to support the PD of its members. These activities include one-day workshops, multi-day conferences, monthly half-day training courses, and a weekly one-hour PD meeting. The company needs to keep track of the subject, location, date, and attendees of each of these programs. The information system should automatically update the PDH numbers for each member that attends a company-delivered PD activity.
A member involved in delivering any of the above activities as a trainer in the company or outside the company also accumulates PDHs. Each hour spent as a trainer is equivalent to 2 PDHs. Members also may accumulate PDH through volunteering for community services and charitable organizations. A volunteer hour is equivalent to 1 PDH to a maximum of 20 PDH per year.
The information system should allow company management to track the history of PDH for each member over the years, to get a list of members that are below, above, or within a user-defined number or range of PDH, or to track PDH hours per activity type for one or all members. It also should allow every member to enter his/her PD activities outside the company and to check his/her PDH history.
Develop a database that delivers the above requirements (at minimum) with a user-friendly interface for different potential users. Assume and expand on any requirements that are necessary but not fully scoped in the above description.
Include in your report the ERD for the database, the SQL statements used for any queries in the system, and a mini user manual of how to use the system.
