Final Assessment Outline
Database
Competency Name: Database
Competency Statement: Demonstrate an understanding of database systems, their applications and tools used to develop databases.
Final Assessment Submission Title: Creating a Microsoft Access Database
Competency Objectives:
1.Demonstrate an understanding of the foundational concepts of database systems and data models.
2.Perform relational data modeling and document relational data models.
3.Create entity-relationship (ER) data models including deriving normalized forms of data.
4.Create Structured Query Language (SQL) queries.
Purpose of This Assessment
Having knowledge of how databases are conceptualized, designed, and created is a critical skill. The final assessment for this competency will have you design and create a database. You will also use SQL to create queries. You will then create reports that can be used as visual displays of the data in the database. Lastly, you will document your database by creating an ER diagram and an executive summary.
Items Required for Submission
You will create a Microsoft Access Database, which will include the following items:
A one-to-two-page executive summary
An ER diagram
A Microsoft Access database that includes a minimum of three database tables, two relationships, five queries, and three reports.
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, that describes the structure of your database and the components you added. The components should include a minimum of three database tables, two relationships, five queries, and three reports.
It should be:
Create an ER diagram of the database that shows each of the tables with their fields listed, relationships between and tables and the primary key for each table.
Typed and double-spaced, using 12-point font, with 1-inch margins on all sides.
Follow APA format.
Step THREE: Create a Database
Create an Access Database with at least three tables
The tables should have a primary key
You should add at least two relationships
Your tables combined should have at least 12 fields.
The fields should have appropriate names.
The fields should make use of appropriate data types.
Each table should have at least 10 records.
Step FOUR: Create SQL Queries
A query represents a question. A query can get data from one or more tables, existing queries, or combination of the two.
Create a query that displays all records and fields for one table
Create a query that displays some records and fields from at least two tables
Create a query that limits the records that are shown by adding criteria to one or more fields
Create a query that is sorted by a specific field
Create a parameter query
Step FIVE: Prepare Your Reports
Prepare two reports and with appropriate titles.
The first report should be based the query that displays all records and fields for one table
The second report should use grouping and can be based off of any query or table.
The third report should make use of summary statistics such as sum, average, minimum or maximum.
Step SIX: Complete Checklist for Submission
Before you submit, check to see if you have met the criteria noted below.
Did you….
Prepare a one-to-two-page paper discussing the database design, that describes the structure of your database and the components you added? The components should include a minimum of three database tables, two relationships, five queries, and three reports.
Create an Er Diagram that shows each of the tables with their fields listed relationships between and tables and the primary key for each table?
Correctly chose all primary and foreign keys?
Completely populated tables with correct data elements, reflecting the ERD design?
Accurately enter data elements into tables with no errors?
