Logical Database Design

Quiz yourself by thinking what should be in each of the black spaces below before clicking on it to display the answer.

Help!

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

🗑

Review the information in the table. When you are ready to quiz yourself you can hide individual columns or the entire table. Then you can click on the empty cells to reveal the answer. Try to recall what will be displayed before clicking the empty cell.

To hide a column, click on the column name.

To hide the entire table, click on the "Hide All" button.

You may also shuffle the rows of the table by clicking on the "Shuffle" button.

Or sort by any of the columns using the down arrow next to any column heading.
If you know all the data on any row, you can temporarily remove it by tapping the trash can to the right of the row.

Embed Code - If you would like this activity on your web page, copy the script below and paste it into your web page.

Normal Size Small Size show me how

Created by: lfrancois

Popular Miscellaneous sets

Review Words Vocabulary

CHS Root Words 10

CHS Root Words 2

CHS Root Words 9