Solution to DBMS Question – 1

Step 1: Make an ER Model

ER
Diagram – Student Database
Step 2: ER Model in to a Relational Model

Relational
Model – Student Database
Step 3: Implement the Relations (or Tables)

CREATE TABLE DEPARTMENT (DNO NUMBER (2), DNAME VARCHAR2 (20));

 Create
Table Department – Student Database
ALTER TABLE DEPARTMENT ADD PRIMARY KEY (DNO);
CREATE TABLE BRANCH (BCODE NUMBER (3), BNAME VARCHAR2 (25), DNO
NUMBER (2));

Create
Table Branch – Student Database
ALTER TABLE BRANCH ADD PRIMARY KEY (BCODE);
ALTER TABLE BRANCH ADD FOREIGN KEY (DNO) REFERENCES DEPARTMENT
(DNO);
CREATE TABLE BRANCH_COURSE (BCODE NUMBER(3),CCODE
NUMBER(4),SEMESTER NUMBER(2));
ALTER TABLE BRANCH_COURSE ADD PRIMARY KEY (BCODE, CCODE);
ALTER TABLE BRANCH_COURSE ADD FOREIGN KEY (BCODE) REFERENCES BRANCH
(BCODE);
ALTER TABLE BRANCH_COURSE ADD FOREIGN KEY (CCODE) REFERENCES COURSE
(CCODE);
CREATE TABLE STUDENT (ROLLNO NUMBER (5),
NAME VARCHAR2 (20),
DOB DATE, GENDER CHAR(2),
DOA DATE, BCODE NUMBER(3));

Create
Student Table – Student Database
ALTER TABLE STUDENT ADD PRIMARY KEY (ROLLNO);
ALTER TABLE STUDENT ADD FOREIGN KEY (BCODE) REFERENCES BRANCH
(BCODE);
ALTER TABLE ADD CONSTRAINT CHK CHECK (GENDER IN ('M','F'));
ALTER TABLE ADD CONSTRAINT CHK2 CHECK (DOA < TO_DATE('31-4-
2016,'DD-MM-YYYY');
CREATE TABLE COURSE (CCODE NUMBER (4), CNAME VARCHAR2 (25), CREDITS
NUMBER (2), DNO NUMBER (2));

Create
Table Course – Student Database
ALTER TABLE COURSE ADD PRIMARY KEY (CCODE);
ALTER TABLE COURSE ADD FOREIGN KEY (DNO) REFERENCES DEPARTMENT
(DNO));
CREATE TABLE ENROLLS (ROLLNO NUMBER (5), CCODE NUMBER (4), SESS
VARCHAR2 (15), GRADE CHAR (2));

Create
Table Enrolls – Student Database
ALTER TABLE ENROLLS ADD PRIMARY KEY (ROLLNO, CCODE, SESS);
ALTER TABLE ENROLLS ADD FOREIGN KEY ROLLNO) REFERENCES STUDENT
(ROLLNO);
ALTER TABLE ENROLLS ADD FOREIGN KEY (CCODE) REFERENCES COURSE
(CCODE);
Step 4: Insert data into the Tables

Use example SQL commands for inserting data into Department table.

INSERT INTO (value, 'string_value', value,value);

We inserted following records to the Department table.
 Insert
Record Into Department Table
We inserted data into the branch table. You may enter your own data for the
branch which requires Branch_Code, Branch_Name and associated Department
Number.

Each branch has a number of courses. We enter this information in the

branch_course table. Again you can enter your own values for Branch_Course.

Now, insert values to Course table using the following command. Make sure that
the Course ID from Branch_Course match with Course ID of Course Table.

INSERT INTO COURSE VALUES (1011, 'LINEAR ALGEBRA', 2,1);

Insert
Data Into Course Table – Student Database
Insert data into Student Table using the following command.

INSERT INTO STUDENT VALUES ( 12001, 'RAMESH KAUSHIK', TO_DATE( '3-

4-1989',DD-MM-YYYY') ,'M' , TO_DATE( '24-4-2016','DD-MM-YYYY'),
110);
Now, we need to insert information about a student who has enrolled in the
branches and the courses offered to them during a SESSION (SESS). This
information is inserted into ENROLLS table.

INSERT INTO ENROLLS VALUES( 12001, 1112, 'APRIL2013','D');

Insert
Data Into Enrolls Table – Student Database
Answers to Questions

Solution (A)
SELECT * FROM DEPARTMENT D WHERE D.DNO IN (SELECT B.DNO FROM BRANCH
B GROUP BY B.DNO HAVING COUNT (B.DNO) > 3);

Query (A)
Solution (B)
SELECT * FROM DEPARTMENT D WHERE D.DNO IN (SELECT C.DNO FROM COURSE
C GROUP BY C.DNO HAVING COUNT (C.CCODE) > 6);

Query (B)
Solution (C)
SELECT * FROM COURSE C WHERE C.CCODE IN (SELECT B.CCODE FROM
BRANCH_COURSE B GROUP BY B.CCODE HAVING COUNT (B.BCODE) > 3);

Query (C)
Solution (D)
SELECT * FROM STUDENT S WHERE S.ROLLNO IN (SELECT E.ROLLNO FROM
ENROLLS E WHERE E.GRADE = 'S' GROUP BY E.ROLLNO HAVING COUNT
(E.GRADE) > 2);

Query (D)
Solution (E)
CREATE VIEW STUDATA AS SELECT E.ROLLNO, S.NAME, COUNT (E.CCODE) AS
CC FROM STUDENT S, ENROLLS E WHERE E.ROLLNO = S.ROLLNO AND
E.GRADE ! = 'U' GROUP BY E.ROLLNO, S.NAME;

Query-E

Solution to DBMS Question – 2

Step 1: Make an ER Model

ER
Diagram – Customer Order Processing Database
Step 2: ER Model into a Relational Model

Relational
Model – Customer Order Processing Database
Queries

Solution (A)
Create all relations and enforced primary key, foreign key and check constraints
according to relational model.

CREATE TABLE CUSTOMER (CUSTOMERNO VARCHAR2 (5), CNAME VARCHAR2

(25));
ALTER TABLE CUSTOMER ADD PRIMARY KEY (CUSTOMERNO));
ALTER TABLE CUSTOMER ADD CONSTRAINT CHK3 CHECK (CUSTOMERNO LIKE ‘C
%’);
 Create
Table Customer – Customer Order Processing Database
CREATE TABLE CUST_ORDER (ORDERNO VARCHAR2 (5), ODATE DATE,
CUSTOMERNO VARCHAR2 (5), ORD_AMT NUMBER (6) DEFAULT 0);
ALTER TABLE CUST_ORDER ADD PRIMARY KEY (ORDERNO);

ALTER TABLE CUST_ORDER ADD FOREIGN KEY (CUSTOMERNO) REFERENCES

CUSTOMER (CUSTOMERNO);

ALTER TABLE CUST_ORDER ADD CONSTRAINT CK2 CHECK (ORDERNO LIKE ‘O

%’);

Create
Table Customer Order – Customer Order Processing Database
CREATE TABLE ITEM (ITEMNO VARCHAR2 (5), ITEM_NAME VARCHAR2 (30),
UNIT_PRICE NUMBER (5));
ALTER TABLE ITEM ADD PRIMARY KEY (ITEMNO);

ALTER TABLE ITEM ADD CONSTRAINT CK4 CHECK (ITEMNO LIKE ‘I %’);

Create
Table Item – Customer Order Processing Database
CREATE TABLE ORDER_ITEM (ORDERNO VARCHAR2 (5), ITEMNO VARCHAR2 (5),
QTY NUMBER (3));
ALTER TABLE ORDER_ITEM ADD PRIMARY KEY (ORDERNO, ITEMNO);

ALTER TABLE ORDER_ITEM ADD FOREIGN KEY (ORDERNO) REFERENCES

CUST_ORDER (ORDERNO);

ALTER TABLE ORDER_ITEM ADD FOREIGN KEY (ITEMNO) REFERENCES

ITEM(ITEMNO);

Create
Table Order Item – Customer Order Processing Database
SOLUTION (B)
Now, we need to insert data to the relations created after following above steps.
Insert data into the Customer table using insert command as follows.
INSERT INTO CUSTOMER VALUES (‘C0001’,’ANIL KUMAR’);

Insert
Data Into Customer Table
Insert Data to Customer Table – Customer Order-Processing Database
Inserting data into the customer order table is bit different, assume that
customer placed an order on particular date then we cannot compute the total
amount unless we know the total quantity of purchase make by the customer
and for each purchase calculate the total amount as follows.

This means we set the total order amount to 0 by default.

INSERT INTO CUST_ORDER (‘O0001’, TO_DATE (’12-5-2013’,’DD-MM-
YYYY’), ‘C0002’,0);
If we insert all data like this our cust_order table will look like following.

Insert
Data Into Customer Order
Now we insert data into the order_item table so that we know the total quantity
and unit price of the item customer has purchased.

INSERT INTO ORDER_ITEM VALUES (‘O0001’, ‘I0002’, 4);

Instance
of Order Item Table
Now we update the customer order table with order amount as follows

Update
Command Customer Order Table
The result should look like the following.

Updated
Cust_Order Table
SOLUTION (C)
SELECT * FROM CUSTOMER C WHERE C.CUSTOMERNO IN (SELECT O.CUSTOMERNO
FROM CUST_ORDER O GROUP BY O.CUSTOMERNO HAVING COUNT (O.ORDERNO) >
2);

Solution
(C)
SOLUTION (D)
SELECT * FROM ITEM I WHERE I.UNIT_PRICE < (SELECT AVG
(UNIT_PRICE) FROM ITEM);

Solution
(D)
SOLUTION (E)
SELECT ORDERNO, SUM (OTY) FROM ORDER_ITEM GROUP BY ORDERNO;
 Solution
(E): Total Quantity of Items
SOLUTION (F)
SELECT * FROM ITEM WHERE ITEMNO IN (SELECT ITEMNO FROM ORDER_ITEM
GROUP BY ITEMNO HAVING COUNT (ITEMNO) >= (SELECT (COUNT (*)/4) FROM
CUST_ORDER));

Solution
(F): Item Count that is present in 25% of Orders
SOLUTION (G)
UPDATE CUST_ORDER SET ORD_AMT = (SELECT SUM (O.QTY * I.UNIT_PRICE)
FROM ORDER_ITEM O, ITEM I WHERE CUST_ORDER.ORDERNO = O.ORDERNO AND
O.ITEMNO = I.ITEMNO);

Solution
(G): Update Customer Order Table
Create a view the customer order table after update and its
called Customer_Details.
 View For
Customer Order Table

Run the following command to view and instance of Customer_Details View.

An Instance of Customer_Detail View

Solution to DBMS Question – 3

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

ER Model – Project Database

Step 2: Convert the E-R Model into a Relational Model
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 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 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 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
Solution (B)

Insert data into all the Relations.

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
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
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
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
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

Solution (C)

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

DEPTNO;
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
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
Solution (H)

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);
Staff With More Than 3 Skills – Project Database

Solution to DBMS Question – 4

Step: Create an E-R Model

ER
Diagram – Bank Database
Step : Relational Model

Relational
Model – Bank Database
Step : Implement Relations

1. Create the CUSTOMER table first and add constraints.

CREATE TABLE CUSTOMER ( CID NUMBER(5), CNAME VARCHAR2(30));

ALTER TABLE CUSTOMER ADD PRIMARY KEY(CID));
2. Create the BRANCH table and add constraints.

CREATE TABLE BRANCH (BCODE VARCHAR2(5), BNAME VARCHAR2(30));

ALTER TABLE BRANCH ADD PRIMARY KEY(BCODE));

ALTER TABLE BRANCH ADD CONSTRAINT CPK CHECK ( BCODE LIKE 'B%');
3. Create the ACCOUNT table and add constraints – primary key, foreign key and
check constraints for account type.

S – Saving and C – current account

CREATE TABLE ACCOUNT ( ANO VARCHAR2(5), ATYPE CHAR(2), BALANCE
NUMBER(7), CID NUMBER(5), BCODE VARCHAR2(5));
ALTER TABLE ACCOUNT ADD PRIMARY KEY(ANO) ;

ALTER TABLE ACCOUNT ADD FOREIGN KEY(CID) REFERENCES CUSTOMER (CID);

ALTER TABLE ACCOUNT ADD FOREIGN KEY(BCODE) REFERENCES

BRANCH(BCODE);
ALTER TABLE ACCOUNT ADD CONSTRAINT CPK2 CHECK (ATYPE IN ('S','C'));

ALTER TABLE ACCOUNT ADD CONSTRAINT CK CHECK (ANO LIKE 'A%');

4. Create the final table – TRANSACTION and add constraints such as primary
key, foreign key and check constraints for TTYPE.

W – WITHDRAWAL
D – DEPOSIT
CREATE TABLE TRANSACTION ( TID VARCHAR2(5), ANO VARCHAR2(5), TTYPE
CHAR(2), TDATE DATE, TAMOUNT NUMBER(7));
ALTER TABLE TRANSACTION ADD PRIMARY KEY(TID); ALTER TABLE
TRANSACTION ADD FOREIGN KEY(ANO) REFERENCES ACCOUNT(ANO); ALTER
TABLE TRANSACTION ADD CONSTRAINT CPK3 CHECK (TTYPE IN ('W','D');
ALTER TABLE TRANSACTION ADD CONSTRAINT CPK4 CHECK (TID LIKE 'T%');
Other Solutions

Solution (B)
You can insert data into the tables now.
1. Customer Relation look like below after inserting data.

Customer
Relation – Bank Database
2. Insert data into the branch table as follows.

Branch
Table – Bank Database
3. Insert the data into account table.
 Account
Table – Bank Database
4. Insert data into the last table – Transaction.

Transactio
n Table – Bank Database
Solution (C)

Solution
(C) – Bank Database
Solution (D)
 Solution
(D) – Bank Database
Solution (E)

Solution
(E) – Bank Database
Solution (F)

Solution
(F) – Bank Database