Changes between Version 12 and Version 13 of P1


Ignore:
Timestamp:
06/24/26 09:55:03 (40 hours ago)
Author:
216009
Comment:

--

Legend:

Unmodified
Added
Removed
Modified

  • P1

    v12 v13  
    88
    99
     10== Data Requirements
    1011
    11 == Data Requirements ==
    12 • University: This entity stores information about universities. We need it to know which faculties belong to which university.
     12=== '''University'''
     13This entity stores information about universities. It defines which faculties belong to which university and tracks affiliations with professors.
    1314
    14  º Candidate keys: Id (primary key).
     15'''Candidate keys:''' id (primary key).
    1516
    16  º Attributes:
     17'''Attributes:'''
    1718
    18   Id – number, required, unique
     19'''id''' – serial (integer), required, unique
    1920
    20   Name – text, required, max 255 characters
     21'''name''' – varchar(255), required
    2122
    22   Location – text, optional, max 255 characters
     23'''location''' – varchar(100), optional
    2324
    24   isprivate – boolean, required
     25'''isprivate''' – boolean, required (mapped to is_private on the diagram)
    2526
    26 ----
     27=== '''Faculty'''
     28Represents a faculty within a university. It organizes students, professors, and subjects. Each faculty belongs to one university.
    2729
    28 • Faculty: Represents a faculty inside a university. It helps organize students, professors, and subjects within that faculty. Each faculty belongs to one university.
     30'''Candidate keys:''' id (primary key).
    2931
    30  º Candidate keys: Id (primary key).
     32'''Attributes:'''
    3133
    32  º Attributes:
     34'''id''' – serial (integer), required, unique
    3335
    34   Id – number, required, unique
     36'''name''' – varchar(255), required
    3537
    36   Name – text, required, max 255 characters
     38'''location''' – varchar(100), optional
    3739
    38   Location – text, optional, max 255 characters
     40'''study_field''' – study_field_enum, required
    3941
    40   Study_field – text, optional, max 255 characters
     42'''university_id''' – integer, required, foreign key to University (on delete cascade)
    4143
    42   University_Id – number, required, foreign key to University
     44=== '''Professor'''
     45This entity stores professor details. Each professor belongs to a specific faculty and can be associated with universities, students (advice sessions), and subject enrollments.
    4346
    44 ----
    45 • Professor: This entity stores professors. Each professor works in a specific faculty, can guide students through advice sessions, and is assigned to teach subjects inside student enrollments.
     47'''Candidate keys:''' id (primary key).
    4648
    47  º Candidate keys: Id (primary key).
     49'''Attributes:'''
    4850
    49  º Attributes:
     51'''id''' – serial (integer), required, unique
    5052
    51   Id – number, required, unique
     53'''name''' – varchar(255), required
    5254
    53   Name – text, required, max 255 characters
     55'''surname''' – varchar(255), optional
    5456
    55   Surname – text, optional, max 255 characters
     57'''age''' – integer, optional, range 18–80 (constrained via CHECK constraint in SQL)
    5658
    57   Age – number, optional, 25–100
     59'''facultyid''' – integer, required, foreign key to Faculty (on delete cascade)
    5860
    59   Faculty_Id – number, required, foreign key to Faculty
     61=== '''Student'''
     62This entity stores student details. Each student is enrolled in a faculty, can take subjects, and can have advice sessions with professors.
    6063
    61 ----
     64'''Candidate keys:''' id (primary key), studentindex (unique key).
    6265
    63 • Student: This entity stores students. Each student belongs to a faculty, can enroll in subjects, and can interact with professors during advice sessions.
     66'''Attributes:'''
    6467
    65  º Candidate keys: Id (primary key).
     68'''id''' – serial (integer), required, unique
    6669
    67  º Attributes:
     70'''name''' – varchar(255), required
    6871
    69   Id – number, required, unique
     72'''surname''' – varchar(255), optional
    7073
    71   Name – text, required, max 255 characters
     74'''location''' – varchar(100), optional
    7275
    73   Surname – text, optional, max 255 characters
     76'''studentindex''' – integer, required, unique (mapped to Index on the diagram)
    7477
    75   Location – text, optional, max 255 characters
     78'''facultyid''' – integer, required, foreign key to Faculty (on delete restrict)
    7679
    77   Index – number, required, unique
     80=== '''Subject'''
     81This entity stores the courses or subjects offered by a faculty. Students can enroll in these subjects.
    7882
    79   Faculty_Id – number, required, foreign key to Faculty
     83'''Candidate keys:''' id (primary key).
    8084
    81 ----
     85'''Attributes:'''
    8286
    83 • Subject: This entity stores courses or subjects offered by a faculty. Students can enroll in these subjects.
     87'''id''' – serial (integer), required, unique
    8488
    85  º Candidate keys: Id (primary key).
     89'''name''' – varchar(255), required
    8690
    87  º Attributes:
     91'''semester''' – integer, optional, range 1–8
    8892
    89   Id – number, required, unique
     93'''credits''' – integer, optional, range 1–10
    9094
    91   Name – text, required, max 255 characters
     95'''facultyid''' – integer, required, foreign key to Faculty (on delete restrict) (implicitly handles the offers relation)
    9296
    93   Semester – text, optional, e.g., "Fall", "Spring"
     97=== '''Student_Subject'''
     98This entity represents a specific student enrollment in a subject (course instance). It tracks grades, attendance, and status. It also acts as the central link resolving which professor teaches this specific instance to the student.
    9499
    95   Credits – number, required, 1–15
     100'''Candidate keys:''' ss_id (primary key).
    96101
    97   Faculty_Id – number, required, foreign key to Faculty
     102'''Attributes:'''
    98103
    99 ----
     104'''ss_id''' – serial (integer), required, unique (mapped to ss_id on the diagram)
    100105
    101 • Student_Subject: This entity represents a specific student enrollment in a subject. It tracks grades, attendance, and status. It also links the specific professor who teaches this instance of the course.
     106'''student_id''' – integer, required, foreign key to Student (on delete cascade)
    102107
    103  º Candidate keys: ssId (primary key).
     108'''subject_id''' – integer, required, foreign key to Subject (on delete cascade)
    104109
    105  º Attributes:
     110'''professor_id''' – integer, required, foreign key to Professor (on delete restrict) (materializes the assignTo relationship from the diagram)
    106111
    107   ssId – number, required, unique
     112'''enrollment_date''' – date, required, defaults to CURRENT_DATE (mapped to Enrollment_Date)
    108113
    109   Final_Grade – number, optional, 6–10
     114'''status''' – varchar(20), optional, defaults to 'Enrolled'
    110115
    111   Status – text, optional, e.g., "Enrolled", "Passed", "Failed"
     116'''final_grade''' – integer, optional (mapped to Final_Grade)
    112117
    113   Enrollment_Date – date, required
     118'''absences_count''' – integer, optional, defaults to 0 (mapped to Abesense_Count on the diagram)
    114119
    115   Absence_Count – number, optional, 0–100
     120=== '''Advice'''
     121This associative table tracks mentorship history and formal guidance sessions between professors and students.
    116122
    117   Student_Id – number, required, foreign key to Student
     123'''Candidate keys:''' Composite primary key (student_id, professor_id, start_date).
    118124
    119   Subject_Id – number, required, foreign key to Subject
     125'''Attributes:'''
    120126
    121   Professor_Id – number, required, foreign key to Professor
     127'''student_id''' – integer, required, foreign key to Student (on delete cascade)
    122128
    123 ----
     129'''professor_id''' – integer, required, foreign key to Professor (on delete cascade)
    124130
    125 • Advice: This ternary associative relationship entity tracks the history of formal advice and mentorship sessions between professors and students.
     131'''start_date''' – date, required (mapped to start date)
    126132
    127  º Candidate keys: Composite key made of (student_id, professor_id, start_date).
     133'''end_date''' – date, optional (mapped to end_date)
    128134
    129  º Attributes:
     135=== '''Affiliated'''
     136This table materializes the relationship between Professors and Universities. While drawn as a 1:N relationship (affiliated_with), it is physically implemented as a Many-to-Many bridge table to allow flexible multi-university affiliations.
    130137
    131   student_id – number, required, foreign key to Student
     138'''Candidate keys:''' Composite primary key (university_id, professor_id).
    132139
    133   professor_id – number, required, foreign key to Professor
     140'''Attributes:'''
    134141
    135   start_date – date, required, unique part of the key
     142'''university_id''' – integer, required, foreign key to University (on delete cascade)
    136143
    137   end_date – date, optional
    138 
    139   status – text, optional, e.g., "Active", "Completed"
    140 
    141 ----
    142 
    143 • Professor_Subject: This bridge entity materializes the Many-to-Many relationship between professors and the subjects they are certified or eligible to teach.
    144 
    145  º Candidate keys: Composite key made of (subject_id, professor_id).
    146 
    147  º Attributes:
    148 
    149   subject_id – number, required, foreign key to Subject
    150 
    151   professor_id – number, required, foreign key to Professor
     144'''professor_id''' – integer, required, foreign key to Professor (on delete cascade)
    152145
    153146== Entity-Relationship Model History