Database Systems Chapter 5

CS 4210 Chapter 5

data model

plan or blueprint for database design

three design stages, and the one we focus on

conceptual design, logical design, and physical design. we focus on conceptual design

the er model

set of concepts and symbols to create conceptual schemas

what er model do we use now

extended er model

entity

something that can be identified and kept track of

entity class

a collection of entities of a given type

entity instance

occurence of a particular entity

attributes

an entity’s characteristics

identifiers

attributes that name/identify entities

identifier vs key

identifiers in data models become keys in database designs

composite identifiers

consists of two or more attributes

relationships

entities associated with one another

relationship classes

associations among entity classes; can involve two or more entity classes

relationship instances

associations among entity instances

degree of the relationship

number of entity classes in the relationship, binary, ternary

entities vs tables

you can express a relationship between entities without using a foreign key

maximum cardinality

the maximum # of relationship in which an entity can participate in

minimum cardinality

minimum # of relationship instances an entity must participate in

types of maximum cardinality

1:1, 1:N, N:M

who is parent and child is 1:N relationship

entity on the one side of the relationship is the parent. the one on the many side is the child

HAS-A relationship

each entity instance “has a” relationship with another entity instance

minimum cardinality types

zero (optional) , one (mandatory)

strong entity

entity that can exist on its own (basically a normal entity)

weak entity

entity whose existence depends on another entity

ID-dependent entity

an entity whos identifier includes the identifier of another entity. logical extension of the parent. minimum cardinality is always one

identifying relationship

solid line connecting an id dependent entity and its parent

nonidentifying relationship

dashed line connecting strong entities or non-id dependent weak entity to its parent

exclusive subtypes

one supertype relates to at most one subtype (student can only be grad or undergrad)

inclusive subtypes

one supertype can relate to one or more subtypes (a student can be in multiple clubs)

total subtypes

if a supertype has to be in one of the subtypes

partial subtypes

if a supertype doesnt have to be in any of the subtypes

IS-A relationships

relationship connecting supertypes and subtypes

the identifier of the supertypes and all the subtypes must be

identical

subtypes are used to avoid

value-inappropriate nulls

ID-Dependent relationship patterns

multivalued attribute pattern, archetype/instance pattern, association pattern

association pattern

includes a third entity to hold additional attributes. look at slides for examples

multivalued attribute pattern

idk like someone can have multiple phone numbers

archetype/instance pattern

when the id dependent child is the physical manifestation of an abstract parent (ie painting→print or house model→house)

patterns for mixing identifying and nonidentifying patterns

line-item pattern and others ig

look for a mixed pattern where

a strong entity has a multivalued composite attribute, and one of the elements of the composite group is an identifier of another strong entity

recursive relationship

when an entity has a relationship with itself, like a train a boxcar has a recursive relationship with the next boxcar

IDEF1X

Integrated Defined 1, Extended (national standard since 1993)

UML

Unified Modeling Language

IDEF1X relationship terms

nonidentifying connection relationships (same), identifying connection relationships (same), nonspecific relationships (N:M relationship), categorization relationships

default cardinality for identifying and non-identifying relationships

1:N, M:O