Term Project Part 2: Relational Modeling

This assignment is a database design project focusing on relational modeling. The student has created a relational schema based on an Entity-Relationship (ER) diagram (not shown in the provided text). The solution details the design choices for each table, including the selection of primary and foreign keys. Tables include AGENCY, OFFERS, ROUTE, SERVES, TRIP, HAS, STOP_TIME, STOP, CALENDAR, and USES. The student explains the rationale behind the design choices, particularly addressing relationships between entities (one-to-one, one-to-many, many-to-many) and the handling of composite attributes and multi-valued attributes. The document demonstrates an understanding of relational database principles and how to translate an ER model into a functional relational schema.

0 | P a g e
Term Project Part 2: Relational Modeling and Schema
STUDENT NAME:
14 November, 2016

1. TABLE AGENCY: This table was easy to build. All the attributes from the entity were used.
The primary key is same as that in ER-diagram.
AGENCY (AGENCY_ID, NAME, URL, PHONE, TIME_ZONE, LANGUAGE)
2. TABLE OFFERS: this table was designed separately because as the relationship says agency
offers 1 route and a route is offered by one or more agencies. Hence we defined the primary
keys from route and agency table into a separate table to show the relationship
OFFERS (AGENCY_ID*, ROUTE_ID*)
3. TABLE ROUTE: This table was easy to build. All the attributes from the entity were used. The
primary key is same as that in ER-diagram. It is not a weak entity so no trade-off.
ROUTE (ROUTE_ID, ROUTE_SHORT_NAME, ROUTE_LONG_NAME, ROUTE_DESCRIPTION,
ROUTE_TYPE, ROUTE_URL, ROUTE_COLOR)
4. TABLE SERVES: as the relation says, route servers on trip and one trip is served by zero or
more routes. So we defined a separate relation where route id is primary key. Here route id
and trip id are foreign keys from route and trip tables respectively.
SERVES (ROUTE_ID*, TRIP_ID*)
5. TABLE TRIP: this table was simple to design. The attributes and primary key hence defined
were used.
TRIP (TRIP_ID, TRIP_HEADSIGN, TRIP_SHORT_NAME)
1 | P a g e

6. TABLE HAS: This table has been designed to present the relation between trip and stop time
table. The relation is that each trip has a stop time and each stop time is related to one or
more trips.
HAS (TRIP_ID*, STOP_SEQUENCE*)
7. TABLE STOP_TIME: this is week entity, so as the relation says each stop is served by stop
time and a stop time serves zero or more stops, so we will use stop_id from stop table as a
part of primary key
STOP_TIME (STOP_ID*, STOP_SEQUENCE, DEPARTURE_TIME, ARRIVAL_TIME)
8. TABLE STOP: this is a strong entity hence was easy to define. The attributes and primary key
so defined were used. Since range is a composite attribute, so we will use the base attributes
as columns in the schema.
STOP (STOP_ID, STOP_NAME, STOP_DESCRIPTION, URL, LATITUDE, LONGITUDE)
9. TABLE CALENDAR: this table has multivalued attribute schedule, so we will use schedule
itself as a column. Apart from that all other attributes in ERD are used in the schema same as
defined.
CALENDAR (SERVICE_ID, TRIP_ID*, START_DATE, END_DATE, SCHEDULE)
10. USES TBALE: This table has been designed to represent the relation between calendar and
trip. The relation says each trip uses zero or more calendar and a calendar is used by a trip.
USES (SERVICE_ID*, TRIP_ID*)
2 | P a g e