You Are Asked To Develop A Database System To Keep Track Of
You Are Asked To Develop A Database System To Keep Track Of Produc
You are asked to develop a database system to keep track of product inventory for an online shopping website, PreownedDVDs.com. It sells used DVDs (movies, TV series, music videos). The company wants to keep track of previous owner, initial DVD release date, retail price, purchase date, condition, and the suggested used-sale price for each DVD. Additionally, it wants to catalog unique information for each DVD category: movies (directors, writers, stars, synopsis), TV series (creators, stars, episodes), and music videos (directors, producers, casts, music titles). The website displays at most two names for directors, stars, and similar attributes. Each DVD title has multiple DVDs for sale, and the company wants to know how many DVDs are available per title.
Paper For Above instruction
Developing an efficient and normalized database system for PreownedDVDs.com requires careful design to accommodate the complex relationships and varied attributes associated with each DVD category. The goal is to create a database in Third Normal Form (3NF) that captures all necessary data points, relationships, and constraints to manage inventory, categorize content, and facilitate querying about DVD availability and details.
Entity-Relationship (ER) Design Overview
The central entity in this design is the DVD_Title, representing each unique DVD content, whether movie, TV series, or music video. Each DVD title can have multiple physical DVDs (instances) for sale, represented by DVD_Instance entities, which track the specific copies, their purchase date, condition, and sale price.
Given the diversity in content, specialized entities are introduced for each category: Movie, TV_Series, and Music_Video. These entities are connected to DVD_Title via one-to-one relationships, embedding category-specific attributes.
To address the attributes that vary across categories, the ER model handles the following:
- Movie includes director(s), writer(s), star(s), and synopses.
- TV_Series includes creator(s), star(s), episodes.
- Music_Video includes director(s), producer(s), cast(s), music titles.
Since the number of people (directors, stars, etc.) can be up to two for each attribute, a design choice is to create separate entities for people involved and relationship entities to handle up to two participants.
Key Entities and Attributes
- DVD_Title
- TitleID (PK)
- TitleName
- Category (Movie, TV Series, Music Video)
- DVD_Instance
- DVDID (PK)
- TitleID (FK)
- PreviousOwner
- InitialReleaseDate
- RetailPrice
- PurchaseDate
- Condition
- UsedSalePrice
- AvailableQuantity
- Movie
- TitleID (PK, FK to DVD_Title)
- Synopsis
- TV_Series
- TitleID (PK, FK to DVD_Title)
- NumberOfEpisodes
- Music_Video
- TitleID (PK, FK to DVD_Title)
- MusicTitle
- Person
- PersonID (PK)
- Name
- Director
- PersonID (PK, FK to Person)
- Writer
- PersonID (PK, FK to Person)
- Star
- PersonID (PK, FK to Person)
- Creator
- PersonID (PK, FK to Person)
- Producer
- PersonID (PK, FK to Person)
- Cast
- PersonID (PK, FK to Person)
- DVD_Director
- DVDID (FK to DVD_Instance)
- PersonID (FK to Person)
- Movie_Writer
- DVDID (FK to DVD_Instance)
- PersonID (FK to Person)
- Movie_Star
- DVDID (FK to DVD_Instance)
- PersonID (FK to Person)
- TV_Creator
- DVDID (FK to DVD_Instance)
- PersonID (FK to Person)
- TV_Star
- DVDID (FK to DVD_Instance)
- PersonID (FK to Person)
- Music_Director
- DVDID (FK to DVD_Instance)
- PersonID (FK to Person)
- Music_Producer
- DVDID (FK to DVD_Instance)
- PersonID (FK to Person)
- Music_Cast
- DVDID (FK to DVD_Instance)
- PersonID (FK to Person)
- Music_Title
- DVDID (FK to DVD_Instance)
- TitleName
This model allows capturing the complex relationships and multiple participants for each DVD category, adhering to 3NF by removing transitive dependencies and ensuring minimal redundancy.
Normalization Process
The design ensures that:
- All data is stored in entities representing single concepts.
- Repeating groups (e.g., multiple directors or stars) are handled via link tables (e.g., DVD_Director).
- Partial dependencies are eliminated; for instance, DVD_Instance depends on DVDID, while specific attributes depend on DVD_Title and category.
- No transitive dependencies exist; for example, person details are stored once in Person rather than duplicating names.
Implementation Summary
The physical implementation involves creating tables for each entity with appropriate primary keys and foreign keys. The relationships between DVD instances and their category-specific information are managed via linking tables. The Movie, TV_Series, and Music_Video tables contain specific details pertinent to their genres. By doing so, the database maintains flexibility to accommodate various DVD categories, supports inventory tracking, and simplifies querying for available DVDs, their details, and involved personnel.
Conclusion
Designing a 3NF ER model for PreownedDVDs.com that captures all necessary attributes and relationships ensures data integrity, minimizes redundancy, and provides a scalable solution suited for online DVD inventory management. The approach balances normalization with practical considerations such as limiting the number of directors or stars to two, by using link tables, thus aligning with the system’s operational needs.