Entity-Relationship Model v.02

Entities

List all entities and relationships with the following information for each of them

Entity/relationship name: one paragraph of explanation, what does the entity/relationship represent, why it is needed, why is it modeled the way it is

Candidate keys: If you have multiple candidates for the primary key, list them and explain which one you chose as the primary key and why you chose it as the primary key

List of entity attributes, for each of them: the data type (numeric/text/date/…) and whether there are any restrictions in the input form/format (required/optional, special format, value interval, …)

NAME OF ENTITY/RELATIONSHIP

DESCREPTION OF THE ENTITY/RELATIONSHIP

• Attributes:
  • NAME_OF_ATTRIBUTE (TYPE, Required/Optional) - DESCREPTION

1.User:

• user_id (bigint) - primary key
• email (text)
• username (bigint)
• password (bigint)
• date_created (date)

2.Admin:

• type (text)
• employee_id (bigint)

3.Consumer

• Candidate Keys
  • telephone_number (text)
  • shipping_address (text)
  • points_collected (bigint)

4.Wishlist:

• Candidate Keys
  • wishlist_id (bigint) - primary key

5.WishlistItem:

• Candidate Keys
  • wishlist_id (bigint) - foreign key(Wishlist)
  • product_id (bigint) - foreign key(Product)
  • added_at (date)

6.Product:

• Candidate Keys
  • product_id (bigint) - primary key
  • format (enum)
  • price (bigint)
  • description (text)
  • stock (bigint)

7.Release:

• Candidate Keys
  • release_id (bigint) - primary key
  • title (text)
  • record_label (text)
  • genre (text)
  • release_date (date)
  • cover_photo (text)

8.Album:

• Candidate Keys

9.Single:

• Candidate Keys
  • duration (text)

10.Artist:

• Candidate Keys
  • artist_id (bigint) - primary key
  • artist_name(text)
  • description(text)
  • photo (text)

11.Song:

• Candidate Keys
  • song_id(bigint) - primary key
  • song_name(text)
  • featured_artist (text)
  • duration (text)

12.OrderItem:

• Candidate Keys
  • order_id (bigint) - foreign key(Order)
  • product_id (bigint) - foreign key(Product)
  • price_at_purchase (bigint)
  • quantity (bigint)

13.Order

• Candidate Keys
  • order_id (bigint) - primary key
• Attributes:
  • payment_method (enum)
  • purchase_date (date)
  • points (bigint)

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.

• 3.version of (Release ↔ Product, 1:N) Each Release can be a different Product.

• 4.refers (Product ↔ OrderItem, 1:N) Each Product can be an OrderItem multiple times.

• 5.contains (OrderItem ↔ Order, N:1) Each Order contains atleast one OrderItem.

• 6.creates (Order ↔ User, N:1) Each User can create multiple Orders.

• 7.creates (Admin ↔ Product, 1:N) Each Admin can create multiple Products.

• 8.modifies (Admin ↔ Product, M:N) Each Admin can modify multiple Products.

• 9.has (Consumer ↔ Wishlist, 1:1) Each Consumer can have a Wishlist

• 10.has (Wishlist ↔ WishlistItem, 1:N) Each Wishlist can contain multiple WishlistItems.

• 11.refers (Wishlist ↔ Product, M:N) Every Product can be a WishlistItem.

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.