Database Assignment
samsmith47
EBTM3377.pptxMicrosoft Access Exercise
In this assignment, you will design a relational database for a business that sells high-quality frozen meals over the Internet.
After your database design is completed, you will create database tables and populate them with data. Then you will produce one form, three queries and one report.
Note that the screenshot images in the instructions may appear somewhat different than what you see in your database, depending upon what database version you are using. Nonetheless, the same functions/actions will be available. A little research should provide you the information you need.
If Microsoft Access is not installed on your computer, then you can use the software in the Cook library computer lab/CBE Computer Lab. Title your database according to the rules used for the course
Your dad’s friends, Fred and David plan to sell quality, gourmet frozen meals over the Internet. He hires you for the summer based on your proficiency in database design and Access to help create this exciting business.
Fred envisions the business to include the following tables: Customers, meals, orders, shipping costs, and transactions.
The next slide would show you the steps to create tables in Access
Microsoft Access Exercise
NOTE: screenshots may appear slightly different depending upon MS Access version you are using (These steps also apply to MS Access 2013)
Open Microsoft Access to create database, then
1) Click Blank desktop database
2) Name database: DB1_Yourlastname__Yourfirstname.accdb
Click
1
2
Create Tables
The database will open in DataSheet view (Images on next pg.)
Right click on view button and click Design View (3)
Create 5 Tables in Design View. Create a first table named Customers (4) with the following fields
CustomerID ‐ number/primary key (5)
Firstname ‐ text
Lastname ‐ text
Address - text
City‐text
State-text
Zipcode-text
Email-text
Telephone#-text
CreditCard#-text
Do not add data to the tables yet
Create a table http://www.youtube.com/watch?v=MhfNQci_VzU
3
4
5
Click
Create Tables (continued)
Create a second table named Meals with the
following fields
– MealID ‐ number/primary key
– MealName ‐ text
– Price ‐ currency
– Mealdescription ‐ text
• Create a third table name Transaction with the following
fields
– OrderID ‐ number/primary key
– MealID – number/primary key
– Quantities ‐ number
Create Tables (continued)
Create a fourth table named Shipping with the following fields
– ShippingOvernight ‐ Yes/No
– Price ‐ currency
• Create a fifth table name Orders with the following
fields
– OrderID ‐ number/primary key
– CustomerID – number
– ShippingDate ‐ Date/Time
– ShippingOvernight – Yes/No
Table Relationships
Create table relationships see (numbered figures on next page)
Click the tab “Database Tools” and then click “Relationships”
(1) Add all 5 tables
(2,3,4) Relate Customers table to Orders table via Customers table primary key by clicking on Customers:CustomerID and dragging the cursor to Orders:CustomerID
(2,3,4) Relate Transaction table to Orders table via Orders table primary key by clicking on Orders:OrderID and dragging the cursor to Transaction:OrderID
(2,3,4) Relate Meals table to Transaction table via Meals table primary key by clicking on Meals:MealID and dragging the cursor to Transaction:MealID
(2,3,4) Relate Shipping table to Orders table via Shipping table primary key by clicking on Shipping:ShippingOvernight and dragging the cursor to Orders:ShippingOvernight
(2,3,4) Enforce referential integrity, cascade updates, cascade deletes by double clicking or right clicking on the relationship line)
Resulting relationships should look like (5) on next page
Create relationships https://www.youtube.com/watch?v=HYf9OZQH4hQ
Step 5
1
1) Note: If you get an error message "Cannot enforce referential integrity in the tables"
Solution: First close all tabs in the database. When you are entering data in the Orders table make sure that values in fields, CustomerID and ShippingOvernight fields correspondto same values in the CustomerID field in Customers table and ShippingOvernight field in the Shipping table. These two fields are foreign keys in the Orders table.
2) If you get an error message "Database is locked"
Solution – Close all tabs and redo the step
Add data to tables
– Double click Customers table in the left pane
– Add data to the table, add any data you like.
– Add data to Customers, Shipping, Meals,
Transaction, Orders in that order
• Try and note what occurs:
– Try and add text (not numbers) to primary key (which is a numbers field)
– Try and add data to Orders table that is not in Customers table or Transaction table or Shipping table
– Try and add same MealID to multiple records in Meal table
– Try and delete record in Orders table that is also in Transaction table
Note: If you get an error message "Cannot enter data in a particular table"
Solution: First, close all tabs in the database. Make sure that the data entered is following foreign key constraints. For example, if you enter a CustomerID number (For eg, 17) in Orders table, make sure the same number (17) is available in the CustomerID column in the Customers table. This applies to all foreign key relationships in the database.
Create data input forms
– Click “Create” tab, and then click “Form Wizard”
– Create input form (for Orders table)
– Work through the wizards and add fields to forms (1)
Add orderid, customerid, shippingdate, and shipping overnight fields
– Use Design View on the Orders Form and manually modify form design as you see fit (2)
1
Create a form https://www.youtube.com/watch?v=GXML5L1OaZI
Creating Reports
Click “Create” tab, and then click “Report Wizard” (1)
From the customer table add Customer ID (2)
Create a report http://www.youtube.com/watch?v=tKQLpq13mkM
Creating Reports
Work through the wizards and add fields to Report
From the order table add order_ID and shipping_date and shipping_overnight
Use Design View on the Orders report and manually modify report design as you see fit
Creating Queries
Create three queries:
Query 1
Create a query called Customer order details which shows a list of all orders placed by a specific customer (First and last name). The query should show the customer’s First name, Last name, CustomerID and Shipping Date.
Creating Queries
Click on the create tab and then select “Query Wizard”
Creating Queries
The new query dialog box will open
Creating Queries
Click OK, the simple query dialog box will open
Click on the field you want to display in your query, then use the arrow to move the field to the selected field section
Creating Queries
From the table/query section you will be able to display the table(s) that you need for your query by clicking on the arrow
Creating Queries
From the customer’s table, select customer first name, last name, and customer ID fields and place them in the selected field section using the arrow
Creating Queries
Click on the table/query section of the simple query dialog box to select the shipping table
Creating Queries
From the order table select shipping date
Creating Queries
Click next and select detail (shows every field of every record)
Creating Queries
Click next and enter the title of your query in the section where it says “what title do you want for your query”
Creating Queries
Type “Customer order details”
Creating Queries
Click finish the query is created
Creating Queries
In the same manner create the following queries:
Query 2
Create a query called shipping cost for specific order. Show the list of all orders by each customer ID, First Name, Last Name, order ID and Shipping Price
Query 3
Create a query called ‘food type’ for each customer where you show customer information including Customer ID, First Name, Last Name, Order ID, Meal ID and Meal Name
Finishing Up
The following YouTube links also provides the steps (applicable for MS Access 2010/13):
Create a table http://www.youtube.com/watch?v=MhfNQci_VzU
Create relationships https://www.youtube.com/watch?v=HYf9OZQH4hQ
Create a form https://www.youtube.com/watch?v=GXML5L1OaZI
Create a report http://www.youtube.com/watch?v=tKQLpq13mkM
Create queries https://www.youtube.com/watch?v=tpIPWIDcMTo
https://www.youtube.com/watch?v=n2LWqnN-yCY
Upload accdb (MS Access 2010/13) file to Blackboard by the deadline listed in the schedule
