1. Oracle lab

Oracle8 documentation is available online. Details are as follows.
Server    :   everest
directory :  /oracle/doc
format    :  html
oracle tutorial : /oracle/tutorial/labs
oracle pro-c samples : /oracle/precomp/demo/proc
			/oracle/precomp/demo/sql          

** Do all the exercises given in the .lab files and mail me the answers
 2. ER Modeling exercises 

a) Prepare a detailed ER model for providing detailed cricket 
statistics, which includes information about matches played 
between various countries over the last few decades, the players
included in the teams, the aggregate scores in each innings and 
scores for each player for each of his role (batsman, bowling),
team captain, batting orders, 'man of the match' and other awards,
etc.
b) ER model for on-line stock trading : Brokers are members
of Stock Exchanges (SE). Each broker has clients on whose behalf 
trading is done. Brokers have accounts with SE, and clients have 
accounts with brokers. Buy and sell orders from clients are entered
into the trading system. A trade takes place when buy and sell orders
match (which is done by SE as per specific rules). A 'buy' may be
met by many 'sell' orders and vice versa. Periodically, settlement
is carried out by SE, where payments are made for stocks purchased,
and stocks are delivered by sellers. Usually, brokers must check
that their clients have money or stocks before they put buy or
sell orders.

3. SQL assignment (due date : August 24, 2001)
The classroom tutorial problem : academics database contains the
following tables :

faculty (fno, name, dno, salary, birthdate, NoPubl), key : fno
dept(dno, name, budget, dateStarted, budget), key : dno
courses(cno, name, pre-req, dno), key : cno, pre-req
students(sno, name, hno, birthdate, joinYear, CPI, dno), key : sno
study(sno, cno, grade, year, semester), key : ??
teach(fno, cno, year, semester), key : fno, cno

dno stands for department number. pre-req gives course-no (cno) of
the course which is a pre-requisite of a course.

Exercises :

1. Identify useful domains that can be defined for this database.
2. Identify various integrity constraints (including foreign keys).
Some sample constraints are mentioned below :
  i) a faculy teaches at most 2 courses in a semester
 ii) a course should have at least 4 students
iii) students registering for a course must have obtained a pass
     grade (at least DD) in its pre-requisites
 iv) a student can't register for a course which he has already passed
3. Give schema definition in SQL2 for this database, complete with
domain definitions, table definitions, necessary CHECKs and ASSERTIONs.
4. Write SQLs for the following :
- list students (name, etc) of EE dept staying in hostel 4 and having
age more than 23
- list courses of CSE dept whose pre-requisites are from a non-CSE dept
- list students studying more than 6 courses in this semester (year = 2001
and semester = 1)
- list faculty earning more than his/her dept's average sal
- list students who are from CSE dept but not studying any CSE course
in this sem
- list students who got AA grade in every CSE course they registered
- give 10% increase in salary if a faculty has more than 20 publications
- move students with age > 25 to hostel 1.


 4. ER and Java/JDBC group assignment (due by Aug. 30, 2001)
For the ER assignments given above, chose one as your database design
project, and implement its schema, complete with all reasonable validation
checks, on Oracle DBMS. Also, populate the DB with sample data, and write
one of the application function/task in Java using JDBC for database
connectivity.


 5. Reading and presentation assignments 
You may choose one of the following. Collect a few initial references
on these from me. Some tools are also available for experimentation
in these topics.

a) Directory Systems and databases (particularly, study LDAP, Microsoft
Active Directory)

b) Metadata standards; metadata repositories, the Adex meta-modeling tool
from TCS

c) XML in database systems (refer to Oracle 9i as case study, or
any other DBMS). Look at a few case studies. Cover also a query 
language for XML.

In each, you need to provide detailed presentation (ppt or ps/pdf)
for use by other students for study.