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 parts of the project are:
An initial study: A written description of the problem with a draft Entity-Relationship Diagram
A relational database implementation with the finalized ER Diagram
Part 1
This assignment covers 1. In roughly 1-2 pages, identify a scenario you would like to manage with your database.
Try to pick a relatively substantial application that is not too big. For example, when expressed in the E-R model, your design should have around six entities.
After the application is chosen, try to obtain statements of requirements by either a top-down approach, a 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 the 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 like, “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 can understand the project and be able to maintain the application by reading it. Specifically, the report should contain the following components:
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.
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. Note that meaningful queries almost certainly include multiple tables. If all of your queries are simply searching for a particular value in one table at a time, that is not sufficient. At least six of your queries need to be joins on multiple tables. Include a text description of what each query does.