= Relational Design

== Notation
- Primary keys are enclosed in [ ] and underlined.
- Foreign keys are marked with * at the end of their name and the referenced entity is written in parentheses.

== Tables ==
'''University''' ( Id, Name, Location, is_private )

'''Faculty''' ( Id, University_Id* (University), Name, Location, Study_field )

'''Professor''' ( Id, Faculty_Id* (Faculty), Name, Surname, Age )

'''Student''' ( Id, Faculty_Id* (Faculty), Name, Surname, Location, Student_Index )

'''Subject''' ( Id, Faculty_Id* (Faculty), Name, Semester, Credits )

'''Student_Subject''' ( [Student_Id* (Student), Subject_Id* (Subject), Ss_Id], Professor_Id* (Professor), Enrollment_Date, Status, Final_Grade, Abesense_Count )

'''Advice''' ( [Student_Id* (Student), Professor_Id* (Professor), Start_Date], End_Date )

'''Affiliated''' ( [University_Id* (University), Professor_Id* (Professor)] )

'''Teach''' ( [Professor_Id* (Professor), Subject_Id* (Subject)] )

== DDL script for creating the database schema and objects:
[attachment:schema_creation3.sql DDL script]

== DML script for inserting data in the tables 
[attachment:data_load.3.sql DML script]


== Relational diagram made in DBeaver 
[[Image(relational_schema2.png)]]

= AI Usage for Relational Design =

Name of AI service/solution that was used: ChatGPT (OpenAI)

URL: https://chatgpt.com/

Type of service/subscription: Free Tier

Final result: I reviewed my updated conceptual ER model from Phase 1 to correctly map all entities, weak entities, and relationships into a logical relational database schema. The weak entity Student_Subject was correctly materialized with a composite primary key formed by its owner entities' keys plus its discriminator (Student_Id, Subject_Id, Ss_Id), while all M:N relationships (Advises, affiliated_with, Teach) were materialized as bridge tables.

== Final Result

== Tables
'''University''' ( Id, Name, Location, is_private )

'''Faculty''' ( Id, University_Id* (University), Name, Location, Study_field )

'''Professor''' ( Id, Faculty_Id* (Faculty), Name, Surname, Age )

'''Student''' ( Id, Faculty_Id* (Faculty), Name, Surname, Location, Student_Index )

'''Subject''' ( Id, Faculty_Id* (Faculty), Name, Semester, Credits )

'''Student_Subject''' ( [Student_Id* (Student), Subject_Id* (Subject), Ss_Id], Professor_Id* (Professor), Enrollment_Date, Status, Final_Grade, Abesense_Count )

'''Advice''' ( [Student_Id* (Student), Professor_Id* (Professor), Start_Date], End_Date )

'''Affiliated''' ( [University_Id* (University), Professor_Id* (Professor)] )

'''Teach''' ( [Professor_Id* (Professor), Subject_Id* (Subject)] )

----

== DDL script for creating the database schema and objects:
[attachment:schema_creation3.sql DDL script]

== DML script for inserting data in the tables 
[attachment:data_load.3.sql DML script]


== Relational diagram made in DBeaver 
[[Image(relational_schema.png)]]


**Results in details / description:**

Schema Mapping & Normalization Review: All conceptual entities from Phase 1 were mapped to relations. Foreign keys were introdu
== Entire AI usage log:

Schema Mapping & Normalization Review: All entities from Phase 1 were mapped to relations. Foreign keys were introduced to represent 1:N relationships (`University_Id` in Faculty, `Faculty_Id` in Professor, Student, and Subject).

Weak Entity Materialization: The weak entity `Student_Subject` was mapped by forming a composite primary key consisting of its discriminator (`Ss_Id`) along with the primary keys of its owner entities (`Student_Id` and `Subject_Id`). The regular 1:N relationship with `Professor` (`assignTo`) was materialized via a foreign key `Professor_Id` in this table.

Relationship Materialization:
- The M:N relationship `Advises` with attributes (`start_date`, `end_date`) was mapped to the `Advice` table with a composite primary key (`Student_Id`, `Professor_Id`, `Start_Date`).
- The M:N relationship `affiliated_with` was mapped to the `Affiliated` bridge table with composite primary key (`University_Id`, `Professor_Id`).
- The M:N relationship `Teach` was mapped to the `Teach` bridge table with composite primary key (`Professor_Id`, `Subject_Id`).