IV/BCA/402P Student's Copy
2 0 1 8
CBCS
4th Semester
BACHELOR OF COMPUTER APPLICATIONS
Paper No. BCA-402P
Oracle Laboratory
Practical
Full Marks 75
Time 3 hours
The figures in the margin indicate full marks for the questions
SECTION—A
Answer any two ques tions
1. Consider the following relations for an order processing database
application in a company
CUSTOMER (cust:int, cname:string, city:string)
ORDER (order:int, odate:date, cust:int, ord-amt:int)
ORDER_ITEM (order:int, item:int, qty:int)
ITEM (item:int, unitprice:int)
SHIPMENT (order:int, warehouse:int, ship-date:date)
WAREHOUSE (warehouse:int, city:string)
Create the above tables by properly specifying the primary keys and
the foreign keys. 4
Enter at least five tuples for each relation. 3
Produce a listing: CUSTNAME, of orders, AVG_ORDER_AMT, where
the middle column is the total no. of orders by the customer and the
last column is the average order amount for that customer. 5
Find out which unit price is the lowest. 3
/549 1 Contd.
2. Consider the following database of student enrollment in courses and
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, marks:int)
BOOK_ADOPTION (course:int, sem:int, book-ISBN:int)
TEXT (book-ISBN:int, book-title:string, publisher:string, author:string)
Create the above tables by properly specifying the primary keys and
foreign keys. 4
Enter five tuples for each relation. 3
Produce a list of textbooks in alphabetical order for courses offered by
BCA department that uses more than two books. 3
List any department that has all its adopted books published by a
specific publisher. 5
3. 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, city:string)
LOAN (loan-number:int, branch-name:string, loan-number:int)
BORROWER (customer-name:string, customer-street:string, city:string)
Create the above tables by properly specifying the primary and foreign
keys. 4
Enter 5 tuples for each relation. 3
Find all the customers who have at least two accounts at the main
branch. 3
Find the average loan taken by each customer in any branch. 3
Find the lowest and highest balance in account table. 2
SECTION—B
Answer any one ques tion
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:script)
ORDER_DETAILS (order-no:int, book-id:int, quantity:int)
Create the above tables by properly specifying the primary keys and
foreign keys. 4
IV/BCA/402P/549 2 Contd.
Enter at least five tuples for each relation. 3
Find the author of the book which has maximum sales. 3
List all authors whose name begins with a letter 3
Select the order detail ordered by quantity. 4
Show the total and average quantity of book order. 3
5. Create the following three tables 8
Salesman
SNUM SNAME CITY COMMISSION
1001 PIYUSH LONDON 12%
1002 NIRAJ SURAT 13%
1003 MITI LONDON 11%
1004 RAJESH BARODA 15%
1005 ANAND NEW DELHI 10%
1006 RAM PATNA 10%
1007 LAXMAN BOMBAY 09%
SNUM A unique number assigned to each salesman.
SNAME The name of salesman.
CITY The location of salesman.
COMMISSION The salesman commission on order.
Customer
CNUM CNAME CITY RATING SNUM
2001 HARDIK LONDON 100 1001
2002 GITA ROME 200 1003
2003 LAXIT SURAT 200 1002
2004 GOVIND BOMBAY 300 1002
2005 CHANDU LONDON 100 1001
2006 CHAMPAK SURAT 300 1007
2007 PRATIK ROME 100 1004
CNUM A unique number assigned to each customer.
CNAME The name of customer.
CITY The location of customer.
RATING A level of preference indicator given to this
customer.
SNUM A salesman number assigned to this customer.
IV/BCA/402P/549 3 Contd.
Orders
ONUM AMOUNT ODATE CNUM SNUM
3001 18·69 10/03/99 2008 1007
3002 767·19 10/03/99 2001 1001
3003 1900·10 10/03/99 2007 1004
3004 5160·45 10/03/99 2003 1002
3005 1098·25 10/04/99 2008 1007
3006 1713·12 10/04/99 2002 1003
3007 75·75 10/05/99 2004 1002
3008 4723·00 10/05/99 2006 1001
3009 1309·95 10/05/99 2004 1002
3010 9898·87 10/06/99 2006 1001
List all orders for more than R 1000. 4
List all customers whose name begins with a letter 4
Calculate the total of orders for each day. 4
SECTION—C
6. Viva voce. 15
7. Practical record book. 10