ISYS210 Introduction to Database Systems   

Instructor: Dr. Hu               Group Project                30%

The goal of this project is to apply the database theory concepts you learn in this course, to develop a database application from the design to the implementation stage. This is a group project, a group project will help you gain valuable experience to work and collaborate with other members on a large project. You will model, design and implement a database application (DA) for a real-world scenario of your choosing. You will design schemas for the database, and you will create an actual database using Oracle9i/8i as the relational database management system. You will populate the database with sample data, write interactive queries and modifications on the database.

This document describes the project steps, guidelines/policies.

Project Steps:

• Step 01: Group formation
• Step 02: Database Application (DA) Selection
• Step 03: Conceptual and Logical Design of DA.
• Step 04: Data Creation using Insert (or Bulk-Loading using SQL Load, if you want to learn how to use SQL Load, please discuss it with me)
• Step 05: Final Implementation
• Step 06: Documentation

Step 01: Group Formation
Each group should consist of 3 to 4 members. You choose the group members at your own discretion. If you have problems forming your group e-mail me by the end of the second week of the lecture. The grade for each member of the team will be based on the Peer Evaluations.

Step 02 : Database Application Selection
Pick an application that your whole group will enjoy working with, be creative! Students can build a database for a supermarket, car dealer, shoe company, software company, Video chain, etc. Try to pick an application that is relatively substantial, but not too enormous. For example, when your EER diagram is expressed in a relational schema your design should consist at least 6-9 relations.

Project Proposal : Due by the end of the 4^th week

The project proposal should contain the following information:

• Title of your Project.
• A description and an outline of a possible application program for your DA. Your description should be brief and relatively informal. If there are any unique or particularly difficult aspects of your proposed application, please point them out.
• A clear description of the "data requirements" and "transaction requirements" for your company using the same format as described by the DreamHome case study. (Handout)
• List of Team Members using the format described in Submission Guidelines.

Step 03: Conceptual and Logical Design of DA.

Begin this step as soon as you finish working on Step 02. Do not wait till you receive your graded proposals back.

• Specify an entity-relationship diagram for your proposed database. As always, don't forget to underline key attributes and include cardinality constraints. You should certainly include different kinds of relationships (e.g., many-one, many-many) and different kinds of data (strings, integers, etc.), but your application need not necessarily require advanced EER features or weak entity sets.
• Use the method for translating an E/R diagram to relations described in class and handout to produce a set of relations for your database design. Again, be sure to underline key attributes of your chosen database schema.

Step 04: Data Creation and Loading

You will create and load data for your database. If you are using real data for your database, write a program to transform the data into files of records

conforming to your DA schema. When inserting or loading data, there are two important points to keep in mind:
(1) generate unique values for key attributes.

(2) consider relations that are expected to join with each other. For example, you may have an Employee relation with attribute DeptNo that's expected to join with attribute DNO in relation Department.     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 one relation to select joining values for the other relation. For example, you could generate DNOs first (either sequentially or randomly), then use these numbers to fill in the DeptNo values in the Employee relation.

• You will then Create Tables in your Oracle accounts for the relations in your database schema. Use the information at my homepage / or class handout to find information on creating tables.
• You will then use the SQL Insert to insert the data into your tables. (Another way to load a large amount of data into your tables is SQL load. If your data amount is large, please come to see me and I will refer you to SQL load information on how to bulk-load data. Make sure that when you create data your program creates large files of records in a format  acceptable to the Oracle bulk loader, then load the data into your DA relations.)

Step 05 Final Implementation

Your final implementation must include the following features.

• You will generate queries on your DA such that they truly reflect the entire operations of your real-world scenario.
• You will generate 8-12 queries on your DA database using the select-from-where construct of SQL (half of which I will supply from your ERD you send me in week nine). Out of these queries, some of your queries should exhibit some interesting feature of SQL: queries over more than one relation, or subqueries, for example. A suggestion, experiment with your queries on a small database (e.g., a hand-created database), before running them on the large database. Initial debugging is much easier when you're operating on small amounts of data. Once you're confident that your queries are working, run them on your complete database. If you discover that most or all of your ``interesting'' queries return an empty answer on your large database, check whether you followed the instructions in generating data values that join.
• You will write 6-8 data modification commands on your DA database. Most of these commands should be ``interesting,'' in the sense that they involve some complex feature, such as inserting the result of a query, updating several tuples at once, or deleting a set of tuples that is more than one but less than all the tuples in a relation. Again you might want to try out your commands on small data before trying it on your full database.
• You will include constraints (Primary Key, Foreign Key, CHECK) in each table definition.

Step 06 Final Documentation:  Due on the final examination date:

The final project report must include the following sections:

• A title page
• A description of your DA.
• A description of the contribution of each team member to the project.
• Submit a brief description of the normalization/denormalization process of your database schema. For each of your current relations, tell whether its relation schema is in 3NF. If not, then either redesign your schema so the relation is in 3NF, or give a rationale for leaving in non-3NF form (e.g., the amount of redundancy introduced is minimal, and splitting the relation would cause some reasonable queries to become multirelational or simply that you ran out of time). Indicate your final choice of design, whether or not you choose to decompose one or more relations.
• A brief description of all your queries, along with SQL code for the queries and the query results. Please do not, however, turn in query results that are thousands (or hundreds of thousands) of lines long!
• A brief description of any features you may have included.
• A brief description of any undeliverable features, which were promised in the project proposal.
• An Appendix A, containing the original project proposal.
• An Appendix B containing properly documented program listings of all programs written to fabricate or to massage the data obtained from some other source. In addition, include your program and a script showing the program running for each of the following:

• All the commands used to create your database schema. Also, show the response of  sqlplus to a request to describe each of your relation schemas. Recall to see the description of the schema Employee you typed:   DESCRIBE Employee; at the SQL prompt.
•  All the constraint definitions. To view all the constraint definitions on your table query the USER_CONSTRAINTS table.
• All of your SQL queries, along with a script  illustrating their execution. Your script should be sufficient to convince me that your commands run  successfully. Please do not, however, turn in query results that are thousands (or hundreds of thousands) of  lines long!
• All your modification commands.
• Use the submission guidelines stated in the General Policy section of this document.

General Policy:

• Late Submissions: The penalty for project proposal or final project report submitted after the above specified due date is 5% per day. Anything submitted after class on the assigned day will be considered to be one day late.
• Submission Guidelines:

SUBMISSION Policy: All submissions are due on the final examination date before the exam. REPORTS: Any documentation must be submitted in a 3 hole or 2 hole punch project cover or binder. Please no loose paper submissions.
                     Loose paper submissions penalty is 10% of the assigned grade.

PROGRAMS: Please email me the program files as attachments.

FRONT PAGE: Every project related submission should include the following information.

· TITLE of the Project

· Last Name, First Name, Middle Initial of each Team Member as it appears on the Roster.

• Peer Evaluation: On the last day of classes you will be asked to fill in a confidential questionnaire to indicate the contribution of your teammates and the grade for your teammates from your point of view. If you do not fill the peer evaluation you will not get a grade for the project.

General Documentation Guidelines:

Any documentation submitted in relation to the project should conform to the following guidelines.

• Documents must be written using MS word or some other word processor.
• Documents must be single-spaced with 12 point font.

Adapted from Stanford Database Group Project Description.