Релациска шема

Primary - oznacuva primaren kluc
atribute – oznacuva zadolzitelen atribut

Prisoner(prisoner_id,date_of_birth,sentence_start_date, first_name,last_name,gender,crime_details,age, cell_id*, activity_name#)

*cell_id referencira od cells(cell_id)
#activity_name referencira od Activities(activity_name)

Cells(cell_id, cell_type, security_level, capacity, cell_number)

Prison_staff(staff_id*)

*staff_id referencira od staff(staff_id)

Prison_incidents(incident_id, incident_date, incident_details, prisoner_id#)

#prisoner_id referencira od Prisoners(prisoner_id)

Medical_record(prisoner_id*, medications, health_condition, alergies)

*prisoner_id referencira od Prisoners(prisoner_id)

Visits(visit_id, visit_time, visit_date, check_in_time, check_out_time, prisoner_id*, visitor_id#)

*prisoner_id referencira od Prisoners(prisoner_id)

#visitor_id referencira od Approved_visitor(visitor_id)

Approved_visitor(visitor_id, contact_number, relationship_to_prisoner, staff_id*,first_name, last_name)

*staff_id referencira od Staff(staff_id)

Prison_director(staff_id*)

*staff_id referencira od Staff(staff_id)

Kitchen_staff(staff_id*)

*staff_id referencira od Staff(staff_id)

Staff(staff_id, first_name, last_name, position_staff)

Activities(activities_name, activity_type, activity_location, working_hours)

Inmate_activities(activity_name*, start_time, end_time, working_days)

Punishment(punishment_id, start_date, end_date, punishment_type, prisoner_id*, incident_id#)

*prisoner_id referencira od Prisoner(prisoner_id)

#incident_id referencira od Prison_incidents(incident_id)

Involved_in(prisoner_id*, incident_id#)

*prisoner_id referencira od Prisoner(prisoner_id)

#incident_id referencira od Prison_incidents(incident_id)

Witnessed(incident_id*,staff_id#)

*incident_id referencira od Prison_incidents(incident_id)

#staff_id referencira od Staff(staff_id)

Is_given(prisoner_id*,punishment_id#)

*prisoner_id referencira od Prisoner(prisoner_id)

#punishment_id referencira od Punishment(punishment_id)

Has_taken_part(visit_id*,visitor_id#)

*visit_id referencira od Visits(visit_id)

#visitor_id referencira od Approved_visitor(visitor_id)

Guarded_by(cell_id*,staff_id#, shift)

*cell_id referencira od Cells(cell_id)

#staff_id referencira od Staff(staff_id)

DDL скрипта за бришење на табелите и креирање на табелите

kreiranje_updated.sql​

DML скрипта за полнење на табелите со податоци

polnenje_updated.sql​

Релациски дијаграм изваден од DBeaver