An athletic department is responsible for scheduling athletic events for a college. You are to consider the information that a single athletic department (e.g. the BCU Athletic Department) needs to keep track of. So in all of the following, it is understood that the department that is using the database is also one of the teams in every competition.
The athletic department must keep track of contact information for its own athletic personnel (employees only, not athletes). Contact information is first name, last name, address, city, state, zip code, work phone, cell phone, and home phone. Do not worry about any other phone numbers a person might have; three is all we will store in our database, and you may assume that almost all employees will have all three numbers. Each local employee will have an employee ID number. Note that a person may coach more than one sport. Note that there may be more than one person at the school with the same name. Since we are only interested in who is in charge of a given sport, we do not have to keep track of more than one person per sport (e.g. no assistant coaches or grad assistants).
The athletic department must be able to contact appropriate personnel at the opposing college, and must store contact information for those people. Each sport will have one contact. Contact information is first name, last name, sport, and work phone. Address information will be the address of the school (e.g. all BCU employees will have the address 3303 Rebecca St., Sioux City, IA 51104). We do not have ID numbers for the employees of opposing schools, but can assign our own ID number (a "surrogate" key). It is possible that there are two people with the same name (even at the same school), and it is possible for an employee to be the contact person for more than one sport.
Each sport has a single contact person (note that this is for each school; e.g. football contact for Doane, football contact for Dordt, etc. And, there will be a contact person for the school for whom the database is being designed [BCU]), usually (but not always) the coach (this applies to both the local school and all opponents). We do not need to keep track of whether the contact is the coach or not. All we need to know is that this person is the one to contact about this sport. We may need to call the contact person for a sport to discuss things related to a specific contest (e.g. rescheduling after a rainout). So we will need to find out, for example, who is the baseball contact at Doane. It is possible for the same person to be the contact for more than one sport.
We need to keep track of a small amount of information for each school: the school name, address, city, state, zip. We need the address of each school so we can send a bus there with our team and so we can send correspondence to the contacts at the school. We need to know how far it is to each school so we can plan transportation costs. Note that while numbers are usually used for keys, it is legal to use a text string as a key (usually a short string, perhaps an abbreviation). Since the number of schools is small, feel free to identify each school using a text string. You may want to use an abbreviation for the key field and the full school name for another field (e.g. NWC for Northwestern College, DWU for Dakota Wesleyan, etc.). The advantage of this over numeric keys is that they are mnemonic.
The department plays the same schools (with minor additions and deletions) from year to year. Each athletic event must be assigned a single date, time, and location. Note: In this and all future database assignments, you may assume that any Date data type also includes the time. Also, in this and all future database assignments, we will not normalize ZIP codes. The location may be the name of this school (e.g. BCU), the name of an opponent school (e.g. NW), or something else (e.g. Tyson Event Center). We only need one field for this (we do not need address, city, state, zip). Each athletic event has one opponent (and the other team is always understood to be the school for which we are creating this data model—BCU in this case, and therefore no information needs to be stored about it when storing event information).
An event brings together two teams (one of which is ours) to play a specific sport on a specific date and time. For away games, the department needs to be able to calculate the cost of transporting the appropriate number of athletes, coaches, etc., to the event. This will be determined by two things: the distance to the college and the number of athletes that are on the travel squad. Each sport will have a designated travel squad size. It is possible for the same opponent (school) to be scheduled for more than one event on the same day (e.g. playing Mt. Marty in both men's basketball and women's basketball). It is also possible to have the same team have more than one event in the same day (e.g. a baseball doubleheader). So it is possible to have men’s baseball playing two home game against Hastings on the same day, but note that they will be at a different time. It is also possible to have a men's baseball game and a women's softball game at the same time on the same day.
The athletic department must be able to automatically generate contracts for athletic events using the data in a database (a contract would just be a report that is automatically generated from the tables in the database, so there are no tables for contracts). The contract must include the name of both schools and the sport contact person from each school and the athletic director of each school. It also must include the name of the sport, the location of the event, and the date of the event. You may also use short, mnemonic text strings for sports keys, as we did with colleges.
The athletic department also must be able to generate schedules (reports) for each sport (e.g. create a men's basketball schedule), each opponent (e.g. create a report with all of the contests that we have with Dordt in all sports this year), a schedule of all home games (for all sports—so we know at a glance which days our facilities will be tied up) and schedules of all away games (for all sports—so we know at a glance which days we will need to have transportation available).
Reports listing all of the contacts at a given school must be created (e.g. create a contact list for Northwestern). Whenever an athletic event is scheduled, the cost of transporting students to the school must be calculated. Early in the school year, there may not be any events entered into the database yet, but all of the school, sport, and employee information will always be in the database. Note that a contract is similar to a report, which is a paper document that is generated from other information in the database, and is not an entity itself.
The department needs to be able to do the following:
Do the following (note that determining the first three items may be an iterative process):
· Identify each entity class.
· Identify all attributes for each entity class.
· Identify all determinants. Note that this may require you to re-think your entity classes.
· Identify a key for each entity class that will uniquely identify a single entity. Note that the key may be more than a single field. Underline the key field.
· Identify each foreign key (not all entity classes will have a foreign key). Make foreign keys italic.
· For each entity class, identify relationships between it and other entity classes.
· Draw the ER diagram using the crow's foot format used in the textbook.
How to check your work:
· Look at your ER diagram and each entity's attributes. Each line (relationship) in the ER diagram corresponds to a foreign key (that is, the entity on the "many" end of the line will have a foreign key in it and the entity on the "one" end of the line will have a primary key in it). If you have a line (relationship) but no foreign key, you have made a mistake. If you have a foreign key but no line, you have made a mistake.
· Look at the list of things the athletic department needs to be able to do (see bulleted list above). Look at your ER diagram (and your attribute lists) and see if you can determine all of the information you need for each one of these tasks. If there is a task you cannot complete by logically following the relationships between the entities, then you have made a mistake.