Database Design Flashcards

Database

A collection of data central to some enterprise

essential to operation of enterprise

contains only record of enterprise activity

an asset in its own right

persistent

Data

Known facts that can be recorded and have an implicit meaning

Mini-world

Some part of the real world about which data is stored in a database

Database Management System (DBMS)

A program/software package that manages a database by supporting high-level access language and interpreting statements to perform database access

Database System

The DBMS software together with the data itself

sometimes includes applications

Entity

Objects or things in the mini-world that are represented in the database

Attribute

Properties used to describe an entity

Simple Attribute

Each entity has a single atomic value for the attribute

Composite Attribute

Attribute may be composed of several components forming a hierarchy

Multi-valued Attribute

Entity may have multiple values for that attribute

Entity Type

Entities with the same basic attributes grouped together

Key Attribute

An attribute of an entity type for which each entity must have a unique value

Entity Set

The collection of all entities of a specific entity type

Relationship

A connection that relates two or more distinct entities with a specific meaning

Relationship Type

Relationships of the same type grouped or typed together

Degree of Relationship Type

The number of participating entity types

Binary Relationship

A relationship with two participating entity types

Recursive Relationship

A relationship where two entities of the same entity type are related

Weak Entity Type

An entity that does not have a key attribute

Identifying Relationship Type

A relationship type that identifies a weak entity through connection to owner entity

Partial Key

A key component of weak entity that identifies it only in combination with owner entity

Cardinality Ratio

Specifies the maximum number of relationship instances (1:1, 1:N, N:M)

One-to-one (1:1) Relationship

Each entity in one set relates to at most one entity in another set

One-to-many (1:N) Relationship

One entity in first set relates to many entities in second set

Many-to-many (M:N) Relationship

Many entities in first set relate to many entities in second set

Participation Constraint

Specifies whether entity participation in relationship is total or partial

Total Participation

Mandatory participation of all entities in a relationship type

Partial Participation

Optional participation where not all entities must participate in relationship

(min

max) Notation

Roles in Relationships

In recursive relationship two entities play different roles

Attributes of Relationship Types

Relationship type can have attributes describing each relationship instance

ER Diagram

A diagrammatic display of aspects of a database schema

Classification

A grouping of member entities into an entity type

Aggregation

A grouping of member relationships into a relationship type

Identification

A means of uniquely identifying entities

Generalization

A process of grouping entity types into higher-level entity types

Schema

The description of a database including structure and constraints

Database Instance

The actual data stored in a database at a particular moment in time

Intension

The database schema

describes structure

Extension

The database state

changes every time database is updated

Three-Schema Architecture

DBMS design that defines internal

Internal Schema

Describes data storage structures and access paths using physical data model

Conceptual Schema

Describes structure and constraints for whole database at conceptual level

External Schema

Describes various user views at external level

Physical Data Independence

Capacity to change internal schema without changing conceptual schema

Logical Data Independence

Capacity to change conceptual schema without changing external schemas

Information

Processed data that has value for decision making

Metadata

Description of data at some level

stored in DBMS catalog

Physical Data Schema

Describes details of how data is stored including tracks

Conceptual Data Level

Hides storage details

presents data as set of tables

External Data Level

Presents data as set of relations

tailored to particular user categories

View

A portion of stored data or a derived computation defined for specific users

Data Model

A set of concepts to describe the structure of a database

Data Model Operations

Operations for specifying database retrievals and updates

Conceptual Data Models

High-level semantic models providing concepts close to user perception

Physical Data Models

Low-level internal models providing concepts describing how data is stored

Implementation Data Models

Models providing concepts between high-level and low-level

Record-based Data Models

Relational

Object-based Data Models

Models close to human perception but farther from computer system

Relational Model

Data model proposed in 1970

organizes data in tables

Network Model

Data model implementing multiple parent-child relationships

Hierarchical Data Model

Data model representing data in tree structure with parent-child relationships

Object-oriented Data Model

Data model for persistent O-O programming languages

Object-Relational Models

Most recent trend combining relational and object-oriented approaches

Benefits of Using DBMS

Controlling redundancy

sharing data

restricting unauthorized access

providing multiple interfaces

representing complex relationships

enforcing integrity constraints

backup and recovery

flexibility to change structures

When Not to Use DBMS

When access is simple and well-defined and not expected to change

when access by multiple users is not required

Self-contained Nature of DB

DBMS catalog stores database description (metadata)

Program-data Independence

Allows changing data storage structures without changing DBMS access programs

Data Abstraction

Data model used to hide storage details and present conceptual view

Support of Multiple Views

Each user may see different view of database

Virtual Data

Data not actually stored but computed when accessed

Sharing of Data and Multiuser Processing

Concurrency control allows multiple users to access data

Data Redundancy

Same data appears in multiple locations

Data Inconsistency

Lack of data integrity when same data appears multiple times

Data Anomalies

Modification

insertion

or deletion anomalies

Modification Anomaly

Data inconsistency due to updating same data in multiple locations

Insertion Anomaly

Inability to insert data without knowing values in other attributes

Deletion Anomaly

Deletion of data causing unintended loss of other data

Structural Dependence

Physical structure of database is dependent on access method