[VTU DBMS PGM 3] Books Database (involving the tables 'STUDENT', 'COURSE', 'ENROLL', 'BOOK_ADOPTION', 'TEXT')

Question:

Consider the following database of student enrollment in courses & books adopted for each course. 

• STUDENT (regno: string, name: string, major: string, bdate: date) 
• COURSE (courseno: int, cname: string, dept: string) 
• ENROLL ( regno: string, courseno: int, sem: int, marks: int) 
• BOOK _ ADOPTION (courseno: int, sem: int, bookisbn: int) 
• TEXT (bookisbn: int, btitle: string, publisher: string, author: string)

Queries:
1. Create the above tables by properly specifying the primary keys and the foreign keys.

CREATE TABLE STUDENT (
regno varchar(10), 
name varchar(10), 
major varchar(8), 
bdate date, 
constraint spk PRIMARY KEY(regno));


CREATE TABLE COURSE (
course# int, 
cname varchar(10), 
dept varchar(10), 
constraint cpk PRIMARY KEY(course#));


CREATE TABLE ENROLL(
regno varchar(10), 
course# int, 
sem int, 
marks int, 
constraint epk PRIMARY KEY(regno,course#,sem), 
constraint efk1 FOREIGN KEY(regno) references STUDENT(regno), 
constraint efk2 FOREIGN KEY(course#) references COURSE(course#));


CREATE TABLE TEXT (
book_isbn int, 
book_title varchar(10),
publisher varchar(10), 
author varchar(10), 
constraint tpk PRIMARY KEY(book_isbn));


CREATE TABLE BOOK_ADOPTION (
course# int, 
sem int, 
book_isbn int, 
constraint bpk PRIMARY KEY(course#, sem), 
constraint bfk1 FOREIGN KEY(course#) references COURSE(course#), 
constraint bfk2 FOREIGN KEY(book_isbn) references TEXT(book_isbn));

2. Enter at least five tuples for each relation.

INSERT INTO STUDENT VALUES('&no','&name','&major','&bdate');

INSERT INTO COURSE
VALUES('&course','&cname','&dept');

INSERT INTO ENROLL VALUES('&no','&course#','&sem','&marks');

INSERT INTO TEXT VALUES('&book_isbn','&book_title','&publisher','&author');

INSERT INTO BOOK_ADOPTION VALUES('&course','&sem','&book_isbn');

3. Demonstrate how you add a new text book to the database and make this book be adopted by some department. 

INSERT INTO TEXT VALUES(1686, 'DBMS', 'pearson', 'navathe');

INSERT INTO BOOK_ADOPTION VALUES(15, 2, 1686);

4. Produce a list of text books (include Courseno, Book-ISBN, Book-title) in the alphabetical order for courses offered by the ‘CS’ department that use more than two books.

SELECT C.course#, T.book_isbn, T.book_title 
FROM COURSE C, BOOK_ADOPTION B, TEXT T
WHERE C.course# = B.course# AND 
B.book_isbn = T.book_isbn  AND 
C.dept='cse' AND 
C.course# IN (SELECT course#
FROM BOOK_ADOPTION 
GROUP BY course# 
HAVING COUNT(*)>2)  
ORDER BY C.course#, T.book_isbn, T.book_title; 

5. List any department that has all its adopted books published by a specific publisher.

SELECT C.dept, COUNT(*) 
FROM COURSE C, BOOK_ADOPTION B 
WHERE C.course# = B.course# 
GROUP BY C.dept 
INTERSECT 
SELECT C.dept, COUNT(*) 
FROM COURSE C, BOOK_ADOPTION B, TEXT T 
WHERE C.course# = B.course# AND 
B.book_isbn = T.book_isbn AND 
T.publisher = 'sapna' 
GROUP BY C.dept; 

6. Generate suitable reports.

ttitle 'BOOKS ADOPTED BY PARTICULAR DEPARTMENT' btitle 'BOOK DATABASE'
column ISBN heading 'BOOK ISBN' 
column title heading 'BOOK TITLE' 
column cname heading 'COURSE NAME' 
column dept heading 'DEPARTMENT NAME' 
break on dept skip 2 
compute count of ISBN on dept
set linesize 100 
set pagesize 40 

select t.ISBN,t.title,c.cname,c.dept 
from course c, BOOK_ADOPTION a,text t 
where c.course#=a.course# and 
a.ISBN=t.ISBN 
order by dept; 

btitle off 
ttitle off 
clear breaks 
clear columns

Author : Unknown

He is a simple passionate tech freak who himself is an engineering student at Canara Engineering college. He likes App Development, Web designing, Blogging, Youtubing, Debugging and also is a CodeGeek!

Sharing is sexy!

Related Articles