Assessment Information -

Overview

You are required to complete TWO assessments - Assessment 1 (Project) and Assessment 2 (Worksheets). You must successfully complete both the assessments to be deemed competent in this course.

As part of Assessment 1 (project), you are required to implement a fully-functional database with suitable data based on a design provided to you using both graphical tools (Stage 1) in MS Access / MS SQL Server and SQL statements (Stage 2) in MS SQL Server.

As part of Assessment 2 (worksheets), you are required to design and build a database based on a given case study. Also enter appropriate data in the database. Further, you also have to answer questions on server concepts.

The details with timelines for the above two assessments are shown below:

• The Assessment 1 (Project) is submitted in two stages.
o Stage 1 is an individual submission.
o Stage 2 is a team (2 or 3 students) submission.

• Assessment 2 (Worksheets) is submitted in two parts.
o (2a) : Database modelling - Individual - in class assessment
o (2b) : Server concepts.- Team (2 or 3 students) - take home submission

Context / Purpose: Assessment 1(Project)

Govinda pty limited is a car garage centre in Melbourne. They operate from many centres in Melbourne.

The business stores all their operational data (customer's details, car repairs, employee details, salaries, payment.....) in a fairly complex database called "Govinda".

You are working as an IT specialist in the organisation. In your current role, based on your manager's request, you are required to enter, extract and manipulate data in Govinda by designing & executing SQL queries.

ASSESSMENT 1 (Project) Stage 1 - Individual submission -
Student will develop skills in

• reading data using SQL SELECT statement to match client requirements.
• trouble shooting SQL syntax errors
• problem solving.
• planning and organisational skills to analyse and organise data.
• programming.

Instruction
• Build the above (MS Access Relationships ) model of Govinda in MS Access / MS SQL Server
o Carefully choose appropriate data type for each attribute.
o Choose PK & FK shown in the above snapshot.
• Populate tables with appropriate data, about 10 records in each table.

Design SQL Queries:

1. Design a query to display details of customers from "Albert Park". Sort data according to the alphabetical order of customer's last name.

2. Write a query to show details of EmployeeNo, FirstName, Office phone and address where first name starts with letter "P".

3. Add a field called gender to Customer table. Enter data for this field. Design a query to find out details of CustomerNo, first name, last name who have paid amount more than the average amount paid by all male customers.

4. The Garage centre is interested in knowing details of Toyota cars of type "sedan" which have come for more than three times for servicing. Design a query to display the requested details.

5. At least one more query will be given at the time of assessment for Stage 1 and the student needs to answer this question under TEST conditions during class time.