Changes between Version 6 and Version 7 of ERModel

Timestamp:

06/09/26 14:13:53 (12 days ago)

Author:

223091

Comment:

--

ERModel

@@ -1 @@
-= Entity-Relationship Model v01 =
+= Entity-Relationship Model v.01 =
 
 == Diagram ==
@@ -5 +5 @@
 [[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:''' Represents a physical building that contains multiple reservable rooms.
-*'''Candidate keys:'''
- * building_id (selected as primary key because it is stable and guaranteed to be unique)
- * (optional candidate) (name, address) if institutional rules guarantee uniqueness; however, this is not considered sufficiently reliable and therefore not selected
-*'''Attributes:'''
- * ''building_id'' (NUMBER or NUMBER(9,2)) – required, unique identifier (PK)
- * ''name'' (VARCHAR2(128)) – required
- * ''address'' (VARCHAR2(128)) – required
+
+'''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'' (NUMBER or NUMBER(9,2)) – required, unique identifier, primary key.
+* ''name'' (VARCHAR2(128)) – required, building name.
+* ''address'' (VARCHAR2(128)) – required, physical address or location description of the building.
 
 ==== Rooms ====
-*'''Description:''' Represents a reservable room (e.g., classroom, office, meeting room, lab) located in exactly one building.
-*'''Candidate keys:'''
- * room_id (selected as primary key because it is stable and unique)
- * room_code (candidate key if guaranteed to be unique across the organization)
-*'''Attributes:'''
- * ''room_id'' (VARCHAR2(128) or NUMBER) – required, unique identifier (PK)
- * ''room_code'' (VARCHAR2(128)) – required; recommended UNIQUE constraint
- * ''capacity'' (NUMBER) – required; constraint: capacity > 0
- * ''type'' (VARCHAR2(128)) – required; suggested domain: {classroom, office, meeting_room, lab, ...}
+
+'''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'' (VARCHAR2(128) or NUMBER) – required, unique identifier, primary key.
+* ''room_code'' (VARCHAR2(128)) – required, recommended to be unique.
+* ''capacity'' (NUMBER) – required, must be greater than 0.
+* ''type'' (VARCHAR2(128)) – required, suggested domain: {classroom, office, meeting_room, lab, ...}.
 
 ==== Equipment ====
-*'''Description:''' Represents a type of equipment that may exist in rooms (e.g., projector, whiteboard, microphone).
-*'''Candidate keys:'''
- * equipment_id (selected as primary key)
- * name (candidate only if guaranteed unique; typically not guaranteed)
-*'''Attributes:'''
- * ''equipment_id'' (VARCHAR2(128) or NUMBER) – required, unique identifier (PK)
- * ''name'' (VARCHAR2(128)) – required
+
+'''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'' (VARCHAR2(128) or NUMBER) – required, unique identifier, primary key.
+* ''name'' (VARCHAR2(128)) – required, name of the equipment type.
 
 ==== !RoomEquipment ====
-*'''Description:''' Associative entity that models the many-to-many relationship between Rooms and Equipment. It stores the quantity of a specific equipment type available in a room.
-*'''Candidate keys:'''
- * (room_id, equipment_id) – selected as primary key (natural composite key)
-*'''Attributes:'''
- * ''room_id'' (FK reference to Rooms) – required
- * ''equipment_id'' (FK reference to Equipment) – required
- * ''quantity'' (NUMBER) – required; constraint: quantity ≥ 0 (typically > 0 when present)
+
+'''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'' (NUMBER) – required, must be greater than or equal to 0; normally greater than 0 when an equipment entry exists.
 
 ==== Users ====
-*'''Description:''' Represents system users who can create reservations and, if authorized, approve or reject them.
-*'''Candidate keys:'''
- * user_id (selected as primary key)
- * username (candidate key; must be unique)
- * email (candidate key; must be unique)
-*'''Attributes:'''
- * ''user_id'' (VARCHAR2(128) or NUMBER) – required, unique identifier (PK)
- * ''username'' (VARCHAR2(128)) – required; UNIQUE constraint recommended
- * ''email'' (VARCHAR2(128)) – required; UNIQUE constraint recommended; must follow standard email format
- * ''full_name'' (VARCHAR2(128)) – required
- * ''role'' (VARCHAR2(128)) – required; suggested domain: {regular, admin, approver}
+
+'''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'' (VARCHAR2(128) or NUMBER) – required, unique identifier, primary key.
+* ''username'' (VARCHAR2(128)) – required, recommended to be unique.
+* ''email'' (VARCHAR2(128)) – required, recommended to be unique, must follow a valid email format.
+* ''full_name'' (VARCHAR2(128)) – required, full name of the user.
+* ''role'' (VARCHAR2(128)) – required, suggested domain: {regular, admin, approver}.
 
 ==== Reservations ====
-*'''Description:''' Represents a reservation request made by a user for a specific room, date, and time interval. The system must prevent overlapping reservations for the same room and time interval.
-*'''Candidate keys:'''
- * reservation_id (selected as primary key)
- * (room_id, reservation_date, start_time, end_time) may form a natural candidate key under strict business constraints; however, a surrogate primary key is preferred
-*'''Attributes:'''
- * ''reservation_id'' (VARCHAR2(128) or NUMBER) – required, unique identifier (PK)
- * ''reservation_date'' (DATE) – required
- * ''start_time'' (DATE or TIME representation) – required
- * ''end_time'' (DATE or TIME representation) – required; constraint: end_time > start_time
- * ''status'' (VARCHAR2(128)) – required; suggested domain: {pending, approved, rejected, cancelled}
+
+'''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'' (VARCHAR2(128) or NUMBER) – 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'' (VARCHAR2(128)) – required, suggested domain: {pending, approved, rejected, cancelled}.
 
 ==== Approvals ====
-*'''Description:''' Represents an approval decision for a reservation, created by an authorized user (admin/approver). Each approval records the decision, decision time, and an optional note.
-*'''Candidate keys:'''
- * approval_id (selected as primary key)
- * reservation_id may be unique if the business rule enforces at most one approval per reservation
-*'''Attributes:'''
- * ''approval_id'' (VARCHAR2(128) or NUMBER) – required, unique identifier (PK)
- * ''decision'' (VARCHAR2(128)) – required; suggested domain: {approved, rejected}
- * ''decision_time'' (DATE) – required
- * ''note'' (VARCHAR2(128)) – optional
+
+'''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'' (VARCHAR2(128) or NUMBER) – required, unique identifier, primary key.
+* ''decision'' (VARCHAR2(128)) – required, suggested domain: {approved, rejected}.
+* ''decision_time'' (DATE) – required, date and time when the decision was made.
+* ''note'' (VARCHAR2(128)) – optional, additional explanation for the decision.
 
 === Relationships ===
 
 ==== has (Buildings–Rooms) ====
-*'''Meaning:''' A building contains rooms.
-*'''Cardinality:''' Buildings (1) — Rooms (N)
-*'''Participation:'''
- * Room participation is total (each room must belong to exactly one building).
- * Building participation is partial (a building may contain zero or more 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:''' A room contains equipment entries with associated quantities.
-*'''Cardinality:''' Rooms (1) — !RoomEquipment (N)
-*'''Participation:'''
- * !RoomEquipment participation is total (each entry must refer to exactly one room).
- * Room participation is partial (a room may have no equipment registered).
+
+'''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:''' Equipment types appear in room-equipment entries.
-*'''Cardinality:''' Equipment (1) — !RoomEquipment (N)
-*'''Participation:'''
- * !RoomEquipment participation is total (each entry must refer to exactly one equipment type).
- * Equipment participation is partial (an equipment type may exist without being assigned to any room).
+
+'''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:''' Each reservation is made for exactly one room.
-*'''Cardinality:''' Rooms (1) — Reservations (N)
-*'''Participation:'''
- * Reservation participation is total (each reservation must specify a room).
- * Room participation is partial (a room may have zero 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:''' Users create reservations.
-*'''Cardinality:''' Users (1) — Reservations (N)
-*'''Participation:'''
- * Reservation participation is total (each reservation must be created by exactly one user).
- * User participation is partial (a user may create zero 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:''' A reservation may have an associated approval decision.
-*'''Cardinality:''' Reservations (1) — Approvals (1), optional on the reservation side
-*'''Participation:'''
- * Approval participation is total (an approval cannot exist without a reservation).
- * Reservation participation is partial (a reservation may exist without an approval record, e.g., pending state).
+
+'''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:''' Authorized users approve or reject reservations.
-*'''Cardinality:''' Users (1) — Approvals (N)
-*'''Participation:'''
- * Approval participation is total (each approval must be created by exactly one user).
- * User participation is partial (not every user must approve reservations).
+
+'''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 (status = pending).
+* A reservation may exist without an approval record while it is still pending.
 * Equipment types may exist without being assigned to any room.
-* Only users with appropriate roles (admin/approver) are allowed to create approval records.
+* 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. Includes: Buildings, Rooms, Equipment, !RoomEquipment (associative entity), Users, Reservations, and Approvals, together with all required relationships, cardinalities, and participation constraints.
+* '''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.