Chapter 4 Database Design Flashcards
Physical
________ database specification indicates all the parameters for data storage that are theninput to database implementation.A) LogicalB) PhysicalC) SchematicD) Conceptual
security specifications
A form of database specification which maps conceptual requirements is called:A) logical specifications.B) response specifications.C) security specifications.D) physical specifications
Tables
Data is represented in the form of:A) data trees.B) tables.C) data notes.D) chairs.
Relation
A two-dimensional table of data sometimes is called a:A) group.B) set.C) declaration.D) relation.
Data integrity
________ is a component of the relational data model included to specify business rules tomaintain the integrity of data when they are manipulated.A) Business rule constraintB) Data integrityC) Business integrityD) Data structure
primary key
An attribute (or attributes) that uniquely identifies each row in a relation is called a:A) column.B) foreign field.C) primary key.D) duplicate key.
Foreign Key
An attribute in a relation of a database that serves as the primary key of another relation in thesame database is called a:A) link attribute.B) link key.C) foreign key.D) foreign attribute.
Enterprise Key
A primary key whose value is unique across all relations is called a(n):A) global primary key.B) inter-table primary key.C) enterprise key.D) foreign global key
Composite Key
A primary key that consists of MORE THAN ONE ATTRIBUTE IS CALLED A:A) foreign key.B) composite key.C) multivalued key.D) cardinal key.
create table
In the SQL language, the ________ statement is used to make table definitions.A) create sessionB) create tableC) create indexD) select
COMPOSITE
In the figure below, the primary key for "Order Line" is which type of key?A) CompositeB) ForeignC) StandardD) Grouped
integrity constraints.
A domain definition consists of all of the following components EXCEPT:A) domain name.B) data type.C) integrity constraints.D) size.
No two rows in a relation are identical.
Which of the following are properties of relations?A) Each attribute has the same name.B) No two rows in a relation are identical.C) There are multivalued attributes in a relation.D) All columns are numeric
Sam Hinz
Which of the following violates the atomic property of relations?A) SamB) HinzC) Sam HinzD) Atomic
no primary key attribute can be null.
The entity integrity rule states that:A) no primary key attribute can be null.B) referential integrity must be maintained across all entities.C) each entity must have a primary key.D) a primary key must have only one attribute
entity integrity rule
The ________ states that no primary key attribute may be null.A) referential integrity constraintB) entity integrity ruleC) partial specialization ruleD) range domain rule
Candidate
In the figure below, Customer_ID in the CUSTOMER Table is which type of key?A) CompositeB) CandidateC) StandardD) Grouped
Maximize Storage Pace
All of the following are the main goals of normalization EXCEPT:A) minimize data redundancy.B) simplify the enforcement of referential integrity.C) maximize storage space.D) make it easier to maintain data
First normal form
When all multivalued attributes have been removed from a relation, it is said to be in:A) first normal form.B) second normal form.C) Boyce-Codd normal form.D) third normal form
Boyce-Codd normal form.
The normal form which removes any remaining functional dependencies because there wasmore than one primary key for the same nonkeys is called:A) fifth normal form.B) fourth normal form.C) Boyce-Codd normal form.D) sixth normal form.
fourth normal form.
The normal form which deals with multivalued dependencies is called:A) fifth normal form.B) fourth normal form.C) Boyce-Codd normal form.D) sixth normal form.
Second
A relation that contains no multivalued attributes and has nonkey attributes solely dependenton the primary key but contains transitive dependencies is in which normal form?A) FirstB) SecondC) ThirdD) Fourth
functional dependency
A constraint between two attributes is called a(n):A) functional relation.B) attribute dependency.C) functional dependency.D) functional relation constraint
the key must indicate the row's position in the table
A candidate key must satisfy all of the following conditions EXCEPT:A) the key must uniquely identify the row.B) the key must indicate the row's position in the table.C) the key must be nonredundant.D) each nonkey attribute is functionally dependent upon it.
determinant.
The attribute on the left-hand side of the arrow in a functional dependency is the:A) candidate key.B) determinant.C) foreign key.D) primary key.
partial functional
A functional dependency in which one or more nonkey attributes are functionally dependenton part, but not all, of the primary key is called a ________ dependency.A) partial key-basedB) partial functionalC) cross keyD) merged relation
transitive dependency.
A functional dependency between two or more nonkey attributes is called a:A) partial functional dependency.B) partial nonkey dependency.C) transitive dependency.D) partial transitive dependency.
Deletion
________ problems are encountered when removing data with transitive dependencies.A) InsertionB) ModificationC) DeletionD) Merging
merge
The need to ________ relations commonly occurs when different views need to beintegrated.A) metadataB) systemC) dropD) merge
synonyms
Two or more attributes having different names but the same meaning are called:A) homonyms.B) aliases.C) synonyms.D) alternate attributes.
homonym
An attribute that may have more than one meaning is called a(n):A) homonym.B) alias.C) double defined attribute.D) synonym.
alias.
An alternative name for an attribute is called a(n):A) synonym.B) alias.C) alternate attribute.D) related characteristic
one must be able to check the output of a CASE tool.
Understanding the steps involved in transforming EER diagrams into relations is importantbecause:A) one must be able to check the output of a CASE tool.B) there are rarely legitimate alternatives from which to choose.C) CASE tools can model any situation.D) CASE tools model hypothetical business problems
descriptor
A nonkey attribute is also called a(n):A) column.B) unimportant datum.C) descriptor.D) address.
create two new relations, one containing the multivalued attribute.
When a regular entity type contains a multivalued attribute, one must:A) create a single relation with multiple lines for each instance of the multivalued attribute.B) create two new relations, one containing the multivalued attribute.C) create two new relations, both containing the multivalued attribute.D) delete the relation and start over.
Strong entity/weak entity
In the figure below, what type of relationship do the relations depict?A) Strong entity/weak entityB) MultivaluedC) Composite foreign keyD) One-to-many
one-to-Many
In the figure below, what type of relationship do the relations depict?A) Strong entity/weak entityB) One-to-manyC) TernaryD) Many-to-many
An associative entity
In the figure below, what is depicted?A) A one-to-one relationshipB) A unary relationshipC) A one-to-many relationshipD) An associative entity
Recursive foreign
In the figure below, what type of key is depicted?A) PrimaryB) Recursive primaryC) CompositeD) Recursive foreign
A component is always used in only one item.
Referring to the figure below, which of the following is NOT true?A) A component is part of an item.B) A component is always used in only one item.C) A component can be part of an item.D) A component may be used in many items
Ternary
The figure below is an example of mapping which type of relationship?A) FirstB) SecondC) UnaryD) Ternary
Modification
________ are anomalies that can be caused by editing data in tables.A) InsertionB) DeletionC) ModificationD) Creation
well-structured
A relation that contains minimal redundancy and allows easy use is considered to be:A) clean.B) simple.C) complex.D) well-structured
True
Data structures include data organized in the form of tables with rows and columns. T / F
referential integrity constraint.
A rule that states that each foreign key value must match a primary key value in the otherrelation is called the:A) referential integrity constraint.B) key match rule.C) entity key group rule.D) foreign/primary match rule.
TRUE.
In the figure below, each employee has exactly one manager.Answer : T / F
A primary key uniquely identifies each row of a relation (or table). It can be either asingle column or a composite of two or more columns, which is called a composite key. Aforeign key allows us to represent the relationship between two tables. A foreign key in one tableis generally a reference to a primary key in another table
Discuss the various relational keys.
Each relation (or table) has a unique name. An entry at the intersection of each row andcolumn is atomic and each row is unique. Each attribute (or column) within a table has a uniquename. The sequence of rows and columns is insignificant
Discuss the properties of relations
A multivalued attribute is one that can take on more than one value. According to thedefinition of a relation, there can be no multivalued attributes. The reason for this will be seenlater when the schema is normalized. A multivalued attribute can be resolved by creating aseparate instance for each value and repeating the data from the non-multivalued attributes
Discuss why it is important to remove multivalued attributes from a relation
A candidate key is an attribute or a combination of attributes that uniquely identifies arow in a relation. A candidate key must be nonredundant and must also uniquely identify eachrow. When we look at functional dependencies and candidate keys, we can always say that adeterminant is always a candidate key.
Explain what a candidate key is and how it might be used
Each regular entity is transformed into a relation. There are a couple of things that needto be done with some special types of attributes. Composite attributes are broken down into theirindividual components. Multivalued attributes are broken down into separate relations. Forexample, if there was a multivalued skill attribute, this would become a skill relation. Also, weignore derived attributes.
Discuss how you would map a regular entity to a relation.
A weak entity becomes a relation. However, since a weak entity instance does not existby itself, we must create a relation with the primary key from the strong relation as the primarykey, as well as the identifying attribute.
How do you convert weak entities to relations?
A surrogate key should be created when any of the following conditions hold: there is acomposite primary key, the natural primary key is inefficient (such as a long name) or the naturalprimary key is recycled. When you have an associative entity, it is often best to use a surrogatekey when you have more than two relations that the associative entity is related to, since theprimary key would be a composite of the primary keys of all entities related to the associativeentity.
Discuss when it is best to create a surrogate key for an associative entity
There are two possible cases for associative entities. In one case, you can have anidentifier assigned. You would then use this as the primary key. The other case is where theidentifier is not assigned. In this case, you would use the primary keys from the other two (ormore) relations that are related to the associative entity.
Discuss the two possible scenarios when mapping an associative entity
The entity type in the unary relationship is mapped to a relation in the same way as wedid for a relation. We next add a foreign key attribute, which maps back to the primary key. Thisis called a recursive foreign key. So, if we had a relation such as a person supervises one or morepersons, the person_id would be a primary key and then there would be a supervisor id whichpoints back to the person_id
Discuss how to map a unary one-to-many relationship.
Two relations are created. One represents the entity type in the relationship, and theother is an associative relation to represent the M:N relationship. The primary key of theassociative relation consists of two attributes which take their value from the primary key of theother relation.
Discuss how to map a unary many-to-many relationship.
When we have a supertype/subtype relationship, we need to assign one entity for thesupertype and one for each subtype. The supertype has all attributes common to all subtypes aswell as a primary key. Each subtype relation has the primary key of the supertype as well as anyattributes that are specific to that subtype. Finally, we assign one or more attributes to thesupertype to function as subtype discriminators
Explain how subtype/supertype relationships are converted to relations
An anomaly is an error or inconsistency that may result when a user attempts to updatea table that contains redundant data. There are three types of anomalies. An insert anomalyoccurs when a user attempts to enter new information for part of a table. For example, if westored customer address with an order. A deletion anomaly occurs when the user wants to deletepart of a record but also has to delete related data. A modification anomaly occurs when we haveto change the same data in multiple places
What is an anomaly, and what are the three types of anomalies?
During logical database design, normalization is used as a check and balance to makesure that your E-R diagram is correct as well as your relational schema before doing physicaldesign. Another occasion when you benefit is reverse-engineering an older system, since manyof the tables and user views are redundant.
Discuss the two major occasions when you benefit from using normalization