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!
|
|
||||
|---|---|---|---|---|---|
| take ERDs & create relational tables that implement organization's business rules; represents ideal | show 🗑
|
||||
| store all the organizations data in a non-redundant manner | show 🗑
|
||||
| implemented via foreign keys | show 🗑
|
||||
| relationships btwn entities are shown by lines in diagrams | show 🗑
|
||||
| show | seldom actually
🗑
|
||||
| logical design does not consider | show 🗑
|
||||
| logical design meets organization's __ requirements but may not meet organization's __ requirements | show 🗑
|
||||
| usually measured in terms of response time/how quickly something is completed | show 🗑
|
||||
| show | surrogate keys
🗑
|
||||
| reason surrogate keys are necessary are | show 🗑
|
||||
| entities from ERD always become a(n) __ & implement relationship btwn entities via __ keys | show 🗑
|
||||
| identifier of entity becomes | show 🗑
|
||||
| show | 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 | show 🗑
|
||||
| show | primary key of #2 table as a foreign key in #1 table
🗑
|
||||
| show | 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 | show 🗑
|
||||
| show | referential integrity
🗑
|
||||
| by setting referential integrity constraint index to be unique it will | show 🗑
|
||||
| show | important part of creating one-to-one aspect of relationship
🗑
|
||||
| show | constraints
🗑
|
||||
| show | using SQL when table is built
🗑
|
||||
| constraints can be added | show 🗑
|
||||
| sometimes adding foreign key constraints later can be problematic if | show 🗑
|
||||
| data must first "cleaned" before | show 🗑
|
||||
| most relational databases today have graphical user interface tool that provides simpler way to | show 🗑
|
||||
| when converting one-to-one binary relationship if one of entities is optional, step 1 | show 🗑
|
||||
| when converting one-to-one binary relationship if one of entities is optional, step 2 | show 🗑
|
||||
| show | 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 | show 🗑
|
||||
| if both entities are optional, when converting one-to-one binary relationship, place foreign key | show 🗑
|
||||
| show | set foreign key to be unique
🗑
|
||||
| show | 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 | show 🗑
|
||||
| since both sides of relationship are required, when converting one-to-one binary relationship, null values | show 🗑
|
||||
| show | it makes no difference which table has foreign key
🗑
|
||||
| show | one-to-many binary relationship
🗑
|
||||
| show | represents the ideal
🗑
|
||||
| when converting a one-to-one binary relationship, Step 1 is to convert | show 🗑
|
||||
| when converting a one-to-one binary relationship, Step 2 each attribute of entity | show 🗑
|
||||
| show | primary key of relational table
🗑
|
||||
| show | on one side & place it in relational table representing entity on many side
🗑
|
||||
| show | foreign key field should allow null values (foreign key will not be unique)
🗑
|
||||
| show | without introducing massive amounts of duplicate data
🗑
|
||||
| solution massive amounts of duplicate data, when converting many-to-many binary relationship, is to | show 🗑
|
||||
| show | 3rd table called intersection table
🗑
|
||||
| when intersection table created, when converting many-to-many binary relationship, unique identifier of this new entity is always | show 🗑
|
||||
| show | 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 | show 🗑
|
||||
| when convert many-to-many binary relationship relationship to relational tables, Step 2 convert each entity to a(n) __ table | show 🗑
|
||||
| show | column in relational table
🗑
|
||||
| when convert many-to-many binary relationship relationship to relational tables, Step 4 unique identifier of entity becomes | show 🗑
|
||||
| when convert many-to-many binary relationship relationship to relational tables, Step 5 make primary key columns of association table | show 🗑
|
||||
| show | intersection data becomes a column in association table
🗑
|
||||
| show | entity
🗑
|
||||
| when converting a one-to-one unary relationship, Step 2 add columns to table for __ __ in entity | show 🗑
|
||||
| show | that entity's relational table
🗑
|
||||
| when converting a one-to-one unary relationship, Step 4 take primary key from entity and | show 🗑
|
||||
| when converting a one-to-one unary relationship, Step 5 __ __ __ on foreign key column | show 🗑
|
||||
| when converting a one-to-one unary relationship, Step 6 determine if foreign key column should | show 🗑
|
||||
| unary relationships will have | show 🗑
|
||||
| show | same domain of values
🗑
|
||||
| when implementing one-to-many binary relationship, Step 1 is to convert | show 🗑
|
||||
| show | entity becomes column in relational table
🗑
|
||||
| show | becomes primary key
🗑
|
||||
| when implementing one-to-many binary relationship, Step 4 take primary key from entity & | show 🗑
|
||||
| when implementing one-to-many binary relationship, Step 5 determine if referential integrity constraint created by foreign key | show 🗑
|
||||
| show | should allow null values
🗑
|
||||
| show | many-to-many
🗑
|
||||
| show | one relational table
🗑
|
||||
| many-to-many relationship requires use of a(n) __ table | show 🗑
|
||||
| when creating relational tables for unary relationship, __ __ add a relational table for unary entity | show 🗑
|
||||
| show | Step 2
🗑
|
||||
| when creating relational tables for unary relationship, __ __ convert unique identifier of entity to primary key of relational table | show 🗑
|
||||
| when creating relational tables for unary relationship, __ __ add relational table representing association entity, which will be a(n) __ __ | show 🗑
|
||||
| when creating relational tables for unary relationship, __ __ make primary key of intersection table same as primary key of original table | show 🗑
|
||||
| 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.) | show 🗑
|
||||
| show | Step 4c
🗑
|
||||
| when creating relational tables for unary relationship, __ __ determine if referential integrity constraint created by foreign key needs to be unique | show 🗑
|
||||
| show | column(s) combined will be primary key
🗑
|
||||
| show | not be allowed; primary
🗑
|
||||
| show | association entity
🗑
|
||||
| show | association relationship
🗑
|
||||
| relation in 3NF in which every determinant is a candidate key | show 🗑
|
||||
| row, record, or node on many side of one-to-many relationship | show 🗑
|
||||
| includes data types, null status, default values, & constraints on values; must be specified | show 🗑
|
||||
| show | data constraint
🗑
|
||||
| column property usually set when relation created; indicated what will be stored in that column | show 🗑
|
||||
| graphical display of tables & their relationships | show 🗑
|
||||
| show | default value
🗑
|
||||
| show | denormalization
🗑
|
||||
| show | domain/key normal form (DK/NF)
🗑
|
||||
| necessary to eliminate anomaly where table can be split apart but not correctly joined back together | show 🗑
|
||||
| show | first normal form (1NF)
🗑
|
||||
| show | fourth normal form (4NF)
🗑
|
||||
| show | 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 | show 🗑
|
||||
| show | multivalued dependency
🗑
|
||||
| NULL/NOT NULL; column property usually set when when table created | show 🗑
|
||||
| show | parent
🗑
|
||||
| show | relation
🗑
|
||||
| relation in 1NF in which all non-key attributes are dependent on all keys | show 🗑
|
||||
| show | surrogate key
🗑
|
||||
| database structure of rows & columns to create cells that hold data values | show 🗑
|
||||
| show | 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 | show 🗑
|
||||
| fifth normal form (5NF) aka | show 🗑
|
||||
| when an intersection table is used to represent entities having many-to-many relationship, it may have __ __ if relationship contains data | show 🗑
|
||||
| show | does not; does not
🗑
|
||||
| show | created
🗑
|
||||
| show | deleted
🗑
|
||||
| surrogate keys would be ideal primary keys except, the numbers generated have no __ meanings, so they cannot be interpreted in a(n) __ ways | show 🗑
|
||||
| surrogate keys would be ideal primary keys except, values may not | show 🗑
|
||||
| show | data value; row
🗑
|
||||
| show | be specified as NULL
🗑
|
||||
| default value may be a(n) __ value | show 🗑
|
||||
| default value may be one __ by application logic | show 🗑
|
||||
| default values are usually set when table is | show 🗑
|
||||
| show | in the database
🗑
|
||||
| it is possible to take normalization | show 🗑
|
||||
| show | 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 | show 🗑
|
||||
| show | appears in the table
🗑
|
||||
| show | parent entity to weak entity's table, becoming part of weak entity's key
🗑
|
||||
| show | many-to-one relationship
🗑
|
||||
| to actually implement 1:1 relationship in database, must __ __ of designated foreign key as UNIQUE | show 🗑
|
||||
| participants in relationship arise from same entity class; types 1:1, 1:N, & N:M | show 🗑
|
||||
| show | child & parent rows reside in same table
🗑
|
||||
| recursive relationships are represented same way as other relationships, except that some are __ rows & some are __ rows | show 🗑
|
||||
| show | is NULL
🗑
|
||||
| show | its value is NULL
🗑
|
||||
| if you need to denormalize a relation, it will end up having | show 🗑
|
||||
| denormalization makes sense if benefit of not normalizing __ possible problems that could be caused by such modifications | show 🗑
|
||||
| weak entities are represented by a(n) | show 🗑
|
||||
| non-ID-dependent entities must have their __ __ recorded as business rules | show 🗑
|
||||
| show | separate tables
🗑
|
||||
| identifier of supertype entity becomes | show 🗑
|
||||
| __ of subtype entities become primary key of subtype tables | show 🗑
|
||||
| primary key of each subtype is primary key used for | show 🗑
|
||||
| primary key for each subtype serves as foreign key that | show 🗑
|
||||
| to represent 1:1 binary relationship you place | show 🗑
|
||||
| to implement 1:1 binary relationship you specified foreign key must be | show 🗑
|
||||
| show | key of parent table into child table
🗑
|
||||
| show | intersection table that contains keys of the 2 other tables
🗑
|
||||
| to represent 1:1, 1:N recursive relationships, add foreign key to | show 🗑
|
||||
| show | represents M:N relationship
🗑
|
||||
| show | specifying primary/surrogate key & properties for each column, then verify normalization
🗑
|
||||
| how entities are transformed into tables when creating relationship by placing foreign keys | show 🗑
|
||||
| how attributes are transformed into columns | show 🗑
|
||||
| data type, null status, default values, and any constraints on the values | show 🗑
|
||||
| foreign key is | show 🗑
|
||||
| show | there is literally not enough room in parent, since can only have 1 value/cell in relational model
🗑
|
||||
| show | 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
🗑
|
||||
| show | 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
🗑
|
||||
| show | associative entity
🗑
|
||||
| association relationship | show 🗑
|
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.
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
Normal Size Small Size show me how
Created by:
lfrancois
Popular Miscellaneous sets