written 7.8 years ago by
teamques10
★ 64k
|
modified 2.2 years ago
by
sagarkolekar
★ 11k
|
|
written 7.8 years ago by
teamques10
★ 64k
|
• modified 7.8 years ago |
Step 1: E-R Diagram
Step 2: Converting the E-R Diagram into Tables
a. Converting entity to table and attribute to columns
USER
| U_id | Primary Key |
|---|---|
| Password | |
| Security_quest |
Passenger
| PNR | Primary Key |
|---|---|
| P_name | |
| Age | |
| Gender | |
| Seat_no. | |
| Reserve_status | |
| U_id | Foreign key references to U_id of User table. |
| Status_id | Foreign key references to Status_id of Train_Status Table. |
| Train_id | Foreign key references to Train_id of Train Table |
Train_Status
| Status_id | Primary Key |
|---|---|
| Wait_seat | |
| Avail_seat | |
| Booked_seat | |
| Train_id | Foreign key references to Train_id of Train Table |
| PNR | Foreign key references to PNR of Passenger table. |
Train
| Train_id | Primary Key |
|---|---|
| T_name | |
| Train_type | |
| Avail_class | |
| Status_id | Foreign key references to Status_id of Train_Status Table. |
Route
| Arr_time | |
|---|---|
| Depart_time | |
| Stop no. | |
| Station_id | Foreign key references to Station_id of Station table. |
| Train_id | Foreign key references to Train_id of Train Table |
Station
| Station_id | Primary Key |
|---|---|
| Station_name |
Step 3: Mapping of Attributes
a. Simple Attributes
Simple attributes which cannot be divided into subparts.
Example: Seat Number of passenger
b. Composite Attributes
Composite attributes which can be divided into subparts.
Example: Passenger Name, Reservation status
P_name
| First_Name |
|---|
| Middle_Name |
| Last_name |
Reserve_status
| Waiting |
|---|
| Confirmed |
Step 4: Mapping of entity set
a. Weak entity set
For each weak entity type with owner entity, create a table and include all simple attributes of weak entity type as columns of table, including foreign key attributes as the primary key of the table that correspond to the owner entity type.(Owner entity is strong entity having own primary key.)
Example: Route (Weak Entity) in Train (Owner entity).
Step 5: Mapping of Relationships
a. Foreign Key approach
Passenger_Table
| U_id | Passenger table makes foreign key references to U_id of User table. |
|---|---|
| Status_id | Passenger table makes foreign key references to Status_id of Train_Status Table. |
| Train_id | Passenger table makes foreign key references to Train_id of Train Table |
Train_Status
| Train_id | Train_status table makes foreign key references to Train_id of Train Table |
|---|---|
| PNR | Train_status table makes foreign key references to PNR of Passenger table. |
Train
Route
| Station_id | Route table makes foreign key references to Station_id of Station table. |
|---|---|
| Train_id | Route table makes foreign key references to Train_id of Train Table |
Step 6: Identifying the relationships
a. Passenger can book many tickets.
Therefore the relations are 1……..N.
b. Trains are associated with each Train_status
Therefore the relation is M……..1.
c. Trains can route from one route.
Therefore the relations are N……..1.
d. Station has a set of Route.
Therefore the relations are 1……..N.
and 3 others joined a min ago.