= Entity-Relationship Model v.01 = == Diagram == [[Image(ERModel_v01.png, width=100%)]] A full-page width image of the latest version of the entity-relationship diagram is shown above. (Attached files: ''ERModel_v01.xml'' and ''ERModel_v01.png'') == Data requirements == This section lists all entity sets and relationships included in the entity-relationship model. For each entity set, the description, candidate keys, selected primary key, attributes, data types, and restrictions are documented. For each relationship, the meaning, cardinality, and participation constraints are described. === Entities === ==== Buildings ==== '''Description:''' Buildings represents physical buildings that contain multiple reservable rooms. This entity is needed because rooms are located in specific buildings, and the system must be able to group rooms by their physical location. '''Candidate keys:''' * building_id – selected as the primary key because it is stable and guaranteed to be unique. * (name, address) – possible candidate key only if institutional rules guarantee uniqueness, but it is not sufficiently reliable and therefore it is not selected as the primary key. '''Attributes:''' * ''building_id'' (INTEGER) – required, unique identifier, primary key. * ''name'' (VARCHAR(128)) – required, building name. * ''address'' (VARCHAR(128)) – required, physical address or location description of the building. ==== Rooms ==== '''Description:''' Rooms represents reservable rooms such as classrooms, offices, meeting rooms, or laboratories. Each room is located in exactly one building and can be reserved by users for specific dates and time intervals. '''Candidate keys:''' * room_id – selected as the primary key because it is stable and unique. * room_code – candidate key if it is guaranteed to be unique across the organization. '''Attributes:''' * ''room_id'' (VARCHAR(128) or INTEGER) – required, unique identifier, primary key. * ''room_code'' (VARCHAR(128)) – required, recommended to be unique. * ''capacity'' (INTEGER) – required, must be greater than 0. * ''type'' (VARCHAR(128)) – required, suggested domain: {classroom, office, meeting_room, lab, ...}. ==== Equipment ==== '''Description:''' Equipment represents types of equipment that may be available in rooms, such as projectors, whiteboards, microphones, or computers. This entity is needed so that equipment can be managed independently and assigned to different rooms. '''Candidate keys:''' * equipment_id – selected as the primary key because it is stable and unique. * name – possible candidate key only if equipment names are guaranteed to be unique, but this is usually not guaranteed. '''Attributes:''' * ''equipment_id'' (VARCHAR(128) or INTEGER) – required, unique identifier, primary key. * ''name'' (VARCHAR(128)) – required, name of the equipment type. ==== !RoomEquipment ==== '''Description:''' !RoomEquipment is an associative entity that models the many-to-many relationship between Rooms and Equipment. It stores information about which equipment type is available in which room and in what quantity. '''Candidate keys:''' * (room_id, equipment_id) – selected as the primary key because the same equipment type should appear only once for the same room. '''Attributes:''' * ''room_id'' (FK reference to Rooms) – required, part of the composite primary key. * ''equipment_id'' (FK reference to Equipment) – required, part of the composite primary key. * ''quantity'' (INTEGER) – required, must be greater than or equal to 0; normally greater than 0 when an equipment entry exists. ==== Users ==== '''Description:''' Users represents people who have access to the reservation system. Users can create reservations, and users with appropriate roles can approve or reject reservation requests. '''Candidate keys:''' * user_id – selected as the primary key because it is stable and unique. * username – candidate key because usernames should be unique. * email – candidate key because email addresses should be unique. '''Attributes:''' * ''user_id'' (VARCHAR(128) or INTEGER) – required, unique identifier, primary key. * ''username'' (VARCHAR(128)) – required, recommended to be unique. * ''email'' (VARCHAR(128)) – required, recommended to be unique, must follow a valid email format. * ''full_name'' (VARCHAR(128)) – required, full name of the user. * ''role'' (VARCHAR(128)) – required, suggested domain: {regular, admin, approver}. ==== Reservations ==== '''Description:''' Reservations represents reservation requests made by users for specific rooms, dates, and time intervals. This entity is central to the system because it stores when and by whom a room is reserved. The system must prevent overlapping reservations for the same room and time interval. '''Candidate keys:''' * reservation_id – selected as the primary key because it is stable and unique. * (room_id, reservation_date, start_time, end_time) – may form a natural candidate key under strict business constraints, but a surrogate primary key is preferred for easier referencing. '''Attributes:''' * ''reservation_id'' (VARCHAR(128) or INTEGER) – required, unique identifier, primary key. * ''reservation_date'' (DATE) – required, date of the reservation. * ''start_time'' (DATE or TIME representation) – required, start time of the reservation. * ''end_time'' (DATE or TIME representation) – required, must be greater than start_time. * ''status'' (VARCHAR(128)) – required, suggested domain: {pending, approved, rejected, cancelled}. ==== Approvals ==== '''Description:''' Approvals represents approval decisions for reservations. An approval is created by an authorized user, such as an administrator or approver. This entity stores the decision, decision time, and an optional note. '''Candidate keys:''' * approval_id – selected as the primary key because it is stable and unique. * reservation_id – may be unique if the business rule enforces at most one approval per reservation. '''Attributes:''' * ''approval_id'' (VARCHAR(128) or INTEGER) – required, unique identifier, primary key. * ''decision'' (VARCHAR(128)) – required, suggested domain: {approved, rejected}. * ''decision_time'' (DATE) – required, date and time when the decision was made. * ''note'' (VARCHAR(128)) – optional, additional explanation for the decision. === Relationships === ==== has (Buildings–Rooms) ==== '''Meaning:''' The relationship has connects Buildings and Rooms. It shows that a building contains rooms. '''Cardinality:''' Buildings (1) — Rooms (N) '''Participation:''' * Room participation is total because each room must belong to exactly one building. * Building participation is partial because a building may contain zero or more rooms. ==== contains (Rooms–!RoomEquipment) ==== '''Meaning:''' The relationship contains connects Rooms and !RoomEquipment. It shows that a room may contain registered equipment entries with quantities. '''Cardinality:''' Rooms (1) — !RoomEquipment (N) '''Participation:''' * !RoomEquipment participation is total because each equipment entry must refer to exactly one room. * Room participation is partial because a room may have no equipment registered. ==== used_in (Equipment–!RoomEquipment) ==== '''Meaning:''' The relationship used_in connects Equipment and !RoomEquipment. It shows that an equipment type can be used in room-equipment entries. '''Cardinality:''' Equipment (1) — !RoomEquipment (N) '''Participation:''' * !RoomEquipment participation is total because each entry must refer to exactly one equipment type. * Equipment participation is partial because an equipment type may exist without being assigned to any room. ==== is_reserved_for (Rooms–Reservations) ==== '''Meaning:''' The relationship is_reserved_for connects Rooms and Reservations. It shows that each reservation is made for one specific room. '''Cardinality:''' Rooms (1) — Reservations (N) '''Participation:''' * Reservation participation is total because each reservation must specify exactly one room. * Room participation is partial because a room may have zero reservations. ==== makes (Users–Reservations) ==== '''Meaning:''' The relationship makes connects Users and Reservations. It shows that users create reservation requests. '''Cardinality:''' Users (1) — Reservations (N) '''Participation:''' * Reservation participation is total because each reservation must be created by exactly one user. * User participation is partial because a user may create zero reservations. ==== has_approval (Reservations–Approvals) ==== '''Meaning:''' The relationship has_approval connects Reservations and Approvals. It shows that a reservation may have an associated approval decision. '''Cardinality:''' Reservations (1) — Approvals (1), optional on the reservation side. '''Participation:''' * Approval participation is total because an approval cannot exist without a reservation. * Reservation participation is partial because a reservation may exist without an approval record, for example while it is still pending. ==== approves (Users–Approvals) ==== '''Meaning:''' The relationship approves connects Users and Approvals. It shows that authorized users approve or reject reservations. '''Cardinality:''' Users (1) — Approvals (N) '''Participation:''' * Approval participation is total because each approval must be created by exactly one user. * User participation is partial because not every user must approve reservations. == Design Assumptions == * Each room belongs to exactly one building. * A reservation is created by exactly one user. * A reservation is made for exactly one room. * A reservation cannot overlap with another reservation for the same room and time interval. * A reservation may exist without an approval record while it is still pending. * Equipment types may exist without being assigned to any room. * The !RoomEquipment entity is used to model the many-to-many relationship between Rooms and Equipment. * Only users with appropriate roles, such as admin or approver, are allowed to create approval records. == Entity-Relationship Model History == * '''v01''' – Initial version of the ER model. This version includes Buildings, Rooms, Equipment, !RoomEquipment as an associative entity, Users, Reservations, and Approvals. It also defines the required relationships, cardinalities, participation constraints, candidate keys, primary keys, and main entity attributes.