Microsoft Access database

 

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:

· 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, the rationale he/she used in developing the specified set of tables, as well as the specific fields in the tables.

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

· 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)

· Suggested user roles and permissions as related to access and security: Using the users identified below, 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)

· 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 file for upload to Blackboard)

· 3 potential reports that could be generated in response to user requests; supporting queries must be included (ex.: report of users that took the A+ in 2013; grouped by instructor, sorted by pass/fail). Rationale required 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. (Will be included in database)

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

Users

The following users must have some level of access to the database:

· Data Administrators: data administrators are responsible for maintaining the customer records, movies released, the genres, and the ratings.

· Data Entry Staff: Data entry staff are responsible for entering the information related to movies the customers have seen, and their numeric feedback rating.

· Management: Viewing statistics reports are used by management to track viewing for marketing of other products to customers.

CIT2410 Project Rubric

Tables (0-20 points)

· All entries are 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.

· Rationale used in developing the specified set of tables, as well as the specific fields in the tables is provided.

Keys (0-10 points)

· All appropriate keys have been identified and their functions described.

· Primary key is a minimal collection of columns on which all columns are functionally dependent.

E-R Diagram (0-10 points)

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

DBDL Diagram (0-10 points)

· Diagram is complete with all tables, attributes, and keys (primary, alternate, secondary and/or foreign).

· DBDL is written using correct formatting.

User Roles/Data Permissions (0-10 points)

· User permissions clearly specify the kind of access each user role has to the objects (tables, reports, etc.) in the database.

· Supporting information from the text is provided.

Backup (0-10 points)

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

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

Archive (0-10 points)

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

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

Reports (0-10 points)

· 3 potential reports that could be generated in response to user requests are identified.

· Rationale for why each report could provide important information for decision-making is provided.

Supporting Queries (0-10 points)

· Queries are provided, and specify criteria for record selection, as well as grouping, sorting and any other requirements for the specific report.