For this project you are to design and implement a small database. This is an individual project and not a group or team project. Each student will decide the application domain for their database.
The first step for the project involves identifying the application domain and providing a description of this domain. The description should be sufficiently detailed to allow an analyst to examine your description, and using only the information provided by this description, develop an entity-relationship model. The application domain must be described using "business" terminology and not database or computer terminology. That is, the description should not indicate that there is an ABC entity type and an XYZ entity type and these entities participate in a 1:M relationship. This is not how business users describe their world.
ShinyShoesForAll (SSFA) is a small shoe repair shop located in a suburban town in the Boston area. SSFA repairs shoes, bags, wallets, luggage, and other similar items. Its customers are individuals and small businesses. The store wants to track the categories to which a customer belongs. For communication purposes, SSFA also needs each customer's name and phone number. A job at SSFA is initiated when a customer brings an item or a set of items to be repaired to the shop. At that time, an SSFA employee evaluates the condition of the items to be repaired and gives a separate estimate of the repair costs for each item. The employee also estimates the completion date for the entire job. Each of the items to be repaired will be classified into one of many item types (such as shoes, luggage, etc.); it should be possible and easy to create new item types even before any item is assigned to a type and to remember previous item types when no item in the data is currently of that type. At the time when a repair job is completed, the system should allow the completion date to be recorded as well as the date when the order is picked up. If a customer has comments regarding the job, it should be possible to capture them in the system.
The domain description must include or be accompanied by a set of reporting requirements that indicate information that must be obtainable from the resulting database. For example, in an academic system, after describing the fact that students register for classes and faculty teach classes, one of the requirements is that a class list can be produced from the database to show all students who have registered for a class, the instructor's name and department, and the time and location of the class. After the database has been implemented, a request such as this will translate to a multi-table SQL query.
Each of the reporting requirements identified in this section will require accessing multiple objects to obtain complete information to support an application domain inquiry. Looking ahead to the implemented database, this means the reports will involve joins, subqueries, or other multi-table operations. Although a report that produces a list of all customers might be useful, we expect you to exploit the relationships of a database and access related tables. Thus, your reports will ultimately demonstrate your understanding of database relationships and complex SQL.
Check Job Status Inquiry
Sometimes a customer will inquire as to the status of a job and the actual total cost. So, it will be necessary for an employee to determine whether a job has been completed and the total cost of the job. Also, should there be any comments regarding the job, these should be accessible to the employee through this inquiry.
Exceptional Service
To assess the quality of service being provided by SSFA, management wishes to periodically determine the number of jobs that were completed prior to the estimated completion date. It is necessary to distinguish the number of such jobs for individual customers and for small business customers.
Customer Loyalty Rewards
Because a job can include many items, management wishes to reward a discount to those customers who have provided more than 2 jobs, each with multiple items. A list of these customers and their phone numbers must be produced to allow these customers to be contacted regarding a discount for future jobs.
Your application domain and reporting requirements must be approved before you can advance to the next step of the project.
After completing the definition of the application domain and reporting requirements, you can begin work on the design of the database. You will follow a top-down design approach as presented in class. This will require developing an entity-relationship diagram and a supporting data dictionary.
Remember that for this project, the entity-relationship diagram is to be produced using only the information present in the description of your application domain. This requires that every potential entity type, relationship, and attribute be identifiable from the narrative description you provide. Be sure that the model reflects items such as optional participation, subtypes and supertypes, identifying attributes, and so on if the narrative implies these features.
The data dictionary must provide a description of each entity type, relationship type, and attribute within the model. Every business rule in the application domain must be documented. Many of these rules will result from the properties of relationships. However, some may not be reflected in the diagram and will be documented only in the data dictionary. For example, if there is value constraint on some attribute, the diagram will not reveal this fact. This must be explained in the data dictionary.
After completing the conceptual model (ER diagram), you must produce the logical model (set of tables). This will require a set data definition statements to build the database. These CREATE TABLE statements must specify accurate data types, primary key, foreign keys (if appropriate), integrity constraints such as CHECK, NOT NULL, and UNIQUE. Due to relationships between tables, the order in which DDL statements are executed is important.
The tables must be populated with sample data. Again, due to relationships between tables, the tables must be populated in a proper order (parents first and then children). You will need to have SQL INSERT statements to populate the tables. After populating each table, a SQL COMMIT statement can be used to complete the transaction.
Care must be taken when producing sample data. You need to be certain that all situations and reports described in your application domain and reporting requirements can be demonstrated with the data you store in your tables.
Reaching this point in the project, you have designed and implemented a database. It is now necessary to show that it fulfills the requirements of your application domain. This will be done by writing a SQL query for each of the reporting requirements previously identified. To complete this step of the project, you must write the query, run the query, and present the output produced by the query.
Project Deliverables
To satisfy this project, you must submit the following items: