Question 1
Rent-a-Car is a car rental company with a large fleet of cars and it offers short term rental to both individual and corporate customers. To rent a car, a customer must be registered. If the customer is an individual, the customer name, an email and a contact number are required. If the customer is a corporate customer, the company registration number, the year it is established, email address and a contact number must be supplied.
Stuck with a lot of homework assignments and feeling stressed ? Take professional academic assistance & Get 100% Plagiarism free papers
If customer registration is unsuccessful, the customer data is not recorded. Otherwise, a customer ID is generated. Individual customers then need to supply credit card information which includes cardholder name, card number, date of issue, expiry date and security code before any rental can be made. If the customer is corporate, an account is created for payment purposes. The account is identified by account number and it records the outstanding balance.
A rental request can be made not more than 3 months in advance. To rent, a customer selects a collection date and time, return date and time, a collection location, a return location and a car model. Upon a successful rental, the customer will receive a rental code. A rental status records that the rental is a pending collection. The other valid values for status are cancelled, not collected, collected or completed.
The rental charge is computed based on the type of day (weekdays, weekends), the time periods (peak, non-peak), the rental duration and the car model. 30% of the charge are payable at the time of reservation for an individual customer, and 70% of the charge is payable upon collection. The payment is paid using the credit card of the individual customer.
For each payment, the payment date, payment number, payment amount and the transaction type (either deposit, balance, extension fee, service fee or refund) are recorded A corporate customer is sent a bill via email at the end of every month or whenever the account balance exceeds $2000. The bill should display the rentals with the corresponding charge. When a corporate payment is received, the payment date, payment number and payment amount is recorded and the balance updated.
To make a collection, a customer must enter a rental code to unlock the assigned car.
A rental can be cancelled at least one week before collection, without incurring a penalty. If that happens, then in the case of a rental by an individual, a refund is made to the credit card and in the case of a rental by the corporate customer, the amount is deducted from the outstanding balance. If a rental is cancelled less than one week before collection or if the car is not collected within 24 hours of the stated collection date and time, a 30% penalty is imposed (no refund or no deduction of outstanding balance). If a car is not collected within 24 hours, the rental is automatically cancelled. Cancelled rentals and rentals with no collection are recorded to determine whether a customer should be blacklisted.
When the customer collects the car, the car registration number is recorded. A customer may choose to extend the rental period at any time before the car is returned. The additional rental charges due to the extension are payable if the extension is successful processed. The payment for the extension is paid using the credit card associated with the individual customer at the time of the requestor added to the account balance of the corporate customer. A service fee of $70 applies only if the extension is made less than 6 hours before the return is due. Note that the rental period cannot be shortened.
The actual return date and time will be recorded in the rental detail when the car is returned.
Analyse and apply the data requirements to design a database for this new information system. Construct an ER model from the statements of requirements to represent the data model for Renta-Car.
In your ER model, provide the relevant
- entities, with entities name, relevant attributes and identifier,
- relationships with maximum and minimum cardinalities and relationship name
Question 2
A supermarket chain promotes items regularly. Several offers are made each week, oftentimes with overlapping periods, for example, weekly offer, weekend offer and special branch offer. When a purchase is made for an item on a purchase date, and there are overlapping offer periods for that purchase date, the lowest offer price is chosen as the purchase price.
An ItemOffer table from existing data was proposed:
ItemOffer (ItemNo, ItemDescription, OfferID, OfferStartDate, OfferEndDate, NonOfferPrice, OfferPrice, ItemStockLevel, ReceiptNo, purchaseDatetime, QtyPurchased)
a. Formulate and list the functional and multi-valued dependencies.
b. Draw a dependency graph and propose candidate key(s) for the table ItemOffer.
c. Normalise the table to BCNF and 4NF by applying the BCNF approach. Show how you apply the steps to arrive at the tables and at each iteration, explain whether each derived table is already in BCNF and 4NF (if applicable). Indicate also whether each MVD is subsumed.
d. Assemble your tables and constraints clearly. Give each table in this form:
TABLE-NAME(col-name1, col-name2, …, col-namen)
Indicate (PK) against the primary key and (FK) against the foreign keys, if any.
Referential integrity constraint(s), if any, together with the child table in this form:
TABLE-NAMEx.col-namex1 …col-namexn must exist in
TABLE-NAMEy.col-namey1…col-nameyn
Question 3
(a) Refer to Figure Q3(a)(i) for the data model for equipment loans
i.Derive the logical model from the data model in Figure Q3(a)(i).
ii. Refer to the description of columns in Table Q3, and implement the tables using the appropriate SQL statements. List the SQL statements in the order that they should be executed.
| Columns | Description |
| id | A string that is exactly 9 characters. The first is a letter which is either S or T and the last character is an alphabet. The characters in between are digits. |
| Name (customer) | A string that is exactly 50 characters. |
| address | A string that is at most 100 characters. |
| contact | A string that is exactly 8 digits. |
| date of birth | A date. A customer must be at least 21 years old in the year registered. |
| occupation | A string that is at most 30 characters. This attribute is optional. |
| equipment code | A string that is exactly 5 characters. |
| name (equipment) | A string that is at most 50 characters. |
| description | A string that is at most 255 characters. This attribute is optional. |
| RentalRatePerDay | A decimal number between 4 and 50, inclusive. |
| startDate, returnedDate | Both are date but returned date is optional. returnedDate must not be earlier than startDate. |
| report Id | An auto-generated value starting with 1 and an increment of 1 |
| damage type | A string that is at most 16 characters, and is either ‘Wear and tear’ or ‘Customer Damaged’ |
| Foreign key constraints | |
| Minimum cardinality between Customer and Loan is 1:N, M:O with delete and update cascaded. | |
| Minimum cardinality between Loan and Equipment is 1:N, M:O with delete disallowed and update cascaded. | |
| Minimum cardinality between Loan and DamageReport is 1:1, M:O with delete and update disallowed. | |
Table Q3
(iii)Write SQL statements to populate the tables with data. List the SQL statements in the order of execution.
b. Write SQL statements for each task:
i. To list the details of equipment (equipment code, name, description, rental rate per day) that cost above $10 to rent per day, with an equipment code that starts with ‘CAM’ and the equipment has been loaned to a customer who is at least 30 years old this year. Give the result in ascending order of the equipment name and rental rate per day. Use subquery.
ii. To list the number of equipment with the same first three letters (the start code, e.g., CAM, DIV) in the equipment code for equipment loaned or returned in the month of June. You should list the start code and the number of equipment types ONLY if there is more than 2 such pieces of equipment with the start code.
iii. Create a view named CustomerSummary with details of each customer, the number of equipment rented, the total amount of rental collected thus far and the number of damages caused by the customer and not due to wear and tear.
iv. Use the CustomerSummary view to find the average rental per loan for the customer with the most number of loans. Do not hardcode any value and do not assume that the loan table is non-empty or that there is one row of results as in general, there may be more than one customer with the most number of loans.
- Write a trigger to ensure that if a customer has caused more than 3 damages in the past12 months, there must be a period of one month from the date when the last damaged equipment was returned before he can make another loan. For example, if a customer wishes to loan an equipment on the 2 August 2021, but in the period August 2, 2020 to August 2, 2021, there are three damages caused by the customer. If the third damaged equipment was returned on or before the 1 July 2021, the customer can make the loan. If the third damaged equipment was returned after the 1 July 2021, the loan request should be rejected.
Furthermore, assume the third damaged equipment was returned in July 2021, he is limited to one loan per calendar month for a year from the date when the last damaged equipment was returned, that is, from August 2021 to July 2022, a maximum of one loan per month should be enforced. If a loan goes beyond a month, for example, the loan started on August 31 2021, and it was returned on September 1 2021, then consider that there is one loan for August 2021 and one loan for September 2021.
Include appropriate informational messages and error messages where appropriate such as successful request, unsuccessful due to a limit of one loan per month or unsuccessful due to a month that has not passed since the date of the last return of damaged equipment.
Question 4
a. Consider two transactions are using a joint account, j. The first transaction attempts to transfer money to b1 and the second transaction attempts to transfer money to b2.
Discuss whether the following event can happen.
- Lost update
- Deadlock
Use an appropriate example/scenario including the interleaving steps if an event can arise or provide a reason if it is not possible for an event to happen
b. Identify a role for a database for a banking application. Suggest ONE (1) permission that should be granted and one permission that should not be granted for the role you identified.
c. Given the scenarios described by the transaction log in Figure Q4(c), explain the database recovery procedure for transactions T1 to T4.
