Database Management Exam 1

How to know to convert a relationship into an A/E

1. All the relationships for the participating entity types are "many" relationships. 2. The resulting associative entity type has independent meaning to end users and, preferably, can be identified with a single-attribute identifier. 3. The associative entity has one or more attributes in addition to the identifier. 4. The associative entity participates in one or more relationships independent of the entities related in the associated relationship.

Relational data model

1. Data structure Data are organized in the form of tables, with rows and columns. 2. Data manipulation Powerful operations (typically implemented using the SQL language) are used to manipulate data stored in the relations. 3. Data integrity The model includes mechanisms to specify business rules that maintain the integrity of data when they are manipulated.

1st Normalization

Any multivalued attributes (also called repeating groups) have been removed, so there is a single value (possibly null) at the intersection of each row and column of the table (like Fig 4-2b)

3rd Normalization

Any transitive dependencies have been removed (i.e., nonkey attributes are identified by only the primary key).

Functional Dependencies

A constraint between two attributes in which the value of one attribute is determined by the value of another attribute

transitive dependency

A functional dependency between the primary key and one or more non-key attributes that are dependent on the primary key via another non-key attribute.

Partial functional dependency

A functional dependency in which one or more non-key attributes are functionally dependent on part (but not all) of the primary key.

Relation

A named two-dimensional table of data.

Composite Key

A primary key that consists of more than one attribute.

1st normal form

A relation that has a primary key and in which there are no repeating groups. 1. There are no repeating groups in the relation (thus, there is a single fact at the intersection of each row and column of the table). 2. A primary key has been defined, which uniquely identifies each row in the relation

referential integrity constraint

A rule that states that either each foreign key value must match a primary key value in another relation or the foreign key value must be null.

entity integrity rule

A rule that states that no primary key attribute (or component of a primary key attribute) may be null

Anomaly

An error or inconsistency that may result when a user attempts to update a table that contains REDUNDANT DATA. The three types of anomalies are insertion, deletion, and modification anomalies.

When to convert to A/E

Convert all ternary (or higher) relationships to associative entities

Root of all evil?

Data Redundancy

Software Development Life Cycle (SDLC) (Waterfall)

Detailed, well-planned development process Time-consuming, but comprehensive Long development cycle

Determinant

The attribute on the left side of the arrow in a functional dependency.

Normalization

The process of decomposing relations with anomalies to produce smaller, well-structured relations.

Identifier & Composite Identifier

attribute (or combination of attributes) whose value distinguishes instances of an entity type.

Simple Attribute

attribute that cannot be broken down into smaller components that are meaningful to the organization.

associate entity

entity type that associates the instances of one or more entity types and contains attributes that are unique to the relationship between those entity instances.

weak entity

entity type whose existence depends on some other entity type

strong entity

exists independently of other entity types.

mapping a 1:M relationship (one to many)

first create a relation for each of the two entity types participating in the relationship, using the procedure described in Step 1. Next, include the primary key attribute (or attributes) of the entity on the one-side of the relationship as a foreign key in the relation that is on the many-side of the relationship. (A mnemonic you can use to remember this rule is this: The primary key migrates to the many side.)

Attributes (Simple & Composite)

property or characteristic of an entity or relationship type that is of interest to the organization.

unary relationship

relationship between the instances of a single entity type.

Normal Form

A state of a relation that requires that certain rules regarding relationships between attributes (or functional dependencies) are satisfied.

Derived Attribute

An attribute whose values can be calculated from related attribute values.

Candidate Key

An attribute, or combination of attributes, that uniquely identifies a row in a relation

2nd Normalization

Any partial functional dependencies have been removed (i.e., non-key attributes are identified by the whole primary key). Steps are required: 1. Create a new relation for each primary key attribute (or combination of attributes) that is a determinant in a partial dependency. That attribute is the primary key in the new relation. 2. Move the non-key attributes that are only dependent on this primary key attribute (or attributes) from the old relation to the new relation.

binary many to many (M:N) relationship

Between two entity types, A and B. For such a relationship, create a new relation, C. Include as foreign key attributes in C the primary key for each of the two participating entity types. These attributes together become the primary key of C. Any nonkey attributes that are associated with the M:N relationship are included with the relation C. Figure 4-13a shows the Completes the relationship between the entity types EMPLOYEE and COURSE from Figure 2-11a. Figure 4-13b shows the three relations (EMPLOYEE, COURSE, and CERTIFICATE) that are formed from the entity types and the Completes relationship. If Completes had been represented as an associative entity, as is done in Figure 2-11b, a similar result would occur, but we will deal with associative entities in a subsequent section. In the case of an M:N relationship, a relation is first created for each of the two regular entity types EMPLOYEE and COURSE. Then a new relation (named CERTIFICATE in Figure 4-13b) is created for the Completes relationship. The primary key of CERTIFICATE is the combination of EmployeeID and CourseID, which are the respective primary keys of EMPLOYEE and COURSE. As indicated in the diagram, these attributes are foreign keys that "point to" the respective primary keys. The nonkey attribute DateCompleted also appears in CERTIFICATE. Although not shown here, it is often wise to create a surrogate primary key for the CERTIFICATE relation

E-R Diagram

Reflects the system's key entities and the relationships among those entities. Ex: Where is a unary relationship, what does it mean, and for what reasons might the cardinalities on it be different in other organizations? • Why is Includes a one-to many relationship, and why might this ever be different in some other organization? • Does Includes allow for a product to be represented in the database before it is assigned to a product line (e.g., while the product is in research and development)? • If there were a different customer contact person for each sales territory in which a customer did business, where in the data model would we place this person's name? • What is the meaning of the Does Business In associative entity, and why does each Does Business In instance have to be associated with exactly one SALES TERRITORY and one CUSTOMER? • In what way might Pine Valley change the way it does business that would cause the Supplies associative entity to be eliminated and the relationships around it to change?

Deletion Anomaly

Suppose that the data for employee number 140 is deleted from the table. This will result in losing the information that this employee completed a course (Tax Acc) on 12/8/2015. In fact, it results in losing the information that this course had an offering that completed on that date

Insertion Anomaly

Suppose that we need to add a new employee to EMPLOYEE2. The primary key for this relation is the combination of EmpID and CourseTitle (as noted earlier). Therefore, to insert a new row, the user must supply values for both EmpID and CourseTitle (because primary key values cannot be null or nonexistent). This is an anomaly because the user should be able to enter employee data without supplying course data.

binary relationship

relationship between the instances of two entity types and is the most common type of relationship encountered in data modeling

Requirements for a relation?

1. Each relation (or table) in a database has a unique name. 2. An entry at the intersection of each row and column is atomic (or single valued). There can be only one value associated with each attribute on a specific row of a table; no multivalued attributes are allowed in a relation. 3. Each row is unique; no two rows in a relation can be identical. 4. Each attribute (or column) within a table has a unique name. 5. The sequence of columns (left to right) is insignificant. The order of the columns in a relation can be changed without changing the meaning or use of the relation. 6. The sequence of rows (top to bottom) is insignificant. As with columns, the order of the rows of a relation may be changed or stored in any sequence.

When to convert to A/E

1. M:N relationship to A/E 2. A relationship with its own attribute (it has its own life) to A/E 3. Ternary (or higher level) relationship to A/E 4. Multi-Valued Attributes (e.g., Time-Stamped)

surrogate primary key

A serial number or other system assigned primary key for a relation. Created when: There is a composite primary key, as in the case of the DEPENDENT relation shown previously with the four-component primary key. • The natural primary key (i.e., the key used in the organization and recognized in conceptual data modeling as the identifier) is inefficient. For example, it may be very long and hence costly for database software to handle if it is used as a foreign key that references other tables. • The natural primary key is recycled (i.e., the key is reused or repeated periodically, so it may not actually be unique over time); a more general statement of this condition is when the natural primary key cannot, in fact, be guaranteed to be unique over time (e.g., there could be duplicates, such as with names or titles).

Foreign Key

An attribute in a relation that serves as the primary key of another relation in the same database.

Primary Key

An attribute or a combination of attributes that uniquely identifies each row in a relation

Composite Attribute

An attribute that has meaningful component parts (attributes).

Multivalued Attribute

An attribute that may take on more than one value for a given entity (or relationship) instance.

Binary one-to-one relationships

First, two relations are created, one for each of the participating entity types. Second, the primary key of one of the relations is included as a foreign key in the other relation.

Modification Anomaly

Suppose that employee number 100 gets a salary increase. We must record the increase in each of the rows for that employee (two occurrences in Figure 4-2); otherwise, the data will be inconsistent.

External

This is the view (or views) of managers and other employees who are the database users.

Conceptual

This schema combines the different external views into a single, coherent, and comprehensive definition of the enterprise's data. The conceptual schema represents the view of the data architect or data administrator.

transitive dependency

Three-step procedure: 1. For each nonkey attribute (or set of attributes) that is a determinant in a relation, create a new relation. That attribute (or set of attributes) becomes the primary key of the new relation. 2. Move all of the attributes that are functionally dependent only on the primary key of the new relation from the old to the new relation. 3. Leave the attribute that serves as a primary key in the new relation in the old relation to serve as a foreign key that allows you to associate the two relations

1NF

When forming a primary key, you must be careful not to include redundant (and therefore unnecessary) attributes. Notice that the only attribute that depends on the full key is OrderedQuantity. All of the other functional dependencies are either partial dependencies or transitive dependencies (both are defined next).

Internal

consists of two separate schemas: a logical schema and a physical schema. The logical schema is the representation of data for a type of data management technology(e.g., relational). The physical schema describes how data are to be represented and stored in secondary storage using a particular DBMS (e.g., Oracle).

Oracle (Database) Advantages

database management system is a software system that enables the use of a database approach. The primary purpose of a DBMS is to provide a systematic method of creating, updating, storing, and retrieving the data stored in a database.

entity

describes a person, a place, an object, an event, or a concept in the business environment for which information must be recorded and retained.

ternary relationship

simultaneous relationship among the instances of three entity types. (convert to a/e)