Reference no: EM132634266
ICT200 Database Design and Development - Kings Own Institute
Case Study
"Start riding" is achain of bike repair shopsin 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.
Group Tasks
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.
e. Create a view which shows a list of repeat customers (those who have availed services more than once). This view should display the name of mechanic, items used and total cost for each of the jobs for these customers.
Individual Task
Task 4
Video Presentation
• Record a 5-minute video with your face clearly visible explaining all the tasks you performed above. Upload it on youtube as an unclassified video and share the links for all group members in the report.