Question

consider a scenario where a company has to automate its systems and wants to store the details of its employees with their empno, name, emailid, salary, DoB ,age ,gender,and address to be stored .Emailid is the primary key. every employee works on multiple projects (pno ,pname, ) and one project may involve many employees who belong to various departments ( dname,loc ) . each employee many have dependents ( depno, depname,relationship) . the depno ia unique among the dependants of samw employee but duplicate across the employee . draw the ER diagram with appropriate notations, relationships,with candidates and participation constraints. (note : make no new assumptions regarding the design and strictly adhere to the given details only )

Answer 1

CREATE TABLE DEPARTMENT (
	Dname varchar(25) not null,
	Dnumber int not null,
	Mgr_ssn char(9) not null,
	Mgr_start_date date,
	primary key (dnumber),
	UNIQUE (dname)
	);
	CREATE TABLE EMPLOYEE (
	Fname varchar(15) not null,
	Minit varchar(1),
	Lname varchar(15) not null,
	Ssn char(9),
	Bdate date,
	Address varchar(50),
	Sex char,
	Salary decimal(10,2),
	Super_ssn char(9),
	Dno int,
	primary key (ssn),
	foreign key (dno) references DEPARTMENT(dnumber)
	);
	CREATE TABLE DEPENDENT (
	Essn char(9),
	Dependent_name varchar(15),
	Sex char,
	Bdate date,
	Relationship varchar(8),
	primary key (essn,dependent_name),
	foreign key (essn) references EMPLOYEE(ssn)
	);
	CREATE TABLE DEPT_LOCATIONS (
	Dnumber int,
	Dlocation varchar(15),
	primary key (dnumber,dlocation),
	foreign key (dnumber) references DEPARTMENT(dnumber)
	);
	CREATE TABLE PROJECT (
	Pname varchar(25) not null,
	Pnumber int,
	Plocation varchar(15),
	Dnum int not null,
	primary key (pnumber),
	unique (pname),
	foreign key (dnum) references DEPARTMENT(dnumber)
	);
	CREATE TABLE WORKS_ON (
	Essn char(9),
	Pno int,
	Hours decimal(4,1),
	primary key (essn,pno),
	foreign key (essn) references EMPLOYEE(ssn),
	foreign key (pno) references PROJECT(pnumber)
	);