Monday, 12 December 2011

Database of student enrollment in courses and books adopted for each course.

Tables

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, Title: String, Publisher: String, Author: String)

1.      Create above tables with properly specifying primary keys and foreign keys.
SQL> CREATE TABLE STUDENT(
  2  REGNO VARCHAR(10),
  3  NAME VARCHAR(10),
  4  MAJOR VARCHAR(10),
  5  BDATE DATE,
  6  PRIMARY KEY(REGNO));

Table created.

SQL> CREATE TABLE COURSE(
  2  COURSENO INT,
  3  CNAME VARCHAR(10),
  4  DEPT VARCHAR(10),
  5  PRIMARY KEY(COURSENO));

Table created.

SQL> CREATE TABLE TEXT(
  2  BOOKISBN INT,
  3  TITLE VARCHAR(20),
  4  PUBLISHER VARCHAR(20),
  5  AUTHOR VARCHAR(20),
  6  PRIMARY KEY(BOOKISBN));

Table created.

SQL> CREATE TABLE ENROLL(
  2  REGNO VARCHAR(10),
  3  COURSENO INT,
  4  SEM INT,
  5  MARKS INT,
  6  PRIMARY KEY(REGNO,COURSENO,SEM),
  7  FOREIGN KEY(REGNO) REFERENCES STUDENT(REGNO) ON DELETE SET NULL,
  8  FOREIGN KEY(COURSENO) REFERENCES COURSE(COURSENO) ON DELETE SET NULL);

Table created.

SQL> CREATE TABLE BOOKADOPTION(
  2  COURSENO INT,
  3  SEM INT,
  4  BOOKISBN INT,
  5  PRIMARY KEY(COURSENO,SEM),
  6  FOREIGN KEY(COURSENO) REFERENCES COURSE(COURSENO) ON DELETE SET NULL,
  7  FOREIGN KEY(BOOKISBN) REFERENCES TEXT(BOOKISBN) ON DELETE SET NULL);

Table created.

2.      Enter at least 7-8 tuples for each relation.

SQL> INSERT INTO STUDENT VALUES('&REGNO','&NAME','&MAJOR','&BDATE');
Enter value for regno: 2BV10MCA01
Enter value for name: JAMES
Enter value for major: COMPUTERS
Enter value for bdate: 01-JAN-89
old   1: INSERT INTO STUDENT VALUES('&REGNO','&NAME','&MAJOR','&BDATE')
new   1: INSERT INTO STUDENT VALUES('2BV10MCA01','JAMES','COMPUTERS','01-JAN-89')

1 row created.

SQL>  INSERT INTO TEXT VALUES(&BOOKISBN,'&TITLE','&PUBLISHER','&AUTHOR');
Enter value for bookisbn: 101
Enter value for title: DATABASE SYSTEMS
Enter value for publisher: PEARSON
Enter value for author: NAVATHE
old   1:  INSERT INTO TEXT VALUES(&BOOKISBN,'&TITLE','&PUBLISHER','&AUTHOR')
new   1:  INSERT INTO TEXT VALUES(101,'DATABASE SYSTEMS','PEARSON','NAVATHE')

1 row created.

SQL>  INSERT INTO ENROLL VALUES('&REGNO',&COURSENO,&SEM,&MARKS);
Enter value for regno: 2BV10MCA01
Enter value for courseno: 1
Enter value for sem: 3
Enter value for marks: 80
old   1:  INSERT INTO ENROLL VALUES('&REGNO',&COURSENO,&SEM,&MARKS)
new   1:  INSERT INTO ENROLL VALUES('2BV10MCA01',1,3,80)

1 row created.


SQL> INSERT INTO BOOKADOPTION VALUES(&COURSENO,&SEM,&BOOKISBN);
Enter value for courseno: 1
Enter value for sem: 3
Enter value for bookisbn: 101
old   1: INSERT INTO BOOKADOPTION VALUES(&COURSENO,&SEM,&BOOKISBN)
new   1: INSERT INTO BOOKADOPTION VALUES(1,3,101)

1 row created.


3.      Displaying the table contents

SQL> SELECT * FROM STUDENT;

REGNO            NAME                  MAJOR                       BDATE
2BV10MCA01    JAMES               COMPUTERS              01-JAN-89
2BV10MCA02    JOHN                 NETWORKS                02-JAN-90
2BV10MCA03    ARNOLD           SYSTEMS                    03-FEB-89
2BV10MCA04    KIM                   E&C                              04-MAR-90
2BV10MCA05    FRED                 E&E                               05-APR-89
2BV10MCA06    HARIS               MECH                           05-MAY-89

6 rows selected.


SQL> SELECT * FROM COURSE;

  COURSENO        CNAME                       DEPT
         1                   DBMS                           COMPUTERS
         2                   CN                                NETWORKS
         3                   SIMULATION              SYSTEMS
         4                   OS                                 COMPUTERS
         5                   DSP                                E&C
         6                   CIRCUITS                     E&E
         7                   SOM                              MECH
         8                   OOP                              COMPUTERS
         9                   DATACOM                  NETWORKS
        10                  MACHINES                  MECH

10 rows selected.


SQL> SELECT * FROM TEXT;

  BOOKISBN                     TITLE                          PUBLISHER    AUTHOR
       101                 DATABASE SYSTEMS           PEARSON       NAVATHE
       102                 OPERATING SYSTEM           TATA                SIBERSCHATZ
       103                  SIMULATIONS                      TATA                JERRY BANKS
       104                   DIP                                         PEARSON        JERRY
       105                  SOM                                       APRESS            DAVID
       106                   DESIGNING                          TATA                GEORGE
       107                  DATACOM                            TATA                 ELMASRI
       108                  CIRCUITS                              PEARSON         TOM

8 rows selected.

SQL> SELECT * FROM ENROLL;

REGNO             COURSENO    SEM     MARKS
2BV10MCA01          1                  3         80
2BV10MCA02          4                  5         70
2BV10MCA03          3                  5         89
2BV10MCA04          5                  7         90
2BV10MCA05          6                  5         76
2BV10MCA06          7                 7          89
2BV10MCA01          4                  5         79
2BV10MCA04          5                  5         80

8 rows selected.

SQL> SELECT * FROM BOOKADOPTION;

  COURSENO        SEM        BOOKISBN
         1                       3                   101
         3                       3                   103
         4                                         102
         5                       5                   104
         6                       7                   108
         7                       5                   105
         9                       3                   107
        10                      3                   106

8 rows selected.


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

SQL> INSERT INTO TEXT(BOOKISBN,TITLE,PUBLISHER,AUTHOR) VALUES(109,'COMPUTER NETWORKS','TATA','FOROUZAN');

1 row created.

SQL> INSERT INTO BOOKADOPTION(COURSENO,SEM,BOOKISBN) VALUES(2,3,109);

1 row created.

SQL> SELECT * FROM TEXT;

  BOOKISBN                     TITLE                      PUBLISHER                    AUTHOR
       101                  DATABASE SYSTEMS         PEARSON                    NAVATHE
       102                 OPERATING SYSTEMS        TATA                            SIBERSCHATZ
       103                 SIMULATIONS                      TATA                            JERRY BANKS
       104                  DIP                                          PEARSON                    JERRY
       105                 SOM                                        APRESS                        DAVID
       106                 DESIGNING                            TATA                            GEORGE
       107                 DATACOM                              TATA                           ELMASRI
       108                 CIRCUITS                                PEARSON                   TOM
       109                 COMPUTER NETWORKS     TATA                            FOROUZAN

9 rows selected.

SQL> SELECT * FROM BOOKADOPTION;

  COURSENO        SEM     BOOKISBN
         1                   3          101
         3                   3          103
         4                   3          102
         5                   5          104
         6                   7          108
         7                   5          105
         9                   3          107
        10                  3          106
         2                   3          109

9 rows selected.




5.      Produce a list of textbooks (include course number, book ISBN, book title) in alphabetical order for courses offered by MCA department that uses more than two books.

SQL> SELECT C.COURSENO COURSE_NO,T.BOOKISBN
  2  BOOKISBN,T.TITLE BOOK_TITLE
  3  FROM BOOKADOPTION B,TEXT T,COURSE C
  4  WHERE B.BOOKISBN=T.BOOKISBN AND
  5  B.COURSENO=C.COURSENO AND C.DEPT='COMPUTERS'
  6  GROUP BY C.COURSENO,T.BOOKISBN,T.TITLE
  7  HAVING COUNT(B.COURSENO)>2;

no rows selected.

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

SQL> SELECT DISTINCT DEPT
  2  FROM COURSE
  3  WHERE COURSENO IN(SELECT COURSENO
  4                                     FROM BOOKADOPTION
  5                                     WHERE BOOKISBN IN(SELECT BOOKISBN
  6                                                                     FROM TEXT
  7                                                                                 WHERE PUBLISHER='PEARSON')
  8                                     );

DEPT
COMPUTERS
E&C
E&E

No comments:

Post a Comment