Changes between Version 29 and Version 30 of RelationalDesign
- Timestamp:
- 09/09/26 12:36:22 (29 hours ago)
Legend:
- Unmodified
- Added
- Removed
- Modified
-
RelationalDesign
v29 v30 1 2 1 = Relational Design 3 2 4 3 == Notation 5 - Primary keys are underlined. 6 4 - Primary keys are enclosed in [ ] and underlined. 7 5 - Foreign keys are marked with * at the end of their name and the referenced entity is written in parentheses. 8 6 9 7 == Tables == 10 '''University''' ( Id, Name, Location, is private )8 '''University''' ( Id, Name, Location, is_private ) 11 9 12 10 '''Faculty''' ( Id, University_Id* (University), Name, Location, Study_field ) … … 18 16 '''Subject''' ( Id, Faculty_Id* (Faculty), Name, Semester, Credits ) 19 17 20 '''Student_Subject''' ( [S s_Id, Student_Id* (Student), Subject_Id* (Subject)], Professor_Id* (Professor), Final_Grade, Status, Enrollment_Date, Absences_Count )18 '''Student_Subject''' ( [Student_Id* (Student), Subject_Id* (Subject), Ss_Id], Professor_Id* (Professor), Enrollment_Date, Status, Final_Grade, Abesense_Count ) 21 19 22 20 '''Advice''' ( [Student_Id* (Student), Professor_Id* (Professor), Start_Date], End_Date ) … … 27 25 28 26 == DDL script for creating the database schema and objects: 29 [attachment:schema_creation .sql DDL script]27 [attachment:schema_creation3.sql DDL script] 30 28 31 29 == DML script for inserting data in the tables 32 [attachment:data_load .sql DML script]30 [attachment:data_load3.sql DML script] 33 31 34 32 35 33 == Relational diagram made in DBeaver 36 [[Image(relational_schema 3.png)]]34 [[Image(relational_schema2.png)]] 37 35 38 36 = AI Usage for Relational Design = … … 44 42 Type of service/subscription: Free Tier 45 43 46 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.44 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. 47 45 48 46 == Final Result 49 47 50 48 == Tables 51 '''University''' ( Id, Name, Location, is private )49 '''University''' ( Id, Name, Location, is_private ) 52 50 53 51 '''Faculty''' ( Id, University_Id* (University), Name, Location, Study_field ) … … 59 57 '''Subject''' ( Id, Faculty_Id* (Faculty), Name, Semester, Credits ) 60 58 61 '''Student_Subject''' ( [S s_Id, Student_Id* (Student), Subject_Id* (Subject)], Professor_Id* (Professor), Final_Grade, Status, Enrollment_Date, Absences_Count )59 '''Student_Subject''' ( [Student_Id* (Student), Subject_Id* (Subject), Ss_Id], Professor_Id* (Professor), Enrollment_Date, Status, Final_Grade, Abesense_Count ) 62 60 63 61 '''Advice''' ( [Student_Id* (Student), Professor_Id* (Professor), Start_Date], End_Date ) … … 69 67 ---- 70 68 71 72 69 == DDL script for creating the database schema and objects: 73 [attachment:schema_creation .sql DDL script]70 [attachment:schema_creation3.sql DDL script] 74 71 75 72 == DML script for inserting data in the tables 76 [attachment:data_load .sql DML script]73 [attachment:data_load3.sql DML script] 77 74 78 75 79 76 == Relational diagram made in DBeaver 80 [[Image(relational_schema 2.png)]]77 [[Image(relational_schema.png)]] 81 78 82 79 83 80 **Results in details / description:** 84 81 85 82 Schema Mapping & Normalization Review: All conceptual entities from Phase 1 were mapped to relations. Foreign keys were introdu 86 83 == Entire AI usage log: 87 84
