★ Must-know
Further detail
Examples of DBMSs include:
DBMS limitations include cost and complexity, the need for trained staff, resource overhead, and the possibility that a DBMS failure affects many users.
A database holds related data; a DBMS manages it.
★ 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.
📌 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.
External views → conceptual structure → internal storage.
★ 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:
Business rules can be implemented through:
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.
ACID: Atomicity, Consistency, Isolation, Durability.
★ Must-know
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:
★ 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.
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.
Strong entities identify themselves; weak entities depend on an owner.
★ 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.
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.
★ 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.
Atomic → no partial dependency → no transitive dependency → superkey determinants.
★ 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.
★ 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.
★ 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.
★ Must-know
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.
Algebra says how; calculus says what.
★ 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:
📌 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.
A table stores base data; a conventional view presents a query.
★ 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.
📌 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.
★ 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.
📌 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.
★ Must-know
Further detail
| Dimension | File system | DBMS |
|---|---|---|
| Storage | Separate files | Structured integrated database |
| Redundancy | High duplication risk | Redundancy can be controlled |
| Access | Limited security and sharing | Authorization and controlled sharing |
| Recovery | Harder backup and concurrency | Transaction, concurrency and recovery facilities |
| Form | Requirement | Dependency issue |
|---|---|---|
| 1NF | Atomic values; no repeating groups | Multivalued fields |
| 2NF | 1NF and full dependence on candidate keys | Partial dependency |
| 3NF | 2NF; formal superkey-or-prime test | Inappropriate transitive dependency |
| BCNF | Every determinant is a super key | Stricter than 3NF |
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?
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.
Upload your class notes and Revizly writes a study guide, a quiz and flashcards in about a minute.
Study guide maker