dbms lab manual

Database Application Lab Manual V Semester Department of CSE/ISE JIT Bangalore P a g e | 1 Jyothy Institute of Technology (Affiliated to V.T.U., Belgaum) Off. Kanakpura Road, Tataguni, Bangalore - 560 062 DEPARTMENT OF COMPUTER SCIENCE & ENGINEERING V Semester CSE & ISE Data Base Application Laboratory 10CSL57 Database Application Lab Manual V Semester Department of CSE/ISE JIT Bangalore P a g e | 2 CONTENTS Sl No Program Page N0 1 VTU –PROGRAMS 1.1 Database for Student Information 3 1.2 Database for Airline Flight Information 6 1.3 Database for Student Enrollment for courses and books adopted. 9 1.4 Database for Book-Dealer. 12 1.5 Database for Banking Enterprise. 15 2 ADDITIONAL –PROGRAMS 2.1 Database for Insurance Management. 18 2.2 Database for Order Processing 20 3 LAB SYLLABUS 23 4 VIVA QUESTIONS 26 Database Application Lab Manual V Semester Department of CSE/ISE JIT Bangalore P a g e | 3 VTU PROGR AMS Program No 1. Consider the following relations: STUDENT (snum: integer, sname: string, major: string, level: string, age: integer) CLASS (name: string, meets_at: string, room: string, fid: integer) ENROLLED(snum: integer, cname: string) FACULTY(fid: integer, fname: string, deptid: integer) The meaning of these relations is straightforward; for example, Enrolled has one record per student-class pair such that the student is enrolled in the class. Level is a two character code with 4 different values (Junior: JR, Senior:SR, Super Senior: SS, Fresher: FR) Table Creation CREATE TABLE STUDENT ( SNUM INTEGER NOT NULL, SNAME VARCHA R (15), MAJOR VARCHAR (10), SLEVEL CHAR (2) NOT NULL, AGE NUMBER (3), PRIMARY KEY (SNUM) ); CREATE TABLE FACULTY ( FID INTEGER NOT NULL, FNAME VARCHAR (12) NOT NULL, DEPTID NUMBER (3), PRIMARY KEY (FID) ); CREATE TABLE CLASS ( NAME VARCHAR (12) NOT NULL, MEETS_AT VARCHAR (10), ROOM VARCHAR (5), FID INTEGER, PRIMARY KEY (NAM E), FOREIGN KEY (FID) REFERENCES FACULTY (FID) ); CREATE TABLE ENROLLED ( SNUM INTEGER NOT NULL, CNAME VARCHAR (12) NOT NULL, PRIMARY KEY (SNUM, CNAME), FOREIGN KEY (SNUM) REFERENCES STUDENT (SNUM), FOREIGN KEY (CNAME) REFERENCES CLASS (NAME) ); Tuples/Records Insertion into Tables Database Application Lab Manual V Semester Department of CSE/ISE JIT Bangalore P a g e | 4 INSERT INTO STUDENT VALUES (&SNUM, ‘&SNAME’, ‘&MAJOR’, ‘&SLEVEL’, &AGE); INSERT INTO FACULTY VALUES (&FID, ‘&FNAME’, &DEPTID); INSERT INTO CLASS VALUES (‘&CNAME’, ‘&MEETS_AT’, ‘&ROOM’, &FID); INSERT IN TO ENROLLED VALUES (&SNUM, ‘&CNAME’); Write the following queries in SQL. No duplicates should be printed in any of the answers. i) Find the names of all Juniors (level = JR) who are enrolled in a class taught by Prof. Harshit. SELECT DISTINCT S.SNAME FROM STUDENT S, CLASS C, ENROLLED E, FACULTY F WHERE S.SNUM = E.SNUM AND E.CNAME = C.NAME AND C.FID = F.FID AND F.FNAME = ‘Prof. Harshith’ AND S.LEVEL = ‘JR’; ii) Find the names of all classes that either meet in room R128 or have five or more Students enrolled. SELECT C.NAME FROM CLASS C WHERE C.ROOM = ‘R128’ OR C.NAME IN ( SELECT E.CNAME FROM ENROLLED E GROUP BY E.CNAME HAVING COUNT (*) >= 5); iii) Find the names of all students who are enrolled in two classes that meet at the same time. SELECT DISTINCT S.SNAME FROM STUDENT S WHERE S.SNUM IN ( SELECT E1.SNUM FROM ENROLLED E1, ENROLLED E2, CLASS C1, CLASS C2 WHERE E1.SNUM = E2.SNUM AND E1.CNAME<>E2.CNAME AND E1.CNAME = C1.NAME AND E2.CNAME = C2.NAME AND C1.MEETS_AT= C2.MEETS_AT); iv) Find the names of faculty members who teach in every room in which some class is taught. SELECT DISTINCT F.FNAME FROM FACULTY F WHERE NOT EXISTS ( ( SELECT * FROM CLASS C Database Appl ication Lab Manual V Semester Department of CSE/ISE JIT Bangalore P a g e | 5 EXCEPT ( SELECT C1.ROOM FROM CLASS C1 WHERE C1.FID = F.FID)); v) Find the names of faculty members for whom the combined enrollment of the courses that they teach is less than five. SELECT DISTINCT F.FNAME FROM FACULTY F WHERE 5 > ( SELECT COUNT (E.SNUM) FROM CLASS C, ENROLLED E WHERE C.CNAME = E.CNAME AND C.FID = F.FID); Database Application Lab Manual V Semester Department of CSE/ISE JIT Bangalore P a g e | 6 Program No 2. The following relations keep track of airline flight information: FLIGHTS(no: integer, from: string, to: string, distance: integer, Departs: time, arrives: time, price: real) AIRCRAFT (aid: integer, aname: string, cruisingrange: integer) CERTIFIED (eid: integer, aid: integer) EMPLOYEES(eid: integer, ename: string, salary: integer) Note that the Employees relation describes pilots and other kinds of employees as well; Every pilot is certified for some aircraft, and only pilots are certified to fly. Table Creation CREATE TABLE FLIGHTS ( FLNO INTEGER PRIMARY KEY, FFROM VARCHAR(15) NOT NULL, TTO VARCHAR(15) NOT NULL, DISTANCE INTEGER, DEPARTS TIMESTAMP, ARRIVES TIMESTAMP, PRICE NUMBER(1 0,2) ); CREATE TABLE AIRCRAFT ( AID INTEGER PRIMARY KEY, ANAME VARCHAR(10), CRUISINGRANGE INTEGER ); CREATE TABLE EMPLOYEES ( EID INTEGER PRIMARY KEY, ENAME VARCHAR(15), SALARY NUMBER(10,2) ); CREATE TABLE CERTIFIED ( EID INTEGER NOT NULL, AID INTEGER NOT NULL, PRIMARY KEY (EID, AID), FOREIGN KEY (EID) REFERENCES EMPLOYEES (EID), FOREIGN KEY (A ID) REFERENCES AIRCRAFT (AID) ); Tuples/Records Insertion into Tables INSERT INTO FLIGHTS VALUES (&FLNO, ‘&FFROM’, ‘&TTO’, &DISTANCE, ‘&DEPARTS’, ‘&ARRIVES’, &PRICE); INSERT INTO AIRCRAFT VALUES (&AID, ‘&ANAME’, &CRUISRANGE); INSERT INTO EMPLOYEES VALUES (&EID, ‘&ENAME’, &SALARY); INSERT INTO CERTIFIED VALUES (&EID, &AID); Database Application Lab Manual V Semester Department of CSE/ISE JIT Bangalore P a g e | 7 i) Find the names of aircraft such that all pilots certified to operate them have salaries more than Rs.80,000. SELECT DISTINCT A.ANAME FROM AIRCRAFT A WHERE A.AID IN ( SELECT C.AID FROM CERTIFIED C, EMPLOYEES E WHERE C.EID = E.EID AND NOT EXISTS (SELECT * FROM EMPLOYEES E1 WHERE E1.EID = E.EID AND E1.SALARY < 80000)); ii) For each pilot who is certified for more than three aircrafts, find the eid and the maximum cruisingrange of the aircraft for which she or he is certified. SELECT C.EID, MAX (A.CRUISINGRANGE) FROM CERTIFIED C, AIRCRAFT A WHERE C.AID = A.AID GROUP BY C.EID HAVING COUNT (*) > 3; iii) Find the names of pilots whose salary is less than the price of the cheapest route from Bengaluru to Frankfurt. SELECT DISTINCT E.ANAME FROM EMPLOYEE E WHERE E.SALARY < ( SELECT MIN (F.PRICE) FROM FLIGHTS F WHERE F.FFROM = ‘Bengaluru’ AND F.TTO = ‘Frankfurt’); iv) For all aircraft with cruisingrange over 1000 Kms,. Find the name of the aircraft and the average salary of all pilots certified for this aircraft. Observe that aid is the key for Aircraft, but the question asks for aircraft names; we deal with this complication by using an intermediate relation Temp; SELECT TEMP.NAME, TEMP.AVGSALARY FROM (SELECT A.AID, A.ANAME AS NAME, AVG (E.SALARY) AS AVGSALARY FROM AIRCRAFT A, CERTIFIED C, EMPLOYEES E Database Application Lab Manual V Semester Department of CSE/ISE JIT Bangalore P a g e | 8 WHERE A.AID = C.AID AND C.EID = E.EID AND A.CRUISINGRANGE > 1000 GROUP BY A.AID, A.ANAME) AS TEMP; v) Find the names of pilots certified for some Boeing aircraft. SELECT DISTINCT E.ENAME FROM EMPLOYEES E, CERTIFIED C, AIRCRAFT A WHERE E.EID = C.EID AND C.AID = A.AID AND A.ANAME = ‘Boeing’; vi) Find the aids of all aircraft that can be used on routes from Bengaluru to New Delhi. SELECT A.AID FROM AIRCRAFT A WHERE A.CRUISINGRANGE > (SELECT MIN (F.DISTANCE) FROM FLIGHTS F WHERE F.FFROM = ‘Bengaluru’ AND F.TTO = ‘New Delhi’); Database Application Lab Manual V Semester Department of CSE/ISE JIT Bangalore P a g e | 9 Program No 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. Table Creation 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) ); Database Application Lab Manual V Semester Department of CSE/ISE JIT Bangalore P a g e | 10 ii) Enter at least five tuples for each relation. Tuples/Records Insertion into Tables 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); Database Application Lab Manual V Semester Department of CSE/ISE JIT Bangalore P a g e | 11 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. vii) Create suitable front end for querying and displaying the results. Database Application Lab Manual V Semester Department of CSE/ISE JIT Bangalore P a g e | 12 Program No 4. The following tables are maintained by a book dealer. AUTHOR (author-id:int, name:string, city:string, country:string) PUBLISHER (publisher-id:int, name:string, city:string, country:string) CATALOG (book-id:int, title:string, author-id:int, publisher-id:int, category-id:int, year:int, price:int) CATEGORY (category-id:int, description: string) ORDER-DETAILS (order-no:int, book-id:int, quantity:int) i) Create the above tables by properly specifying the primary keys and the foreign keys. CREATE TABLE AAUTHOR ( AUTHORID INTEGER PRIMARY KEY, NAME VARCHAR(30) NOT NULL, CITY VARCHAR(30) NOT NULL, COUNTRY VARCHAR(30) NOT NULL ); CREATE TABLE PPUBLISHER ( PUBLISHERID INTEGER PRIMARY KEY, NAME VARCHAR(30) NOT NULL, CITY VARCHAR(30) NOT NULL, COUNTRY VARCHAR(30) NOT NULL ); CREATE TABLE BOOKCATEGORY ( CATEGORYID INTEGER PRIMARY KEY, DESCRIPTION VARCHAR(30) NOT NULL ); CREATE TABLE CCATALOG ( BOOKID INTEGER PRIMARY KEY, TITLE VARCHAR(30) NOT NULL, AUTHORID INTEGER NOT NULL, PUBLISHERID INTEGER NOT NULL, CATEGORYID INTEGER NOT NULL, YEAROFPUBLISH INTEGER NOT NULL, PRICE INTEGER NOT NULL, FOREIGN KEY (AUTHORID) REFERENCES AAUTHOR(AUTHORID), FOREIGN KEY (PUBLISHERID) REFERENCES PPUBLISHER(PUBLISHERID), FOREIGN KEY (CATEGORYID) REFERENCES BOOKCATEGORY(CATEGORYID) ); CREATE TABLE OORDERDETAILS ( ORDERNO INTEGER PRIMARY KEY, BOOKID INTEGER NOT NULL, QUANTITY INTEGER NOT NULL, FOREIGN KEY (BOOKID) REFERENCES CCATALOG(BOOKID) ); Database Application Lab Manual V Semester Department of CSE/ISE JIT Bangalore P a g e | 13 ii) Enter at least five tuples for each relation. Tuples/Records Insertion into Tables INSERT INTO AAUTHOR VALUES (1,'NAVATHE','ARLINGTON','USA'); INSERT INTO AAUTHOR VALUES (2,'RAGHU RAMAKRISHNAN','CALIFORNIA','USA'); INSERT INTO AAUTHOR VALUES (3,'DHAMDHERE','MUMBAI','INDIA'); INSERT INTO AAUTHOR VALUES (4,'BJARNE','NEW JERSY','USA'); INSERT INTO AAUTHOR VALUES (5,'TANENBAUM','AMSTERDAM','NETHERLAND'); INSERT INTO PPUBLISHER VALUES (1,'JOHN WILEY','NEW YORK','USA'); INSERT INTO PPUBLISHER VALUES (2,'PEARSON','BANGALORE','INDIA'); INSERT INTO PPUBLISHER VALUES (3,'O REILLY','NEW JERSY','USA'); INSERT INTO PPUBLISHER VALUES (4,'TMH','CALCUTTA','INDIA'); INSERT INTO PPUBLISHER VALUES (5,'JOHN WILEY','NEW DELHI','INDIA'); INSERT INTO BOOKCATEGORY VALUES (1,'DATABASE MANAGEMENT'); INSERT INTO BOOKCATEGORY VALUES (2,'OPERATING SYSTEMS'); INSERT INTO BOOKCATEGORY VALUES (3,'C++'); INSERT INTO BOOKCATEGORY VALUES (4,'COMPUTER NETWORKS'); INSERT INTO BOOKCATEGORY VALUES (5,'C'); INSERT INTO CCATALOG VALUES (1,'FUNDAMENTALS OF DBMS',1,2,1,2004,500); INSERT INTO CCATALOG VALUES (2,'PRINCIPLES OF DBMS',2,1,1,2004,400); INSERT INTO CCATALOG VALUES (3,'OPERATING SYSTEMS',3,4,2,2004,200); INSERT INTO CCATALOG VALUES (4,'C++ BIBLE',4,5,3,2003,500); INSERT INTO CCATALOG VALUES (5,'COMPUTER NETWORKS',5,3,4,2002,250); INSERT INTO CCATALOG VALUES (6,'FUNDAMENTALS OF C',1,2,5,2004,700); INSERT INTO CCATALOG VALUES (7,'OPERATING SYSTEMS 2',3,2,2,2001,600); INSERT INTO OORDERDETAILS VALUES (1,1,1); INSERT INTO OORDERDETAILS VALUES (2,2,1); INSERT INTO OORDERDETAILS VALUES (3,3,1); INSERT INTO OORDERDETAILS VALUES (4,4,1); INSERT INTO OORDERDETAILS VALUES (5,5,1); INSERT INTO OORDERDETAILS VALUES (6,6,7); INSERT INTO OORDERDETAILS VALUES (7,7,9); iii) Give the details of the authors who have 2 or more books in the catalog and the price of the books is greater than the average price of the books in the catalog and the year of publication is after 2000. SELECT * FROM AAUTHOR A WHERE EXISTS ( SELECT A1.AUTHORID,COUNT(A1.AUTHORID) FROM AAUTHOR A1,CCATALOG C WHERE A1.AUTHORID=C.AUTHORID AND Database Application Lab Manual V Semester Department of CSE/ISE JIT Bangalore P a g e | 14 A.AUTHORID=A1.AUTHORID AND C.YEAROFPUBLISH > 2000 AND C.PRICE > (SELECT AVG(PRICE) FROM CCATALOG) GROUP BY A1.AUTHORID HAVING COUNT(A1.AUTHORID) >=2); iv) Find the author of the book which has maximum sales. SELECT DISTINCT A.NAME FROM AAUTHOR A, CCATALOG C, OORDERDETAILS ODM WHERE A.AUTHORID=C.AUTHORID AND ODM.BOOKID=C.BOOKID AND EXISTS ( SELECT OD.BOOKID,SUM(OD.QUANTITY) FROM OORDERDETAILS OD WHERE OD.BOOKID=ODM.BOOKID GROUP BY BOOKID HAVING SUM(OD.QUANTITY)>= ALL (SELECT SUM(QUANTITY) FROM OORDERDETAILS GROUP BY BOOKID)); v)Demonstrate how you increase the price of books published by a specific publisher by 10%. UPDATE CCATALOG SET PRICE = (1.1) * PRICE WHERE AUTHORID = ( SELECT AUTHORID FROM AAUTHOR WHERE NAME = 'NAVATHE'); vi) Generate suitable reports. vii) Create suitable front end for querying and displaying the results. Database Application Lab Manual V Semester Department of CSE/ISE JIT Bangalore P a g e | 15 Program No 5. Consider the following database for a banking enterprise BRANCH(branch-name:string, branch-city:string, assets:real) ACCOUNT(accno:int, branch-name:string, balance:real) DEPOSITOR(customer-name:string, accno:int) CUSTOMER(customer-name:string, customer-street:string, customer-city:string) LOAN(loan-number:int, branch-name:string, amount:real) BORROWER(customer-name:string, loan-number:int) i)Create the above tables by properly specifying the primary keys and the foreign keys CREATE TABLE BBRANCH ( BRANCHNAME VARCHAR(30) PRIMARY KEY, BRANCHCITY VARCHAR(30) NOT NULL, ASSETS NUMBER(10,2) NOT NULL ); CREATE TABLE BBANKACCOUNT ( ACCNO NUMBER(5) PRIMARY KEY, BRANCHNAME VARCHAR(30) NOT NULL, BALANCE NUMBER(10,2), FOREIGN KEY (BRANCHNAME) REFERENCES BBRANCH (BRANCHNAME) ); CREATE TABLE BBANKCUSTOMER ( CUSTOMERNAME VARCHAR(30) PRIMARY KEY, CUSTOMERSTREET VARCHAR(30) NOT NULL, CUSTOMERCITY VARCHAR(30) NOT NULL ); CREATE TABLE DDEPOSITOR ( CUSTOMERNAME VARCHAR(30) NOT NULL, ACCNO NUMBER(5) NOT NULL, PRIMARY KEY (CUSTOMERNAME, ACCNO), FOREIGN KEY (CUSTOMERNAME) REFERENCES BBANKCUSTOMER (CUSTOMERNAME), FOREIGN KEY (ACCNO) REFERENCES BBANKACCOUNT (ACCNO) ON DELETE CASCADE ); CREATE TABLE LLOAN ( LOANNUMBER INTEGER PRIMARY KEY, BRANCHNAME VARCHAR(30) NOT NULL, AMOUNT NUMBER(10,2) NOT NULL, FOREIGN KEY (BRANCHNAME) REFERENCES BBRANCH (BRANCHNAME) ); CREATE TABLE BBORROWER ( CUSTOMERNAME VARCHAR(30) NOT NULL, LOANNUMBER INTEGER NOT NULL, PRIMARY KEY (CUSTOMERNAME, LOANNUMBER), FOREIGN KEY (CUSTOMERNAME) REFERENCES BBANKCUSTOMER (CUSTOMERNAME), Database Application Lab Manual V Semester Department of CSE/ISE JIT Bangalore P a g e | 16 FOREIGN KEY (LOANNUMBER) REFERENCES LLOAN (LOANNUMBER) ); ii) Enter at least five tuples for each relation Tuples/Records Insertion into Tables INSERT INTO BBRANCH VALUES('CHAMRAJPET','BANGALORE',50000); INSERT INTO BBRANCH VALUES('RESIDENCY ROAD','BANGALORE',10000); INSERT INTO BBRANCH VALUES('M G ROAD','BANGALORE',100000); INSERT INTO BBRANCH VALUES('CP','DELHI',100000); INSERT INTO BBRANCH VALUES('JANTARMANTAR','DELHI',100000); INSERT INTO BBANKACCOUNT VALUES(1,'CHAMRAJPET',2000); INSERT INTO BBANKACCOUNT VALUES(2,'RESIDENCY ROAD',5000); INSERT INTO BBANKACCOUNT VALUES(3,'M G ROAD',6000); INSERT INTO BBANKACCOUNT VALUES(4,'CP',9999); INSERT INTO BBANKACCOUNT VALUES(5,'JANTARMANTAR',999); INSERT INTO BBANKACCOUNT VALUES(6,'M G ROAD',999); INSERT INTO BBANKACCOUNT VALUES(8,'RESIDENCY ROAD',999); INSERT INTO BBANKACCOUNT VALUES(9,'CP',10000); INSERT INTO BBANKACCOUNT VALUES(10,'RESIDENCY ROAD',5000); INSERT INTO BBANKACCOUNT VALUES(11,'JANTARMANTAR',9999); INSERT INTO BBANKCUSTOMER VALUES('ANNE','BULL TEMPLE ROAD','BANGALORE'); INSERT INTO BBANKCUSTOMER VALUES('DANNY','BANNERGATTA ROAD','BANGALORE'); INSERT INTO BBANKCUSTOMER VALUES('TOM','J C ROAD','BANGALORE'); INSERT INTO BBANKCUSTOMER VALUES('NICK','CP','DELHI'); INSERT INTO BBANKCUSTOMER VALUES('ROVER','JANTARMANTAR','DELHI'); INSERT INTO DDEPOSITOR VALUES('ANNE',1); INSERT INTO DDEPOSITOR VALUES('DANNY',2); INSERT INTO DDEPOSITOR VALUES('TOM',3); INSERT INTO DDEPOSITOR VALUES('NICK',4); INSERT INTO DDEPOSITOR VALUES('ROVER',5); INSERT INTO DDEPOSITOR VALUES('ANNE',6); INSERT INTO DDEPOSITOR VALUES('ANNE',8); INSERT INTO DDEPOSITOR VALUES('NICK',9); INSERT INTO DDEPOSITOR VALUES('DANNY',10); INSERT INTO DDEPOSITOR VALUES('NICK',11); INSERT INTO LLOAN VALUES(1,'CHAMRAJPET',1000); INSERT INTO LLOAN VALUES(2,'RESIDENCY ROAD',2000); INSERT INTO LLOAN VALUES(3,'M G ROAD',3000); INSERT INTO LLOAN VALUES(4,'CP',4000); INSERT INTO LLOAN VALUES(5,'JANTARMANTAR',5000); INSERT INTO BBORROWER VALUES('ANNE',1); INSERT INTO BBORROWER VALUES('ANNE',2); INSERT INTO BBORROWER VALUES('TOM',3); Database Application Lab Manual V Semester Department of CSE/ISE JIT Bangalore P a g e | 17 INSERT INTO BBORROWER VALUES('NICK',4); INSERT INTO BBORROWER VALUES('ROVER',5); iii) Find all the customers who have at least two accounts at the Main branch. SELECT * FROM BBANKCUSTOMER C WHERE EXISTS ( SELECT DP.CUSTOMERNAME, COUNT (DP.CUSTOMERNAME) FROM DDEPOSITOR DP,BBANKACCOUNT BA WHERE DP.ACCNO=BA.ACCNO AND C.CUSTOMERNAME=DP.CUSTOMERNAME AND BA.BRANCHNAME='RESIDENCY ROAD' GROUP BY DP.CUSTOMERNAME HAVING COUNT(DP.CUSTOMERNAME)>=2); iv) Find all the customers who have an account at all the branches located in a specific city. SELECT * FROM BBANKCUSTOMER BC WHERE NOT EXISTS ( SELECT BRANCHNAME FROM BBRANCH WHERE BRANCHCITY='DELHI' MINUS SELECT BA.BRANCHNAME FROM DDEPOSITOR D,BBANKACCOUNT BA WHERE D.ACCNO=BA.ACCNO AND BC.CUSTOMERNAME=D.CUSTOMERNAME ); v) Demonstrate how you delete all account tuples at every branch located in a specific city. DELETE FROM BBANKACCOUNT WHERE BRANCHNAME IN ( SELECT BRANCHNAME FROM BBRANCH WHERE BRANCHCITY='BANGALORE'); vi) Generate suitable reports. vii) Create suitable front end for querying and displaying the results. Database Application Lab Manual V Semester Department of CSE/ISE JIT Bangalore P a g e | 18 ADDITIONAL PROGRAMS Program No 1. Consider the Insurance database given below.The primary keys are underlined and the data types are specified PERSON(driver_id:string, name :string, address:stringl) CAR(regno :string , model:string , year:int) ACCIDENT(rptno:int ,date :date , location :string) OWNS(driver_id:string , regno :string) PARTICIPATED(driver_id :string , regno:string , rptno:int , damageamt:int) i)Create the above tables by properly specifying the primary keys and the foreign keys CREATE TABLE PERSON ( DRIVER_ID VARCHAR(10) PRIMARY KEY, NAME VARCHAR(10) , ADDRESS VARCHAR(10) ); CREATE TABLE CAR ( REG_NO VARCHAR(10) PRIMARY KEY, MODEL VARCHAR(10) , YEAR NUMBER(4) ); CREATE TABLE ACCIDENT1 ( REPT_NO NUMBER(10) PRIMARY KEY, DAMAGE_DATE DATE, LOC VARCHAR(20) ); CREATE TABLE OWNS ( DRIVER_ID VARCHAR(10) , REG_NO VARCHAR(10) , PRIMARY KEY (DRIVER_ID, REG_NO), FOREIGN KEY (DRIVER_ID ) REFERENCES PERSON (DRIVER_ID), FOREIGN KEY (REG_NO) REFERENCES CAR (REG_NO) ); CREATE TABLE PARTICIPATED ( DRIVER_ID VARCHAR(10) , REG_NO VARCHAR(10), REP_NO NUMBER(10), DAMAGE_AMT NUMBER(10,2), PRIMARY KEY (DRIVER_ID,REG_NO,REPT_NO), FOREIGN KEY (DRIVER_ID) REFERENCES PERSON(DRIVER_ID), FOREIGN KEY (REG_NO) REFERENCES CAR (REG_NO), FOREIGN KEY (REPT_NO) REFERENCES ACCIDENT1(REPT_NO) ); Database Application Lab Manual V Semester Department of CSE/ISE JIT Bangalore P a g e | 19 ii) Enter at least five tuples for each relation Tuples/Records Insertion into Tables INSERT INTO PERSON VALUES (‘P001, 'SUMA',‘BANGALORE’); INSERT INTO PERSON VALUES (‘P004’,’SUMA','BANGALORE); INSERT INTO CAR VALUES ('R001',’FORD’, 2004); INSERT INTO CAR VALUES ('R002',’FORD’, 2004); INSERT INTO ACCIDENT1 VALUES (01,'12-DEC-2004','BANGALORE'); INSERT INTO ACCIDENT1 VALUES (12,'12-DEC-2004','BANGALORE'); INSERT INTO OWNS VALUES('P001',’R001’); INSERT INTO OWNS VALUES('P004',’R002’); INSERT INTO PARTICIPATED VALUES ('P001',’R001’,01,2500.00); INSERT INTO PARTICIPATED VALUES ('P004',’R002’,12,2500.00); INSERT INTO PARTICIPATED VALUES ('P001',’R001’,01,2500.00); iii) Update the damage amount for the car with a specified REG_NO in the accident with REG_NO 12 to 25000. UPDATE PARTICIPATED SET DAMAGE_AMT =25000 WHERE REG_NO=’R001’ AND REP_NO=1; iv) Add a new accident to the database INSERT INTO ACCIDENT1 VALUES (23,'28-MAY-2006','MANGALORE'); v) Find the total number of people who owned cars that were involved in the accidents in 2002 SELECT * FROM PARTICIPATED,ACCIDENT1 A WHERE PARTICIPATED.REP_NO=A.REP_NO AND DAMAGE_DATE BETWEEN ’01-JAN-2002’ AND ’31-DEC-2002'); vi) Find the number of accidents in which cars belonging to a specific model were involved. SELECT CAR.MODEL,COUNT(PARTICIPATED.REP_NO) FROM CAR , PARTICIPATED WHERE PARTICIPATED.REG_NO=CAR.REG_NO GROUP BY MODEL Database Application Lab Manual V Semester Department of CSE/ISE JIT Bangalore P a g e | 20 Program No 2. Consider the following relations for an order processing database application in a company. CUSTOMER(cust_id:int, cname :string, city: string) ORDER(order_id :int , odate :date , cust_id : int ,ord_amt:int) ORDER_ITEM(order_id:int ,item_id :int ,qty :int) ITEM(item_id:int, unit_price :int) SHIPMENT(order_id :int ,warehouse_id :int ,ship_date :date) WAREHOUSE(warehouse_id :int,city:string) i)Create the above tables by properly specifying the primary keys and the foreign keys CREATE TABLE CUSTOMER ( CUST_ID NUMBER(10) PRIMARY KEY, CNAME VARCHAR(10) , CITY VARCHAR(10) ); CREATE TABLE ORDER ( ORDER_ID NUMBER(10) PRIMARY KEY, ODATE DATE, CUST_ID NUMBER(10), ORDER_AMT NUMBER (10), FOREIGN KEY (CUST_ID) REFERENCES CUSTOMER (CUST_ID) ); CREATE TABLE ITEM ( ITEM_ID NUMBER (10) PRIMARY KEY , UNIT_PRICE NUMBERD(10), ); CREATE TABLE ORDER_ITEM ( ORDER_ID NUMBER (10) , ITEM_ID NUMBERD(10), QTY NUMBER(10), PRIMARY KEY(ORDER_ID,ITEM_ID), FOREIGN KEY(ORDER_ID) REFERENCES ORDER(ORDER_ID), FOREIGN KEY(ITEM_ID) REFERENCES ITEM (ITEM_ID), ); CREATE TABLE WAREHOUSE ( WAREHOUSE_ID NUMBER(10) PRIMARY KEY , CITY VARCHAR(10) , ); CREATE TABLE SHIPMENT ( ORDER_ID NUMBER(10) , WAREHOUSE_ID NUMBER(10), SHIP_DATE DATE), PRIMARY KEY (ORDER_ID,WAREHOUSE_ID), FOREIGN KEY (ORDER_ID) REFERENCES ORDER(ORDER_ID), FOREIGN KEY (WAREHOUSE_ID) REFERENCES WAREHOUSE(WAREHOUSE_ID), ); Database Application Lab Manual V Semester Department of CSE/ISE JIT Bangalore P a g e | 21 ii) Enter at least five tuples for each relation Tuples/Records Insertion into Tables INSERT INTO CUSTOMER VALUES (1, 'SUMA',‘BANGALORE’); INSERT INTO CUSTOMER VALUES (2, 'SWETHA',‘BANGALORE’); INSERT INTO CUSTOMER VALUES (3, 'SMITHA',‘BANGALORE’); INSERT INTO CUSTOMER VALUES (4, 'SHULAJA',‘MANGALORE’); INSERT INTO CUSTOMER VALUES (5, 'SUMA',‘MANGALORE’); INSERT INTO ORDER VALUES (1,'12-DEC-2005',1,123.9); INSERT INTO ORDER VALUES (2,'12-JUN-2005',1,1000); INSERT INTO ORDER VALUES (3,'12-JUN-2005',1,2345'); INSERT INTO ORDER VALUES (4,'03-MAY-2005',2,1234); INSERT INTO ORDER VALUES (5,'04-MAY-2005',3,12344); INSERT INTO ITEM VALUES (2, 65756); INSERT INTO ITEM VALUES (13, 5676); INSERT INTO ITEM VALUES (14, 7645’); INSERT INTO ITEM VALUES (15, 7678); INSERT INTO ITEM VALUES (16, 8990); INSERT INTO ORDER_ITEM VALUES(1,2,23); INSERT INTO ORDER_ITEM VALUES(2,13,45); INSERT INTO ORDER_ITEM VALUES(2,14,45); INSERT INTO ORDER_ITEM VALUES(2,15,34); INSERT INTO ORDER_ITEM VALUES(3,13,23); INSERT INTO WAREHOUSE VALUES (33,‘BANGALORE’); INSERT INTO WAREHOUSE VALUES (34 ,‘BANGALORE’); INSERT INTO WAREHOUSE VALUES (35,‘BANGALORE’); INSERT INTO WAREHOUSE VALUES (36,‘MANGALORE’); INSERT INTO WAREHOUSE VALUES (37,‘MANGALORE’); INSERT INTO SHIPMENT VALUES (1,33, '12-MAY-2005'); INSERT INTO SHIPMENT VALUES (1,36, '23-MAY-2005'); INSERT INTO SHIPMENT VALUES (1,34, '24-MAY-2005'); INSERT INTO SHIPMENT VALUES (1,35, '25-MAY-2005'); INSERT INTO SHIPMENT VALUES (1,35, '12-JUN-2005'); iii) Produce a listing: Customer Name,No of Orders,AVG_ORDER_AMT where the middle column is the total number of orders by the customer and the last column is the Average Order Amount for the customer. SELECT C.CNAME,COUNT(ORDER_AMT) AS TOTAL_NO_OF_ORDERS ,AVG(ORDER_AMT) AS AVG_ORDER_AMT FROM CUSTOMER C,ORDER R WHERE C.CUST_ID=R.CUST_IDGROUP BY C.CNAME iv) List the order_id for orders that were shipped from all the warehouses that the company has in a city. Database Application Lab Manual V Semester Department of CSE/ISE JIT Bangalore P a g e | 22 SELECT O.ORDER_ID FROM ORDER O,SHIPMENT S,WAREHOUSE W WHERE W.WAREHOUSE_ID=ALL( SELECT DISTINCT W.WAREHOUSE_ID FROM ORDER O,WAREHOUSE W.SHIPMENT S WHERE W.CITY=’BANGALORE’ AND W.WAREHOUSE_ID=S.WAREHOUSE_ID AND O.ORDER_ID= S.ORDER_ID); Database Application Lab Manual V Semester Department of CSE/ISE JIT Bangalore P a g e | 23 DATABASE APPLICATION LABORATORY-SYLLABUS Subject Code: 10CSL57 I.A. Marks : 25 Hours/Week : 03 Exam Hours: 03 Total Hours : 42 Exam Marks: 50 1. Consider the following relations: STUDENT (snum: integer, sname: string, major: string, level: string, age: integer) CLASS (name: string, meets at: string, room: string, fid: integer) ENROLLED (snum: integer, cname: string) FACULTY (fid: integer, fname: string, deptid: integer) The meaning of these relations is straightforward; for example, Enrolled has one record per student-class pair such that the student is enrolled in the class. Level is a two character code with 4 different values (example: Junior: JR etc) Write the following queries in SQL. No duplicates should be printed in any of the answers. i. Find the names of all Juniors (level = JR) who are enrolled in a class taught by Prof. Harshith ii. Find the names of all classes that either meet in room R128 or have five or more Students enrolled. iii. Find the names of all students who are enrolled in two classes that meet at the same time. iv. Find the names of faculty members who teach in every room in which some class is taught. v. Find the names of faculty members for whom the combined enrollment of the courses that they teach is less than five. 2. The following relations keep track of airline flight information: FLIGHTS (no: integer, from: string, to: string, distance: integer, Departs: time, arrives: time, price: real) AIRCRAFT (aid: integer, aname: string, cruisingrange: integer) CERTIFIED(eid: integer, aid: integer) EMPLOYEES(eid: integer, ename: string, salary: integer) Note that the Employees relation describes pilots and other kinds of employees as well; Every pilot is certified for some aircraft, and only pilots are certified to fly. Write each of the following queries in SQL: i. Find the names of aircraft such that all pilots certified to operate them have salaries more than Rs.80, 000. ii. For each pilot who is certified for more than three aircrafts, find the eid and the maximum cruisingrange of the aircraft for which she or he is certified. iii. Find the names of pilots whose salary is less than the price of the cheapest route from Bengaluru to Frankfurt. iv. For all aircraft with cruisingrange over 1000 Kms,. Find the name of the aircraft and the average Database Application Lab Manual V Semester Department of CSE/ISE JIT Bangalore P a g e | 24 salary of all pilots certified for this aircraft. v. Find the names of pilots certified for some Boeing aircraft. vi. Find the aids of all aircraft that can be used on routes from Bengaluru to New Delhi. 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. 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. v. List any department that has all its adopted books published by a specific publisher. vi. Generate suitable reports. vii.Create suitable front end for querying and displaying the results. 4. The following tables are maintained by a book dealer. AUTHOR (author-id:int, name:string, city:string, country:string) PUBLISHER (publisher-id:int, name:string, city:string, country:string) CATALOG (book-id:int, title:string, author-id:int, publisher-id:int, category-id:int, year:int, price:int) CATEGORY (category-id:int, description:string) ORDER-DETAILS (order-no:int, book-id:int, quantity:int) 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.Give the details of the authors who have 2 or more books in the catalog and the price of the books is greater than the average price of the books in the catalog and the year of publication is after 2000. iv. Find the author of the book which has maximum sales. v. Demonstrate how you increase the price of books published by a specific publisher by 10%. vi. Generate suitable reports. vii.Create suitable front end for querying and displaying the results. Database Application Lab Manual V Semester Department of CSE/ISE JIT Bangalore P a g e | 25 5. Consider the following database for a banking enterprise BRANCH(branch-name:string, branch-city:string, assets:real) ACCOUNT(accno:int, branch-name:string, balance:real) DEPOSITOR(customer-name:string, accno:int) CUSTOMER(customer-name:string, customer-street:string, customer-city:string) LOAN(loan-number:int, branch-name:string, amount:real) BORROWER(customer-name:string, loan-number:int) 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. Find all the customers who have at least two accounts at the Main branch. iv. Find all the customers who have an account at all the branches located in a specific city. v. Demonstrate how you delete all account tuples at every branch located in a specific city. vi. Generate suitable reports. vii. Create suitable front end for querying and displaying the results. Instructions: 1. The exercises are to be solved in an RDBMS environment like Oracle or DB2. 2. Suitable tuples have to be entered so that queries are executed correctly. 3. Front end may be created using either VB or VAJ or any other similar tool. 4. The student need not create the front end in the examination. The results of the queries may be displayed directly. 5. Relevant queries other than the ones listed along with the exercises may also be asked in the examination. 6. Questions must be asked based on lots. Database Application Lab Manual V Semester Department of CSE/ISE JIT Bangalore P a g e | 26 VIVA QUESTIONS WITH ANSWERS 1. What is database? 2. What is DBMS? 3. What is a Database system? 4. Advantages of DBMS? 5. Disadvantage in File Processing System? 6. Describe the three levels of data abstraction? 7. Define the "integrity rules" 8. What is extension and intension? 9. What is System R? What are its two major subsystems? 10. How is the data structure of System R different from the relational structure? 11. What is Data Independence? 12. What is a view? How it is related to data independence? 13. What is Data Model? 14. What is E-R model? 15. What is Object Oriented model? 16. What is an Entity? 17. What is an Entity type? 18. What is an Entity set? 19. What is an Extension of entity type? 20. What is Weak Entity set? 21. What is an attribute? 22. What is a Relation Schema and a Relation? 23. What is degree of a Relation? 24. What is Relationship? 25. What is Relationship set? 26. What is Relationship type? 27. What is degree of Relationship type? 28. What is DDL (Data Definition Language)? 29. What is VDL (View Definition Language)? 30. What is SDL (Storage Definition Language)? 31. What is Data Storage - Definition Language? 32. What is DML (Data Manipulation Language)? 33. What is DML Compiler? 34. What is Query evaluation engine? Database Application Lab Manual V Semester Department of CSE/ISE JIT Bangalore P a g e | 27 35. What is DDL Interpreter? 36. What is Record-at-a-time? 37. What is Set-at-a-time or Set-oriented? 38. What is Relational Algebra? 39. What is Relational Calculus? 40. How does Tuple-oriented relational calculus differ from domain-oriented relational calculus? 41. What is normalization? 42. What is Functional Dependency? 43. When is a functional dependency F said to be minimal? 44. What is multivalued dependency? 45. What is Lossless join property? 46. What is 1 NF (Normal Form)? 47. What is Fully Functional dependency? 48. What is 2NF? 49. What is 3NF? 50. What is BCNF (Boyce-Codd Normal Form)? 51. What is 4NF? 52. What is 5NF? 53. What is Domain-Key Normal Form? 54. What are partial, alternate, artificial, compound and natural key? 55. What is indexing and what are the different kinds of indexing? 56. What is system catalog or catalog relation? How is better known as? 57. What is meant by query optimization? 58. What is join dependency and inclusion dependency? Join Dependency: 59. What is durability in DBMS? 60. What do you mean by atomicity and aggregation? 61. What is a Phantom Deadlock? 62. What is a checkpoint and when does it occur? 63. What are the different phases of ARIES Algorithm transaction? 64. What do you mean by flat file database? 65. What is "transparent DBMS"? Database Application Lab Manual V Semester Department of CSE/ISE JIT Bangalore P a g e | 28 66. Brief theory of Network, Hierarchical schemas and their properties 67. What is a query? 68. What do you mean by Correlated sub query? 69. What are the primitive operations common to all record management systems? 70. Name the buffer in which all the commands that are typed in are stored 71. What are the unary operations in Relational Algebra? 72. Are the resulting relations of PRODUCT and JOIN operation the same? 73. What is RDBMS KERNEL? 74. Name the sub-systems of a RDBMS. 75. Which part of the RDBMS takes care of the data dictionary? How? 76. What is the job of the information stored in data-dictionary? 77. Not only RDBMS takes care of locating data it also determines an optimal access path to store or retrieve the data. 78. How do you communicate with an RDBMS? 79. Define SQL and state the differences between SQL and other conventional programming Languages should be performed rather than how to perform them. 80. Name the three major set of files on disk that compose a database in Oracle 81. Spurious tuples may occur due to 82. Tables derived from the ERD 83. What are database files, control files and log files? How many of these files should a database have at least? Why? 84. What is ROWID? 85. What is database Trigger? 86. Name two utilities that Oracle provides, which are use for backup and recovery. 87. What are stored-procedures? What are the advantages of using them?

Editors