Flashcards Database Systems: Key Concepts, Models, and Architecture

Database (DB)

A collection of data; the passive component managed by the DBMS.

Database Management System (DBMS)

Software used to manage a database; the active component that interacts with the database.

Centralized Database System

A database architecture where users interact with one DBMS, one operating system, and one database.

Client-Server Database System

A database architecture where multiple clients communicate with a database server containing the DBMS, OS, and database.

Distributed Database System (DBS)

A database system involving multiple operating systems, DBMSs, and databases.

Homogeneously Distributed DBS

A distributed database system in which the DBMS software is the same type at the different sites.

Heterogeneously Distributed DBS

A distributed database system in which different types of DBMS software are used at different sites.

Replicated Data

Data that is copied across multiple databases, providing redundancy.

Non-Replicated Data

Data that is not copied onto another database.

Two disadvantages of database systems

Cost (hardware/software) and complexity.

Data Format Consistency

A database advantage in which data is required to follow defined formats or acceptable values.

Data Integrity

A database advantage where constraints are used to ensure data follows specified rules.

Database Security

A database advantage allowing access to data to be controlled or restricted.

Recovery

A database advantage ensuring that after a failure, a transaction is treated as either fully completed or never completed, rather than partially completed.

Transaction Atomicity

All or nothing of a transaction is executed.

Concurrency Control

Safely executing concurrent transactions so that simultaneous operations do not improperly interfere with or overwrite one another.

1st Generation Language

Machine/binary language.

2nd Generation Language

Assembly language, such as ADD, MOV, and SUB.

3rd Generation Language

Procedural languages such as C, C++, and Java that specify how to perform a task.

4th Generation Language

A non-procedural language that specifies what is wanted rather than the exact procedure for how to obtain it.

Program/Data Independence

The program is independent of the data structure, reducing the need to modify programs when the data structure changes.

Schema

The defined structure or organization of a database.

Metadata

Information about data.

Three-Schema Architecture

The External, Conceptual, and Internal levels used to separate user views, the overall database structure, and physical storage.

External Schema (E)

The highest level of the three-schema architecture; describes what a particular user or group sees.

Conceptual Schema (C)

The overall logical view of the database and the information the database contains.

Internal Schema (I)

Describes how the database is physically stored, such as records, indexes, disk pages, and byte layouts.

Physical Data Independence

The ability to modify the internal schema without affecting the conceptual schema.

Logical Data Independence

The ability to modify the conceptual schema without affecting the external schema.

Purpose of the Three-Schema Architecture

To prevent changes at one level from unnecessarily affecting the level above it.

Data Definition Language (DDL)

Database language used to create, remove, or modify database structures and schemas.

Data Manipulation Language (DML)

Database language used to store, retrieve, update, and otherwise manipulate actual data.

SQL

A database language containing both DDL and DML.

Data Model

A description of the structure of a database.

Network Data Model

A model where data is represented as records/nodes and relationships as sets/edges, forming a graph-like structure.

Hierarchical Data Model

A restricted network model represented as a tree in which a node can have only one parent.

Relational Data Model

A model based on mathematical relations in which data and relationships are represented using tables.

Object-Oriented Data Model

A model in which data items are treated as objects using object-oriented concepts.

Object-Relational Data Model

A hybrid of the object-oriented and relational data models.

Graph Database Model

A flexible graph/network-style model without a rigid schema or structure.

Relation

The relational-model term for a table.

Tuple

The relational-model term for a row or record.

Attribute

The relational-model term for a column.

Degree of a Relation

The number of attributes (columns) in a relation.

Cardinality of a Relation

The number of tuples (rows) in a relation.

Domain of an Attribute

The set or range of acceptable values for an attribute.

Key of a Relation

An attribute or set of attributes used to uniquely identify tuples.

Superkey

An attribute or set of attributes that uniquely identifies a tuple within a relation.

Candidate Key

A minimal superkey; no proper subset of it is itself a superkey.

Primary Key

The candidate key selected to uniquely identify tuples within a relation.

Can a relation have multiple candidate keys?

Yes. One candidate key is selected as the primary key.

Can a superkey contain unnecessary attributes?

Yes. As long as it uniquely identifies tuples, it can contain additional attributes.

Can a candidate key contain unnecessary attributes?

No. A candidate key must be minimal.

Foreign Key

An attribute or set of attributes that gets its values from the primary key of another table and creates a relationship between the tables.

Composite Key

A key consisting of more than one attribute.

Domain Constraint

An attribute's value must come from its defined domain.

Key Constraint

Key attribute values must be unique.

Entity Integrity Constraint

Primary key values cannot be NULL.

Referential Integrity Constraint

A foreign key value, if not NULL, must be present as a primary key value in the table it references.

Cascading Deletion

Deleting records containing foreign keys because the referenced primary-key record was deleted.

Relation Structure

A relation consists of a set of attributes and a set of tuples.

Relational Algebra

A collection of operations performed on relations to produce resulting relations.

Selection (σ)

A unary relational algebra operation that returns only tuples satisfying a specified condition or predicate.

Selection notation

σ_condition(Relation)

What does Selection affect?

Rows/tuples. It filters tuples according to a condition.

Does Selection change the degree of a relation?

No. Selection keeps the same attributes, so the degree remains unchanged.

Projection (π)

A unary relational algebra operation that extracts specified attributes/columns and eliminates duplicate tuples.

Projection notation

π_attribute(s)(Relation)

What does Projection affect?

Columns/attributes. It returns only the specified attributes.

What happens to duplicates during Projection?

Duplicate resulting tuples are eliminated.

Union (∪)

Returns tuples that occur in R, S, or both; duplicate tuples are eliminated.

Union notation

R ∪ S

Union Compatibility

Two relations are union-compatible when they have the same number of attributes and corresponding attributes have the same domains.

Does Union require union-compatible relations?

Yes.

Set Difference (−)

Returns the tuples that occur in R but do not occur in S.

Set Difference notation

R − S

Does Set Difference require union-compatible relations?

Yes.

Intersection (∩)

Returns the tuples that occur in both R and S.

Intersection notation

R ∩ S

Does Intersection require union-compatible relations?

Yes.

Cartesian Product (×)

Combines every tuple of one relation with every tuple of another relation, producing all possible tuple combinations.

Cartesian Product notation

R × S

Degree of a Cartesian Product

If deg(R) = m1 and deg(S) = m2, then deg(R × S) = m1 + m2.

Cardinality of a Cartesian Product

If |R| = n1 and |S| = n2, then |R × S| = n1 × n2.

|R| notation

The cardinality, or number of tuples/records, in relation R.

Join (Inner Join)

Combines tuples from two relations according to a join condition involving compatible join attributes.

Join notation

R ⋈ S (with the appropriate join condition when required).

Join Attribute

An attribute used to join two relations; the participating attributes must have the same domain.

Join Condition

The condition specifying how tuples from two relations are matched during a join.

Natural Join

A join over common same-named attributes that removes the duplicate occurrence of the common join attribute from the result.

Natural Join notation

R ⋈ S

Join Selectivity

The number of records in the join result divided by the relevant number of records used for comparison in the join calculation.

Is Union commutative?

Yes. R1 ∪ R2 = R2 ∪ R1.

Is Intersection commutative?

Yes. R1 ∩ R2 = R2 ∩ R1.

Is Set Difference commutative?

No. R1 − R2 is generally not equal to R2 − R1.

Is Union associative?

Yes. (R1 ∪ R2) ∪ R3 = R1 ∪ (R2 ∪ R3).

Is Intersection associative?

Yes. (R1 ∩ R2) ∩ R3 = R1 ∩ (R2 ∩ R3).

Selection vs. Projection

Selection filters ROWS/TUPLES; Projection selects COLUMNS/ATTRIBUTES.

Degree vs. Cardinality

Degree = number of columns/attributes. Cardinality = number of rows/tuples.

Superkey vs. Candidate Key

A superkey uniquely identifies tuples and may contain unnecessary attributes; a candidate key is a minimal superkey with no unnecessary attributes.