Module #4: Entity Relationship (ER) Modeling (Review)

What is a Required Attribute?

An attribute that must have a value. In other words, it cannot be left empty.

What is a Derived Attribute?

An attribute whose value is calculated (derived) from other attributes. For Example: - An employee's age, EMP_AGE, may be found by computing the integer value of the difference between the current date and the EMP_DOB.

What is a Weak Entity?

An entity that displays existence dependence and inherits the primary key of its parent entity. For example: A DEPENDENT requires the existence of an EMPLOYEE.

What is Existence Independence?

A property of an entity that can exist apart from one or more related entities. Such a table must be created first when referencing an existence-dependent table.

How would you classify relationships if you know only one side of the relationship?

A DIVISION is managed by one EMPLOYEE. - You don't know if the relationship is 1:1 or 1:M. Therefore, you should ask the question "Can an employee manage more than one division?" If the answer is yes, the relationship is 1:M, and the second part of the relationship is then written as: An EMPLOYEE may manage many DIVISIONs - If an employee cannot manage more than one division, the relationship is 1:1, and the second part of the relationship is then written as: An EMPLOYEE may manage only one DIVISION.

What is Existence Dependence?

A property of an entity whose existence depends on one or more other entities. In such an environment, the existence-independent table must be created and loaded first because the existence-dependent key cannot reference a table that does not yet exist. For Example:

What is Cardinality?

A property that assigns a specific value to connectivity and expresses the range of allowed entity occurrences associated with a single occurrence of the related entity. For Example: - A classroom has can have a minimum of zero students and a maximum of 30 students, the cardinality would be expressed under the relationship line as (0, 30).

What is a Weak (Non-Identifying) Relationship?

A relationship in which the primary key of the related entity does not contain a primary key component of the parent entity. For Example: - Suppose the 1:M relationship between COURSE and CLASS is defined as: COURSE (CRS_CODE, DEPT_CODE, CRS_DESCRIPTION, CRS_CREDIT) CLASS (CLASS_CODE, CRS_CODE, CLASS_SECTION, CLASS_TIME, ROOM_CODE, PROF_NUM) In this example, the CLASS primary key did not inherit a primary key component from the COURSE entity. In this case, a weak relationship exists between COURSE and CLASS because CRS_CODE (the primary key of the parent entity) is only a foreign key in the CLASS entity.

What is a Strong (Identifying) Relationship?

A relationship that occurs when two entities are existence-dependent; from a database design perspective, this relationship exists whenever the primary key of the related entity contains the primary key of the parent entity.

What are Participants?

An ER term for entities that participate in a relationship. For Example: - In the relationship "PROFESSOR teaches CLASS," the teaches relationship is based on the participant's PROFESSOR and CLASS.

What does an Entity correspond to in Database Terms?

An Entity corresponds to a table, not a column or row.

What is a Relationship is Database terms?

An association between entities. For Example: -

What is a Composite Attribute?

An attribute that can be further subdivided to yield additional attributes. Example: A phone number such as 615-898-2368 may be divided into an area code (615), an exchange number (898), and a four-digit code (2368).

What is a Multi-Valued Attribute?

An attribute that can have many values for a single entity occurrence. For Example: - An EMP_DEGREE attribute might store the string "BBA, MBA, Ph.D." to indicate three different degrees held. - A car's color may be subdivided into many colors for the roof, body, and trim.

What is a Single-Value Attribute?

An attribute that can have only one value. For Example: A person can have only one Social Security number, and a manufactured part can have only one serial number.

What is a Simple Attribute?

An attribute that cannot be subdivided into meaningful components. Compare to composite attribute. Example: Age, Sex, and Marital Status would be classified as simple attributes.

What is an optional Attribute?

An attribute that does not require a value; therefore, it can be left empty.

What is a Strong Entity?

An entity that is existence-independent, that is, it can exist apart from all of its related entities. Also called a regular entity. For Example: - Suppose that the XYZ Corporation uses parts to produce its products. Furthermore, suppose that some of those parts are produced in-house and other parts are bought from vendors. In that scenario, it is quite possible for a PART to exist independently from a VENDOR in the relationship "PART is supplied by VENDOR" because at least some of the parts are not supplied by a vendor. Therefore, PART is existence-independent from VENDOR.

What are Attributes?

Attributes are characteristics of entities. Example: A table (Entity) labeled STUDENT includes the attributes STU_LNAME, STU_FNAME, AND STU_INITIAL.

How do you show a Weak Relationship in Crows Foot Notation?

By making the relationship line dashed.

How do you show a Strong Relationship in Crows Foot Notation?

By making the relationship line solid.

How is Cardinality indicated?

By placing the appropriate numbers beside the entities, using the format (x, y). The first value represents the minimum number of associated entities, while the second value represents the maximum number of associated entities.

What is Optional Participation?

In ER modeling, a condition in which one entity occurrence does not require a corresponding entity occurrence in a particular relationship.

What is a Composite Identifier?

In ER modeling, a key composed of more than one attribute. Example:

What is an Identifier?

One or more attributes that uniquely identify each entity instance. Example:

What are the disadvantages of story Derived Attributes?

Stored - Requires constant maintenance to ensure derived value is current, especially if any values used in the calculation change. Not Stored - Uses CPU processing cycles - Increases data access time - Adds coding complexity to queries

What are the advantages of storing Derived Attributes?

Stored - Saves CPU processing cycles - Saves data access time - Data value is readily available - Can be used to keep track of historical data Not Stored - Saves storage space - Computation always yields current value

What determines whether a relationship between two tables (entities) is strong or weak?

The Primary Key (PK) of the table that comes after the first table is what dictates whether a relationship is strong or weak.

What is Connectivity?

The Relationship Classification. A Connectivity is basically the classification of lines that connect tables, i.e. 1:1, 1:M, and M:M. For Example: -

What is a Relational Schema?

The organization of a relational database as described by the database administrator. Example:

What is a Domain?

The set of possible values for a given attribute. Example: The domain for a grade point average (GPA) attribute is written (0,4) because the lowest possible GPA value is 0 and the highest possible value is 4.

Should a Database Designer always use Composite Attributes?

Yes, A Database Designer should always use composite attributes because it helps simplify policies and information. For Example: A user at Tiny College might need to know a student's name, address, and phone number. The designer must recognize that these are composite attributes and determine the correct way to decompose the composite into simple attributes.