Database Project - Part 1
Conceptual Design
The conceptual design (part 1 of the project) consists of a four part report to:
1. state the purpose of the database
2. provide a model of the logical structure of the database (an ERD)
3. document the entities and attributes in a proposed table map
4. document the properties of each of the data elements in a Data Dictionary
You will not be using Microsoft Access for this stage of the project. This part of the project will be completed in Word.
Project Suggestions
To manage the scope of the project, limit your primary entities to between four and six.
To simplify the project, your database will be based on a service-related industry. Please choose a business from one of the categories listed below. Your instructor will indicate which, if any, projects are unavailable.
1. Service Provider - e.g. Clinic (Medical, Dental, Vet); Salon (Hair, Beauty); Landscaping Service, Temp Agency, Catering, B&B ...
2. Rental Business - e.g. Cars, Bikes, Kayaks, Tools, Scooters, Facilities...
3. Training and Education - e.g. Driver Training, Computer Training, Dance School, Music Lessons...
4. Other - If you have another project you would like to work on, instructor permission will be required.
Data and Process Modeling Report
The Database and Process Modelling report will document the architecture of the proposed database. Use the headings below to structure your design report and create the report in MS Word. Add subheadings for each of the bulleted points to make sure you address all of the assignment requirements.
1. Statement of Purpose
• Project Name: Specify the nature of the business.
• Information Requirements:
i. Provide a bulleted list of at least 10 reports that you want to be able to produce or questions that you want the database to be able to answer.
ii. List ten basic business processes that your database will support (such as adding new customers, scheduling appointments, etc.).
2. Data Model
• Entities: List the primary entities in your database and document the relationships between them.
• ERD: Create an Entity Relationship Diagram (ERD) with all relationships normalized (i.e. eliminate any many to many relationships with join tables, and make sure that all relationships are 1:M or M:1). Make sure that your ERD has been approved before continuing. Do not include field names in your ERD.
3. Table Map
• Create a separate Table Map that includes the Primary and Foreign Keys for each table and the data elements (fields) that belong to each table. Make sure that each table is properly normalized (i.e. there are no repeating groups, each field is atomized and depends only on the primary key of the table, there are no functional dependencies, and all derived (calculated) fields are removed from the tables (e.g. Date of Birth is a valid field while Age is a calculated field based on the Date of Birth field and would not be included in a table)).
4. Data Dictionary
• Create a separate Data Dictionary documenting the fields and field properties for each of the tables. Make sure that the field names are fully qualified. An example format is shown below:
Table Name: tblCustomers
|
Field Name
|
Field Type
|
Size
|
Properties
|
|
CustomerID
|
Autonumber
|
|
Primary Key
|
|
CustomerLastName
|
Text
|
15
|
Caption: Last Name
Input Mask: >L
Index: Yes (Duplicates Allowed)
|
|
CustomerSex
|
Text
|
1
|
Validation Rule: ="M" or "F" Validation Text: Please enter "M" or "F" Format: >
|
If any field properties need to be clarified, please provide a brief written explanation (e.g. if you chose to use something other than an AutoNumber field for your Primary Key fields, you should attach a brief explanation as to why).
Project Overview
Car Rental
Statement of Purpose
To keep track of business operation specifics. For example: who the clients are, payment amounts, payment types, cars being rented, whether clients are individual or large companies, etc.
Reports
1. The most popular cars rented.
2. The average rental length.
3. The most popular season for rentals.
4. The frequency for types of cars rented.
5. How customers usually pay.
6. What percentage of income comes from large company rentals.
7. The average cost for clients to rent moving trucks.
8. The average age of individual rentals.
9. The percentages of client occupations that are employed. (vs self-employed or student).
10. The average annual repair costs.
Business Processes
1. Rental length
(daily, weekend, weekly, monthly)
2. Maintenance costs
(monthly)
3. Client payment amounts and types of payment
(Mastercard, Cash, VISA)
4. Post-rental car check-up
(charges for customer if damage is found and maintenance information (over mileage charges etc.)).
5. Client information and frequency
6. Employee wages
7. Requested rental car class and mileage allowance
8. Promotions
(based on popular season/customer loyalty/large companies).