iLab Assignment 2 Must have MS Access and MS Visio to complete solution. See attached files for detailed step by step in

iLab Assignment 2 Must have MS Access and MS Visio to complete solution. See attached files for detailed step by step in

Page 1 of 27
A. Lab # : BSBA BIS245A-2
B. Lab 2 of 7 : Skills Development in Visio; Creation of MS Access Database
C. Lab Overview–Scenario / Summary:
TCOs:
1. Given a business situation in which managers require information from a
database, determine, analyze, and classify that information so that
reports can be designed to meet the requirements.
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 physical tables.
Scenario:
You have been asked to create a conceptual database model using MS Visio
Database Model Diagram Template. The purpose of this lab is to have you gain
experience with the various modeling tools needed to create a conceptual model
(entity relationship diagram) of a database. You will then modify the model for
implementation as a MS Access database. You will create and modify one
conceptual model.
You will then create a MS Access database based on the model developed in
Visio, creating the necessary tables and relationships.
Upon completing this lab, you will be able to
1. relying on detailed instructions, add two entities to the existing conceptual
model (ERD), including attribute data types and required field lengths;
2. create a new MS Access database file;
3. using the model from Parts A and B of the lab, and relying on detailed
instructions; create the first two tables in the database;
4. use the experience gained in creating the first two tables to add the
remaining tables;
5. using the model from Parts A and B of the lab, and relying on detailed
instructions; create the relationship between the first two tables in the
database; and
6. use the experience gained in creating the first relationship to create the
remaining relationships between the tables.
Page 2 of 27
D. Deliverables
Section Deliverable Points
Part B Step
2
YourNameLab2.vsd (Visio Diagram)
Part C Step
4
YourNameLab2.accdb (Access Database)
E. Lab Steps
Preparation
1. Get the Visio diagram from Doc Sharing
a. Download Lab2.vsd file from your course Doc Sharing panel (Labs
dropdown), 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 Lab2.vsd file to your Citrix drive, or
allow Citrix access to your system. Follow the instructions for
uploading files to Citrix, located in the iLab tab in Course Home.
Page 3 of 27
Lab:
Part A: Create a Visio ERD with an Associative Entity
Step 1: Open the Lab2.vsd file
a. Open the Lab2.vsd file using Microsoft Office or Visio.
b. If you are using Citrix, click on Microsoft Office Applications folder to start
Visio. Then use the File menu, Open command to open the Lab2.vsd file.
Step 2: Add two new entities and relationships to the Visio diagram
a. The Lab2.vsd file should open like the following screen. Using the Save As
command in the Office Button list, save the file as YourNameLab2.vsd.
Figure 2- a
b. The existing entities may be moved by clicking and dragging to create more
room. You will be adding entities to the diagram, so drag a new entity shape
Page 4 of 27
to the page. You will name the new entity OrderLine, and add the following
attributes.
1. OrdLineUnitPrice (Required attribute)
2. OrdLineQuantity (Required attribute)
3. OrdLineDiscount (Not a required attribute)
NOTE: If you need assistance to do this, refer to the instructions for the Week 1
Lab.
Figure 2- b
c. Add a second new entity named Orders with the following attributes so that
your diagram matches the diagram below
1. OrderID (Primary key)
2. OrderDate (Required)
Figure 1 Figure -ab
Page 5 of 27
3. OrderRequiredDate (Required)
4. OrderShippedDate (Not required)
5. OrderShipVia (Not required)
6. OrderShipName (Required)
7. OrderShipStreet (Required)
8. OrderShipCity (Required)
9. OrderShipState (Required)
10. OrderShipZip (Required)
Figure 2- c
Page 6 of 27
d. At this point, the OrderLine table does not have a primary key. This is an
associative entity and will borrow its primary key from the two parent tables.
To add them to the OrderLine entity, you must first add the relationships
from it to the parent tables. Drag a Relationship shape to the diagram.
Connect the one end to the Products entity, and the many end to the
OrderLine entity. Name the relationship Specified by. ProdID now appears in
the OrderLine table as a foreign key. If you have trouble reading the
relationship, drag and move the OrderLine table as shown below.
Figure 2- d
e. Drag a second Relationship shape to the diagram. Connect the one end to the
Orders entity, and the many end to the OrderLine entity. Name the
Figure 1- c
Page 7 of 27
relationship Included On. OrderID now appears in the OrderLine table as a
foreign key. (NOTE: The page orientation in the diagram below has been
changed to Landscape. You will find the orienation options on the Design
ribbon.)
Figure 2-e
f. Select the OrderLine entity, and specify the attributes ProdID and OrderID so
that both are primary keys. This is the composite primary key for the
associative entity, OrderLine. The OrderLine entity may be moved to better
show the relationships. Your diagram will look like the one below. Save your
Page 8 of 27
diagram. If you do not wish to continue with Part B of the lab at this time,
close the file.
Figure 2- f
End of Part A
Part B: Adding data types and field lengths to the ERD
Step 1: Open the Visio Diagram File
Open the YourNameLab2.vsd file created in the first part of the lab,
Step 2: Add the data types and field lengths to the Suppliers entity.
Page 9 of 27
a. The diagram was created without specifying data types for the attributes.
Before we can create the tables in Access, we need to specify both the data
type for each attribute and the field length for each that should be set in the
Access tables. First set the data type and field size properties for the Supplier
table. Because you will create the tables in an Access database, you will use
the data types most consistent with Access. You will also be adjusting the field
length found in parenthesis immediately after the data type. Click to select
the Suppliers entity, and in the Columns category set the Data Type
properties for each attribute as follows
1. SupID TEXT(10)
2. SupCompanyName TEXT(25)
3. SupContactName TEXT(25)
4. SupContactTitle TEXT(25)
5. SupAddress TEXT(20)
6. SupCity TEXT(20)
7. SupRegion TEXT(10)
8. SupPostalCode TEXT(10)
9. SupCountry TEXT(20)
10. SupPhone TEXT(10)
11. SupFax TEXT(10)
12. SupHomePage TEXT(25)
(See the illustration on the next page.)
Page 10 of 27
Figure 2- g
b. Continue by changing the remaining tables so that the attributes show the
character types and field lengths shown below
Page 11 of 27
Products Entity
1. ProdID TEXT(10)
2. ProdName TEXT(25)
3. ProdDescription LONGTEXT
4. ProdUnitPrice CURRENCY
5. ProdQtyPerUnit INTEGER
6. ProdUnitsInStock INTEGER
7. ProdUnitsOnOrder INTEGER
8. ProdReorderLevel INTEGER
9. ProdDiscontinued BINARY(3)
10. SupID TEXT(10)
OrderLine Entity
1. OrderLineUnitPrice CURRENCY
2. OrderLineQuantity INTEGER
3. OrderLineDiscount NUMERIC(10,2)
4. ProdID TEXT(10)
5. OrderID TEXT(10)
Orders Entity
1. OrderID TEXT(10)
2. OrderDate DATETIME
3. OrderRequiredDate DATETIME
4. OrderShippedDate DATETIME
5. OrderShipVia TEXT(10)
6. OrderShipName TEXT(25)
7. OrderShipStreet TEXT(25)
8. OrderShipCity TEXT(20)
9. OrderShipState TEXT(2)
10. OrderShipZip[ TEXT(10)
c. At this point, you can only see the data type and field size properties by
selecting a particular entity. Now you will change your Visio settings so that
these appear on the actual diagram. To do this, go back to the Database
ribbon, Display Options. In the Display Options dialog box, select the Table
tab. You will then change the Data Types to Show Physical. Click the OK
button to apply the new setting. The data types will then appear in your
diagram. Note that you will need to move the entities so that they are easily
viewed as they are now larger. Your diagram should look similar to the one
shown on the next page.
Page 12 of 27
Figure 2- h
d. Save the file. If you are not going to continue to Part C of the lab, you may
close the file.
End of Part B
Page 13 of 27
Part C: Creating the Access Database (Tables and Relationships) from
the ERD
Preparation
Open the Visio file created in Part B of this lab, you will reference this file in Part
C.
Step 1: Locate MS Office Applications
a. If you are using Citrix, click on Microsoft Office Applications folder
b. If you are using Access on a local computer, select Microsoft Office from your
Program Menu
Step 2: Start MS Access
a. Click on Microsoft Access and Microsoft Access should open like the following
screen.
Figure 2- i
Page 14 of 27
Step 3: Open a New Blank Database
a. Click on the New Blank Database Icon show below to open a new database
file. Notice that the right side of the window now enables you to name your
database and create it. In the File Name text box enter YourNameLab2, then
click the Create button to begin working with your database.
Figure 2- j
b. The new database opens to a blank table in Datasheet view as shown below.
Click the View button on the left side of the ribbon to change to Design view.
Page 15 of 27
Figure 2- k
c. Notice that the Save As dialog appears and you are asked to name the table.
As you will create the Suppliers table first, enter the table name Suppliers and
click the OK button to close the dialog box.
Page 16 of 27
Figure 2- l
Step 4: Create the Tables
a. The Design view opens with the Cursor in the Field Name column. Notice the
key symbol indicating that, unless you specify otherwise, this will be the
primary key for the Suppliers table. Referring to the ERD, enter the name for
the Suppliers table primary key, SupID. Use the dropdown arrow in the Data
Type column to select the Text data type as specified in the ERD. In the
Properties pane at the bottom of the window, change the first property (Field
Size) in the General tab to 10 so that the field size meets the requirements of
Page 17 of 27
the ERD.
Figure 2- m
b. Add the SupCompanyName field name and corresponding data type, Text. In
the properties pane, set the field length to 25, and change the required
property to Yes. Recall in the ERD that required fields are shown in bold font.
Page 18 of 27
Figure 2- n
c. Utilizing the Visio ERD, enter the remaining field names with corresponding
data types and field properties. Notice that the SupHomePage goes in as a
Hyperlink data type which is not a type available in Visio. Once all fields are
added, close the Suppliers table by clicking on the Close button on the right
side of the design pane. You will be prompted to save changes, so be sure to
save.
Page 19 of 27
Figure 2- o
d. Click on the Create ribbon tab, and then on the Table option on the left side of
the ribbon. Access returns to the data view, and a new table is presented.
Page 20 of 27
Figure 2- p
e. Access opens a new table in datasheet view. Change to Design view. When
prompted for the table name, enter Products. Using the information from the
ERD, create the Products table. Notice in the illustration below that the
LONGTEXT data type for the ProdDescription field is comparable to the Access
MEMO data type. The INTEGER data type changes to NUMBER, and the
BINARY data type changes to YES/NO.
Page 21 of 27
Figure 2- q
f. Repeat the process to create the remaining two tables from the ERD. When
creating the OrderLine table, you will need to designate the composite
primary key consisting of the ProdID and OrderID fields. To do so, click and
drag to select both rows in the design screen, and then click on the Primary
Key button on the ribbon as shown below. Notice that the primary key
designator now shows to the left of both field names. Set the OrdLineDiscount
datatype to Number, the field to Decimal and the precision to 2.
Page 22 of 27
Figure 2- r
g. After creating and closing the final table, click on the Database Tools ribbon,
and then on the Relationships button. See below.
Page 23 of 27
Figure 2- r
h. When the Show Table dialog box appears, drag it to the position shown below
so that you can see the Relationships work space. Click and drag to select all
four tables, and then click the Add button. You should see all four tables in
the Relationships area. Close the Show Table dialog box.
Page 24 of 27
Figure 2- s
i. Click the table name and drag the tables to positions corresponding to those
on the ERD. Then click the bottom border of each table object and drag it so
that all fields in the table are visible.
Page 25 of 27
Figure 2- t
j. Create a relationship between the Suppliers and the Products table. To do
this, drag and drop the primary key field name, SupID, in the Suppliers table
to the SupID field in the Products table. This will open the Edit Relationships
dialog box. Make sure you show SupID in both tables as shown in the dialog
box below. Click the checkbox for the Enforce Referential Integrity option.
Click the Create button on the dialog box.
Page 26 of 27
Figure 2- u
k. When the dialog box closes, you will see the relationship. Create the
remaining relationships by dragging the primary key from the parent tables to
the corresponding fields in the associative table, OrderLine. Be sure to enforce
referential integrity for all relationships. Your diagram should closely resemble
the one below.
Page 27 of 27
Figure 2- v
l. Save the database by clicking the Save icon or by using the File menu, Save
command. Close the relationship diagram by clicking on the Close button.
Close the database, and close Access. Congratulations on the successful
creation of your database!
End of Part C
Lab 2 Final Deliverables
a. YourNameLab2.vsd (Visio Diagram) – from Lab 2 Parts A and B
b. YourNameLab2.accdb (Access Database)– from Lab 2 Part C
Submit these files to the Week 2 iLab Dropbox.
END OF LAB

Is this the question you were looking for? If so, place your order here to get started!