Solution to DBMS Question – 1

On this page, you will find the 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

Step 2: ER Model in to a Relational Model

Step 3: Implement the Relations (or Tables)

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

ALTER TABLE DEPARTMENT ADD PRIMARY KEY (DNO);
CREATE TABLE BRANCH (BCODE NUMBER (3), BNAME VARCHAR2 (25), DNO NUMBER (2));

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

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

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

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.

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 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');

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

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

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

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

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;