The following is for relational database subject, thank you for help in advanced
ID: 3730182 • Letter: T
Question
The following is for relational database subject, thank you for help in advanced!
Quiz Ch05.1 - Company-Employee Database For this quiz, your task is to use MySQL's workbench to create a database to keep track of Employees that work for a specific Company (Note, according to the diagram below, an Employee is only able to work for 1 Company) Exercises 1. 2. 3. Using MySQL's workbench, please create the EER diagram and export your diagram as a PNG image for submission Next "Forward Engineer" your diagram to create the sql script (name it CompanyEmployeeSetup.sql) for submission Then create another script with your (open query tab and "Save Script As..." with your insert statement to populate your database with data (at least 10 - 15 entries) Please ensure that some have the same last names, year, etc... Submit this as a sql script named StudentInserts.sql EmployeelD EMPLOYEE COMPANY umber gDate us nesS me me Files to submit: CompanyEmployee.png (image file from EER diagram) CompanyEmployeeSetup.sq CompanyEmloyeeInserts.sqlExplanation / Answer
// The given ER Diagram when converted to relational schema yields 5 tables. Each table has a primary key and associated foreign key. The create table statment for creating all the 5 tables is given below.
CREATE TABLE EMPLOYEE
(
EmployeeID INT,
EmployeePhone VARCHAR(15),
EmployeeSalary DECIMAL(7,2),
EmpFirstName VARCHAR(50),
EmpLastName VARCHAR(50),
PRIMARY KEY (EmployeeID)
);
CREATE TABLE COMPANY
(
CompanyID INT,
CompanyName VARCHAR(50),
CompanyFoundingDate DATE,
YearsInBusiness INT,
PRIMARY KEY (CompanyID)
);
CREATE TABLE SKILLS
(
SkillID INT,
SkillName VARCHAR(100),
PRIMARY KEY (SkillID)
);
CREATE TABLE EMPSKILLS
(
EmployeeID INT,
SkillID INT,
PRIMARY KEY (EmployeeID, SkillID),
FOREIGN KEY (EmployeeID) REFERENCES EMPLOYEE (EmployeeID),
FOREIGN KEY (SkillID) REFERENCES SKILLS (SkillID)
);
CREATE TABLE WORKSFOR
(
EmployeeID INT,
CompanyID INT,
PRIMARY KEY (EmployeeID, CompanyID),
FOREIGN KEY (EmployeeID) REFERENCES EMPLOYEE (EmployeeID),
FOREIGN KEY (CompanyID) REFERENCES COMPANY (CompanyID)
);
// Inserting values to the created tables
INSERT INTO EMPLOYEE VALUES (1, '9898989898', 2400.00, 'Jack', 'Tan');
INSERT INTO EMPLOYEE VALUES (2, '5558989898', 3400.00, 'Glenn', 'Yuhico');
INSERT INTO COMPANY VALUES (1, 'Max Pvt. Ltd.', '2018-03-15', 3);
INSERT INTO SKILLS VALUES (1, 'JAVA Expert');
INSERT INTO EMPSKILLS VALUES (1,1);
INSERT INTO WORKSFOR VALUES (1, 1);
Related Questions
Navigate
Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.