• Shuffle
    Toggle On
    Toggle Off
  • Alphabetize
    Toggle On
    Toggle Off
  • Front First
    Toggle On
    Toggle Off
  • Both Sides
    Toggle On
    Toggle Off
  • Read
    Toggle On
    Toggle Off

Card Range To Study



Play button


Play button




Click to flip

Use LEFT and RIGHT arrow keys to navigate between flashcards;

Use UP and DOWN arrow keys to flip the card;

H to show hint;

A reads text to speech;

68 Cards in this Set

  • Front
  • Back

DBMS components (4)

1.) database

2.)DBA (database administrator)

3.) Application Programs

4.) Hardware ( storage, servers, etc)

Why use DBMS? (7)

1.) data sharing

2.) centralized control

3.) Redundancy control

4.) Data Integrity

5.) Data security

6.) Views

7.) Data independence (logical data, physical data)


The overall description of a database (at various granularities)


A description of DBMS components and their interconnections

ANSI/SPARC Architecture

External level (ANSI/SPARC)

describes user perspectives of the DB

Conceptual Level (ANSI/SPARC)

design of the field groupings, data relationships, etc.

Internal Level (ANSI/SPARC)

define record sizes, representatives, indices,etc.

Hidden from application programs, users

Interface between ANSI/SPARC levels (mappings)

External Mapping

(view to view) often easier to tweak an existing view. but efficiency is a concern

Interface between ANSI/SPARC levels (mappings)

External- Conceptual Mapping

allows field renaming, arrangements for user benefits

Interface between ANSI/SPARC levels (mappings)

Conceptual- Internal Mapping

Convert logical structure to physical representations

Provides physical data independence

Server Architecture (two tier)

Client- presentation of data to user

Server- fields requests from clients, runs the DBMS

Server Architecture (multi tier)

-more capabilities


-less client computing needed

What is SOA?

Service Oriented Architectures

software design technique

Apps are built using pre written service mods

Goal is to be flexible with the adoption of new business processes

3 Parts of an SOA

Advertising (service)



Distributed DBMS

Each server has all or some, or none of the DB stored locally

Storage Pyramid

Hard drive Physical Characteristics side View

Hard drive Physical Characteristics top view

Sources of Read Write delay

Seek Time

Rotational delay

Transfer Time

Raid Disk Mirroring

1 Write

2 copies

Adv: can operate w/ failed drive (possibly read in parallel)

Disadv: Cost (2 disks, capacity of one)

Disk Striping

distributing units of data across disks

Adv. Performance

Disadv. Inc probability of drive failure

Parity Schemes (raid)

Odd, Even 

Odd, Even

Independent events for drive failure

p(A ^ B) = p(A) + p(B) - p(A) * p(B)

Hard drive failure review


A file containing structured references to records of another file.

Blocking Factor

The # of whole records that can be stored in a block. bf - (block size/ record size)

Internal Fragmentation

Unallocable storage within an allocation unit.

Fixed length Records:

packed allocation (contiguous) vs. unpacked allocation

packed allocation (contiguous) vs. unpacked allocation

Variable length Records

similar to unpacked but you need ptrs in the directory w/ offsets


A file containing structured references to records of another file

Candidate key

A key able to uniquely identify a record (i.e. Student ID, SSN)

Primary Key

The chosen Candidate key to be the records identifier

Secondary key

Any non-candidate key

Sort Key

The key used to sort the files records

Ordered and Unordered Classification of indices

Single (sorted file)

Multi-level (B+ tree)

Primary index: The indexed Field is a _______

Candidate Key

Primary index: The index records are sorted on ___________

a candidate key

Primary index: The file records are sorted on _____________

a candidate key

Clustered Index: The indexed field is ___________

not a candidate key (therefore, secondary)

Clustered Index: The index records are sorted on ____________

a secondary key

Clustered Index: The file records are sorted on _______________

a secondary key

Secondary Index: the indexed field is _____________

any field

Secondary Index: the index records are sorted on ____________

any field

Secondary Index: the file records are not sorted on ____________

any field

Dense Indices:

Hold a record for every DB file record

permits existence queries w/ index only

Sparse Indices:

indices have only a subset of the field values

Therefore: smaller, faster to search

Extendable Hashing

B tree insert

If leaf has capacity insert,

otherwise, form new set for keys, promote median value to parent, create two nodes that are less than and greater than. Attach children on either side

B Tree deletion

For B-tree of order 2 how many keys?

first level: 4

second level: 5 * 4 = 20

Third level: 5^2 * 4 = 100

For b tree of order 100, how many keys?

first level: 200

second level: 201 * 200

third level: 201^2 * 200

B tree order determination

B+ tree differences from B Tree

Copies of some keys occupy internal nodes

each key is stored in a leaf node

leaf nodes are linked sequentially

What are the Advantages of a B+ over a B Tree. Disadv?

Adv: built in disk pointers for all key values, Supports exact match (10-20)

DisAdv: Waste of storage capacity

Insertion/ Deletions are a little more complex

Definition: Null

A null is a marker that indicates that a field does not have a value.

Definition: Foreign Key

A field in one file whose values are drawn from the primary key field of another file

Which can be null? Primary or foreign?


Foreign keys are defined when.....

the DB is defined

No PK can be ______


Four Phases of DB design

1: Requirements Analysis

2: Conceptual Design

3: Logical Design

4: Physical Design

Definition: Entity

An Entity is an instance of a general classification

Ex. Each of you is an instance of class "human"

Definition: Relationship
A relationship is an association between sets of entities

Ex. Instructors teach classes

Definition: One to One Relationships

In a 1 to 1 relationship between two entity sets A and B, an entity from A is associated with at most one entity from B, and an entity from B is associated with at most one entity from A.

Definition: One to Many Relationship

In a 1:N relationship, one entity from A is associated with none or more entities from B, but each from B is associated with at most one from A

Definition: Many to Many Relationship

An M to N relationship is a pair of 1 to N relationships

Ternary Relationship

3 degree or 3 way. Ex. SPJ schema

Four Data Models of Note