Exam 2 Chapter 5 and Lecture Unit 6: Logical Data Modeling Review Questions
What problems may arise in view integration or merging relations?
1) Synonyms, 2) Homonyms 3) Transitive dependencies 4) Supertype/subtype relationships
What is a functional dependency? What notation indicates a functional dependency?
A constraint between two attributes (or two sets of attributes) in which the value of one attribute is determined by the value of another attribute. A functional dependency occurs when one attribute in a relation uniquely determines another attribute. This can be written A -> B which would be the same as stating "B is functionally dependent upon A."
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 (p. 141)
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 a composite primary key?
A key made up of two or more attributes within a table that together uniquely identify a record
What is the purpose of a 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.
What is a relation?
A set of tuples that have the same attributes (p. 140)
What does a primary key uniquely identify?
A tuple within a table (p. 142)
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 is the term for a group of one or more attributes that uniquely identifies a tuple?
Candidate Key
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)?
Composite key/determinate
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?
Dependent Entity
What is an example of an update anomaly?
Each record in an "Employees' Skills" table contains an EmployeeID, Employee Address, and Skill; thus a change of address for a particular employee will potentially need to be applied to multiple records (one for each skill). If the update is not carried through successfully then the table is in an inconsistent state (p. 125)
What researcher defined the relational data model?
Edgar Codd (p. 139)
What rule guarantees that every primary key attribute is not null?
Entity integrity
In what normal form have all multivalued attributes been removed?
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 are examples of functional dependencies?
In an "Employee" table that includes the attributes "EmployeeID" and "EmployeeBirthday", the functional dependency {EmployeeID}→{EmployeeBirthday} (p. 128)
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 attribute or combination of attributes uniquely identifies a row in a relation?
Primary key
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 rule states that a foreign key must either match a primary key value in another relation or it must be null?
Referential integrity constraints
What type of integrity constraint maintains consistency among tuples in two relations?
Referential integrity constraints
What is a synonym for tuple in a relational database?
Row
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?
Second normal form
In the Logical Data model, what is another name for a regular, independent entity?
Strong entity (p. 136)
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 (p. 142)
What is an attribute that has more than one meaning called?
Synonym
What is another name for a relation?
Table
What is a determinant?
The attribute on the left hand side of the arrow in a functional dependency that identifies other attributes in a relation.
In a 1:N relationship, in which relation is the foreign key placed?
The primary key attribute of the entity on the one-side of the relationship becomes a foreign key in the relation on the many-side of the relationship (p. 136)
For most business transactional databases, what form should we normalize relations into?
Third normal form
In what normal form have any transitive dependencies been removed?
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?
Trivial functional dependency
What anomalies does normalization to third normal form help avoid?
Update, insertion, and deletion anomalies (p. 134)
What type of relation contains minimal data redundancy?
Well-structured relation
How is a functional dependency between two attributes usually represented?
X→Y
A row or tuple has a _______ schema, but an entire database has a _______ schema.
relation relational
