2. Conceptual design
Create ER-Diagram in Visio Studio, pointing out connections and primary keys and foreign keys.
Table 1.
3. Logical design
In the Logical Design part, we transform our ER-Diagram into the table, to see and make out all of our entities and compare them with each other to bring our database to normal forms and identify a Primary key and Foreign key. Much attention is paid to the data structure.
Final version ER-Diagram u can find in Table 1.
Note: N – for numeric, C – character, D – date, PK – primary key, FK – foreign key.
Autopark
Field’s content |
Field’s name |
Type, length |
Note |
ID |
autopark_id |
N |
PK |
Autopark name |
autopark_name |
C(25) |
Mandatory field |
Drivers
Field’s content |
Field’s name |
Type, length |
Note |
ID |
driver_id |
N |
PK |
Name |
driver_name |
C(50) |
Mandatory field |
Surname |
driver_surname |
C(50) |
Mandatory field |
Date of birth |
date_birth |
D |
Mandatory field |
Class of driving |
class_driving |
C(25) |
Mandatory field |
Experience |
exp |
C(25) |
Mandatory field |
Salary |
salary |
N |
Mandatory field |
Address |
address |
C(25) |
Mandatory field |
Telephone number |
telephone_number |
N |
Mandatory field |
e_mail |
C(25) |
Mandatory field |
Conductors
Field’s content |
Field’s name |
Type, length |
Note |
||||
ID |
conductor_id |
N |
PK |
|
|||
Name |
conductor_name |
C(50) |
Mandatory field |
|
|||
Surname |
cinductor_surname |
C(50) |
Mandatory field |
|
|||
Date of birth |
date_birth |
D |
Mandatory field |
|
|||
Salary |
salary |
N |
Mandatory field |
|
|||
Address |
address |
C(25) |
Mandatory field |
|
|||
Telephone number |
telephoe_number |
N |
Mandatory field |
|
|||
e_mail |
C(25) |
Mandatory field |
|
||||
Routes
Field’s content |
Field’s name |
Type, length |
Note |
ID |
routes_id |
N |
PK |
The starting point |
start_point |
C(25) |
Mandatory field |
The final desination |
final_point |
C(25) |
Mandatory field |
Interval length |
interval_length |
N |
Mandatory field |
Stops
Field’s content |
Field’s name |
Type, length |
Note |
ID |
stops_id |
N |
PK |
Stops name |
stops_name |
C(25) |
Mandatory field |
Breakage
Field’s content |
Field’s name |
Type, length |
Note |
ID |
breakage_id |
N |
PK |
Date of breakage |
date_breakage |
D |
Mandatory field |
Description |
discription |
C(50) |
Mandatory field |
Transport
Field’s content |
Field’s name |
Type, length |
Note |
ID |
transport_id |
N |
PK |
Name |
model_name |
C(25) |
Mandatory field |
Type |
type |
C(25) |
Mandatory field |
Mileage |
mileage |
N |
Mandatory field |
Date of technical Inspection |
date_tech_inspec |
D |
Mandatory field |
Ready for use |
rdy_for_use |
С(25) |
Mandatory field |
Number of seats |
number_seats |
N |
Mandatory field |
Total capacity |
total_capacity |
N |
Mandatory field |
Have a validator |
have_validator |
C(25) |
Mandatory field |
Autopark |
autopark_id |
N |
FK |
Routes |
routes_id |
N |
FK |