Consider the following database. Employee has a unique eid, name, age and salary

Question

Consider the following database. Employee 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 pct_time in Works table is the percentage of time an employee works for a department. The salary of the employees is fixed and is not based on pct_time or the deparments they work in.

Emp (eid: INT(11), ename: VARCHAR(50), age: INT(11), salary: FLOAT)

Works (eid: INT(11), did: INT(11), pct_time: INT(11))

Dept (did: INT(11), dname: VARCHAR(50, managerid: INT(11))

Constraints

-eid is primary key for Emp table and cannot be NULL

-did is primary key for Dept table and cannot be NULL

-eid and did are foreign keys referencing to Emp and Dept tables respectively in Works table. combination of eid and did is the primary key or works table.

-managerid is foreign key referencing to Emp table and can have NULL values if the department doesn’t have a manager assigned.

Sample values.

Emp

eid

ename

age

salary

1001

Georgi Facello

34

45000.00

1002

Parto Bamford

28

34000.00

1003

Christian Koblick

42

55000.00

1004

Saniya Peac

24

28000.00

Dept

did

dname

managerid

d001

Marketing

1003

d002

Finance

1002

d003

Information Security

NULL

Works

eid

did

pct_time

1001

d001

60

1002

d002

100

1003

d001

100

1001

d003

40

1004

d003

50

1004

d002

50

1. Write the insert statements to enter the above sample values to the database. Add 4 more employees, 2 more departments and assign the 4 new employees to departments with percentage time. Submit all insert statements.

2.Alter the table Emp to change the column age to “dob” with data type DATE and update the values based on their current age (should be approximate). Submit the alter and update table statement.

eid

ename

age

salary

1001

Georgi Facello

34

45000.00

1002

Parto Bamford

28

34000.00

1003

Christian Koblick

42

55000.00

1004

Saniya Peac

24

28000.00

Explanation / Answer

Answer 1:

Emp Table:

Insert into Emp values (1005,’Thomas Riedel’,35,400000.00);

Insert into Emp values (1006,’Eric van’,38,300000.00);

Insert into Emp values (1007,’Rupen Hawk’,45,50000.00);

Insert into Emp values (1008,’Sabine Gompel’,29,40000.00);

Dept Table:

Insert into Dept values (‘d004’,’HR’,1005);

Insert into Dept (did,dname) values (‘d005’,’Service’);

Works Table:

Insert into Works values (1005,’d004’,10);

Insert into Works values (1006,’d004’,20);

Insert into Works values (1007,’d005’,50);

Insert into Works values (1008,’d005’,70);

Answer 2:

ALTER TABLE Emp
RENAME COLUMN "age" TO "dob";

UPDATE Emp SET dob = ‘1972-01-01’ WHERE eid=1001;

UPDATE Emp SET dob = ‘1992-05-02’ WHERE eid=1002;

UPDATE Emp SET dob = ‘1965-12-15’ WHERE eid=1003;

UPDATE Emp SET dob = ‘1995-10-12’ WHERE eid=1004;

Note: DATE type field has a format yyyy-mm-dd hence updated the record in that manner.

I have answered your question. Please do not forget to give a positive feedback to the answer. Thank you.

Related Questions

Navigate

• Browse (All)
• Browse C
• Subjects

---

---

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