You work for a motor vehicle rental company, where all rental information is currently stored on paper-based forms. The company is about to launch its online rental services and you have been tasked with designing a relational database for the company.
Draw a complete EER diagram in Chen’s notation that captures all information and semantics required by the rental agreement form shown in Figure 1. The EER diagram must incorporate at least one specialisation, decompose any N:M relationship to its corresponding weak entity type, and must consider business rules outlined on the next page. The diagram can be drawn using a software tool (e.g. Draw.io, Visio, LucidChart, etc.) or with hands. To complete answering this question, copy and paste the completed diagram onto this document.
Figure 1. Business form: Motor vehicle rental agreement
- The first three fields at the top of the rental agreement form (company name, address, and phone) refer to the name, address, and phone of the motor vehicle rental company you are working for. The company is not willing to accept any new data field/attribute in the EER design other than what is currently included in the rental form.
- One rental agreement is only applicable to one vehicle; renting more than one vehicle requires more than one rental agreement. Details of a hirer are only recorded when he or she makes at least one rental.
- Each rented vehicle has fixed daily/weekly/monthly charge rates and a fixed extra hour rate. These rates are non-negotiable and are not determined by the rental agreement.
- Each rented vehicle has a fixed damage/loss liability value that depreciates as it gets older and must be updated from time to time by the company. This is not determined by a rental agreement.
- The company distinguishes two kinds of hirer: single hirer and joint hirer. In the case of joint hirer, details of a second hirer must be recorded.
- It is not possible for the same vehicle to be part of two different rental agreements with a similar date out.
- It is possible for a hirer to rent the same vehicle again in the future.
You are part of a software development team that is tasked with redesigning Charles Darwin University’s database system. There are 1,200 teaching staff, 500 units and 22,000 students at the university.
- A unit may exist even if no student is currently enrolled in it and no teaching staff is assigned to it.
- A teaching staff may teach none, one, or more units in a semester. On average a teaching staff teaches 2.0 units per semester.
- A unit may have none or up to two prerequisites. A unit can be a pre-requisite to more than one unit but not all units require a pre-requisite.
- A unit is taught by exactly one teaching staff in a semester. The same unit, however, may be taught by another teaching staff in a different semester.
- The average enrollment in a unit is 25 students.
- A student may exist prior to making enrolment into a unit. A student, however, must choose at least one unit per semester. On average, a student chooses 4.0 units in a semester.
Draw a UML class diagram that considers business rules above. There is no need to list variables and methods in each UML class. Show association classes where appropriate. Use as many UML notations and concepts you have learned this semester to capture as much semantics of the problem as possible.
A library database records the authors and the publishers of each book. Normalise the following relation up to 3NF.
A_Relation (ISBN, title, author(name, date_of_birth), publisher(name, address(streetnr, streetname, zipcode, city)), pages, price)
The assumptions are:
- Each book has a unique ISBN number;
- Each author has a unique name;
- Each publisher has a unique name;
- A book has only one publisher;
- A publisher has only one address.
Show the stages of your normalisation process (i.e. 1NF → 2NF → 3NF). At each stage, explain with example the specific problem to be resolved. Once normalised to 3NF, assign a suitable name to each relation and indicate their primary keys and foreign keys.
(a) An arbitrary SQL statement is given below. This block of code contains one error which leads to an unsuccessful execution. Identify the error and rewrite this block of code with an appropriate fix.
FROM product y LEFT JOIN orderline x ON (x.productnumber = y.pnumb)
WHERE SUM(y.quantityonhand) > 10
ORDER BY x.productnumber;
(b) Figure 2 shows the basic structure of a graph database, consisting of just two labels (Student, Unit) and a relationship [:ENROL_INTO]. Assume that the database is populated with Student and Unit nodes, with relationships among them. A student may enroll into many units. A unit can enroll many students.
A Student node has a .fullname property (assumed to be unique), whereas a Unit node has a .unitname property (also unique).
Write a Cypher statement that recommends new units to a student from the units taken by another student with whom the first student has previously shared at least one common unit. The Cypher statement should return the full name of both students plus the name of unit(s) to be recommended.
Two students Leonardo Da Vinci and Michelangelo previously enrolled into the same painting class (i.e. common unit). In addition, Michelangelo also took a sculpting class, which Da Vinci (i.e. the first student) never enrolled into. Therefore, your Cypher statement should recommend the sculpting class to Da Vinci.
Note: Your Cypher code should NOT be written for specific student names (e.g. Leonardo Da Vinci and Micheangelo), but should be applicable to all students in the database. The above example is only an illustration.
Consider an OLAP cube shown in Figure 3. It represents sales figures (in AUD$ ‘000) of various products according to their types (accessories, bikes, clothing, components), colors (black, blue, red, and silver) and financial years (2010 – 2013).
Figure 3. OLAP cube
Using values shown in the OLAP cube, explain with example the meaning of each OLAP operation below:
(a) rolling up the color dimension
(b) slicing operation
Note: you are not required to write any SQL statement to solve these problems.
Examine database transactions detailed in Table 1.
Solve the following problems:
(a) Argue whether transaction schedule is serializable or not serializable. Support your answer by drawing a complete precedence graph, along with detailed explanations.
(b) Demonstrate one type of concurrency problem that might arise from transaction schedule . By indicating relevant part(s) in both transactions, explain how the error occurs.
(c) Determine if transaction schedule is a serial schedule or non-serial schedule. Explain your answer.
(d) In general, contrast the impact of optimistic and pessimistic schedulers on the performance of a database system. Discuss in what case each of these approaches is suitable.
Table 1. Transaction schedule S showing two transactions T1 and T2.