Database Concepts Study Guide

Entity

Anything (people, places, objects, events, etc.) about which we store information.

Attribute

Properties of the entities in an entity set.

Entity Instance

A single occurrence of an entity.

Entity Set

A collection of similar entities.

Composite Attribute

An attribute that can be further subdivided to yield additional attributes.

Simple Attribute

An attribute that cannot be subdivided.

Single-valued Attribute

An attribute that can only have a single value.

Derived Attribute

An attribute that is not physically stored within the database and is created by an algorithm.

Multi-valued Attribute

An attribute that can have multiple values.

Relationship

Connections among two or more entity sets.

Cardinality

Refers to the maximum number of times an instance in one entity can be associated with instances in the related entity.

Modality

Refers to the minimum number of times an instance in one entity can be associated with an instance in the related entity.1. Cardinality:The number of occurrences in one entity that are associated with the number of occurrences in another.

One-to-one (1:1) cardinality

A type of cardinality where each occurrence in one entity is associated with only one occurrence in another entity.

One-to-many (1:M) cardinality

A type of cardinality where each occurrence in one entity is associated with multiple occurrences in another entity.

Many-to-many (M:N) cardinality

A type of cardinality where multiple occurrences in one entity are associated with multiple occurrences in another entity.

Identifier

Attributes that uniquely identify entity instances.

Composite identifiers

Identifiers that consist of two or more attributes.

Underlined attribute

How identifiers are depicted in a diagram, with the attribute name being underlined.

Crow's Foot Notation

A notation used in ER diagrams, also known as IE notation.

Entity

What is represented by a rectangle with its name on the top in an ER diagram.

Mandatory-One

In Crow's Foot Notation, two lines represent this type of cardinality.

Mandatory-Many

In Crow's Foot Notation, a line and a crow's foot represent this type of cardinality.

Optional-One

In Crow's Foot Notation, a circle and a line represent this type of cardinality.

Optional-Many

In Crow's Foot Notation, a circle and a crow's foot represent this type of cardinality.

Business Rules

Used to define entities, attributes, relationships, and constraints.

Bi-directional

Finalized business rules must be this, meaning they apply in both directions.

Degree Constraint

A constraint that attaches bounding limits to the edges connecting a relationship to an entity set.

Entity

A real-world object, concept, or thing that can be uniquely identified and about which data needs to be stored.

Attributes

Properties or characteristics that describe entities.

Relationships

Show how entities are connected or related to each other in ER models.

Composite Attributes

Attributes that can be divided into further parts.

Simple Attributes

Attributes that cannot be divided further.

Single-valued attributes

Have a single value for each instance of a particular entity.

Multi-valued attributes

Can have a set of values for a particular entity.1. Key Attributes:Attributes within an entity that uniquely identify each instance (or row) of that entity.

Primary Key

A specific type of key attribute that must be unique, non-null, and stable.

Data Uniqueness

Primary keys ensure that each record in the database is distinct, preventing duplicate data.

Data Retrieval

Primary keys are used to retrieve specific records efficiently.

Data Integrity

Primary keys help maintain data integrity by ensuring uniqueness and stability.

Cardinality

The "how many" part of a relationship, defining the minimum and maximum limits for connections between entities.

Multiplicity

The "exact count" part of the relationship, indicating the exact number or range of instances for connections between entities.

Cardinality (in database terminology)

The number of unique values in a specific column or attribute of a database table.

Multiplicity (in database terminology)

Describes the relationship between entities in a database schema, indicating how many instances of one entity can be associated with another entity.

Data

Collection of facts, can be in the form of words, numbers, or pictures.

Database

A collection of data managed by a DBMS, serving as a repository of data.

DBMS

Database Management System, a software tool for managing data in a database.

RDBMS

Relational Database Management System, a DBMS used in relational databases.

File System

Supports schema for data but is limited to creating directory structures for files and does not directly support a query language for data in files.

DDL

Data Definition Language, allows schema altering commands.

DML

Data Manipulation Language, impacts the data but not the schema, used for queries and modifying data.

Conventional users and application programs

Users and programs that interact with the database using commands that change the content of the database without affecting the schema.1. Query compiler:Analyzes and optimizes a query before passing it to the Execution Engine.

Execution Engine

Issues requests for small pieces of data to a Resource Manager and interacts with other components of the DBMS.

Resource Manager (DBA)

Manages data files and index files, helping to find elements of data files quickly.

Buffer Manager

Brings required data from secondary storage to main-memory buffers.

Storage Manager

Controls the placement of data on disk and its movement between disk and main memory.

Query Processing

The process of translating a query into a query plan and executing the steps in the plan.

Query Plan

An internal form of a query represented as a sequence of operations to be performed on the data.

Execution Engine

Responsible for executing each step in the chosen query plan and interacting with other components of the DBMS.

Storage Manager

Controls the placement of data on disk and obtains blocks containing a file from the buffer manager.

Buffer Manager

Responsible for partitioning main memory into buffers and transferring disk blocks into these buffers.

Transaction Processor

Divided into logging and recovery manager and concurrency-control manager/scheduler.

Atomicity

The property of a transaction where multiple operations are grouped into a single logical entity and must be executed in isolation from one another.

ACID Properties of Transactions

Atomicity, Consistency, Isolation, Durability.

Consistency

The property of transactions preserving the consistency of the database by adhering to consistency constraints.

Isolation

The property of transactions appearing to be executed as if no other transaction is executing at the same time.

Durability

The property of transactions ensuring that the effect on the database is never lost once the transaction has completed.

SQL Join

A statement used to combine data or rows from two or more tables based on a common field between them.

INNER JOIN

A join that selects all rows from both tables as long as the condition is satisfied.

LEFT JOIN

A join that returns all rows from the table on the left side and matches rows from the table on the right side, with null values for unmatched rows.

LEFT JOIN (also known as LEFT OUTER JOIN)

An alternative name for the LEFT JOIN operation.1. RIGHT JOIN:A join that returns all the rows of the table on the right side of the join and matching rows for the table on the left side of the join. For the rows with no matching row on the left side, the result-set will contain null.

RIGHT OUTER JOIN

Another term for RIGHT JOIN.

Inner Join

A join that returns the combined tuple between two or more tables based on the common attributes.

Outer Join

A join that returns the combined tuple from a table even if the join condition fails.

LEFT OUTER JOIN

A clause used with Outer Join to return all the rows from the table on the left side of the join and matching rows for the table on the right side.

RIGHT OUTER JOIN

A clause used with Outer Join to return all the rows from the table on the right side of the join and matching rows for the table on the left side.

FULL OUTER JOIN

A join that creates the result-set by combining the results of both LEFT JOIN and RIGHT JOIN. The result-set will contain all the rows from both tables.

NATURAL JOIN

A join that joins tables based on the common columns in the tables being joined. It returns all rows by matching values in common columns with the same name and data type.

CROSS JOIN

A join where two tables are joined without any specific condition.

Data Model

A notation for describing the data or information, including the structure of data, operations on the data, and constraints on the data.

Structure of data

The way data is organized, such as using physical data models like arrays and structures in programming languages.

Operations on the data

Actions that can be performed on the data, typically in programming languages.

Constraints on the data

Limitations on what the data can be and their potential limits.

Relational Model

A data model based on tables or relations, where operations are performed on the tables.

Semi-Structured Data Model

A data model for data that resembles trees or graphs, rather than tables or arrays.

Object-relational model

A model where values can have structure, rather than elementary types like integers or strings.

Relation

A single way to represent data in a 2-D table, where each row represents a record and each column represents a property of the record.

Attributes

The columns of a relation, named by attributes, which describe the meaning of entries in the column.

Schema

The set of schemas for the relations of a database, which defines the structure of the relations.

Tuples

The rows of a relation, containing the components for each attribute of the relation.

Domains

The elementary types required for each component of each tuple in the relational model.

Equivalent Representations of a Relation

The ability to list the tuples in any order or reorder the attributes without changing the relation.

Set

An unordered collection of distinct elements of the same type.

Relations in SQL

Stored relations, views, and temporary tables in SQL, which can be modified or constructed when needed.1. Relations:A set of tables in a relational database.

Relational Database

A database that organizes data into tables or relations.

Relation

A mathematical term for a table in a relational database.

Relational Schema

Specifies the name of a relation and the name and data types of each column or attribute.

Relational Instance

The set of tuples (rows) of a relation at a particular instance of time.

Domain

Represents the set of atomic values that an attribute can take.

Tuples

Rows in a relation that have no order and represent records or data entries.

Cardinality

The number of tuples (rows) in a relation.

Default Values

The value that appears in a column if no other value is known or specified.