Solution to DBMS Question

Table of Contents

This article has the solution to the third DBMS lab question to help you learn. We recommend you to try solving the problem yourself and then check the solution.

Step 1: Create an E-R diagram of Question – 3.

ER Model - Project Database — ER Model – Project Database

Step 2: Convert the E-R Model into a Relational Model

Relational Model - Project database — Relational Model – Project database

QUERIES

Solution(A)

CREATE TABLE DEPTS ( DEPTNO VARCHAR2(5), DNAME VARCHAR2(30));

ALTER TABLE DEPTS ADD PRIMARY KEY(DEPTNO);

ALTER TABLE DEPTS ADD CONSTRAINT CPK CHECK (DEPTNO LIKE 'd%');

Create Table Department – Project Database

CREATE TABLE STAFF ( STAFFNO NUMBER(5), NAME VARCHAR2(30), DOB DATE, GENDER CHAR(2), DOJ DATE, DESIGNATION VARCHAR2(30), BASIC_PAY NUMBER(6),DEPTNO VARCHAR2(5));

ALTER TABLE STAFF ADD PRIMARY KEY(STAFFNO);

ALTER TABLE STAFF ADD FOREIGN KEY(DEPTNO) REFERENCES DEPTS (DEPTNO);

ALTER TABLE STAFF ADD CONSTRAINT CPK2 CHECK( GENDER IN ('M','F'));

Create Table Staff – Project Database

CREATE TABLE SKILL ( SKILL_CODE VARCHAR2(5), DESCRIPTION VARCHAR2(30), CHARGE_OUTRATE NUMBER(3));

ALTER TABLE SKILL ADD PRIMARY KEY(SKILL_CODE);
ALTER TABLE SKILL ADD CONSTRAINT CPK4 CHECK( SKILL_CODE LIKE 'S%');

Create Table Skill - Project Database — Create Table Skill – Project Database

CREATE TABLE STAFF_SKILL ( STAFFNO NUMBER(5), SKILL_CODE VARCHAR2(5));

ALTER TABLE STAFF_SKILL ADD PRIMARY KEY(STAFFNO,SKILL_CODE);

ALTER TABLE STAFF_SKILL ADD FOREIGN KEY(STAFFNO) REFERENCES STAFF(STAFFNO);

ALTER TABLE STAFF_SKILL ADD FOREIGN KEY(SKILL_CODE) REFERENCES SKILL(SKILL_CODE);

Create Table Staff_Skill - Project Database — Create Table Staff_Skill – Project Database

CREATE TABLE WORKS ( STAFFNO NUMBER(5), PROJECTNO VARCHAR2(5), DATE_WORKED_ON DATE, INTIME TIMESTAMP, OUTTIME TIMESTAMP);

ALTER TABLE WORKS ADD PRIMARY KEY(STAFFNO,PROJECTNO);

ALTER TABLE WORKS ADD FOREIGN KEY(STAFFNO) REFERENCES STAFF(STAFFNO);

ALTER TABLE WORKS ADD FOREIGN KEY(PROJECTNO) REFERENCES PROJECT(PROJECTNO);

Create Table Works - Project Database — Create Table Works – Project Database

Solution (B)

Insert data into all the Relations.

Advertisements

1. First we insert into the DEPTS relation.

INSERT INTO DEPTS VALUES('d0001','FINANCE');
INSERT INTO DEPTS VALUES('d0002','MARKETING');
INSERT INTO DEPTS VALUES('d0003','ADMINISTRATION');
INSERT INTO DEPTS VALUES('d0004','OPERATIONS');
INSERT INTO DEPTS VALUES('d0005','MEDICAL');
INSERT INTO DEPTS VALUES('d0006','STORE');
INSERT INTO DEPTS VALUES('d0007','SALES');
INSERT INTO DEPTS VALUES('d0008','PURCHASE');

Your table should look like this,

Insert Data Into Department Table - Project Database — Insert Data Into Department Table – Project Database

2. Next, we insert data into the STAFF relation.

INSERT INTO STAFF VALUES (50001,'RENUKA SHASTRI',TO_DATE('01-7-1980','DD-MM-YYYY'),'F',TO_DATE('10-5-2010','DD-MM-YYYY'),'ACCOUNTANT',20000,'d0001');

INSERT INTO STAFF VALUES (50002,'MIR RAJA',TO_DATE('02-08-1987','DD-MM-YYYY'),'M',TO_DATE('5-5-2011','DD-MM-YYYY'),' CHIEF ACCOUNTANT',50000,'d0001');

INSERT INTO STAFF VALUES (50003,'KARTICK RAJ',TO_DATE('04-11-1979','DD-MM-YYYY'),'M',TO_DATE('06-06-2013','DD-MM-YYYY'),'AUDITOR',30000,'d0001');

INSERT INTO STAFF VALUES (50004,'MIKE TYSON',TO_DATE('21-5-1984','DD-MM-YYYY'),'M',TO_DATE('4-6-2012','DD-MM-YYYY'),'AD DESIGNER',100000,'d0002');

INSERT INTO STAFF VALUES (50005,'NEIL ARMSTRONG',TO_DATE('01-04-1976','DD-MM-YYYY'),'M',TO_DATE('05-06-2011','DD-MM-YYYY'),'MANAGER',150000,'d0002');

INSERT INTO STAFF VALUES (50006,'KAVITA CHOPRA',TO_DATE('04-6-1988','DD-MM-YYYY'),'F',TO_DATE('21-08-2012','DD-MM-YYYY'),'WRITER',25000,'d0002');

INSERT INTO STAFF VALUES (50007,'VINI JOSEPH',TO_DATE('03-5-1982','DD-MM-YYYY'),'F',TO_DATE('03-07-2013','DD-MM-YYYY'),'OFFICE ASSISTANT',40000,'d0003');

INSERT INTO STAFF VALUES (50008,'MIKE PEGGS',TO_DATE('05-07-1987','DD-MM-YYYY'),'M',TO_DATE('5-5-2011','DD-MM-YYYY'),'OFFICE MANAGER',120000,'d0003');

INSERT INTO STAFF VALUES (50009,'LARRY PAGE',TO_DATE('06-6-1969','DD-MM-YYYY'),'M',TO_DATE('4-4-2010','DD-MM-YYYY'),'OFFICE SECRETARY',50000,'d0003');

INSERT INTO STAFF VALUES (50010,'YIN YO',TO_DATE('08-8-1987','DD-MM-YYYY'),'F',TO_DATE('6-5-2010','DD-MM-YYYY'),'OPERATIONS MANAGER',120000,'d0004');

INSERT INTO STAFF VALUES (50011,'WU YONG',TO_DATE('7-5-1987','DD-MM-YYYY'),'M',TO_DATE('6-3-2014','DD-MM-YYYY'),'EXECUTIVE',20000,'d0004');

INSERT INTO STAFF VALUES (50012,'PETER CHONG',TO_DATE('2-11-1982','DD-MM-YYYY'),'M',TO_DATE('1-5-2014','DD-MM-YYYY'),'EXECUTIVE',23000,'d0004');

INSERT INTO STAFF VALUES (50013,'XIN GI',TO_DATE('7-5-1987','DD-MM-YYYY'),'M',TO_DATE('6-3-2014','DD-MM-YYYY'),' PUBLIC RELATION EXEC',40000,'d0004');

INSERT INTO STAFF VALUES (50014,'RUSKIN BOND',TO_DATE('4-10-1988','DD-MM-YYYY'),'M',TO_DATE('5-5-2014','DD-MM-YYYY'),'STENOGRAPHER',10000,'d0004');

INSERT INTO STAFF VALUES (50015,'MARY DESOUZA',TO_DATE('6-10-1985','DD-MM-YYYY'),'M',TO_DATE('3-3-2014','DD-MM-YYYY'),'STORE KEEPER',15000,'d0004');

INSERT INTO STAFF VALUES (50016,'JASON KIRK',TO_DATE('6-8-1985','DD-MM-YYYY'),'M',TO_DATE('3-5-2014','DD-MM-YYYY'),'NUSRE',15000,'d0005');

INSERT INTO STAFF VALUES (50017,'JOHN DOE',TO_DATE('1-9-1988','DD-MM-YYYY'),'M',TO_DATE('3-11-2014','DD-MM-YYYY'),'NUSRE',15000,'d0005');

INSERT INTO STAFF VALUES (50018,'KIM BARN',TO_DATE('6-6-1990','DD-MM-YYYY'),'F',TO_DATE('23-5-2014','DD-MM-YYYY'),' CASHIER',15000,'d0005');

INSERT INTO STAFF VALUES (50019,'RAGHU RAM',TO_DATE('3-1-1978','DD-MM-YYYY'),'M',TO_DATE('3-2-2011','DD-MM-YYYY'),'MEDICAL OFFICER',55000,'d0005');

INSERT INTO STAFF VALUES (50020,'RAMAKANTH DORE',TO_DATE('1-10-1986','DD-MM-YYYY'),'M',TO_DATE('3-2-2012','DD-MM-YYYY'),'MEDICAL OFFICER',55000,'d0005');

INSERT INTO STAFF VALUES (50021,'DR. VIRDAS GUPTA',TO_DATE('2-5-1978','DD-MM-YYYY'),'M',TO_DATE('20-12-2014','DD-MM-YYYY'),' MD',115000,'d0005');

Insert Data into Staff Table - Project Database — Insert Data into Staff Table – Project Database

3. Insert data into the SKILL table.

INSERT INTO SKILL VALUES('S0001','TYPING SKILL',50);

INSERT INTO SKILL VALUES('S0002','BOOK KEEPING',60);

INSERT INTO SKILL VALUES('S0003','TALLY EXPERT',60);

INSERT INTO SKILL VALUES('S0004','AD DESIGNING',70);

INSERT INTO SKILL VALUES('S0005','ART WORKS',40);

INSERT INTO SKILL VALUES('S0006','WEB CONTENT WRITING',60);

INSERT INTO SKILL VALUES('S0007','STORE KEEPING',40);

INSERT INTO SKILL VALUES('S0008','MS OFFICE',80);

INSERT INTO SKILL VALUES('S0009','LINUX ADMINISTRATION',50);

INSERT INTO SKILL VALUES('S0010','AUTOCAD',100);

Insert Data into Skill Table - Project Database — Insert Data into Skill Table – Project Database

4. Insert data into the STAFF_SKILL relation where each staff can have one or more skills.

INSERT INTO STAFF_SKILL VALUES(50001,'S0001');

INSERT INTO STAFF_SKILL VALUES(50001,'S0002');

INSERT INTO STAFF_SKILL VALUES(50001,'S0003');

INSERT INTO STAFF_SKILL VALUES(50001,'S0007');

INSERT INTO STAFF_SKILL VALUES(50001,'S0008');

INSERT INTO STAFF_SKILL VALUES(50002,'S0001');

INSERT INTO STAFF_SKILL VALUES(50002,'S0002');

INSERT INTO STAFF_SKILL VALUES(50002,'S0003');

INSERT INTO STAFF_SKILL VALUES(50002,'S0008');

INSERT INTO STAFF_SKILL VALUES(50003, 'S0001');

INSERT INTO STAFF_SKILL VALUES(50003, 'S0002');

INSERT INTO STAFF_SKILL VALUES(50003, 'S0007');

INSERT INTO STAFF_SKILL VALUES(50003, 'S0008');

INSERT INTO STAFF_SKILL VALUES(50004,'S0001');

INSERT INTO STAFF_SKILL VALUES(50004,'S0008');

INSERT INTO STAFF_SKILL VALUES(50004,'S0004');

INSERT INTO STAFF_SKILL VALUES(50004,'S0005');

INSERT INTO STAFF_SKILL VALUES(50005,'S0010');

INSERT INTO STAFF_SKILL VALUES(50005,'S0008');

INSERT INTO STAFF_SKILL VALUES(50006,'S0001');

INSERT INTO STAFF_SKILL VALUES(50006,'S0006');

INSERT INTO STAFF_SKILL VALUES(50006,'S0008');

INSERT INTO STAFF_SKILL VALUES(50007,'S0001');

INSERT INTO STAFF_SKILL VALUES(50007,'S0002');

INSERT INTO STAFF_SKILL VALUES(50008,'S0002');

INSERT INTO STAFF_SKILL VALUES(50008,'S0008');

INSERT INTO STAFF_SKILL VALUES(50008,'S0010');

INSERT INTO STAFF_SKILL VALUES(50009,'S0008');

INSERT INTO STAFF_SKILL VALUES(50009,'S0009');

INSERT INTO STAFF_SKILL VALUES(50010,'S0002');

INSERT INTO STAFF_SKILL VALUES(50010,'S0008');

INSERT INTO STAFF_SKILL VALUES(50010,'S0004');

INSERT INTO STAFF_SKILL VALUES(50010,'S0010');

Insert Data into Staff Skill - Project Database — Insert Data into Staff Skill – Project Database

5. We now insert data into the relation WORKS.

INSERT INTO WORKS VALUES(50001,'P0003',TO_DATE('2-2-2015','DD-MM-YYYY'),TO_TIMESTAMP('2-FEB-2015 9.30.20','DD-MON-RR HH24.MI.SS'),TO_TIMESTAMP('2-FEB-2015 17.34.20' ,'DD-MON-RR HH24.MI.SS'));

INSERT INTO WORKS VALUES(50002,'P0003',TO_DATE('2-2-2015','DD-MM-YYYY'),TO_TIMESTAMP('2-FEB-2015 9.15.50','DD-MON-RR HH24.MI.SS'),TO_TIMESTAMP('2-FEB-2015 18.12.40' ,'DD-MON-RR HH24.MI.SS'));

INSERT INTO WORKS VALUES(50003,'P0001',TO_DATE('4-5-2015','DD-MM-YYYY'),TO_TIMESTAMP('4-MAY-2015 9.22.10','DD-MON-RR HH24.MI.SS'),TO_TIMESTAMP('4-MAY-2015 17.32.10' ,'DD-MON-RR HH24.MI.SS'));

INSERT INTO WORKS VALUES(50004,'P0005',TO_DATE('1-4-2014','DD-MM-YYYY'),TO_TIMESTAMP('1-APR-2014 9.15.20','DD-MON-RR HH24.MI.SS'),TO_TIMESTAMP('1-APR-2014 17.34.50' ,'DD-MON-RR HH24.MI.SS'));

INSERT INTO WORKS VALUES(50005,'P0004',TO_DATE('1-3-2014','DD-MM-YYYY'),TO_TIMESTAMP('1-MAR-2014 9.15.20','DD-MON-RR HH24.MI.SS'),TO_TIMESTAMP('1-MAR-2014 18.34.50' ,'DD-MON-RR HH24.MI.SS'));

INSERT INTO WORKS VALUES(50006,'P0006',TO_DATE('1-1-2015','DD-MM-YYYY'),TO_TIMESTAMP('1-JAN-2015 9.11.33','DD-MON-RR HH24.MI.SS'),TO_TIMESTAMP('1-JAN-2015 17.30.00' ,'DD-MON-RR HH24.MI.SS'));

Insert Data into Works Table - Project Database — Insert Data into Works Table – Project Database

Solution (C)

SELECT DEPTNO, COUNT(STAFFNO) AS STAFF_COUNT FROM STAFF GROUP BY DEPTNO;

Staff Count by Department - Project Database — Staff Count by Department – Project Database

Solution (D)

SELECT * FROM STAFF WHERE BASIC_PAY < (SELECT AVG(BASIC_PAY) FROM STAFF;

Staff with less than AVG basic pay of all Staff - Project Database — Staff with less than AVG basic pay of all Staff – Project Database

Solution (E)

SELECT * FROM STAFF WHERE STAFFNO IN ( SELECT STAFFNO FROM STAFF_SKILL WHERE SKILL_CODE IN ( SELECT SKILL_CODE FROM SKILL WHERE CHARGE_OUTRATE < 60));

Find out all the staff whose Charge_Outrate more than 60

Solution (G)

CREATE VIEW STAFFCOUNT AS
SELECT D.DEPTNO,D.DNAME,COUNT(S.STAFFNO) AS STAFF_COUNT,SUM(S.BASIC_PAY) AS DEPT_EXPENSE
FROM DEPTS D,STAFF S
WHERE D.DEPTNO = S.DEPTNO GROUP BY D.DEPTNO,D.DNAME;

Staff Count - Project Database — Staff Count – Project Database

Solution (H)

Staff Count Greater than 5 - Project Database — Staff Count Greater than 5 – Project Database

Solution (I)

SELECT * FROM STAFF
WHERE STAFFNO IN
( SELECT STAFFNO FROM STAFF_SKILL GROUP BY STAFFNO
HAVING COUNT(SKILL_CODE) > 3);