Download Database Design Project Guidelines for CPEG 450 - Prof. Mary Khalil and more Study Guides, Projects, Research Deductive Database Systems in PDF only on Docsity!
CPEG 450 Database Design Fall 2009
Database Design Final Project
This is a final long project to give students the experience of developing database system and database application using a commercial database management system and developing tools.
Table 1. Summary of database design term project by phases
Phase Description Output Due
Phase I (10%)
Project Initiation
Form project team
Problem Statement
Plan your project
Documentation Sep. 2 2
Phase II (30%)
Conceptual/Logical Database Design
ER modeling
Mapping Relations
Normalization
Documentation Oct. 1 3
Phase III (15%)
Physical Database Design/Implementation
Create Table and other objects
Data Insert
Formulate the queries
SQL statements & Documentation
Nov. 17
Phase IV (25%)
Application Develop and Demonstration
Application implementation
Final Documentation
Demonstration
System & Documentation
Dec. 8
Peer Review (20%)
Peer Review of Project
Evaluate your team
Evaluate your team member
Peer Review Form Dec. 8
Stages of the Projects
Phase I [10%]: Project Initiation
Step 1: Form Project Team
This is a team project. Students should form groups of 3 or 4 members per team. After you form the team, each team should email to instructor about members including name, UB ID and contact, and project title (Just Title) before Phase I due. Then, instructor will assign team number to each team. By approval of instructor, your team can continue to the next step. Please, choose an interesting problem in you domain or area of interest/expertise which requires data modeling and support for a database management system (DBMS). The team number will be used for your submission and presentation.
Step 2: Problem Statement (Chapter 3)
After you choose the topic, write a detailed description of the application, such as the requirements of what data needs to be represented and captured. Some examples of the applications are here: Retail shop management: Video, Nail care, doughnut, etc. Human Resources Management System Auto part shops Travel reservation system Inventory management in grocery store Online bidding system Not allow very popular topics: Library, Hospital, Video Rental, University, etc.
More examples will be discussed in the classroom. Please, make sure the description is as detailed as possible. Otherwise, you have to revise this phase until you have all the details to proceed to the next phase.
Step 3: Formulate in English at least 10 realistic queries (Chapter 3)
The queries would be useful to somebody using the data. The queries must be realistic and you should have more than few complex queries. Note, the queries that the database system must be able to answer ultimately determine what information needs to be maintained in the database. For example, you need not include the information about best friends of students if no user of your database will be asking for that. On the other hand, some additional information, not reflected in your 10 queries, may be included in the database if you feel that somebody may be interested in it.
Step 4: Specify the assumption about the database in English (Chapter 3)
Here you talk about attributes, keys, the nature of relationships between entities, etc. Do not discuss something that is obvious (e.g., that a student can take several courses). In addition, don't make too many simplifying assumptions.
[Submission guideline for Phase I]
You should submit the full and legible report with a cover page that includes Team name, names of all members, the title of the project, and abstraction (brief description of the project). The body of Phase I documentation should include sufficient detail to describe all steps of Phase I: (Step1) Team Information: number, name, member, and so on (Step2) Problem Statement: description of your database (Step3) List at least 10 queries in English (Step4) Any assumptions
In conclusion, describe what you have learned from the project, and what you need for the better project output. The GA and Instructor may recommend modification or additions to the document submitted in Phase I. After revision phase I as recommended, in this phase, you can move to the next phase. You are strongly recommended to contact the instructor/GA before you submit Phase I documentation.
The name of your submitted documentation file should be your “Team#_phase1”. You can use your team number for #. For example, “Team1_phase1.doc”. Then, You will submit your file using blackboard digital drop-box (http://bridgeport.ctdlc.org/).
Due on: September 2 2 , 2009 23:59:59 PM
Phase III [15%]: Physical Database Design/Implementation (Chapter 8)
In this phase, students should implement the database tables from the normalized set of relations created in the previous phase. Sample data should be supplied for each table. This phase is called as Physical database design.
Step 9: Creating DDL Script Filename: dbDDL.sql
Make a file containing the SQL statements that create your entire database schema, named dbDDL.sql. This includes the tables with their constraints, view, indexes, triggers, and all other database objects if you have them. To keep the project consistent, make sure you have at least 8 tables. Make sure you have at least 2 database objects among trigger, function, procedure or view.
Step 10: Creating DML Script Filename: dbDML.sql
Make a file containing INSERT statements which populate the table created in Step 9, named dbDML.sql. This script will contain SQL commands to fill data in your data. Each table should have around 7 ~ 10 sample data. If needed, other DML statement, such UPDATE, and DELETE can be included here
Step 11: Creating Drop Script Filename: dbDROP.sql
Create a script that will drop all the objects you have created for your project including table, trigger, index, and etc.. This will be used to start from a clean state after some inserts and deletes have been added to your application to check the correctness of your queries. You should be able to clean everything through this script and re-create the database instance. Step 12: Creating SQL Script Filename: dbSQL.sql
Create a script with queries from the relation algebra in Step 7, named dbSQL.sql. This script should contain at least 5 queries on your database. Use the comment facility in SQLPlus (starting a line with -- , or / */ ) to write the English version of your query, followed by the SQL version of the query. Also show the expected output in the file. These queries need to satisfy the following:
- Should be at least join queries (some involving more than 2 relations)
- At least two of them should be aggregate queries including GROUP BY and HAVING clauses with ORDER BY clause as well
- At least one of them should have nested subquery
The purpose of having you write these is to make you think about slightly complex scenarios on your database schema and have you write queries involving join, aggregation and nesting that you have learned in the class.
[Submission guideline for Phase III]
Before you submit, you should test all four SQL script files using SQL*Plus by giving the command SQL> @ or SQL> start
Make a single zip file. Please, use the same file name as the Phase I. Then, you will submit your program using blackboard digital drop-box (http://bridgeport.ctdlc.org/).
Due on: November 17 , 2009 23:59:59 PM
Phase IV [25%]: Application Develop and Demonstration (Chapter 9)
Step 13: Application Development
In this phase, you will develop a front-end application that will interface with your DBMS at the backend. The user will interact with the DBMS only through this interface. The GUI is not mandatory. However, the GUI will be used by the user to retrieve and update the database without having to write SQL queries. The interface will have a menu-driven input through which all interaction with the DBMS is accomplished. The results will be displayed to the user as well.
If you want to do it with as a web-based interface which can be accessed from anywhere within UB, that is fine. The interface you develop should have the following features. The emphasis in grading phase of the project will be on the functionality and not on the GUI design. However, the GUI should be easy to understand and easy to use.
Step 14: Project Presentation and Demonstration
Sign up for a presentation (A sign-up sheet will be made available at the appropriate time by the instructor/GA. The presentation will be during the last two weeks of the class. Be prepared for the presentation to last about 20 to 25 minutes. Your presentation may include
- Introduction: team, title
- Executive summary of the project
- Bring along the graded EER diagram and relational mapping. (If you have made changes to these bring the latest copy as well).
- Key queries or transactions
- Demonstration. If your application needs some setting up on our machine, make sure it can be done in the least amount of time. If you are using some specific web servers or tools, you need to demo on your own laptop.
- Conclusion
Your presentation should be prepared with MS PowerPoint file and practiced. The evaluation sheet of presentation will be posted on the class website.
Step 15: Final Report and Project delivery
Prepare the final project report including:
- Cover page (Same as Phase I)
- Phase I documentation. You need to revise it if needed
- Phase II documentation. You need to revise it if needed
- Sources of SQL scripts created in Step 9 ~ 12
- System configuration of the project (usually system diagram)
- Sources of Applications generate in Step 13
- Presentation file (PPT file format)
- In conclusion, describe what you have learned from the project, and what you need for the better project output.
Put everything including documents, SQL file, and source files into CD-Rom. Then, submit it to GA/instructor on the date of demonstration. Neither hard-copy nor electronic submission are accepted. Only CR-ROM submission is allowed.
Due on: December 8 , 2009 in class
