Question

1. {90 points} Your task is to design a 'Company database capable of storing information about EM- PLOYEE(S), DEPARTMENT(s), and PROJECT(s). Knowing that: We store each employee's name composite attribute - {10 points}), social security number, address, salary, and telephone number. An employee is assigned to one department, but may work on several projects which are not necessarily controlled by the same department. We keep track of the number of hours that an employee works on each project - {10 points} • Each department has a unique name, a unique number, a single location, and a particular employee who manages - {10 points} the department. We keep track of the start date - {10 points} when that employee began managing the department • A department controls a number of projects, each of which has a unique name, a unique number and several locations - {10 points) (a) {5 + 5 points} Give the ER diagram and schema (b) {5 + 5 points} Which type of relationships do you have? Discuss both the cardinality and the partici- pation constraints (c) {10 points} Give the corresponding relational schema; and (d) {10 points} Give a snapshot containing at least 5 entrances better if mean full). 2. {10 points} Primary, candidate and foreign keys: what are them? Give examples (refering to answer 1)

Answer 1

Ans-1)I have attached the ER diagramcreate table employee(empId int primary key,social_sec_no varchar(40) unique,address varchar(40), salary numeric ,telephone numeric,deptId int references department(deptId));

Department Admin Adminid [PK] + userid DepartmentId[PK] departmentname location startdate mangerid manages password has Employee Projects ProjectID [PK] EmployeeID[PK] works on Employeeld [PK] Departmentld (FK] hoursworked address social_sec_no salary telephone_no location projectname

create table department(deptId int primary key,deptname varchar(40),location varchar(40) ,managerid int,startdate date );

create table project(projectid int primary key,project name varchar(40),project_location varchar(40),empId int references employee(empId));

We have one to many relationship.

Ans-2 )Primary key are the unique identifier which are used to identify a record uniquely in a table .For example we have empId in employee as primary key.Similary deptId in department and projected in project.

Foregin key are the primary keys of one table when mapped to another table are called foreign keys.The are used to map records of one table with other uniquely.For ex deptId in employee table is primary key of department table but is a foreign key in employee table.Similarly , empId is primary key of employee table but foreign key in project table.