Please explain in detailed, steps No hand drawing, please. Please use an editor
ID: 3726884 • Letter: P
Question
Please explain in detailed, steps
No hand drawing, please.
Please use an editor tool. Do not provide me with a handdrawing.
---------------------------------------------------------------------------------------------------------
----------------------------------------------------------------------------------------------------------
Map the E-R diagram to a relational schema. The E-R diagram is attached below.
indicate links from foreign key to primary key.(use Italic for foreigh key)
Draw the schema with all connected relations.
Salary Name Fname Address WORKS FOR Sex Location Mname Name DEPARTMENT EMPLOYEE Lname StartDate Bdate MANAGES CONTROLS Hours WORKS ON Supervisor Location SUPERVISION Name Number EPENDENTS OE DEPENDENT Name Relationship Sex BirchDateExplanation / Answer
For M-N relationship, Relationship is form as new table with PRIMARY KEYS of associated entities. In the relationship table, that primary keys called as FOREGIN KEYS. EX: EMP_WORKS_PROJ...(check below)
For 1-M or M-1 relationship, The primary key of 1 cardinality side entity's primary key added into M side of entity. Exmple: DEPARTMENT table, EMPLOYEE, etc...
For 1-1 relationship, We have a choice to add any entity's primary key add to alternative entity. but if we have one side total participation, we must add primary key to TOTAL participation side...
In Weak entities, We don't have Primary key, So It forms the primary key with depended entity primary key.. check EMP_DEPENDENT.
For Multi valued attribute, It also forms, separate table example DEPT_LOCATION
CREATE TABLE DEPARTMENT(
`Number` int NOT NULL PRIMARY KEY,
`Name` VARCHAR(100),
ESsn int,
StartDate DATE,
FOREIGN KEY(ESsn) REFERENCES EMPLOYEE(Ssn)
);
CREATE TABLE DEPT_LOCATION(
`Number` int,
`Location` VARCHAR(255),
PRIMARY KEY(`Number`, `Location`),
FOREIGN KEY(`Number`) REFERENCES DEPARTMENT(Number)
);
CREATE TABLE EMPLOYEE(
Ssn int NOT NULL PRIMARY KEY,
Fname VARCHAR(255),
Mname VARCHAR(255),
Lname VARCHAR(255),
Sex ENUM('M', 'F'),
`Address` VARCHAR(255),
Bdate DATE,
Salary DOUBLE
supervisor_ssn int,
DeptNumber int,
FOREIGN KEY(supervisor_ssn) REFERENCES EMPLOYEE(Ssn)
FOREIGN KEY(DeptNumber) REFERENCES EMPLOYEE(`Number`)
);
CREATE TABLE PROJECT(
`Number` int NOT NULL PRIMARY KEY,
`Name` VARCHAR(255) NOT NULL,
`Location` VARCHAR(255),
DNumber int,
FOREIGN KEY(DNumber) REFERENCES DEPARTMENT(DNumber)
);
CREATE TABLE EMP_WORKS_PROJ(
ESsn int,
PNumber int,
`Hours` int,
PRIMARY KEY(ESsn, PNumber, `Hours`);
FOREIGN KEY(ESsn) REFERENCES EMPLOYEE(Ssn),
FOREIGN KEY(PNumber) REFERENCES PROJECT(PNumber)
);
CREATE TABLE EMP_DEPENDENT(
ESsn int NOT NULL PRIMARY KEY,
`Name` VARCHAR(255) NOT NULL,
Sex ENUM('M', 'F'),
BirthDate DATE,
Relationship VARCHAR(20)
FOREIGN KEY(ESsn) REFERENCES EMPLOYEE(Ssn)
);