Chapter 2
referential integrity constraint
A rule that requires that the values in a foreign key must have a matching value in the primary key to which the foreign key corresponds
Concept of a surrogate key
A surrogate key is an artificial key that is created to act as the primary key for a relation. The surrogate key is a unique, numeric value that is appended to the relation. Surrogate keys are used in situations when no suitable primary key exists within the user data, or when all available primary keys within the data are too cumbersome for an efficient design. Surrogate key values have no meaning to the users and are normally hidden on all forms, reports, and displays. Most DBMS products have the ability to automatically generate values for surrogate keys as needed.
Composite Key
A key that contains more than one attribute
Null values can cause problems because they are ambiguous.
TRUE
Foreign Key
When the primary key of one relation is placed in a second relation to represent a relationship, the attribute in the second relation
Referential Integrity
When the primary key of one relation is placed into a second relation
A primary key is
a candidate key
A candidate key is
a candidate to be the primary key
True about a key
-It may be unique. -It may be non-unique.
The key that has been designated the ________ key of a relation functionally determines all the other attributes in the relation.
primary
Row Synonyms
record, tuple
Column Synonyms
field, attribute
Any table that meets the requirements of a(n) ________ is in first normal form
relation
Codd
the developer of the relational model for databases
When used to represent a relationship, the primary key must have the same name as the corresponding foreign key.
FALSE
A double arrow notation, A →→ B, is used to indicate a multivalued dependency
TRUE
To be a well-formed relation, every ________ in the relation must be a candidate key
determinant
Determinant
the attribute(s) whose value determines the value of the other attribute
identity Seed
the starting value of a surrogate key in SQL Server
Which of the following functional dependency diagrams accurately represents the following situation: • A campus has many buildings. • Each building has a unique name. • Each building has many rooms. • All rooms in any given building are numbered sequentially starting at "101." • Each room has a certain capacity, although many rooms in the same building or different buildings may have the same capacity. • Each room is assigned to a single department. • A department may have many rooms in one or more buildings, each with the same or different capacities.
(BuildingName, RoomNumber) → (Capacity, Department)
Interpretations of a null value
A null value can mean that the value is unknown. A null value is ambiguous. A null value can mean that the value is known to be blank. A null value can mean that no value for the field is appropriate.
Given the relations: STUDENT (SID, StudentName, Major, AdvisorID) ADVISOR (AdvisorID, AdvisorName, Office, Phone) such that each student is assigned to one advisor, what is true?
AdvisorID is a foreign key
A key must be unique
FALSE
Any table that meets the definition of a relation is said to be in second normal form.
FALSE
Candidate keys may or may not be unique.
FALSE
Every table is a relation, but not every relation is a table
FALSE
In Microsoft Access, a relationship is created by dragging a foreign key column and dropping it on top of the corresponding primary key.
FALSE
In Microsoft Access, foreign keys are designated by using the Foreign Key button in the toolbar.
FALSE
In the normalization process, if you find a candidate key that is not a primary key, then you have determined that the relation needs to be broken into two or more other relations.
FALSE
In the normalization process, it is not necessary to identify all the functional dependencies in a relation.
FALSE
In the relational model, each row of a table contains data that represents an attribute of the entity.
FALSE
Microsoft Access forms can only contain data from one table.
FALSE
Normalization is the process of removing all functional dependencies from a relation
FALSE
Since Microsoft Access is a personal database, it is not subject to the modification problems that occur in other relational databases.
FALSE
Since surrogate keys are used to uniquely identify rows, their values are normally displayed prominently on all forms and reports for the users to see.
FALSE
The use of surrogate keys usually complicates application programming since most DBMS products require the application program to generate surrogate key values.
FALSE
While the relational model for databases appears to hold much promise, few commercial databases have implemented it.
FALSE
A table that meets the requirements of a relation is said to be in which normal form
First normal form
How to create a relationship in Microsoft Access
In Microsoft Access, relationships are created in the Relationships window, which is opened by using the Relationships button on the Database Tools command tab. Once the Relationships window is open, the needed database tables are displayed using the Show Table dialog box. A relationship is initiated by dragging the primary key of one table and dropping it on top of the corresponding foreign key in the related table. At this point the Edit Relationships dialog box is displayed. A referential integrity constraint can be set in this box by checking the Enforce Referential Integrity check box. The relationship is actually created by clicking the Create button in the Edit Relationships dialog box.
Why would tables that have duplicate rows be allowed
It is not uncommon for a table that is returned as the result of a data manipulation operation, such as a query, to contain duplicate rows. This is often tolerated because of the processing time necessary for the DBMS to search the table to find and eliminate duplicate rows. This is especially true if the table is very large. In these cases, it is often acceptable to allow the duplicate rows to exist.
Given the below functional dependency, MedicineCode → (MedicineName, ShelfLife, Manufacturer, Dosage) which statements is not known to be true?
MedicineName is a determinant.
In Microsoft Access, the Relationships window is accessed by using the ________ button on the Database tools command tab.
Relationship
Ensuring that every value of a foreign key matches a value of the corresponding primary key is an example of a referential integrity constraint.
TRUE
Every cell in a relation can hold only a single value.
TRUE
Given the functional dependency for the attributes of ENTITY1, X → (A, B, C), X is a candidate key for the relation ENTITY1 (A, B, C, X).
TRUE
If the condition exists such that knowing the value of attribute X determines the value of attribute Y, then attribute Y is functionally dependent on attribute X.
TRUE
In Microsoft Access, relationships between tables are created in the Relationships window.
TRUE
In the normalization process, if you find that every determinant in a relation is a candidate key, then you have determined that the relation is well formed.
TRUE
In the normalization process, it is necessary to identify all the determinants in a relation.
TRUE
Surrogate key values have no meaning to the users.
TRUE
The first step of the normalization process is to identify all the candidate keys of a relation
TRUE
The primary key is used both to identify unique rows in a relation and to represent rows in relationships.
TRUE
To be considered a composite key, a key must contain at least two attributes.
TRUE
To create a well-formed relation through normalization, every determinant must be a candidate key
TRUE
To represent a relationship in the relational model, the primary key of one relation is placed into a second relation.
TRUE
A surrogate key may be appropriate under this circumstance
The available candidate keys would be prone to typographical errors.
Normalization
a process whereby relations that are not well-formed are modified to become well-formed relations. A relation is considered to be well-formed if the data within it are not subject to unintended negative consequences when it is maintained. Although normalization recognizes several different normal forms, which are categories that the structure of a relation can be classified into based on the types of problems to which it is vulnerable, the basic premises of normalization are that (1) every determinant should be a candidate key, and (2) any relation that is not well formed should be broken into two or more relations that are well-formed.
Concept of a functional dependency
a relationship that exists among the attributes of a relation, such that if the value of one attribute or group of attributes is known, the value of another attribute or group of attributes can be determined. In a functional dependency, the attribute(s) whose value determines the value of the other attribute is called the "determinant." The other attribute, whose value is determined by the determinant, is said to be functionally dependent on the determinant.
During the normalization process, the remedy for a relation that is not well formed is to
break it into two or more relations that are well formed
One important relational design principle is that
every determinant must be a candidate key
Table Synonyms
file, relation
First step of the normalization process
identify all the candidate keys of a relation
To create a relationship in Microsoft Access, we drag and drop the ________ of a table
primary key
In Microsoft Access, the relationship between two tables is not actually created until
the Join button in the Edit Relationships dialog box is clicked
In the normalization process, if you find that every determinant in a relation is a candidate key then you have determined that
the relation is well formed
Primary key tasks
uniquely identify the rows in a table represent rows in relationships most DBMS products use the values of the primary key to organize the storage of the relation. used in indexes and other structures to improve performance for search operations
Relation, tuple, and attribute
used primarily by database theoreticians. These terms are synonymous with the terms table, row, and column, respectively, in regards to a relational database. They are also equivalent to the terms file, record, and field, which tend to be used by many traditional data processing professionals.
Key
one or more columns of a relation that is used to identify a row.
In Microsoft Access, referential integrity constraints are created in the
Edit Relationships dialog box
Composite Key
A key that contains two or more attributes
Functional Dependency
The relationship between two attributes that denotes that if the value of the first attribute is known, then the value of the second attribute can be determined
Alternate Keys
The unique keys that are not chosen to be the primary key
Surrogate Keys
They are numeric. They are usually generated by the DBMS. They are unique. They are usually hidden on forms and reports.
What is true about a relation
a two-dimensional table. The cells of a relation must hold a single value. may not have duplicate rows. The order of the rows of a relation is insignificant.
Surrogate Key
a unique, numeric value that is appended to the relation to serve as the primary key
In the normalization process, it is necessary to
identify all the candidate keys of a relation identify all the functional dependencies of a relation identify all the determinants of a relation determine if every determinant is a candidate key
In Microsoft Access, relationships between tables are created
in the Relationships window
Although Microsoft Access is a personal database, the database tables in Access are still subject to
modification problems if they are not well-formed
Every relation is a table, but not every table is a relation.
TRUE
What is a multivalued dependency, and how do they affect the normalization process?
A multivalued dependency is the case where a determinant is associated with a set of values. When isolated, they do not have modification anomalies; tables with these isolated dependencies are considered to be in fourth normal form (4NF).
Concept of a foreign key
To implement a relationship within a relational database, the primary key of one relation is placed as an attribute in another relation. This attribute is called a foreign key in the second relation because it is the primary key of a relation that is foreign to the table in which the field resides.
In Microsoft Access, referential integrity constraints are created
by checking the Enforce Referential Integrity check box in the Edit Relationships dialog box
In Microsoft Access, a relationship between two tables is created
by dragging the primary key column of one table onto the foreign key column of the other table in the Relationships window
Basic steps of the normalization process
Before starting the normalization process, the relation must be in first normal form, which means that it meets the basic requirements of being a relation. The first step of the normalization process is to identify all the candidate keys in the relation. The second step is to identify all the functional dependencies in the relation. Third, check to see if all the identified determinants are candidate keys. If all determinants are candidate keys, the relation is well-formed and nothing more needs to be done. On the other hand, if any of the determinants is not a candidate key, the relation is not well-formed, and it is necessary to: (1) place the columns of that functional dependency into a new relation, (2) make the determinant of that functional dependency the primary key of the new relation, (3) leave a copy of the determinant in the original relation as a foreign key, and (4) create a referential integrity constraint between the original relation and the new relation. This process should be repeated for every relation until every determinant in a relation is a candidate key of that relation.
Distinguish between the primary key and a candidate key
Both the primary key and a candidate key can uniquely identify the rows in a table. The primary key is the candidate key that is chosen by the database designer, working with the users, to uniquely identify rows and to represent relationships. Although any candidate key could, by definition, be selected to act as the primary key, the choice of primary key is often based on design decisions such as the amount of foreign key data that would be generated.
In the normalization process, if you find a candidate key that is not a determinant then you should not
rename the determinant to another attribute description