= Relational Design

==== Notation:
* **Primary keys** - underlined with bold lettering
* **Foreign keys** - marked with a * after the key name
* **Required attributes** - bold lettering

== Tables

{{{#!div style="text-align: justify; width: 100%;"

1. USERS (**__user_id__**, **username**, **email**, **password**, **date_created**, shipping_address, telephone_number)

2. ADMINS (**__user_id*__**[USER], **type**, **discount_percentage**)

3. CONSUMERS (**__user_id*__**[USER], **points_collected**)

4. WISHLISTS (**__wishlist_id__**, **__user_id*__**[USER])

5. WISHLIST_ITEMS (**__wishlist_id__**, **__product_id__**[PRODUCT], **added_at**)

6. PRODUCTS (**__product_id__**, **__release_id*__**[RELEASE], **price**, **format**, **product_description**, **stock**)

7. RELEASES (**__release_id__**, **title**, **release_date**, **genre**, **cover_photo**, record_label)

8. ALBUMS (**__release_id*__**[RELEASE])

9. SINGLE_RELEASES (**__release_id*__**[RELEASE], **song_duration**)

10. SONGS (**__song_id__**, **song_name**, **duration**)

11. ORDERS (**__order_id__**, **__user_id*__**[USER], **purchase_date**, **status**, **payment_method**, **points_earned**, **points_used**)

12. ORDER_ITEMS (**__order_id*__**[ORDER], **__product_id*__**[PRODUCT], **quantity**, **price_at_purchase**)

13. ARTISTS (**__artist_id__**, **artist_name**, artist_photo, artist_description)

14. MODIFICATIONS (**__modification_id__**, **__user_id*__**[ADMIN], **date_modified**, **type_of_modification**, discount)

15. MODIFICATION_PRODUCTS (**__modification_id*__**[MODIFICATION], **__product_id*__**[PRODUCT])

16. RELEASE_ARTIST (**__release_id*__**[RELEASE], **__artist_id*__**[ARTIST], **release_ordinal**, **type**)

17. ALBUM_SONG (**__album_id*__**[ALBUM], **__song_id*__**[SONG])

18. SONG_ARTIST (**__song_id*__**[SONG], **__artist_id*__**[ARTIST], song_ordinal)
}}}


==== DDL script for creating the database schema and objects:

[attachment:ddl-v3.sql​ DDL Script]

==== DML script for filling tables with data:

[attachment:dml-v3.sql​ DML Script]

== Relational Diagram

[[Image(relation-v2.png, align=center, width=100%)]]