Start riding - Case Study - ER Diagram - Assignment Help

ASSIGNMENT TASK:

TASK:

Case Study for Lecture A

“Start riding” is a chain of bike repair shops in Sydney. Because of the prevailing COVID 19 pandemic and increasing competition, the owners want to track the repair jobs for bikes they repair, the items used for each repair job, the labor costs for each repair job, the mechanic performing each repair job, and the total cost of each repair job.
 

The process is as follows:
When customers bring their bikes in to be repaired, they need to pay a deposit on the repair job and are given a date to return and pick up their bikes after the mechanic evaluates the repair job. Mechanic then perform the repairs on the customers’ bikes, details the labor costs and the items used for each repair job. When customers return on the date advised, they must pay the total cost of the repair job less the deposit, collect a receipt for their payment, and pick up the repaired bikes using this payment receipt.

 

Task 1 ER Diagram 

•  Identify the entities and indicate minimum/maximum. Resolve Many to many relationships.
• Use UPPERCASE to name your entities and use Capitalisation (of first letter) to name attributes of entities. Underline all primary key attributes and mention all the foreign key attributes. Specify relationship verb.
•  State your assumptions clearly.
•  Create an ER diagram using crow’s foot notation in any tool of your choice. Hand drawn diagrams will not be accepted.

Task 2 Normalization 

•  List all the dependencies in a dependency diagram including partial and transitive.
•  Show the determinants and select primary key, foreign key.
•  Starting with unnormalized data, convert all the tables to 1 NF. Clearly outline the rules for a table to be in 1 NF and provide justification.
•  Identify partial dependency if any and resolve it.
•  Identify transitive dependency if any and resolve it.
• Make sure the tables are in BCNF. Explain the rule with tables as example.
•  Identify multivalued dependency if any and resolve it.
•  At each stage list the rules and provide explanation of why the tables are in 2, 3 or 4 NF.

Task 3 
1. Write table creation scripts for all the entities identified in ERD above and insert a few records in each table. 5 marks
2. Write the below queries:

a. Listing of jobs on hand, mechanics working on them and items required for the job arranged in descending order of completion date
b. List of customers who have paid more than $ 200 on the total job and a deposit of over $50
c. A summary of all jobs that require more than 2 items to fix.
d. Names of mechanics who are working on more than 2 jobs at the same time.

This  Case Study Assignment has been solved by our Case Study Experts at My Uni Paper. Our Assignment Writing Experts are efficient to provide a fresh solution to this question. We are serving more than 10000+ Students in Australia, UK & US by helping them to score HD in their academics.Our Experts are well trained to follow all marking rubrics & referencing style.

Be it a used or new solution, the quality of the work submitted by our assignment experts remains unhampered. You may continue to expect the same or even better quality with the used and new assignment solution files respectively. There’s one thing to be noticed that you could choose one between the two and acquire an HD either way. You could choose a new assignment solution file to get yourself an exclusive, plagiarism (with free Turnitin file), expert quality assignment or order an old solution file that was considered worthy of the highest distinction.