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:

DDL script​

DML script for inserting data in the tables

DML script

Relational diagram made in DBeaver

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:

DDL script​

DML script for inserting data in the tables

DML script

Relational diagram made in DBeaver

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