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:

$$

$1.$ A one$-$to$-$two$-$page executive summary

$2.$ An ER diagram

$3.$ 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:

o$$ Typed and double$-$spaced, using $12-$point font, with $1-$inch margins on all sides.

o$$ Follow APA format.

$$

$$ 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.

$$

Step THREE: Create a Database

$$

$$ Create an Access Database with at least three tables

o$$ The tables should have a primary key

o$$ You should add at least two relationships

o$$ Your tables combined should have at least $12$ fields.

o$$ The fields should have appropriate names.

o$$ The fields should make use of appropriate data types.

o$$ 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?

$$ Create all the queries needed for the database as defined in project requirements

$$ Create all the reports needed for the database as defined in project requirements?

$$ Format your executive summary using APA?

$$

$$

$$

$$

$$

$$

$$

$$

$$

$$

$$

$$

$$

$$

$$

$$

$$

$$

$$

$$

$$

Step SEVEN: Submit Your Work

$$

$$ Your completed final assessment documents should be submitted through the Final Assessment page of your competency.

$$

$$ Please note, if your file is larger than $10$MB$,$ contact your instructor for directions on how to submit your file.