= Relational Design

== Notation
- Primary keys are 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, isprivate )

'''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''' ( [Ss_Id, Student_Id* (Student), Subject_Id* (Subject)], Professor_Id* (Professor), Final_Grade, Status, Enrollment_Date, Absences_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_creation.sql DDL script]

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


== Relational diagram made in DBeaver 
[[Image(relational_schema3.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 initial core database schema with the AI assistant to identify potential gaps in tracking student-professor interactions and professor-university ties. Based on these discussions, I successfully refined my tables by designing and incorporating the Advice and Affiliated relation schemas.

== Final Result

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

'''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''' ( [Ss_Id, Student_Id* (Student), Subject_Id* (Subject)], Professor_Id* (Professor), Final_Grade, Status, Enrollment_Date, Absences_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_creation.sql DDL script]

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


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


**Results in details / description:**


== 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`).