Database Management Systems Exam Review Flashcards

Flashcards for reviewing key concepts in database management systems, including data modeling, normalization, SQL, and database design.

Entity

Things in reality we want to keep data about.

Attribute

A distinct characteristic or property of an entity for which we want to keep data.

Relationship

Logical links between entities.

Candidate Key

Any set of attributes that could be chosen as a key of an entity.

Primary Key

The candidate key designated for principle use in uniquely identifying instances of an entity.

Alternate Key

A candidate key that was not designated as the primary key.

Surrogate Key

An artificial attribute that is added to an entity to serve as the primary key.

Composite Key

A primary key consisting of more than one attribute.

Foreign Key

An attribute in one entity that constitutes a key in some other (or possibly the same) entity, used to indicate logical links between entities.

Relationship Cardinality

The number of instances of one entity related to a single instance of another entity. includes the minimum and maximum number of entity instances that must participate in a relationship instance.

Minimum Cardinality

The minimum number of entity instances that must participate in a relationship instance.

Maximum Cardinality

The maximum number of entity instances that can participate in a relationship instance.

Strong entity

Exists independently of other types of entities, has its own unique identifier (key).

Weak entity

Dependent on a strong entity (identifying owner), cannot exist on its own, does not have a unique identifier (only a partial identifier).

Identifying relationship

Links strong entities to weak entities.

Functional Dependency

The value of one or more attributes in a row determines the value of another attribute in the row.

Normalization

Data redundancy and data integrity issues can be solved using this process.

Creation/ Insertion Anomaly

Inability to add data to the database due to absence of other data.

Update Anomaly

Data inconsistency resulting from data redundancy and partial update.

Deletion Anomaly

Unintended loss of data due to deletion of other data.

Second Normal Form (2NF)

A relation is in this normal form if and only if it is in 1NF and all non-key attributes are determined by the entire primary key.

Third Normal Form (3NF)

A relation is in this normal form if and only if it is in 2NF and there are no non-key attributes determined (functionally dependent) by another non-key attribute.

Conceptual Model

Establish the entities, their attributes, and their relationships.

Logical Data Model

Defines the structure of the data elements (e.g. columns) and set the relationships between them.

Physical Data Model

Describes the database-specific implementation of the data model.

Data Type

A type of allowed data for each column.

Default value

Assumed certain value(s) if no explicit value.

Range control

Allowable value range/limitations.

Referential integrity

Enforcing relationships and their cardinality constraints via foreign-key to primary- key match-ups.

Data manipulation language (DML)

Used for querying, inserting, modifying, and deleting data.

Data definition language (DDL)

Used for creating tables, relationships, and other structures.

Keyword

A reserved word that is part of the SQL language.

SELECT clause

Specifies which columns are to be listed in the query results.

FROM clause

Specifies which tables are to be used in the query.

WHERE clause

Specifies (conditions/ filters) which rows are to be listed in the query results.

Operator

A reserved keyword or character used in a WHERE clause to perform an operation, such as comparisons or arithmetic operation.

Wildcards

A wildcard character is used to substitute one or more characters in a string. % matches any number of occurrences of any character. _ matches just a single character.

Functions in SQL

Operations that are performed on data; they Facilitate the manipulation or conversion of data.

Aliases

Used to name calculated fields; use the AS keyword.

Aggregate Functions in SQL

Operate on a set of rows to calculate and return a single value.

GROUP BY clause

Lets you divide data into logical sets so that you can perform aggregate calculations on each group.

WHERE clause

Filters before data is grouped.

HAVING clause

Filters after data is grouped.

Subquery

A query nested within another query, always enclosed in parenthesis ().

SQL JOIN clause

Combines rows from two or more tables.

Views

Creates virtual tables (via queries) on-the-fly that dynamically retrieve data when needed.