Quiz 1 Flashcards
What makes a well-structured relation?
A relation that contains minimal redundancy and allows users to insert,
modify, and delete the rows in a table without errors or inconsistencies
Insertion anomalies
An insertion anomaly is the inability to add data to the database due to the absence of other data. Are experienced when we attempt to store a value for one attribute
but cannot because the value of another attribute is unknown
Deletion anomalies
A deletion anomaly occurs when you delete a record that may contain attributes that shouldn’t be deleted.
Are experienced when a value for one attribute we wish to keep is unexpectedly removed when a value for another attribute is deleted
Modification anomalies
A modification anomaly is an unexpected side effect from trying to insert, update, or delete a row.
Are experienced when changes to multiple instances of an entity (rows of a table) are needed to effect an update to a single value of an attribute
Normalization
The process of successively reducing relations with anomalies to produce smaller, well-structured relations.
What are the objectives of normalization?
To eliminate certain kinds of data redundancy and to avoid certain anomalies
Normal Form
A state of a relation that can be determined by applying simple rules regarding functional dependencies (or relationships between attributes). Are used to eliminate or reduce redundancy in database tables
A violation of NF is when…
two (or more) different entities are combined into a single entity
What are the two constraints required for 1NF
- There are no repeating groups in the relation. In other words, each
row/column intersection can contain one and only one value, rather
than a set of values. - A primary key has been defined, which uniquely identifies each row in the relation.
A table with repeating groups is converted to a relation in 1NF by
Extending the data in each column to fill the cells that are empty because of the repeating groups structures
Functional Dependency
A particular relationship between two attributes or two sets of attributes: The value of one attribute (the determinant) determines the value of other attributes
Determinant
Any attribute that you can use to determine the values assigned to other attribute(s) in the same row.
The attribute on the left-hand side of the arrow in a functional dependency
A primary key is always a ________, while a __________ may or may not be a primary key
Determinant
Give an example of a functional dependency
SSN -> NAME, ADDRESS, BIRTHDATE. A person’s name, address and birthdate are functionally dependent on that person’s social security number
True/False: Data in a relation prove that a functional dependency exists
FALSE. Only knowledge of the problem domain is a reliable method for identifying
a functional dependency
Identify the functional dependencies:
EMPLOYEE3 (EMP_ID, NAME, DEPT, SALARY, COURSE_ID, COURSE_NAME, DATE_ COMPLETED)
EMP_ID -> NAME, DEPT, SALARY
COURSE_ID ->COURSE_NAME
EMPID, COURSE_ID -> DATE_COMPLETED
Partial functional dependency
A functional dependency in which one or more nonkey (non-prime) attributes are functionally dependent on part (but not all) of the primary key
We check partial dependency if …
we have a composite primary key.
Partial dependencies create issues because….
Partial dependencies cannot be tolerated because a table that contains such dependencies is still subject to data redundancies and, therefore, to various anomalies
The requirements for 2NF are:
- The table is in 1NF
- It includes no partial dependencies; that is, no attribute is dependent on only a portion of the primary key
2NF a relation that is in 1NF in which every nonkey attribute is fully functionally dependent on the primary key
Convert this table from 1 NF to 2 NF
EMPLOYEE3 (EMP_ID, NAME, DEPT, SALARY, COURSE_ID, COURSE_NAME,
DATE_COMPLETED)
Write each key component on a separate line, and then write the original key on the last line.
Each component will become the key in a new table:
EMP_ID
COURSE_ID
EMP_ID, COURSE_ID
Write the dependent attributes after each new key.
We get three new tables:
EMPLOYEE (EMP_ID, NAME, DEPT, SALARY)
COURSE (COURSE_ID, COURSE_NAME)
EMPCOURSE (EMP_ID, COURSE_ID, DATE_COMPLETED)
A relation that is in 1NF will be in 2NF if any one of the following conditions applies
The primary key consists of only one attribute
No nonkey attributes exist in the relation (thus all of the attributes in the
relation are components of the primary key)
Every nonkey attribute is functionally dependent on the full set of primary key attributes
Transitive Dependency
A functional dependency between two (or more) nonkey (or non-prime) attributes. A functional dependency is said to be transitive if it is indirectly formed by two functional dependencies. When an indirect relationship causes functional dependency it is called Transitive Dependency.
If P -> Q and Q -> R is true, then P-> R is a transitive dependency.
To achieve 3NF, eliminate the Transitive Dependency.
Example of a transitive dependency
Course (CourseID, Cname, InstructorID, Iname)
Neither Instructor ID nor Iname is a key attribute - that is, neither
attribute is at least part of a key.
However, Iname is functionally dependent on InstructorID
InstructorID -> Iname
True/ False: A transitive Dependency still yields data anomalies
True
A table is in 3NF if it is
in 2NF and It contains no transitive dependencies
A transitive dependency can be resolved by
decomposing into more tables
ER Model stands for
entity-relationship
True/False a table in 3NF guarantees all anomalies have been removed
False
A table is in BCNF if
it is 3NF and For every non-trivial functional dependency (FD) in the table, the determinant is a super key of the table.
A functional dependency is trivial if
the dependent is a subset of the determinant
If a functional dependency X->Y holds true where Y is not a subset of X, this is an example of a
non trivial Functional dependency.
A super key is
a set of one or more attributes (columns), which can uniquely identify a row in a table.
A candidate key
Candidate key is also a unique key (like primary keys) to identify a record uniquely in a table but a table can have multiple candidate keys.
Steps to convert to BCNF
Pull out the undesirable FDs from the target relation as separate relation(s).
Retain the determinant of the pulled-out relation schema as an attribute(s) in the leftover target relation schema to facilitate reconstruction of the original target relation schema
BCNF stands for
Boyce-Codd Normal Form
A table is in 4NF if
If it is in BCNF and does not have multi-valued dependency
Conditions for a table to have a multi-valued dependency
Minimum of three columns.
Two multi-valued attributes, e.g., Y and Z, that are independent of each other in R
X ⇉Y | Z
X
X ⇉Y, means that for a given value of X, the same set of Y values occurs for each value of Z
Steps to decompose to 4NF
Replace the target relation schema (R) by the projections (R1 and R2) that contain the determinant and dependent present in each of the two MVDs.
What is a Lossless join decomposition
a decomposition of a relation R into relations R1, R2 such that if we perform a natural join of relation R1 and R2, it will return the original relation R.
A lossless join decomposition can test
potential multivalued dependency
Steps to test a multivalued dependency
List all potential/possible multivalued dependencies and test them. Ex. Name →→ Skill | Music, Music →→ Skill | Name, Skill →→ Name | Music
Then do natural joins to see if additional rows occur
If additional rows are created while doing a natural join to test multivalued dependency then
the test fails, we have multi-value dependency
A table is in 5NF
if it is in 4NF and it does not have a join dependency
Multi-valued dependency definition
occurs when two attributes in a table are independent of each other but, both depend on a third attribute.
A multivalued dependency consists of at least two attributes that are dependent on a third attribute that’s why it always requires at least three attributes.
5NF is also called
Project Join Normal Form
A join dependency in a relation schema R pertains to conditions where
the natural join of a proper subset of its projections results in the strict reconstruction of R
A relation schema, R does not have a join dependency if
It cannot be reconstructed by a natural join of any proper subset of its projections
True/False If a table can be decomposed into smaller tables without losing information or having additional information, the table violates the 5th normal form.
True
We resolve a join dependency by
decomposing the table into smaller tables
True/False A table that has no undesirable functional dependencies is in 4NF
FALSE
Given table R (X, Y, Z) with Y and Z mapping to multi-valued attributes, if we cannot do lossless-join decomposition with binary projections R1 (X, Y) and R2 (X, Z), then Y and Z are not independent.
TRUE
Primary Key Definition
Primary Key is a set of attributes (or attribute) which uniquely identify the tuples in relation or table.
The primary key is a minimal super key, so there is one and only one primary key in any relationship.
A primary key is always a candidate key.
Composite Key
A primary key that consists of more than one attribute
Foreign key
An attribute in a relation that serves as the primary key of another relation in the same database
