Final Exam Assessment Project Database Management Systems

Fina Assessment Project

The objective of this project is for students to apply the database design principles to a real-life

scenario. This is a practical design project – students must create and submit the database, as

well as documentation for the items below. The students will review database requirements and

produce the following:

Part One: Database in Access and Rationale Document (0-60 points)

Database Design and Rationale (0-30 points)

Access database with normalized tables: A set of tables in at least third-normal form is

required for completion of this project. Complete table structures are required; students

must also determine if primary or any other keys are required for each table. The student

must also describe, in detail, and in a separate document, the rationale he/she used in

developing the specified set of tables, as well as the specific fields in the tables.

 Rationale used in developing the specified set of tables, as well as the specific

fields in the tables, is provided (Separate file for upload to Blackboard).

 Fieldnames are all single-valued; each column has a distinct name; all the values

in a column are values of the attribute that is identified by the column name; the

order of columns and rows is immaterial; each row is distinct.

 Database and tables are created in Access.

 Correct table relationships are established in Access.

 Referential integrity, cascade update and cascade delete are used appropriately

Keys (0-10 points)

 All appropriate keys have been identified and their functions described in the

rationale document

 Primary key is a minimal collection of columns on which all columns are

functionally dependent.

Report Rationale (0-10 points)

Three potential reports described in the rationale document that could be generated in

response to user requests; supporting queries and reports must be included (ex.: report of

number viewers that viewed each movie each year; correct sorting and grouping) in the

Access database. Rationale required in the rationale document for why each report could

provide important information for decision-making; queries must specify criteria for

record selection, as well as grouping, sorting and any other requirements for the specific

report (included in Rationale file described in Database/Tables above.)

 Three reports that could be used in response to user requests are identified.

 Rationale for why each report could provide important information for decision-

making is provided.

 Reports must be created in Access and use the correct queries as the source.

Supporting Queries (0-10 points)

 Three queries are provided, and specify criteria for record selection, as well as

grouping, sorting and any other requirements for the specific report.

 Queries are not simple listings with simple criteria, but must use more advanced

functions, such as sorting, grouping and aggregate functions (counts, averages, etc).

Part Two: E-R Diagram (0-10 points)

E-R diagram: An E-R diagram for the database is required for this project. This may be

completed in Word, Excel, or whatever program the student is most comfortable using.

(Separate file for upload to Blackboard)

 Graphic model is complete with all entities, attributes, and relationships represented.

 ER Diagram is formatted according to instructions provided in Chapter 1 in the

textbook.

Part Three: DBDL Diagram (0-10 points)

DBDL diagram, complete with relevant keys: A DBDL diagram is required for this project.

This may be completed in Word, Excel, or whatever program the student is most comfortable

using. (Separate file for upload to Blackboard)

 Diagram is complete with all tables, attributes, and keys (primary, alternate,

secondary and/or foreign).

 ER Diagram is formatted according to instructions provided in Chapter 6 in the

textbook.

Part Four: Users and Roles/Data Permissions (0-10 points)

Users must be recommended to provide levels of access to the database, in order to perform

functions such as database administration, data entry, and management decision-making.

Suggested users, roles and permissions as related to access and security must be required:

please specify what data and types of reports each user would most likely need access to, and

privileges in the database that would most likely be assigned. Support your answer with

information from the textbook or other valid sources (Separate file for upload to Blackboard).

 User permissions clearly specify the kind of access each user role has to the objects

(tables, reports, etc.) in the database.

 Examples of specific commands that would be used to grant privileges are provided.

 Supporting information from the text is provided.

Part Five: Backup/Archive (0-10 points)

Backup (0-5 points)

Suggested backup/archive procedures: Please suggest procedures that would be used for

backup and archive of the database. Provide a detailed rationale for each, using support

from the textbook or other valid sources. The emphasis is should be on process and

procedures, as well as rationale for why and how each should be done; however, you may

include specific technologies that you would recommend (Separate files for backing up

and archiving for upload to Blackboard).

 Suggested procedures and schedule for backup of the database are provided.

 Details regarding specific backup technologies and procedures are provided.

 A detailed rationale, with supporting information from the text, is provided.

Archive (0-5 points)

 Description of the differences between backup and archive must be provided.

 Suggested procedures and schedule for archival of the database are provided.

 Details regarding specific archive procedures are provided.

 A detailed rationale, with supporting information from the text, is provided.

Scenario

You have been asked to develop a database system to track the movies your customers viewed

over the past summer. The database system must structured in such a way as to be able to

produce a variety of reports related to the movies viewed:

Movies Released During Summer

Movie Genre Rating

X-Men: Days of

Future Past

Action/Adventure PG-13

Maleficent Family and Kids PG

Edge of Tomorrow Science Fiction PG-13

How to Train your

Dragon 2

Family and Kids PG

Transformers: Age of

Extinction

Action/Adventure PG-13

Planes: Fire and

Rescue

Family and Kids PG

Hercules Action/Adventure PG-13

Guardians of the

Galaxy

Action/Adventure PG-13

Step Up: All In Drama PG-13

Teenage Mutant

Ninja Turtles

Action/Adventure PG-13

The Hundred Foot

Journey

Drama PG

When the Game

Stands Tall

Drama PG

Movies Viewed

The following movies were viewed during the summer. (Information not displayed here, but that

needs to be collected, includes address and any other pertinent identifying information for

customers).

Customer and Movie Viewed Numeric Feedback Rating*

1-5

Charlotte Osborne

Maleficent 5

Edge of Tomorrow 5

How to Train your Dragon 2 4

Cory Simon

Planes: Fire and Rescue 1

Hercules 2

Guardians of the Galaxy 1

Step Up: All In 3

Jan Cruz

The Hundred Foot Journey 3

When the Game Stands Tall 4

Arnold Nunez

X-Men: Days of Future Past 5

Maleficent 5

Edge of Tomorrow 4

How to Train your Dragon 2 4

Transformers: Age of Extinction 3

Gilberto Hale

How to Train your Dragon 2 2

Transformers: Age of Extinction 2

Chad Olson

When the Game Stands Tall 1

Jodi Knight

Maleficent 1

Emanuel Ingram

Teenage Mutant Ninja Turtles 5

The Hundred Foot Journey 5

When the Game Stands Tall 4

Carol Rodgers

X-Men: Days of Future Past 4

Maleficent 4

Edge of Tomorrow 4

Freddie Simpson

The Hundred Foot Journey 3

Ebony King

Guardians of the Galaxy 3

Step Up: All In 5

Teenage Mutant Ninja Turtles 5

Kim Marshall

Planes: Fire and Rescue 5

Hercules 4

Guardians of the Galaxy 4

Step Up: All In 1

* 1 – hated it, 2 –disliked it, 3 – neither liked nor disliked, 4 – liked it, 5 – loved it