Database Management System: SQL Queries and Database Design

On successful completion of this module students will be able to

1.Understand fundamental concepts of database management systems such data independence, data models and database technologies

2.Be able to design and implement relational database systems

3.Be able to use manipulation and querying tools, including the use of Structured Query Language (SQL) for managing data held in a relational database management system (RDBMS)

4.Be able to test and document relational database systems

3.Be able to use manipulation and querying tools, including the use of Structured Query Language (SQL) for managing data held in a relational database management system (RDBMS)

4.Be able to test and document relational database systems

Attempt all the following tasks set in the assignment. Marks are awarded for producing a properly documented system that meets the requirements as specified below. Provide screen shots of working solution and sample output as an evidence.

Task 1:  Structure Query Language (SQL) [20%]

1.Explain the two Data Manipulation Language (DML) statements, Update and Delete, with correct syntax and appropriate examples of each statement. (10 marks)

2.Explain the two Data Control Language (DCL) statements, Grant and Revoke, with correct syntax and appropriate examples of each statement. (10 marks)

Consider the above ERD diagram and write the following SQL statements. Each SQL statement has 5 marks.

Note: First need to create above database/tables

Kindly see the SQL script in appendix to create the above database / tables and to insert the data)

1.Write an SQL statement to list all employees with their full names, gender and salary

2.Write an SQL statement to show the title of all employees and their department name.

3.Write an SQL statement to show the full names and gender who belong to department number 'd004'.

4.Write an SQL statement to show the all departments and their department’s managers.

5.Write an SQL statement to show a list of department’s managers who were hired after 1990

6.Write an SQL statement to change any employee’s date of birth. Assume the employee has just phoned in with his/her last name.

7.Write an SQL statement to increase salaries of all employees up to 5.5% who are working from 1997.

8.Write an SQL statement to delete employee’s record who belongs to department 'd004' and ID is 10003.

9.Create a database view to list full names of all employees their departments and salaries.

10. Create a database view to list all departments and their department’s managers, who were hired between 1990 and 2000.

Task 3: Database Testing

1.What is Database testing and why database testing is important?

2.Provide the screen shots of testing process on Employees database for the following constraints

·field size validation

·Null values

·Check constraints.

·referential integrity

Task 4: Referencing and Bibliography

You are required to follow the Harvard referencing system when citing others' work. An accompanying list of references must also be provided as part of your report.

Have you explained the two Data Control Language (DCL) statements e.g. Grant and Revoke

Have you included correct syntax of Grant and Revoke statements?

Have you included appropriate one examples of Grant and Revoke statements?

Have you developed correct syntax of all 10 SQL statements? Have your SQL queries provided correct output?

Have you included output screenshots for each statements?

Have you written about Database testing and why database testing is important?

Have you provided the screen shots of testing process on Employees database for the provided constraints?

Have you correctly cited your work by using Harvard references style?

1.All components of the assignment report must be word processed (hand written text or hand drawn diagrams are not acceptable), font size must be within the range of 12 point to 14 point including the headings, body text and any texts within diagrams.

2.Standard and commonly used fonts such as Times New Roman, Arial or Calibri should be used.

3.Your document must be aligned left or justified with line spacing of 1.5.