ER Modeling

Create entity-relationship (ER) models for each of the following environments (solutions):

1. A musical group comprises a name, location (e.g., city), and director. A musical group may be an orchestra, chamber group, or jazz ensemble, but must specialize in one of these forms of music. An orchestra has a world ranking, a chamber group has a year founded, and a jazz ensemble has a jazz specialty.

   A musical group must be associated with one or more types of musicians who may play for one group at a time (the time commitment precludes greater involvement). Musicians are characterized by the instrument(s) that they play, such as woodwinds, brass, and percussion. A single musician may be formally trained in one or all of these types of instruments.

   

2. The Super Baseball League wants to maintain information about its teams, their coaches, players, and bats. The information about players is historical. For each team, the league wants to keep track of all of the players who have ever played on the team, including the current players. For each player, it wants to know about every team the player ever played for. On the other hand, coach affiliation and bat information is current, only.

   The league wants to keep track of each team’s team number, which is unique, its name, the city in which it is based, and the name of its manager. Coaches have a name (which is assumed to be unique only within its team) and a telephone number. Coaches have units of work experience that are described by the type of experience and the number of years of that type of experience. Bats are described by their serial numbers (which are unique only within a team) and their manufacturer’s name. Players have a player number that is unique across the league, a name, and an age.

   A team has at least one and usually several coaches. A coach works for only one team. Each coach has several units of work experience or may have none. Each unit of work experience is associated with the coach to whom it belongs. Each team owns at least one and generally many bats. Currently and historically, each team has and has had many players. To be of interest to the league, a player must have played on at least one and possibly many teams during his career. Further, the league wants to keep track of the number of years that a player has played on a team and the batting average that he compiled on that team. (Minicase 2.2)

   

3. The Houston, TX, city government wants to develop an information system to keep track of all the buildings in the city for both taxation and fire department dispatch purposes. The city will track the address, year built, and owner of record of every building. It will also record the station number, address, and telephone number of each fire station. Each fire station has primary responsibility for a given set of buildings.

   There are four types of building: single-family homes, apartment buildings, stores, and office buildings. The city wants to record the number of apartments in each apartment building, and the type of goods and annual sales volume of each store. It wants to record the number of floors in each office building. It must also keep track of the companies in each office building. An office building can have several or many companies in it; a company can have offices in several buildings. Each company has a name, telephone number, and unique tax identification number. The city also wants to store the number of square feet that a particular company occupies in a particular office building. Single-family homes are made up of three parts: the house itself, a garage, and a shed. The city wants to keep track of the number of bedrooms, number of baths, and total floor space in the house, the capacity of the garage in number of cars, and the capacity of the shed in volume (cubic feet). There is also a tax calculation formula that differs for each of the four building types. (Exercise 9.1)