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.
Step 01: Group
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:
Your Name (with Univ Id)
Table of Contents (with page numbers)
Statement of Purpose (or Executive Summary)
(Discussion of the current system, manual or automated)
What are you the requirements that you intend to address in this active database
In Scope details
Out of Scope details
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
Business Rules they implement
Tables they are based on
How to use it (syntax, parameter list etc)
Brief description of triggers
Business Rules they implement
When are they used
Cautionary notes, if any
Table that shows who’s the author of the SP/SF/Trigger/SQL scripts
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)
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.