Lab work for Business database

User Generated

Nan511

Business Finance

DeVry University

Description

All the details are in files attached

Unformatted Attachment Preview

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
User generated content is uploaded by users for the purposes of learning and should be used following Studypool's honor code & terms of service.

Explanation & Answer

Here...


Anonymous
I was stuck on this subject and a friend recommended Studypool. I'm so glad I checked it out!

Studypool
4.7
Trustpilot
4.5
Sitejabber
4.4

Related Tags