Determine And List Your Entities, Then Create Relationships

Determine and list your entities . Then create relationship sentence pairs between those entities that

Alonzo Simmons, Jr. CMIS 320 Project 1 Step 1) (7 points) Determine and list your entities. Then create relationship sentence pairs between those entities that are related. You should not have any many-to-many relationships.

Entities to identify include: CUSTOMER, RENTALS, INVENTORY, RATES, MOVIES, STARS, RELATIONSHIPS. The relationships are as follows:

  • Each CUSTOMER charged for one or more RENTALS. One or more RENTALS are charged to the CUSTOMER.
  • RENTALS rented from INVENTORY with each rental renting only one item from inventory. INVENTORY rents one or more RENTALS.
  • RENTAL prices calculated using one, and only one RATE. INVENTORY purchased one or more MOVIES.
  • One or more MOVIES were purchased by INVENTORY. MediaType is either Video or DVD.
  • MOVIES made by STARS (directors, actors, etc.), and each movie made by one or more stars.
  • One or more STARS make MOVIES.

The context is the Mom and Pop Johnson Video Store Database.

Create an entity/relationship diagram (ERD) showing all your entities, attributes, and relationships

The ERD should be sketched either by hand or using a drawing program, and must fit on a single page. All entities should be related to at least one other entity. The diagram should include all one-to-many relationships and eliminate any many-to-many relationships. This includes defining primary keys and foreign keys appropriately, matching the relationships described above.

Create metadata that describes the table created from each entity and the columns derived from each attribute in the ERD

The metadata must specify primary keys (PK) and foreign keys (FK) explicitly, ensuring consistency with the ERD. This includes tables for:

  • CUSTOMERS: CustomerID (PK), Name, Email, Address, Phone
  • RENTALS: RentalID (PK), InventoryID (FK), CustomerID (FK), DueDate, DateOut, RateID (FK)
  • RATES: RateID (PK), RentalFee, LateFee, DamageFee
  • INVENTORY: InventoryID (PK), MovieID (FK), MediaType, RentalStatus, Distributor, Price, SerialNumber
  • MOVIES: MovieID (PK), Title, Year, Length, Rating, Nominations, MadeBy (e.g., Director/Studio)
  • STARS: StarID (PK), Name, Role, Address, Phone, Email
  • Movie_Stars: MovieID (FK), StarID (FK) – junction table to handle many-to-many between MOVIES and STARS

All relationships should be appropriately established via foreign keys, adhering to the constraints described above, to avoid many-to-many relationships and to ensure referential integrity.

Paper For Above instruction

The Mom and Pop Johnson Video Store requires a well-structured relational database to manage its rentals, inventory, movies, customers, rates, and stars involved in movie productions. The design begins with identifying the core entities that realize the business processes and data requirements. These entities include CUSTOMER, RENTALS, INVENTORY, RATES, MOVIES, and STARS, each of which hold critical information for operation management.

The CUSTOMER entity contains customer-specific details such as CustomerID (acting as the primary key), Name, Email, Address, and Phone. Each customer can rent multiple movies, which creates a one-to-many relationship between CUSTOMER and RENTALS. This relationship ensures that each rental transaction can be traced back to the customer responsible for it.

RENTALS serve as a transaction record, linking customers to specific inventory items during rental periods. RentalID is the primary key, with foreign keys InventoryID and CustomerID referencing the INVENTORY and CUSTOMER tables respectively. Additional attributes such as DueDate, DateOut, and RateID (linking to the RATES table) are essential for tracking rental duration, fees, and penalties.

The INVENTORY entity catalogues all rentable items, including attributes like InventoryID (PK), MovieID (FK), MediaType (either Video or DVD), RentalStatus (whether available or rented), Distributor, Price, and SerialNumber. Each inventory item is associated with a specific movie, requiring a foreign key relationship to the MOVIES table.

MOVIES are central to the database, with attributes like MovieID (PK), Title, Year, Length, Rating, and Nominations. To account for movies made by multiple stars and stars appearing in multiple movies, a many-to-many relationship is established through an associating junction table, Movie_Stars, which references MovieID and StarID as foreign keys.

The STARS entity records information about actors, directors, or other notable contributors to movies, including StarID (PK), Name, Role, Address, Phone, and Email. The Movie_Stars table links stars to movies, capturing their roles and enabling queries about who starred in which films.

The RATES entity defines rental pricing schemes with a primary key RateID (PK) and associated fees: RentalFee, LateFee, and DamageFee. Each rental transaction references a specific rate to standardize pricing.

All relationships between entities are designed as one-to-many, aligning with the business rules specified. For example, each rental is linked to one inventory item, one customer, and one rate. Conversely, inventory items can be rented many times, and each movie can be associated with multiple stars, reflecting the many-to-many relationship managed via the Movie_Stars junction table.

This relational schema ensures data integrity, avoids many-to-many relationships within primary entities, and supports the operational demands of the video store, including tracking rentals, managing inventory, and maintaining details on movies and their stars.

References

  • Elmasri, R., & Navathe, S. B. (2015). Fundamentals of Database Systems (7th ed.). Pearson.
  • Rob, P., & Coronel, C. (2007). Database Systems (8th ed.). Cengage Learning.
  • Date, C. J. (2004). An Introduction to Database Systems (8th ed.). Pearson.
  • Connolly, T., & Begg, C. (2014). Database Systems (6th ed.). Pearson.
  • Kroenke, D. M. (2015). Database Processing: Fundamentals, Design, and Implementation. Pearson.
  • Hoffer, J. A., Venkataraman, R., & Topi, H. (2016). Modern Database Management (12th ed.). Pearson.
  • Silberschatz, A., Korth, H. F., & Sudarshan, S. (2019). Database System Concepts (7th ed.). McGraw-Hill Education.
  • Ullman, J. D., & Widom, J. (2008). A First Course in Database Systems. Pearson.
  • Coronel, C., & Morris, S. (2015). Database Systems: Design, Implementation, & Management (11th ed.). Cengage Learning.

Related Assignments