Relational Design

Notation:

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

Tables

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:

DDL Script​

DML script for filling tables with data:

DML Script​

Relational diagram