Explain how to successfully write SQL statements, and perform relational data modeling will enable you to create your own database and extract normalized data.

Explain how to successfully write SQL statements, and perform relational data modeling will enable you to create your own database and extract normalized data. The final assessment for this competency is to document a relationship data model, prepare an entity-relationship (ER) diagram, Structured Query Language (SQL) queries, and database reports.

Just need help with step #2 – Include rest of data for reference

Items Required for Submission
You will create a
Microsoft Access Database, which will include the following items:
1.
A one to two page executive summary
2.
An ER diagram of the database
3.
A Microsoft Access database that includes a
minimum of two database tables, two queries, and two reports to determine the number of sales by region for each
sales representative.

Step ONE: Preparation

You
have been hired to help a company create a database to track their sales
activity. The goal of the database is to monitor sales activity and to provide
reports to management that indicate the top performing sales representatives
and the most commonly sold products.

Step TWO: Prepare Your Executive Summary and
ER Diagram

·
Prepare a one to two page paper discussing the
database design, the tale relationships, primary keys, and how queries/reports
will be designed. It should be:
o Typed
and double-spaced, using 12-point font, with 1 inch margins on all sides.
o Follow
APA format.
o
Include your name and page number in the header/footer.
·
Create an ER diagram of the database
that lists the relationship of all tables
(i.e. – one-to-one, one-
to-many, many-to-many).

Step THREE: Create a Database

•
Create an Access
Database table with the title: Sales. The table should
have the following field names:
o
Salesperson Last Name
o
Salesperson First Name
o
Salesperson ID
o
Department Number
o
Customers
o
Zip Code
o
Annual Sales
•
Create a second
Access database table with the title: Sales
Regions. The table should have the
following field names:
o
Salesperson ID
o
Customers
o
Customer Territory
o
Region
o
Region Sales
•
Create ten databases records for each of the
table fields in both tables.
o
The last record and the tenth record must have
your name for the salesperson (both last
and first names) under the appropriate fields.
o
Assume that each salesperson has only one
corporate customer.
•
Complete the data for each of the table fields
representing fictitious sales information.
o
You should put sales records representing last
year sales only (Annual Sales).
o
The Annual Sales should range from $5,000 up to $200,000.

Step FOUR: Create SQL Queries

(A
select query is a type of database object that shows information in Datasheet
view. A query can get its data from one or more tables, from existing queries,
or from a combination of the two. The tables or queries from which a query gets
its data are referred to as its record source.)

•
Create a query that displays all records for
sales greater than $25,000
o
Provide an appropriate Query title.
•
Create another query, displaying only those records
which represent a specific Sales
Region during the previous
year, sorted in Descending Order by Annual Sales. Display the following fields
from both tables:
o
Salesperson Last Name
o
Salesperson First Name
o
Annual Sales
o
Region
o
Region Sales

Step FIVE: Prepare Your Reports

•
Prepare two reports and provide appropriate
report titles. Both reports should include the
following fields:
o
Salesperson Last Name
o
Sales
o
Customer
o
Region
o
Sales Territory
o
Zip Code
·
The report should
group sales under
Salesperson and should
be a summary of the sales activity
for the month.
·
The report should
group customers under territory and should have both detail and summary information. The summary
information should include:
o
Annual Sales Totals
o
Average Annual
Sales
o
Minimum Annual
Sales
o
Maximum Annual
Sales

The
report will need to have a record source pointing to a query, a table, or a
combination of both. You should use the queries you created in Step 2 as the
record source for these two reports.

Last Completed Projects