School of Computer Science, McGill University



School of Computer Science, McGill University

COMP-421B Database Systems, Winter 2008

Programming Project 2: Creating your Database

Due date: Mar 3, 13:30

In this assignment you are going to refine your schema and create your database using DB2.

Please note that for this and the next two project assignments you might frequently need to work with the DB2 online information (link from WebCT) in order to figure out how things work in DB2. It is an essential part of the project to learn how to find the needed information in the online help.

In this assignment, you are using the command line processor (CLP) (interactive interface) of DB2 or a quite simple graphical user interface provided by DB2. How to use either CLP or the GUI is described on WebCT.

Assignment (Please turn in one solution per team)

1. (0 Pts) Please attach a copy of your relational schema of assignment#1. If you have modified your schema because of TA feedback (or any other reason), please hand in the modified schema instead; the new design will not be graded but will be used as a basis for the future assignments.

2. (10 Pts) Are all your relations in BCNF-form? If yes, explain why. If not, indicate which are not in BCNF and explain why they are not in BCNF. Do you want to decompose those relations? For each opportunity to combine or decompose relations, decide whether or not to do so, and explain your reasoning briefly (e.g., tell us what queries you expect will be typical for your database, and tell how your design will support their fast execution). If there is anything you still don’t like about the schema (e.g., attribute names, relation structure, duplicated information, etc.), modify the relational schema to something you prefer.

Turn in the decomposition steps, the reasons why you decompose (or not decompose despite existing functional dependencies) and the final relational schema.

3. (0 Pts) Login to your team account at DB2. Try some simple commands to check that the account works fine for you. Tell us immediately if you encounter any problems.

4. (30 Pts) Write an SQL database schema for the relational schema you have designed using the CREATE TABLE command and enter it in the DB2 database. Choose suitable data types for your attributes. Indicate primary keys, foreign keys and any other integrity constraints that you can express with the commands learnt so far. Indicate the constraints you cannot express.

Turn in your CREATE TABLE statements. Furthermore, use the DB2 DESCRIBE command to print the description of the relation on the screen for each of your relations, print the result and turn it in.

5. (10 Pts) Execute five INSERT commands to insert tuples into one of your relations.

Turn in your INSERT statements. Furthermore, print and turn in the response of DB2 when you type the INSERT commands. Print and turn in the result when you issue a “SELECT * FROM relation-name” command.

6. (50 Pts) Develop a substantial amount of data for your database and load it into your relations using the SQL load command. To create the data, write a program (in C, C++ or Java) that creates large files of records in a format acceptable to the loader, then load the data into your project relations. If you are using real data for your project, your program will need to transform the data into files of records conforming to your project schema. Otherwise, you will write a program to generate data: your program will generate either random or nonrandom (e.g., sequential) records conforming to your schema. Note that it is both fine and expected for your data values-strings especially-to be meaningless. The point of generating large amounts of data is that you can experiment with a database of realistic size, rather than the small “toy” databases often used in classes. The data you generate and load should be on the order of:

• Two relations with 4000 to 5000 tuples,

• One additional relation with hundreds of tuples.

Choose relations that are related to each other (the patients/appointments relation that many of you have would be a good example). Your program needs only implement the data generation for three of your relations. For the other relations, you can probably use simple insert statements.

DO NOT ENTER MORE THAN 500 TUPLES IN ANY OTHER RELATION.

When writing a program to fabricate data, there are two important points to keep in mind:

a) Be sure not to generate duplicate values for the set of primary key attributes and attributes for which you have specified UNIQUE.

b) Your project schema almost certainly includes relations that are expected to join with each other. In generating data, be sure to generate values that actually do join – otherwise all of your interesting queries will have empty results! One way to guarantee joinability is to generate the values in one relation, then use the generated values in this relation to select joining values for the other relation.

Turn in your program code for generating or transforming data, a small sample of the records generated for each relation (10 or so records per relation), and a script showing the loading of your data into DB2.

................
................

In order to avoid copyright disputes, this page is only a partial summary.

Google Online Preview   Download