Question

A relational database is to be designed for a medium sized Company named Global Support Solutions (GSS) 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 long-term basis, i.e. there are 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 departments.

•  An employee may possess a number of skills.

•  Every manager 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.

•  Each project has specific title, deadline, and project_value

•  Each project can have any of the following statuses: Ongoing, Terminated, Completed, And Closed.

1.An Entity – Relationship Diagram (ERD) - Map the entities with appropriate relationships

2.Database implementation:

1. a) Create database

2. b) Create the tables

3. c) Properly establish the (PK,FK, UK or any other constraints)

4. d) Create additional table if required.

5. e) Create required attributes

Answer 1

1. ER diagram

Enthies Employee Depactmewt Raject ocatin Enane Eid Atkees)No Did Name Works In eptuent Eypdryee Pesigust Woks Paoject Status Titic Peglect Value Peadthie Cinployge -A Peojeet nust Rave afleaot on Pgiect tn a departnient pasticipates - Ai leoot

2.  Database implementation:

queries

a) To create database

create database company;

use company;

b) To create all the required table

create table employee_table(eid int primary key, ename varchar(200), address varchar(200), ph_no int, designation varchar(200));

create table skill_table(eid int, skill varchar(200), foreign key(eid) references employee_table(eid));

create table department_table(did int primary key, name varchar(200), location varchar(200), office_no int);

create table worksin_table(eid int, did int, foreign key(eid) references employee_tableeid),foreign key(did) references department_table(did));

create table project_table(title varchar(200) primary key, deadline date, project_value int, status varchar(200));

create table workson_table(eid int, did int, foreign key(eid) references employee_table(did), foreign key(did) references department_table(did));

create table participate_table(did int, title varchar(200), foreign key(did) references department_table(did), foreign key(title) references project_table(title));

ORACLE Database Express Edition Hone SOLSOL Commants aplay Save Hun ab re i. (20 deaigration rsbAc(0y Houla LauSI Ua Sued L Irioy alte teralal. 03а явсото. Autocammit 1piy Run Save . tahla r/206). focetan keyid) reterenres pl tahie(eay Resudts Feplsin hescibe Saved SQ Hiry Table sreatad. Save Dipy 10 srasts taale departact tabla Hun aftice n AC(2 Results Explain Describe Saved 5QL Hisory 5OL 50LConnards Save create tabla Rckain tebiet sid int LErda Ueaula 3e SL 1adur Hulla gile sreated. D.c0scconds H SQlS Cannds tocommit Dsolay 1 Savs TSTte varch ker Proje Fus Explain Decrb 1awe yaL History Teble create a05 secsnds Hane NOSQL Com Aunocommt Display 10 ' Save Ни e lemau toble furvign kaysi) rfern plupos_teblet Hsu Eape escb Sevd SuL Halory Таble ceeated. S seconde tle varchae) foceier kealrtie) cetecanca orodectae a Sa SUL Halure Naala Lpla latle created. p.01 saconds