An Engineering company has two types of Employees, full time and part-time. Full
ID: 3622724 • Letter: A
Question
An Engineering company has two types of Employees, full time and part-time. Full-time employees receive a monthly salary whereas part-time employees have an hourly rate. Initially, the two types were mutually exclusive, meaning that no employee can be both full-time and part-time at the same time. The company used the following relations:Full-Time-Employee (EmpId, Name, Address, Salary)
Part-Time-Employee (EmpId, Name, Address, HourlyRate)
Later it was decided to allow full-time employees to work on a part time basis in their spare time and so can be both full-time and part-time employees. To reduce redundancy, the company decided to change the database schema to the following:
Employee (EmpId, Name, Address)
FT (EmpId, Salary)
PT (EmpId, Hourly-Rate)
Using cursors, create a SQL procedure to convert the old schema into the new one. In order to maintain old application programs, your procedure should also create two views, called Full-Time-Employee and Part-Time-Employee with identical structure of the old tables with the same names.
Explanation / Answer
--STEP 1: CREATE TABLES AND INSERT SAMPLE DATA CREATE TABLE Full_Time_Employee ( EmpId NUMBER(7) NOT NULL, Name VARCHAR2(50 BYTE), Address VARCHAR2(250 BYTE), Salary NUMBER(10,2) ); SELECT * FROM FULL_TIME_EMPLOYEE; ALTER TABLE Full_Time_Employee ADD (PRIMARY KEY (EmpId)); CREATE TABLE Part_Time_Employee ( EmpId NUMBER(7) NOT NULL, Name VARCHAR2(50 BYTE), Address VARCHAR2(250 BYTE), Hourly_Rate NUMBER(7) ); ALTER TABLE Part_Time_Employee ADD (PRIMARY KEY (EmpId)); INSERT INTO Full_Time_Employee(EmpId, Name, Address, Salary) Values(1, 'Tranjan Naraine', 'MUMBAI',5000); INSERT INTO Full_Time_Employee(EmpId, Name, Address, Salary) Values(2, 'Peter Obskley', 'DELHI',4000); INSERT INTO Full_Time_Employee(EmpId, Name, Address, Salary) Values(3, 'Joseph Adam', 'ISLAMABAD',3000); INSERT INTO Part_Time_Employee(EmpId, Name, Address, Hourly_RATE) Values(1, 'Faheem Moodi', 'ISLAMABAD',50); INSERT INTO Part_Time_Employee(EmpId, Name, Address, Hourly_RATE) Values(2, 'Lal Ashwar', 'PESHAWAR',75); INSERT INTO Part_Time_Employee(EmpId, Name, Address, Hourly_RATE) Values(3, 'Qaseem Uddin', 'CHENNAI',35); CREATE TABLE Employee ( EmpId NUMBER(7) NOT NULL, Name VARCHAR2(50 BYTE), Address VARCHAR2(250 BYTE) ); ALTER TABLE Employee ADD (PRIMARY KEY (EmpId)); CREATE TABLE FT ( EmpId NUMBER(7) NOT NULL, Salary NUMBER(10,2) ); ALTER TABLE FT ADD (PRIMARY KEY (EmpId)); CREATE TABLE PT ( EmpId NUMBER(7) NOT NULL, Hourly_Rate NUMBER(7) ); ALTER TABLE PT ADD (PRIMARY KEY (EmpId)); ALTER TABLE FT ADD (CONSTRAINT FK_FT_EmpId FOREIGN KEY (EmpId) REFERENCES Employee (EmpId)); ALTER TABLE PT ADD (CONSTRAINT FK_PT_EmpId FOREIGN KEY (EmpId) REFERENCES Employee (EmpId)); --cREATE PROCEDURE TO INSERT DATA FROM SOLD SCHEMA TO NEW --Procedure to insert data from old tables to new schema tables declare cursor cur_emp is select empid,name ,address,salary from Full_Time_Employee; cursor cur_temp is select empid,name ,address,Hourly_Rate from Part_Time_Employee; v_seq number; begin for lp_emp in cur_emp loop select nvl(max( EmpId),0)+1 into v_seq from employee; insert into employee (EmpId, Name, Address) values (v_seq,lp_emp.name,lp_emp.address); insert into FT (EmpId, Salary) values (v_seq,lp_emp.salary); end loop; for lp_emp in cur_temp loop select nvl(max( EmpId),0)+1 into v_seq from employee; insert into employee (EmpId, Name, Address) values (v_seq,lp_emp.name,lp_emp.address); insert into PT (EmpId, Hourly_Rate) values (v_seq,lp_emp.Hourly_Rate); end loop; commit; end; --dROP TABLE IN ORDER TO CREATE VIEW OF SAME NAME DROP TABLE Full_Time_Employee; DROP TABLE Part_Time_Employee; --CREATE VIEW create view Full_Time_Employee as select e.empid,e.name,e.address,f.salary from employee e,ft f where e.empid = f.empid; create view Part_Time_Employee as select e.empid,e.name,e.address,f.Hourly_Rate from employee e,pt f where e.empid = f.empid; --hOPE THIS WILL HELP YOU