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

**Subject_Professor** ( __Subject_Id*__ (Subject), __Professor_Id*__ (Professor) )

**Student_Subject** ( __Ss_Id__, __Student_Id*__ (Student), __Subject_Id*__ (Subject), Final_Grade, Status, Enrollment_Date, Absences_Count )

----


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

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


== Relational diagram made in DBeaver 
[[Image(relational_schema1.jpg)]]

= AI Usage for Relational Design
ChatGPT – OpenAI
URL: https://chat.openai.com/

Type of subscription: Free online access (no paid subscription required)

Note: This AI tool was selected to assist with database design and relational modeling based on a short survey of available AI tools. Its responses helped in structuring tables, foreign key constraints, and M:N relationships for this project.

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

**Subject_Professor** ( __Subject_Id*__ (Subject), __Professor_Id*__ (Professor) )

**Student_Subject** ( __Ss_Id__, __Student_Id*__ (Student), __Subject_Id*__ (Subject), 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))


----


== 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_schema.jpg)]]



ChatGPT - https://chatgpt.com/c/69693542-7640-8327-920b-0065ba155df0