Database Design Flashcards

Entity-relationship model
Terms Definitions
binary relationship
An ER term used to describe an association (relationship) between two entities. Example: PROFESSOR teaches COURSE
cardinality
Assigns a specific value to connectivity. Expresses the range (minimum to maximum) of allowed entity occurrences associated with a single occurrence ofthe related entity.
composite attribute
An attribute that can be further subdivided to yield additional attributes. For example, a phone number (615-898-2368) may be divided into an area code (615), an exchange number (898), and a four-digit code (2368). Compare to simple attribute.
composite identifier
In ER modeling, a key composed of more than one attribute.
connectivity
Describes the classification ofthe relationship between entities. Classifications include 1:1, 1:M, and M:N.
derived attribute
An attribute that does not physically exist within the entity and is derived via an algorithm. Example: Age = current date birth date.
existence-dependent
A property of an entity whose existence depends on one or more other entities. In an existence-dependent environment, the existenceindependent table must be created and loaded first because the existencedependent key cannot reference a table that does not yet exist.
existence-independet
An entity that can exist apart from one or more related entities. It must be created first when referencing an existence-dependent table to it
identifiers
The ERM uses identifiers to uniquely identify each entity instance. In the relational model, such identifiers are mapped to prin1ary keys in tables
identifying relationship
A relationship that exists when the related entities are existence-dependent. Also called a strong relationship or strong identifying relationship because the dependent entity's primary key contains the primary key ofthe parent entity
iterative process
A process based on repetition of steps and procedures
mandatory participation
A term used to describe a relationship in which one entity occurrence must have a corresponding occurrence in another entity. Example: EMPLOYEE works in DIVISION. (A person cannot be an employee if he or she is not assigned to a company's division.)
multivalued attribute
An attribute that can have many values for a single entity occurrence. For example, an EMP _DEGREE attribute might store the string "BBA, MBA, PHD" to indicate three different degrees held.
non-identifying relationship
A relationship that occurs when the primary key ofthe dependent (many side) entity does not contain the primary key of the related parent entity. Also known as a weak relationship.
optional attribute
In ER modeling, refers to an attribute that does not require a value, therefore it can be left empty.
optional participation
In ER modeling, refers to a condition where one entity occurrence does not require a corresponding entity occurrence in a particular relationship.
participant
An ER term used to label the entities that participate in a relationship. Example: PROFESSOR teaches CLASS. (The teaches relationship is based on the participants PROFESSOR and CLASS.)
recursive relationship
A relationship that is found within a single entity type. For example, an EMPLOYEE is married to an EMPLOYEE or a PART is a component of another PART.
relationship degree
ndicates the number of entities or participants associated with a relationship. A relationship degree can be unary, binary, ternary, or higher level.
required attribute
In ER modeling, refers to an attribute that must have a value. In other words, it cannot be left empty.
simple attribute
An attribute that cannot be subdivided into meaningful components. Compare to composite attribute.
single-valued attribute
An attribute that can have only one value.
strong relationship
When two entities are existence-dependent; from a database design perspective, this exists whenever the primary key ofthe related entity contains the primary key ofthe parent entity.
ternary relationship
An ER ternl used to describe an association (relationship) between three entities. Example: A CONTRIBUTOR contributes money to a FUND from which a RECIPIENT receives money.
unary relationship
An ER term used to describe an association within an entity. Example: A COlTRSE is a prerequisite to another COURSE.
weak entity
An entity that displays existence dependence and inherits the primary key of its parent entity. Example: A DEPENDENT requires the existence of an EMPLOYEE.
weak relationship
A relationship that exists when the PK ofthe related entity does not contain a PK component of the parent entity. Also known as a nonidentifying relationship.
ERD Entity Relationship Model 1 of three components
Entities
ERD Entity Relationship Model 1 of three components
Attributes
ERD Entity Relationship Model 1 of three components
Relationships
Entities
Refers to entity set and not to single entity occurrenceCorresponds to table and not to row in relational environmentIn Chen and Crow’s Foot models, entity represented by rectangle with entity’s nameEntity name, a noun, written in capital letters
Attributes
Characteristics of entitiesChen notation: attributes represented by ovals connected to entity rectangle with a lineEach oval contains the name of attribute it representsCrow’s Foot notation: attributes written in attribute box below entity rectangle
Required attribute
must have a value
Optional attribute
may be left empty
Domain
set of possible values for an attribute
Identifiers
one or more attributes that uniquely identify each entity instance
Composite Identifier
primary key composed of more than one attribute
Composite attribute
can be subdivied
Simple attribute
cannot be subdivided
Single-value attribute
can have only a single value
Multived attributes
can have many values
Derived attribute
value may be calculated from other attributes
Derived attribute
need not be physically stored within database
Relationships that should not be implented
M:N and multivalued
Participants
entities that participate in a relationship
Relationships
Association between entities
Relationships between entities
always operate in both directions
Relationship classification is difficult to establish
if only one side of the relationship is known
Connectivity
Describes the relationship classification
Cardinality
Expresses minimum and maximum number of entity occurrences associated with one occurrence of related entity
Existence dependence
Entity exists in database only when it is associated with another related entity occurrence
Existence independence
Entity can exist apart from one or more related entitiesSometimes such an entity is referred to as a strong or regular entity
Weak (non-identifying) relationships
Exists if PK of related entity does not contain PK component of parent entity
Strong (identifying) relationships
Exists when PK of related entity contains PK component of parent entity
Weak entity meets two conditions
Existence-dependentPrimary key partially or totally derived from parent entity in relationship
Optional participation
One entity occurrence does not require corresponding entity occurrence in particular relationship
Mandatory participation
One entity occurrence requires corresponding entity occurrence in particular relationship
Relationship Degree
Indicates number of entities or participants associated with a relationship
Unary relationship
Association is maintained within single entity
Binary relationship
Two entities are associated
Ternary relationship
Three entities are associated
Recursive Relationships
Relationship can exist between occurrences of the same entity setNaturally found within unary relationship
Associative (Composite) Entities
Also known as bridge entities
Associative (Composite) Entities
Used to implement M:N relationships
Associative (Composite) Entities
Composed of primary keys of each of the entities to be connected
Associative (Composite) Entities
May also contain additional attributes that play no role in connective process
Database Design Challenges: Conflicting Goals
design standards, processing speed, information requirements
ERM’s main components
EntitiesRelationshipsAttributesIncludes connectivity and cardinality notations
Entity supertype
Generic entity type related to one or more entity subtypesContains common characteristics
Entity subtypes
Contains unique characteristics of each entity subtype
Disjoint subtypes
Also known as non-overlapping subtypesSubtypes that contain unique subset of supertype entity set
Overlapping subtypes
Subtypes that contain nonunique subsets of supertype entity set
Partial completeness
Symbolized by a circle over a single lineSome supertype occurrences that are not members of any subtype
Total completeness
Symbolized by a circle over a double lineEvery supertype occurrence must be member of at least one subtype
Specialization
Identifies more specific entity subtypes from higher-level entity supertypeTop-down processBased on grouping unique characteristics and relationships of the subtypes
Generalization
Identifies more generic entity supertype from lower-level entity subtypesBottom-up processBased on grouping common characteristics and relationships of the subtypes
Entity Integrity: Selecting Primary Keys
Primary key most important characteristic of an entitySingle attribute or some combination of attributes
Entity Integrity: Selecting Primary Keys
Primary key’s function is to guarantee entity integrity
Entity Integrity: Selecting Primary Keys
Primary keys and foreign keys work together to implement relationships
Entity Integrity: Selecting Primary Keys
Properly selecting primary key has direct bearing on efficiency and effectiveness
Natural Keys and Primary Keys
Natural key is a real-world identifier used to uniquely identify real-world objectsFamiliar to end users and forms part of their day-to-day business vocabularyGenerally data modeler uses natural identifier as primary key of entity being modeledMay instead use composite primary key or surrogate key
Primary Key Guidelines
Attribute that uniquely identifies entity instances in an entity setCould also be combination of attributesMain function is to uniquely identify an entity instance or row within a tableGuarantee entity integrity, not to “describe” the entityPrimary keys and foreign keys implement relationships among entitiesBehind the scenes, hidden from user
Primary Key Characteristic: Unique values
The PK must uniquely identify each entity instance. A primary key must be ableto guarantee unique values. It cannot contain nulls
Primary Key Characteristic: nonintelligent
The PK should not have embedded semantic meaning (factless). An attributewith embedded semantic meaning is probably better used as a descriptive char-acteristic of the entity rather than as an identifier. In other words, a student ID of650973 would be preferred over Smith, Martha L. as a primary key identifier. Inshort, the PK should be factless.
Primary Key Characteristic: No Change Over time
If an attribute has semantic meaning, it might be subject to updates. This is whynames do not make good primary keys. If you have Vickie Smith as the primarykey, what happens when she gets married? If a primary key is subject to change,the foreign key values must be updated, thus adding to the database work load.Furthermore, changing a primary key value means that you are basically chang-ing the identity of an entity. In short, the PK should be permanent andunchangeable.
Primary key characterisic preferable single-attribute
A primary key should have the minimum number of attributes possible(irreducible). Single-attribute primary keys are desirable but not required. Single-attribute primary keys simplify the implementation of foreign keys. Havingmultiple-attribute primary keys can cause primary keys of related entities togrow through the possible addition of many attributes, thus adding to the data-base work load and making (application) coding more cumbersome.
Primary key charactrisic preferable numeric
Unique values can be better managed when they are numeric, because thedatabase can use internal routines to implement a counter-style attribute thatautomatically increments values with the addition of each new row. In fact, mostdatabase systems include the ability to use special constructs, such as Autonum-ber in Microsoft Access, to support self-incrementing primary key attributes
When to Use Composite Primary Keys
As identifiers of composite entitiesWhere each primary key combination allowed once in M:N relationshipAs identifiers of weak entitiesWhere weak entity has a strong identifying relationship with the parent entityAutomatically provides benefit of ensuring that there cannot be duplicate values
When to Use Composite Primary Keys
When used as identifiers of weak entities normally used to represent:Real-world object that is existent-dependent on another real-world objectReal-world object that is represented in data model as two separate entities in strong identifying relationship
When To Use Surrogate Primary Keys
No natural keySelected candidate key has embedded semantic contentsSelected candidate key is too long or cumbersome
When To Use Surrogate Primary Keys
Ensure that candidate key of entity in question performs properlyUse “unique index” and “not null” constraints
Implementing 1:1 Relationships
Foreign keys work with primary keys to properly implement relationships in relational modelPut primary key of the “one” side on the “many” side as foreign keyPrimary key: parent entityForeign key: dependent entity
Implementing 1:1 Relationships
Place a foreign key in both entities (not recommended)Place a foreign key in one of the entities Primary key of one of the two entities appears as foreign key of other
Maintaining History of Time-Variant Data
Normally, existing attribute values replaced with new value without regard to previous valueTime-variant data:Values change over timeMust keep a history of data changesKeeping history of time-variant data equivalent to having a multivalued attribute in your entityMust create new entity in 1:M relationships with original entityNew entity contains new value, date of change
Fan Traps
occurs when relationship is improperly or incompletely identifiedRepresented in a way not consistent with the real world
Redundant Relationships
Occur when there are multiple relationship paths between related entitiesMain concern is that redundant relationships remain consistent across modelSome designs use redundant relationships to simplify the design
/ 96
Term:
Definition:
Definition:

Leave a Comment ({[ getComments().length ]})

Comments ({[ getComments().length ]})

{[comment.username]}

{[ comment.comment ]}

View All {[ getComments().length ]} Comments
Ask a homework question - tutors are online