Class Assignment 1: Hospital Administration

Schema :

Nurse(NID,Name,Bdate,WID)

Function(FID,Fname,Description)

Ward(WID,Wname,Location)

Services(WID,FID)

Certified(NID,FID)

Queries :

1.print names of nurses not assigned to any ward.

2.print the name of the ward for which no nurse is assigned.

3.for each ward print ward name and no.of services it offers.

4.print the ward with maximum no.of nurses assigned.

5.print the names of nurses whose functions are ensured by the ward to which they are assigned.

6.list the wards that offer all services offered by ward w1.

7.print the name of the most certified nurse.

8.print pairs of nurses assigned to same ward.

9.print the name of wards that ensure each function offered by the hospital.

10.print nurse-id of nurses certified for every function the hospital offers.

11.for each ward print ward-id and nurse-id of most certified nurse.

A Pedagogical tool for Teaching Advanced Database Systems

The designing and implementing database engines may have become a lost art. Although most standard database text books include ample coverage of algorithms for design and implementation database engines, many computer science programs seem to provide minimal coverage of file organizations, theoretical foundations, and algorithms necessary to build a database engine. The systematic removal of “file organizations and information retrieval” as a topic of study coupled with greater emphasis on the so called “practical applications” of databases, have joined hands to eliminate the coverage of theory and implementation of the underlying database engine.


This paper discusses a step by step process by which we in this advanced database course can design and construct a simple, yet fully functional database engine.

Click here to download this paper.

A Simpler (and Better) SQL Approach to Relational Division

A common type of database query requires one to find all tuples of some table that are related to each and every one of the tuples of a second group. In general those queries can be solved using the relational algebra division operator. This paper conveys that the phrasing of this operator in SQL seems to present an overwhelming challenge to novice and experienced database programmers. This paper presents an alternative solution that is not only more intuitive and easier to deliver in the classroom but also exhibits a better computational performance.

Click here to download this paper.

A Relational Model of Data for Large Shared Banks - IBM Research Laboratory

Future users of large data banks must be protected from having to know how the data is organized in the machine (the internal representation). A prompting service which supplies such information is not a satisfactory solution. Activities of users at terminals and most application programs should remain unaffected when the internal representation of data is changed or even when external representation is changed. Changes in data representation will often be needed as a result of changes in query, update etc. Here in this paper we study the inadequacies of the tree structured files or general network data models, and this paper also provides the operations on relations to solve the problems of consistency and redundancy in the user's model.

Click here to download the paper.

Basic Data model proposals

This paper provides a summary of 35 years of data model proposals, grouped into 9 different eras. It discusses the proposals of each era, and show that there are only a few basic data modeling ideas, and most have been around a long time. Later proposal inevitably bear a strong resemblance to certain earlier proposals. Hence, it is a worthwhile exercise to study previous proposals. Here in this paper we study data models in 9 historical epochs;

Click here to download the paper.

Oracle Database Download

To practice SQL queries at home, you could download Oracle 10g Express Edition from here and then follow this tutorial. (Note that the sample database supplied with Oracle 10g is the same database we use in college)

Case Study - Scenario 1 (ER Model)

You have been engaged by a pizza shop to create a database to enable the business to efficiently keep records. The pizza shop sells pizzas to customers who walks in (takeaway) and also takes phone orders which may be delivered to the customer's address or held at the shop until the customer comes in to pick them up. When a customer orders by phone the shop assistant taking the order asks the customer for their phone number and enters it into the computer system. The assistant also enters his ID number so a record is also kept of who took the order. The time when the phone was answered is recorded as well as the time when the phone call was terminated. (From this we can calculate how many orders each assistant took and the average time for each order). If the customer has previously ordered by phone the name(last name only) and address appears on the screen. The customer is then asked for his name and address and then takes the order. If the customer has not ordered before or if the name and address given does not correspond with that in the computer or if that phone number and address is marked as being the subject of a hoax previously, after the order has been taken the assistant dials the number given and confirms the order with the customer. It is required that each order given by a customer be recorded. The price of each item is recorded with the order. Pizzas are made from ingredients. From the database you must be able to determine the name and amount of each ingredient for a particular type of pizza. For each ingredient is kept the amount on hand (in the shop) and the date of the last stocktake. (Stocktakes are done weekly). It should be possible to calculate from the database the total amount of each ingredient used in the making of the pizzas from one stocktake to the next. Any variation from the expected amount is also recorded for that stocktake as a percentage e.g.-1%. For each ingredient we also keep the name and address of the supplier. Each ingredient can be supplied by many suppliers and each supplier can supply many ingredients. Not all pizzas are the same price, so a record exists in database for the current price of each type of pizza. The employees at the shop may be divided into two types: those who work in the shop and those who carry out the deliveries (the drivers). For each employee is kept their name and address, home phone number and tax file number. For the drivers is also kept their driver's license number Hours of work are not regular so for each time an employee works a record must be kept of their hours. Employees inside the shop are paid at an hourly rate. Drivers are paid according to the number of deliveries the do. A record is kept for which orders a driver delivers and of how many deliveries a driver does on each shift.