Question
Consider the following schema:
Employees(eid: integer, ename: string, age: integer, salary:real)
Works(eid: integer, did: integer, since: date)
Departments(did: integer, dname: string, budget: real, managerid:integer)
1) Identify all foreign key constraints that should exist in thisschema
2) Write SQL statements to create all of the above relations. Makesure to include any applicable primary key constraints and foreignkey constraints.
3) Suppose every department must have a manager. Write the SQLstatement to create the Departments relation with thisconstraint.
4) Write a SQL statement to add a new employee (you can choose theattribute values you want).
5) Write a SQL statement to give a 10% raise to every employee whois 40 years old
6) Write a SQL statement to delete the “Toy”department. What constraint(s) can you add to the above relationsthat can possibly prevent the deletion from occurring?
Explanation / Answer
Dear, Here the given relations are, Employees( eid, ename, age, salary) Works( eid, did, since ) Departments( did, dname, budjet,managerid ) 1) Here "eid", "did" are primary keys inEmployees and Departments relations. These fields acts as ForeignKeys in Works relation. 2) CREATE table Employees ( eid number(5) primarykey, enamevarchar2(15), agenumber(3), salarynumber(10,2), did number(5)references Departments(did)); CREATE table Works ( eid number(5) referencesEmployees(eid), didnumber(5) references Departments(did), sincedate); CREATE tableDepartments ( did number(5) primarykey, dnamevarchar2(15), budgetnumber(10,2), managerIdnumber(5)); 3. Realtion Depatments that must havemanager is constructed by using "not null "constarint. CREATEtable Departments ( didnumber(5) primary key, dnamevarchar2(15), budgetnumber(10,2), managerIdnumber(5) not null ); 4. insert into Employees values( 101,'smith', 29,15000 ); 5. Update Employees set salary := salary +(salary * (10/100)) where age = 40; 7. select avg(age) from Employees where eid= ( select eid fromEmployees where salary > $100000 and did = ( select did fromDepartments where dname = ' Database Book Reading')); " I hope this will help you " ( didnumber(5) primary key, dnamevarchar2(15), budgetnumber(10,2), managerIdnumber(5) not null ); 4. insert into Employees values( 101,'smith', 29,15000 ); 5. Update Employees set salary := salary +(salary * (10/100)) where age = 40; 7. select avg(age) from Employees where eid= ( select eid fromEmployees where salary > $100000 and did = ( select did fromDepartments where dname = ' Database Book Reading')); " I hope this will help you "