Entity-Relationship Model

Entities

1. User - Represents any registered person in the system with personal information

• user_id (bigint, required)
• email (text, required, unique)
• username (bigint, required, unique)
• password (bigint, required)
• date_created (date, required)
• shipping_address (text, optional)
• telephone_number (text, optional)

2. Admin - A type of user responsible for managing products and performing modifications

• type (enum, required)
• discount_percentage (numeric, required)

3. Consumer - type of user who can purchase products and collect loyalty points

• points_collected (bigint)

4. Wishlist - A collection of products that a user saves for future interest

• wishlist_id (bigint, required)

5. WishlistItem - A specific product added to a wishlist

• wishlist_id* (bigint)
• product_id* (bigint)
• added_at (date, required)

6. Product - A purchasable item with price, stock, and format

• product_id (bigint)
• format (enum, required)
• price (numeric, required)
• description (text, required)
• stock (bigint, required)

7. Release - A musical work that groups different product formats

• release_id (bigint)
• title (text, required)
• record_label (text, optional)
• genre (text, required)
• release_date (date, required)
• cover_photo (text, required)

8. Album - A type of release consisting of multiple songs

• release_id* (bigint)

9. Single - A type of release typically representing a single track

• release_id* (bigint)
• duration (text, required)

10. Artist - A creator or performer associated with releases and songs

• artist_id (bigint)
• artist_name(text, required)
• description(text, optional)
• photo (text, optional)

11. Song - An individual track that can belong to albums

• song_id (bigint)
• song_name(text, required)
• duration (text, required)

12. OrderItem - A specific product entry within an order

• order_id* (bigint)
• product_id* (bigint)
• price_at_purchase (numeric, required)
• quantity (bigint, required)

13. Order - A transaction made by a user containing purchased products

• order_id (bigint)
• payment_method (enum, required)
• purchase_date (date, required)
• points_earned (bigint, required)
• points_used (bigint, optional)
• status (enum, required)

14. Modification - An action performed by an admin that changes one or more products

• modification_id (bigint)
• date_modified (date, required)
• type_of_modification(enum, required)
• discount (numeric, optional)

Relationships

1. contains (Album ↔ Song, M:N)

Each Album contains Songs, while the same Song can be in multiple albums.

2. has (Release ↔ Artist, M:N)

Each Artist has multiple Releases, and multiple Artists can be associated with one Release.

3. features (Song ↔ Artist, M:N)

Each Song can feature multiple Artists, and each Artist can be featured in multiple Songs.

4. version_of (Product ↔ Release, N:1)

Each Product represents a specific format of one Release, while a Release can have multiple Products.

5. refers (OrderItem ↔ Product, N:1)

Each OrderItem refers to one Product, while a Product can appear in multiple OrderItems.

6. contains (Order ↔ OrderItem, 1:N)

Each Order contains multiple OrderItems, while each OrderItem belongs to exactly one Order.

7. creates (User ↔ Order, 1:N)

Each User can create multiple Orders, while each Order is created by exactly one User.

8. modifies (Admin ↔ Modification, 1:N)

Each Admin can perform multiple Modifications, while each Modification is performed by exactly one Admin.

9. refers (Modification ↔ Product, M:N)

Each Modification can affect multiple Products, while each Product can be affected by multiple Modifications.

10. has (User ↔ Wishlist, 1:1)

Each User has exactly one Wishlist, and each Wishlist belongs to exactly one User.

11. has (Wishlist ↔ WishlistItem, 1:N)

Each Wishlist contains multiple WishlistItems, while each WishlistItem belongs to exactly one Wishlist.

12. refers (WishlistItem ↔ Product, N:1)

Each WishlistItem refers to one Product, while a Product can appear in multiple WishlistItems.

Entity-Relationship Model History

List of each version, with a one-sentence explanation of the changes in each version, i.e. what was corrected in that version compared to the previous one. If you made the correction at the request of the teacher/assistant based on a ticket posted in the system, you can link to the ticket instead of an explanation.