Changes between Version 2 and Version 3 of RelationalDesign

Timestamp:

05/18/26 19:38:13 (9 days ago)

Author:

232012

Comment:

--

RelationalDesign

@@ -1 +1 @@
 = Relational Design
 
-Descriptive representation of the relational schema (syntax as used in lectures/exercises - listed relational schemas, attributes, marked primary and secondary keys)
+==== Notation:
+* **Primary keys** - underlined with bold lettering
+* **Foreign keys** - marked with a * after the key name
+* **Required attributes** - bold lettering
 
-== DDL script for creating the database schema and objects:
+== Tables
 
-* The script should (re-)create the schema, all the tables, with all constraints, and all other relevant database objects.
-* The script should work in an empty database to fully construct everything needed for the project, and should also work in a database where the schema, tables and other objects are already created and they need to be re-created. So drop existing objects and create them again.
-* The script should be named schema_creation.sql
-* Link the script in this section in the !RelationalDesign wiki page as an attachment to the page.
+{{{#!div style="text-align: justify; width: 100%;"
 
-== DML script for filling tables with data:
+1. USER (**__user_id__**, **username**, **email**, **password**, **date_created**, shipping_address, telephone_number)
 
-* The script should (re-)create all the data in your tables
-* The script should work with empty tables to fully load all needed data for the project, and should also work in a database where the tables already have data and need to be emptied and the data should be imported again.
-* The script should be named data_load.sql
-* Link the script in this section in the !RelationalDesign wiki page as an attachment to the page.
+2. ADMIN (**__user_id*__**[USER], discount_percentage, type)
+
+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, product_id*[PRODUCT]?????, title, release_date, genre, record_label, cover_photo)
+
+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_used, points_earned)
+
+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, discount)
+}}}
+
+
+==== DDL script for creating the database schema and objects:
+
+==== DML script for filling tables with data:
 
 == Relational diagram
-
-* DBeaver is the recommended tool for working with the assigned database, if you follow the instructions for connecting to the database.
-* In DBeaver it is possible to create a relational schema diagram for the entire project schema. Double click on the project schema to open it's properties dialog, then switch to the ER diagram tab. The diagram will be automatically created based on the created objects in the schema.
-* Switch the diagram to use crow-feet notation.
-* Visually adjust the diagram to have the same positioning of tables and relations as the corresponding entities and relations in the ER diagram in the previous phase.
-* Export the diagram to a file named relational_schema.jpg and attach it in this section.