Changes between Version 3 and Version 4 of RelationalDesign


Ignore:
Timestamp:
06/18/26 20:02:44 (2 days ago)
Author:
223091
Comment:

--

Legend:

Unmodified
Added
Removed
Modified

  • RelationalDesign

    v3 v4  
    11= Relational Design =
    22
    3 This page documents Phase P2: Logical and Physical Design and Database Creation. The entity-relationship model from Phase P1 is transformed into a relational database schema and implemented in PostgreSQL in the assigned project database. The official schema used for the project is named ''project''.
    4 
    53== Relational schema ==
    64
    7 The following relational schema is derived from the ER model in Phase P1. Primary keys are marked with '''PK''', foreign keys are marked with '''FK''', and unique keys are explicitly noted.
    8 
    9 '''BUILDINGS'''(
    10  building_id '''PK''',
    11  name,
    12  address,
    13  '''UNIQUE'''(name, address)
    14 )
    15 
    16 '''ROOMS'''(
    17  room_id '''PK''',
    18  building_id '''FK''' → BUILDINGS(building_id),
    19  room_code '''UNIQUE''',
    20  capacity,
    21  type
    22 )
    23 
    24 '''EQUIPMENT'''(
    25  equipment_id '''PK''',
    26  name '''UNIQUE'''
    27 )
    28 
    29 '''ROOM_EQUIPMENT'''(
    30  room_id '''PK, FK''' → ROOMS(room_id),
    31  equipment_id '''PK, FK''' → EQUIPMENT(equipment_id),
    32  quantity
    33 )
    34 
    35 '''USERS'''(
    36  user_id '''PK''',
    37  username '''UNIQUE''',
    38  email '''UNIQUE''',
    39  full_name,
    40  role
    41 )
    42 
    43 '''RESERVATIONS'''(
    44  reservation_id '''PK''',
    45  room_id '''FK''' → ROOMS(room_id),
    46  user_id '''FK''' → USERS(user_id),
    47  reservation_date,
    48  start_time,
    49  end_time,
    50  status,
    51  '''UNIQUE'''(room_id, reservation_date, start_time, end_time)
    52 )
    53 
    54 '''APPROVALS'''(
    55  approval_id '''PK''',
    56  reservation_id '''FK, UNIQUE''' → RESERVATIONS(reservation_id),
    57  approver_id '''FK''' → USERS(user_id),
    58  decision,
    59  decision_time,
    60  note
    61 )
    62 
    63 == Transformation notes ==
    64 
    65 The ER model was transformed into a relational model using partial transformation. Each strong entity set from the ER diagram is transformed into a separate relation. The entities Buildings, Rooms, Equipment, Users, Reservations, and Approvals are represented as separate tables.
    66 
    67 The many-to-many relationship between Rooms and Equipment is represented through the associative relation ROOM_EQUIPMENT. This relation has a composite primary key consisting of room_id and equipment_id. It also contains the additional attribute quantity, which stores how many pieces of a specific equipment type are available in a specific room.
    68 
    69 The one-to-many relationships are implemented using foreign keys:
    70  * each room references exactly one building;
    71  * each reservation references exactly one room;
    72  * each reservation references exactly one user;
    73  * each approval references exactly one reservation;
    74  * each approval references exactly one authorized user.
    75 
    76 The optional one-to-one relationship between Reservations and Approvals is implemented by allowing reservations to exist without an approval record, while each approval must reference exactly one reservation. The UNIQUE constraint on approvals.reservation_id ensures that one reservation can have at most one approval record.
    77 
    78 == Constraints ==
    79 
    80 The database schema includes the following constraints:
    81 
    82  * Primary keys for all tables.
    83  * Foreign keys for all relationships derived from the ER model.
    84  * Composite primary key on ROOM_EQUIPMENT(room_id, equipment_id).
    85  * UNIQUE constraint on BUILDINGS(name, address).
    86  * UNIQUE constraint on ROOMS(room_code).
    87  * UNIQUE constraint on EQUIPMENT(name).
    88  * UNIQUE constraints on USERS(username) and USERS(email).
    89  * UNIQUE constraint on RESERVATIONS(room_id, reservation_date, start_time, end_time).
    90  * UNIQUE constraint on APPROVALS(reservation_id).
    91  * CHECK constraint that room capacity must be greater than 0.
    92  * CHECK constraint that room equipment quantity must be greater than 0.
    93  * CHECK constraint that reservation end_time must be greater than start_time.
    94  * CHECK constraints for controlled values of room types, user roles, reservation statuses, and approval decisions.
     5This phase transforms the entity-relationship model from Phase P1 into a relational database schema implemented in PostgreSQL. The database objects are created in the official project schema named '''project'''.
     6
     7The relational model was created using partial transformation. Strong entity sets are transformed into separate relations, while many-to-many relationships and associative entity sets are transformed into separate relations with composite primary keys. Optional participation is represented using nullable foreign keys where appropriate.
     8
     9=== Relations ===
     10
     11'''buildings'''(
     12'''building_id''' PK,
     13name,
     14address
     15)
     16
     17Candidate keys:
     18
     19* building_id
     20* (name, address)
     21
     22Constraints:
     23
     24* building_id is the primary key.
     25* name is required.
     26* address is required.
     27* (name, address) is unique.
     28
     29'''rooms'''(
     30'''room_id''' PK,
     31building_id FK,
     32room_code AK,
     33capacity,
     34type
     35)
     36
     37Candidate keys:
     38
     39* room_id
     40* room_code
     41
     42Foreign keys:
     43
     44* building_id references buildings(building_id)
     45
     46Constraints:
     47
     48* room_id is the primary key.
     49* building_id is required.
     50* room_code is required and unique.
     51* capacity must be greater than 0.
     52* type is restricted to the values: classroom, office, meeting_room, lab.
     53
     54'''equipment'''(
     55'''equipment_id''' PK,
     56name AK,
     57stock_quantity
     58)
     59
     60Candidate keys:
     61
     62* equipment_id
     63* name
     64
     65Constraints:
     66
     67* equipment_id is the primary key.
     68* name is required and unique.
     69* stock_quantity is required and must be greater than or equal to 0.
     70
     71'''room_equipment'''(
     72'''room_id''' PK, FK,
     73'''equipment_id''' PK, FK,
     74quantity
     75)
     76
     77Candidate keys:
     78
     79* (room_id, equipment_id)
     80
     81Foreign keys:
     82
     83* room_id references rooms(room_id)
     84* equipment_id references equipment(equipment_id)
     85
     86Constraints:
     87
     88* (room_id, equipment_id) is the composite primary key.
     89* quantity is required and must be greater than 0.
     90
     91'''users'''(
     92'''user_id''' PK,
     93username AK,
     94email AK,
     95full_name,
     96role
     97)
     98
     99Candidate keys:
     100
     101* user_id
     102* username
     103* email
     104
     105Constraints:
     106
     107* user_id is the primary key.
     108* username is required and unique.
     109* email is required and unique.
     110* full_name is required.
     111* role is restricted to the values: regular, admin, approver.
     112* email must contain the character @.
     113
     114'''reservations'''(
     115'''reservation_id''' PK,
     116room_id FK,
     117user_id FK,
     118reservation_date,
     119start_time,
     120end_time,
     121status
     122)
     123
     124Candidate keys:
     125
     126* reservation_id
     127* (room_id, reservation_date, start_time, end_time) for room-based reservations
     128
     129Foreign keys:
     130
     131* room_id references rooms(room_id)
     132* user_id references users(user_id)
     133
     134Constraints:
     135
     136* reservation_id is the primary key.
     137* room_id is optional because equipment-only reservations are allowed.
     138* user_id is required.
     139* reservation_date is required.
     140* start_time is required.
     141* end_time is required and must be greater than start_time.
     142* status is restricted to the values: pending, approved, rejected, cancelled.
     143* (room_id, reservation_date, start_time, end_time) is unique for exact room reservation intervals.
     144
     145'''reservation_equipment'''(
     146'''reservation_id''' PK, FK,
     147'''equipment_id''' PK, FK,
     148requested_quantity
     149)
     150
     151Candidate keys:
     152
     153* (reservation_id, equipment_id)
     154
     155Foreign keys:
     156
     157* reservation_id references reservations(reservation_id)
     158* equipment_id references equipment(equipment_id)
     159
     160Constraints:
     161
     162* (reservation_id, equipment_id) is the composite primary key.
     163* requested_quantity is required and must be greater than 0.
     164
     165'''approvals'''(
     166'''approval_id''' PK,
     167reservation_id FK, AK,
     168approver_id FK,
     169decision,
     170decision_time,
     171note
     172)
     173
     174Candidate keys:
     175
     176* approval_id
     177* reservation_id
     178
     179Foreign keys:
     180
     181* reservation_id references reservations(reservation_id)
     182* approver_id references users(user_id)
     183
     184Constraints:
     185
     186* approval_id is the primary key.
     187* reservation_id is required and unique, because one reservation can have at most one approval record.
     188* approver_id is required.
     189* decision is restricted to the values: approved, rejected.
     190* decision_time is required.
     191* note is optional.
    95192
    96193== DDL script for creating the database schema and objects ==
    97194
    98 The DDL script recreates the project schema and all database objects. It first drops the existing schema if it already exists, then creates the schema named ''project''. After that, it creates all tables and defines all primary keys, foreign keys, unique constraints, and check constraints.
    99 
    100 The script is designed to work both in an empty database and in a database where the project schema and tables already exist, because it drops the existing project schema and recreates it.
    101 
    102 Attached file: ''schema_creation.sql''
     195The DDL script recreates the '''project''' schema and creates all required tables, primary keys, foreign keys, unique constraints, and check constraints.
     196
     197The script is attached to this page:
     198
     199[attachment:schema_creation.sql schema_creation.sql]
    103200
    104201== DML script for filling tables with data ==
    105202
    106 The DML script fills all created tables with realistic sample data. The sample data includes buildings, rooms, equipment, equipment quantities per room, users, reservations, and approval decisions. The data is logically related and simulates realistic room reservation situations.
    107 
    108 The script can be executed multiple times because it first truncates the tables, restarts identity values, and then reloads the sample data.
    109 
    110 Attached file: ''data_load.sql''
     203The DML script clears the existing data from the created tables and inserts realistic sample data for the Room Reservation System. The inserted data includes buildings, rooms, equipment, equipment assigned to rooms, users, reservations, requested equipment, and approval decisions.
     204
     205The sample data includes examples of:
     206
     207* room-only reservations,
     208* equipment-only reservations,
     209* reservations that include both a room and equipment,
     210* approved, rejected, cancelled, and pending reservation statuses.
     211
     212The script is attached to this page:
     213
     214[attachment:data_load.sql data_load.sql]
    111215
    112216== Relational diagram ==
    113217
    114 The relational schema diagram is generated in DBeaver from the PostgreSQL schema named ''project''. The diagram uses crow-foot notation and visually represents the tables, primary keys, foreign keys, and relationships created by the DDL script.
     218The relational diagram was generated in DBeaver from the created PostgreSQL tables in the '''project''' schema. The diagram shows the implemented relations and the foreign key references between them.
    115219
    116220[[Image(relational_schema.jpg, width=100%)]]
    117221
    118 Attached file: ''relational_schema.jpg''
     222The exported relational diagram is attached to this page:
     223
     224[attachment:relational_schema.jpg relational_schema.jpg]