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:
• 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.