The University of Texas

CS 327e

Spring 2005

Lab 2 Solution

2.77 The Public Affairs Office at Highline University receives requests for speakers on particular topics. To be able to respond to such request, that office wants to build a database. In particular, it wants to keep track of topics, speakers, speeches, and organizations to which someone from Highline has spoken.

A. Make a list of possible entities for the Speaker database.

TOPICS

SPEAKERS

SPEECHES

ORGANIZATIONS

B. Create an IDEF1X E-R diagram showing only entities (like Figure 2-34). Assume that there is an M:N relationship between SPEAKER and TOPIC.

C. For each relationship in your diagram, specify the relationship type, and the minimum and maximum cardinality on both parent and child. Name each relationship.

SPEAKER and TOPIC: This would be a non-specific relationship because it is a many-to-many relationship.

SPEAKER and SPEECHES

SPEECHES and ORGAINZATIONS

D. Repeat questions B and C, but add an entity to your diagram so that there will be no N:M relationship.

E. Do you prefer the design in question B and C or the design in question D? Justify your preference.

2.78 The Metropolitan Housing Agency (MHA) is a non-profit organization that advocates the development and improvement of low-income housing. The MHA operates in a metropolitan area of approximately 2.2 million people in a midwestern city. The MHA maintains data about the location, availability, and condition of low-income housing in 11 different census tracts in the metropolitan area. Within the boundaries of these tracts are approximately 250 different buildings that provide low-income housing. On average, each building contains 25 apartments or other units.

The MHA keeps data about each census tract, including geographic boundaries, median income of the population, elected officials, principal businesses, principal investors involved in attributes in that tract, and other demographic and economic data. It also maintains a limited amount of data about crime. For each building, the MHA stores the name, address, size, owner(s)’s name and address, mortgagor(s)’s name and address, renovations and repairs, and avail-ability of facilities for handicapped people. In addition, the MHA keeps a list of each of the units within each building, including the type of unit, size, number of bedrooms, number of baths, kitchen and dining facilities, location in the building, and any special remarks. The MHA wants to maintain data regarding the average occupancy rates for each unit, but to date it has been unable to collect or store such data. The MHA does, however, keep data about whether a given unit is occupied.

The MHA serves as an information clearinghouse and offers three basic ser-vices. First, it works with politicians, lobbyists, and advocacy groups to support legislation that encourages the development of low-income housing through tax incentives, developmental zoning preferences, and other legislative inducements. To accomplish this, the MHA provides information about low-income housing to state, county, and city governments. Second, through speeches, seminars, displays at conventions, and other public relations activities, the MHA officials strive to raise the community’s consciousness about the need for low-income housing. Finally, the MHA provides information about the availability of low-income housing to other agencies that work with the low-income and homeless populations.

A. Make a list of possible entities for a database to support MHA’s information needs.

Elected Officials

Principle Businesses

Principle Investors

Census Tract

Building

Apartment

Crime

B. Construct an IDEF1X entity-relationships diagram for the entities in question A.

C. Name each relationship and ensure that you have correctly defined the minimum and maximum cardinalities.

These are shown on the diagram above.

Note: You may find that as you answer question B, you will add, remove, or change some of the entities in your answer to question A. This is normal and very typical of the data modeling process. Just keep working back and forth until you have an IDEF1X E-R diagram that you believe is correct.

3.37Examine the list of email messages in Figure 3-30. Using the structure and example data items in this list, do the following:

A. Create a single entity data model for this list. Specify the identifier and all entities.

Since no attribute is unique, a MailID was created.

B. Modify your answer to A to include entities SENDER and SUBJECT. Specify the identifiers and attributes of entities and the type and cardinalities of relationships. Explain which cardinalities can be inferred from Figure 3-30 and which need to be checked out with users.

The one-to-many relationships between EMailMessage and Subject can be implied because the subject repeats. For example, there are three messages with the Subject RE:Hotel.

The one-to-many relationships between EMailMessage and Sender can also be implied because the sender repeats. For example, there are three messages from Tom Cooper.

C. There are two different styles of email address in the From column in Figure 3-30. One style has the true email address; the second style (Tom Cooper, for example) is the name of an entry in the user’s email dictionary. Create two categories of SENDER for these two styles. Specify identifiers and attributes.