Page 1 of 6 A. Lab # : BSBA BIS245A - 3 B. Lab 3 of 7: Database Design Using Vis
ID: 3840947 • Letter: P
Question
Page 1 of 6 A. Lab # : BSBA BIS245A - 3 B. Lab 3 of 7: Database Design Using Visio and Based on Data Requirements and Business R ules C. Lab Overview -- Scenario/Summary TCOs: 2. Given a situation containing entities, business rules, and data requirements, create the conceptual model of the database using a database modeling tool. 3. Given an existing relational database schema, evaluate and alter the database design for efficiency. 4. Given an existing database structure demonstrating efficiency and integrity, design the ph ysical tables. Scenario You have been asked to create a database model using MS Visio Database Model Diagram Template. The purpose of this lab is to provide experience designing, with limited instructions, a simple database based on a list of data require ments and associated business rules. You will then complete an MS Access database based on the model developed in Visio, creating the necessary tables and relationships. Upon completin g this lab, you will be able to 1. c reate a new Visio file for database design; 2. u sing the data requirements and the business rules provided, develop a conceptual model (ERD), including attribute data types and required field lengths; and 3. c reate a new MS Access database based on the ERD. D. Deliverables Section Deliverable Points Part A Step 7 YourName Lab3.vsd (Visio Diagram) Part B Step 3 YourName Lab3.accdb (Access Database) E. Lab Steps Page 2 of 6 Preparation 1. If you ar e using Citrix for MS Visio and/or MS Access, follow the login instructions located in the iLab tab in Course Home. Lab Part A: Create a Visio ERD from Data Requirements and Business Rules Step 1: Open Visio a. Open Microsoft Office, Visio application or b. If you are using Citrix, click on Microsoft Office Applications folder to start Visio. Step 2: Identify and create the entities 1. Open a new blank Database Model Diagram. If you need assistance with this, refer to the Week 1 Lab Instructions. Be sure that all options are set consistent to those used in previous weeks so that you generate your model in Crows Foot notation. 2. Save the file as YourName_Lab3.vsd. 3. Based on the information provided below, create the necessary entities for the Pages in Time database. If you need assistance to create the entities, refer to labs from Weeks 1 and 2. Pages in Time Pages in Time is a small bookstore carrying a variety of books. The owners have decided to computerize the books available through the store so that they can determine more easily what books are on hand and which books need to be special ord ered to meet customer needs. Because customers do not always remember the name of a desired book, the owners want to be able to look for books by author or by type (genre). They also want to be able to find the publisher’s information using the system so t hat they can order books more easily. After visiting with the owners, you have gathered the following information on data requirements and business rules to develop a conceptual design (ERD), prepare it for conversion to an Access database, and then creat e the actual database.I need help builing my entities and the relationships. If you can build the diagram for me so i can see if I am on the rigt track. Page 1 of 6 A. Lab # : BSBA BIS245A - 3 B. Lab 3 of 7: Database Design Using Visio and Based on Data Requirements and Business R ules C. Lab Overview -- Scenario/Summary TCOs: 2. Given a situation containing entities, business rules, and data requirements, create the conceptual model of the database using a database modeling tool. 3. Given an existing relational database schema, evaluate and alter the database design for efficiency. 4. Given an existing database structure demonstrating efficiency and integrity, design the ph ysical tables. Scenario You have been asked to create a database model using MS Visio Database Model Diagram Template. The purpose of this lab is to provide experience designing, with limited instructions, a simple database based on a list of data require ments and associated business rules. You will then complete an MS Access database based on the model developed in Visio, creating the necessary tables and relationships. Upon completin g this lab, you will be able to 1. c reate a new Visio file for database design; 2. u sing the data requirements and the business rules provided, develop a conceptual model (ERD), including attribute data types and required field lengths; and 3. c reate a new MS Access database based on the ERD. D. Deliverables Section Deliverable Points Part A Step 7 YourName Lab3.vsd (Visio Diagram) Part B Step 3 YourName Lab3.accdb (Access Database) E. Lab Steps Page 2 of 6 Preparation 1. If you ar e using Citrix for MS Visio and/or MS Access, follow the login instructions located in the iLab tab in Course Home. Lab Part A: Create a Visio ERD from Data Requirements and Business Rules Step 1: Open Visio a. Open Microsoft Office, Visio application or b. If you are using Citrix, click on Microsoft Office Applications folder to start Visio. Step 2: Identify and create the entities 1. Open a new blank Database Model Diagram. If you need assistance with this, refer to the Week 1 Lab Instructions. Be sure that all options are set consistent to those used in previous weeks so that you generate your model in Crows Foot notation. 2. Save the file as YourName_Lab3.vsd. 3. Based on the information provided below, create the necessary entities for the Pages in Time database. If you need assistance to create the entities, refer to labs from Weeks 1 and 2. Pages in Time Pages in Time is a small bookstore carrying a variety of books. The owners have decided to computerize the books available through the store so that they can determine more easily what books are on hand and which books need to be special ord ered to meet customer needs. Because customers do not always remember the name of a desired book, the owners want to be able to look for books by author or by type (genre). They also want to be able to find the publisher’s information using the system so t hat they can order books more easily. After visiting with the owners, you have gathered the following information on data requirements and business rules to develop a conceptual design (ERD), prepare it for conversion to an Access database, and then creat e the actual database.
I need help builing my entities and the relationships. If you can build the diagram for me so i can see if I am on the rigt track. Page 1 of 6 A. Lab # : BSBA BIS245A - 3 B. Lab 3 of 7: Database Design Using Visio and Based on Data Requirements and Business R ules C. Lab Overview -- Scenario/Summary TCOs: 2. Given a situation containing entities, business rules, and data requirements, create the conceptual model of the database using a database modeling tool. 3. Given an existing relational database schema, evaluate and alter the database design for efficiency. 4. Given an existing database structure demonstrating efficiency and integrity, design the ph ysical tables. Scenario You have been asked to create a database model using MS Visio Database Model Diagram Template. The purpose of this lab is to provide experience designing, with limited instructions, a simple database based on a list of data require ments and associated business rules. You will then complete an MS Access database based on the model developed in Visio, creating the necessary tables and relationships. Upon completin g this lab, you will be able to 1. c reate a new Visio file for database design; 2. u sing the data requirements and the business rules provided, develop a conceptual model (ERD), including attribute data types and required field lengths; and 3. c reate a new MS Access database based on the ERD. D. Deliverables Section Deliverable Points Part A Step 7 YourName Lab3.vsd (Visio Diagram) Part B Step 3 YourName Lab3.accdb (Access Database) E. Lab Steps Page 2 of 6 Preparation 1. If you ar e using Citrix for MS Visio and/or MS Access, follow the login instructions located in the iLab tab in Course Home. Lab Part A: Create a Visio ERD from Data Requirements and Business Rules Step 1: Open Visio a. Open Microsoft Office, Visio application or b. If you are using Citrix, click on Microsoft Office Applications folder to start Visio. Step 2: Identify and create the entities 1. Open a new blank Database Model Diagram. If you need assistance with this, refer to the Week 1 Lab Instructions. Be sure that all options are set consistent to those used in previous weeks so that you generate your model in Crows Foot notation. 2. Save the file as YourName_Lab3.vsd. 3. Based on the information provided below, create the necessary entities for the Pages in Time database. If you need assistance to create the entities, refer to labs from Weeks 1 and 2. Pages in Time Pages in Time is a small bookstore carrying a variety of books. The owners have decided to computerize the books available through the store so that they can determine more easily what books are on hand and which books need to be special ord ered to meet customer needs. Because customers do not always remember the name of a desired book, the owners want to be able to look for books by author or by type (genre). They also want to be able to find the publisher’s information using the system so t hat they can order books more easily. After visiting with the owners, you have gathered the following information on data requirements and business rules to develop a conceptual design (ERD), prepare it for conversion to an Access database, and then creat e the actual database.
I need help builing my entities and the relationships. If you can build the diagram for me so i can see if I am on the rigt track.
Explanation / Answer
1. create a new Visio file for database design; and
2. using the data requirements and the business rules provided, develop a
conceptual model (ERD), including attribute data types and required field
lengths.
D. Deliverables
Section
Step 1
Step 3 Deliverable
YourName_Lab5A_ERMatrix.docx
YourNameLab5A.vsd (Visio Diagram) Points E. Lab Steps
Preparation
1. Get the Lab5A_ERMatrix.docx document from Doc Sharing:
a. Download the Lab5A_ERMatrix document file from your course Doc
Sharing panel (Labs view), and Save the file to your local drive.
2. Using Citrix for MS Visio and/or MS Access
a. If you are using the Citrix remote lab, follow the login instructions
located in the iLab tab in Course Home.
3. Start MS Visio
a. Open Microsoft Office, Visio application, or
b. if you are using Citrix, click on Microsoft Office Applications folder
to start Visio. Page 1 of 5 Lab
Step 1: Identify the entities and relationships.
a. Over the past four weeks, you have created ERDs based on information
provided. This week, you will complete the ERD being sure to normalize it to
3NF (third normal form). Normalization is the systematic application of rules
designed to eliminate redundancy from the database. Data in previous weeks
was, to some extent, normalized for you. This week, it is not. An abbreviated
list of the data requirements follows.
College Scheduling Data Requirements
Student ID
Student First Name
Student Last Name
Course Code
Course Name
Course Description
Course Credit Hours
Course Time
Course Days
Instructor ID
Instructor First Name
Instructor Last Name
Room Number
Room Capacity
b. Open the Word file in the Week 5 Lab Materials, Lab5A_ERMatrix.docx
c. Save the Word file as YourName_Lab5A_ERMatrix.
d. An ER Matrix helps define both the entities and the relationships between the
entities. Using the partially completed ER Matrix below as a guide, complete
the matrix.
Hint: You should end up with four entities.
1. List each entity both across and down.
2. Determine whether a relationship exists between entities and define that relationship with a verb phrase. (NOTE: The entity with the greatest
number of related entities is usually the center of the ER diagram.)
3. Identify minimum (Optional or Mandatory) and maximum (only one, or one
or more). Page 2 of 5 Student Course Student
none Is taken by:
Optional
One or
more Course
Enrolls in;
Mandatory
One or
more
none e. Save the completed matrix to submit as part of your lab.
Step 2: Create the initial ERD based on the matrix.
a. Open a new blank Database Model Diagram in Visio. If you need assitance with
this, refer to the Week 1 Lab Instructions. Be sure that all options are set
consistent to those used in previous weeks so that you generate your model in
Crow’s Foot notation.
b. One of the issues in denormalized data is that it can result in many-to-many
relationships that are not compatible with the relational database. Visio does
not allow creation of a many-to-many relationship because of this
incompatibility. Therefore, for any many-to-many relationships in your data,
you will need to create two one-to-many relationships. See the example
below: Page 3 of 5 c. Based on the information from Step 1, create the initial ERD for the College
Scheduling database including the many-to-many relationships. If you need
assistance to create the entities, refer to labs from Weeks 1 and 2.
d. Assign the primary keys and attributes from the data requirements to the
proper entities.
e. Save the file as YourName_Lab5A.vsd.
Step 3: Normalize the database
a. While the initial ERD is accurate, if you design a database without deviating
from it, you will encounter problems. For example, a course is offered many
times. If you use the course code, for example BIS245, as the primary key,
you will only be able to list the course once. There are three possible solutions.
1. Assign another primary key, such as an auto-number field, so that
the course can be listed multiple times. However, this is not
acceptable because it introduces redundancy rather than reducing it.
2. Use a composite primary key consisting of the Course Code and the
Section code. If you create a unique section code (EXAMPLE:
YearTerm_Section or 2010SpringA_A), then you can list the course
repeatedly. Again, this introduces redundancy, and is not an
acceptable solution.
3. Split the table to create two tables. To stay consistent with DeVry
terminology, create a second Section table. To ensure that a course is
not entered twice, you might modify the Section code to include the
course: BIS245_2010SpringA_A. By consistently using this format,
you should not be able to duplicate the section, and can avoid a
complicated composite key. However, as the Section table will serve
as an associative entity, it is acceptable to borrow the primary keys of
the parent tables.
b. Open the Visio file from Step 2, and add a new page. Change the page tab to
read Revised. Revise the initial ERD based on the following information.
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
schedule course (Section) 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.
c. Save the file with your revised ERD. Page 4 of 5 Lab 5A Final Deliverables
a. YourName_Lab5A_ERMatrix.docx (Word Document) from Lab 5A Step 1
b. YourName_Lab5A.vsd (Visio Diagram) from Lab 5A Step 3.
Submit these files to the Week 5 iLab Dropbox.
5. Given a physical database containing tables and relationships, create
forms which demonstrate effective user-interface design and allow for
efficient entry and retrieval of data.
Scenario/Summary
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. D. Deliverables
Submit the MS Access Database file that contains the forms created in this lab.
Step
1
2
3
4 Deliverable
Form 1 – Form Tool - step-by-step
Form 2 – Form Design – step-by-step
Form 3 – Form Wizard—Employee/Customers Multi-Table Form
– step-by-step
Form 4 – Suppliers and Products Multi-Table Form Points
5
5
5
10 E. Lab Steps
Preparation
1. Get the Database from Doc Sharing Page 1 of 11 a. Download the Lab5_Start.accdb Northwind database file from your
course Doc Sharing panel (Labs view), and Save the file to your local
drive.
2. Using Citrix for MS Visio and/or MS Access
a. If you are using the Citrix remote lab, follow the login instructions
located in the iLab tab in Course Home.
b. You will have to upload the Lab5_Start.accdb file to your Citrix folder.
Follow the instructions located on the iLab tab in Course Home.
3. Start MS Access
a. If you are using Citrix, click on Microsoft Office Applications folder.
b. If you are using Visio on a local computer, select Microsoft Office from your
Program Menu.
Lab
Step 1: Using the Form Tool
Select Employees table as in the following figure. You will see the Employees
table highlighted. Click the Create tab, and then click the Form tool. Page 2 of 11 After clicking the Form button, a new form is created by Access. The appearance of the form can be easily modified using the Themes option on
the Design ribbon. As you move your cursor over the various themes, the form
will adjust to preview how the modified form would look. You may select any
theme for your form.
Click the Save button to save the form. Name it Lab5_Form1, and then close the
form. Step 2: Using the Form Design Page 3 of 11 Click the Create tab; then, click on Form Design. (Make sure that you do not
have any of the tables selected in the Tables list on the left side of the screen.) After clicking the Form Design, a new form will be shown as the following Page 4 of 11 Click the Add Existing Fields
option, and then click Show All
Tables to show all the tables in
the database. Drag and drop
fields from the Customer table
to the form. You can also
double click the fields that you
want to add. Select a number
of fields from the Customers
table. Adjust the location of the
fields. Then, click the Form
option in the View list to see
the form. The View button is
highlighted at the right. After viewing the form, click the View button again, and select Design View to go
back to form design. Save the form as Lab5_Form2. Step 3: Creating Multi-Table Forms
You can incorporate data from several tables in a single form. In this step, you
will create a form that displays data concerning one employee. The form will also Page 5 of 11 display data concerning the many customers that your employees serve. This
type of relationship between employees and customers is called one-to-many
relationships (one employee serving many customers). In this relationship, the
Employees table is the “one” table and the Customers table is the “many” table.
To create a multi-table form using the Form Wizard
• click the Create tab, then select the Form Wizard; and
• make sure that Customers table is selected in the Table/Queries box. •
• Select CustomerID, CompanyName, ContactName, ContactTitle, Address,
City, Region, PostalCode, Country, and Phone Fields from Customers table.
Back in the Tables/Queries, click on the down arrow and select Employees
table. See illustration on next page. Page 6 of 11 •
• • Select EmployeeID, LastName, FirstName, Photo, and ReportsTo from that
table, then click Next button.
Make sure that by Employees is selected in “How do you want to view your
data?” You also have the option to select Form with subform(s) or Linked forms.
Select Form with subform(s), then click Next. Page 7 of 11 • In the next screen, you have the option of selecting the layout of your
subform. Select Tabular, and then click Next. • Assign a title to your form (Lab5_Form3) and subform (Lab5_Subform3),
and click finish. • Click on View, and then view the form in Form View. Page 8 of 11 Before closing the form, go to Design View, and change the Caption
property for the form to Employees. (See below.) Return to Form View
before saving your form. Page 9 of 11 In Form View your form should display nine records, showing employees and the
customers that they serve. These records can be cycled through on using the
navigation options available on the lower left of the application screen.
You will be prompted to save changes when closing the form. Say Yes to save
changes at the prompt, and close the form.
Step 4: Create a Multi-Table Form Following instructions provided in Step 3, create a multi-table form, showing what
products are supplied by which supplier.
Requirements: There is ONE Supplier for MANY Products. You must display the
following fields: SupplierID, CompanyName, ContactName, ContactTitle, Address,
City, Region, PostalCode, Country, and Phone fields along with ProductID,
ProductName, and CategoryID. Page 10 of 11 Your form should look like the following There should be 29 records in your suppliers, each providing numerous products.
Save your form as Lab5_form4, and the Subform as Lab5_Subform4.
Step 5: Submit Deliverables
Save your MS Access Lab5_Start.accdb file as YourName_Lab5_ Final.accdb
Submit the Access file created during this assignment to the Dropbox located on
the silver tab at the top of this page. (See Syllabus/"Due Dates for Assignments &
Exams" for due dates.
Related Questions
Navigate
Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.