Question

Create a working MS Access database based on the attached data set. Each tab represents a table in the database. The field names are already defined for you as column headers. Use the MS Access import wizard for the best results.

Provided Tables – field names are provided as column headers. You need to import these
1. Locations – 5 provided records
2. Products – 25 provided records
3. Inventory – 125 provided records
4. Employees – 25 provided records
5. Transactions – 2000 provided records

Required relationships – you need to define these
1. The Inventory table has a relationship with both Products and Locations. There is 1 record in the Inventory table for each product, at each location, and each of those records has the quantity on hand for that product at that location.
2. The Employees table has a relationship with Locations. For this project, we assume that each employee only works at 1 location.
3. The Transactions table has a relationship with both Employees and Inventory. For every record in the Transactions table, we track which employee was the cashier, and which location the transaction occurred at.

Required Queries – you need to create these
1. List of Employees and the Locations that they work at. The fields that should be in your query are: first name, last name, hire_date, location. Name the query "Employee List by Location".
2. List of Employees ordered by pay_rate from highest to lowest. The fields that should be in your query are: first name, last name, pay rate. Sort by pay rate from high to low. Name the query "Employee Pay Rates".
3. List the top 5 most profitable individual products (HINT: this will take 2 queries to accomplish; One to get profit for all products, then a second query to pull the top 5 profitable products from the first query.) If you find another way to get the same result, that's fine too. Name these queries "Product Profit" and "Product Profit Top 5".
4. List all items in inventory, including the location, the product name, quantity, cost, and calculated field "total_cost" defined by quantity * cost. Name the query “Inventory Quantity by Store”.
5. Calculate the total cost of all items currently in inventory across all locations. Name the query “Sum of Inventory Cost”.

Required Reports – you need to create these.
You can base these on 1 or more of the queries above, if applicable. The reports should be formatted neatly, meaning rows and columns all fit in the printable space, and there are no extra pages as a result of improper formatting.
1. Employee List
2. Inventory By Store

Solution Preview

This material may consist of step-by-step explanations on how to solve a problem or examples of proper writing, including the use of citations, references, bibliographies, and formatting. This material is made available for the sole purpose of studying and learning - misuse is strictly forbidden.

Pet Store Database

This is only a preview of the solution. Please use the purchase button to see the entire solution

Assisting Tutor

Related Homework Solutions

E-R Diagram
Homework Solution
$25.00
Computer
Science
E-R Diagram
Database
Development
Customer
Service
Installation
Reviewing PL/SQL
Homework Solution
$12.00
Computer
Science
Database
Development
Data
PL/SQL
DBA
Procedures
Triggers
Technology
Applications
Data Normalization
Homework Solution
$98.00
SQL
Normalization
Computer
Science
Database
Development
Dependencies
Keys
Table
Primary
Foreign
SQL Server for Business Intelligence
Homework Solution
$20.00
Computer Science
Database Development
SQL
Server
Business
Intelligence
Multidimensional
Tabular
Analysis
Services
Warehouse
MOLAP
HOLAP
ROLAP
Partition
Engine
MDX
Hierarchies
Fact
Relationship
Property
Unknownmember
Action
Cube
Pr
Get help from a qualified tutor
Live Chats