PLease Help Submission Guidelines Type your answers in a Word document and uploa

Question

PLease Help

Submission Guidelines Type your answers in a Word document and upload it in the blackboard. Assignment must be completed individually. Penalty for Late Submission Description of Homework For each question below, provide solution for DBMS of your choice (Oracle or Microsoft SQL Server) as shown in class. So, your answer must contain the following: SQL command Output from DBMS (Oracle or Microsoft SQL Server) 1. Create the database for following relational schema with Employee, Department, and Works tables: Employee(eid: integer, ename: string, age: integer, salary: real) Works(eid: integer, did: integer, hours: integer) Department (did: integer, dname: string, budget: real, managerid: integer) Define a table constraint on Department that will ensure that managerid is always positive and less than 1000. Insert the following records in the database: Get the sorted list of name and budget of each department in which employee Jones works. Get the name and id of youngest employee in Research department. Create a view that shows the eids and managerids in Sales department. Add a column called numHours to the Employee table. Use the UPDATE command to insert values into this new column to correspond to the current information in the Works table. Write a trigger that will increment the new field num Hours in the Employee table automatically whenever a record is inserted in the Works table. Write a trigger that will decrement the new field numHours in the Employee table.

Explanation / Answer

due to time constraint i am able to anser only first 5 parts

1) create table Employee

(

eid number,

ename varchar2(100),

age number,

salary number,

primary key(eid)

);

create table department

(

did number,

dname varchar2(100),

budget number,

managerid number,

primary key (did)

)

create table works (

eid number,

did number,

hours number,

foreign key (eid) refrences employee(eid),

foreign key (did) refrences department(did)

)

2) ALTER TABLE department ADD CHECK (managerid>=1 && managerid<=1000);

3) insert into employee (eid,ename, age, salary) values(101,'jones',23,60000);

insert into employee (eid,ename, age, salary) values(105,'Adamas',23,55000);

insert into employee (eid,ename, salary) values(110,'Rivera',43000);

insert into employee (eid,ename, salary) values(120,'Tanaka',60000);

insert into employee (eid,ename, age, salary) values(122,'Lee',57,45000);

insert into department (did,dname,budget,managerid) values(10, 'Accounting', 250000, 234)

insert into department (did,dname,budget,managerid) values(12, 'Research', 100000, 453)

insert into department (did,dname,managerid) values(20, 'Sales', 234)

insert into department (did,dname,budget,managerid) values(22, 'IT', 400000, 234)

insert into works(eid, did, hours) values(101, 10, 15)

insert into works(eid, did, hours) values(105, 10, 20)

insert into works(eid, did, hours) values(101, 12, 25)

insert into works(eid, did, hours) values(122, 20, 20)

insert into works(eid, did, hours) values(122, 22, 20)

insert into works(eid, did, hours) values(105, 20, 10)

insert into works(eid, did, hours) values(120, 12, 40)

insert into works(eid, did, hours) values(105, 12, 10)

4) select dname, budget from department where eid in (select eid from employee where ename ='Jones') order by dname

5) select top(1) ename , eid from employee where eid in (select eid from works whhere did in (select did from department where dname = 'Research')) order by age

Related Questions

Navigate

• Browse (All)
• Browse P
• Subjects

---

---

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