University:
California State University, Northridge
Course:
COMP 440 | Database Design
Academic year:

2024
Views:

60

Pages:

3
Author:

Samuel K.

Introduction This report outlines the key operational structure and business rules for the database design of a film rental store’s operation. Film • Each film has a unique identifier, title, description, release year, language, and original language (if applicable). • A film is associated with at least one film category. • Films can feature multiple actors, and actors can appear in several films. • Additional film information includes rental duration, rental rate, length, replacement cost, rating, and special features stored as a set. Business Rules: • Each film must be linked to a language and at least one category. • A film can have an original language that differs from its current language. Customer • Each customer has a unique identifier and is associated with a specific store. • Customer records include personal details such as name, email, address, phone number, and registration date. • Customers can be active or inactive. Business Rules: • Each customer must provide a valid first name, last name, address, and phone number at the time of registration. • Customers are linked to a single store but may rent from any store location. Staff Information • Staff members have unique identifiers and are linked to a specific store. • Staff records include name, address, email, username, password, and a profile picture. • Staff can be designated as active or inactive. Business Rules: • Each staff member is associated with one store location. Store • The rental service operates through multiple stores, each with a unique identifier and address. • Each store is managed by a designated staff member, and several employees can work at each store location. Business Rules: • Each store must have a unique identifier and a single manager. Inventory Management • Each physical copy of a film is represented as an inventory item, uniquely identified and associated with a specific store. • Multiple copies of the same film can exist within a store. • Each inventory item is linked to a store location and a film. Business Rules: • Every inventory item must belong to one specific store and represent one film. Rental Operations • Rentals are tied to a specific customer, inventory item, and the staff member who processes the transaction. • Rental information includes the rental date, return date, and a timestamp for the last update. Business Rules: • Rentals must be linked to a customer, an inventory item, and a staff member. • Rentals can only have one associated payment. Payment Processing • Payments are recorded for each rental transaction, specifying the amount and the payment date. • Payments are processed by staff members and are linked to both the customer and rental entry. Business Rules: • Payments must be associated with a customer, rental, and staff member. • All payments are recorded with the exact payment amount and date. Address and Location Information • Addresses are associated with specific cities, which in turn are linked to countries. • Each address has a unique identifier and is used for customers, staff, and stores. Business Rules: • Each address must be linked to a city and each city must be associated with a country. • Cities and countries must have unique identifiers.