Reference no: EM132998320

HS2021 Database Design and Use

Objective(s)

This assessment item relates to the unit learning outcomes as in the unit descriptor. This assessment is designed to reinforce the lecture material and give students practice at applying Database design techniques, as well as providing a relational schema using DDL statements and relational DML statements to demonstrate access of the Database to be built.

Instructions

Consider the following Case Study Scenario for 'Holmes Software Engineering'

A relational database is to be designed for a medium sized Company dealing with industrial applications of computers. The Company delivers various products to its customers ranging from a single application program through to complete installation of hardware with customized software. The Company employs various experts, consultants and supporting staff. All personnel are employed on longterm basis, i.e. there is no short- term or temporary staff. Although the Company is somehow structured for administrative purposes (that is, it is divided into departments headed by department managers) all projects are carried out in an inter- disciplinary way. For each project a project team is selected, grouping employees from different departments, and a Project Manager (also an employee of the Company) is appointed who is entirely and exclusively responsible for the control of the project, quite independently of the Company's hierarchy. The following is a brief statement of some facts and policies adopted by the Company.

• Each employee works in some department.
• An employee may possess a number of skills
• Every manager (including the MD) is an employee
• A department may participate in none/one/many projects.
• At least one department participates in a project.
• An employee may be engaged in none/one/many projects
• Project teams consist of at least one member.

For the above mini-case study, you are to analyze the data requirements and develop a set of relational tables to suit the above case study.

• Draw and ER diagram depicting the relational database.
• Make sure the tables are normalized.
• Build the Database in PostgreSQL (storing your DDL statements in a script)
• Add test data to the PostgreSQL database (again store these DML statements in a script), one of the employees you add must have an employee ID the same as your own Student ID. This employee must also be part of a team working on some project
• Develop a set SQL queries to query this database (there should be at least 4 queries)
- at least 2 queries should joint multiple tables (1 should join at least 3 tables)
- at least one query should show the employee with the ID the same as your student ID

For each query, you should show the output from executing the Query in PostgreSQL by cutting and pasting the PostgreSQL output into your assignment just under the Query.

Attachment:- Data and design.rar