Module on Database Management Systems: Concepts, Design, Implementation, and Testing

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

5.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)

6.Be able to test and document relational database systems

Attempt all the following tasks 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 Control Language (DCL) statements, Grant, and Revoke with the   correct syntax and appropriate examples of each statement.

2.Explain the term ACID in the relational database with appropriate examples

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 above database/tables

(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 who are working in the Sales department for which the hire date is between 1995 - 2008

2.Write an SQL statement to show all titles of employees who are getting more than £20000 salary and for which the hire date was after 1985

3.Write an SQL statement to show the full names, salaries and gender for those in the HR department.

4.Write an SQL statement to show that all employees names, department names and their departments’ managers.

5.Write an SQL statement to show a list of employees who were hired between 1980 and 1995 and whose first name starts with A or S

6.Write an SQL statement to change the employee’s title who works in the finance department and whose first name starts with ‘S’.

7.Write an SQL statement to increase the salaries of all employees up to 10 % for those who have been working since 1997 and work in the Marketing department.

8.Write an SQL statement to delete employees’ record in the sales department and whose last name starts with A or S

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

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

Task 3: Database Testing [25%]

1.What is Database Testing and how Jet Airline’s database can be tested?

2.Develop the Jet Airline’s database testing template and provide screen shots of the testing process of the following database constraints.

• Field size validation
• Null values
• Referential integrity (Primary/Foreign Key)

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.

You need to state the word count at the end of their assignment. 10% over the stated word count is permitted without penalty.

If students go beyond this, then there is a penalty of 5 marks for every additional 10% beyond the word count with a maximum of a 15 mark penalty reduction.

There is no specific penalty for submitting a piece which is below the word count, but please note that shorter submissions are likely to attract poorer grades, particularly where they lack the necessary depth of analysis.

How do you calculate the word count?

The word count includes the Abstract or Executive Summary and all in-text citations. The word count does not include the Bibliography and Appendices.

Please note that Appendices should only include supplementary information, not information critical to your work.

Marking Criteria:

Structure Query Language (SQL)

Have you explained the two Data Control Language (DCL) statements grant and revoke?

Have you explained the term ACID in the relational database with appropriate examples

Data Manipulation

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

Have you included output screenshots for each statement?

Database Testing

Have you written about Database Testing and why it is important?

Have you provided the screen shots of testing process on the employees’ database for the provided constraints?=

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