= 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** (__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** (__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