= 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 ==

[[Image(diagram.png)]]