Modern Database Management Ch 1-4

Front 1

Entity

Back 1

A person, place, object, event, or concept in the user environment about which the organization wishes to maintain data

Front 2

Three Schema Approach

Back 2

External Schema

Conceptual Schema

Internal Schema

Front 3

External Schema

(Enterprise Data Model)

Back 3

The step in which the scope and general contents of databases are specified

Front 4

External Schema Contents

Back 4

High level, abstract picture of data

Entities: SEE ENTITY FLASHCARD

Entity-relationship diagram: Relationships between entities

Business rules (constraints)

Business rules determined from business functions/data matrices

Front 5

Conceptual Schema

Back 5

A detailed, technology independent specification of the overall structure of data

Front 6

Internal Schema Types

Back 6

Logical

Physical

Front 7

Logical Schema Definition

Back 7

Representation of data for a particular data management technology (such as the relational model)

Front 8

Physical Schema

Back 8

Specifications detailing how the logical schema data is represented and stored in the secondary storage using a specific database management system (DBMS) (like Oracle)

Front 9

Each logical data model has BLANK physical data models

Back 9

ONE

Front 10

Enterprise Data Modeling (7 factors)

Back 10

First step in the development process

Specifies scope/content

Big picture, high level of abstraction

E-R Diagram

Descriptions of entity types

Entity-relationships

Business Rules

Front 11

System Development Life Cycle

SDLC

Back 11

Detailed, planned development process

Time consuming but comprehensive

Long development cycle

Front 12

Rapid Application Development

RAD

Back 12

AKA Prototyping

Cursory attempt at conceptual data modeling

Define DB during development of prototype

Repeat implementation and maintenance w/ new prototype versions

Front 13

Data Warehouse

Back 13

Integrated decision support database whose content is derived from various operational databases

Can be used to find patterns/trends

Front 14

SDLC 6 Steps

Back 14

Planning

Analysis

Logical Design

Physical Design

Implementation

Maintenance

Front 15

Entity-relationship Model

(E-R Model)

Back 15

Logical representation of data using entities for categories of data and relationships for associations between entities

Front 16

Composite Attribute

Back 16

Attribute has MEANINGFUL component parts

Ex: Names (first, middle, last) Address (number, street name, city, state, zip)

Front 17

Component attribute

Back 17

??

Front 18

Derived Attribute

Back 18

Attribute whose values can be calculated from related attribute values

Ex: Years with company? Use today's date & date hired, no need to store

Front 19

Degree

Back 19

Number of entity types that participate in the relationship

Front 20

Three most common degrees

(in E-R models)

Back 20

Unary

Binary

Ternary

Front 21

Cardinality Constraint

Back 21

Specifies the number of instances of one entity that can (or must) be associated with each instance of another entity

Front 22

Disjointedness Constraint

Back 22

Whether an instance of a supertype may SIMULTANEOUSLY be a member of two (or more) subtypes

Front 23

Disjoint rule

Back 23

Instance of supertype can be only ONE of the subtypes

(Cannot be both plaintiff & defendant)

Front 24

Overlap rule

Back 24

Instance of supertype could be more than one of the subtypes

(Alumni can be employees)

Front 25

Supertype-Subtype

Define both

Back 25

Supertype: Generic entity type that has a relationship with one or more subtypes

Subtype: Subgrouping of entities in an entity type using their shared common attributes DISTINCT FROM OTHER SUBGROUPINGS

Front 26

Entity Cluster

Back 26

Set of one or more entity types & associated relationships grouped into a single abstract entity type

Front 27

Subtype Discriminator

Back 27

Attribute of a supertype whose values determine the target subtype(s)

Ex: Employee type

Front 28

Primary Key

Back 28

Unique identifiers of the relation. Ex: employee numbers, SSNs, etc

USED TO GUARANTEE ALL ROWS ARE UNIQUE

Front 29

Table is made up of

Back 29

Rows and Columns

Front 30

Rows on a table are:

Back 30

Instances of an entity

Front 31

Columns on a table are:

Back 31

Attributes of the entities

Front 32

Composite Key

Back 32

Key with more than one field

Front 33

Recursive foreign keys

Back 33

A foreign key in a relation that references the primary key values of the same relation.

Ex: See figure 4-17 page 174

Front 34

Foreign Key

Back 34

An attribute in a relation that serves as the primary key of another relation in the same database

Front 35

One to One Binary Relationship

Back 35

Primary key on mandatory side becomes a foreign key

Front 36

Null

Back 36

A value that may be assigned to an attribute when no other value applies or is unknown

Front 37

Unary Relationship

Back 37

?

Front 38

Strong Entity Type

Back 38

Exists independently of other types of entities

Has its own unique identifier

Identifier underline with a single line

Front 39

Weak Entity

Back 39

Dependent on a strong entity (identifying owner) Cannot exist on its own

Does not have a unique identifier

Entity box and partial identifier have double lines

Front 40

Relation

Back 40

Named, two dimensional table of data

Front 41

3 Anomaly Types

Back 41

Insertion Anomaly

Deletion Anomaly

Modification Anomaly

Front 42

Attribute Inheritance

Back 42

A property by which subtype entities inherit values of all attributes and instances of all relationships of their supertype

Front 43

Associative Entity

Back 43

An entity type that associates the instances of one or more entity types and contains attributes that are peculiar to the relationship between those entity instances

Front 44

Multi-valued attribute

Back 44

Shown using { }

Attribute that can have more than one option

Ex: skills, employee can have multiple skills

Front 45

Referential Integrity Constraint

Back 45

Any foreign key value (on the relation of the many side) must match a primary key value in the relation on the one side (or the foreign key can be null)

Front 46

Delete Rules

Back 46

Restrict: Can't delete parent if dependents exists

Cascade: Delete dependent if parent is deleted

Set-to-Null: Set foreign key on the dependent side to null if deleting from the parent side (not allowed for weak entities)

Front 47

Entity Integrity Rule

Back 47

Designed to ensure that every relation has a primary key & the data values for that primary key are all valid

Front 48

Generalization (subtype/supertype)

Back 48

A way of defining sub & super types.

Bottom up

The process of defining a more general entity type from a set of more specialized entity types

Ex: car, truck, motorcycle

Common themes create a supertype and place the shared attributes in the supertype entity

Front 49

Partial Specialization (subtype/supertype)

Back 49

Shown with a single line

Ex: vehicle can be car or truck but can also be neither of those things

Front 50

Total Specialization (subtype/supertype)

Back 50

Shown with a double line

Ex: must be either outpatient OR resident. Cannot be both or neither