# BV-SIS Entity Relationships
## Entity Descriptions & ERD Recommendations

**Document Version:** 1.0

---

## 1. Entity Relationship Overview

The BV-SIS data model follows a hierarchical academic structure flowing from institution configuration through student lifecycle to finance and examination outcomes.

---

## 2. Core Entity Hierarchy

```
Institution
  └── Campus
  └── Faculty
        └── Program (Area of Study)
              └── Programme (Programme of Study)
                    └── Programme Track
                          └── Programme Structure
                                └── Programme Structure Items → Course
```

```
Academic Path
  └── Academic Session
        └── Academic Intake
```

```
Student
  └── Student Program (enrollment in programme)
        └── Student Enrollment (session/period)
              └── Student Course (course registration)
  └── Student Documents
  └── Student Offences
  └── Invoices → Receipts
  └── Student ID Card
```

---

## 3. Entity Descriptions

### 3.1 Institution
**Description:** Root entity representing the educational institution. Singleton in current deployment.  
**Relationships:** One-to-many → Campuses, Faculties  
**Cardinality:** 1 Institution : N Campuses

### 3.2 Campus
**Description:** Physical location of the institution.  
**Relationships:** Many-to-one → Institution; One-to-many → Students (school assignment)  
**Cardinality:** N Campuses : 1 Institution

### 3.3 Faculty
**Description:** Academic division (e.g., Faculty of Education).  
**Relationships:** Many-to-one → Institution; One-to-many → Programs, Courses  
**Cardinality:** 1 Faculty : N Programs, N Courses

### 3.4 Program (Area of Study)
**Description:** Broad academic area within a faculty (BRD: "Create Program Under This Faculty").  
**Relationships:** Many-to-one → Faculty; One-to-many → Programmes  
**Note:** BRD uses "Program" here; "Programme of Study" is the formal qualification.

### 3.5 Programme (Programme of Study)
**Description:** Formal qualification (Degree, Diploma, Certificate) with code, name, abbrev, study period.  
**Relationships:** Many-to-one → Program; Many-to-one → Study Period Structure; One-to-many → Programme Tracks, Student Programs  
**Cardinality:** 1 Programme : N Tracks

### 3.6 Programme Track
**Description:** Variant pathway within a programme (e.g., C-ICT with MONTH 1 scheme).  
**Relationships:** Many-to-one → Programme; Many-to-one → Period Scheme; One-to-many → Programme Structures

### 3.7 Programme Structure
**Description:** Versioned curriculum definition for a programme track.  
**Relationships:** Many-to-one → Programme, Track; One-to-many → Programme Structure Items  
**Cardinality:** 1 Structure : N Structure Items

### 3.8 Programme Structure Item
**Description:** Individual course placement within a period of the structure.  
**Relationships:** Many-to-one → Structure, Period Node, Course  
**Attributes:** is_core, credit_hours, sort_order

### 3.9 Study Period Structure / Nodes
**Description:** Hierarchical time periods (One Year → First Month → Week 1).  
**Relationships:** Self-referencing tree (parent_id); One-to-many → Structure Items, Enrollments

### 3.10 Academic Path
**Description:** Intake pathway (January, September, July, short courses).  
**Relationships:** One-to-many → Academic Sessions

### 3.11 Academic Session
**Description:** Time-bounded academic period (Jan2010/I, Jul2022 I).  
**Relationships:** Many-to-one → Academic Path; One-to-many → Intakes, Enrollments, Invoices, Marks

### 3.12 Academic Intake
**Description:** Specific intake period linking path, session, and period scheme.  
**Relationships:** Many-to-one → Path, Session, Period Scheme

### 3.13 Course
**Description:** Individual academic course/module in the catalogue.  
**Relationships:** Many-to-one → Faculty; Many-to-many → Programmes; One-to-many → Prerequisites, Managers, Structure Items, Student Courses, Marks  
**Cardinality:** N Courses : M Programmes (via course_programme pivot)

### 3.14 Course Prerequisite
**Description:** Directed dependency between courses.  
**Relationships:** Many-to-one → Course (dependent), Course (prerequisite)  
**Constraint:** Prevent circular dependencies at application level

### 3.15 Course Manager
**Description:** Assignment of staff user to manage a course.  
**Relationships:** Many-to-one → Course, User, Session (optional)

### 3.16 Student
**Description:** Core student record with full biodata.  
**Relationships:** One-to-many → Student Programs, Documents, Offences, Invoices, ID Cards; Many-to-one → Campus, lookup tables  
**Unique:** student_number, optionally application_id

### 3.17 Student Program
**Description:** Student's enrollment in a specific programme (supports Switch Program).  
**Relationships:** Many-to-one → Student, Programme, Track, Session; One-to-many → Enrollments  
**Attributes:** is_default, status (active/completed/switched)

### 3.18 Student Enrollment
**Description:** Registration status for a session/period.  
**Relationships:** Many-to-one → Student, Student Program, Session, Period Node  
**Attributes:** status (registered/not_registered/finished), batch

### 3.19 Student Course
**Description:** Individual course registration with status.  
**Relationships:** Many-to-one → Student, Course, Enrollment, Session  
**Status Values:** normal, exempted, repeat, deferred, overload, withdraw

### 3.20 Application
**Description:** Admission application (may convert to Student upon admission).  
**Relationships:** One-to-many → References, Documents; Many-to-one → Form Template, lookup tables  
**Lifecycle:** draft → submitted → approved → admitted (creates Student)

### 3.21 Invoice / Receipt
**Description:** Financial billing and payment records.  
**Relationships:** Invoice many-to-one → Student; One-to-many → Invoice Items; Receipt many-to-one → Invoice, Student

### 3.22 Student Clearance
**Description:** Multi-type clearance record gating results release.  
**Relationships:** Many-to-one → Student, Session

### 3.23 Result Release
**Description:** Audit record of result publication with finance gate.  
**Relationships:** Many-to-one → Student (optional for group), Session

---

## 4. ERD Diagram (Mermaid)

```mermaid
erDiagram
    INSTITUTION ||--o{ CAMPUS : has
    INSTITUTION ||--o{ FACULTY : has
    FACULTY ||--o{ PROGRAM : contains
    PROGRAM ||--o{ PROGRAMME : offers
    PROGRAMME ||--o{ PROGRAMME_TRACK : has
    PROGRAMME_TRACK ||--o{ PROGRAMME_STRUCTURE : defines
    PROGRAMME_STRUCTURE ||--o{ PROGRAMME_STRUCTURE_ITEM : contains
    PROGRAMME_STRUCTURE_ITEM }o--|| COURSE : references
    STUDY_PERIOD_STRUCTURE ||--o{ STUDY_PERIOD_NODE : contains
    STUDY_PERIOD_NODE ||--o{ STUDY_PERIOD_NODE : parent
    PROGRAMME_STRUCTURE_ITEM }o--|| STUDY_PERIOD_NODE : in_period

    ACADEMIC_PATH ||--o{ ACADEMIC_SESSION : has
    ACADEMIC_SESSION ||--o{ ACADEMIC_INTAKE : has

    FACULTY ||--o{ COURSE : owns
    COURSE ||--o{ COURSE_PREREQUISITE : requires
    COURSE ||--o{ COURSE_MANAGER : managed_by
    COURSE }o--o{ PROGRAMME : assigned_via_pivot

    STUDENT ||--o{ STUDENT_PROGRAM : enrolled_in
    STUDENT_PROGRAM }o--|| PROGRAMME : for
    STUDENT_PROGRAM }o--|| PROGRAMME_TRACK : track
    STUDENT_PROGRAM }o--|| ACADEMIC_SESSION : session
    STUDENT ||--o{ STUDENT_ENROLLMENT : registers
    STUDENT ||--o{ STUDENT_COURSE : takes
    STUDENT_COURSE }o--|| COURSE : course
    STUDENT ||--o{ INVOICE : billed
    INVOICE ||--o{ RECEIPT : paid_by
    STUDENT ||--o{ STUDENT_ID_CARD : issued

    APPLICATION ||--o| STUDENT : converts_to
    APPLICATION ||--o{ APPLICATION_REFERENCE : has

    STUDENT ||--o{ STUDENT_MARK : receives
    STUDENT_MARK }o--|| COURSE : for
    STUDENT_MARK }o--|| ACADEMIC_SESSION : in
```

---

## 5. Key Relationship Rules

| Rule | Description |
|------|-------------|
| R-01 | A Student may have multiple Student Programs (history) but only one is_default=true |
| R-02 | Student Courses must reference a valid Course in the student's Programme Structure (with override capability) |
| R-03 | Course Prerequisites validated at Student Course registration time |
| R-04 | Invoice generation triggered by Fee Assignments matching student's programme/session |
| R-05 | Result Release blocked if Student Clearance status != cleared for finance type |
| R-06 | Application converts to Student upon admission; application_number preserved |
| R-07 | Programme Structure is versioned; students locked to structure version at enrollment time |
| R-08 | Computer Number Scheme generates unique student_numbers via atomic increment |
| R-09 | Soft delete on all master data; hard reference integrity on financial/audit records |
| R-10 | All FK relationships use RESTRICT on delete for active records; CASCADE only on pivot/junction cleanup |

---

## 6. Cardinality Summary Table

| Entity A | Relationship | Entity B | Cardinality |
|----------|-------------|----------|-------------|
| Institution | has | Campus | 1:N |
| Faculty | contains | Program | 1:N |
| Program | offers | Programme | 1:N |
| Programme | has | Track | 1:N |
| Track | defines | Structure | 1:N |
| Structure | contains | Structure Item | 1:N |
| Course | prerequisite of | Course | N:M (via course_prerequisites) |
| Course | assigned to | Programme | N:M (via course_programme) |
| Student | enrolled in | Programme | N:M (via student_programs) |
| Student | takes | Course | N:M (via student_courses) |
| Student | billed via | Invoice | 1:N |
| Invoice | paid by | Receipt | 1:N |
| Academic Path | has | Session | 1:N |
| Session | has | Intake | 1:N |

---

## 7. Index Strategy Recommendations

| Table | Index | Reason |
|-------|-------|--------|
| students | student_number (UNIQUE) | Primary lookup |
| students | nrc_passport | Duplicate detection |
| students | full_name | Search |
| student_courses | (student_id, session_id) | Course tab query |
| invoices | (student_id, status) | Clearance check |
| academic_sessions | (academic_path_id, start_date) | Session filter |
| courses | (faculty_id, code) | Course catalogue |
| activity_log | (subject_type, subject_id) | Audit queries |

---

## 8. Data Integrity Constraints

1. **Unique student_number** — enforced at DB and application level
2. **Unique session_code** — per institution
3. **Date validation** — session end_date > start_date; intake within session bounds
4. **Credit hour totals** — programme structure credits should match programme.total_credits (warning, not block)
5. **Prerequisite acyclic** — graph traversal on insert
6. **Finance balance** — invoice.balance = total_amount - paid_amount (computed)
7. **Status transitions** — enforced via state machine in application layer

---

## 9. Future Expansion Points

| Area | Expansion |
|------|-----------|
| Multi-institution | institution_id on all entities |
| Multi-campus enrollment | campus_id on student_programs |
| Alumni module | alumni_status on students, graduation records |
| HR/Staff | staff table linked to users, course_managers |
| Library | library_clearances in student_clearances |
| Hostel | accommodation assignments |
| Payroll | staff payment (separate system integration) |

---

*End of Entity Relationships*