| | 3 | |
| | 4 | == Data Requirements == |
| | 5 | |
| | 6 | === Entities === |
| | 7 | |
| | 8 | * **Entity: Admin** |
| | 9 | * **Description**: An entity that disjointly specializes from the `Account` entity and uniquely identifies administrators of the system. |
| | 10 | * **Primary Key**: `email` - Entered when creating the account. |
| | 11 | * **Attributes**: |
| | 12 | |
| | 13 | * **Entity: Vehicle** |
| | 14 | * **Description**: Generalization entity for all means of transport available on the system. |
| | 15 | * **Primary Key**: `plate_num` - The registration/plate number of the vehicle. |
| | 16 | * **Candidate Key**: Composite key of `brand` and `model` (not used since `plate_num` is a superkey). |
| | 17 | * **Attributes**: |
| | 18 | * `model`: The specific model of the vehicle. |
| | 19 | * `brand`: The manufacturer of the vehicle. |
| | 20 | * `capacity`: The seating capacity of the vehicle. |
| | 21 | |
| | 22 | * **Entity: Trip** |
| | 23 | * **Description**: A weak entity representing an instance of a route, used to check information about tickets purchased for a route. |
| | 24 | * **Partial Key**: `trip_id` - An artificial ID of a trip. |
| | 25 | * **Attributes**: |
| | 26 | * `status`: The state of the trip (e.g., `completed`, `cancelled`, `in progress`). |
| | 27 | |
| | 28 | * **Entity: Review** |
| | 29 | * **Description**: A weak entity describing user ratings for a trip. |
| | 30 | * **Partial Key**: `review_id` - An artificial ID for the review. |
| | 31 | * **Attributes**: |
| | 32 | * `description`: User-provided context about the trip. |
| | 33 | * `rating`: An integer rating (out of 5). |
| | 34 | |
| | 35 | * **Entity: Ticket** |
| | 36 | * **Description**: Allows a user (`Account`) to reserve a place on a route. |
| | 37 | * **Primary Key**: `ticket_id` - An artificial ID for the ticket. |
| | 38 | * **Attributes**: |
| | 39 | * `price`: The base price for the ticket. |
| | 40 | * `date_purchased`: The date the ticket was purchased. |
| | 41 | * `seat`: Derived attribute (calculated as `capacity - free_seats`). |
| | 42 | |
| | 43 | * **Entity: Automobile** |
| | 44 | * **Description**: A disjoint specialization of the `Vehicle` entity, identifying cars. |
| | 45 | * **Primary Key**: `plate_num`. |
| | 46 | * **Attributes**: |
| | 47 | |
| | 48 | * **Entity: Bus** |
| | 49 | * **Description**: A disjoint specialization of the `Vehicle` entity, identifying buses. |
| | 50 | * **Primary Key**: `plate_num`. |
| | 51 | * **Attributes**: |
| | 52 | |
| | 53 | * **Entity: Train** |
| | 54 | * **Description**: A disjoint specialization of the `Vehicle` entity, identifying trains. |
| | 55 | * **Primary Key**: `plate_num`. |
| | 56 | * **Attributes**: |
| | 57 | |
| | 58 | * **Entity: Van** |
| | 59 | * **Description**: A disjoint specialization of the `Vehicle` entity, identifying vans. |
| | 60 | * **Primary Key**: `plate_num`. |
| | 61 | * **Attributes**: |
| | 62 | |
| | 63 | * **Entity: Route** |
| | 64 | * **Description**: Describes a scheduled/frequent route between two or more places. |
| | 65 | * **Primary Key**: `id` - An artificial ID for the route. |
| | 66 | * **Attributes**: |
| | 67 | * `date`: Scheduled date(s) for the route. |
| | 68 | * `free_seats`: Derived attribute indicating available seats. |
| | 69 | |
| | 70 | * **Entity: Child Ticket** |
| | 71 | * **Description**: A disjoint specialization of the `Ticket` entity for passengers below a certain age. |
| | 72 | * **Primary Key**: `ticket_id`. |
| | 73 | * **Attributes**: |
| | 74 | * `price` |
| | 75 | * `purchase_date` |
| | 76 | * `discount` |
| | 77 | * `SSN` |
| | 78 | * `parent_SSN` |
| | 79 | |
| | 80 | * **Entity: One-Way Ticket** |
| | 81 | * **Description**: A disjoint specialization of the `Ticket` entity for one-direction travel. |
| | 82 | * **Primary Key**: `ticket_id`. |
| | 83 | * **Attributes**: |
| | 84 | * `price` |
| | 85 | * `purchase_date` |
| | 86 | |
| | 87 | * **Entity: Two-Way Ticket** |
| | 88 | * **Description**: A disjoint specialization of the `Ticket` entity for round-trip travel. |
| | 89 | * **Primary Key**: `ticket_id`. |
| | 90 | * **Attributes**: |
| | 91 | * `expiration_period`: Time the ticket is valid. |
| | 92 | * `purchase_date` |
| | 93 | * `price` |
| | 94 | * `expiration_date`: Derived attribute (`purchase_date + expiration_period`). |
| | 95 | |
| | 96 | * **Entity: Account** |
| | 97 | * **Description**: Contains main information about application users. |
| | 98 | * **Primary Key**: `email`. |
| | 99 | * **Attributes**: |
| | 100 | * `name` |
| | 101 | * `surname` |
| | 102 | * `password` |
| | 103 | * `university`: Where the student studies. |
| | 104 | * `student_index` |
| | 105 | |
| | 106 | * **Entity: Transport Organizer** |
| | 107 | * **Description**: A disjoint specialization of the `Account` entity for managing trips and schedules. |
| | 108 | * **Primary Key**: `email`. |
| | 109 | * **Attributes**: |
| | 110 | * `name` |
| | 111 | * `surname` |
| | 112 | * `password` |
| | 113 | * `company_name` |
| | 114 | * `company_CIN` |
| | 115 | |
| | 116 | * **Entity: Driver** |
| | 117 | * **Description**: A disjoint specialization of the `Account` entity for individuals responsible for vehicle operation. |
| | 118 | * **Primary Key**: `email`. |
| | 119 | * **Attributes**: |
| | 120 | * `name` |
| | 121 | * `surname` |
| | 122 | * `password` |
| | 123 | |
| | 124 | * **Entity: Student** |
| | 125 | * **Description**: A disjoint specialization of the `Account` entity for students enrolled at Macedonian universities. |
| | 126 | * **Primary Key**: `email`. |
| | 127 | * **Attributes**: |
| | 128 | * `name` |
| | 129 | * `surname` |
| | 130 | * `password` |
| | 131 | * `university` |
| | 132 | |
| | 133 | * **Entity: Favorite** |
| | 134 | * **Description**: A weak entity representing a user's favorite routes. |
| | 135 | * **Primary Key**: Composite key (`email`, partial key for each favorite route). |
| | 136 | * **Attributes**: |
| | 137 | |
| | 138 | * **Entity: Location** |
| | 139 | * **Description**: Contains geographical points for route stops (along the way or final destinations). |
| | 140 | * **Primary Key**: Composite key (`latitude`, `longitude`). |
| | 141 | * **Attributes**: |
| | 142 | * `time`: Estimated arrival time. |
| | 143 | * `name`: Descriptive local names. |
| | 144 | |
| | 145 | * **Entity: Payment** |
| | 146 | * **Description**: A weak entity describing the process of purchasing tickets. |
| | 147 | * **Partial Key**: artificial ID `payment_id`. |
| | 148 | * **Attributes**: |
| | 149 | * `date` |
| | 150 | * `num_tickets` |
| | 151 | * `total_price`: Derived attribute. |
| | 152 | |
| | 153 | |
| | 154 | === Relationships === |
| | 155 | |
| | 156 | * **makes**: 1:N Relation between `Account` and `Payment`. |
| | 157 | * Description: Tells which payment was made by which account and is used as an identifying relation for a payment. |
| | 158 | |
| | 159 | * **leaves_a**: 1:N Relation between `Account` and `Review`. |
| | 160 | * Description: Tells which review was left by which user. This relation identifies the entity `Review`. |
| | 161 | |
| | 162 | * **adds**: 1:N Relation between `Account` and `Favorite`. |
| | 163 | * Description: Tells which account added which route to favorites. This relation identifies the entity `Favorite`. |
| | 164 | |
| | 165 | * **for_ticket**: 1:N Relation between `Payment` and `Ticket`. |
| | 166 | * Description: Tells which payment was made for which ticket. |
| | 167 | |
| | 168 | * **works_for**: 1:N Relation between `Transport Organizer` and `Driver`. |
| | 169 | * Description: Tells which driver works for which transport organizer. |
| | 170 | |
| | 171 | * **on**: 1:N Relation between `Review` and `Trip`. |
| | 172 | * Description: Tells which review was left on which trip. This relation identifies the entity `Review`. |
| | 173 | |
| | 174 | * **organizes**: 1:N Relation between `Transport Organizer` and `Route`. |
| | 175 | * Description: Tells which transport organizer organized which route. |
| | 176 | |
| | 177 | * **is_a**: 1:N Relation between `Route` and `Favorite`. |
| | 178 | * Description: Tells which route is favorited by which account. This relation identifies the entity `Favorite`. |
| | 179 | |
| | 180 | * **for**: 1:N Relation between `Route` and `Trip`. |
| | 181 | * Description: Tells which route was made for which trip. This relation identifies the entity `Trip`. |
| | 182 | |
| | 183 | * **on**: 1:N Relation between `Route` and `One-Way Ticket`. |
| | 184 | * Description: Tells which route was bought by which one-way tickets. |
| | 185 | |
| | 186 | * **back**: 1:N Relation between `Route` and `Two-Way Ticket`. |
| | 187 | * Description: Tells which route was purchased as part of a two-way ticket for the return journey. |
| | 188 | |
| | 189 | * **forward**: 1:N Relation between `Route` and `Two-Way Ticket`. |
| | 190 | * Description: Tells which route was purchased as part of a two-way ticket for the outbound journey. |
| | 191 | |
| | 192 | * **owns**: 1:N Relation between `Transport Organizer` and `Vehicle`. |
| | 193 | * Description: Tells which transport organizer owns which vehicle. |
| | 194 | |
| | 195 | * **stops_at**: N:M Relation between `Route` and `Location`. |
| | 196 | * Description: Tells which locations are included as stops on specific routes. |
| | 197 | |
| | 198 | * **operates**: N:M Relation between `Driver` and `Vehicle`. |
| | 199 | * Description: Tells which vehicles are operated by which drivers. |
| | 200 | |
| | 201 | * **uses**: N:M Relation between `Route` and `Vehicle`. |
| | 202 | * Description: Tells which vehicles are used on which routes. |
| | 203 | |
| | 204 | * **drives_on**: N:M Relation between `Route` and `Driver`. |
| | 205 | * Description: Tells which drivers drive on which routes. |
| | 206 | |
| | 207 | * **lists**: N:M Relation between `Account` and `Trip`. |
| | 208 | * Description: Tells which account has been on which trips. |
| | 209 | |
