Task instructions

Overview

A workable database structure will be provided for Assessment 1 (Project)

Assessment 1 ( Project) Stage 1:

- The database structure is implemented using graphical tools with appropriate data either in MS Access or in MS SQL Server.
- You will design and execute simple SQL queries to extract data from this database.
- You will be asked to demonstrate, answer question about, and modify queries during feedback time.

Stage 2:
- 2 or 3 students will be expected to form a team.
- The database structure is implemented using SQL statements in MS SQL Server. Further tables are populated with appropriate data using SQL statements.
- You will design and execute complex SQL Queries to extract data from this database.
- You will be asked to demonstrate, answer question about, and modify queries during feedback time.
- Also answer questions on server concepts.

ASSESSMENT 1 (Project) Stage 2:

Context / Purpose:

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

The business stores all their 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 extract details from Govinda by designing & executing SQL queries.

A snapshot of "Govinda" database structure is shown below:

ASSESSMENT 1 (Project) Stage 2

Aim / Purpose:
Student will develop skills in
- using SQL to create database structures, and store, retrieve and manipulate data in a relational database.
- designing a variety of SQL queries to match client requirements.
- understanding typical warnings and errors.
- troubleshooting SQL syntax errors.
- problem-solving.
- planning and organisational skills to analyse and organise data.
- numeracy skills to use arithmetical operators and mathematical functions
- programming skills.
- creating and using views and stored procedures.
- server concepts.

Instruction:
o Build the above model (Govinda structure) in MS SQL server (Refer question 1 below under design SQL queries:).
o Choose appropriate data types and sizes for each attribute carefully.
o Choose PK and FK as shown in the above structure.
o Populate tables with appropriate data, about 10 records in each table. So that when queries are executed, enough records are displayed.

- Design SQL Queries: 1 - 14

1. Use CREATE statement to build EMPLOYEE, CUSTOMER, CAR, SERVICE tables in MS SQL Server. Rest of the tables can be built by using GUI tools.

2. Use INSERT statement to enter sufficient number (minimum 10) of records into these tables.

3. "Alter" EMPLOYEE table to add a field called Mobile. Choose appropriate data type and size.

4. Design a SELECT statement to display EmployeeNo, last name and date of birth. The date of birth must be in the year 1990. The employee last name should start with letter "R". All duplicate records must be eliminated.

5. The Management needs listing of employees by last name and first name. The first name should be displayed in capital letters. The entire name should be joined together to display in a single column with a heading "Staff Name". The last and first name should be separated by a comma. The name should start with last name.

6. Employees working in finance department need restricted access to employee information. Create a view named StaffView that includes only EmployeeNo, First Name , Last Name, office phone. The view should have more meaningful names (for the fields) than given in the table / s. The first and last names should be combined to display (Eg: Krsna, RAMA) as EmployeeName & CAST as CHAR(20) .
a. Write a select statement on the view so that employee numbers more than 40 are displayed.

7. Drop StaffView from the database.

8. The management is interested in knowing when an employee is reaching 60 years so that they can send them additional info for certain courses. Add a field called employee gender (if missing) with data. Display details of EmployeeNo, Employee First Name and employee last name, employee gender.

9. Add a field called AmountDue to CUSTOMER table. Populate this field with appropriate data. Design a query to display details of amount due by a Customer. No cents should be displayed. (Hint: ROUND())

10. How many times an employee has worked in shifts? Show employee number, first name, number of times worked. Display details only when an employee has worked more than three times. Enter appropriate data to show results.

11. Add "Gender" field to CUSTOMER table. Display details of CustomerNo, first name, last name, suburb, Amountpaid for all female customers. Use RIGHT join to display details of all customers whether they made payment or not.

12. Design a query to display details of top 5% customers who paid less than the average amount paid by all male customers put-together.

13. The garage management has agreed to raise salary of all employees by 10%. Write a procedure named AmountIncrease with a parameter called percent_increase that increases the salary.

14. Write a trigger to check that the salary of an employee does not increase more than 10% of the current salary. Use EMPLOYEE table.
- Questions on Server concepts:

15. List major advantages of the client / server architecture compared with other computing approaches.

16. Describe advantages and disadvantages of database servers.

17. Illustrate with a diagram the relationships among the different client and server components. Also briefly explain them.

18. Explain briefly with a diagram the advantages / disadvantages of two-tier over three tier system.

Attachment:- Project.rar