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. PRODUCT (product_id, price, format, description, stock)

6. RELEASE (release_id, title, release_date, genre, cover_photo, record_label)

7. ALBUM (release_id*[RELEASE])

8. SINGLE (release_id*[RELEASE], duration)

9. SONG (song_id, song_name, duration)

10. ORDER (order_id, user_id*[USER], purchase_date, status, payment_method, points_earned, points_used)

11. ORDER_ITEM (order_id*[ORDER], product_id*[PRODUCT], quantity, price_at_purchase)

12. ARTIST (artist_id, artist_name, photo, description)

13. MODIFICATION (modification_id, user_id*[ADMIN], date_modified, type_of_modification, discount)

DDL script for creating the database schema and objects:

DML script for filling tables with data:

Relational diagram