Lab Overview
Scenario/Summary
Part A: You have been asked to create a database model using the MS Visio
Database Model Diagram Template. The purpose of this Lab is to provide
experience normalizing the database to third normal form based on limited
instructions, data requirements, and associated business rules.
Upon completing this Lab, you will be able to
•
•
create a new Visio file for database design; and
using the data requirements and the business rules provided, develop a
conceptual model (ERD), including attribute data types and required field
lengths.
Part B: The Lab begins with creating a form in Access using the Form Wizard.
After the form is created, the user can use Themes to change the appearance of
the form. The second part of the Lab uses Form Design to create a form. Finally,
the form will be customized.
Upon completing this lab, you should be able to
•
•
•
create a form using the Form Tool;
create a form using Form Design; and
create a form using the Form Wizard.
Deliverables
Part A: Step 1: YourName_Lab5A_ERMatrix.docx
Part A: Step 3: YourName_Lab5A.vsdx (Visio Diagram)
Part B: Step 5: YourName_Lab5B_Final.accdb (Access Database)
Required Software
Microsoft Office: Word 2013, Visio 2013, and Access 2013
Lab Overview
Transcript (Links to an external site.)Links to an external site.
Supporting Documents
The following files are supporting documents for the tutorials shown above.
•
•
•
Data Modeling Mini Case (Links to an external site.)Links to an external site.
Sample ER Matrix Form (Links to an external site.)Links to an external site.
Entity Attribute Form (Links to an external site.)Links to an external site.
Lab Steps
STEP 1: Complete the Week 5a and 5b Labs
1. Download and complete the Week 5a Lab Instructions (Links to an external
site.)Links to an external site..
2. Download and complete the Week 5b Lab Instructions (Links to an external
site.)Links to an external site..
3. Download the following files to complete your Lab assignment.
o ER Matrix Starter File (Links to an external site.)Links to an external site.
o Access Starter File (Links to an external site.)Links to an external site.
Data Modeling
Minicase
The purpose of this exercise is to have you become familiar with data modeling
and normalization and the Visio tool. (See the Sample Entity-Attribute Form and
Sample ER-Matrix)
A. Deliverables:
1. Entity Attribute Form (use form provided)
2. Entity-Relationship Matrix (use form provided)
3. First Draft ERD (Draw by hand)
Draft only contains the entity name, relationship name, and the
cardinality.
4. Normalized ERD (using Visio)
B. Scenario
You have been asked to create the logic data model for the Golden Eagle
Wholesale Golf Distributors. Golden Eagle distributes golf equipment for various
manufacturers to the pro shops at golf courses throughout the state. They store
the equipment in a centrally located warehouse and ship to the pro shops based
on the purchase orders submitted by their sales staff or that are phoned in
directly by their customers. If an order is phoned in by the customer, the sales
rep is still credited with the commission. Because this is a wholesale distributor,
there is no sales tax charged to the customer. Each sales rep is assigned to golf
courses within a specific area of the state. Golden Eagle must have the following
information.
Attribute List
Equipment cost
Equipment selling price
Equipment type
Golf Course Address
Golf Course location
Golf Course name
Golf Course Phone number
Golf Course Pro Shop Contact
Items Ordered
Manufacturer name
Price of each item ordered
Purchase Order Date
Purchase Order Number
Quantity on hand
Quantity ordered for each item
Reorder point
Sale Rep responsible for the Golf Course
Sales commission rate
Sales Rep who wrote the order
Sales Reps name
DeVry University
Data Modeling
Minicase
Attribute List
Shipping date
Total amount due for the order
C. Tasks:
1. Given the above scenario, identify the entities and list them at the top of
the Entity Attribute Form.
2. Once you think you have a complete list of entities, complete the Entity
Attribute Form by listing each entity in its own row, then place the
attributes with its entity. Review to make sure that attributes are
nonredundant. (See the sample provided.)
3. Using the entity-relationship (ER) matrix, define the relationship among
entities. (See the sample provided)
4. Create a first draft ERD. This should be hand drawn and need only list the
entity name and the primary verb phrase to describe the relationship.
5. Identify the cardinality for each relationship and place that on the draft.
6. Normalize your draft and create the Visio ERD. This drawing must show
the Entity Name, primary key, any foreign keys, cardinality, and verb
phrase. You must also put in all of the nonkey attributes.
DeVry University
Entity / Relationship Matrix SAMPLE – Course Enrollment
Major Steps in Data Modeling
A.
B.
C.
D.
E.
F.
Identify the entities (see the Entity-Attribute Matrix SAMPLE).
Create the ER Matrix.
Draft 1st ERD.
Normalize.
Create final ERD.
Update the Entity-Attribute Matrix.
Using the ER Matrix
1. List each Entity both across and down.
2. Determine whether a relationship exists between entities and define that relationship with a verb phrase. The entity with the
greatest number of related entities is usually the center of the diagram
3. Identify minimum (optional or mandatory) and maximum (only one or one or more).
Student
Course
Room
Instructor
Student
none
none
none
Course
Room
Is taken by:
Optional
One or more
none
Enrolls in;
Mandatory
One or more
none
Held in:
Optional
one or more
none
Assigned to:
Optional
one or more
none
Instructor
none
none
none
Scheduled for:
Optional
one or more
Teaches:
Optional
One or more
Page 1 of 4
Entity / Relationship Matrix SAMPLE – Course Enrollment
First Draft ERD—Not Normalized
Instructor
Is Assigned to
Student
Is Taken By
Course
Is Taught By
Enrolls In
Is Scheduled In
Room
Is Assigned to
Because Visio does not allow you to draw a single relationship line with M:M, you have to draw two lines showing both sides of the
relationship. As you can see from this example, every entity has a M:M relationship with another entity.
Page 2 of 4
Entity / Relationship Matrix SAMPLE – Course Enrollment
Continue to normalize by creating associative entities for the many-to-many relationships. Create new entities necessary to help
normalize and group the data.
For this example, the following rules were determined after the first Draft ERD was created.
1. A course may exist without being offered. (It exists in the catalog.) Courses are scheduled for a term and given a section. It is
the scheduled course that students enroll in, that are scheduled in rooms and assigned instructors.
2. A student can exist without being enrolled in a course
3. An instructor can exist without being assigned to teach a course
4. A room can exist without being scheduled for a course
The Normalized ERD is on the next page
Page 3 of 4
Entity / Relationship Matrix SAMPLE – Course Enrollment
Course
Term
PK
PK
Term ID
Course No
Course Title
Course Desc
Credit Hours
Contact Hours
Term Desc
Is Scheduled
Section
Is offered In
Scheduled Course
Assigned Room
Is Assigned to
PK,FK1
PK,FK2
PK,FK2
PK,FK2
PK,FK1
PK,FK2
PK,FK3
Term ID
Course No
Section ID
PK
Section ID
Is Assigned to
Section Desc
Is Scheduled in
Room No
Term ID
Course No
Section ID
Is Taken By
Is Taught By
Assigned Instructor
PK,FK1
PK,FK2
PK,FK2
PK,FK2
Room
PK
Room No
Inst ID
Term ID
Course No
Section ID
Enrolled Student
PK,FK1
PK,FK2
PK,FK2
PK,FK2
Student ID
Term ID
Course No
Section ID
Enrolls In
Instructor
PK
Inst ID
Student
Is Assigned to
PK
Student ID
Page 4 of 4
Entity/Attribute Form
List of Entities: (Add more rows if needed)
Entity / Attribute Matrix (add more rows if necessary)
ENTITY
Example:
Student
Student First Name
Student Last Name
Student Date of Birth
Student Gender
Student Address
Student City
Student State
Student Zip
List Attributes
Entity/Attribute Form
DeVry University
Student Lab Activity
BIS245 Database Essentials for Business with Lab
A. Student Name:
B. Lab 5A ER Matrix:
Student
Course
Student
none
Course
Is taken by:
Optional
One or more
Enrolls in;
Mandatory
One or more
none
Purchase answer to see full
attachment