Database Table Design

BUAD 2020 – Assignment #4
Database Creation – With Microsoft Access
40 Points
Shelly owns an auto repair shop and would like a database that would help track her customers and their repairs.
• It should be able to keep track of the customers’ first and last names along with their street address, city, state, ZIP code, and main phone number.
• In addition, the database should track all of the customers’ cars including the make, model, year, color and VIN number.
• Shelly also wants to track data on her Employees including first and last names, their date of hire and rate of pay.
• Each time a customer brings a car in for repair – Shelly wants to record the work performed: repair type (tire rotation, oil change, etc.), hours worked, and total cost.
• You will have a separate table to define the repair types performed for purposes of standardization.
1. Create a new database (named yourLastName_AutoRepair.accdb) for this assignment.
2. Next, create the tables depicted in Figure 1.
3. After creating all tables, establish the relationships between the tables. From the MS
Access Menu, Select: Database Tools / Relationships. Draw the relationships pictured in Figure 1 and enforce Referential Integrity. An error message when attempting to
save the relationship suggests that your primary and foreign keys
are not of the correct
datatype.
4. Populate the tables by entering enough sample data to demonstrate the functionality of your database. At the minimum, have at least
• 5 customers,
Figure 1: Access Relationship Window
BUAD 2020 – Assignment #4
Database Creation – With Microsoft Access
40 Points
• 8 cars (some customers have 2 or more cars),
• 18 repairs (some vehicles have more than one repair),
• 5 repair types,
• and 4 employees who work on the cars.
Figure 1 above is an incomplete picture of the Relationship Window in Access and
should be used as a reference to ensure that you replicate the relationships and have
the same tables. You MUST add more attributes for each of the tables according to the
narrative described above.
To ensure that your relationships work, when designing your tables, use the
AutoNumber data type for all of the primary keys and select Number type for the
foreign keys. For example, in table Car, CarID (Primary Key) should be an AutoNumber
while CustomerID (foreign key) should be Number type. All other datatypes should be
appropriate for each of the attributes based upon the type of data to be entered.
Project Deliverables:
Build a Word Document (named yourLastName_A4DB Screenshots) with the following
screenshots and organized as follows:
• A view of the Access relationship window with all tables in view showing the
attributes and relationships you created
• A snippet of each table in design view
• A snippet of each table in datasheet view
Save and Submit the Word Document to Blackboard by the assignment due date.
Submit the Access database to Blackboard.
I would like to remind you that this homework assignment must be completed and
submitted individually and that no late work is accepted.
BUAD 2020 – Assignment #4
Database Creation – With Microsoft Access
40 Points
Grading Rubric for Assignment #4:
Points
Earned
Category Description/Requirements
______(10) Table Creation
Demonstrate the correct creation of tables. All tables must
be created with the proper names
______(12) Proper Attributes
All attributes must be represented and must be of the
proper data types.
______(6)
Correct
Relationships
All the tables must have the proper relationships.
Relationships must match the ERD at the top of the
assignment.
______(12) Tables Populated
Every table must be populated with each field completed.
Must have the minimum number of records.
______(-5)
Proper
Submission
Submit the properly named Word Document containing the
relationship report, and snippets of each table in design
view and datasheet view to Blackboard.
Additionally, submit your Access database to the
Blackboard Assignment Course Link with the correct
naming convention.
______(40)
Total Points
Earned
Attention: No late work accepted.