File size: 4,865 Bytes
5dd1bb4 | 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139 140 141 142 143 144 145 146 147 148 149 150 151 152 153 154 | """
SQLAlchemy ORM models for the university course management database.
This module defines all tables using SQLAlchemy declarative syntax with proper
relationships and data types.
"""
from datetime import datetime
from sqlalchemy import Column, Integer, String, DateTime, ForeignKey
from sqlalchemy.orm import declarative_base, relationship
Base = declarative_base()
class Address(Base):
"""Address information for people."""
__tablename__ = "Addresses"
address_id = Column(Integer, primary_key=True, autoincrement=True)
line_1 = Column(String(255), nullable=False)
line_2 = Column(String(255))
city = Column(String(100))
zip_postcode = Column(String(20))
state_province_county = Column(String(100))
country = Column(String(100))
# Relationships
people_addresses = relationship("PersonAddress", back_populates="address")
class Person(Base):
"""Person information."""
__tablename__ = "People"
person_id = Column(Integer, primary_key=True, autoincrement=True)
first_name = Column(String(100), nullable=False)
middle_name = Column(String(100))
last_name = Column(String(100), nullable=False)
cell_mobile_number = Column(String(20))
email_address = Column(String(255))
login_name = Column(String(100), unique=True)
password = Column(String(255))
# Relationships
people_addresses = relationship("PersonAddress", back_populates="person")
class Student(Base):
"""Student information."""
__tablename__ = "Students"
student_id = Column(Integer, primary_key=True, autoincrement=True)
student_details = Column(String(500))
# Relationships
course_registrations = relationship(
"StudentCourseRegistration", back_populates="student"
)
course_attendance = relationship(
"StudentCourseAttendance", back_populates="student"
)
class Course(Base):
"""Course information."""
__tablename__ = "Courses"
course_id = Column(String(50), primary_key=True)
course_name = Column(String(200), nullable=False)
course_description = Column(String(500))
other_details = Column(String(500))
# Relationships
course_registrations = relationship(
"StudentCourseRegistration", back_populates="course"
)
course_attendance = relationship("StudentCourseAttendance", back_populates="course")
class PersonAddress(Base):
"""Link between people and their addresses with date ranges."""
__tablename__ = "People_Addresses"
person_address_id = Column(Integer, primary_key=True, autoincrement=True)
person_id = Column(Integer, ForeignKey("People.person_id"), nullable=False)
address_id = Column(Integer, ForeignKey("Addresses.address_id"), nullable=False)
date_from = Column(DateTime)
date_to = Column(DateTime)
# Relationships
person = relationship("Person", back_populates="people_addresses")
address = relationship("Address", back_populates="people_addresses")
class StudentCourseRegistration(Base):
"""Student registration for courses."""
__tablename__ = "Student_Course_Registrations"
student_id = Column(Integer, ForeignKey("Students.student_id"), primary_key=True)
course_id = Column(String(50), ForeignKey("Courses.course_id"), primary_key=True)
registration_date = Column(DateTime, default=datetime.utcnow)
# Relationships
student = relationship("Student", back_populates="course_registrations")
course = relationship("Course", back_populates="course_registrations")
class StudentCourseAttendance(Base):
"""Student attendance records for courses."""
__tablename__ = "Student_Course_Attendance"
student_id = Column(Integer, ForeignKey("Students.student_id"), primary_key=True)
course_id = Column(String(50), ForeignKey("Courses.course_id"), primary_key=True)
date_of_attendance = Column(DateTime, primary_key=True)
# Relationships
student = relationship("Student", back_populates="course_attendance")
course = relationship("Course", back_populates="course_attendance")
class Candidate(Base):
"""Candidate information."""
__tablename__ = "Candidates"
candidate_id = Column(Integer, primary_key=True, autoincrement=True)
candidate_details = Column(String(500))
# Relationships
assessments = relationship("CandidateAssessment", back_populates="candidate")
class CandidateAssessment(Base):
"""Assessment records for candidates."""
__tablename__ = "Candidate_Assessments"
candidate_id = Column(
Integer, ForeignKey("Candidates.candidate_id"), primary_key=True
)
qualification = Column(String(200), primary_key=True)
assessment_date = Column(DateTime, primary_key=True)
asessment_outcome_code = Column(String(50))
# Relationships
candidate = relationship("Candidate", back_populates="assessments")
|