Assignment 2: Relational Databases Access And SQL Due By 11

Assignment 2 Relational Databases Accessand Sqldue Due By 1130 Pm

Analyze a basic problem from a structured, relational perspective; create an entity-relationship (ER) diagram to solve a basic database problem; create a simple relational database using MS Access; input data into an MS Access database; create, use, and display SQL queries from a relational database.

Paper For Above instruction

Relational databases are foundational to managing structured data efficiently and effectively. Their design hinges on understanding how entities relate to each other within a system. This paper discusses the process of designing and implementing a relational database for a movie and actor tracking system, illustrating key principles through a case study approach, including ER diagram creation, database construction, data entry, and querying.

The initial step involves analyzing stakeholder requirements, which in this case include tracking actors, movies, roles, ratings, and comments. The professor's specific needs—such as rating scales from 1 to 10, comments on movies and performances, and flexible data entry—shape the database design. Recognizing issues in the initial database attempt highlights common problems such as data redundancy and lack of normalization.

One problem observed is data redundancy. The sample data includes actor names, movie titles, and ratings stored in a flat structure, leading to duplicates and inconsistent data (e.g., repeated actor entries with varying spellings or ratings). This redundancy increases storage overhead and complicates data updates. The second problem is data inconsistency, seen where related data such as actor details and movie information are scattered, making it challenging to maintain data integrity and perform accurate queries.

To address these issues, a normalized database structure is designed featuring three tables: Actors, Movies, and Roles. The Actors table contains actor-specific information, with a primary key such as ActorID. The Movies table holds movie details, with MovieID as its primary key. The Roles table serves as a junction table, linking actors to movies and capturing role-specific data, including actor performance ratings and comments, with its own primary key, and foreign keys referencing Actors and Movies.

Entity-Relationship Diagram

A coherent ER diagram illustrates the three tables and their relationships. The Actors table includes attributes such as ActorID (Primary Key), FirstName, LastName, and ActorRating. The Movies table contains MovieID (PK), Title, Year, Director, Category, and MovieRating. The Roles table includes RoleID (PK), ActorID (FK), MovieID (FK), RoleName, PerformanceRating, and Comments. Relationships are established with foreign keys: ActorID in Roles links to ActorID in Actors; MovieID in Roles links to MovieID in Movies. The cardinality between Actors and Roles is one-to-many, as each actor can perform in multiple roles, and similarly for Movies.

This ER diagram offers a robust blueprint for constructing the database, ensuring data normalization, reducing redundancy, and enabling efficient querying and data integrity through well-defined relationships.

Creating the Database in MS Access

Using the ER diagram as a guide, the next phase involves implementing the designed structure in MS Access. The process begins with creating three tables: Actors, Movies, and Roles. During table creation, primary keys are designated (ActorID, MovieID, RoleID), and appropriate data types are assigned—e.g., Short Text for names, Number for ratings, and Date/Time for years. After defining tables, relationships are set using the Relationships tool, linking foreign keys appropriately and enforcing referential integrity.

Data entry entails populating these tables with existing data, ensuring consistency with the ER diagram. For instance, actors like Jack Nicholson and Matt Damon, movies like 'The Departed,' are added with all relevant details. Additional data, such as new actors and movies, are also incorporated using external sources like IMDb for accuracy.

Constructing SQL Queries

The power of relational databases lies in their ability to answer complex questions through SQL queries. For example, to list movies rated worse than 9, a query uses SELECT to specify fields, FROM to specify the Movies table, and WHERE with the condition MovieRating < 9. Similarly, querying actors rated better than 7 involves referencing the Actors table with a WHERE clause on ActorRating.

More advanced queries link tables to answer questions about performances and relationships, such as listing movies and roles with ratings above certain thresholds, joining Movies, Roles, and Actors tables on their foreign keys. These queries are written explicitly in SQL view, giving precise control over data retrieval.

Expanding the ER Diagram

Based on client feedback, the ER diagram is extended to include Director information—name and comments—by adding a Directors table, linked to Movies via a foreign key. Directors are assigned unique identifiers, and their ratings are stored similarly to actors and movies. The updated ER diagram demonstrates this relationship and ensures consistency across the database design.

An SQL query is crafted to identify movies and directors both rated 8 or worse, illustrating the integration of the new table into existing queries.

Conclusion

Designing a relational database requires careful analysis, normalization, and detailed planning. The development process, from creating an ER diagram to implementing tables, entering data, and writing SQL queries, exemplifies core database management principles. Proper design enhances data integrity, reduces redundancy, and facilitates meaningful data analysis, essential for maximizing the utility of relational databases in research and enterprise applications.

References

  • Elmasri, R., & Navathe, S. B. (2015). Fundamentals of Database Systems (7th ed.). Pearson.
  • Coronel, C., & Morris, S. (2015). Database Systems: Design, Implementation, & Management (11th ed.). Cengage Learning.
  • Rob, P., & Coronel, C. (2007). Database System Concepts (5th ed.). Thomson.
  • Date, C. J. (2012). Database Design and Relational Theory. O'Reilly Media.
  • Harrington, J. L. (2016). Relational Database Design Clearly Explained. Morgan Kaufmann.
  • Hoffer, J. A., Venkataraman, R., & Topi, H. (2016). Modern Database Management (12th ed.). Pearson.
  • Silberschatz, A., Korth, H. F., & Sudarshan, S. (2010). Database System Concepts (6th ed.). McGraw-Hill.
  • Heuer, B. (2016). Introduction to SQL. In Microsoft Office Access 2016: Illustrated Introductory, (pp. 350-370). Pearson.
  • Schneider, K. (2018). Practical Data Modeling with ER Diagrams. Journal of Data Management, 22(3), 45-60.
  • IMDB. (2024). Internet Movie Database. Retrieved from https://www.imdb.com

Related Assignments