Study guide: DBMS

Course Outline

  1. Database Foundations and Abstraction
  2. Data Models and Business Rules
  3. Transactions and ACID Properties
  4. Database Design and ER Modeling
  5. Relational Structure and Integrity
  6. Normalization and Functional Dependencies
  7. Codd’s Relational Rules
  8. Relational Algebra Operators
  9. Joins, Division, and Grouping
  10. Relational Calculus and Expressiveness
  11. Constraints and Views
  12. SQL Categories and Commands
  13. Aggregates and NULL Values
  14. Nested Queries and Access Control

1. Database Foundations and Abstraction

Key Concepts & Definitions

  • Database: An organized collection of related data.
  • Database Management System: Software used to define, create, store, retrieve, update and control access to a database.
  • Data abstraction: Data abstraction hides unnecessary storage details and presents data at different levels.
  • Database system: The database, DBMS software, applications, users and hardware.

★ Must-know

  • A DBMS reduces unnecessary redundancy and inconsistency, supports efficient data operations, enforces integrity, provides security and authorization, supports concurrent access, backup and recovery, and offers data independence and multiple user views.

📌 A file system commonly stores data in separate files with greater duplication, weaker sharing and security controls, program-data dependence, and harder backup, recovery and concurrency; a DBMS manages an integrated database and provides controlled sharing, data independence, and transaction, concurrency and recovery facilities.

  • The three abstraction levels are:
    • internal or physical
    • conceptual or logical
    • external or view

📌 Physical data independence permits changes to internal storage without changing the logical schema or applications, whereas logical data independence permits conceptual-schema changes without changing all external views or application programs.

Further detail

  • Examples of DBMSs include:

    • MySQL
    • Oracle Database
    • PostgreSQL
    • Microsoft SQL Server
    • SQLite
  • DBMS limitations include cost and complexity, the need for trained staff, resource overhead, and the risk that a DBMS failure affects many users.

  • The internal or physical level describes how data is stored, including files, indexes and storage blocks.

  • The conceptual or logical level describes the data stored, its attributes, relationships and constraints.

  • The external or view level shows only the database portion required by a particular user or application, and different users may have different views.

  • The three-schema architecture consists of external schemas, one conceptual schema and an internal schema, with mappings between levels helping provide data independence.

  • DBMS functional components include:

    • query processor
    • storage manager
    • transaction/concurrency manager
    • recovery manager
    • authorization and integrity manager
    • data dictionary or catalog

Memory Hook

The database holds data; the DBMS manages it.

2. Data Models and Business Rules

Key Concepts & Definitions

  • Business rule: A statement defining or restricting how an organization operates and helping determine database constraints.

★ Must-know

  • Data models include:
    • hierarchical
    • network
    • relational
    • entity–relationship
    • object-oriented
    • object-relational
    • semi-structured

Further detail

  • The hierarchical model arranges data as a tree with parent-child relationships, while the network model connects records as a graph and supports multiple parents.

  • The relational model represents data as tables with rows and columns, while the ER model represents entities, attributes and relationships for conceptual design.

  • Basic data-model building blocks are:

    • entities or objects
    • attributes or properties
    • relationships
    • constraints
    • operations
  • Business rules can become constraints through:

    • PRIMARY KEY
    • FOREIGN KEY
    • UNIQUE
    • NOT NULL
    • CHECK
    • application-level validation
  • 🔄 Broad data-model evolution proceeded through:

    1. file-processing systems
    2. hierarchical and network databases
    3. relational databases
    4. object-oriented and object-relational systems
    5. distributed, web, cloud and NoSQL databases

Memory Hook

Files → hierarchical/network → relational → object-based → distributed and NoSQL.

3. Transactions and ACID Properties

Key Concepts & Definitions

  • Transaction: A logical unit of work containing one or more database operations.
  • Atomicity: All operations in a transaction happen or none happen.
  • Consistency: A transaction preserves database rules and valid states.
  • Isolation: Concurrent transactions do not interfere incorrectly, such as by reading another transaction’s incomplete transfer.
  • Durability: Committed changes survive failures, so a committed payment remains recorded after a restart.

★ Must-know

  • ACID properties are:
    • atomicity
    • consistency
    • isolation
    • durability

Further detail

  • Transaction management coordinates concurrent operations and failures so they do not leave the database in an invalid state.

  • BEGIN or START TRANSACTION begins a transaction, COMMIT makes changes permanent, and ROLLBACK undoes uncommitted changes.

Memory Hook

ACID

4. Database Design and ER Modeling

Key Concepts & Definitions

  • Entity: A distinguishable real-world object, such as a Student or Course.
  • Relationship: An association between entities, such as a student enrolling in a course.
  • Weak entity: An entity that cannot be uniquely identified by its own attributes alone and depends on an owner entity, using a partial key.

★ Must-know

  • 🔄 Database design proceeds through: requirements gathering, conceptual ER design, conversion to relational tables and keys, normalization, SQL implementation, testing of integrity, security, performance and recovery

📌 Cardinalities are one-to-one (1:1), one-to-many (1:M) and many-to-many (M:N); total participation is mandatory for every entity in a set, whereas partial participation is optional.

Further detail

  • ER attribute types include:

    • simple
    • composite
    • single-valued
    • multivalued
    • derived
    • key
  • Common ER notation uses rectangles for entities, ovals for attributes, diamonds for relationships, underlining for key attributes, double rectangles for weak entities and double diamonds for identifying relationships.

  • In the Student–Course example, STUDENT ENROLLS_IN COURSE is M:N and is represented by ENROLLMENT(StudentID, CourseID, EnrollDate), with composite primary key (StudentID, CourseID) and foreign keys to STUDENT and COURSE.

  • A DEPENDENT entity may be identified by the composite attributes (EmployeeID, DependentName).

5. Relational Structure and Integrity

Key Concepts & Definitions

  • Relational schema: A relational schema describes a relation’s name and attributes, while a relational database schema is the collection of relation definitions and their constraints.

★ Must-know

  • In the relational model, a relation is a table, a tuple is a row, an attribute is a column, a domain is an attribute’s allowed values, degree is the number of attributes, and cardinality is the number of tuples.

  • Key types are:

    • super key
    • candidate key
    • primary key
    • alternate key
    • foreign key
    • composite key
  • Integrity constraints include:

    • domain constraints
    • key constraints
    • entity integrity
    • referential integrity
    • NOT NULL
    • UNIQUE
    • CHECK
    • DEFAULT

Further detail

  • A super key uniquely identifies a row and may contain extra attributes, whereas a candidate key is a minimal super key.

  • A primary key is the selected candidate key, an alternate key is an unselected candidate key, and a foreign key refers to a primary or candidate key in the same or another table.

  • Entity integrity prohibits NULL primary-key values, and referential integrity requires a foreign key to match a referenced key value or be NULL when permitted.

  • A CHECK constraint requires a condition to be true, such as Marks BETWEEN 0 AND 100, while DEFAULT supplies a value when none is provided.

  • Poor relational design can cause:

    • insertion anomalies
    • update anomalies
    • deletion anomalies

6. Normalization and Functional Dependencies

Key Concepts & Definitions

  • Functional dependency: A functional dependency X → Y means each value of X determines exactly one value of Y.
  • First Normal Form: A relation in which each cell contains a single atomic value and there are no repeating groups or multivalued fields.
  • Second Normal Form: A relation in 1NF in which every non-prime attribute is fully dependent on every candidate key, with no partial dependency on part of a composite candidate key.
  • Third Normal Form: A relation in 2NF in which, for every non-trivial dependency X → A, X is a super key or A is a prime attribute.
  • Boyce–Codd Normal Form: A relation in which, for every non-trivial functional dependency X → Y, X is a super key; BCNF is stricter than 3NF.

Essential Points

  • In ENROLLMENT(StudentID, CourseID, StudentName, CourseName, Grade) with key (StudentID, CourseID), StudentName depends only on StudentID and CourseName only on CourseID, so the relations can be decomposed into STUDENT(StudentID, StudentName), COURSE(CourseID, CourseName) and ENROLLMENT(StudentID, CourseID, Grade).

  • A good decomposition is lossless, meaning that joining the decomposed tables recreates exactly the original relation, and should preserve dependencies where possible.

Memory Hook

Atomic values → no partial dependency → no transitive dependency → superkey determinants.

7. Codd’s Relational Rules

★ Must-know

  • Codd proposed rules for a relational DBMS, and Rule 0 requires a system to manage the database entirely through its relational capabilities.

  • The commonly taught 12 Codd rules include:

    • Information rule
    • Guaranteed access rule
    • Systematic treatment of NULLs

Further detail

  • Codd’s Information rule states that all information is represented as values in tables.

  • Codd’s Guaranteed access rule states that every atomic value is accessible by table name, primary key and column name.

  • Codd’s Systematic treatment of NULLs rule requires missing or inapplicable information to be handled consistently.

  • Codd's rule 4 requires a dynamic online catalog whose relationally represented metadata can be queried.

  • The language must support:

    • data definition
    • views
    • data manipulation
    • constraints
    • authorization
    • transactions
  • Codd's rule 6 says that theoretically updatable views should be updatable by the system.

  • The set-level operations are:

    • insert
    • update
    • delete
  • Codd's rule 8 requires physical data independence, so changes to physical storage do not require application changes.

  • Codd's rule 9 requires logical data independence, so logical schema changes should minimally affect applications.

  • Codd's rule 10 requires constraints to be definable in the database language and stored in the catalog.

  • Codd's rule 11 requires distribution independence, so users need not know whether data is distributed.

  • Codd's rule 12 prohibits low-level access from bypassing relational integrity and security rules.

8. Relational Algebra Operators

Key Concepts & Definitions

  • Relational algebra: A procedural query language that specifies a sequence of operations to obtain a required relation.

★ Must-know

  • Selection, written σ, selects rows satisfying a condition, as in σ Marks>70 (STUDENT).

  • Projection, written π, selects columns, as in π Name,Dept (STUDENT).

📌 Union, intersection, and difference require union-compatible relations with the same number of attributes and compatible corresponding domains.

Further detail

  • Relational algebra has the closure property: operators take relations as input and produce a relation as output.

  • Classical relational algebra removes duplicate rows from projection results.

  • Set difference R − S returns rows that occur in R but not in S.

  • Intersection R ∩ S returns the tuples common to both relations.

  • Rename, written ρ, renames a relation or its attributes.

9. Joins, Division, and Grouping

Key Concepts & Definitions

  • Join: Combines related tuples from two relations.
  • Division: Returns the A values in R(A, B) associated with every B value listed in S(B).

★ Must-know

📌 A theta join uses a general condition, an equijoin uses equality, and a natural join matches same-named attributes and removes duplicate join columns.

📌 An inner join returns only matching rows; a left outer join retains every left row and matching right rows, with NULLs for unmatched right-side attributes; a right outer join retains every right row and matching left rows; and a full outer join includes matched rows and unmatched rows from both sides.

Further detail

  • A cross join is a Cartesian product in which every left row pairs with every right row.

  • ENROLLED(StudentID, CourseID) ÷ REQUIRED(CourseID) returns students enrolled in every course listed in REQUIRED.

  • Grouping collects tuples with the same grouping-attribute values and can calculate aggregates such as COUNT, SUM, or AVG; extended relational algebra often writes grouping with γ.

  • The expression γ Dept; AVG(Marks)→AverageMarks (STUDENT) groups students by department and calculates their average marks.

  • Ungrouping expands collection-valued attributes in nested or complex relational models into separate tuples, reversing grouping or a nested collection.

10. Relational Calculus and Expressiveness

Key Concepts & Definitions

  • Relational calculus: A non-procedural query language that describes what data is required rather than the sequence of operations to obtain it, and is based on predicate logic.
  • Tuple Relational Calculus: Uses variables representing whole tuples, with general form { t | P(t) }, meaning the set of tuples t for which predicate P(t) is true.
  • Domain Relational Calculus: Uses variables representing individual attribute values rather than whole tuples, with general form { <x1, x2, ...> | P(x1, x2, ...) }.

★ Must-know

  • Safe TRC and DRC have the same expressive power as relational algebra, and relational algebra and safe relational calculus express the same class of relational queries.

Further detail

  • The TRC expression { t | STUDENT(t) AND t.Marks > 70 } returns student tuples whose marks exceed 70.

  • The DRC expression { <name> | EXISTS sid, dept, marks (STUDENT(sid, name, dept, marks) AND marks > 70) } returns the names of students scoring more than 70.

📌 A safe relational-calculus query produces a finite result based on values in the database rather than an unrestricted infinite set.

📌 Syntax defines the legal form of an expression, whereas semantics defines its meaning.

  • SQL is based on relational ideas but includes additional practical features.

Memory Hook

Algebra says how; calculus says what.

11. Constraints and Views

Key Concepts & Definitions

  • Constraint: A rule restricting the data that may be stored and helping maintain valid, consistent data.
  • View: A named query that behaves like a virtual table.

★ Must-know

  • Entity integrity requires a primary key never to be NULL.

  • Referential integrity requires a foreign key to reference an existing key value or to be NULL if permitted.

  • A conventional view stores its query definition rather than a separate copy of all result data, whereas a materialized view stores query results and must be refreshed.

Further detail

  • Domain constraints require values to have the correct type and range, while key constraints require key values to be unique.

  • A business constraint can require Marks to be between 0 and 100.

  • DEFAULT supplies a value when one is omitted, CHECK enforces a condition, and foreign keys may use ON DELETE or ON UPDATE actions such as CASCADE or SET NULL, subject to DBMS support and design needs.

  • Views can:

    • restrict access to sensitive rows or columns
    • simplify complex queries
    • present data conveniently
    • provide logical data independence
    • offer customized user views
  • Views involving these features may not be updatable:

    • GROUP BY
    • aggregates
    • DISTINCT
    • set operations
    • complex joins
  • A view depends on its underlying tables and may break when their structure changes; ordinary views do not necessarily store results or automatically improve performance.

12. SQL Categories and Commands

★ Must-know

📌 DDL defines or changes database structures and commonly includes CREATE, ALTER, DROP, and TRUNCATE.

📌 DML retrieves and modifies table data and commonly includes SELECT, INSERT, UPDATE, and DELETE.

  • DCL and TCL commands include:
    • GRANT
    • REVOKE
    • COMMIT
    • ROLLBACK
    • SAVEPOINT

📌 UPDATE and DELETE statements without a WHERE clause may affect every row, so the WHERE condition should be checked before either operation.

📌 DROP removes a table definition and its data, whereas TRUNCATE removes all rows while retaining the table structure, with details varying by DBMS.

Further detail

  • CREATE TABLE Course (CourseID INT PRIMARY KEY, Title VARCHAR(60) NOT NULL, Credits INT CHECK (Credits > 0)) creates a course table with a primary key, required title, and positive-credit constraint.

  • INSERT INTO Course (CourseID, Title, Credits) VALUES (101, 'DBMS', 4) inserts a course with ID 101, title DBMS, and 4 credits.

  • SELECT Name, Marks FROM Student WHERE Marks >= 60 ORDER BY Marks DESC retrieves student names and marks of at least 60, ordered from highest marks downward.

  • Some textbooks classify SELECT as DQL, while others include it under DML.

13. Aggregates and NULL Values

Key Concepts & Definitions

  • Aggregate function: Calculates one result from a set of rows.
  • NULL: Represents missing, unknown, or inapplicable information and is not zero or an empty string.

★ Must-know

  • COUNT(*) counts rows, whereas COUNT(column) counts non-NULL values in that column.

📌 WHERE filters rows before grouping, whereas HAVING filters groups after GROUP BY.

📌 SQL tests for NULL using IS NULL or IS NOT NULL; comparisons such as Marks = NULL are incorrect.

Further detail

  • SUM(column) totals numeric values, AVG(column) averages non-NULL numeric values, and MIN(column) and MAX(column) return the smallest and largest values.

  • Most aggregate functions ignore NULL values, COUNT(*) still counts rows, and arithmetic involving NULL generally yields NULL.

  • SQL uses three-valued logic with TRUE, FALSE, and UNKNOWN.

14. Nested Queries and Access Control

Key Concepts & Definitions

  • Subquery: A SELECT statement inside another SQL statement, whose result supplies data to the outer query.

★ Must-know

📌 A non-correlated subquery can execute independently, whereas a correlated subquery refers to a column from the outer query.

  • IN tests membership in a set of values, EXISTS tests whether at least one row exists, and a scalar subquery is used when one value is expected.

📌 ANY is true when a comparison holds for at least one value returned by a subquery, whereas ALL is true when it holds for every returned value.

📌 GRANT gives privileges and REVOKE removes privileges.

Further detail

📌 NOT EXISTS can be safer than NOT IN when NULL values are present.

  • Exact user and role syntax for GRANT and REVOKE depends on the DBMS.

Synthesis Tables

Relational Algebra and Calculus

DimensionRelational algebraRelational calculus
Query styleProcedural: specifies howNon-procedural: specifies what
Expression toolsOperators such as σ, π, and ÷Predicates, variables, quantifiers, and logical conditions
Operation sequenceExplicitNot specified
RelationshipCommon foundation for query processingLogical foundation of declarative query languages

Test yourself

Take a 11-question practice quiz on DBMS, with every answer explained.

1. Which statements accurately describe databases and DBMS functions?

2. A company changes its file organization while retaining its logical schema and applications. Which statements about data abstraction and independence are accurate?

Take the quiz →

Study with flashcards

Memorize the key concepts of DBMS with 10 flashcards.

What is a database?

An organized collection of related data.

What does DBMS software do with a database?

It defines, creates, stores, retrieves, updates, and controls access to it.

Which data models are included in the data-model classification?

Hierarchical, network, relational, entity–relationship, object-oriented, object-relational, and semi-structured models.

See the flashcards →

Related classes

Make your own study guides

Upload your class notes and Revizly writes a study guide, a quiz and flashcards in about a minute.

Study guide maker