Question
click below
click below
Question
Normal Size Small Size show me how
DatabaseConceptsCh4
Data Modeling & Entity-Relationship Model
Question | Answer |
---|---|
value that describes entity's characteristics; column of relation | attribute |
relationship btwn exactly 2 entities or tables | binary relationship |
binary relationship, max/min number of elements allowed on each side of relationship | cardinality |
a row, record, or node on many side of one-to-many relationship | child entity |
system requirements are used to create data model | component design stage |
identifier of entity instance that consists of 2/more attributes | composite identifier |
vertical line (│), circle (0), & crows foot (⋗) | crow's foot symbols |
representation content, relationships, & constraints of data needed to support requirements of users' & their data; usually expressed in terms of entity-relationship model | data model |
graphical display of tbls, relationships, & constraints; incl tbl name & names of all tbl columns, data types & properties of columns, description of primary & foreign keys | database design |
consists of 3 major stages: requirements analysis, component design, & implementation | database development process |
in entity-relationship model, number of entity classes participating in a relationship | degree |
in entity-relationship model, attribute of supertype entity that determines which subtype pertains to supertype | discriminator |
in entity-relationship model, are restricted to things that cannot be represented by single table | entity |
grouped entities of same type | entity class |
occurrence of particular entity | entity instance |
graphical used to represent entities & their relationships | entity-relationship (E-R) diagram |
constructs & conventions used to create model of users' data;most popular; 1st pub by Peter Chen in 1976 | entity-relationship model |
supertype instances is related to at most one subtype | exclusive subtype |
set of constructs & conventions used to create data models; things in users' world are rep by entities, & associations among those things are rep by relationships | extended entity-relationship (E-R) model |
relationship btwn 2 entities/objects that are of different logical types; entity instance has a relationship to 2nd entity instance | HAS-A relationship |
entity that cannot logically exist without existence of another entity | ID-dependent entity |
attributes that name or identify entity instances; consists of 1/more of entity's attributes | identifier |
relationship that is used when child entity if ID-dependent upon parent entity; weak entity contains the identifier (primary key) of its parent within its own identifier (primary key) | identifying relationship |
system of symbology used to construct E-R diagrams in data modeling & database design | IE Crow's Foot model |
last stage of database development; database constructed & filled w/data; queries, forms, & reports created; application programs written; all are tested | implementation stage |
supertype instance can relate to 1/more subtypes | inclusive subtype |
an E-R model developed by James Martin 1990; uses crow's feet to show many side of relationship | Information Engineering (IE) model |
incorporates basic ideas of E-R model but uses different graphical symbols that make it difficult to understand & use; often used in government work | Integrated Definition 1, Extended (IDEF1X) |
relationships that contain supertype & subtype, because subtype is same entity as supertype | IS-A relationship |
when minimum cardinality = 0, entity is when min cardinality is 1/another number (not 0) entity required to have relationship w/another specified entity, making this relationship __ | mandatory |
in relationship btwn tables, max number of rows to which a row of one table can relate in the other table | maximum cardinality |
in relationship btwn tables, min number of rows to which a row of one table can relate in the other table; AKA modality | minimum cardinality |
in data modeling, relationship btwn 2 entities such that one is not ID-dependent on the other; weak entity does not contain the identifier (primary key) of its parent within its own identifier (primary key) | nonidentifying relationship |
identifier that determines set of entity instances | nonunique identifier |
when minimum cardinality = 0, entity is not required to have relationship w/another specified entity, making this relationship __ | optional |
row, record, or node on one side of one-to-many relationship | parent entity |
relationship among entities, objects, or rows of same type; entity has relationship to self | recursive relationship |
association btwn 2 entities, objects, or rows of relation | relationship |
association among entity classes | relationship class |
association among entity instances; specific relationship btwn 2 tables in database | relationship instance |
system users are interviewed & sample forms, reports, queries, & descriptions of update activities are obtained | requirements analysis stage |
in entity-relationship model, any entity whose existence in database does not depend on instance of any other entity | strong entity |
generalization hierarchies, an entity/object that logically contains subtypes | supertype entity |
relationship btwn 3 entities | ternary relationship |
single entity involved in a relationship; recursive relationship | unary relationship |
object-oriented methodology, adopted E-R model but introduced own symbols while putting object-oriented spin on it | Unified Modeling Language (UML) |
identifier that determines exactly one entity instance | unique identifier |
descriptions of ways users will employ features & functions of new info system; consists of description of roles of users will play when utilizing new system together w/descriptions of activities scenarios | use case |
in entity-relationship model, entity whose logical existence in database depends on existence of another entity | weak entity |
maximum cardinality can be | 1:1, 1:N, N:1, or N:M |
minimum cardinality can be | optional/optional, optional/mandatory, mandatory/optional, or mandatory/mandatory |
data model is sometimes called | users' data model |
in database design, tables are shown in __ & relationships shown using __ | rectangles; lines |
in database design, a many relationship is shown with __ __ on end of line | crow's foot |
in database design, an optional relationship is depicted by a(n) __ | oval |
in database design, mandatory relationship is shown with __ __ | hash marks |
entities in entity-relationship (E-R) diagram, usually shown in __ or __ | rectangles; squares |
cardinality of relationship, in entity-relationship (E-R) diagram, usually shown inside __ __ | the diamond |
__ of entity-relationship model & extended entity-relationship model usually documented in entity-relationship diagram | results |
to be an ID-dependent entity, __ of entity must contain the identifier on which it __ | identifier; depends |
ID-dependent entity are __ of a weak entity | subsets |
most organizations use __ __ version like crow's foot model | simpler E-R |
UML is __ & set of tools for such development | methodology |
UML incorporates entity-relationship model for data __ | modeling |
data model is usually a(n) __ __ of a complex object or process | simplified representation |
for a business, data modeling is a way to represent entities that comprise business & way these entities __ | interrelate |
important concepts of entities, entity set, and attributes | key to understanding data modeling |
entire population of a single entity type | entity set |
requires the capture and storage of the proper data | creation of information |
data modeling allows us to __ data required in a business | describe |
data modeling allows us to __ our design | test |
data modeling allows us to __ __ quickly without incurring a large cost | make changes |
data modeling tool most often used is | Entity Relationship Diagram (ERD) |
will indicate a fact about some aspect of business or it may indicate some limit on business | business rule |
often business rules are result of __ __ | regulatory requirements |
may also indicate implementation of a business rule | calculations |
database should __ business rules & make them easy to implement | reinforce |
business rules will be identified during __ __ process of a project | requirements gathering |
any policies company follows may translate into __ __ as it is likely policy was developed to implement a(n) __ __ | business rules; business rule |
generally do not translate into business rules | company procedures |
using primary key, foreign key, check constraint, database trigger, or stored procedure | business rules can be implemented |
small software capabilities built into the database so programmers don't have to write the code in application programs | database triggers & stored procedures |
minimum number of entity occurrences in the relationship | modality |
relationship btwn 2 entities is most __ __ | common occurrence |
less common than a binary relationship is a(n) __ relationship | ternary |
ternary is derived from __ | three |
derived from unit or one | unary |
for __ __ __, you are able to have unary, binary, or ternary entities involved | degree of relationship |
determined by examining one of entities in relationship & seeing how many of other entities related | maximum cardinality of relationship |
maximum cardinality is important because __ __ foreign keys in database design | helps place |
determined by examining 1 entity in relationship & seeing how few of other entities related | minimum cardinality of relationship |
minimum cardinality is important because it helps to determine __ __ __ in database design | referential integrity options |
from a design standpoint, it is only important to know if minimum cardinality is | 0 / > 0 |
we need to know if entity's participation in relationship is optional/mandatory, based on whether | minimum cardinality is 0 / > 0 |
__ relationships represent ideal as far as relational databases are concerned | binary |
one-to-one, one-to-many, or many-to-many are ways to describe | unary & binary relationships |
there can be a(n) one-to-__ unary & binary relationship | one |
there can be a(n) one-to-__ unary & binary relationship | many |
there can be a(n) many-to-many __ & binary relationship | unary |
entity relationship diagram (ERD) is used to demonstrate __, __, & __ unary & binary relationships | one-to-one; one-to-many; many-to-many |
to model data & business rules so a logical design of a database can be built | purpose of ERD |
Crow's foot symbol, __ __, represents one | vertical line |
Crow's foot symbol, __, represents zero | circle |
Crow's foot symbol, __ __, represents many | crows foot |
Crow's foot symbols will always be | used in pairs |
there are __ __ combinations of Crow's foot pairs | specific legal |
maximum & minimum cardinality is one; represents an entity that is mandatory | straight horizontal line with 2 vertical lines to far right |
maximum cardinality is many & minimum cardinality is one; represents an entity that is mandatory | straight horizontal line with 1 vertical line & reversed crow's feet to far right |
maximum cardinality is one & minimum cardinality is zero; represents an entity that is optional | straight horizontal line with circle then horizontal line continued with 1 vertical line to far right |
maximum cardinality is many & minimum cardinality is zero; represents an entity that is optional | straight horizontal line with circle then reversed crow's feet to far right |
ID-dependent & non-ID-dependent are | two basic types of weak entities |
ID-dependent means weak entity has identifier (primary key) from its parent entity as | part of its identifier (primary key) |
entity with __ __ can indicate it is a weak entity | rounded edges |
some design methods use a rectangle with two borders to | indicate a weak entity |
weak entity becomes an ID-dependent weak entity when | identifier (primary key) of its parent must be added |
ID-dependent weak entities always have __ __ with their parent entity | identifying relationships |
Non-ID-dependent weak entities are harder to identify because they do not have | their parent entity's identifier (primary key) as part of their identifier (primary key) |
Non-ID-dependent weak entities always have __ __ with their parent entity | non-identifying relationships |
ID-dependent weak entities will always have a __ __ (primary key) | composite identifier |
Non-ID-dependent weak entities __ or __ __ have a composite identifier | may; may not |
__ ID-dependent entities are weak entities | all |
all weak entities will have a(n) | minimum cardinality of one |
all weak entities will have a(n) | maximum cardinality of one |
since weak entities have a minimum & maximum cardinality of __, there is one & only one __ __ for each weak entity and this parent is required to exist | one; parent entity |
represent the minimum and maximum cardinality for entity | two vertical lines opposite an entity |
once the database is created we must make sure that the __ __ of a weak entity is added to the database __ any row for a related weak entity row | parent row; before |
once the database is created we must make sure that if the parent row of a weak entity is __ __ the parent table, the row (or rows) representing the weak entity must also __ __ (first) | removed from; be removed |
ID-dependent weak entity will contain | identifier of its parent as part of its identifier & this identifier will be foreign key back to parent |
non-ID-dependent weak entity will contain | identifier of its parent as a separate attribute; will be separate column not part of its identifier & this column will be a foreign key back to parent |
will have foreign keys to parent entity | key point of both types of weak entities |
once established, insures that a value contained in foreign key column must match value stored in primary key column it references | referential integrity |
must ensure referential integrity is set for foreign keys of weak entities so that | constraint/error check will be created in database such that weak entity cannot be added unless corresponding parent entity already exists |
another side to referential integrity setting options DB software to automatically maintain | integrity between foreign key row & its related primary key row |
integrity between foreign key row & its related primary key row come into play when | processing updates & deletes on one side of one-to-many relationship |
relational database management systems usually have, at least, __ __ that can be executed against rows on many side when deletes/updates occur on one side | five actions |
each of these actions maintain integrity btwn foreign & primary key | Restrict, No Action, Cascade, Set To Null, & Set To Default |
when dealing with delete/update of weak entities important to select either __ or __ option | Restrict; Cascade |
by selecting Restrict/Cascade option, when dealing w/delete/update of weak entities, DB will __ __ of row(s) rep weak entity & corresponding parent | maintain relationship |
when dealing w/update/delete of weak entities, Cascade option will automatically __ rows representing weak entity __ __ to parent | delete; in addition |
when Cascade option updates/deletes weak entity along w/corresponding parent, there will be __ __ weak entity with out a __ __ entity | no remaining; corresponding parent |
when dealing w/update/delete of weak entities, Restrict option will prevent __ __ from being deleted if any rows representing weak entity exist | parent row |
using either Restrict/Cascade will prevent a(n) __ from occurring in database | inconsistency |
whether to use Restrict or Cascade option, when dealing w/update/delete of weak entities, is usually a(n) __ __ made by project team & business users | business decision |
when dealing w/update/delete of weak entities, Restrict option is often selected if | users want manual control |
when dealing w/update/delete of weak entities, Restrict option will __ __ __ that informs user to check each row for weak entity ensuring it really can be deleted, before they manually delete row | generate error message |
when dealing w/Restrict option to update/delete weak entities, once all rows representing weak entity are __ __, row representing parent entity can be deleted | manually deleted |
professor at college assigned to an office, this is a(n) | example of binary relationship |
a one-to-one relationship exists based on | maximum cardinality involved |
it is maximum cardinality from each side of an ERD that | names the relationship |
initial ERD diagram should include __ & __ for all entities | names; identifiers |
initial ERD diagram should include __ __ __, other than name/identifiers | other entity attributes |
initial ERD diagram should include __ __ __, in addition to name/identifiers for all entities & other entity attributes | name for relationship |
minimum cardinality always goes __ maximum cardinality relative to entity | outside |
maximum cardinality always goes closest to | entity being represented |
finished ERD becomes input to __ __ __ phase of system development project | logical database design |
during __ __ design phase we will have formal rules to transform ERD into tables, primary keys, foreign keys, & referential integrity rules | logical database |
change as business rules change | ERDs |
exists based on maximum cardinality involved | one-to-many relationship |
means a maximum of more than one; also means there is no predetermined maximum, could be 10, 50, or 5 million | maximum of many |
foreign keys are __ __ in ERDs | not represented |
foreign keys are added in __ __ design based on the work done in __ | logical database; ERD |
database designer would be able to determine that __ __ are necessary based upon a well drawn data design document that includes __ __ ERDs | foreign keys; properly formed |
one-to-one relationship is __ because of maximum cardinality involved | determination |
even when a diagram doesn't have __ it is actually read in __ __ when used to build ERD | arrows; two directions |
means there is a maximum cardinality of one of 1st entity & maximum cardinality of "many" of 2nd entity | one-to-many cardinality in relationship |
because maximum cardinality involved is single entity, still means there is a maximum cardinality of one of 1st entity & maximum cardinality of "many" of 2nd entity | one-to-one unary relationship in relationship |
in one-to-one unary relationship, __ __ is more logical than physical | 2nd entity |
in one-to-one unary relationship, you don't want to physically create a new entity to represent 2nd entity; the "second" entity in a unary relationship is a(n) __ __ | imaginary entity |
in one-to-one unary relationship, when reading ERD from opposite direction the | relationship is optional |
one-to-many cardinality __ __ for unary, binary, or ternary relationships | can exist |
even though diagram __ __ __ it is actually read in two directions | doesn't have arrows |
__ __ relationship is not commonly found in business environment | unary many-to-many |
many-to-many would exists because maximum cardinality in __ __ of ERD is many | each direction |
many-to-many relationship requires use of a(n) __ __ | intersection table |
intersection table will have as its primary key, at a minimum, primary key of tables | involved in many-to-many relationship |
for many-to-many unary relationship, foreign key is placed in __ __ | association table |
foreign key, in many-to-many unary relationship, will create a(n) __ __ __ in the database | referential integrity constraint |
when dealing w/foreign key of many-to-many unary relationship, need to decide if referential integrity constraint needs to be | unique & whether column should allow null values |
1st step to create relational tables for many-to-many unary relationship | add relational table for unary entity |
2nd step to create relational tables for many-to-many unary relationship | convert each attribute to a column in relational table |
3rd step to create relational tables for many-to-many unary relationship | convert unique identifier of entity to primary key of relational table |
4th step to create relational tables for many-to-many unary relationship | add a relational table, intersection table, representing association entity |
1st step for creating intersection table | make primary key of table same as primary key of original table (original entity) |
2nd step for creating intersection table | add primary key of original table to table as foreign key; make this a part of primary key (change name so you don't have 2columns w/same name) |
3rd step for creating intersection table | convert each piece of intersection data, if any, to a column in this table |
5th step to create relational tables for many-to-many unary relationship | determine if referential integrity constraint created by foreign key needs to be unique; individual column(s) will not be unique but combination of column(s) will be unique because column(s) combined will be primary key |
6th step to create relational tables for many-to-many unary relationship | determine if referential integrity constraint created by foreign key should allow null values; null values will not be allowed because column will be part of primary key |
many-to-many relationship is __ __ in a relational database | never implemented |
many-to-many relationships can & are represented in __ | ERD (logical design) |
these relationships present a special problem related to their data attributes | unary/binary many-to-many relationships |
duplications are __ when implementing actual database | problematic |
data that only exists as a result of the relationship, __ __ occurs when you have a many-to-many relationship | almost always |
solution to data that only exists as a result of the relationship, occurring when you have a many-to-many relationship, is | to create intersection data |
data that exists due to relationship btwn 2 entities | intersection data |
additional entity, created by intersection data, is called a(n) | association entity |
__many-to-many relationship(s) will have intersection data | only |
__ __ many-to-many relationship(s) will have intersection data | not all |
Crow's Foot ERD symbol for entity | rectangle |
Crow's Foot ERD symbol for intersection data | pentagon |
can only be one or many; will only use vertical line or crow's foot symbols for | the maximum cardinality |
can only be zero (optional) or one; will use only circle/vertical line symbols | minimum cardinality (modality) |
there is ONLY __ __ __ __ each for maximum cardinality and minimum cardinality | one pair of symbols |
minimum cardinality __ __ maximum cardinality symbols; places them farther from entities | go inside |
maximum cardinality symbols __ __ minimum symbols; places them closer to entities | go outside |
every relationship btwn entities will have a(n) __ __ | relationship name |
symbol for optional is always placed on side __ __ that is actually optional | opposite entity |
only a many-to-many relationship | will have intersection data |
one-to-one & one-to-many relationships | cannot have intersection data |
not all many-to-many relationships | have intersection data |
doesn't matter which way | entities are positioned |
binary relationship btwn entity A & B, it doesn't matter | which is on left & which is on right |
have to make sure maximum cardinality & minimum cardinality __ __ for each entity - entity on right & entity on left | are correct |
working demonstrations of selected portions of futures; created during requirements phase & used to obtain feedback from system users | prototypes |
consist of limits on data values, referential integrity, & business rules | data constraints |
every purchased part will have a quotation from at least 2 suppliers, is a(n) | example of business rule |
during implementation stage __ __ is transformed into a database design consisting of tables, relationships, & constraints | data model |
users are trained, documentation is written, & system is installed for use | during implementation stage |
database development process is a subset of | systems development life cycle (SDLC) model |
identify users of new info system & interview them | sources of system requirements |
examples of existing forms, reports & queries are obtained during | interviews of users |
users should be asked about needs for | changes to existing forms, reports, & queries & new forms, reports & queries |
in __ __, inputs provided to system & outputs generated by system are defined | use cases |
sometimes __ of use cases are necessary | dozens |
use cases provide sources of __ | requirements |
use cases can also be used to __ data model, design & implementation | validate |
in addition to use case requirements, you need to document | characteristics of data items |
for each data item in a form, report, or query the team needs to determine its | data type, properties, & limits on values |
during process of establishing requirements system developers need to document | business rules than constraint actions on database activity |
business rules arise from | business policy & practice |
in order to design a database, requirements must be | transformed into data model |
when writing programs, __ __ must 1st be documented in flowcharts/object diagrams | program logic |
with a database __ __ must 1st be documented in data model | data requirements |
entities, attributes, identifiers, & relationships are | most important elements of E-R model |
an entity is something that | users want to track |
entity class is collection of entities described by __ of entities within that class | structure |
there are usually __ __ of an entity in an entity class | many instances |
entities are usually __ of 1/more forms or reports, or are __ __ in 1/more forms or reports | subject; major section |
E-R model assumes that all __ of given entity class have same __ | instances; attributes |
attribute has __ __ & __ that are determined from requirements | data type; properties |
specify whether attribute is required, has a default value, value has limits, & any other constraint | properties |
one or more attributes that users think of as a name of entity, making an identifier a(n) | logical concept |
logical concept identifiers, may/may not be represented as __ in database design | keys |
primary & candidate keys must be unique, whereas identifiers | might/might no be unique |
entities are portrayed in __ __ of detail in a data model | three levels |
when an entity & all its attributes are displayed, identifiers of attribute is shown at __ of entity and horizontal line drawn __ identifier | top; after |
in large data models, all entities attributes can make data diagrams unmanageable, so entity diagram is | abbreviated by showing just identifier |
E-R model contains __ classes & instances | relationship |
in original specifications of E-R model, relationships __ __ attributes | could have |
in modern practice, E-R model, __ __ have attributes | only entities |
relationship class can __ __ entity classes | involve many |
principle difference btwn entity & table is that you can express relationship btwn entities w/out | using foreign keys |
when creating data model, 1st | identify entities |
when creating data model, 2nd | think about relationships |
when creating data model, 3rd | determine attributes |
in logical data modeling, you are able to show relationships btwn entities | before you know what identifiers are |
characteristic of logical data modeling allows you to work from | general to specific |
in E-R diagrams, __ __ are shown using rectangles | entity classes |
in E-R diagrams, relationships are shown using __ | diamonds |
in E-R diagrams, maximum cardinality is shown __ __ | inside diamond |
in E-R diagrams, minimum cardinality is shown using __ or __ __ next to entity | oval; hash mark |
in E-R diagrams, name of entity is shown __ __ | inside rectangle |
in E-R diagrams, name of relationship is shown __ __ | near diamond |
Information Engineering (IE) model is sometimes called IE __ __ model | Crow's Foot |
other significant variations of E-R model include | IDEF1X & Unified Modeling Language |
version of E-R model, adopted as national standard 1993, but difficult to understand & use | IDEF1X |
begun to receive widespread use among object-oriented programming (OOP) practitioners, may encounter notation in systems development courses | UML |
because 2 products that both implement IE Crow's Foot model may do so in different ways, when creating data model diagram need to know | version of E-R model & idiosyncrasies of data modeling product |
there is no __ __ set of symbols for IE Crow's Foot notation | completely standard |
produced by Computer Associates; commercial data modeling product that handles both data modeling & database design tasks; can be used to produce either IE Crow's Foot or IDEF1X diagrams | ERwin |
special case of supertype entity; used in data model to avoid inappropriate NULL values | subtype entity |
identifier of supertype & all its subtypes | must be the same |
supertype & all its subtypes all represent | different aspects of same entity |
IS-A & HAS-A are different because in HAS-A relationships | identifiers of 2 entities are different |
recursive relationships can be | 1:1, 1:N & N:M |
having __ __ is common during data modeling process | missing facts |
there is no need to __ data modeling when something is __ | stop; unknown |
as more forms & reports are obtained __ __ & other changes will need to be made to data model | new attributes |
when creating data model need to lookout for business rules that constrain | data values & processing of database |
after data model completes it needs | to be validated |
most common way to validate data model is to | show to users & obtain their feedback |
often data models need to be broken into sections or be expressed in | more understandable terms |
prototypes are __ for users to understand & evaluate than data models | easier |
prototypes can be developed to show __ of data model designs w/out requiring user to learn E-R modeling | consequences |
lastly, data model needs to be validated against | all use cases |
for each use case, need to verify that all data & relationships necessary to support use case are | present & accurately represented in data model |