This project aims to design and develop a small-scale database system. Your project database should, if possible be a real one with real life users. Alternatively, you may create a simulated real world database based on information requirements in an organization with which you are familiar. It is broken into three parts, each of which will be graded.
The three parts of the project are:
An initial study: A written description of the problem
An Entity-Relationship Diagram model
A relational database implementation
Part 1
This assignment covers 1. In roughly 2-3 pages, identify a scenario you would like to manage with your database.
Try to pick an application that is relatively substantial, but not too enormous. For example, when expressed in the E-R model, your design should have around 6 entities.
After the application is chosen, try to obtain statements of requirements by either top-down approach or bottom-up approach, or by both. These statements include:
Organization objectives. What is the organization’s general operating environment and what is its mission within the environment? For example, if you choose a video game store, one of the objectives might be to provide a selection of video games that appeal to all types of customers and rent them out at a competitive price.
Organizational structure. How the organization is organized and managed? For example, if this database is for a company with many franchised stores, what are relationships between the company and stores?
Description of operations. What activities are taken within each department? What are the relationships between them? For example, if a Computer Lab is chosen, there may be operations like equipment check-out and check-in management, Lab access management, etc.
Business rules. What is the policy, procedure, or principle within a specific business environment? For example, a lab may have a policy “a lab cannot be scheduled for demonstration purposes more than one hour per day.”
Potential end users of your database. Who are the main database users? The description of this project should build a basis for your next project assignment -- an initial E-R model.
If you’re having trouble thinking of an application, or if you’re unsure whether your proposed application is appropriate, please feel free to consult with me.
Part 2
This is the final project. It is the formal document of your application. Therefore, it should be written in such a way that other people could understand the project and be able to maintain the application by reading it. Specifically, the report should contain the following components:
A general description of the project.
The ER model. You should present the finalized ER model and provide necessary explanations of the model, such as why specific entity types, relationships, and attributes are modeled in your model. You should state all assumptions you have made.
Relational model. You should map the ER model into relations and normalize the relations into 3NF.
Data dictionary. You should explain all relations including their attribute types, domains, keys, and foreign keys.
A listing of the CREATE TABLE statements you used to implement the tables of your database, and a listing showing your sample data for each table.
Sample data. Write a script file to insert at least 10 records into each table.
At least 8 meaningful queries based on your database. Show and explain the output from these queries.
As your project grade is based on the final document, it is very important to follow the guideline above to prepare and organize your final project report. Omission of any part of the report will result in deduction of the project grade.
Use the following provided format (as a minimum guideline) to document each of the following components:
1. Entity types
Entity Name:
Entity Type: (Regular or Weak Entity)
Entity Definition:
Identifier:
A sample entity definition:
Entity Name: FACULTY
Entity Type: regular
Entity Definition: a university employee who is academically qualified to teach course and perform other designated duties.
Identifier: Faculty_ID
2. Relationship types
Relationship Name:
Relationship Type: (Binary Many-to-Many, One-to-Many, …)
Relationship Description:
Attributes: (Attributes attached to the relationship)
A sample relationship definition:
Relationship Name: Is-qualified
Relationship Type: binary Many-to-Many
Relationship Description: associates each faculty member with the course(s) he or she is qualified to teach based on academic preparation and experience.
Attributes: Date_Qualified
3. Attributes
Attribute Name:
Attribute Type: (Simple, Composite, …)
Attribute Definition:
A sample attribute definition:
Attribute Name: Faculty_Name
Attribute Type: simple
Attribute Definition: name of a faculty member
The above information starts your data dictionary.