Case 3: The Personal Trainer Database

Relational Database Project 321 – Management Info Systems

INDIVIDUAL PROJECT

Case 3: The Personal Trainer Database Pages: 67-73 Due Date: April 30th 2023

NOTE: Highly recommended to review Tutorial A and Tutorial B from your textbook before

you will start working on DBMS project.

Database Design– Part One

Design your initial designs on paper. Please NOTE every work should be done individually. The first

step is creation of a logical design for the database. In this step, you will identify entities/tables, their

attributes, and relationships. While reading the case, identify entities/tables (hint: nouns), their

attributes (hint: nouns) and relationships (hint: verbs). Also, look at the form, queries and report in the

textbook and this handout to make sure that your tables include all the appropriate fields. Avoid

calculated fields (ex: age, number of students, and duration of course) as actual fields in your tables.

For each table, identify the primary key by underlining it. Determine the proper relationships

(examples: 1:1, 1:M, M:N) among the tables and create your logical ER diagram. Use the Student

Registration Database Logical Design handout uploaded in the Brightspace as a model and design

yours along the same lines.

The second step in database design is physical design. In this step, you are factoring the specific

technology (in our case RDBMS) that you are planning to use for your database implementation.

Physical designs are the basis for actual implementation of the database similar to construction (or

contractor) drawings of a building. Fortunately, in RDBMS, the physical designs are “almost”

identical to the logical designs with few modifications. Relational technology uses foreign keys to

implement relationships and can’t directly implement M:N relationships. You will create transaction

entities wherever M:N relationships are involved. Use your logical designs as basis to create the

physical designs. Your physical design will have revised tables (with foreign keys), new tables

(transaction entities), and a revised ER diagram. Determine the field characteristics such as type (short

text, yes/no, currency etc.), length etc., which are appropriate and adequate to accommodate the data

presented in the tables. Remember to use Short Text data type for fields that are not used in

calculations (Ex: Customer ID). Apply input mask for fields such as phone numbers. Transaction

entities will have composite keys or new primary keys. Include look up type field(s) for tables

(especially for transaction tables). Check out the form in this handout to decide which field(s) is(are)

look up type field(s). The final step in the physical design is populating the tables with actual data (all

made up).

Populating tables: Create at least 10 clients (make up all the needed info such as names, addresses

etc.). The first customer must be John Doe. Your team members will later on replace the John Doe

record (except the primary key) with their own names and other data (made up) while individually

implementing the database in Access. Do not use names of your or your classmates, PFW staff or

faculty in the database. Follow the instructions in Assignment 2A on page 69.

You MUST do your

project in MS Access 2016

Use the Student Registration Database Physical Design handout uploaded in the BB as a model and

design yours along the same lines. Please note that everything does not have to fit into one or two

pages compactly like the handouts.

Use MS Excel to prepare your final designs. Microsoft Excel is very handy for creating ER diagrams

and for importing the data into Access tables directly. See the video on BB to learn how to create an

ER Diagram in Excel.

Database Construction in Access – Second part

 Create a blank database file in MS Access 2016. Name the file with your last name followed by

first name. For ex: DoeJohn.accdb. Note that Access automatically adds the extension “accdb”

to the end of the filename; you do not have to type accdb as an extension. Capitalize the first

character of your last and first names as shown above. NO commas or spaces in the file name.

 Create tables by importing the data from the Excel spreadsheet that you created. You should

create your own data. Everyone should create his or her own database file from scratch.

Using someone else’s database file (even if it contains just tables) is considered as cheating.

After importing data into Access tables, change the attributes of fields in the tables as per your

physical design. Change the field type, width, etc., and designate the primary key or

composite key fields. It is very important that you strictly adhere to the design specs stated

in your physical design. Apply the input mask to fields such as telephone numbers. Include

look up type fields for tables (especially transaction entity tables). It is important that you

include the appropriate look up type field(s) at this time, before proceeding to relationship

diagram in the next step. Finally, make sure to change the John Doe record with your

information (change name, address and contact info, but leave Customer ID and other

fields as is).

 Create a relationship diagram (Database Tools  Relationships). You may notice that Access

has already included the tables used for look type fields in the relationship window. Add the

remaining tables to the window. Create relationships among the tables (as per your physical

design ER diagram). For each relationship, check mark the Enforce referential Integrity box

in Edit Relationship dialog box to avoid inconsistencies in the data entry later on. Every

relationship in your relationship window will show 1 and ∞ to reflect 1:M relationship.

 Now you are ready for the real stuff. I suggest that you launch the Microsoft Word program and create a blank document at this time. You will copy/paste the outputs (such as relationship

diagram, queries etc.) from Access to Word document as you finish each one.

Page 2 of 3

Remember to use the same names for Form, Queries and Report as given in the textbook. The instructions in the remaining pages of this handout given in the textbook.

Assignment 2B: Create Form, Queries and Report Follow the instruction from your text book page 73 to 77.

NOTE: Do Query 1, 2, 3, 4, 5 and 8. Report (Assessment Summary)

Deliverables:

1. Database Designs

 Be sure to include a cover page with your info name, course section and semester info

(Ex: John Doe, BUS321_0I, Spring 2023).

 Include the final Logical (ERD & attributes) and Physical (ERD, field characteristics of

tables, and data) designs similar to the handouts uploaded in the BB. Handwritten final

designs are not acceptable. Must be created in Excel. Upload the package to BB.

2. Database Implementation

 You should create your own database file from scratch by importing the data from the

you’s Excel file. Using someone else’s database file (even if it contains “just” tables) is

treated as cheating.

 Use the same names for form, queries and report as given in the textbook/handout.

 You need to include electronic copy of your DB file, where you will have your all tables, queries,

report and Forms..

 Submit your Access file and ER diagram using the DBMS Drop Box in Blackboard.

o Please note that you can't open the access file (unlike excel file) from the drop box

submission window. Just submit your database file in drop box, and I will contact you

later if there are any issues with the file. No need to submit your Word file in Drop Box.

 Late Submissions:

o Your submission is not complete and will be considered late if BOTH your ER file and

Access file (via Drop Box) are not turned in by the deadline.

o 10% penalty for each day (week day or weekend day) late.

Any evidence of cheating would result in a grade of zero for all involved.

Page 3 of 3