The Objective Of This Project Is For Students To Apply The D

The Objective Of This Project Is For Students To Apply The Database De

The objective of this project is for students to apply the database design principles to a real-life scenario. Students are required to create and submit a comprehensive database system based on the scenario provided, which involves tracking movies watched by customers over the summer. The deliverables include normalized database tables, an Entity-Relationship (E-R) diagram, a Database Definition Language (DBDL) diagram, user roles and permissions with security considerations, backup and archive procedures, and potential reports with supporting queries.

Students must design a database that adheres to third-normal form, ensuring data integrity and minimizing redundancy. Each table must have clearly defined primary keys, with appropriate foreign keys establishing relationships among tables. The rationale behind table design, including selected fields and key functions, must be clearly documented.

An Entity-Relationship (E-R) diagram must visually represent all entities, attributes, and relationships within the database. The DBDL diagram should detail the table structures, including attributes and keys, formatted correctly using the chosen software tools.

Additionally, students should define user roles—such as Data Administrators, Data Entry Staff, and Management—and specify the access privileges and security measures appropriate for each role. Backup and archive procedures should be thoughtfully proposed, detailing the processes, schedules, and technologies involved, supported by appropriate justification.

Three potential reports, beneficial for decision-making, must be identified, along with the specific queries required to generate these reports. The queries should specify record selection criteria, grouping, and sorting methods to demonstrate how the reports will function effectively.

Paper For Above instruction

The development of a comprehensive movie viewing database system necessitates meticulous planning and adherence to sound database design principles. This paper describes the process of designing such a system, including normalization of tables, creation of an E-R diagram, development of a DBDL diagram, user roles and permissions, backup and archive procedures, and report generation strategies. Each component is justified with reference to established database management best practices.

Introduction

The primary goal of this project is to create a normalized relational database that accurately reflects movie viewings by customers during a specific time frame. The database must facilitate efficient data retrieval and support various reporting needs for different user roles. To achieve this, the design process involved identifying entities, attributes, and relationships inherent in the scenario, followed by normalization to third normal form to ensure data integrity and reduce redundancy.

Database Design and Normalization

The core entities identified include Customers, Movies, Genres, Ratings, and Views. Each entity's attributes are carefully assigned to minimize duplication while capturing essential information. For instance, the Customers table includes customer identifiers and contact information, while the Movies table includes movie identifiers, titles, release years, and associated genre and rating identifiers.

Normalization to third normal form was achieved by ensuring each table contained atomic values, with no transitive dependencies. Primary keys were assigned to uniquely identify records—such as CustomerID for Customers and MovieID for Movies—and foreign keys established relationships, such as GenreID linking Movies to Genres. This structure supports data consistency and integrity, facilitating future modifications or expansions of the database.

Entity-Relationship Diagram

The E-R diagram visually represents the relationships among entities. Customers are linked to Views through a one-to-many relationship, indicating that each customer can view multiple movies. Movies are linked to Genres and Ratings via many-to-one relationships, implying that each movie belongs to a single genre and rating category. The diagram ensures clarity in data relationships and supports the comprehensive integrity of the database design.

Database Definition Language (DBDL) Diagram

The DBDL diagram translates the E-R model into table structures with appropriate attribute definitions and keys. For example, the Customers table includes CustomerID (PK), Name, Address, and PhoneNumber. The Views table combines CustomerID and MovieID as foreign keys, with an additional FeedbackRating attribute. This structure enables complex querying and reporting capabilities essential for the scenario.

User Roles, Permissions, and Security

Three key user roles are defined: Data Administrators, Data Entry Staff, and Management. Data Administrators possess full access rights, including creating, modifying, and deleting table structures and data, ensuring database maintenance and security. Data Entry Staff have permissions limited to inserting and updating views data relevant to movies watched and ratings. Management roles are restricted to reading reports and statistical summaries, supporting decision-making without altering underlying data.

Security measures include attribute-level access controls, login authentication, and encrypted connections. Based on best practices, role-based access control (RBAC) ensures that users can only access data pertinent to their responsibilities, minimizing security risks.

Backup and Archive Procedures

Regular backups are critical for data preservation. A proposed schedule includes full backups weekly and incremental backups daily to minimize data loss. Backup storage can be off-site or cloud-based, employing technologies such as automated backup software (e.g., Bacula, Veeam). Rationale for this schedule prioritizes data safety, minimizing downtime and ensuring quick recovery in case of failure or corruption.

Archiving procedures involve periodically moving historical data to secure, long-term storage media to optimize database performance. Archived data should be stored in formats compatible with current systems, with documented retention policies compliant with data governance standards.

Potential Reports and Supporting Queries

Three significant reports identified include:

1. Weekly Movie Viewing Summary: Shows total views per movie during a selected week. Supported by a query filtering Views by date range, grouping by MovieID, and counting entries to evaluate watch popularity.
2. Customer Viewing Patterns: Lists customers with the highest ratings for movies viewed during summer. The query filters Views by customer, joins to Movies and Ratings, and orders results by rating and customer name.
3. Genre Popularity Report: Displays total views per genre and average ratings, helping marketing strategies. The query aggregates Views by genre, using groupings, and computes averages for ratings.

These reports facilitate strategic decision-making, optimize marketing, and enhance customer engagement, supporting data-driven management decisions.

Conclusion

Implementing a robust, normalized database system for tracking summer movie viewings involves thoughtful design, detailed documentation, and rigorous testing. By integrating normalization, comprehensive ER and DBDL diagrams, role-based security, sound backup strategies, and meaningful reports, the database will serve as a reliable foundation for managing and analyzing customer movie-watching behaviors. Such a system ensures data integrity, promotes security, and provides valuable insights, aligning with best practices in database management.

References

• Fundamentals of Database Systems (7th ed.). Pearson.
• Database Systems: Design, Implementation, & Management (12th ed.). Cengage Learning.
• Database Design and Relational Theory: Normal Forms and Their Application. O'Reilly Media.
• Database Management Systems (3rd ed.). McGraw-Hill.
• Database System Concepts (7th ed.). McGraw-Hill.
• Database Concepts (8th ed.). Pearson.
• Texts and Tools for Database Management. Pearson.
• Journal of Information Technology.
• TechTarget.
• International Journal of Data Management.