Lesson 4 Flashcards
entity-relationship (ER) model
high-level representation of data requirements, ignoring implementation details
Entity
person, place, product, concept, or activity
Relationship
statement about two entities
Attribute
descriptive property of an entity
Reflexive relationship
relates an entity to itself
ER diagram
schematic picture of entities, relationships, and attributes
Entity type
set of things
Relationship type
set of related things
Attribute type
set of values
Entity instance
Individual thing
Relationship instance
statement about entity instances
attribute instance
Individual value
Analysis for Database Design
Conceptual design
Strong entity
entity with own primary key
can exist independent of other entities
Weak entity
entity with composite key
must depend on strong entities to exist
cardinality
refers to maxima and minima of relationships and attributes
*one-to-one
*one-to-many
*many-to-many
Relationship maximum
Greatest number of instances of one entity that can relate to a single instance of another entity
one-to-many
Relationship minimum
Least number of instances of one entity that can relate to a single instance of another entity
Subtype entity
subset of another entity type, called the supertype entity
partition
A partition of a supertype entity is a group of mutually exclusive subtype entities
Database Design
After entities, relationships, attributes, cardinality, and strong and weak entities are determined, the designer looks for supertype and subtype entities.
crow’s foot notation
depicts cardinality as a circle (zero), short line (one), or three short lines (many)
Artificial Key
single column primary key created by the database designer when no suitable single column or composite primary key exist
functional dependence
dependence of one column on another
Redundancy
repetition of related values in a table
Normal forms
rules for designing tables with less redundancy
Learn Database Normalization - 1NF, 2NF, 3NF, 4NF, 5NF
candidate key
simple or composite column that is unique and minimal
a table can have multiple candidate key
non-key
column that is not a possible candidate key
Normalization
Eliminates redundancy by decomposing a table into two or more tables
Redundancy is eliminated with normalization
Last step of the Logical design
trivial dependencies
When the columns of A are a subset of the columns of B, with A always depending on B
Denormalization
intentionally introducing redundancy by merging tables to eliminate JOIN queries
Boyce-Codd normal form
ideal for tables with frequent inserts, updates, and deletes
IsA
supertype entity identifies its subtype entities
Attribute minimum
Least number of attribute values that can describe each instance
Appears in parentheses
