Given the business rule an employee may have many degrees

1. What two conditions must be met before an entity can be classified as a weak entity? Give an example of a weak entity.

The Two conditions that must be met to be classified as a weak entity are under :

1. The entity must be associated with another entity ,called identifying or owner or parent entity. That it is existence dependent on parent entity.

2. The entity must inherit at least part of its primary key from its parent entity.Only than it would be a valid and strong primary key.

CRS_NAME

CRS_CODE

CONNECT

CLASSNAME

CLASS_SECTION

Class

Course

For example, the (strong) relationship depicted in the above Figure shows a weak CLASS entity:

1. CLASS is clearly existence-dependent on COURSE. (You can’t have a database class unless a database course exists.)

2. The CLASS entity’s PK is defined through the combination of CLASS_SECTION and CRS_CODE. The CRS_CODE attribute is also the PK of COURSE.

The conditions which define a weak entity are similar to the strong relationship between an entity and its parent. In short, you can say, the existence of a weak entity produces a strong relationship. And if the entity is strong, its relationship to the other entity is weak.

Whether or not an entity to be weak it is usually dependent on the database designer’s decisions. For instance, if the database designer had decided to use a single-attribute as shown in the text’s Figure below., the CLASS entity would be strong. (The CLASS entity’s PK is CLASS_CODE, which is not derived from the COURSE entity.) In this case, the relationship between COURSE and CLASS is weak. However, regardless of how the designer classifies the relationship – weak or strong – CLASS is always existence-dependent on COURSE.

CONNECT

CLASS_CODE

Class

CRS_NAM E

Course

CRS_CODE

cr

2. What is a strong (or identifying) relationship, and how is it depicted in a Crow’s Foot ERD?

A strong relationship exists / occurs when an entity is existence-dependent on another entity and inherits at least part of its primary key from that entity. The Visio Professional software shows the strong relationship as a solid line. In other words, a strong relationship exists when a weak entity is related to its parent entity. As discussed in the above question Class , the weak entity is related to strong entity Course with the help of a relationship called CONNECT which is a showing /identifying a strong relationship. As shown in the above figure a strong relationship is represented by two decision boxes… One inside another embedded together represent this.

3. Given the business rule “an employee may have many degrees,” discuss its effect on attributes, entities, and relationships. (Hint: Remember what a multivalued attribute is and how it might be implemented.)

Suppose that an employee has the following degrees: BBA, Ph.D , and MBA. These degrees could be stored in a single string as a multivalued attribute named EMP_DEGREE in an EMPLOYEE table such as the one shown next:

EMP_NUM

EMP_LNAME

EMP_DEGREE

123

Carter

BS, BBA

124

O’Shanski

BBA, MBA, Ph.D.

125

Jones

BS

126

Ortez

BS, MS

Although the preceding solution has no obvious design flaws, but it is likely to yield reporting problems. For example, suppose one want to get a count for all employees who have BBA degrees. He could, of course, can do an “in-string” search to find all of the BBA values within the EMP_DEGREE strings. But such a solution is cumbersome i.e. time consuming from a reporting point of view. Query simplicity is a valuable thing for application developers – and to end users who like maximum query execution speeds. Database designers have to pay some attention to the competing database interests that exist in the data environment by all.

One – very poor and old – solution is to create a field for each expected value. This “solution is shown next:

EMP_NUM

EMP_LNAME

EMP_DEGREE1

EMP_DEGREE2

EMP_DEGREE3

123

Carter

AA

BBA

124

O’Shanski

BBA

MBA

Ph.D.

125

Jones

AS

126

Ortez

BS

MS

But this “solution yields nulls for all employees who have fewer than three degrees. And if even one employee earns a fourth degree, the table structure must be altered to accommodate the new data value. (One piece of evidence of poor design is the need to alter table structures in response to the need to add data of an existing type.) In addition, the query simplicity is not enhanced by the fact that any degree can be listed in any column. For example, a BA degree might be listed in the second column, after an “associate of arts (AA) degree has been entered in EMP_DEGREE1. One might simplify the query environment by creating a set of attributes that define the data entry, thus producing the following results:

EMP_NUM

EMP_LNAME

EMP_AA

EMP_AS

EMP_BA

EMP_BS

EMP_BBA

EMP_MS

EMP_MBA

EMP_PhD

123

Carter

X

X

124

O’Shanski

X

X

X

125

Jones

X

126

Ortez

X

X

This “solution” clearly proliferates the nulls at an ever-increasing pace. So not a good solution for the database designer .

The only reasonable solution is to create a new DEGREE entity that stores each degree in a separate record, this producing the following tables. (There is a 1:M relationship between EMPLOYEE and DEGREE). Note that the EMP_NUM can occur more than once in the DEGREE table.

The DEGREE table’s PK is EMP_NUM + DEGREE_CODE. This solution also makes it possible to record the date on which the degree was earned, the institution from which it was earned, and so on.

Table name: EMPLOYEE

EMP_NUM

EMP_LNAME

123

Carter

124

O’Shanski

125

Jones

126

Ortez

Table name: DEGREE

EMP_NUM

DEGREE_CODE

DEGREE_DATE

DEGREE_PLACE

123

AA

May-1999

Lake Sumter CC

123

BBA

Aug-2004

U. of Georgia

124

BBA

Dec-1990

U. of Toledo

124

MBA

May-2001

U. of Michigan

124

Ph.D.

Dec-2005

U. of Tennessee

125

AS

Aug-2002

Valdosta State

126

BS

Dec-1989

U. of Missouri

126

MS

May-2002

U. of Florida

Note that this solution leaves no nulls, produces a simple query environment, and makes it unnecessary to alter the table structure when employees earn additional degrees. Thus making a new table is a good and result giving and less time consuming solution.

4. What is a composite entity, and when is it used?

A composite entity is generally used to transform M:N relationships into 1:M relationships. A composite entity, also known as a bridge entity, is one that has a primary key composed of multiple attributes. The PK attributes are inherited from the entities that it relates to one another. A composite entity is defined by the fact that at least one of the PK attributes is also a foreign key.

For instance,consider the above question.Here we have created DEGREE table.In this the primary key attributes are EMP_NUM and DEGREE_CODE which is an example of composite entity.

5. Suppose you are working within the framework of the conceptual model in Figure Q4.5.

Figure Q4.5 The Conceptual Model for Question 5

FigQ4-05-The-Conceptual-Model-for-Question-5

Given the conceptual model in Figure Q4.5:

a. Write the business rules that are reflected in it.

In this case, the business rules are derived from the ERD in a “reverse-engineering” procedure designed to document the database design. In a real world database design situation, the ERD is generated on the basis of business rules that are written before the first entity box is drawn.

Following business rules can be identified :

1. A customer can own many cars.

2. Some customers do not own cars.

3. A car is owned by one and only one customer.

4. A car may generate one or more maintenance records.

5. Each maintenance record is generated by one and only one car.

6. Some cars have not (yet) generated a maintenance procedure.

7. Each maintenance procedure can use many parts.

(Comment: A maintenance procedure may include multiple maintenance actions, each one of which may or may not use parts. For example, 10,000-mile check may include the installation of a new oil filter and a new air filter. But tightening an alternator belt does not require a part.)

8. A part may be used in many maintenance records.

(Comment: Each time an oil change is made, an oil filter is used. Therefore, many oil filters may be used during some period of time. Naturally, you are not using the same oil filter each time – but the part classified as “oil filter” shows up in many maintenance records as time passes.)

Note that the apparent M:N relationship between MAINTENANCE and PART has been resolved through the use of the composite entity named MAINT_LINE. The MAINT_LINE entity ensures that the M:N relationship between MAINTENANCE and PART has been broken up to produce the two 1:M relationships shown in business rules 9 and 10.

9. Each maintenance procedure generates one or more maintenance lines.

10. Each part may appear in many maintenance lines.

Use the following two tables to show some sample data entries. For example, take a look at the (simplified) contents of the following MAINTENANCE and LINE tables and note that the MAINT_NUM 10001 occurs three times in the LINE table:

Sample MAINTENANCE Table Data

MAINT_NUM

MAINT_DATE

10001

15-Mar-2008

10002

15-Mar-2008

10003

16-Mar-2008

Sample LINE Table Data

MAINT_NUM

LINE_NUM

LINE_DESCRIPTION

LINE_PART

LINE_UNITS

10001

1

Replace fuel filter

FF-015

1

10001

2

Replace air filter

AF-1187

1

10001

3

Tighten alternator belt

NA

0

10002

1

Replace taillight bulbs

BU-2145

2

10003

1

Replace oil filter

OF-2113

1

10003

2

Replace air filter

AF-1187

1

b. Identify all of the cardinalities.

The Visio-generated Crow’s Foot ERD, shown in Figure Q4.5, does not show cardinalities directly. Instead, the cardinalities are implied through the Crow’s Foot symbols. You might write the cardinality (0,N) next to the MAINT_LINE entity in its relationship with the PART entity to indicate that a part might occur “N” times in the maintenance line entity or that it might never show up in the maintenance line entity. The latter case would occur if a given part has never been used in maintenance.

6. What is a recursive relationship? Given an example.

A recursive relationship exists when an entity is related to itself.That it is when an entity call or use itself in its query. For example, a COURSE may be a prerequisite to a COURSE. Than it would be recursive relationship.

7. How would you (graphically) identify each of the following ERM components in a Crow’s Foot model?

The answers to questions (a) through (d) are illustrated with the help of Figure Q4.7.

FIGURE Q4.7 Crow’s Foot ERM Components

a. an entity

An entity is represented by a rectangle containing the entity name. (Remember that, in ER modeling, the word "entity" actually refers to the entity set.)

The Crow’s Foot ERD – as represented in Visio Professional – does not distinguish among the various entity types such as weak entities and composite entities. Instead, the Crow’s Foot ERD uses relationship types – strong or weak – to indicate the nature of the relationships between entities. For example, a strong relationship indicates the existence of a weak entity.

A composite entity is defined by the fact that at least one of the PK attributes is also a foreign key. Therefore, the Visio Crow’s Foot ERD’s composite and weak entities are not differentiated – whether or not an entity is weak or composite depends on the definition of the business rule(s) that describe the relationships. In any case, two conditions must be met before an entity can be classified as weak:

1. The entity must be existence-dependent on its parent entity

2. The entity must inherit at least part of its primary key from its parent entity.

b. the cardinality (0,N)

Cardinalities are implied through the use of Crow’s Foot symbols. For example, note the implied (0,N) cardinality in Figure.

c. a weak relationship

A weak relationship exists when the PK of the related entity does not contain at least one of the PK attributes of the parent entity. For example, if the PK of a COURSE entity is CRS_CODE and the PK of the related CLASS entity is CLASS_CODE, the relationship between COURSE and CLASS is weak. (Note that the CLASS PK does not include the CRS_CODE attribute.) A weak relationship is indicated by a dashed line in the (Visio) ERD.

d. a strong relationship

A strong relationship exists when the PK of the related entity contains at least one of the PK attributes of the parent entity. For example, if the PK of a COURSE entity is CRS_CODE and the PK of the related CLASS entity is CRS_CODE + CLASS_SECTION, the relationship between COURSE and CLASS is strong. (Note that the CLASS PK includes the CRS_CODE attribute.) A strong relationship is indicated by a solid line in the (Visio) ERD.

8. Write the ten cardinalities that are appropriate for this ERD.

The cardinalities are indicated in Figure Q4.17sol.

FIGURE Q4.17sol The Cardinalities

FigQ4-17-The-Cardinalities-for-Que-7

9. Write the business rules reflected in this ERD.

The following business rules are reflected in the ERD:

· A store may place many orders. (Note the use of “may” – which is reflected in the ORDER optionality.)

· An order must be placed by a store. (Note that STORE is mandatory to ORDER. In this ERD, the order environment apparently reflects a wholesale environment.)

· An order contains at least one order line. (Note that ORDER_LINE is mandatory to ORDER, and vice-versa.)

· Each order line is contained in one and only one order. (Discussion: Although a given item – such as a hammer – may be found in many orders, a specific hammer sold to a specific store is found in only one order.)

· Each order line has a specific product written in it.

· A product may be written in many orders. (Discussion: Many stores can order one or more specific products, but a product that is not in demand may never be sold to a store and will, therefore, not show up in any order line -- note that ORDER_LINE is optional to PRODUCT. Also, note that each order line may indicate more than one of a specific item. For example, the item may be “hammer” and the number sold may be 1 or 2, or 500. The ORDER_LINE entity would have at least the following attributes: ORDER_NUM, ORDLINE_NUM, PROD_CODE, ORDLINE_PRICE, ORDLINE_QUANTITY. The ORDER_LINE composite PK would be ORDER_NUM + ORDLINE_NUM. You might add the derived attribute ORDLINE_AMOUNT, which would be the result of multiplying ORDLINE_PRICE and ORDLINE_QUANTITY.)

· A store may employ many employees. (Discussion: A new store may not yet have any employees, yet the database may already include the new store information … location, type, and so on. If you made the EMPLOYEE entity mandatory to STORE, you would have to create an employee for that store before you had even hired one.)

· Each employee is employed by one (and only one) store.

· An employee may have one or more dependents. (Discussion: You cannot require an employee to have dependents, so DEPENDENT is optional to EMPLOYEE. Note the use of the word “may” in the relationship.)

· A dependent must be related to an employee. (Discussion: It makes no sense to keep track of dependents of people who are not even employees. Therefore, EMPLOYEE is mandatory to DEPENDENT.)

10. What two attributes must be contained in the composite entity between STORE and PRODUCT? Use proper terminology in your answer.

The composite entity must at least include the primary keys of the entities it references. The combination of these attributes may be designated to be the composite entity's (composite) primary key. Each of the (composite) primary key's attributes is a foreign key that references the entities for which the composite entity serves as a bridge.

As you discuss the model in Figure Q4.17sol, note that an order is represented by two entities, ORDER and ORDER_LINE. Note also that the STORE’s 1:M relationship with ORDER and the ORDER’s 1:M relationship with ORDER_LINE reflect the conceptual M:N relationship between STORE and PRODUCT. The original business rules probably read:

· A store can order many products

· A product can be ordered by many stores.

11. Describe precisely the composition of the DEPENDENT weak entity’s primary key. Use proper terminology in your answer.

The DEPENDENT entity will have a composite PK that includes the EMPLOYEE entity’s PK and one of its attributes. For example, if the EMPLOYEE entity’s PK is EMP_NUM, the DEPENDENT entity’s PK might be EMP_NUM + DEP_NUM.

Simplified Crow’s Foot entity box

(no attribute component.)

STUDENT

Crow’s Foot entity box

(attribute component included.)

Crow’s Foot connectivity symbol,

implied (0,N) cardinality.

A weak relationship

A strong relationship

CRS_NAME

CRS_NAME

STUDENT

STU_NUM (PK)

STU_LNAME

STU_FNAME

STU_INITIAL

DEPT_CODE (FK)

Simplified Crow’s Foot entity box

(no attribute component.)

STUDENT

Crow’s Foot entity box

(attribute component included.)

Crow’s Foot connectivity symbol,

implied (0,N) cardinality.

A weak relationship

A strong relationship