Scribd
Upload
26 views3 pages

Database Schema Design and Queries Guide

The document outlines lab activities focused on designing a database schema for managing customer and inventory data. It includes tasks for identifying users and their data needs, creating relational tables, implementing referential integrity, and testing the database with sample data. Additionally, it requires writing queries to analyze sales data from an existing database.

Uploaded by

jonus
Copyright
© All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as PDF, TXT or read online on Scribd
26 views3 pages

Database Schema Design and Queries Guide

The document outlines lab activities focused on designing a database schema for managing customer and inventory data. It includes tasks for identifying users and their data needs, creating relational tables, implementing referential integrity, and testing the database with sample data. Additionally, it requires writing queries to analyze sales data from an existing database.

Uploaded by

jonus
Copyright
© All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as PDF, TXT or read online on Scribd

Week 3 Lab Activities

4.1 The following data elements comprise the conceptual-level schema for a database:

billing address
cost
credit limit
customer name
customer number
description
invoice number
item number
price
quantity on hand
quantity sold
shipping address
terms

Required

a. Identify three potential users and design a subschema for each. Justify your design by
explaining why each user needs access to the subschema data elements.

b. Use Microsoft Access or some other relational database product to create the schema tables.
Specify the primary key(s), foreign key(s), and other data for each table. Test your model
by entering sample data in each table.

4.3 Ashton wants to store the following data about S&S’s purchases of inventory:
item number date of purchase
vendor number vendor address
vendor name purchase price
quantity purchased employee number
employee name purchase order number
description quantity on hand
extended amount total amount of purchase

Required:

a. Design a set of relational tables to store this data. Do all of the data items need to be stored
in a table? If not, which ones do not need to be stored and why do they not need to be
stored?
b. Identify the primary key for each table.
c. Identify the foreign keys needed in the tables to implement referential integrity.
d. Implement your tables using any relational database product to which you have access.
e. Test your specification by entering sample data in each table.
f. Create a few queries to retrieve or analyze the data you stored.

4.4 Retrieve the S&S In-Chapter Database (in Microsoft Access format) from the text’s website (or
create the tables in Table 4-5 in a relational DBMS product). Write queries to answer the
following questions. Note: For some questions, you may have to create two queries—one to
calculate an Invoice Total and the second to answer the question asked.

1
2
a. How many different kinds of inventory items does S&S sell?
b. How many sales were made during October?
c. What were total sales in October?
d. What was the average amount of a sales transaction?
e. Which salesperson made the largest sale?
f. How many units of each product were sold?
g. Which product was sold most frequently?

You might also like