Solution to DBMS Question – 1

On this page you will find solution to DBMS Question – 1 and all answers in this article is created using Oracle 10g.

View DBMS Assignment Questions

Step 1: Make an ER Model of the system

ER Diagram - Student Database
ER Diagram – Student Database

Step 2: Convert the ER Model in to a Relational Model

Relational Model - Student Database
Relational Model – Student Database

Step 3: Use SQL to implement the Relations (or Tables) and Constraints on Relations

CREATE TABLE DEPARTMENT (DNO NUMBER (2), DNAME VARCHAR2 (20));
Create Table Department - Student Database
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
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
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
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
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 DepartmentTable
Insert Record Into DepartmentTable

We inserted data in to the branch table. You may enter your own data for branch which requires Branch_Code, Branch_Name and associated Department Number.

Each branch have 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 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 Course Table – Student Database

Insert Data to Course Table – Student DatabaseInsert data into Student Table using 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 student who have enrolled to 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
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)
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)
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)
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)
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
Query-E
Advertisements