= Entity-Relationship Model v.02 = == Diagram == [[Image(ERModel_v02.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'', ''ERModel_v01.png'', ''ERModel_v02.xml'' and ''ERModel_v02.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. A room may be included in reservations for specific dates and time intervals, but reservations may also exist without a room when they are only equipment requests. '''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'' (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 or in general stock, such as projectors, whiteboards, microphones, computers, cables, or other reservable equipment. This entity is needed so that equipment can be managed independently, assigned to specific rooms, or requested separately as part of reservations. '''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'' (INTEGER) – required, unique identifier, primary key. * ''name'' (VARCHAR(128)) – required, name of the equipment type. * ''stock_quantity'' (INTEGER) – required, number of equipment items available in general stock, must be greater than or equal to 0. ==== !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. This entity is used only for equipment assigned to rooms, while general equipment stock is represented by the stock_quantity attribute in Equipment. '''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 0. ==== Users ==== '''Description:''' Users represents people who have access to the reservation system. Users can create reservation requests, and users with appropriate roles can approve or reject reservations. '''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'' (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 dates and time intervals. A reservation may include a room, requested equipment, or both. This entity is central to the system because it stores when and by whom a resource is reserved or requested. The system must prevent overlapping room 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 for room-based reservations under strict business constraints, but a surrogate primary key is preferred for easier referencing. '''Attributes:''' * ''reservation_id'' (INTEGER) – required, unique identifier, primary key. * ''reservation_date'' (DATE) – required, date of the reservation. * ''start_time'' (TIME) – required, start time of the reservation. * ''end_time'' (TIME) – required, must be greater than start_time. * ''status'' (VARCHAR(128)) – required, suggested domain: {pending, approved, rejected, cancelled}. ==== !ReservationEquipment ==== '''Description:''' !ReservationEquipment is an associative entity that models equipment requested as part of a reservation. It allows the system to support reservations that include only equipment, only a room, or both a room and equipment. '''Candidate keys:''' * (reservation_id, equipment_id) – selected as the primary key because the same equipment type should appear only once in the same reservation request. '''Attributes:''' * ''reservation_id'' (FK reference to Reservations) – required, part of the composite primary key. * ''equipment_id'' (FK reference to Equipment) – required, part of the composite primary key. * ''requested_quantity'' (INTEGER) – required, quantity of equipment requested, must be greater than 0. ==== 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'' (INTEGER) – required, unique identifier, primary key. * ''decision'' (VARCHAR(128)) – required, suggested domain: {approved, rejected}. * ''decision_time'' (TIMESTAMP) – 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. ==== includes_room (Rooms–Reservations) ==== '''Meaning:''' The relationship includes_room connects Rooms and Reservations. It shows that a reservation may include one room. A reservation can also exist without a room when it is only an equipment request. '''Cardinality:''' Rooms (1) — Reservations (N) '''Participation:''' * Room participation is partial because a room may have zero reservations. * Reservation participation is partial because a reservation may be only for equipment and may not include a room. ==== 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. ==== requests_equipment (Reservations–!ReservationEquipment) ==== '''Meaning:''' The relationship requests_equipment connects Reservations and !ReservationEquipment. It shows that a reservation may include one or more requested equipment items. '''Cardinality:''' Reservations (1) — !ReservationEquipment (N) '''Participation:''' * !ReservationEquipment participation is total because each equipment request entry must refer to exactly one reservation. * Reservation participation is partial because a reservation may include only a room and no requested equipment. ==== is_requested_in (Equipment–!ReservationEquipment) ==== '''Meaning:''' The relationship is_requested_in connects Equipment and !ReservationEquipment. It shows that an equipment type may be requested in reservation-equipment entries. '''Cardinality:''' Equipment (1) — !ReservationEquipment (N) '''Participation:''' * !ReservationEquipment participation is total because each equipment request entry must refer to exactly one equipment type. * Equipment participation is partial because an equipment type may exist without being requested in any reservation. ==== 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 may be made for a room, for equipment, or for both. * A reservation must include at least one requested resource: either a room or equipment. * Equipment may be assigned to a specific room, but it may also exist only in general stock. * !RoomEquipment represents equipment available in a specific room. * !ReservationEquipment represents equipment requested as part of a reservation. * A reservation cannot overlap with another room 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 or requested in any reservation. * 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. * '''v02''' – Updated version of the ER model based on supervisor feedback. This version generalizes reservations so that a reservation may include a room, equipment, or both. A new associative entity, !ReservationEquipment, was added to represent equipment requested as part of a reservation. The Equipment entity was extended with stock_quantity to represent equipment available in general stock and not necessarily assigned to a specific room. The Rooms–Reservations relationship was changed into includes_room so that equipment-only reservations are allowed. == AI Usage == The AI usage for this phase is documented on the following page: [[ERModelAIUsage]].