Assessment 4
Assessment type: Group Project (Report, Demonstration and Presentation) - Problem Based Scenario to Design (ERD), Implement and Query a SQL Server Database Report 15% Presentation 5% Demonstration 5%.
Purpose: Students will demonstrate an understanding of the concepts they learnt in this unit by working a group to design, normalize, implement and query an SQL Server database according to a scenario provided to them. Students will answer questions pertaining to SQL Queries during their presentation and demonstrate by executing queries they might have written. This assessment contributes to learning outcomes d and e.
Value: 25% (Report 15%; Presentation 5%; Demonstration 5%)
Due Date: Report Week 11 Presentation in class Weeks 10 and 11
Assessment topic: Case Studies selected by students to be approved by tutor by week 8
Submission: Report and presentation file via Turnitin on Moodle; Presentation in class during tutorial
Task details: Students will be provided a case study by week 7 for this assessment. An ERD, database, scripts for creating tables, views, triggers, functions, stored procedures, insert, update, alter, delete and successful query results as required in the case study will be assessed according to the marking criteria
Students will deliver a 15 mins presentation and demonstration of tasks they performed as a group in class during tutorial and explain their understanding and the approach they took to work on the assignment. This will be arranged in Week 10.
Students must create a data model using crow’s foot notation in any tool of their choice, normalize the data from 1 NF to 3 NF and show each normalization with diagrams/tables and appropriate sample data to explain their understanding. Students will submit one group written report of 1,000 words detailing the tasks they carried out in the assignment. 3 to max 4 students can be in one group. This report will be due by the end of week 11.
The report requires a title page with student group members’ names and student id. Students are advised to use group assignment cover sheet.
A brief introduction to the assignment can then be followed by the initial ERD, the Normalized version and the SQL statements developed as required in the case study. Report must be well-written, have in-text and detailed APA style referencing and presented professionally, containing:
1. Title page
2. Table of Contents
3. Introduction
4. Appropriate use of headings within report
5. Overall structure, presentation and formatting.
All group members must remain present for the demonstration in tutorial and must be able to explain the scripts written for the tasks outlined in case study.
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.
Group Tasks
Task 1 ER
Diagram 5
marks
• 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
5 marks
• 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 10
marks
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: 5 marks
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.