Busy. Please wait.
or

show password
Forgot Password?

Don't have an account?  Sign up 
or

Username is available taken
show password

why

Make sure to remember your password. If you forget it there is no way for StudyStack to send you a reset link. You would need to create a new account.

By signing up, I agree to StudyStack's Terms of Service and Privacy Policy.


Already a StudyStack user? Log In

Reset Password
Enter the associated with your account, and we'll email you a link to reset your password.

Remove ads
Don't know
Know
remaining cards
Save
0:01
To flip the current card, click it or press the Spacebar key.  To move the current card to one of the three colored boxes, click on the box.  You may also press the UP ARROW key to move the card to the "Know" box, the DOWN ARROW key to move the card to the "Don't know" box, or the RIGHT ARROW key to move the card to the Remaining box.  You may also click on the card displayed in any of the three boxes to bring that card back to the center.

Pass complete!

"Know" box contains:
Time elapsed:
Retries:
restart all cards




share
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

DatabaseConceptsCh5

Logical Database Design

QuestionAnswer
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
Created by: lfrancois