Entity-Relationship Model v.03

Diagram

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, ERModel_v02.png, ERModel_v03.xml and ERModel_v03.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, participation constraints, and possible relationship attributes are described.

Entities

Buildings

Description: Buildings represents physical buildings that contain 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.

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 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.
• A natural uniqueness rule may be based on the selected room, reservation date, start time, and end time for room-based reservations, but this rule depends on the relationship with Rooms and is therefore not selected as the primary key.

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}.

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.
• The relationship with Reservations is constrained so that a reservation may have at most one approval record.

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.

includes_room (Rooms–Reservations)

Meaning: The relationship includes_room connects Rooms and Reservations. It shows that a reservation may include one room. A reservation may 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.

has_equipment (Rooms–Equipment)

Meaning: The relationship has_equipment connects Rooms and Equipment. It shows which equipment types are available in which rooms and in what quantity.

Cardinality: Rooms (M) — Equipment (N)

Participation:

• Room participation is partial because a room may have no equipment assigned.
• Equipment participation is partial because an equipment type may exist only in general stock and may not be assigned to any room.

Relationship attributes:

• quantity (INTEGER) – required for each relationship instance, must be greater than 0.

requests_equipment (Reservations–Equipment)

Meaning: The relationship requests_equipment connects Reservations and Equipment. It shows which equipment types are requested as part of a reservation and in what quantity.

Cardinality: Reservations (M) — Equipment (N)

Participation:

• Reservation participation is partial because a reservation may include only a room and no requested equipment.
• Equipment participation is partial because an equipment type may exist without being requested in any reservation.

Relationship attributes:

• requested_quantity (INTEGER) – required for each relationship instance, must be greater than 0.

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.
• The relationship has_equipment represents equipment available in a specific room and stores the quantity of that equipment in the room.
• The relationship requests_equipment represents equipment requested as part of a reservation and stores the requested quantity.
• A room-based reservation cannot overlap with another active reservation for the same room and time interval.
• A reservation may exist without an approval record while it is still pending.
• An approval record cannot exist without a reservation.
• 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 included Buildings, Rooms, Equipment, RoomEquipment as an associative entity, Users, Reservations, and Approvals. It also defined 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 generalized 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.

• v03 – Corrected version of the ER model based on additional supervisor feedback. The associative entities RoomEquipment and ReservationEquipment were removed from the conceptual ER model. Instead, the many-to-many relationship between Rooms and Equipment is represented as has_equipment with the relationship attribute quantity, and the many-to-many relationship between Reservations and Equipment is represented as requests_equipment with the relationship attribute requested_quantity. This version avoids foreign keys in the ER model and keeps only strong entity sets and conceptual relationships.

AI Usage

The AI usage for this phase is documented on the following page: ERModelAIUsage.