DATABASE MANAGEMENT SYSTEMS LABORATORY 10CSL57 VTU prg 3

The Eduladder is a community of students, teachers, and programmers just interested to make you pass any exams. So we solve previous year question papers for you.
See Our team
Wondering how we keep quality?
Got unsolved questions?

Ask Questions

You are here:Open notes-->VTU-->DATABASE-MANAGEMENT-SYSTEMS-LABORATORY-10CSL57-VTU-prg-3

DATABASE MANAGEMENT SYSTEMS LABORATORY [10CSL57] VTU prg-3

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


STUDENT (regno: string, name: string, major: string, bdate:date)
COURSE (course #:int, cname:string, dept:string)
ENROLL ( regno:string, course#:int, sem:int, marks:int)
BOOK _ ADOPTION (course# :int, sem:int, book-ISBN:int)
TEXT (book-ISBN:int, book-title:string, publisher:string, author:string)
i. Create the above tables by properly specifying the primary keys and the foreign keys.
ii. Enter at least five tuples for each relation.
iii. Demonstrate how you add a new text book to the database and make this book be adopted by some department.


3. Consider the following database of student enrollment in courses & books adopted for each course.
STUDENT (regno: string, name: string, major: string, bdate:date)
COURSE (course:int, cname:string, dept:string)
ENROLL ( regno:string, course:int, sem:int, marks:int)
BOOK _ ADOPTION (course:int, sem:int, book-ISBN:int)
TEXT (book-ISBN:int, book-title:string, publisher:string, author:string)
i. Create the above tables by properly specifying the primary keys and the foreign keys.
CREATE TABLE SSTUDENT
(RREGNO VARCHAR(30) PRIMARY KEY,
NAME VARCHAR(30) NOT NULL,
MAJOR VARCHAR(30) NOT NULL,
BDATE DATE NOT NULL);
CREATE TABLE CCOURSE
(COURSE INTEGER PRIMARY KEY,
CCNAME VARCHAR(30) NOT NULL,
DEPT VARCHAR(30) NOT NULL);
CREATE TABLE EENROLL
(RREGNO VARCHAR(30) NOT NULL,
COURSE INTEGER NOT NULL,
SEM INTEGER NOT NULL,
MARKS INTEGER NOT NULL,
PRIMARY KEY (RREGNO, COURSE, SEM),
FOREIGN KEY (RREGNO) REFERENCES SSTUDENT(RREGNO),
FOREIGN KEY (COURSE) REFERENCES CCOURSE(COURSE));
CREATE TABLE TTEXT
(BOOKISBN INTEGER PRIMARY KEY,
BOOKTITLE VARCHAR(30) NOT NULL,
PUBLISHER VARCHAR(30) NOT NULL,
AUTHOR VARCHAR(30) NOT NULL);
CREATE TABLE BBOOKADOPTION
(COURSE INTEGER NOT NULL,
SEM INTEGER NOT NULL,
BOOKISBN INTEGER NOT NULL,
PRIMARY KEY (COURSE, SEM, BOOKISBN),
FOREIGN KEY (COURSE) REFERENCES CCOURSE (COURSE),
FOREIGN KEY (BOOKISBN) REFERENCES TTEXT (BOOKISBN));

ii. Enter at least five tuples for each relation.

INSERT INTO SSTUDENT VALUES('1RN10IS012','ANN','DATBASE','15-JAN-84');
INSERT INTO SSTUDENT VALUES('1 RN10CS012','MARY','DMS','25-FEB-84');
INSERT INTO SSTUDENT VALUES('1 RN10TC012','TOM','SSDT','11-DEC-84');
INSERT INTO SSTUDENT VALUES('1 RN10EE012','EVE','POWER GENERATION','1-APR-84');
INSERT INTO SSTUDENT VALUES('1 RN10EC012','GEORGE','POWER ELECTRONICS','5-NOV-84');
INSERT INTO CCOURSE VALUES(1,'DATABASE','CS');
INSERT INTO CCOURSE VALUES(2,'DMS','CS');
INSERT INTO CCOURSE VALUES(3,'SSDT','TC');
INSERT INTO CCOURSE VALUES(4,'POWER GENERATION','EE');
INSERT INTO CCOURSE VALUES(5,'POWER ELECTRONICS','EC');
INSERT INTO CCOURSE VALUES(6,'DATASTRUCTURE','CS');
INSERT INTO TTEXT VALUES(1,'DATABASE A SYSTEMATIC APPROACH','JOHN WILEY','R ASHOK KUMAR');
INSERT INTO TTEXT VALUES(2,'DMS FOR DUMMIES','JOHN WILEY','MADHUPRIYA');
INSERT INTO TTEXT VALUES(3,'SSDT NO ONE CAN TEACH BETTER','PEARSON','GAURA');
INSERT INTO TTEXT VALUES(4,'POWER GENERATION BIBLE','TMH','MEENA');
INSERT INTO TTEXT VALUES(5,'POWER OF POWER ELECTRONICS','O REILLY','GG THE GREAT');
INSERT INTO TTEXT VALUES(6,'POWER OF DATASTRUCTURES','JOHN WILEY','DENNISRITCHIE');
INSERT INTO TTEXT VALUES(7,'ELEMENTARY DATASTUCTURES1','JOHN WILEY','HERBERT SHIELD');
INSERT INTO TTEXT VALUES(8,'ELEMENTARY DATASTUCTURES2','JOHN WILEY','HERBERT SHIELD');
INSERT INTO TTEXT VALUES(9,'DATABASE','JOHN WILEY','MAYOR');
INSERT INTO EENROLL VALUES ('1RN10IS012', 1, 5, 98);
INSERT INTO EENROLL VALUES ('1RN10CS012', 2, 3, 88);
INSERT INTO EENROLL VALUES ('1RN10TC012', 3, 5, 88);
INSERT INTO EENROLL VALUES ('1RN10EE012', 4, 5, 88);
INSERT INTO EENROLL VALUES ('1RN10EC012', 5, 5, 88);
INSERT INTO BBOOKADOPTION VALUES (1, 5, 1);
INSERT INTO BBOOKADOPTION VALUES (1, 4, 9);
INSERT INTO BBOOKADOPTION VALUES (2, 3, 2);
INSERT INTO BBOOKADOPTION VALUES (3, 5, 3);
INSERT INTO BBOOKADOPTION VALUES (4, 5, 4);
INSERT INTO BBOOKADOPTION VALUES (5, 5, 5);
INSERT INTO BBOOKADOPTION VALUES (6, 4, 6);
INSERT INTO BBOOKADOPTION VALUES (6, 4, 7);
INSERT INTO BBOOKADOPTION VALUES (6, 4, 8);

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

INSERT INTO TTEXT VALUES (10, 'DATABASE FUNDAS', 'PEARSON', 'SCHIELD');
INSERT INTO BBOOKADOPTION VALUES (1, 3, 10);
iv. Produce a list of text books (include Course #, 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.BOOKISBN, T.BOOKTITLE
FROM CCOURSE C, BBOOKADOPTION BA, TTEXT T
WHERE C.COURSE=BA.COURSE AND
BA.BOOKISBN=T.BOOKISBN
AND C.DEPT='CS' AND
EXISTS
(SELECT COUNT (COURSE)
FROM BBOOKADOPTION
WHERE COURSE=C.COURSE
GROUP BY COURSE
HAVING COUNT (COURSE)>=2)
ORDER BY T.BOOKTITLE;

v. List any department that has all its adopted books published by a specific publisher.
SELECT C.DEPT, T.BOOKTITLE, T.PUBLISHER
FROM CCOURSE C, TTEXT T, BBOOKADOPTION BA
WHERE C.COURSE=BA.COURSE AND T.BOOKISBN=BA.BOOKISBN
AND
T.PUBLISHER ='JOHN WILEY'AND
T.PUBLISHER= ALL (SELECT T1.PUBLISHER
FROM CCOURSE C1, BBOOKADOPTION BA1, TTEXT T1 WHERE BA1.BOOKISBN=T1.BOOKISBN AND
BA1.COURSE=C1.COURSE AND C.DEPT=C1.DEPT);
vi. Generate suitable reports.

You can achive this using Php my admin
vii. Create suitable front end for querying and displaying the results.

You can do this using PHP


Editors




Join eduladder!