INFO 365  Database  Administration I 
 

Instructor: Dr. Hu                  Project                35%

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. 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 Oracle as the relational database management system. You will populate the database with sample data, write interactive queries and modifications on the database. You will create stored procedures, stored functions and triggers in an RDBMS

This document describes the project steps, guidelines/policies.

Project Steps:

 

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. This is a 11-week long project.

Step 02 : Database Application Selection
Pick an application that you 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 3-4 relations.

 Project Proposal :  Due in the 3rd  week

The project proposal should contain the following information:

 

Step 03: Conceptual and Logical Design of DA.

 

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.

 

Step 05 Create stored procedures, stored functions and triggers

You are required to create four working triggers, procedures or functions (mix-n-match) that demonstrates your competency of the course matter.


 

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

The final project report must include the following sections:

Project Name

Your Name (with Univ Id)

Table of Contents (with page numbers)

Statement of Purpose (or Executive Summary)

System Analysis

(Discussion of the current system, manual or automated)

Project Requirements

What are you the requirements that you intend to address in this active database

Project Scope

In Scope details

Out of Scope details

Database Design

ERD diagram

Brief description of Entities & their relations

Brief description of an ‘Data Integrity’ issues i.e. The Business Rules implemented in the Database

Features of the active database

Brief description of each Procedure/Function

Functionality

Business Rules they implement

Tables they are based on

How to use it (syntax, parameter list etc)

Brief description of triggers

Functionality

Business Rules they implement

When are they used

Cautionary notes, if any

Work Breakdown

Table that shows who’s the author of the SP/SF/Trigger/SQL scripts

Appendix A

SQL code (Procedures, Functions, Triggers etc)

-------------------------------------------------------------------------------------------------------------------------------

Project Deliverables – A Checklist

1)      Project Report in above format

2)      A zipped file  with the project code:

a.      Get Familiar with the ‘project pieces’

1.      A README.TXT which talks the user through the Setup, Testing and Cleanup Phase.

b.      Set up Phase

2.      One file with CREATE TABLE statements, called CREATE.SQL.

3.      One file with INSERT statements, called INSERT.SQL.

4.      One file with all the CREATE PROCEDURE/FUNCTION/TRIGGER statements, called Create_Procs.SQL.

c.       Testing Phase

5.      One or more SQL script files called TESTnn.SQL which help do the testing of your project. (Use SQL*Plus for this)

6.      It should include User Prompts and Pauses indicative of how the project is progressing. (brief comprehensive messages score more than long meandering narratives)

7.      All output should be ‘SPOOLed’ to an output file, which can be perused offline to determine what happened step-by-step in the project.

8.      If you can provide a script file that can do all of the above (and still not confuse the tester/user), then call the file as 'AUTORUN.SQL'. If not then make the instructions in the 'README.TXT' very explicit.

d.      Clean-Up Phase

9.      Only one DELETE.SQL script files that will DELETE all the database objects (tables, SP, SF, Triggers, or View) created above.  (Remember View’s are not dropped with the DROP TABLE… CASCADE….. command)

 

General Policy:

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

·         TITLE of the Project

·          Last Name, First Name, Middle Initial as it appears on the Roster.

 

General Documentation Guidelines:

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