Data Modelling SQL Language Assignment 2

Module title : Data Modeling & SQL Language

Module Learning outcomes : On successful completion of this module students will be able to

Understand fundamental concepts of database management systems such data independence, data models and database technologies Be able to design and implement relational database systems 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) Be able to test and document relational database systems

Learning outcomes assessed within this piece of work as agreed at the programme level meeting :

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) Be able to test and document relational database systems

Type of assessment : Individual Report Database Development (1500 words)

Weighting : 40% of the total module marks

Submission deadline : The Database Development report should be submitted by as a word file which should contain screen shots of the SQL queries and sample output (see the deliverables required for this assignment)

Assignment 2

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%]
Describe two Data Definition Language (DDL) statements (Create and Alter) with correct syntax and appropriate examples. (10 marks) Explain two Data Control Language (DCL) statements (Grant and Revoke) with correct syntax and appropriate examples (10 marks)

Task 2: Data Manipulation [50%]

Consider the above ERD diagram and write the following SQL statements. Each SQL statement has 5 marks.
Note : First you need to create the database/tables shown above using phpMyAdmin
Please use the SQL script provided to create the above database / tables and to insert the data.

Create a SQL statement to list all managers and their titles. Create a SQL statement to show the salary of all employees and their department name. Create a SQL statement to show the hire date and birth date who belongs to HR department Create a SQL statement to show the all departments and their department’s managers. Create a SQL statement to show a list of HR’s employees who were hired after 1986 Create a SQL statement to increase any employee’s salary up to 2%.Assume the employee has just phoned in with his/her last name. Create a SQL statement to delete employee’s record who belongs to marketing department and name start with A Create a database view to list the full names of all departments’ managers, and their salaries. Create a database view to list all departments and their department’s managers, who were hired between 1980 and 1990. Create a SQL statement to increase salaries of all department’s managers up to 10% who are working since 1990.

Task 3: Database Testing [25%]

Describe the Database testing and state why database testing is important Provide the testing template and screen shots of the testing process on the Employees database for the following constraints:
  field size validation
  Null values
  Check constraints
  referential integrity

Task 4: Referencing and Bibliography [5%]

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.

To Continue Click Here > Data-Modelling-SQL-Language-Assignment-2.pdf