Database Management

The University of Texas at El Paso

Professor Kirs

 

ERDs: Sample Problems and Exercises

 

Match the Terms on the Left with the Definitions on the Right

 

 

         SEE ANSWER

 

2.      List three uses of an ERD

 

         SEE ANSWER

 

3.      Draw each of the Basic ERD Symbols and explain what each one is.

 

         SEE ANSWER

 

3a.    Explain what strong and weak entities are and give at least one example of each.

 

         SEE ANSWER

 

3b.    Draw and describe the major attribute types.

 

         SEE ANSWER

 

4.      Explain What Cardinality is, why it is important, and what basic symbols are used to represent it.

 

         SEE ANSWER

 

4a.    Explain what cardinality constraints are and how they impact on database design.

 

         SEE ANSWER

 

5.      Explain What the degree of a relationship is, and give some examples of different degrees of relationships.

 

         SEE ANSWER

 

5a.    Explain what a composite attribute is. Explain what multi-valued attribute is. Give at least one example of each.

 

         SEE ANSWER

 

5.b.  Refer to the ERD Below to answer the following questions:

 

5.b1. Use the following business rules to write all appropriate connectivities in the E-R diagram:

a.   A department employs many employees, but each employee is employed by one department.

b.   Some employees, known as "rovers," are not assigned to any department.

c.   A division operates many departments, but each department is operated by one division

d.   An employee may be assigned to many projects and a project may have many employees assigned to it.

e.   A project must have at least one employee assigned to it.

f.   One of the employees manages each department.

g.   One of the employees runs each division.

5.b.2.    Write all the cardinalities into the model.

5.b.3.  Modify the E-R model by splitting the M:N relationship into two 1:M relationships that are connected through a composite entity, then rewrite the connectivities and cardinalities to match the changes you have made.

         SEE ANSWER

 

Additional Multiple Choice Questions:

 

The questions which follow refer to this ERD:

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

MC1a.         ___                                           This is a __ relationship.

 

a.      unary                                                      d.      simple

b.      binary                                                     e.      complex

c.      ternary

 

MC1b.   ___  This is a __ relationship.

 

a.      1:1                                                          d.      1:1:M

b.      1:M                                                         e.      M:M:M

c.      M:M    

 

MC1c. ___  Which of the following is a primary key?

 

a.   employee_ID                                           d.      depart_id

b.   employee_name                                     e.      skill

c.   address

 

 

MC1d.         ___                                            Which of the following is a foreign key?

 

a.   employee_ID                                           d.      depart_id

b.   employee_name                                     e.      skill

c.   address

 

MC1e.         ___                                            Which of the following is a composite attribute?

 

a.   employee_ID                                           d.      yrs_employeed

b.   address                                                    e.      skill

c.   depart_id

 

MC1f.  ___  Which of the following is a multivalued attribute?

 

a.   employee_ID                                           d.      yrs_employed

b.   address                                                    e.      skill

c.   depart_id

 

MC1g.         ___                                            Which of the following is a derived attribute?

 

a.   employee_ID                                           d.      yrs_employeed

b.   address                                                    e.      skill

c.   depart_id

 

      SEE ANSWERS

 

6.      Draw the ERD for the following situations. Show the needed attributes.

 

6.a.   A college course may have one or more scheduled sections, or may not have a scheduled section. Attributes of COURSE include Course_ID (identifier), name, and Credits. Attributes of SECTION include Section_ID (identifier), and Semester_ID (A composite attribute consisting of Semester and Year).

 

         SEE ANSWER

 

6.a2.  I noted that this class is the relationship between you and I (which is true). However, that was kind of a simplification. For instance, at the end of the semester, you will be given a grade for this class. How do we keep track of that grade? This class is different from this course (a course can have many classes).How do we associate the two? A class is also associated with a room, and is conducted related to a specific room and semester. How?

 Complete the (partially filled) ERD below. Make sure you fill indicate all of the relationship cardinalities, primary keys and foreign keys. There are also some necessary non-keys (i.e. your grade) that need to be shown.

 

 

 

 In response to your requests, I've added some solutions to the above question:

 

 

 

The Data would now appears as:

The results are unchanged

 

6.b.   A company has a number of employees. The attributes of EMPLOYEE include Employee_ID (identifier), Name, Address (A composite attribute), and birthdate. The company also has several projects. Attributes of PROJECT include Project_ID (identifier), Project_name, and Start_Date. Each employee may be assigned to one or more projects, or may not be assigned to a project. A project must have at least one employee assigned to it, and may have many employees assigned to it. An employee’s billing rate may vary by project, and the company wishes to record the applicable billing rate (Billing_Rate) for each employee when assigned to a particular project.

 

         SEE ANSWER

 

6.c.   A University has a large number of courses in its catalog. Attributes of COURSE include Course_ID (Identifier), Name, and Credits.  Each course may have one or more different courses as a prerequisite, or may have no prerequisites. Similarly, a particular course may be a prerequisite for any number of courses, or may not be a prerequisite for any other course.

 

         SEE ANSWER

See Answer

6.d.   A Laboratory has several chemists who work on one or more projects. Chemists also may use certain kinds of equipment on each project. Attributes of CHEMIST include Employee_ID (Identifier), Name and PhoneNo. Attributes of PROJECT include Project_ID (Identifier), and Start_Date. Attributes of EQUIPMENT include Serial_No and Cost. The organization wishes to record Assign_Date, that is, the date when a given piece of equipment was assigned to a particular chemist on a particular project. A chemist must be assigned to at least one project and one equipment item. A given piece of equipment need not be assigned, and a given project need not be assigned either a chemist or equipment item.

 

         SEE ANSWER

 

6.e.   Consider a simple purchase made with a credit card. Many BANKs issue a credit cards to many of its CUSTOMERs. Naturally, each CUSTOMER may have credit cards from any number of BANKs. Each BANK has a unique BankID and other characteristics. Information maintained about a CUSTOMER includes the CustID, CustName, CustAddress, CreditLimit, and AvailableBalance. The CreditLimit is the maximum amount of credit available, and the AvailableBalance is equal to the CreditLimit minus the amount of the outstanding (made but not yet paid for) purchases made by the CUSTOMER. Additionally, each credit card transaction involves the MERCHANT (the store where the customer made the purchase). Information maintained about the MERCHANT includes the MerchantID, and other information. Therefore, each credit card transaction must include information about the CUSTOMER who made the purchase, The BANK that issued the credit card, and the MERCHANT who sold the item(s). Transaction Information must also include the date of the transaction, as well as the amount of the transaction (if an item is returned, the amount of the transaction is negative).

 

         Draw the ERD to reflect this situation.

 

         SEE ANSWER

 

6.01    The other day I was at TGIF’s, and I started wondering about the database they, and probably every other restaurant, used to keep track of their employees. So I asked my friend, Bob the bartender (this in spite of the fact that he is The Master of Disinformation – an M.D.). He told me that they did keep information on each employee, things like IDs, Names, Addresses, and so forth. It turns out that because TGIF’s has a number of restaurants in the same city (each with their own Restaurant IDs), an employee could work at many of the restaurants. Naturally, each restaurant always has many employees. Each employee can have many different titles, which surprised me. For example, an employee can be a waiter, a bartender, a cook, a gopher, a space occupant, and so forth – it all depends on where they are needed. They keep track of things like title name, and title pay rate. It turns out that titles are filled in terms of the skills necessary for that title. A title may require many skills, and, of course, a skill may be used in many titles. For example, the waitress and bartender titles both require interpersonal skills, a fake but impressive smile, and a good memory, as well as other skills. As it turns out, when an employee is hired, they are given a number of tests to determine their skills. Each employee generally has a number skills (interpersonal skills, cooking skills, cleaning skills, the ability not to laugh at drunks, etc.), and of course, each skill is shared by a number of employees (this in spite of the fact that I have yet to find a bartender there that can get you a drink in less than 15 minutes (What am I, a termite??); maybe they don’t consider speed a skill).

 

         Draw the ERD for this situation. Make sure you show ALL of the primary and foreign keys necessary, and the cardinality and cardinality constraints for each of the relationships. If additional fields are necessary, show them, but you need not show ALL of the fields you would add (a simple composite field called “Others” will suffice).

 

         SEE ANSWER

 

6.10 Consider a chain of stores, such as Sears. Each store employs a number of employees, but an employee can only be employed by one store. At each store, there is one and only one employee who is the store manager. However, within each store, there are many Supervisors, all of whom are also employees. These Supervisors can supervise Many other employees. Each store sells many different items. Each item is generally sold at each store, although some stores sell unique items, and the quantity of items on hand typically varies from store to store. We also need to keep track of the cost of each each item, which is also the same at each store.

The basic template (with many missing entries) is given below:



Fill in the template with appropriate table names and fields. Check your design by entering sample data for table.

The link below will take you to a possible solution. CLICKING ON THE LIST BEFORE YOU TRY AND COME UP WITH YOUR OWN SOLUTION IS A COMPLETE WASTE OF YOUR TIME; YOU WILL NOT LEARN ANYTHING, AND PROBABLY WILL NOT BE ABLE TO ANSWER THIS PROBLEM ON THE  TEST (should it be asked of you).

SEE ANSWER
 

7.      Prepare an ERD for a real estate firm that lists property for sale. The following describes the organization (NOTE: For most of the information given, it is up to you to determine which are primary keys):

 

·        The firm has a number of sales offices in several states. Each office is identified by a Office_Number and its location.

 

·        Each sales office is assigned one or more employees. Attributes of employee include Employee_ID and Employee_Name. An employee must be assigned to only one sales office.

 

·        For each sales office, there is always one employee assigned to manage that office. An employee may manage only the sales office to which s/he is assigned.

 

·        The firm lists property for sale.  Some of the information they keep track of include Property_ID and location. Components of location include Street, City, State, and Zipcode.

 

·        Each unit of property must be listed with one (and only one) of the sales offices. A sales office may have any number of properties listed, or may have no properties listed.

 

·        Each unit of property has one or more owners, who are identified by Owner_ID and Name. An owner may own one or more units of property.

 

         SEE ANSWER

 

8.      You have been asked to develop a preliminary ERD for a symphony Orchestra. So far, you have uncovered the following Entities:

 

·        CONCERT SEASON. The Season during which a series of concerts will be performed. One of the attributes you have chosen is Opening_Day, which includes Month, Day, and Year.

 

·        CONCERT. This is a given performance, which consists of one or more compositions. Some of the attributes decided upon include Concert_No and Concert_Date, which consists of Month, Day, Year and Time. Each Concert is generally, although not always, perfomed on more than one concert date.

 

·        COMPOSITION. Each concert may have may compositions performed, although sometimes, only one composition is performed. Attributes decided upon include Composition_ID, Composer_Name, Composition_Name, and Movement_ID (Which is a composite attribute consisting of Movement_Number and Movement_Name). Many, but not all, compositions have multiple movements.

 

·        CONDUCTOR.  Although there is a principal conductor, who generally conducts the symphony, there are frequent guest conductors. This entity includes Conductor_ID and Conductor_Name.

 

·        SOLOIST. The symphony frequently, although not always, employs guest soloists. These are identified by Soloist_ID and Soloist_Name.

 

During further investigation, you discovered a number of additional considerations:

 

·        During a concert season,  there are one or more concerts scheduled. A particular concert is scheduled only once in a season.

 

·        The Concert includes the performance of one or more compositions. However, A composition may be performed at one or more concerts, or may not be performed all season.

 

·        For each concert, there is one and only one conductor. A conductor may conduct any number of compositions, or may not conduct that season.

 

·        Each composition may require one or more soloists, or may not require a soloist. A soloist may perform one or more compositions at any given concert. The symphony wishes to record the date when a soloist last performed a given composition (Date_Last_Performed).

 

SEE ANSWER

 

9.      8.5. We know that the Treatment ERD we constructed was unrealistically simple. Here are some of the modifications we need to make:

 

1.         A treatment can include multiple physicians

2.         A treatment can include multiple nurses (for simplicity, assume each nurse costs $100/hour).

3.         A treatment can include multiple treatment procedures. Each of these procedures are associated with a cost.

4.         A treatment can involve the administration of multiple drugs (during the treatment). Each of these drugs is associated with a cost.

5.         Treatments can be performed in any number of locations. The costs for treatment also vary with the cost of the location.

 

            As we know there are a number of additional considerations (In-patient v. out-patient treatments, hospital room costs, meals … the list can be very long). Let’s recognize them, but not include them here.

 

 

Draw the necessary ERD to reflect this situation.

 

9.     Consider a Hockey Game. A Game is really nothing more than a relationship between two Hockey Teams. For Example, the New York Rangers play the Buffalo Sabres many times in a season. Sometimes, The New York Rangers are the HomeTeam, and sometimes they are the VisitingTeam. For each game played, we need to keep track of who is the HomeTeam, who is the VisitingTeam, When they played (i.e., the DatePlayed), What the HomeTeamScore was, and What the VisitingTeamScore was. Each Team is identified by a TeamID. We need to also keep track of each TeamName, and other information. Each team also employs a number of players. Each player is identified by a PlayerID. We also keep track of each PlayerName, as well as other information. Unfortunately (for this problem), a player can play for a number of different teams during their careers. As a matter of fact, a player might play for one team, be traded to another, and then later be traded back to the team they played for previously. We must therefore also keep track of when a player started playing for a particular team, and when they stopped playing for a particular team (if they are still playing for a team, the value NULL is placed in the date they stopped playing for a particular team).

 

Draw the necessary ERD to reflect this situation.

 

SEE ANSWER

 

 10.    This will be a (Graded) assignment for you.

 

Remember the hockey game ERD we went over last week? It started me thinking about what additional data were necessary. I went on-line and looked at what data the NBA kept. First I looked at the ‘standings’ page (from USA Today):

 

Eastern Conference

Atlantic Division	W	L	PCT	GB	HOME	ROAD	CONF	STREAK
Boston Celtics	36	21	.632	-	16-11	20-10	23-13	Lost 2
Toronto Raptors	31	28	.525	6	21-9	10-19	22-17	Lost 4
Philadelphia 76ers	22	37	.373	15	10-18	12-19	10-21	Lost 3
New York Knicks	20	39	.339	17	12-20	8-19	14-25	Lost 2
New Jersey Nets	6	53	.102	31	3-26	3-27	5-32	Lost 1
Central Division	W	L	PCT	GB	HOME	ROAD	CONF	STREAK
Cleveland Cavaliers	47	14	.770	-	26-4	21-10	27-8	Won 4
Chicago Bulls	31	29	.517	15 1/2	19-10	12-19	19-18	Lost 2
Milwaukee Bucks	30	29	.508	16	18-9	12-20	21-15	Lost 1
Detroit Pistons	21	38	.356	25	14-16	7-22	14-19	Lost 3
Indiana Pacers	20	39	.339	26	13-16	7-23	15-22	Won 1
Southeast Division	W	L	PCT	GB	HOME	ROAD	CONF	STREAK
Orlando Magic	41	20	.672	-	23-6	18-14	29-11	Won 2
Atlanta Hawks	38	21	.644	2	23-7	15-14	20-12	Won 2
Miami Heat	29	31	.483	11 1/2	14-14	15-17	18-17	Lost 4
Charlotte Bobcats	28	30	.483	11 1/2	20-8	8-22	17-18	Lost 1
Washington Wizards	21	36	.368	18	12-18	9-18	15-21	Won 1

 

We already keep some of this data (at least we keep Team Name). Some can easily be added (Conference (CONF) and Division). Most of this, I think, can be generated from a pre-set form (Games Won (W), Lost (L), HOME record (W-L), ROAD (W-L), probably even STREAK). If we store the conference, we could readily determine CONF (W-L). The other fields are all calculated (percentage won (PCT), games behind (GB)). All we really need to add is conference (CONF) and Division.

 

That having been said, I looked at the ‘statistics’ page, specifically for the Cleveland Cavaliers (who are leading the league (above)). Here’s what I found:

Roster

Player	Pos	Status	Ht	Wt	DOB	Age
Anderson Varejao	Center/Forward	Active	6'11'	260	9/28/1982	27
Shaquille O'Neal	Center	Out	7'1'	325	3/6/1972	37
Jawad Williams	Forward	Active	6'9'	218	2/19/1983	27
Antawn Jamison	Forward	Active	6'9'	235	6/12/1976	33
LeBron James	Forward	Active	6'8'	250	12/30/1984	25
Leon Powe	Forward	Active	6'8'	240	1/22/1984	26
Jamario Moon	Forward	Active	6'8'	205	6/13/1980	29
J.J. Hickson	Forward	Active	6'9'	242	9/4/1988	21
Darnell Jackson	Forward	Active	6'9'	253	11/7/1985	24
Danny Green	Forward	Active	6'6'	210	6/22/1987	22
Mo Williams	Guard	Active	6'1'	195	12/19/1982	27
Sebastian Telfair	Guard	Out	6'0'	175	6/9/1985	24
Delonte West	Guard	Active	6'3'	190	7/26/1983	26
Daniel Gibson	Guard	Out	6'2'	200	2/27/1986	24
Anthony Parker	Guard/Forward	Active	6'6'	210	6/19/1975	34

 

We will need some minor  modifications to our Player Table

 

Now, in terms of individual player game statistics, here is what I found:

 

2009-2010 Cleveland Cavaliers Individual Stats

				Rebounds
PLAYER	G	PPG	APG	RPG	OFF	DEF	TOT	AST	STL	BLK	TO	PF
L.James	61	29.9	8.5	7.1	55	377	432	517	100	59	212	100
A.Jamison	47	19.7	1.3	8.7	104	305	409	61	49	11	71	131
M.Williams	50	16.1	5.0	3.1	20	133	153	252	57	11	129	133
S.O`Neal	53	12.0	1.5	6.7	93	262	355	80	15	62	105	169
A.Varejao	59	8.6	1.0	7.8	153	310	463	60	57	59	55	184
D.West	43	7.9	2.9	2.7	17	97	114	123	40	24	57	55
J.Hickson	60	7.6	0.4	4.4	68	195	263	24	24	27	62	96
A.Parker	61	7.3	1.7	2.8	19	150	169	102	43	19	53	111
D.Gibson	50	6.9	1.3	1.4	13	55	68	67	24	7	35	76
L.Powe	3	5.3	0.0	3.3	3	7	10	0	1	1	5	7
J.Moon	45	4.7	0.8	3.4	21	130	151	35	20	23	17	57
S.Telfair	39	4.3	2.9	1.1	5	36	41	114	22	5	44	65
J.Williams	35	3.8	0.7	1.3	11	35	46	26	9	3	9	46
D.Green	14	2.6	0.2	0.7	5	5	10	3	3	2	5	7
D.Jackson	24	0.8	0.1	0.8	8	12	20	3	2	4	11	15
TEAM	-	137.5	28.3	55.3	585	2001		1342	413	341	819	1252

 

	Field Goals			3-Point FG			Free Throws
PLAYER	M	A	PCT	M	A	PCT	M	A	PCT	PTS	HI
L.James	615	1222	.503	111	318	.349	483	623	.775	1824	48
A.Jamison	351	777	.452	62	187	.332	163	236	.691	927	34
M.Williams	279	639	.437	116	271	.428	129	145	.890	803	28
S.O`Neal	262	463	.566	0	1	.000	112	226	.496	636	22
A.Varejao	207	375	.552	1	5	.200	90	134	.672	505	22
J.Hickson	189	333	.568	0	0	.000	77	112	.688	455	23
A.Parker	155	350	.443	86	190	.453	52	68	.765	448	16
D.Gibson	121	257	.471	68	142	.479	34	49	.694	344	16
D.West	125	283	.442	20	54	.370	68	83	.819	338	21
J.Moon	78	175	.446	23	73	.315	31	36	.861	210	14
S.Telfair	67	166	.404	11	47	.234	24	31	.774	169	17
J.Williams	48	122	.393	20	59	.339	18	23	.783	134	17
D.Green	14	29	.483	5	15	.333	3	4	.750	36	6
D.Jackson	8	24	.333	1	3	.333	2	4	.500	19	10
L.Powe	5	10	.500	0	0	.000	6	8	.750	16	8
TEAM	2306	4724	.488	466	1173	.397	1176	1619	.726	6254	-

 

This requires data that we don’t have. In fact it will require additional table(s) (I leave it to you to figure out how many tables we will need).

 

The abbreviations used in the table are:

 

G: The number of games each player has played in

PPG: Points per game

APG: Assists per game

RPG: Rebounds per game

OFF: Total Offensive rebounds

DEF: Total Defensive rebounds

TOT: Total rebounds

AST: Total assists

STL: Total steals

BLK: Total blocks

TO: Total turnovers

PF: Total personal fouls

 

For field goals (2-pts), 3-point field goals, and free throws (1pt):

 

M: Total made

A:  Total attempted

PCT:  Percent of total made (based on total attempted)

 

PTS: The total points

HI: The most number of points in any one game.

 

NOTICE THAT NOT ALL OF THESE ARE STORED; MANY ARE CALCULATED FROM STORED DATA. YOU WILL HAVE TO FIGURE OUT WHICH ONES.

 

Just so you know (you don’t have to include these for the test; this is just for information purposes), there are additional statistics that are maintained/or calculated (from: http://www.ebawords.com/tiki-index.php?page=Statistics%20Abbreviations):

 

 

One final requirement:

 

Other statistics I have seen, but are not discussed above:

 

Who are the referees for each game?

What percentage (or how many) games were sell-outs?

 

Make sure you store the data necessary to be able to answer the above questions.

 

 

DRAW THE NEW ERD