Study guide: DBMS

Course Outline

  1. Database Systems and Their Purpose
  2. Abstraction, Architecture and Independence
  3. Data Models, Rules and Transactions
  4. Database Design and ER Foundations
  5. ER Relationships and Weak Entities
  6. Relational Concepts, Keys and Constraints
  7. Relational Design and Normalization
  8. Codd’s Relational Rules
  9. Relational Algebra Operators
  10. Joins and Algebra Extensions
  11. Relational Calculus and Query Power
  12. Constraints and Views
  13. SQL Commands and Aggregation
  14. NULLs and Nested Queries
  15. Security and Access Control

1. Database Systems and Their Purpose

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.
  • Database system: The database, DBMS software, applications, users and hardware.

★ Must-know

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

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 possibility that a DBMS failure affects many users.

Memory Hook

A database holds related data; a DBMS manages it.

2. Abstraction, Architecture and Independence

Key Concepts & Definitions

  • Data abstraction: Hides unnecessary storage details and presents data at different levels.
  • Data independence: The ability to change a schema at one level without requiring changes at the next higher level.

★ Must-know

📌 File systems commonly store data in separate files with high duplication and inconsistency risks, limited security and sharing, program-data dependence, and harder backup, recovery and concurrency; DBMSs manage structured integrated databases with controlled redundancy, authorization, data independence, and transaction, concurrency and recovery facilities.

  • The three-schema architecture has external schemas, one conceptual schema and an internal schema, with mappings between levels supporting data independence.

📌 Physical data independence allows internal storage changes, such as adding an index or changing file organization, without changing the logical schema or applications; logical data independence allows conceptual schema changes without changing all external views or application programs and is generally harder to achieve.

Further detail

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

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

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

  • A query processor parses, validates and optimizes SQL, then executes the query plan.

  • The storage manager manages files, indexes, buffers and access to stored data; transaction/concurrency, recovery, and authorization/integrity managers coordinate transactions, restore consistency after failures, and check permissions and constraints, respectively.

  • The data dictionary or catalog stores metadata such as table definitions, columns, constraints and users.

Memory Hook

External views → conceptual structure → internal storage.

3. Data Models, Rules and Transactions

Key Concepts & Definitions

  • Business rule: A statement defining or restricting how an organization operates and helping determine database constraints.
  • Transaction: A logical unit of work containing one or more database operations.

★ Must-know

  • The relational model represents data as tables, or relations, with rows and columns.

  • ACID means atomicity (all operations happen or none do), consistency (rules and valid states are preserved), isolation (concurrent transactions do not interfere incorrectly) and durability (committed changes survive failures).

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 parent relationships.

  • The ER model represents entities, attributes and relationships for conceptual design, while the object-oriented model represents data as objects with attributes and methods.

  • Object-relational models combine relational tables with selected object-oriented features, and semi-structured models use flexible structures such as JSON or XML documents.

  • Data models use these basic building blocks:

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

    • PRIMARY KEY
    • FOREIGN KEY
    • UNIQUE
    • NOT NULL
    • CHECK
    • application-level validation
  • Data management evolved from file-processing systems to hierarchical and network databases, relational databases, object-oriented and object-relational systems, and distributed, web, cloud and NoSQL databases.

  • 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 its changes permanent, and ROLLBACK undoes uncommitted changes.

Memory Hook

ACID: Atomicity, Consistency, Isolation, Durability.

4. Database Design and ER Foundations

Key Concepts & Definitions

  • Entity: A distinguishable real-world object, such as a Student or Course.
  • Attribute: Describes an entity, such as StudentID or Name.
  • Relationship: An association between entities, such as a student enrolling in a course.
  • Entity set: A collection of similar entities.

★ Must-know

  • The database design process collects requirements and business rules, creates a conceptual ER design, converts entities and relationships into relational tables and defines keys, normalizes relations, implements tables and database objects in SQL, then tests integrity, security, performance and recovery.

Further detail

  • ER attributes may be simple and indivisible, composite and divisible, single-valued, multivalued, derived from other data, or key attributes that uniquely identify an entity.

  • Common ER notation uses:

    • rectangle for entity
    • oval for attribute
    • diamond for relationship
    • underlined attribute for key
    • double rectangle for weak entity
    • double diamond for identifying relationship

5. ER Relationships and Weak Entities

Key Concepts & Definitions

  • Weak entity: Cannot be uniquely identified by its own attributes alone and depends on an owner strong entity, with a partial key.

★ Must-know

📌 A one-to-one (1:1) relationship pairs each entity with at most one entity on the other side, a one-to-many (1:M) relationship allows one entity to relate to many on the other side, and a many-to-many (M:N) relationship allows many entities on both sides.

📌 Total participation requires every entity in a set to participate in a relationship, whereas partial participation makes participation optional.

  • In the Student–Course–Instructor 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.

Further detail

📌 If each course has one instructor, INSTRUCTOR TEACHES COURSE can be 1:M with InstructorID as a foreign key in COURSE; if courses can have several instructors, TEACHES should be a separate relationship table.

  • A DEPENDENT weak entity may be identified by the composite of EmployeeID and DependentName.

Memory Hook

Strong entities identify themselves; weak entities depend on an owner.

6. Relational Concepts, Keys and Constraints

Key Concepts & Definitions

  • Relation: A relation is a table, a tuple is a row, an attribute is a column, and a domain is the set of allowed values for an attribute.
  • 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

📌 A super key uniquely identifies a row and may contain extra attributes; a candidate key is a minimal super key; an alternate key is a candidate key not chosen as primary; a primary key is the selected candidate key; a foreign key refers to a primary or candidate key in the same or another table; and a composite key contains two or more attributes.

  • Domain constraints restrict values to an allowed domain, key constraints require uniqueness, entity integrity prohibits NULL primary-key values, and referential integrity requires a foreign key to match a referenced key or be NULL when allowed.

Further detail

  • The degree of a relation is its number of attributes, and its cardinality is its number of tuples.

  • If StudentID and Email are both unique in STUDENT(StudentID, Email, Name), both may be candidate keys; selecting StudentID as primary makes Email an alternate key.

  • NOT NULL prohibits NULL values, UNIQUE prevents duplicates (with NULL handling varying by DBMS), CHECK requires a condition such as Marks BETWEEN 0 AND 100, and DEFAULT supplies a value when none is provided.

7. Relational Design and Normalization

Key Concepts & Definitions

  • Functional dependency: A functional dependency X → Y means each value of X determines exactly one value of Y.

★ Must-know

  • An insertion anomaly prevents adding one fact without another unrelated fact, an update anomaly requires changing the same fact in multiple rows, and a deletion anomaly removes another useful fact when a row is deleted.

  • A relation is in 1NF when every cell contains one atomic value and there are no repeating groups or multivalued fields.

  • A relation is in 2NF if it is in 1NF and every non-prime attribute is fully dependent on every candidate key, with no partial dependency on part of a composite candidate key.

  • A relation is in 3NF if it is in 2NF and has no inappropriate transitive dependency of non-key attributes on a key; formally, for every non-trivial X → A, X is a super key or A is a prime attribute.

  • A relation is in BCNF if, for every non-trivial functional dependency X → Y, X is a super key; BCNF is stricter than 3NF.

Further detail

  • Good relational design minimizes duplicated facts, represents relationships clearly, avoids unnecessary NULLs and supports lossless decomposition.

  • A Student table storing PhoneNumbers as '111, 222' violates 1NF; storing one phone per row in a separate StudentPhone table removes the multivalued field.

  • In ENROLLMENT(StudentID, CourseID, StudentName, CourseName, Grade) with key (StudentID, CourseID), StudentName depends only on StudentID and CourseName only on CourseID, so decomposition into STUDENT(StudentID, StudentName), COURSE(CourseID, CourseName) and ENROLLMENT(StudentID, CourseID, Grade) removes those partial dependencies.

  • In EMPLOYEE(EmpID, DeptID, DeptName), EmpID → DeptID and DeptID → DeptName make DeptName transitively dependent on EmpID, so decomposition into EMPLOYEE(EmpID, DeptID) and DEPARTMENT(DeptID, DeptName) removes that dependency.

📌 A good decomposition is lossless, so joining the decomposed tables recreates exactly the original relation, and preserves dependencies where possible.

Memory Hook

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

8. Codd’s Relational Rules

★ Must-know

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

  • Codd's rules 4–12 require: a dynamic online catalog whose relational metadata can be queried, a comprehensive data sublanguage for definition, views, manipulation, constraints, authorization, and transactions, theoretically updatable views to be updatable, set-level insert, update, and delete, physical data independence, logical data independence, integrity constraints defined in the database language and stored in the catalog, distribution independence, no low-level bypass of relational integrity or security rules

Further detail

  • The commonly taught 12 Codd rules include the Information rule, under which all information is represented as values in tables.

  • The commonly taught 12 Codd rules include the Guaranteed access rule, under which every atomic value is accessible by table name, primary key and column name.

  • The commonly taught 12 Codd rules include systematic treatment of NULLs, requiring missing or inapplicable information to be handled consistently.

9. 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), and classical relational algebra removes duplicate rows.

Further detail

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

  • Set difference R − S returns rows in R but not in S, and Cartesian product R × S returns every combination of rows from the two relations.

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

  • Rename, written ρ, renames a relation or its attributes, as in ρ S(STUDENT).

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

10. Joins and Algebra Extensions

Key Concepts & Definitions

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

★ 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 all left rows and matching right rows, with NULLs for unmatched right-side attributes.

Further detail

  • A right outer join retains all right rows and matching left rows, while a full outer join retains matched rows and unmatched rows from both sides.

  • 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 represents it with γ.

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

  • Ungrouping, often written UNGROUP in nested or complex relational models, reverses grouping by expanding collection-valued attributes into separate tuples.

  • The exact notation for ungrouping varies by textbook.

11. Relational Calculus and Query Power

Key Concepts & Definitions

  • Relational calculus: A non-procedural query language based on predicate logic that describes what data is required rather than the sequence of operations used to obtain it.
  • Tuple Relational Calculus: Uses variables representing whole tuples, and its general form { t | P(t) } denotes the 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, so relational algebra and safe relational calculus express the same class of relational queries and are relationally complete.

Further detail

  • The TRC expression { t | STUDENT(t) AND t.Marks > 70 } returns student tuples whose marks are greater than 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.

  • Relational algebra uses operators such as σ, π, and ÷, while relational calculus uses predicates, variables, quantifiers, and logical conditions.

  • Relational algebra makes its operational sequence explicit, whereas relational calculus does not specify a sequence of operations.

📌 A safe relational-calculus query must produce 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.

12. 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 that a primary key is never NULL.

  • Referential integrity requires a foreign key to reference an existing key value or to be NULL if NULL is 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 or range, and key constraints require key values to be unique.

  • A business-defined constraint can require Marks to be between 0 and 100, as in CHECK (Marks BETWEEN 0 AND 100).

  • DEFAULT supplies a value when one is omitted, and CHECK enforces a condition.

  • Views can:

    • restrict access to sensitive rows or columns
    • simplify complex queries
    • present convenient customized data
    • provide logical data independence

📌 Some views are not updatable, especially those involving GROUP BY, aggregates, DISTINCT, set operations, or complex joins, and the rules vary by DBMS.

  • A view depends on its underlying tables and may break if their structure changes; ordinary views do not necessarily store results or automatically improve performance.

  • Foreign keys may use ON DELETE or ON UPDATE actions such as CASCADE or SET NULL, subject to DBMS support and design needs.

Memory Hook

A table stores base data; a conventional view presents a query.

13. SQL Commands and Aggregation

★ 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.

📌 UPDATE or DELETE statements without a WHERE clause may affect every row, so the WHERE clause should be checked before execution.

  • Aggregate functions calculate one result from a set of rows:
    • COUNT(*) counts rows
    • COUNT(column) counts non-NULL values
    • SUM(column) totals numeric values
    • AVG(column) averages non-NULL numeric values
    • MIN(column) returns the smallest value
    • MAX(column) returns the largest value

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

Further detail

  • DCL controls permissions through commands such as GRANT and REVOKE, while TCL controls transactions through COMMIT, ROLLBACK, and SAVEPOINT.

  • A CREATE TABLE statement can define CourseID as a primary key, Title as VARCHAR(60) NOT NULL, and Credits as INT with CHECK (Credits > 0).

  • SELECT Name, Marks FROM Student WHERE Marks >= 60 ORDER BY Marks DESC selects students with at least 60 marks and orders them by marks in descending order.

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

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

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

14. NULLs and Nested Queries

Key Concepts & Definitions

  • NULL: Represents missing, unknown, or inapplicable information and is not zero or an empty string.
  • Subquery: A SELECT statement inside another SQL statement, and its result supplies data to the outer query.

★ Must-know

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

Further detail

  • Most aggregate functions ignore NULL values, but COUNT(*) still counts rows.

  • Arithmetic involving NULL generally yields NULL, and SQL uses three-valued logic: TRUE, FALSE, and UNKNOWN.

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

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

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

📌 NOT EXISTS can be safer than NOT IN when the subquery may contain NULLs.

15. Security and Access Control

★ Must-know

  • GRANT gives privileges and REVOKE removes privileges; for example, GRANT SELECT ON Student TO user1 grants SELECT access, and REVOKE SELECT ON Student FROM user1 removes it.

Further detail

  • The exact syntax for specifying users or roles in access-control statements depends on the DBMS.

Synthesis Tables

File System and DBMS

DimensionFile systemDBMS
StorageSeparate filesStructured integrated database
RedundancyHigh duplication riskRedundancy can be controlled
AccessLimited security and sharingAuthorization and controlled sharing
RecoveryHarder backup and concurrencyTransaction, concurrency and recovery facilities

Normal Forms

FormRequirementDependency issue
1NFAtomic values; no repeating groupsMultivalued fields
2NF1NF and full dependence on candidate keysPartial dependency
3NF2NF; formal superkey-or-prime testInappropriate transitive dependency
BCNFEvery determinant is a super keyStricter than 3NF

Test yourself

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

1. Which statements accurately distinguish a database from a DBMS?

2. A database architect is assigning responsibilities across the three-schema architecture. Which statements are correct?

Take the quiz →

Study with flashcards

Memorize the key concepts of DBMS with 95 flashcards.

What is a database?

An organized collection of related data.

What software defines, creates, stores, retrieves, updates, and controls access to a database?

A Database Management System (DBMS).

What does a DBMS do to reduce data problems?

It reduces unnecessary data redundancy and inconsistency.

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