Deck 5: Database Design

Question 1

From a pragmatic standpoint,the only important rule of normalization is that the determinant of every functional dependency must be a candidate key.

Answer 1

True

Question 2

An entity needs to be examined according to normalization criteria before creating a table from it in the relational database design.

Answer 2

False

Question 3

To represent a M:N relationship in the relational model,an intersection relation is created to represent the relationship itself.

Answer 3

True

Question 4

Relations should always be normalized to the highest degree possible.

Question 5

The ideal primary key is short,numeric,and fixed.

Question 6

In the relational model,many-to-many relationships cannot be directly represented by relations the way 1:1 and 1:N relationships can.

Question 7

For a 1:1 relationship,the key of each table should be placed in the other table as the foreign key.

Question 8

To represent a 1:N relationship in the relational model,the key of the entity on the one side of the relationship is placed as a foreign key in the entity on the many side of the relationship.

Question 9

The first step in representing entities using the relational model is to determine which identifier will be used as the key.

Question 10

If a weak entity is ID-dependent but not existence-dependent,it can be represented using the same techniques as a strong entity.

Question 11

When creating a table in the relational database design from an entity in the extended E-R model,the attributes of the entity become the rows of the table.

Question 12

The key of the parent entity becomes part of the key of an ID-dependent entity.

Question 13

By default,the identifier of the entity becomes the foreign key of the corresponding table.

Question 14

When applied to 1:N relationships,the term "parent" refers to the many side of the relationship since a child may have many parents.

Question 15

In certain circumstances,there may be a preference as to which table in a 1:1 relationship contains the foreign key.

Question 16

A surrogate key is appropriate when the primary key of a table contains a lengthy text field.

Question 17

Relationships that are 1:1 do not require referential integrity constraints.

Question 18

The technique for representing E-R relationships in the relational model is dependent on the minimum cardinality.

Question 19

To represent a 1:N relationship in the relational model,the key of either entity may be placed as a foreign key in the other entity.

Question 20

One of the important properties of an attribute is whether or not it is required.

Question 21

Which of the following would be a reason to denormalize a relation?

A)Relax security
B)Lack of design time
C)End user preference
D)Improve performance
E)None of the above

Question 22

All recursive relationships are 1:1.

Question 23

Microsoft Access uses the same pure N:M relationships that occur in data modeling.

Question 24

Given the tables
TABLE_A (Attribute1, Attribute2, Attribute3)
TABLE_B (Attribute4, Attribute5, Attribute6)
As shown in the figure below,which of the following would display the correct placement of foreign keys in the relational model?

A)
TABLE_A (Attribute1, Attribute2, Attribute3)
TABLE B (Attribute4, Attribute5, Attribute6, Attributel)
B)
TABLE_A (Attribute1, Attribute2, Attribute3, Attribute4, Attribute5)
TABLE_B (Attribute4, Attribute5, Attribute6)

C)
TABLE_A (Attribute1, Attribute2, Attribute3, Attribute4)
TABLE_B (Attribute4 , Attribute5, Attribute6, Attributel)

D)
TABLE_A (Attribute1, Attribute2, Attribute3)
TABLE B (Attribute4, Attribute5, Attribute6)

E)
TABLE_A (Attribute1, Attribute2, Attribute3, Attribute6)
TABLE_B (Attribute4, Attribute5, Attribute6)

Question 25

Each attribute of an entity becomes a(n)________ of a table.

A)column
B)primary key
C)foreign key
D)alternate key
E)B or D

Question 26

One of the important properties of a column is whether or not it is ________.

A)found in more than one entity
B)required
C)character or numeric
D)subject to normalization
E)subject to denormalization

Question 27

Recursive relationships can be represented in the relational model using the same techniques that are used for binary relationships.

Question 28

To create a 1:1 relationship in Microsoft Access,the Indexed property of the foreign key column must be set to Yes (No Duplicates).

Question 29

The ideal primary key is ________.

A)short
B)numeric
C)fixed
D)A and B
E)A,B,and C

Question 30

Which of the following is not true about surrogate keys?

A)They are identifiers that are supplied by the system,not the users.
B)They have no meaning to the users.
C)They are nonunique within a table.
D)They can be problematic when combining databases.
E)The DBMS will not allow their values to be changed.

Question 31

The key for an intersection relation is always the combination of the keys of the parent entities.

Question 32

Which of the following is true about representing a weak entity with the relational model?

A)If the weak entity is existence-dependent,the key of the parent must be part of the key of the weak entity.
B)If the strong entity has a minimum cardinality of 1,the key of the weak entity must be part of the strong entity.
C)If the weak entity is ID-dependent,the key of the weak entity must be part of the key of the parent entity.
D)If the weak entity is ID-dependent,the key of the parent entity must be part of the key of the weak entity.
E)If the parent entity is existence-dependent,then the minimum cardinality of the weak entity is zero.

Question 33

As far as Microsoft Access is concerned,there are no 1:N relationships.

Question 34

Which of the following is true when representing a 1:1 binary relationship using the relational model?

A)The key of the entity with the highest minimum cardinality must be placed in the other entity as a foreign key.
B)The key of each entity must be placed in the other as a foreign key.
C)The key of either entity is placed in the other as a foreign key.
D)The key of the entity with the most attributes must be placed in the other entity as a foreign key.
E)Both entities must have the same primary key.

Question 35

As far as Microsoft Access is concerned,there are no N:M relationships.

Question 36

The first step in transforming an extended E-R model into a relational database design is to ________.

A)create a table for each relationship
B)evaluate the entities against the normalization criteria
C)create a table for each entity
D)remove any recursive relationships
E)document referential integrity constraints

Question 37

A surrogate key should be considered when ________.

A)a relationship is M:N
B)a composite key is required
C)the key contains a lengthy text field
D)the key contains a number
E)an index needs to be created

Question 38

By default,Microsoft Access creates 1:1 relationships between tables.

Question 39

In a relational database design,all relationships are expressed by ________.

A)creating a primary key
B)creating a foreign key
C)creating a supertype
D)creating a subtype
E)creating a line between entities

Question 40

The identifier of the entity becomes the ________ of the corresponding table.

A)primary key
B)foreign key
C)supertype
D)subtype
E)either A or B

Question 41

Which of the following is the correct technique for representing a 1:N relationship in the relational model?

A)The key of the entity on the one side is placed into the relation for the entity on the many side.
B)The key of the child is placed into the relation of the parent.
C)The key of either relation can be placed into the other relation.
D)The key of the entity on the many side is placed into the relation for the entity on the one side.
E)An intersection relation is created,and the keys from both parent entities are placed as keys in the intersection relation.

Question 42

Given the tables
PRODUCT (ProductID, Description, Cost)
SUPPLIER (SupplierID, ContactName, PhoneNumber)
As shown in the figure below,which of the following would represent the correct placement of foreign keys?

A)
PRODUCT (ProductID,Description,Cost) SUPPLIER (SupplierID,ContactName,PhoneNumber)
B)
PRODUCT (ProductID,Description,Cost,SupplierID) SUPPLIER .(SupplierID,ContactName,PhoneNumber,ProductID).
C)
PRODUCT (ProductID,Description,Cost) SUPPLIER ,(SupplierID,ContactName,PhoneNumber)
PRODUCT_SUPPLIER (ProductID,SupplierID)..

D)
PRODUCT (ProductID,Description,Cost,SupplierID) SUPPLIER (SupplierID,ContactName,PhoneNumber),
E)
PRODUCT (ProductID,Description,Cost) SUPPLIER (SupplierID,ContactName,PhoneNumber)
PRODUCT_SUPPLIER ( ProductID , SupplierID),.

Question 43

After a 1:1 relationship has been created between two tables in Microsoft Access,the Relationship Type of One-To-One appears:

A)in the Relationship Type property of the primary key column in table Design View.
B)in the Relationship Type property of the foreign key column in table Design View.
C)in the table object in the Relationships window.
D)in the Show Table dialog box.
E)in the Edit Relationships dialog box.

Question 44

In many-to-many relationships in a relational database design,________.

A)the intersection table is ID-dependent on one of the parents
B)the intersection table is ID-dependent on both of the parents
C)the minimum cardinality from the intersection table to the parents is always M
D)A and B
E)B and C

Question 45

Which of the following is not true of recursive relationships?

A)When the recursive relationship is M:N,an intersection table is created.
B)The rows of a single table can play two different roles.
C)The techniques for representing the tables are the same as for non-recursive relationships except the rows are in the same table.
D)Recursive relationships can be 1:1,1:N,or M:N relationships.
E)Even when the relationship is 1:N,a new table must be defined to represent the relationship.

Question 46

As far as Microsoft Access is concerned,there are no:

A)1:1 relationships.
B)1:N relationships.
C)N:1 relationships.
D)N:M relationships.
E)recursive relationships.

Question 47

The first step of database design is to define a table for each ________.

Question 48

Which of the following is the correct technique for representing a M:N relationship using the relational model?

A)An intersection relation is created,and the key of either entity is placed as a key in both the intersection relation and in the other relation.
B)An intersection relation is created with a surrogate key,which is placed in each of the parent entities.
C)An intersection relation is created,and the keys of both parent entities are placed as a composite key in the intersection relation.
D)The key from either relation is placed as a foreign key in the other relation.
E)None of the above

Question 49

When transforming an ID-dependent E-R data model relationship into a relational database design and the child entity is designed to use a surrogate key,then ________.

A)the parent entity must also use a surrogate key
B)the relationship remains an ID-dependent relationship
C)the relationship changes to a non-ID-dependent relationship
D)A and B
E)A and C

Question 50

What relationship pattern is illustrated in the following schema? EMPLOYEE EmployeeID,OfficePhone,Manager)
Manager in EMPLOYEE must exist in EmployeeID in EMPLOYEE

A)Association relationship
B)Intersection relationship
C)Recursive relationship
D)Strong entity relationship
E)Supertype/subtype relationship

Question 51

When transforming an E-R data model into a relational database design,the key of the parent entity should be placed as part of the primary key into the child entity ________.

A)when the child entity is ID-dependent
B)when the child entity is non-ID-dependent
C)when the child entity has a 1:1 relationship with the parent entity
D)when the child entity has a 1:N relationship with the parent entity
E)when the child entity has a recursive relationship with the parent entity

Question 52

By default,when Microsoft Access creates a relationship between two tables,it creates a(n):

A)1:1 relationship.
B)1:N relationship.
C)N:M relationship.
D)association relationship.
E)recursive relationship.

Question 53

To create a 1:1 relationship between two tables in Microsoft Access:

A)the Indexed property of the foreign key column must be set to No.
B)the Indexed property of the foreign key column must be set to Yes (Duplicates OK).
C)the Indexed property of the foreign key column must be set to Yes (No Duplicates).
D)the Data Type of the foreign key column must be set to AutoNumber.
E)the Smart Tag property of the foreign key column must be set to Foreign Key.

Question 54

Microsoft Access does not create N:M relationships because:

A)Microsoft Access creates databases based on database designs instead of data models.
B)Microsoft Access creates databases based on data models instead of database designs.
C)Microsoft Access cannot implement association relationships.
D)Microsoft Access cannot implement supertype/subtype relationships.
E)Microsoft Access cannot implement recursive relationships.

Question 55

What relationship pattern is illustrated in the following schema?
PRODUCT (ProductID, Description)
SUPPLIER (SupplierID, ContactName, PhoneNumber)
PRODUCT_SUPPLIER (ProductID, SupplierID, Cost)
ProductID in PRODUCT_SUPPLIER must exist in ProductID in PRODUCT
SupplierID in PRODUCT_SUPPLIER must exist in SupplierID in PRODUCT

A)Association relationship
B)Intersection relationship
C)Recursive relationship
D)Strong entity relationship
E)Supertype/subtype relationship

Question 56

Which of the following is not true about representing subtypes in a relational database design?

A)One table is created for the supertype and one for each subtype.
B)All of the attributes of the supertype are added to the subtype relations.
C)The key of the supertype is made the key of the subtypes.
D)A subtype and its supertype are representations of the same underlying table.
E)An instance of the supertype may be related to one instance each of several subtypes.

Question 57

Given the tables
PRODUCT (ProductID, Description, Cost)
SUPPLIER (SupplierID, ContactName, PhoneNumber)
As shown in the figure below,which of the following would represent the correct placement of foreign keys?

A)PRODUCT (ProductID,Description,Cost) SUPPLIER (SupplierID,ContactName,PhoneNumber)
B)PRODUCT (ProductID,Description,Cost) SUPPLIER (SupplierID,ContactName,PhoneNumber,ProductID)
C)PRODUCT (ProductID,Description,Cost,SupplierID) SUPPLIER (SupplierID,ContactName,PhoneNumber,ProductID)
D)PRODUCT (ProductID,Description,Cost,ContactName) SUPPLIER (SupplierID,ContactName,PhoneNumber)
E)PRODUCT (ProductID,Description,Cost,SupplierID) SUPPLIER (SupplierID,ContactName,PhoneNumber)

Question 58

In relational database design,ID-dependent entities are used to ________.

A)represent 1:1 relationships
B)represent 1:N relationships
C)represent N:M relationships
D)handle recursive relationships
E)eliminate the need for weak entities being converted to tables.

Question 59

In many-to-many relationships in a relational database design,________.

A)the key of the child is placed as a foreign key into the parent
B)the key of the parent is placed as a foreign key into the child
C)the keys of both tables are placed in a third table
D)the keys of both tables are joined into a composite key
E)C and D

Question 60

What relationship pattern is illustrated in the following schema?
VEHICLE (VehicleID, Cost)
CAR (VehicleID, NumberOfSeats)
TRUCK (VehicleID , CargoCapacity)
VehicleID in CAR must exist in VehicleID in VEHICLE
VehicleID in TRUCK must exist in VehicleID in VEHICLE

A)Association relationship
B)Intersection relationship
C)Recursive relationship
D)Strong entity relationship
E)Supertype/subtype relationship

Question 61

To represent a many-to-many relationship in the relational model,a(n)________ table is used.

Question 62

After a 1:1 relationship has been created between two tables in Microsoft Access,the Relationship Type of One-To-One appears in the ________.

Question 63

Microsoft Access does not create N:M relationships because Microsoft Access creates databases based on ________.

Question 64

How are one-to-one recursive relationships addressed using the relational model?

Question 65

A(n)________ is a relationship among entities of the same class.

Question 66

Explain the representation of a many-to-many strong entity relationship in a relational database design.

Question 67

To create a 1:1 relationship between two tables in Microsoft Access,the Indexed property of the foreign key column must be set to ________.

Question 68

There are cases where it is possible to normalize a table too far,in which case there may be a need for ________.

Question 69

For a(n)________ weak entity,it is necessary to add the key of the parent entity to the weak entity's relation so that this added attribute becomes part of the weak entity's key.

Question 70

As far as Microsoft Access is concerned,there are no ________.

Question 71

By default,when Microsoft Access creates a relationship between two tables it creates a(n)________ relationship.

Question 72

Explain the pragmatic reason for using surrogate keys.

Question 73

Explain the process of representing an entity using the relational model.

Question 74

To normalize a relation,the determinant of every functional dependency should be a(n)________.

Question 75

Explain the representation of a one-to-many strong entity relationship in a relational database design.

Question 76

What is an association relationship,and how does it differ from an N:M relationship?

Question 77

What is denormalization and why can it be desirable?

Question 78

Explain the representation of ID-dependent weak entities using the relational model.

Question 79

Write the schema to represent the entities below,including tables,the proper placement of the foreign key,and referential integrity constraint.

Question 80

Once a table has been defined,it should be examined according to ________ criteria.