Question 1

take ERDs & create relational tables that implement organization's business rules; represents ideal

Accepted Answer

goal of logical database design

Question 2

store all the organizations data in a non-redundant manner

Accepted Answer

relational tables

Question 3

implemented via foreign keys

Accepted Answer

relationships btwn entities in relational tables

Question 4

relationships btwn entities are shown by lines in diagrams

Accepted Answer

in ERDs

Question 5

logical design is __ __ implemented

Accepted Answer

seldom actually

Question 6

logical design does not consider

Accepted Answer

performance requirements for database

Question 7

logical design meets organization's __ requirements but may not meet organization's __ requirements

Accepted Answer

data; performance

Question 8

usually measured in terms of response time/how quickly something is completed

Accepted Answer

performance

Question 9

technically __ __ should not be added as part of logical design

Accepted Answer

surrogate keys

Question 10

reason surrogate keys are necessary are

Accepted Answer

performance related

Question 11

entities from ERD always become a(n) __ & implement relationship btwn entities via __ keys

Accepted Answer

table; foreign

Question 12

identifier of entity becomes

Accepted Answer

table's primary key

Question 13

attributes of entity become

Accepted Answer

columns in table

Question 14

1 of 2 ways one-to-one binary relationship can be converted to relational tables, 2 entities become individual relational tables & relationship is implemented by

Accepted Answer

placing primary key of #1 table as a foreign key in #2 table

Question 15

1 of 2 ways one-to-one binary relationship can be converted to relational tables, 2entities become individual relational tables & relationship is implemented by placing

Accepted Answer

primary key of #2 table as a foreign key in #1 table

Question 16

database design should strive to reduce

Accepted Answer

number of null values likely to occur

Question 17

better design of one-to-one binary relationship converted to relational tables is when there are __ null values

Accepted Answer

fewer

Question 18

placement of a foreign key implements

Accepted Answer

referential integrity

Question 19

by setting referential integrity constraint index to be unique it will

Accepted Answer

not allow duplicate values

Question 20

not allowing duplicate values is a(n)

Accepted Answer

important part of creating one-to-one aspect of relationship

Question 21

foreign keys are created in a database as __

Accepted Answer

constraints

Question 22

constraints can be created by

Accepted Answer

using SQL when table is built

Question 23

constraints can be added

Accepted Answer

later after table is built

Question 24

sometimes adding foreign key constraints later can be problematic if

Accepted Answer

data already exists in a table because that data may violate foreign key rules

Question 25

data must first "cleaned" before

Accepted Answer

adding foreign key constraint

Question 26

most relational databases today have graphical user interface tool that provides simpler way to

Accepted Answer

interact w/database & create constraints (as well as process any other SQL type command)

Question 27

when converting one-to-one binary relationship if one of entities is optional, step 1

Accepted Answer

convert each entity into a relational table

Question 28

when converting one-to-one binary relationship if one of entities is optional, step 2

Accepted Answer

add columns to table for each attribute in entity

Question 29

when converting one-to-one binary relationship if one of entities is optional, step 3

Accepted Answer

make unique identifier of each entity primary key of that entity's relational table

Question 30

when converting one-to-one binary relationship if one of entities is optional, step 4

Accepted Answer

take primary key from optional side & place as foreign key in entity on required side, creating referential integrity constraint

Question 31

if both entities are optional, when converting one-to-one binary relationship, place foreign key

Accepted Answer

such that occurrence of null values minimized

Question 32

when converting one-to-one binary relationship if one of entities is optional, step 5

Accepted Answer

set foreign key to be unique

Question 33

when converting one-to-one binary relationship if one of entities is optional, step 6

Accepted Answer

determine if null values should be allowed in foreign key column

Question 34

if neither of entities is optional, when converting one-to-one binary relationship, hard part is deciding

Accepted Answer

where to place foreign key

Question 35

since both sides of relationship are required, when converting one-to-one binary relationship, null values

Accepted Answer

will not be an issue

Question 36

from a database design standpoint, when converting one-to-one binary relationship, if neither entity is optional

Accepted Answer

it makes no difference which table has foreign key

Question 37

most common type of relationship implemented in relational databases

Accepted Answer

one-to-many binary relationship

Question 38

in relational database implementation, one-to-many binary relationship

Accepted Answer

represents the ideal

Question 39

when converting a one-to-one binary relationship, Step 1 is to convert

Accepted Answer

each entity to relational table

Question 40

when converting a one-to-one binary relationship, Step 2 each attribute of entity

Accepted Answer

becomes column in relational table

Question 41

when converting a one-to-one binary relationship, Step 3 unique identifier of entity becomes

Accepted Answer

primary key of relational table

Question 42

when converting a one-to-one binary relationship, Step 4 take unique identifier from entity

Accepted Answer

on one side & place it in relational table representing entity on many side

Question 43

when converting a one-to-one binary relationship, Step 5 determine if

Accepted Answer

foreign key field should allow null values (foreign key will not be unique)

Question 44

many-to-many binary relationship cannot be implemented directly as relational tables

Accepted Answer

without introducing massive amounts of duplicate data

Question 45

solution massive amounts of duplicate data, when converting many-to-many binary relationship, is to

Accepted Answer

break many-to-many binary relationship into 2 one-to-many relationships

Question 46

when converting many-to-many binary relationship & many-to-many binary relationship is broken into 2 one-to-many relationships the result is

Accepted Answer

3rd table called intersection table

Question 47

when intersection table created, when converting many-to-many binary relationship, unique identifier of this new entity is always

Accepted Answer

unique identifier of entity on left plus unique identifier of entity on right

Question 48

when convert many-to-many binary relationship relationship to relational tables, Step 1 is to convert one many-to-many binary relationship into

Accepted Answer

two one-to-many binary relationships by adding an association entity

Question 49

when convert many-to-many binary relationship relationship to relational tables, Step 1a unique identifier of association entity will be at least

Accepted Answer

unique identifier of entity on left plus same of entity on right

Question 50

when convert many-to-many binary relationship relationship to relational tables, Step 2 convert each entity to a(n) __ table

Accepted Answer

relational

Question 51

when convert many-to-many binary relationship relationship to relational tables, Step 3 each attribute of entity becomes a(n)

Accepted Answer

column in relational table

Question 52

when convert many-to-many binary relationship relationship to relational tables, Step 4 unique identifier of entity becomes

Accepted Answer

primary key of relational table (these are 2 original entities not association entity)

Question 53

when convert many-to-many binary relationship relationship to relational tables, Step 5 make primary key columns of association table

Accepted Answer

foreign keys back to their respective "parent" tables

Question 54

when convert many-to-many binary relationship relationship to relational tables, Step 6 each attribute of

Accepted Answer

intersection data becomes a column in association table

Question 55

when converting a one-to-one unary relationship, Step1 convert __ into a relational table

Accepted Answer

entity

Question 56

when converting a one-to-one unary relationship, Step 2 add columns to table for __ __ in entity

Accepted Answer

each attribute

Question 57

when converting a one-to-one unary relationship, Step 3 make unique identifier of entity primary key of

Accepted Answer

that entity's relational table

Question 58

when converting a one-to-one unary relationship, Step 4 take primary key from entity and

Accepted Answer

place it as a foreign key in table (you will have to change name so you don't have 2 columns w/same name)

Question 59

when converting a one-to-one unary relationship, Step 5 __ __ __ on foreign key column

Accepted Answer

create unique index

Question 60

when converting a one-to-one unary relationship, Step 6 determine if foreign key column should

Accepted Answer

allow null values

Question 61

unary relationships will have

Accepted Answer

only one relational table

Question 62

means 2 columns represent same thing

Accepted Answer

same domain of values

Question 63

when implementing one-to-many binary relationship, Step 1 is to convert

Accepted Answer

entity to a relational table

Question 64

when implementing one-to-many binary relationship, Step 2 each attribute of

Accepted Answer

entity becomes column in relational table

Question	Answer
take ERDs & create relational tables that implement organization's business rules; represents ideal	goal of logical database design
store all the organizations data in a non-redundant manner	relational tables
implemented via foreign keys	relationships btwn entities in relational tables
relationships btwn entities are shown by lines in diagrams	in ERDs
logical design is __ __ implemented	seldom actually
logical design does not consider	performance requirements for database
logical design meets organization's __ requirements but may not meet organization's __ requirements	data; performance
usually measured in terms of response time/how quickly something is completed	performance
technically __ __ should not be added as part of logical design	surrogate keys
reason surrogate keys are necessary are	performance related
entities from ERD always become a(n) __ & implement relationship btwn entities via __ keys	table; foreign
identifier of entity becomes	table's primary key
attributes of entity become	columns in table
1 of 2 ways one-to-one binary relationship can be converted to relational tables, 2 entities become individual relational tables & relationship is implemented by	placing primary key of #1 table as a foreign key in #2 table
1 of 2 ways one-to-one binary relationship can be converted to relational tables, 2entities become individual relational tables & relationship is implemented by placing	primary key of #2 table as a foreign key in #1 table
database design should strive to reduce	number of null values likely to occur
better design of one-to-one binary relationship converted to relational tables is when there are __ null values	fewer
placement of a foreign key implements	referential integrity
by setting referential integrity constraint index to be unique it will	not allow duplicate values
not allowing duplicate values is a(n)	important part of creating one-to-one aspect of relationship
foreign keys are created in a database as __	constraints
constraints can be created by	using SQL when table is built
constraints can be added	later after table is built
sometimes adding foreign key constraints later can be problematic if	data already exists in a table because that data may violate foreign key rules
data must first "cleaned" before	adding foreign key constraint
most relational databases today have graphical user interface tool that provides simpler way to	interact w/database & create constraints (as well as process any other SQL type command)
when converting one-to-one binary relationship if one of entities is optional, step 1	convert each entity into a relational table
when converting one-to-one binary relationship if one of entities is optional, step 2	add columns to table for each attribute in entity
when converting one-to-one binary relationship if one of entities is optional, step 3	make unique identifier of each entity primary key of that entity's relational table
when converting one-to-one binary relationship if one of entities is optional, step 4	take primary key from optional side & place as foreign key in entity on required side, creating referential integrity constraint
if both entities are optional, when converting one-to-one binary relationship, place foreign key	such that occurrence of null values minimized
when converting one-to-one binary relationship if one of entities is optional, step 5	set foreign key to be unique
when converting one-to-one binary relationship if one of entities is optional, step 6	determine if null values should be allowed in foreign key column
if neither of entities is optional, when converting one-to-one binary relationship, hard part is deciding	where to place foreign key
since both sides of relationship are required, when converting one-to-one binary relationship, null values	will not be an issue
from a database design standpoint, when converting one-to-one binary relationship, if neither entity is optional	it makes no difference which table has foreign key
most common type of relationship implemented in relational databases	one-to-many binary relationship
in relational database implementation, one-to-many binary relationship	represents the ideal
when converting a one-to-one binary relationship, Step 1 is to convert	each entity to relational table
when converting a one-to-one binary relationship, Step 2 each attribute of entity	becomes column in relational table
when converting a one-to-one binary relationship, Step 3 unique identifier of entity becomes	primary key of relational table
when converting a one-to-one binary relationship, Step 4 take unique identifier from entity	on one side & place it in relational table representing entity on many side
when converting a one-to-one binary relationship, Step 5 determine if	foreign key field should allow null values (foreign key will not be unique)
many-to-many binary relationship cannot be implemented directly as relational tables	without introducing massive amounts of duplicate data
solution massive amounts of duplicate data, when converting many-to-many binary relationship, is to	break many-to-many binary relationship into 2 one-to-many relationships
when converting many-to-many binary relationship & many-to-many binary relationship is broken into 2 one-to-many relationships the result is	3rd table called intersection table
when intersection table created, when converting many-to-many binary relationship, unique identifier of this new entity is always	unique identifier of entity on left plus unique identifier of entity on right
when convert many-to-many binary relationship relationship to relational tables, Step 1 is to convert one many-to-many binary relationship into	two one-to-many binary relationships by adding an association entity
when convert many-to-many binary relationship relationship to relational tables, Step 1a unique identifier of association entity will be at least	unique identifier of entity on left plus same of entity on right
when convert many-to-many binary relationship relationship to relational tables, Step 2 convert each entity to a(n) __ table	relational
when convert many-to-many binary relationship relationship to relational tables, Step 3 each attribute of entity becomes a(n)	column in relational table
when convert many-to-many binary relationship relationship to relational tables, Step 4 unique identifier of entity becomes	primary key of relational table (these are 2 original entities not association entity)
when convert many-to-many binary relationship relationship to relational tables, Step 5 make primary key columns of association table	foreign keys back to their respective "parent" tables
when convert many-to-many binary relationship relationship to relational tables, Step 6 each attribute of	intersection data becomes a column in association table
when converting a one-to-one unary relationship, Step1 convert __ into a relational table	entity
when converting a one-to-one unary relationship, Step 2 add columns to table for __ __ in entity	each attribute
when converting a one-to-one unary relationship, Step 3 make unique identifier of entity primary key of	that entity's relational table
when converting a one-to-one unary relationship, Step 4 take primary key from entity and	place it as a foreign key in table (you will have to change name so you don't have 2 columns w/same name)
when converting a one-to-one unary relationship, Step 5 __ __ __ on foreign key column	create unique index
when converting a one-to-one unary relationship, Step 6 determine if foreign key column should	allow null values
unary relationships will have	only one relational table
means 2 columns represent same thing	same domain of values
when implementing one-to-many binary relationship, Step 1 is to convert	entity to a relational table
when implementing one-to-many binary relationship, Step 2 each attribute of	entity becomes column in relational table
when implementing one-to-many binary relationship, Step 3 unique identifier of entity __ __ __ of relational table	becomes primary key
when implementing one-to-many binary relationship, Step 4 take primary key from entity &	place it as a foreign key in table (you will have to change name so you don't have 2 columns w/same name)
when implementing one-to-many binary relationship, Step 5 determine if referential integrity constraint created by foreign key	needs to be unique; in one-to-many relationship, foreign key column will not be unique
when implementing one-to-many binary relationship, Step 6 determine if referential integrity constraint created by foreign key	should allow null values
unary __ relationship is not commonly found in business environment	many-to-many
when there is a unary relationship, there would normally only be	one relational table
many-to-many relationship requires use of a(n) __ table	intersection
when creating relational tables for unary relationship, __ __ add a relational table for unary entity	Step 1
when creating relational tables for unary relationship, __ __ convert each attribute to column in relational table	Step 2
when creating relational tables for unary relationship, __ __ convert unique identifier of entity to primary key of relational table	Step 3
when creating relational tables for unary relationship, __ __ add relational table representing association entity, which will be a(n) __ __	Step 4; intersection table
when creating relational tables for unary relationship, __ __ make primary key of intersection table same as primary key of original table	Step 4a
when creating relational tables for unary relationship, __ __ add primary key of original table to this table as foreign key & make this a part of primary key (you will have to change the name so you don't have 2 columns w/same name.)	Step 4b
when creating relational tables for unary relationship, __ __ convert each piece of intersection data, if any, to a column in this table	Step 4c
when creating relational tables for unary relationship, __ __ determine if referential integrity constraint created by foreign key needs to be unique	Step 5
in Step 5 of creating relational tables for unary relationship, individual column(s) from intersection table will not be unique, but combination of column(s) will be unique because	column(s) combined will be primary key
when creating relational tables for unary relationship, Step 6 determine if referential integrity constraint created by foreign key should allow null values; null values will __ __ __ because column will be part of the __ key	not be allowed; primary
represents combination of at least 2 other objects & contains data about that combination; often used in contracting & assignment applications	association entity
table pattern where intersection table contains additional attributes beyond attributes that make up composite primary key	association relationship
relation in 3NF in which every determinant is a candidate key	Boyce-Codd Normal Form (BCNF)
row, record, or node on many side of one-to-many relationship	child
includes data types, null status, default values, & constraints on values; must be specified	column property
data values in some columns that may be subject to restrictions on values that can exist in those columns	data constraint
column property usually set when relation created; indicated what will be stored in that column	data type
graphical display of tables & their relationships	database design
value that DBMS automatically supplies when new row created	default value
process of intentionally designing relation that is not normalized; done to improve performance/security	denormalization
relation in which all constraints are logical sequences of domain & keys; relation in which determinants of all functional dependencies are candidate keys	domain/key normal form (DK/NF)
necessary to eliminate anomaly where table can be split apart but not correctly joined back together	fifth normal form (5NF)
any table that fits definition of a relation	first normal form (1NF)
relation in BCNF in which every multivalue dependency is functional dependency	fourth normal form (4NF)
relation used to represent many-to-many relationship; contains keys of relations in relationship	intersection table (definition)
exists where strong entity has a multivalued composite group, &/or one of elements of composite group is an identifier of another strong entity	mixed entity pattern
condition in relation w/3 or more attributes in which independent attributes appear to have relationships they do not have	multivalued dependency
NULL/NOT NULL; column property usually set when when table created	NULL status
row, record, or node on one side of one-to-many relationship	parent
2D array that contains single-value entries & no duplicate rows; meaning of column is same in every row; order of rows & columns is immaterial	relation
relation in 1NF in which all non-key attributes are dependent on all keys	second normal form (2NF)
unique, system-supplied identifier used as primary key of relation; values have no meaning to users & usually are hidden on forms/reports	surrogate key
database structure of rows & columns to create cells that hold data values	table
relation in 2NF that had no transitive dependencies	third normal form (3NF)
in relation having at least 3 attributes, situation in which A determines B and B determines C, but B does not determine A	transitive dependency
fifth normal form (5NF) aka	Project-Join Normal Form (PJ/NF)
when an intersection table is used to represent entities having many-to-many relationship, it may have __ __ if relationship contains data	non-key date
exemplifying multivalued dependency, in a relation R (A, B, C) having key (A, B, C) where A is matched w/multiple values of B/C/both, B __ __ determine C, and C __ __ determine B	does not; does not
surrogate keys are assigned, by a DBMS, when a row is __	created
surrogate keys, assigned by a DBMS, are __ when a row is deleted	deleted
surrogate keys would be ideal primary keys except, the numbers generated have no __ meanings, so they cannot be interpreted in a(n) __ ways	intrinsic; meaningful
surrogate keys would be ideal primary keys except, values may not	be unique between 2 databases
if you set column as NOT NULL when you do not know the __ __ at time row being created, you will not be able to __ row	data value; row
some columns may appear as needing to be NOT NULL but must actually	be specified as NULL
default value may be a(n) __ value	static
default value may be one __ by application logic	calculated
default values are usually set when table is	actually created in the database
data constraints are usually set when table is actually created	in the database
it is possible to take normalization	too far
if weak entity is not ID dependent it can be represented as a table, as long as the dependency is recorded in relational design so that no application will	create weak entity w/out its proper parent
when weak entity is not ID dependent & is represented as a table, a business rule will need to be implemented so that when parent is deleted	weak entity is also deleted
when creating a table for a weak entity that is ID dependent, must ensure identifier of parent & identifier of ID dependent weak entity	appears in the table
for a weak entity that is ID dependent entity necessary to add primary key of	parent entity to weak entity's table, becoming part of weak entity's key
N:1 represented same way as one-to-many relationship	many-to-one relationship
to actually implement 1:1 relationship in database, must __ __ of designated foreign key as UNIQUE	constrain values
participants in relationship arise from same entity class; types 1:1, 1:N, & N:M	recursive relationship
only difference between recursive & nonrecursive 1:1 relationships is that	child & parent rows reside in same table
recursive relationships are represented same way as other relationships, except that some are __ rows & some are __ rows	parent; child
if a key, in a recursive relationship, is supposed to be a parent key & the row has no parent, its value	is NULL
if a key, in a recursive relationship, is supposed to be a child key & row has no child	its value is NULL
if you need to denormalize a relation, it will end up having	insertion, update, & deletion problems
denormalization makes sense if benefit of not normalizing __ possible problems that could be caused by such modifications	outweighs
weak entities are represented by a(n)	table
non-ID-dependent entities must have their __ __ recorded as business rules	existence dependence
supertypes & subtypes are each represented by __ __	separate tables
identifier of supertype entity becomes	primary keys of supertype table
__ of subtype entities become primary key of subtype tables	identifier
primary key of each subtype is primary key used for	each supertype
primary key for each subtype serves as foreign key that	links subtype back to supertype
to represent 1:1 binary relationship you place	key of one table into other table
to implement 1:1 binary relationship you specified foreign key must be	constrained as UNIQUE
to represent a 1:N binary relationship you place	key of parent table into child table
to represent a N:M binary relationship you create a(n)	intersection table that contains keys of the 2 other tables
to represent 1:1, 1:N recursive relationships, add foreign key to	relation that represents entity
to represent N:M recursion, create intersection table that	represents M:N relationship
how entities are transformed into tables when creating table for each entity	specifying primary/surrogate key & properties for each column, then verify normalization
how entities are transformed into tables when creating relationship by placing foreign keys	create relationship by placing foreign keys, strong entity relationships (1:1, 1:N, N:M), ID-dependent or nondependent weak entity relationships, subtypes, recursive (1:1, 1:N. N:M)
how attributes are transformed into columns	each attribute in an entity becomes a column in the corresponding table
data type, null status, default values, and any constraints on the values	column properties to take into account when transforming attributes into columns
foreign key is	an attribute in one relation that is a primary key of a different relation
for 1:N relationship, must place key of parent table in child table rather than placing key of child table in parent table because	there is literally not enough room in parent, since can only have 1 value/cell in relational model
meaning of term intersection table	relation that represents intersection of 2 entities having M:N relationship; each row of table represents 1 line in instance diagram showing connections btwn related entities
5.25 Why is it not possible to represent N:M relationships with the same strategy used to represent 1:N relationships?	there is only room for 1 value/cell in relation, not enough room; thus neither of 2 relations in relationship can hold foreign key values referencing other relation
equivalent of an intersection table with additional columns beyond the composite primary key; can be, and indeed must be, included in a data model	associative entity
association relationship	pattern of 2/more strong entities related to each other via an associative entity

Question

Question

Question

DatabaseConceptsCh5

Logical Database Design

"Know" box contains:
Time elapsed:
Retries: