Date:
WEEK 1: E-R Model
Analyze the problem carefully and come up with the entities in it. Identify what data has to be persisted in the database. This contains the entities, attributes etc.Identify the primary keys for all the entities. Identify the other keys like candidate keys, partial keys if any. The entities for the above company named Roadway Travels are as follows 1. Bus 2. Ticket 3. Passenger 4. Reservation 5. Cancellation Now we will identify the attributes for each entity and construct the E-R Model.
1. BUS Entity:
The attributes are: i. Bus_No ii. Source iii. Destination iv. Dep_Time The E-R Model is as follows:
In the above figure Bus_No is the Primary Key
2. TICKET Entity:
The attributes are: i. Ticket_No ii. Journey_Date iii. Age iv. Sex v. Source vi. Destination vii. Dep_Time
Page-No:
Date: The E-R Model is as follows:
In the above figure Ticket_No is the Primary Key
3. PASSENGER Entity:
The attributes are: i. PNR_No ii. Ticket_No iii. Name iv. Age v. Sex vi. PPNo The E-R Model is as follows:
In the above figure PNR_No is the Primary Key and Ticket_No is Foreign Key
4. RESERVATION Entity:
The attributes are: i. PNR_NO ii. Journey_Date iii. No_Of_Seats iv. Address v. Contact_No vi. Status
Page-No:
Date: The E-R Model is as follows:
In the above figure PNR_No is the Foreign Key
5. CANCELLATION Entity:
The attributes are: i. PNR_No ii. Journey_Date iii. No_Of_Seats iv. Address v. Contact_No vi. Status The E-R Model is as follows:
In the above figure PNR_No is the Foreign Key
Page-No:
Date:
WEEK 2: Concept Design with E-R Model
Relate the entities appropriately. Apply cardinalities for each relationship. Identify the strong entities and weak entities (if any). Indicate the type of relationships (total/partial). Try to incorporate generalization, aggregation, specialization etc wherever required.
Relationship between BUS Entity and PASSENGER Entity:
The following diagram depicts the relationship exists between BUS Entity and PASSENGER
The relationship between BUS Entity and PASSENGER Entity is strong relationship. Because the two entities consists of primary keys. Here the two entities are strong entities. So the relationship is strong relationship.
Page-No:
Date:
Relationship between PASSENGER Entity and RESERVATION Entity:
The following diagram depicts the relationship exists between PASSENGER Entity and RESERVATION Entity.
The relationship between PASSENGER Entity and RESERVATION Entity is weak relationship. Because the one entity consists of primary key and another entity consists of foreign key. Here the one entity is strong entity (single border rectangle) and another entity is weak entity (double border rectangle). The relationship between strong entity and weak entity is weak relationship.
Page-No:
Date:
Relationship between PASSENGER Entity and CANCELLATION Entity:
The following diagram depicts the relationship exists between PASSENGER Entity and CANCELLATION Entity.
The relationship between PASSENGER Entity and CANCELLATION Entity is weak relationship. Because the one entity consists of primary key and another entity consists of foreign key. Here the one entity is strong entity (single border rectangle) and another entity is weak entity (double border rectangle). The relationship between strong entity and weak entity is weak relationship.
Page-No:
Date:
WEEK 3: Relational Model
Represent all the entities (Strong, Weak) in tabular fashion. Represent relationships in a tabular fashion. There are different ways of representing relationships as tables based on the cardinality. Represent attributes as columns in tables or as tables based on the requirement. Different types of attributes (Composite, Multivalued and Derived) have different way of representation. The following are the tabular representations of the entities used in this company.
1. BUS Entity Table:
2. TICKET Entity Table:
3. PASSENGER Entity Table:
Page-No:
Date:
4. RESERVATION Entity Table:
5. CANCELLATION Entity Table:
Page-No:
