Database Management Systems Exam Review Flashcards

These flashcards cover critical concepts and definitions in database management systems.

Database System

A collection of related data stored in a computer.

DBMS

The software that directly supports various database applications.

Centralized DBMS

A system where a user can directly access the database through an application on their PC or cellphone.

Inhibitor of using DBMS

High initial cost.

Database Schema

A structure that describes the organization of data in a database.

Conceptual Schema

The structure that end users use to access the database.

Client/Server Architecture

A model where client and server can be on different machines.

Network Model

A low-level data model describing how data is stored on a computer.

Relational Model

A high-level data model that can represent the semantics of the application.

Domains (Relational Model)

Can contain specific types of values.

Object Model vs ER Model

The object model is more expressive than the ER model.

Attributes in Relation

Columns in a relation are known as attributes.

Foreign Key

A key that references a primary key in a different relation.

Select and Project Operations

The order cannot be changed without altering the meaning of a query.

Project Operation

Is not commutative.

Rename Operation

Used to change the relation name and its attributes in relational algebra.

Relational Algebra Query

An operation like 'project sname (student)' is a relational algebra query.

Join Operation

It can be represented by using other operations but is essential in relational queries.

Union Operation

Can be used on two relations if they have the same number of attributes.

Attributes in ER Model

Attributes can exist in a relationship in the ER model.

Atomic Attributes

Attributes in ER model must be atomic.

Tuple Relational Calculus

Can represent the divide-by operation in relational algebra.

Domain Relational Calculus

Often considered a weak query language for relational databases.

Tuple Calculus Query

Can represent unary, binary, aggregate operations together.

SQL Query Capabilities

SQL can represent unary, binary, aggregate operations together.

SQL Expressive Power

Has the same expressive power as relational algebra.

Complete SQL Language

Can do everything that database applications need.

Entity Representation in ER Model

Every real-world object is represented as an entity.

One-to-One Relationship

Every entity in set A must have one and only one corresponding entity in set B.

Ternary Relationship

Can be represented equivalently with three binary relationships.

Normalization

The process of decomposing bad relations into smaller relations.

3NF and BCNF

A relation in 3NF is also in BCNF.

PL/SQL Programs

Can interact with users directly by taking input and displaying output.

Dynamic SQL vs Static SQL

Dynamic SQL is generally less efficient than Static SQL.

Impedance Mismatch

Occurs between the database and application programs.

Transaction SQL Statements

A transaction can contain several SQL statements.

Hierarchical Data Model

Cannot directly represent many-to-many relationships.

Non-Atomic Values

Supported in the object data model.

Attribute Designation in RDB

Designates the role played by a domain in a relation.

End Users

Users who use the database frequently but often don't have in-depth knowledge.

E.F. Codd

Proposed the relational model.

Turing Award

Larry Ellison is noted as someone who did not receive this award.

Oracle

The biggest relational database vendor.

Query Language Absence

The ER model lacks a query language.

Built-In Integrity Constraints

Relational model has a specific number of built-in integrity constraints.

Minus Operation

An algebra operation that can be defined by other operations.

Join Types

Theta Join is the operation used in joins.

Information Loss in Joins

An outer join does not lose any information.

Correct Tuple Calculus Query

{S, N, A | Student(S, N, A)} is a correct tuple calculus query.

Queries Not Representable by Calculus

Student nfulljoin Grade cannot be represented.

Single Query Expression Limitations

Some languages cannot express complex queries with a single expression.

Divide-By Operation Representation

Must use forall in tuple calculus.

SQL expressiveness

SQL is less expressive compared to algebra and calculus.

Identifying Relationship Sets

Used to connect weak entity sets to their owners.

4GL Characteristics

The 4th generation language (4GL) is declarative in nature.

Client/Server Structure

Thin client/fat server architecture is preferable.

Three-Tier Architecture Benefit

The main benefit is enhanced security.

Tuple Processing in PL/SQL

A cursor must be used to process a set of tuples.

C Variable Prefixing

C variables in SQL statements are prefixed with a colon.

Embedded SQL Programs

Classified as application programs.

SQLPlus in Oracle VM

Is a client and server-side program.

Dynamic SQL Prepare Method

Used to parse the SQL statement.

Dynamic SQL Execution

The execute statement instructs the DBMS to run the query.

SQL Accessing Any Relation

Requires Method 4 for dynamic SQL to access any relation.

Dynamic SQL Issues

Main problems include run-time overhead.

Select for Update Cursor Purpose

To lock the selected data.

Implicit Cursors in PL/SQL

Statements like 'select' do not require explicit cursor declaration.

Loop Types in PL/SQL

Different types of loops exist, such as simple and while.

Functional Dependency Definition

Defines relationships between sets of attributes.

Join Dependency Applications

Used to define relationships in the fifth normal form.

Disallowed Attributes in Relation

When composites and multi-valued attributes are disallowed, it is in first normal form.

Multi-Valued Dependency in 4NF

There can be at most one when in fourth normal form.

Dependency on Primary Key in 3NF

All non-key attributes must be dependent on the primary key.

Functional Dependency in Second Normal Form

Every non-prime attribute must be fully dependent on the primary key.

Relation Schemas Normal Form

Highest normal form can be determined from relation schemas.

ER Diagram Relations

The number of relations generated from an ER diagram can vary.

Business Operations and Transactions

Various database systems can support high availability and scalability.

ACID Properties

Some database systems may not support all ACID properties.