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

Uploaded by DeaconMetal12261

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

This gives the branch an opportunity to follow up with the customers that haven’t showed up yet and keep track of them. This allows them to function efficiently by getting information about the upcoming rentals and preparing the equipment accordingly. WITH bd AS ( SELECT *, LEFT ( SKU, CHARINDEX ( '-' , SKU) -1 ) as Category FROM Booking_Detail ) SELECT bd.Category, bk.Branch_Code, sum (bd.Quantity) as Frequency FROM bd INNER JOIN Booking bk ON bk.Booking_ID = bd.Booking_ID INNER JOIN Rental re ON re.Booking_ID = bk.Booking_ID WHERE DATEDIFF ( week , re.Ended, CURRENT_TIMESTAMP ) BETWEEN 1 AND 4 GROUP BY bd.Category, bk.Branch_Code ORDER BY bd.Category, Frequency; Nature of the information returned: The following query is fetching details regarding the frequency of equipments been rented for the past 4 weeks. ONE likely use for the information: This will help business in identifying the outlier equipments if any for each Branch and help the company address any issues regarding low demand of a category from a particular branch. (c) [2] You've just been informed that the SQL query in (b) above used to work, but has now started to produce the following error instead: Invalid length parameter passed to the LEFT or SUBSTRING function. What data quality issue has broken the query; why did it used to work, but now produces an error? The following error indicates that there has been a data quality issue related to the CHARINDEX function. The function requires all SKUs to have ‘-‘ in it which could be false for the recent data, hence leading to this error.

Good morning, I hope you're well. I wanted to inquire if it would be possible for me to miss tomorrow's induction due to a prior commitment. I have a summer course consultation and exam scheduled that I must attend. Additionally, could you please provide me with a schedule for the induction? This would allow me to prioritize my commitments accordingly. I'll do my utmost to attend, but I wanted to ensure you're aware of my circumstances. Thank you for your understanding.

Assignment

Related Documents