Relational Model

1. Tables

Objects

• PK: object_id
• Attributes: inventory_number (UNIQUE), title, creation_year, acquisition_date, description
• FK: site_id → Sites, current_status_id → Status_Types, found_by_user_id → Users

Sites

• PK: site_id
• Attributes: site_name, latitude, longitude, altitude, discovery_year, is_active
• FK: site_type_id → Site_Types, region_id → Regions, protection_status_id → Protection_Status, municipality_id → Municipalities

Users

• PK: user_id
• Attributes: username (UNIQUE), full_name, email (UNIQUE), password_hash
• FK: role_id → Roles

Institutions

• PK: institution_id
• Attributes: name (UNIQUE), address, city

Publications

• PK: publication_id
• Attributes: title, request_date, publisher_location
• FK: status_id → Status_Types, main_author_id → Authors

Treatments

• PK: treatment_id
• Attributes: treatment_date, description
• FK: object_id → Objects

Fragments

• PK: fragment_id
• Attributes: description, discovery_date
• FK: site_id → Sites, object_id → Objects, found_by_user_id → Users, status_id → Status_Types, parent_fragment_id → Fragments (self-reference)

Conservation_Projects

• PK: project_id
• Attributes: project_name, start_date, end_date, budget
• FK: institution_id → Institutions, object_id → Objects

Exhibitions

• PK: exhibition_id
• Attributes: name, start_date, end_date
• FK: location_institution_id → Institutions

Condition_Assessment

• PK: assessment_id
• Attributes: assessment_date, notes
• FK: object_id → Objects, status_id → Status_Types

2. Junction Tables (M:N Relationships)

• Object_Publication (object_id, publication_id) — Objects ↔ Publications
• Object_Exhibition (object_id, exhibition_id) — Objects ↔ Exhibitions
• Publication_Authors (publication_id, author_id) — Publications ↔ Authors
• Materials_Objects (object_id, material_id) — Objects ↔ Materials
• Treatment_Materials (treatment_id, material_id) — Treatments ↔ Materials

3. Constraints

• UNIQUE: inventory_number, username, email, site_name+region_id
• CHECK: координати во опсег за Македонија
• CHECK: датуми (end_date >= start_date)
• CHECK: creation_year не е во иднина
• CHECK: fragment не може да си биде сам parent

4. Design Decisions

• M:N релации се решени со помошни табели: Object_Publication, Object_Exhibition, Publication_Authors, Materials_Objects, Treatment_Materials
• Self-reference: Fragments (parent_fragment_id → fragment_id)
• Status_Types е генерализирана табела со поле "type" за различни видови статуси
• Одделна табела User_Details за дополнителни информации за корисниците

5. Diagram