= 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. USER (**__user_id__**, **username**, **email**, **password**, **date_created**, shipping_address, telephone_number)

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

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

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

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

6. PRODUCT (**__product_id__**, **__release_id*__**[RELEASE], **price**, **format**, **description**, **stock**)

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

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

9. SINGLE (**__release_id*__**[RELEASE], **duration**)

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

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

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

13. ARTIST (**__artist_id__**, **artist_name**, photo, description)

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

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

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

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

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


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

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

== DML script for filling tables with data:

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

== Relational diagram