Assignment

.docx

School

University of Melbourne *

*We aren’t endorsed by this school

Course

90520

Subject

Information Systems

Date

Feb 20, 2024

Type

docx

Pages

5

Uploaded by DeaconMetal12261

Report
Different branches can charge different rates for the same equipment. [2 points] Yes, different branches can charge different rates for the same equipment, the Branch Code and SKU are a part of a composite key with duration code in the Rental Rate table making it possible to have different set of combinations and, hence having unique combinations. Customers are required to make a booking before renting. They cannot simply show up at a branch without a booking and ask to rent a piece of equipment. [3] Rentals and the Booking table have a one-to-many required relationship, and Booking ID is a PK in Rental table making it a non-Null value, therefore customers need to have a booking before renting an equipment. However, the booking can also be done a minute prior since
there is not time constraint. Therefore, a customer can simply show up at a branch but has to first do a booking before renting an equipment. Additionally, customers and rentals are only related to each other via Booking therefore a customer must have a Booking ID before renting an equipment. When is the customer required to pay for the rental? Explain with reference to the ERD. [2 points] The Payment ID is present in Rental as a FK and Rental has an optional relationship with Payment table allowing it to have a NULL value. This means that Payment is to be done at the last. Explain why this makes sense, given the way RR operates? [1] RR is a rental company, and charge for the equipment depending on the duration it was rented for and hence the final amount can only be calculated once the equipment is returned. It gives customers the flexibility to hold equipments for a longer duration. Could the current database structure support a change in operations to get the customer to pre-pay at time of booking, then again any additional amount if they exceed the original booking duration? Explain with reference to the ERD. [2] No, the current database cannot support a change in operations to get the customers to pre-pay at time of booking. For this operation to be allowed following changes will have to be made- The Payment is only related to Rental, for this operation to be possible Booking table will need to have a Payment ID as a required FK. Additionally, the Rental and Payment have a one-to-one relation, which disables them to have multiple Payment IDs linked to the same Booking ID. Adding an amount to the Payment table will help in identifying how much amount needs to be deducted from the final amount at the end of the booking. However, there is a Duration Code as a required FK in Booking table which can help them know the duration the equipment is originally supposed to be rented for.
RR has always prioritized excellent service. However, recent customer satisfaction surveys indicate a decrease in satisfaction, specifically regarding customer interactions with staff. Customers typically interact with 2 different staff members for each rental; one who issues the equipment and one who accepts the returned equipment. The company aims to enhance accountability and trace issues back to individual staff by adding a new Employee entity to the database. To add the new entity to the current database (already shown in the ERD), we must establish the appropriate relationships with the other entities. Describe how to implement all the necessary relationships with the other entities. To address this issue, the Employee ID can be added as a required FK in Bookings table ID as Issuing Employee ID and Rental table as Receiving Employee ID. The employee table will have a one-to-many required relation between both meaning one Booking ID can be associated to a single Issuing/Receiving Employee ID however one Issuing/Receiving Employee ID can be associated to multiple Booking IDs. Both can also be added as a required FK to Customers table to understand which customer was dealt by which employee. Adding Employee ID as a required FK to Branch table can also help the business in understanding which employee is associated from which branch. This would help the business understand which branch’s hiring process needs their attention. The employee table will have a one-to-many required relation meaning one branch can have multiple employees, however one employee can only be associated a single branch. SELECT bk.Booking_ID, bk.Starting, bk.Duration_Code, cu.Name, cu.Phone, bd.SKU, eq.Name, bd.Quantity FROM Booking bk INNER JOIN Customer cu ON cu.Customer_ID = bk.Customer_ID INNER JOIN Booking_Detail bd ON bd.Booking_ID = bk.Booking_ID INNER JOIN Equipment eq ON eq.SKU = bd.SKU WHERE bk.Branch_Code = 'VIC3042' -- Change as needed AND bk.Starting >= DATEADD(hour, - 2 , CURRENT_TIMESTAMP) AND bk.Booking_ID NOT IN (SELECT Booking_ID from Rental) ORDER BY bk.Starting, bk.Booking_ID, bd.SKU Nature of the information returned: The following query is fetching branch specific booking details where the bookings were made 2 hours ago but are yet to be converted into Rentals. ONE likely use for the information:
Your preview ends here
Eager to read complete document? Join bartleby learn and gain access to the full version
  • Access to all documents
  • Unlimited textbook solutions
  • 24/7 expert homework help