Week 6 / 10.22.02

ER Analysis

 Part 1.  Relationships

 Ternary Relationship

            Student : Course : Professor

 Recursive Relationship

              Child is sibling of Child

 Derivative Attribute

                BMI or Body Mass Index.  Derived from Height and Weight.              

1:1 Relationship

            Vehicle : VIN Number

 1:N Relationship

            Rose : Varieties

 M:N Relationship

            Patients : Appointments

 Part 2. Conceptual Schema

 Entities

Agency (identified by name)

Public Transportation Routes

Stops

Person

Need/Subject Area

Address (made up of several weak entities)

Street Number

Street Name

City Name

State (fixed length field, 2 letters)

Zip code (fixed length field, 9 digits)

Distance (calculated by database operator)

 Relationships

Agency <has> Office Location – hierarchical relationship

Office Location <has> Address 1:1

Address <has> Street Number 1:1

Address <has> Street Name 1:1

Address <has> City Name 1:1

Address <has> State 1:1

Address <has> Zip Code 1:1

Person <has> Address 1:N

Route <has> Stops – hierarchical relationship 1:N

Stops <has> Address

Person <has> Need

Agency <deals with> Subject/Need

  The conceptual schema for the this system is rather simple.  An agency entity which deals with a  particular subject or need entity is at a stop entity of a public transportation route that also has a stop entity that is close to the address entity of a person entity that has the need entity served by the agency entity.  I dealt with the problem in a fairly simple way, allowing all information to be held in one database.  I expect that in reality this type of database would also allow a person to find the schedule for the public transportation routes and to see the times at which the bus, train, etc. arrives at each stop.  I imagine if enough funding was available the database would allow people to find out which agencies could help Spanish or Chinese speakers. 

  Part 3.
Contrast the entity-oriented approach with the request-oriented approach and explain the choices you made. (In other words, respond to Soergel's question in the text near the bottom of Page 141.)

        In the entity-oriented approach there may be a tendency to over-describe an entity.  This occurs when one seeks to incorporate all data elements need to characterize an entity.  In the request-oriented approach some data elements may be left out or modified to fit the needs of the requester.  In this problem, the entity oriented approach might seek to describe the agency entity in full, including staffing, hours, facilities details and more.  By using the request-oriented approach we are able to describe the agency entity only to the extent that is necessary to fulfill the transportation needs of the person in need. When user needs are taken into consideration the entity classes deemed necessary in a database can change radically.

Part 4. Input and Output formatting

    The input formatting for this problem has two sides, the format for inputting by staff or programmers, and the format used to query the database. A programmer or staff member must be able to enter information about the agency and route entities into the database.  This is done behind the scenes and this information is seen by the person querying the database as a result.  At the same time, the person in need must be able to enter their own address information as a way of querying the system.  This is another input format.  As for output format, there are at least two types required.  The programmer or staff member must be able to access all the entity information and entries in the database.  They may wish to view all the addresses that have been used to query the system in order to better serve the public.  For the person in need there should be an output format that shows the agencies in their area and the routes and stops that correspond to the agency and to their own address.

Part 5.

Entity Relationship Diagram 

Part 6.

 UML Entity Class drawing