MIS Exam #2 Part 4

What is a transitive dependency? Please illustrate with an example.

(a functional dependency between nonprimary key attributes is also called a transitive dependency). a transitive dependency is a functional dependency which holds by virtue of transitivity. A transitive dependency can occur only in a relation that has three or more attributes. Let A, B, and C designate three distinct attributes (or distinct collections of attributes) in the relation.

Functional dependency

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

Third normal form (3NF)

A relation is in second normal form and has no functional (transitive) dependencies between two (or more) nonprimary key attributes.

Referential integrity

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

Suppose that Susan Martin resigned from the organization and that we no longer wanted to keep a record of her "department" or "salary". Should we delete all fields of all records that have Susan's name in them? Or is there some information that someone in the organization (such as the corporate training department) might still want to keep?

Keep the information so corporate training department has access to the data.

Field

The smallest unit of named application data recognized by system software.

Synonym

Two different names that are used for the same attribute.

How is a foreign key represented in relational notation

SALES1, and is an attribute that appears as a non primary key attribute in one relation, and as a primary key attribute in another relation, designate foreign key by using a dashed underline. So like Sales_Person

Pointer

A field of data that can be used to locate a related field or row of data.

Calculated field

A field that can be derived from other database fields. Also known as a computed field or a derived field.

Indexed file organization

A file organization in which rows are stored either sequentially or nonsequentially, and an index is created that allows software to locate individual rows.

Sequential file organization

A file organization in which rows in a file are stored in sequence according to a primary key value.

Hashed file organization

A file organization in which the address of each row is determined using an algorithm.

Recursive foreign key

A foreign key in a relation that references the primary key values of that same relation.

Physical table

A named set of rows and columns that specifies the fields in each row of the table.

Physical file

A named set of table rows stored in a contiguous section of secondary memory.

Relation

A named, two-dimensional table of data. Each relation consists of a set of named columns and an arbitrary number of unnamed rows.

In the context of a relational database model, what is a "relation"?

A named, two-dimensional table of data. Each relation consists of a set of named columns and an arbitrary number of unnamed rows. Each column in a relation corresponds to an attribute of that relation Each row of a relation corresponds to a record that contains data values for an entity.

What does it mean for a relation to be in third normal form?

A relation is in second normal form and has no functional (transitive) dependencies between two (or more) nonprimary key attributes. SALES (Customer_ID, Customer_ Name,Salesperson,Region) The following functional dependencies exist in the SALES relation: Customer_ID —> Customer_Name, Salesperson, Region (Customer_ID is the primary key) Saleperson —> Region

Second normal form

A relation is in second normal form if every nonprimary key attribute is functionally dependent on the whole primary key.

What does it mean for a relation to be in second normal form?

A relation is in second normal form if every nonprimary key attribute is functionally dependent on the whole primary key. Example EMPLOYEE2(Emp_ID,Name,Dept,Salary,Course,Date_Completed) Functional dependencies EMP_ID —> Name, Dept, Salary EMP_ID,Course —> Date_Completed

Well-structured relation

A relation that contains a minimum amount of redundancy and that allows users to insert, modify, and delete the rows without error or inconsistencies; also known as a table.

Null value

A special field value, distinct from zero, blank, or any other value, that indicates that the value for the field is missing or otherwise unknown.

Default value

A value a field will assume unless an explicit value is entered for that field.

What is a "foreign key"? Please illustrate with an example.

An attribute that appears as a nonprimary key attribute in one relation and as a primary key attribute (or part of a primary key) in another relation. You designate a foreign key by using a dashed underline.

Secondary key

One or a combination of fields for which more than one row may have the same combination of values.

How are the ER diagrams (i.e., "conceptual data models") -- which are generated during the Analysis stage -- used for database design? Please illustrate with an example.

The primary deliverable from the conceptual data modeling step within the analysis phase is an E-R diagram, similar to the one shown in Figure 8-3. This figure shows the major categories of data (rectangles on the diagram) and the business relationships between them (lines connecting rectangles). For example, Figure 8-3 shows that, for the business represented by this diagram, a SUPPLIER sometimes Supplies ITEMs to the company, and an ITEM is always Supplied by one to four SUPPLIERS. The fact that a supplier only sometimes supplies items implies that the business wants to keep track of some suppliers without designating what they can supply. This diagram includes two names on each line so that a relationship can be read in each direction. For simplicity, we will not typically include two names on lines in E-R diagrams in this book; however, this is a standard used in many organizations.

What is the purpose of normalization

The process of converting complex data structures into simple, stable data structures. Principles and rules Second normal form, third normal form Result of normalization is that every nonprimary key attribute depends upon the whole primary key and nothing but the primary key.

Denormalization

The process of splitting or combining normalized relations into physical tables based on affinity of use of rows and fields.

The Employee1 relation of Figure 9-5

This is a well structured relation, since each row of the table contains data describing one employee, and any modification to an employee's data (such as a change in salary) is confined to one row of the table.

The Employee2 relation of Figure 9-6 (318)

This is not a well-structured relation, however. If you examine the sample data in the table, you notice a considerable amount of redundancy. For example, the Emp_ID, Name, Dept, and Salary values appear in two separate rows for employees 100, 110, and 150. Consequently, if the salary for employee 100 changes, we must record this fact in two rows (or more, for some employees). The problem with this relation is that it contains data about two entities: EMPLOYEE and COURSE.

Suppose that you wanted to modify the "department" field for Susan Martin (e.g., Susan transfers to the Info Systems department). How many records in the table would you have to modify? Would it be better if the relation that contains information about employees was designed so that only one record would need to be modified when this type of modification was desired?

Two fields. I don't think it matters

What type of shorthand notation is used to represent a relation? How would you use the shorthand to represent the relation shown in Figure 9-6 of the text?

You can express the structure of a relation with a shorthand notation in which the name of the relation is followed (in parentheses) by the names of the attributes in the relation. The identifier attribute (called the primary key of the relation) is under- lined. For example, you would express EMPLOYEE1 as follows: EMPLOYEE1(Emp_ID,Name,Dept,Salary)

What are the steps for database design?

1. Develop a logical data model for each known user interface (form and report) for the application using normalization principles. 2. Combine normalized data requirements from all user interfaces into one consolidated logical database model; this step is called view integration. 3. Translate the conceptual E-R data model for the application or enterprise, developed without explicit consideration of specific user interfaces, into normalized data requirements. 4. Compare the consolidated logical database design with the translated E-R model and produce, through view integration, one final logical database model for the application.

List five properties of relations

1. Entries in cells are simple. An entry at the intersection of each row and column has a single value. 2. Entries in a given column are from the same set of values. 3. Each row is unique. Uniqueness is guaranteed because the relation has a non- empty primary key value. 4. The sequence of columns can be interchanged without changing the meaning or use of the relation. 5. The rows may be interchanged or stored in any sequences.

Data Type

A coding scheme recognized by system software for representing organizational data.

What is a functional dependency? Illustrate with an example.

A constraint between two attributes in which the value of one attribute is determined by the value of another attribute. In a given relation, attribute B is functionally dependent on attribute A if, for every valid value of A, that value of A uniquely determines the value of B (Date, 2012; Hoffer et al., 2016). The functional dependence of B on A is represented by an arrow, as follows: A S B (e.g., Emp_ID S Name in the relation of Figure 9-5) An attribute may be functionally dependent on two (or more) attributes rather than on a single attribute. For example, consider the relation EMP COURSE (Emp_ID,Course,Date_Completed) shown in Figure 9-7. We represent the functional dependency in this relation as follows: Emp_ID,Course S Date_Completed (this is sometimes shown as Emp_ID + Course S Date_Completed). In this case, Date_Completed cannot be determined by either Emp_ID or Course alone because Date_Completed is a characteristic of an employee taking a course.

Homoym

A single attribute name that is used for two or more different attributes.

Index

A table used to determine the location of rows in a file that satisfy some condition.

File organization

A technique for physically arranging the records of a file.

Primary key

An attribute (or combination of attributes) whose value is unique across all occurrences of a relation.

Foreign key

An attribute that appears as a nonprimary key attribute in one relation and as a primary key attribute (or part of a primary key) in another relation.

Relational database model

Data represented as a set of related tables or relations.

What is a relational database model?

Data represented as a set of related tables or relations.

Why is normalization done? Please illustrate with an example.

The process of converting complex data structures into simple, stable data structures

Normalization

The process of converting complex data structures into simple, stable data structures.

Can instances of a relation (sample data) prove the existence of a functional dependency? Why or why not

You should be aware that the instances (or sample data) in a relation do not prove that a functional dependency exists. Only knowledge of the problem domain, obtained from a thorough requirements analysis, is a reliable method for identifying a functional dependency. However, you can use sample data to demonstrate that a functional dependency does not exist between two or more attributes. For example, consider the sample data in the relation EXAMPLE(A,B,C,D), shown in Figure 9-8. The sample data in this relation prove that attribute B is not functionally dependent on attribute A because A does not uniquely determine B (two rows with the same value of A have different values of B).