The schema is as follows:
Hotel(hotelname, hoteladdress, suburb, hotelphone, fax)
Room(roomno, hotelname, type, price)
Guest(guestno, guestname, guestaddress, guestphone)
Booking(guestno, datecheckin, datecheckout, hotel, payment)
Hotels are uniquely identified by names (hotelname).
Each guest is assigned a unique guest number (guestno).
Payment is the amount that a guest has paid (if he has paid).
For each relation schema in the given database schema, give
the candidate keys,
the primary keys,
the foreign keys,
other possible integrity constraints.
For the given database schema, is NULL allowed for attribute payment of relation Booking? Explain your answer.
Suppose that we would like to expand the given database schema by adding a Facility relation, describing whether a hotel offers a range of services such as restaurant, bar, laundry etc. Define the relation schema for Facility with reasonable attributes and give likely integrity constraints incurred.
Regarding the given database, for each of the following queries, give an expression in relational algebra. You can use a series of intermediate relations and can rename. You cannot use aggregate functions.
Find the type and price of rooms that the Ibis hotel has.
Find the guest number (guestno) and name of guests who have bookings with the Ibis hotel and who have paid.
Find the guest number (guestno) and name of guests who have no bookings with any of the hotels located in Carlton.
Find the booking where the guest has paid the most.
For each of the 4 queries above, write the SQL expressions.
Officials at the Noosa Airport decided that all essential information related to the airport should be organised using a DBMS, and you have been hired to design the database. Your first task is to organise the information about all the airplanes stationed and maintained at the airport. The relevant information is as follows.
Every airplane has a registration number, and each airplane is of a specific model.
The airport accommodates a number of airplane models, and each model is identified by a model number (for example DC-10) and has a capacity and a weight.
A number of technicians (such as mechanics, traffic controllers, computer repairmen and network support people) work at the airport. You need to store the name, driver licence number, address, phone number, and salary of each technician.
Many technicians, especially mechanics and traffic controllers, are experts on one or more plane model(s), and his or her expertise may overlap with that of other technicians. This information about technicians must also be recorded.
The Civil Aviation Safety Authority (CASA) establishes that airport personnel must take a progressive series of training courses. Each course is identified by a courseID and has a title. For example, mechanics must receive a manufacturer's update on airplane models they service, traffic controllers must have a retraining on airplane tracking systems, and network support should be instructed on security issues. Store start and end dates that the person attends.
Once a person has attended a training course, he/she can take a certification exam at the level CASA requires for their position. For each course, certification levels are identified as 1, 2, 3, etc. Exam date and mark should be recorded when a technician takes an exam.
About 80% of all airport technicians belong to the Noosa Airport Union. Union officers will be allowed to access those items of the database that are relevant to them. You must store the union membership ID of each person, and the date they joined the union.
The airport has a number of tests that are used periodically to ensure that airplanes are still airworthy. Each test has a CASA test number, a name, and a maximum possible score.
The CASA requires the airport to keep track of each time a given airplane is tested by a given technician using a given test. For each testing event, the information needed is the date, the number of hours the technician spent doing the test, and the score the airplane received on the test.
Draw an ER diagram to plan the Noosa Airport technical database. In addition to entities and relationships, indicate wherever applicable: all cardinality and participation constraints, weak entities, and their identifying relationships, and all key, composite and multivalued attributes.
From the design developed above, write out the full database schema. For each relation, underline primary keys, and asterisk foreign keys if any.
In some cases, the result obtained in a certification exam is not satisfactory for CASA purposes and the technician is allowed to retake the exam on a later date. Modify the schema to satisfy the requirement of recording, for the same person, the same exam on different dates.