Help
Options
Didn't know it?
click below
click below
Knew it?
click below
click below
Don't Know
Remaining cards (0)
Know
retry
shuffle
restart
0:00
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
Normal Size Small Size show me how
DatabaseConceptsCh5
Logical Database Design
| 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 |
Created by:
lfrancois
Popular Miscellaneous sets