This Project Allows Students To Demonstrate Their Skills In
This Project Allows Students To Demonstrate Their Skills In The Area O
This project allows students to demonstrate their skills in the area of designing relational databases to satisfy specific business rules and requirements. The deliverables for this project include an Entity Relationship Diagram and detailed documentation describing the database design and structure.
In this project, you will be provided with a description of an application to create an entity-relationship diagram (ERD) and design accompanying table layout using sound relational modeling concepts and practices. The relationships between the entities and the attributes for the entities will be identified and described. This database will provide the foundation for the follow-on project.
You are a database consultant with Ace Software, Inc. and have been assigned to develop a database for the Mom and Pop Johnson video store in town. Mom and Pop have been keeping their records of videos and DVDs purchased from distributors and rented to customers in handwritten records, invoices, and rental forms for years. They have decided to automate their record-keeping with a relational database.
After observing their operation for about a week, you learn that a video and a DVD are both copies of a movie kept in a separate plastic case that is rented out. Several copies of each movie are available in their inventory, and they have several thousand videos and DVDs, which they acquire wholesale from about six distributors. The wholesale prices differ based on shipment quantity and previous business volume with each distributor. The price for a DVD or video varies for the same movie depending on the format, distributor, and purchase conditions.
Distributors provide electronic catalogs, and these catalogs include information such as movie types (e.g., suspense, horror, mystery, comedy), and the same movie can be obtained from multiple distributors at different wholesale prices. Each video and DVD has a unique internal identification number assigned by Mom and Pop, as well as the distributor’s serial number. Similarly, each movie has a unique ID assigned by Mom and Pop, and the distributors’ catalog listings include movie IDs. Multiple actors, actresses, directors, and awards are associated with each movie, with no restriction on the number of entries per entity.
The database must also record rental transactions including rental date, return date, late fees, damage fees, failure to rewind fees, taxes, discounts, and other charges. It should generate reports, such as which videos are overdue, rental history counts (monthly, yearly). Customer details are basic—name, address, phone numbers, etc.—and there is no limit to inventory size or the number of related entities like actors and awards.
Important constraints include: Video/DVD ID numbers, movie ID numbers, and distributor ID numbers are all distinct. Each movie can have many actors, actresses, directors, and awards. Equipment rentals and sales of other items are not part of this database design.
In this context, your tasks are to: Determine entities and create relationship sentence pairs (avoiding many-to-many relationships); develop a comprehensive ERD with all entities, attributes, and relationships, ensuring only one-to-many relationships; and produce metadata describing each table and column, emphasizing PK and FK constraints. All work should be compiled into a single Word document.
Paper For Above instruction
The design of a relational database for the Mom and Pop Johnson video store requires careful analysis of the business processes and data requirements. To create an efficient and accurate database, the first step involves identifying key entities and their relationships while adhering to relational modeling principles that prevent many-to-many relationships. The subsequent diagrammatic and documentation phases translate these conceptual models into tangible database structures, ready for implementation.
Entities and Relationship Sentence Pairs
Based on the business scenario, the primary entities include Customer, Movie, Format (Video/DVD), Distributor, Inventory, Rental, and Actor/Actress/Director/Award entities. Each entity was determined based on discrete data classes that encapsulate the business information.
- Customer rents Format (Video/DVD) copies of Movie.
- Movie is supplied by one or more Distributors.
- Distributor provides multiple Movies and maintains an Electronic Catalog.
- Format (Video/DVD) are specific copies linked to Inventory, which tracks stock and serial numbers.
- Rental records link Customer with specific Format (Video/DVD) copies, including date, fees, and return status.
- Movie associates with multiple Actors/Actresses/Directors and Awards which are many-to-one or many-to-many, broken down into separate relational tables.
It is critical to ensure that all relationships are one-to-many where necessary, not many-to-many, by incorporating junction tables as needed.
Entity-Relationship Diagram (ERD)
The ERD encapsulates all chosen entities, their attributes, and the relationships. For example, the Movie entity includes attributes such as Movie_ID (PK), Title, Year_Released, Rating, and Running_Length. Distributor has Distributor_ID (PK), Name, and Contact_Info. The relationship between Movie and Distributor is modeled via a junction table, Movie_Distributor, which includes foreign keys to both entities and contains additional info like wholesale price.
The Format entity distinguishes between Video and DVD, with Format_ID as PK and attributes such as Format_Type. Inventory contains copy-specific information such as Copy_ID (PK), Movie_ID (FK), Format_ID (FK), and Serial_Number, with each row representing a unique copy.
The Rental entity logs rental transactions with attributes like Rental_ID (PK), Customer_ID (FK), Copy_ID (FK), Rental_Date, Return_Date, Fees, and Penalties. Relationships to the Customer and Inventory establish the rental history. Also, Actor/Actress/Director tables link via many-to-many relationships with Movie through junction tables such as Movie_Actor.
The ERD diagram should be prepared visually to illustrate these relationships clearly, with appropriate cardinality signs indicating the one-to-many nature of relationships.
Database Metadata and Table Layouts
Following ERD development, the next step involves detailed schema definitions. Each table corresponds to an entity with columns matching the entity's attributes. Primary keys are identified as PK, and foreign keys are marked as FK and linked explicitly to relevant primary keys, ensuring referential integrity.
For example, the Movies table has columns: Movie_ID (PK), Title, Year, Rating, and Running_Length. The Distributed_Movies junction table includes Movie_ID (FK), Distributor_ID (FK), and Wholesale_Price.
The Inventory table covers copies in stock, with Copy_ID as primary key, and foreign keys to Movie and Format tables. The Rental table includes Rental_ID, Customer_ID, Copy_ID, Rental_Date, and Returned_Date, with necessary FK constraints.
This schema ensures all constraints are strictly adhered to, supporting data integrity, efficient querying, and future scalability.
Conclusion
The comprehensive approach involving entity identification, relationship formulation, ERD creation, and precise schema documentation provides a robust foundation for developing a relational database tailored to the Mom and Pop Johnson video store operations. Such a implementation will streamline record-keeping, improve reporting, and support future business growth, illustrating core relational database design principles.
References
- Casteel, R. (2014). Database Design and Development. Pearson.
- Fundamentals of Database Systems (7th ed.). Pearson.