- A. Lab # : BSBA BIS245A-4A
- Lab 4A of 7: Database design based on data requirements and business rules focusing on interpreting business rules to determine relationships.
- C. Lab Overviewâ€”Scenario/Summary
- 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.
You have been asked to create a database model using MS Visio Database
Model Diagram Template. The purpose of this iLab is to provide experience designing, with limited instructions, a simple database based on a list of data requirements and associated business rules.
Upon completing this iLab, you will be able to
- 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.
|Part A||YourNameLab4A.vsdx (Visio Diagram)|
- E. Lab Steps
- 1. Using Citrix for MS Visio and/or MS Access
- If you are using the Citrix remote lab, follow the login instructions located in the iLab area in Course Home.
- 2. Start Visio
- Open Microsoft Office 2013, Visio application, or
- if you are using Citrix, click on Microsoft Office 2013
Applications folder to start Visio.
Step 1: Identify and create the entities
- 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 Crow’s
- b. Save the file as YourName_Lab4vsdx.
- Based on the information provided below, create the necessary entities for the Catering by Caren database. If you need assistance to create the entities, refer to the iLabs from Weeks 1 and 2.
Catering by Caren
Catering by Caren is an upscale catering company focusing on full, four- course gourmet dinners for groups from two to 40. Owner/chef James Caren is wonderful in the kitchen, but has become overwhelmed with the business side of running his rapidly growing operation. You have been hired as his business manager, and youâ€™ve decided to computerize information on the engagements.
Chef Caren is excited about this project and has provided you with the following information. He doesnâ€™t know databases nearly as well as he knows haute cuisine, so the data requirements are not well-organized, nor is data in its smallest parts. He has noted whether the menu items are appetizers, salads, main courses, or desserts. He has also provided a list of the information he keeps on each customer and each booking.
By talking with Chef Caren, you feel you have enough information on the companyâ€™s business rules to understand the relationships between the data. Chef Caren is particularly concerned that you capture the exact requirements for the menu for each engagement. For instance, if 20 people are to be served, he wants to know how many want the vegetarian main course, the Kosher meals, and so forth.
At this point, you are going to use the following information to put together an entity relationship diagram that you will then use with Chef Caren to verify that you have accurately captured the requirements.
Approved for credit or not
Number of Attendees
Special Diet Plates
Assistant Chef Assigned Employees Assigned
Payment Method (AmEx, Visa, MasterCard, Check, Cash, Bill)
Deposit Paid Date
Balance Paid Date
Item Name Description
Classification (Appetizer, Salad, Main Course, Dessert)
Special Diet Item (Kosher, Vegetarian)
NOTE: You may find it helpful to consider the business rules in Step 4 in creating your entities.
Step 2: Identify and create attributes (fields)
NOTE: Because you are creating your diagram in Visio, it will be easier to create the attributes prior to the relationships.
- Refer to the data requirements from Step 1 of this iLab. If you have not already created the attributes (fields) in your ERD, add them at this time.
- b. Save your file and continue to Step 3.
Step 3: Identify and designate the keys.
- Determine whether an attribute exists in each table that will satisfy the requirements of a primary key. If no appropriate field exists, create a field for this purpose.
- b. Check the Primary Key property for the field(s) in each table using the Visio column propertie
Step 4: Identify the relationships.
- Using the information below under Business Rules for Catering by Caren, create the relationships between the entities created in Steps 1 and 2.
- b. Notice that, where many-to-many relationships exist, you will need to create associative entitie If you are not sure of the process to create relationships in Visio, refer to the iLabs for Weeks 1 and 2. You created an associative entity in Week 2.
- For any associative entities created, enter necessary fields. You may also need to designate or create a primary key.
NOTE: If the relationship is mandatory (must have at least oneâ€¦) you right click the relationship, then choose Select Begin Symbol and choose ‘zero or more’ or ‘1 or more’ as appropriate.
Business rules help determine the relationships between data that should help you design the relationships between your entities.
- 1. Each customer can book many engagements over time, but each engagement is placed by only one customer.
- 2. One assistant chef is assigned to each engagement. An assistant chef may work many engagements over time, but each engagement will have only one assistant chef assigned.
- 3. Each engagement will have many menu item Each menu item may be served at many engagements. When a menu item is selected, the number of servings required for the event must be recorded. (Hint: Remember that an associative entity may have attributes!)
- 4. Each engagement must have at least one assistant chef assigned. There may be many other employees assigned to the engagement. Each employee may work many engagement However, some employees never work engagements.
- 5. Only one engagement may be scheduled for any particular date and time.
Lab 4A Final Deliverables
- YourNameLab4A.vsdx (Visio Diagram)â€”from iLab 4A
Submit this file to the Week 4 iLab Dropbox.
END OF LAB PART A
- Lab #: BSBA BIS245A-4B
- Lab 4B of 7: Completing Queries
- Lab Overviewâ€”Scenario / Summary
# 6: Â Given a physical database containing tables and relationships and business requirements, create the necessary queries.
The iLab begins with a simple example of query development using Access; then, evolves to more complex queries which the student should perform after completing the first exercise. Â The student can create a query with the wizard, with query design view, or with SQL statements. The Northwind database will be used again in this lab.
Upon completing this iLab, you should be able to
- create a query by following lab instruction;
- create a query by using either query designer, or query wizard;
- create a query by using SQL statements; and
- interpret the results of queries.
Submit the MS Access Database file that contains the queries created in this lab.
|2||Query #2â€”Compound Statements|
|4||Query #4â€”Suppliers (more advanced)|
|5||Query #5â€”Customersâ€”using SQL Statements|
|6||Query #6â€”Putting it all together|
- Lab Steps
- Download the database starter file, accdb (Northwind database), from the Week 4 iLab page and save the file to your local drive.
- Using Citrix for MS Visio and/or MS Access
- If you are using the Citrix remote lab, follow the login instructions located in the iLab area in Course Home.
- You will have to upload the accdb file to your Citrix folder. Follow the instructions located in the iLab area in Course Home.
- The E-R diagram for the database is represented
- Start MS Access:
- If you are using Citrix, click on Microsoft Office Applications folde
- If you are using Visio on a local computer, select Microsoft Office from your Program Men
Step 1: Query #1 Using Step-by-Step Instructions
Open the Lab4_Start.accdb in Access by going through the File Menu, Open command.
Query 1: In the Create ribbon, use the Query Design function to find the list of employees who worked on orders placed by UK customers. The list should be presented in ascending order of the employee last names
After clicking Query Design, the Tables window will open. Hold down the Ctrl key, and click to select three tables (Customers, Orders, and Employees). Then, click Add.
The tables are added to the query design panel. Drag and drop (or double click on the field names) to add the FirstName and LastName fields from the Employees table onto the query design grid. Add the Country field from the Customers table.
In the Country field, Criteria Row, enter â€œUKâ€ to filter so that only the UK customers will show in the query results. Also, set the Last Name field, Sort Row to Ascending.
The grid now looks like the following. (Note that the tables have been rearranged to better show the relationships. You may choose to do this also.)
Select the Property Sheet function from the Query Tools, Design ribbon. Set the Unique Value property to Yes. Notice that the properties shown are for the query rather than a particular field. If you are not seeing the appropriate properties, move your cursor to the upper part of the design grid displaying the tables.
Click the Run icon to run the query.
The query result should appear as below.
Save the queryÂ by clicking theÂ SaveÂ button at the top left portion of the screen. For aÂ query name enterÂ Lab4_Query1.
Step 2: Query #2 Using Compound Statements
Using the same procedures described in Step 1, find the list of employees who worked on orders placed by Germany, UK, and USA customers. The list should be presented in ascending order of the employee last names. Make sure Unique Values is set to No.
Hint: In the criteria row, under Country, key in â€œUKâ€ OR â€œGermanyâ€ OR â€œUSAâ€
The results will look like the following (only partial table is displayed to save space. You should produce 300 records).
Save the query as Lab4_Query2.
Step 3: Query #3 Using Step-by-Step Instructions
Query 3: Make a list of suppliers, who supply products ordered by USA customers.
Note that for this query, only the major steps are demonstrated here. Refer to previous steps if you need further assistance.
Create a new query and add the following tables. Name the query as Lab4_Query3.
Tables to add:
- Order Details
Hereâ€™s a snapshot of how your query should look like in the Design mode.
Note that you need to set the properties to display only the unique values (just like Query1). Also, notice that even though the CompanyName and Country fields from the Customers table are added to the grid, the Show check boxes are unchecked. These two fields will not appear in the query results.
When executed, your query should return 29 records.
Step 4: Query #4
Using the procedures described in Step 3, find the list of suppliers who supply products ordered by German customers.
When executed, your query should return 29 records, a part of which is displayed.
Save the query as Lab4_Query4.
Note that you need to set the properties to display only the unique values (just like Query1).
Step 5: Query #5
Query 5: Find the customer details of all your customers in the United States. Use the straight SQL approach (as opposed to Query wizard).
In this query, we wonâ€™t use the Query Designer or Query wizard. This is to demonstrate the SQL coding approach. SQL (Structured Query Language) is a very powerful language. It has a rich set of features to manipulate data in a number of ways.
Guidelines for SQL Query
-Ââ€Â Â Â Â Â Â Â Â Â Select the fields for the query.
-Ââ€Â Â Â Â Â Â Â Â Â Determine which table or tables contain those fields.
-Ââ€Â Â Â Â Â Â Â Â Â Determine criteria.
-Ââ€Â Â Â Â Â Â Â Â Â Determine Sort order.
-Ââ€Â Â Â Â Â Â Â Â Â Determine grouping.
-Ââ€Â Â Â Â Â Â Â Â Â Determine any update operations to be performed.
Basic SQL Commands
The basic form of SQL expression is quite simple.
The statement begins with SELECT clause, which consists of the word SELECT, followed by a list of those fields you want to include.
Next, there is a FROM clause, which consists of the word FROM, followed by a list of tables involved in the query
Finally, there is an OPTIONAL WHERE clause, which consists of the word WHERE, followed by any criteria that the data must satisfy.
The command ends with a Semicolon (;).
Simple criteria: The criterion following the word WHERE is called a Simple Criterion. A Simple Criterion has the form: Field name, Comparison Operator, then either another field name or a value.
= Â Â Â Â Â Â Â Â Â Equal to
< Â Â Â Â Â Â Â Â Â Less than
> Â Â Â Â Â Â Â Â Â Greater than
<= Â Â Â Â Â Â Â Less than or equal to
>= Â Â Â Â Â Â Â Greater than or equal to
<> or ! Not equal to
Create a new query by clicking Query Design. However, close the Show Table dialog box without selecting any tables. The Query is shown in Design View. Using the View option, change to SQL View as shown below.
Enter the following query.
Run the query. The result should look like the following.
Save the query as Lab4_Query5. Save the database file.
Step 6: Query #6
In this query, you will demonstrate your understanding of queries.
Using the steps described in Step 5, create a new query using SQL View. Enter the following query.
SELECT Customers.CompanyName, Customers.ContactName, Orders.EmployeeID, Orders.OrderDate, Orders.ShippedDate, Orders.ShipVia
FROM Employees INNER JOIN (Customers INNER JOIN Orders ON Customers.CustomerID =
Orders.CustomerID) ON Employees.EmployeeID = Orders.EmployeeID;
The results will look like the following, with 830 records.
Save the query as Lab4_Query6. Save the database file.
When you upload your iLab, use the comment area of the Dropbox to explain what you accomplished in this query.
Step 7: Submit Deliverables
Save your MS Access Lab4_Start.accdb file as YourName_Lab4 _Finitial.accdb.
Submit the Access file created during this assignment to the weekly iLab Dropbox located on the silver tab at the top of this page.
Do not forget to provide your comments from Step 6 in the comments area of the Dropbox.
(See the Syllabus section ‘Due Dates for Assignments & Exams’ for due date information.
End of Lab 4b