#2: Chapter 5

What is a functional dependency? What notation indicates a functional dependency?

A constraint between two attributes or two sets of attributes. Normal Form.

How does the domain impact the value of an attribute?

A domain describes the set of possible values for a given attribute, and can be considered a constraint on the value of the attribute

What is a foreign key? Can a relation have more than one foreign key?

A field in a relational table that matches the primary key column of another table. Yes.

What is a partial functional dependency?

A functional dependency in which one or more nonkey attributes are functionally dependent on part, but not all, of the primary key

What is the benefit to IS Professionals of understanding how to merge relations?

It's a skill you can apply to the job (it's just something you know) ← prof gave bad answer

What is the purpose and motivation for the normalization process?

Minimizes redundancy and dependency.

What is the process of decomposing relations to produce smaller, well-structured relations called?

Normalization

What is it called when the value for a non-key attribute of a relation is dependent on the value of some part of the relation's primary key, but not all of it?

Partial functional dependency

What is the term for an attribute that uniquely identifies each row in a relation?

Primary Key

What has been created when the values in one or more attributes used as a foreign key in a relation must exist as identifying attributes in another relation?

Referential Integrity Constraint

What type of integrity constraint maintains consistency among tuples in two relations?

Referential Integrity Constraint

What is the term for a group of one or more attributes that uniquely identifies a tuple?

Relation

A row or tuple has a _______ schema, but an entire database has a _______ schema.

Relation; relational

What is a synonym for tuple in a relational database?

Row

A relation that has no multivalued attributes, but does have partial functional or transitive dependencies is in what normal form?

Second Normal Form

In the Logical Data model, what is another name for a regular, independent entity?

Strong Entity

Which type of entity has its relationship to another entity determined by an attribute in that other entity called a discriminator?

Subtype Entity

What is a unique, DBMS-supplied identifier used as the primary key of a relation called?

Surrogate Key

What is another name for a relation?

Table

How are PK and FK indicated in a tabular LDM model?

Tabular is a table - usually we put PK or FK in parenthesis behind it

What is a determinant?

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

What is a composite primary key?

A key made up of two or more attributes within a table that (together) uniquely identify a record

What is a relation?

A named, two-dimensional table of data

What does a primary key uniquely identify?

The relation in question

In what normal form have any transitive dependencies been removed?

Third Normal Form

For most business transactional databases, what form should we normalize relations into?

Third Normal form

If attribute A determines attribute B, and attribute B determines attribute C, then what type of dependency exists between B and C? Is this the same as (A,B) --> C?

Transitive Dependency

What attribute is required in a relation?

Tuple

What attribute or combination of attributes uniquely identifies a row in a relation?

Tuple

What conditions must a candidate key satisfy?

Uniquely identify the row Nonredundant Each nonkey attribute is functionally dependent One of the primary key

What anomalies does normalization to third normal form help avoid?

Update anomalies

Which type of entity cannot exist in the database unless another type of entity also exists in the database, but does not require that the identifier of that other entity be included as part of its own identifier?

Weak/Dependent Entity

What do we call a relation that contains minimal redundancy and allows users to insert, modify, and delete the rows in a table without errors or inconsistencies?

Well-structured relation

What type of relation contains minimal data redundancy?

Well-structured relation

How is a functional dependency between two attributes usually represented?

X --> Y

Is the term relation derived from relationships identified in an ERD model?

Yes

What are typical problem that often arises in merging or combining relations?

- Synonyms: Two (or more) attributes in ER models have different names but the same meaning - Homonyms: A single attribute has more than one meaning - Transitive Dependency: Merging relations produces transitive dependencies - Supertype/Subtype: May be implied by content of existing relations

What conditions suggest a surrogate key should be created for the primary key of a relation?

-Presence of a composite primary key -Natural primary key (one initially identified by users during conceptual modeling activities) is too long or made up of more than two attributes which causes performance issues with database software -Natural primary key cannot be guaranteed to be unique over time (e.g., due to duplicates or re-use over time)

What problems may arise in view integration or merging relations?

1. Synonyms 2. Homonyms 3. Transitive dependencies 4. Supertype/subtype relationships

What properties make a table a relation?

1. Table must have a unique name 2. Every attribute value must be atomic 3. Every row must be unique 4. Attributes in tables must have unique names 5. Order of columns is irrelevant 6. Order of rows is irrelevant

What ERD term corresponds to a column in the relation?

Attribute

If a relation has been normalized so that all determinants are candidate keys, then what is the normal form of the relation?

Boyce-Codd Normal Form

What are two common ways of expressing or documenting a logical schema?

Bubble diagrams, information engineering (crows foot), reduced set/reduced form notation

In a 1:N relationship, in which relation is the foreign key placed?

Child

If attributes A and B determine attribute C, then what is (A, B)?

Composite key/determinate

If the attributes OrderNumber and ProductNumber determine QtyOrdered, then what is (OrderNumber, ProductNumber)?

Compound (Primary) key/determinate

What is the purpose of a referential integrity constraint?

Constraints make it possible to further restrict the domain of an attribute

What is an example of an update anomaly?

Each record in an "Employee's Skills" table might contain an ID, Address, and Skill; thus a change of address for a particular employee will potentially need to be applied to multiple records. If the update is not carried through successfully, if that is, the employee's address is updated on some records but not others, then the table is left in an inconsistent state. Specifically, the table provides conflicting answers to the question of what this particular employee's address is.

What researcher defined the relational data model?

Edgar Codd

What RDBMS term is used for a key value that is unique across all relationships in a database?

Enterprise Key

What rule guarantees that every primary key attribute is not null?

Entity Integrity

What rule states that a foreign key must either match a primary key value in another relation or it must be null?

Entity Integrity Rule

In what normal form have all multivalued attributes been removed?

First Normal Form

In which normal form is a relation that contains no multivalued attributes, and has nonkey attributes solely dependent on the primary key, but contains transitive dependencies?

First Normal Form

In the relational model, how are relationships between relations created?

Foreign Key

What is the term for an attribute in a relation that serves as a primary key of another relation in the same database/schema?

Foreign Key

What is a constraint between two sets of attributes in a relation called?

Functional Dependency

What is an attribute that has more than one meaning called?

Homonym

What are examples of functional dependencies?

when A is the primary key of an entity (eg. SID) and B is some single valued attribute of the entity (eg. Sname). Then, A → B must always hold.