Use myphpadmin. Consider the following database. Emplayee has a unique eid, name

Question

Use myphpadmin.

Consider the following database. Emplayee has a unique eid, name, age and salary. Department has a unique did, name and an employee as a manager. A department may or may not have a manager assigned. An employee can work for multiple departments. The pctine in Works table is the percentage of time an employee works for a department. The salary of the emplayees is fixed and is not based on p tine or the deaacmeats they work in. Ema leid INTI11, eoADA: VARCHAR(50), age: INT(11), salary: FLOAT works leit 11), did: INT(11), geta time INT[11)) De (did: NT111), duang: VARCHAR(50, uauagesid: INTI 11)) Constraints . eid is primary keyfor Ee table and cannot be NULL e did is primary key for Dent table and cannot be NULL . ei and did are foreign keys referencing to Eand DeRt tables respectively in Works table. combination of eit and did is the primary key or works table. naoagesid is foreign key referencing to guatable and have a manager assigned n have NULL values if the department doesn't · Sample values. salary 45000.00 34000.00 28 42 24 Pacte Bamford 1003 Christian 55000.0 28000.00 d001 d002 d003 Marketin 003 formation Setu NULL d001 001 003 003 d002 50 100 100 40 50 50 1001 Create a new database named eped Write the SQL statements required to create the above relations, including appropriate versions of all primary and foreign key integrity constraints. Submit all create table statements. Write the insert statements to enter the above sample values to the database. Add 4 more emplayees, 2 more departments and assign the 4 new employees to departments with percentage time. Submit all insert Alter the table Eme to change the column age to "dek" with data type DATE and update the values based on their current age (should be approximate). Submit the alter and update table statement. (20

Explanation / Answer

DDL statememt to create all the given 3 tables Emp, Dept and Works. Primary key and foreign key are mentioned in the tables.

CREATE TABLE Emp

(

eid INT(11),

ename VARCHAR(50),

age INT(11),

salary FLOAT,

PRIMARY KEY (eid)

);

CREATE TABLE Dept

(

did INT(11),

dname VARCHAR(50),

managerid INT(11),

PRIMARY KEY (did),

FOREIGN KEY (managerid) REFERENCES Emp(eid)

);

CREATE TABLE Works

(

eid INT(11),

did INT(11),

nct_time INT(11),

PRIMARY KEY (eid, did),

FOREIGN KEY (eid) REFERENCES Emp(eid),

FOREIGN KEY (did) REFERENCES Dept(did)

);

// Inserting data elements to the tables Emp, Dept and works.

INSERT INTO Emp VALUES (1001, 'Georgi Facello', 34, 45000.00);

INSERT INTO Emp VALUES (1002, 'Parto Bamford', 28, 34000.00);

INSERT INTO Dept VALUES (001, 'Marketing', 1002);

INSERT INTO Dept VALUES (002, 'Finance', 1002);

INSERT INTO Works VALUES (1001, 001, 60);

INSERT INTO Works VALUES (1002, 002, 100);

// Inserting 4 new employees to the table emp

INSERT INTO Emp VALUES (1005, 'Jack Tan', 32, 32000.00);

INSERT INTO Emp VALUES (1006, 'George S', 45, 70000.00);

INSERT INTO Emp VALUES (1007, 'Smith L', 38, 50000.00);

INSERT INTO Emp VALUES (1008, 'Mewa Lal', 35, 30000.00);

// Inserting 2 new depart,ents to the Dept table

INSERT INTO Dept VALUES (004, 'Operations' 1005);

INSERT INTO Dept VALUES (005, 'Legal', 1007);

// Assigning dept to employees.

INSERT INTO Works VALUES (1005, 004, 60);

INSERT INTO Works VALUES (1006, 002, 40);

INSERT INTO Works VALUES (1007, 005, 50);

INSERT INTO Works VALUES (1008, 005, 50);

// Alter table statement that alter the Age field to DOB.

ALTER TABLE Emp

CHANGE age DOB DATE;

// Updating table values for column DOB based on the given age.

UPDATE Emp

SET DOB = '1984-02-21'

WHERE eid = 1001;

UPDATE Emp

SET DOB = '1989-04-28'

WHERE eid = 1002

Related Questions

Navigate

• Browse (All)
• Browse U
• Subjects

---

---

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.