DB 1

arity of a relation

the number of attributes it has

LInear hashing

the one that expands linearly (if index 0 is full, start at index M [2M + i where i is 0]. Can be maintained by a file-load factor instead of just overflows

Total participation with subclass

the superclass HAS to be one of the subclasses

Relation state

the tuples existing in a relation. A subset of the cross-products of all attribute domains

Seek time

time for disk head to find track

block transfer time

time needed to read info from block into main memory

Mappings

transforming requests/results between three DB levels

A database represents some aspect of the real world, sometimes called the

universe of discourse miniworld

A state that satisfies the structure and constraints specified in the schema is called a ________ state.

valid

Traditional file processing

writing custom programs to access data stored in files. User defines and implements files needed for a specific software app as part of programming the app

DBMS

- allows user to create database - specify types of data - relationships among data elements - constraints on what is stored

Data normalization is great, but sometimes we use _________ to improve the performance of queries, like storing pieces of data in multiple places, called _________________

1. controlled redundancy 2. denormalization

View

A composite of a subset of columns from one or more related tables. Contains virtual data derived from the database files but is not explicitly stored

Online transaction processing applications

A concurrently available application that needs to specify holds on data per user efficiently and correctly (online airline booking example)

Data model

A form of data abstraction / conceptual representation that supports basic operations (retrievals and updates on the database) and dynamic behavior (allows database designer to specify a set of valid operations allows on DB objects, like COMPUTE_GPA and stuff). Hides implementation details form users

Operation

A function on data built into the DB definitions. Specified as an interface (inputs and the operation name) and the implementation.

Stored Procedure

A set of SQL statements that can be parameterized and executed ("macro").

Table

A set of data records of the same format, divided into columns that all contain the same kind of data, and rows of related records.

Domain

A set of valid atomic values

Index

A type of access path, allows direct access to data using an index term or a keyword

Four Desirable Properties of Transactions

ACID properties Atomicity Consistency preservation Isolation Durability

Partial participation constraint

An entity can exist without a relationship to the other entity

Total participation constraint

An entity cannot exist without a relationship to the other entity

Dense vs sparse index

An entry for every value versus entries for just some

SQL CHECK Clause

Applies to each tuple individually Example: Dnumber INT NOT NULL CHECK (Dnumber > 0 AND Dnumber < 21); CHECK (Dept_create_date <= Mgr_start_date);

Relationship

Association among entities

Uniqueness constraint

Attributes must be distinct between entities.

Difference between B+ tree and B tree

B tree contains pointers at intermediate levels, B+ tree only has data pointers in leaves. Leaves point to their right neighbors too in B+ trees

SQL Create statement

CREATE TABLE [name] (columns, constraitns, etc);

Which of the CRUD operation(s) result in a change of state in a database?

CUD (not retrieve)

A row in a relational model

Called a tuple. represents a collection of related data values. Typically corresponds to real-world entity or relationship.

Definitions of attributes

Can be an ordered list (this is customary) or an unordered list of (attribute, value) pairs.

Data Independence

Capacity to change the schema at one level of a database system Without having to change the schema at the next higher level

Types of end users

Casual end users (occasionally access the DB, usually through specific interfaces) Naive/parametric end users (constantly query/update the DB using canned transactions, like bank tellers and stuff) Sophisticated end users (engineers/scientists/etc that use the DBMS often to execute complex requirements) Standalone users (maintain personal DBs and use GUIs and packages, like someone using a financial software package)

Query processing/optimization

Chooses an efficient query plan for each query based on storage structures

Category

Class that is a subset of the union of n defining superclasses

Subclass

Class whose entities must always be a subset of the entities in another class

Modules and who develops them?

Components of a DBMS for implementing the catalog, querying, interface processing, accessing data. Developed by DBMS System designers/implementers

Semantic integrity constraints

Constraints on the type of values for an attribute

What are nine advantages of using a DBMS and the capabilities that a good DBMS should possess?

Controlling redundancy. Restricting unauthorized access. Providing persistent storage for program objects. Providing storage structures and search techniques for efficient query processing. Providing backup and recovery. Providing multiple user interfaces. Representing complex relationships among data. Enforcing integrity constraints. Permitting inferencing and actions using rules and triggers

Buffering/caching

DBMS often uses this to keep parts of the database in memory to avoid queries loading/unloading data from memory

Who are the three categories of people whose jobs are associated with the design, development, and operation of the DBMS software and system environment.

DBMS system designers and implementers. Tool developers. Operators and maintenance personnel.

In many DBMSs where no strict separation of levels is maintained, this one language is used by the DBA and database designers to define both the conceptual and internal schemas. Otherwise, it is used to specify the conceptual schema only.

Data Definition Language (DDL)

Once the database schemas are compiled and the database is populated with data, users must have some means to manipulate the database. Typical manipulations include retrieval, insertion, deletion, and modification of the data. This language provides these manipulations.

Data Manipulation Language (DML)

Self-describing data

Data contains item names and data values together in one structure (usually used in NOSQL)

Mirroring/Shadowing

Data is written redundantly to two discs that are treated as one logical disc.

Who are the four categories of people whose jobs involve the day-to-day use of a large database

Database administrators, database designers, end users, and system analysts and application programmers (software engineers).

Storage definition language

Define internal schema only

Data definition language (DDL)

Define the internal and conceptual schema

The DBMS is a general-purpose software system that facilitates what four processes that involve databases?

Defining, constructing, manipulating, and sharing databases

Physical data model

Describe how files are stored on computer.

Atomic

Describes a value that is indivisible

External/view level

Describes part of the database that a particular user group is interested in

Internal level

Describes physical storage structure of the database

Conceptual level

Describes structure of the whole database for a community of users

Primary key

Designated among candidate keys. Other candidate keys are designated as unique keys

Predicate-defined subclass

Displayed by writing the predicate condition next to the line that connects the subclass to the specialization circle. An attribute equals a particular value.

What constraints can Insert violate?

Domain constraints Key constraints Entity integrity Referential integrity

Weak Entities

Don't have key attributes of their own. They are identified by being related to another entity. Always has total participation constraint. If parent entity is deleted, all related weak entities are deleted too.

Backup and recovery system

Ensures DB can recover, such as, in the middle of a transaction or by disk backup etc

Isolation property of transactions

Ensures that each transaction appears to execute in isolation from other transactions

Atomicity property of transactions

Ensures that either all the database operations in a transaction are executed or none are

Specialization hierarchy

Every subclass participates as a subclass in ONLY ONE class/subclass relationship

Transaction

Executing process that includes multiple DB accesses

A Primary Key must be composed of exactly one Attribute in a Relation schema (TRUE/FALSE)

FALSE

A database whose state violates only one Integrity Constraint may still be possible to be in a valid state. (TRUE/FALSE)

FALSE

Tuples in a relation are ordered, TRUE OR FALSE

FALSE. A relation state is a SET of tuples

Sequential file

File containing ordered records

Heap/Pile file

File containing unordered records

Difference between STUDENT and STUDENT(Name, Ssn, ...)

Former is the current state and latter is only the schema

Multiple hashing

Hashing collision method that applies a second hash function if a collision happens. If that collides, use open addressing or a third hash function

Chaining

Hashing collision resolution method that dumps records in an overflow location and sets a pointer of the occupied hash address location to the address of the overflow location

Open addressing

Hashing collision resolution method that proceeds in order from the specified address and checks each position until an open one is found

The overhead costs of using a DBMS are due to which three general areas?

High initial investment in hardware, software, and training. The generality that a DBMS provides for defining and processing data. Overhead for providing security, concurrency control, recovery, and integrity functions.

Entity-relationship ER model/diagram

High-level conceptual model and diagrammatic notation associated with the ER model

Two types of distributed DDBMS software

Homogenous (same DBMS at all sites) and heterogenous (different DBMS at each site)

Database designers

Identifying data to be stored and choosing structures to represent/store the data. Develop views. Collect requirements for the entities to be stored

Deductive database systems

Inferring new information from the stored database facts

Types of Model Constraints

Inherent model-based (constraints inherent in the data model) Schema-based (expressed in schemas of data model by specifying them in the DDL) Application based (business rules not expressed in the schema, but expressed by application program)

What four main types of actions involve databases?

Insert (Add) Delete (Remove) Modify (Update) Retrieve (Query)

Program-operation independence

Interface includes operation name and data types of its arguments, so implementation can be changed without affecting interface

What are the three levels of the Three-Schema Architecture?

Internal level Conceptual level External level

Levels of three-schema architecture

Internal level Conceptual level External/view level

Basic SQL constraints

Key and referential integrity constraints Restrictions on attribute domains and NULLs Constrains on individual tuples within a relation

List four common Database System Utilities discussed in Chapter 2.

Loading (load data files into DB) Backup DB storage reorganization (reorganize DB or make new access paths) Performance monitoring

What are the two types of data independence?

Logical data independence Physical data independence

Of the two types of Data Independence, which is harder to achieve? Why?

Logical data independence is harder to achieve because it allows structural and constraint changes without affecting application programs.

List the nine DBMS Interfaces discussed in the Chapter 2.

Menu-based for web clients or browsing Apps for mobile devices Forms-based GUIs Natural Language Keyword-based DB search Speech I/O Interfaces for Parametric users Interfaces for DBA

Stored Procedures

More involved procedures to enforce rules

Three things DBMS offer

Multiuser usage Encryption and authentication API to interact with it

Entity integrity constraint

No NULL primary key

Key constraints

No two tuples can have the same combination of values for all their attributes

Why include class/subclass specializations in the model?

Not all attributes/relationships may apply to all members of the entity type

What constraints can Delete violate

Only referential integrity. CASCADE, RESTRICT, SET NULL/DEFAULT

Schema evolution

Ops that change the schema. Modern DBMS makes it possible to do so while the DB is operational

Tools and who develops them?

Optional software packages that facilitate DB modeling and design, DB system design, and improved performance. Tool developers develop these.

End users

People whose jobs require access to DB for querying, updating, generating reports.

What are five additional implications of using the database approach, i.e. those that can benefit most organizations

Potential for enforcing standards. Reduced application development time. Flexibility. Availability of up-to-date information. Economies of scale.

How to specify a relation schema R

R(A1, A2, A3... An) where Ai is attribute i (name of a role played by domain D in the relation schema R)

Know the following

Referential integrity constraint Uniqueness/Key constraint Business rules

Attribute

Represents some property of interest about an entity

Security and authorization subsystem

Responsible for creating username/passwords for accessing the database according to certain access restrictions

Triggers

Rule activated by updates to the table

What are the four main characteristics of the database approach versus the file-processing approach?

Self-describing nature of a database system. Insulation between programs and data, and data abstraction. Support of multiple views of the data. Sharing of data and multiuser transaction processing.

Relational database schema

Set of relation schemas S = {R1, R2, ..., Rm) and a set of integrity constraints IC.

Relational database state

Set of relation states DB = { r1, r2,.... rm}

Class

Set or collection of entities.

In spite of the advantages of using a DBMS, there are a few situations in which a DBMS may involve unnecessary overhead costs that would not be incurred in traditional file processing. These four situations are:

Simple, well-defined database applications that are not expected to change at all. Stringent, real-time requirements for some application programs that may not be met because of DBMS overhead. Embedded systems with limited storage capacity, where a general-purpose DBMS would not fit. No multiple-user access to data.

Union constraint on specialization

Single superclass/subclass relationship with more than one superclass (disjoint)

Privileged software

Software for applying big changes to the DB like creating new accounts

Spanned vs unspanned

Spanned -- records can span more than one block, with the tail end of a block pointing to the rest of the record in the next block Unspanned -- if there's not enough space for one more, don't store another one

Canned transaction

Standard DB operations like queries and updates

In databases where a clear separation is maintained between the conceptual and internal levels, this language is used to specify the internal schema.

Storage Definition Language (SDL)

Program-data independence

Structure of data files is stored in DBMS catalog separately from access programs

What features are in EER diagrams that aren't in ER diagrams

Sub-/super-classes Specialization/generalization Category/union type Attribute/relationship inheritance

User-defined subclass

Subclass is not defined by a predicate

Specialization lattice

Subclasses can be a subclass of more than one class/subclass relationship (multiple inheritance).

Overlap constraint on specialization

Subclasses of a specialization don't have to be disjoint

Disjointness constraint on specialization

Subclasses of a specialization must be disjoint (only one specialization per entity)

Key

Superkey of Relation r.

Query optimizer

Takes compiled query/form and makes an efficient search plan

Dynamic hashing

The one that looks like a binary tree that splits on bit values until the leaf node points to the address of the bucket

Extendible hashing

The one with a directory indexed by higher order bits. Index by the first d bits, but each bucket can be indexed by the first <d bits until they overflow, at which point, they're split.

How do you represent inheritance in an EER diagram

The pitchfork U where the forked end points toward the parent

Relationship participation constraints

Total and partial

A Primary Key has a unique value for each Tuple (i.e. Row) in a given Relation (i.e. Table). (TRUE/FALSE)

True

What two properties does a key satisfy

Uniqueness (no two tuples can have the same values for all attributes in key) Minimal superkey (cannot remove any attributes and still have uniqueness constraint hold)

Do key attributes have to be unique?

Uniqueness property must hold for every entity set of the entity type by key attributes

How do you match queries on aggregate values?

Use the HAVING keyword (after WHERE)

What constraints can update violate

Usually causes no problems unless the primary/foreign key is being updated

Referential integrity constraint

Value of Foreign key in a tuple is the primary key of another tuple OR is NULL

In true Three-schema architectures, this language specifies user views (perspectives) and their mapping to the conceptual schema.

View Definition Language (VDL)

Correlated nested query

When a condition in the where clause of a nested query references an attribute of a relation declared in the outer query, the two are correlated

Does the order of table creation matter?

Yes, when foreign keys are involved

Attribute-defined subclass

You can tell the specialization of a class by a particular attribute that the subclass may have.

Relational model represents data as

a collection of relations

Consistency of a transaction

a correct execution of this transaction moves the DB from one consistent state to another

low-level/procedural DML

a data sublanguage embedded in general purpose host language. uses record-at-a-time processing to work on one record at a time

High-level/nonprocedural DML

a query language used on their own to specify complex DB ops at batch levels. Set-at-a-time. DECLARATIVE LANGUAGE because it says what to retrieve instead of how

Valid state

a state that satisfies ALL integrity constraints

Overflow/transaction file

a supplementary file to sequential files -- insertions are just thrown in there and merged in later

Atomicity of a transaction

a transaction is an atomic unit of processing; it is either performed in its entirety or not performed at all.

Isolation of a transaction

a transaction should not make its updates visible to other transactions until it is committed; this property, when enforced strictly, solves the temporary update problem and makes cascading rollbacks of transactions unnecessary

Centralized DBMS

all DBMS ops executed on ONE machine

Data manipulation language

allows CRUD

Schema construct

an object in the schema (which I think is a table)

rotational delay

beginning of block rotates until head finds it

RAID 6

block striping with TWO parity blocks

RAID 5

block striping with parity block distributed

Does DBMS allow you to manipulate the schema or the actual data?

both!

Logical data independence

capacity to change the conceptual schema without having to change external schemas or application programs

Physical data independence

capacity to change the internal schema without having to change the conceptual schema

High-Level/Conceptual data models

closer to the way users perceive data

Entity type

collection of entities that have the same attributes

What is a DB

collection of related data over a miniworld that has inherent meaning

Stored data manager

conducts low-level IO between main-memory and disk

Virtual data

data derived from the DB files but not explicitly stored

A database design that stores each logical data item in only one place in the database is called...

data normalization

Specialization

defining a set of subclasses for an entity type defined on the basis of some distinguishing characteristic of the entities in the superclass. They can define specific attributes or specific relationship types

Low-level/physical data models

describe the details of how data is stored on computer storage media

Metadata

describes data and is stored in the DB

System analysts

determine requirements of end users and create canned transactions for them. Also application programmers, software developers, software engineers

Data striping

distributing data over multiple discs to make them look like a single, large, fast disc (bit-level striping, block-level striping, etc)

Representational data models

easily understood by end users also similar to how data is organized in computer storage hides many detail of data storage on disk, but can be implemented on a computer system directly

Query compiler

evaluates queries typed in interactively.

RAID 1

exact copy of data on both discs. No striping

Runtime DB Processor

executes all queries and updates catalog with statistic changes

Blocking Factor

for fixed-length records of size R and disc of block size B, floor(B/R) which is the number of records per block.

Traditional database systems often suffered from the so-called [a] problem, since the data structures provided by the DBMS were incompatible with the programming language's data structures.

impedance mismatch

Three types of constraints on entities

implicit (inherent in the data model) explicit (defined by the schema) semantic (contained in the business rules)

Clustering index

index on non-distinct key (that the table is ordered on) and has one entry per distinct value -- points to the block where that distinct value first shows up. BAD PERFORMANCE DUE TO REDIRECTION

The DBMS must provide specialized data structures and search techniques to speed up disk search for the desired records. Auxiliary files called _____ are often used for this purpose.

indexes

Primary index

indexes on primary key and has one entry per DISK BLOCK. entry is for the first record in that block

Column names in a relational model

interpret meanings of the values in tuple attributes

The DBMS stores the descriptions of the schema constructs and constraints—also called the ________ —in the DBMS catalog so that DBMS software can refer to the schema whenever it needs to.

metadata

Type of higher-level implementation data model

object-data model

Secondary index

on a potentially non-ordered potentially non-distinct value. dense index that points to the BLOCK where the value is contained -- indexed values are stored in order in index

Durability of a transaction

once a transaction changes the DB and the changes are committed, these changes must never be lost because of subsequent failure

Client module vs Server module

one runs on user-facing end, other is responsible for data storage, access, etc

RAID 0

only data striping. No redundancy

Stored procedures

part of the DB definition and are invoked when certain conditions are met

active DB system

provides active rules that auto initiate actions when certain events/conditions occur

Open Database Connectivity (ODBC)

provides an API for direct DB access that can run on the client side (LIKE JDBC)

Entity

real-world object or concept

RAID

redundancy (reliable), data striping (performance), parity (error detection).

Candidate key

relation schema can have more than one key -- any attribute is a candidate key

Operators and maintenance personnel

responsible for actual running and maintenance of the hardware and software environment for the DB system

Database administrator

responsible for administering the database, the DBMS, and related software. Responsible for authorizing access to database, coordinating and monitoring its use, and acquiring software and hardware resources as needed

In a data model, it is important to distinguish between the description of the database and the database itself. The description of a database is the database _____________

schema

access path

search structure that makes the search for a particular DB record efficient, such as indexing or hashing

A DBMS should provide a ________, which the DBA uses to create accounts and to specify account restrictions.

security and authorization subsystem

Relation

set of tuples of arity n. Each tuple t is an ordered list of n values, where each is an element in the domain of Ai or a NULL value.

Concurrency control

several users trying to update the same data do so in a controlled manner and the result of the updates is correct

View definition language

specifies user views/mappings to conceptual schema

DBMS catalog (and what does it store)

stores info such as the structure of each file, the type and storage format of each data item, and various constrains on the data -- called METADATA

Access path

structure that makes the search for particular database records efficient

Schema

structure/framework of the DB

precompiler

takes DB queries written in progrzmming languages and extracts DML commands while sending the rest of hte program to the compiler

Deductive DB systems

the ability to deduce/infer new information from stored DB facts. Done by specifying declared rules.

database state

the data in the database at a point in time

What is used to classify a DBMS

the data model (relational versus NOSQL) number of users (single vs multiuser systems) number of sites the DB is distributed over (centralized vs distributed) cost (opensource versus other) Access path options (reverse structures vs others) General vs specific purpose

Intension

the database schema

Extension

the database state

Superkey

the entire tuple

relational data model, network/hierarchal models, record-based data model

the first is new, the next two are old, and the last is another representational data model