The Group Projects:

(09-MAY-01)

You will need to make your own group assignments. Help is available if you are having trouble. Click here for group composition guidelines and a list of students.

Submit:

A weekly project progress update sheet, every Friday in class. 3 (three) points will be deducted from overall project marks for each progress report missed or late. The report form is available on-line as a word document.

The project itself including

1	a)	Overall problem description	(5 points)
	b)	Statement of functional requirements. This includes scope of the system (what is and is not in it), things it will need to do, information it must contain, assumptions/simplifications you are making about the world it models and assumptions/simplifications you are making about what it must store. (click here for hints on part 1)	(20 points)
2	a)	An ERM (click here for the ERM checklist and hints) Include at least five entities and five relationships. Make sure there is at least one m:n relationship and a subtype/super type.	(20 points)
3	a)	A relational schema mapping the ERM to tables and constraints. Explain any decisions or assumptions you make during the translation. See here for translation hints.	(15 points)
	b)	DDL commands for ORACLE to implement this schema. These must form a script that can be run automatically. (ie comment out any headings or comments /* */ )	(15 points)
4	a)	INSERT statements to fill the tables with sample data	(5 points)
	b)	Routine queries as would be used for input and output. Explain how each query relates to the original functional requirements. Make at least five. See here for details.	(20 points)

There is no need to submit these parts individually. However, you are always welcome to come and discuss any aspect of the project during office hours. You may wish to get one stage of the project checked before spending too much time on the next one!

As a rough guide, you should try and have your functional requirements ready by the end of week 6. Get the ERM and assumptions ready by the end of week 7, in draft form at least. Then if you have the ERM mapped to tables and the tables implemented in Oracle by the end of week 8, that gives a week and a half to make test data, write your queries and check that everything works. These last steps can be done in parallel – but do make extra sure that everything works before handing it in!

I suggest that you start practicing with ORACLE as soon as possible.

PROJECT SUBMISSION

Please submit both ELECTRONICALLY and ON PAPER. Submit only one copy per group. (exception: graduating seniors may submit a preliminary version separately if necessary).

Both are due before class on Friday June 1 (or by the end of Wednesday 30^th for graduating seniors).

For electronic submission include the following:

Word document holding part 1 and part 3a

ERM as Visio 2000 file, as Word document or as bitmap

Plain text file holding parts 3b, 4a and 4b

Oracle log showing the results of running ALL your SQL (every statement included in 3b, 4a and 4b).

Also submit printouts of all the above, except for the Oracle log.

For class on Friday, June 1 also have ready the final individual progress reports. There will be the regular group progress reports also.

A NOTE ON SYSTEM SCOPE:

In this exercise, you work a system all the way through from an overall problem statement to a finished database populated with test data. It is important that each stage is based on the stage before it. However, for practical reasons you may not be able to implement the entire system. You are therefore allowed to shrink the scope of the system at each stage – as long as you do so explicitly. You might decide to exclude a major area of the problem description, for example plane maintenance information, from your functional requirements. Then you might decide not to include information for many of these requirements in the ERM. Making tables is a lot of work, so you might realize that it won’t be practical to implement tables for the frequent flier program. Then you may find that you would need to write twenty queries to implement all the original requirements for the remaining part of the system, and pick only five for implementation.

All this is fine. Obviously you will get more marks for the implementation of a more ambitious part of the database. But it’s better to select a do-able portion, do it well, and explicitly exclude some stuff then to try and do everything and finish up with a mess. If you did good conceptual design in the ERM and you realize you can’t implement all of it then there is no need to scrub the non-implemented stuff from the ERM – but I suggest you mark it somehow (print in a different color, gray it out, etc.) to show that it will not be implemented.