★ Must-know
📌 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.
📌 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:
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:
The database holds data; the DBMS manages it.
★ Must-know
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:
Business rules can become constraints through:
🔄 Broad data-model evolution proceeded through:
Files → hierarchical/network → relational → object-based → distributed and NoSQL.
★ Must-know
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.
ACID
★ Must-know
📌 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:
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).
★ 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:
Integrity constraints include:
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:
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.
Atomic values → no partial dependency → no transitive dependency → superkey determinants.
★ 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:
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:
Codd's rule 6 says that theoretically updatable views should be updatable by the system.
The set-level operations are:
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.
★ 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.
★ 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.
★ Must-know
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.
Algebra says how; calculus says what.
★ 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:
Views involving these features may not be updatable:
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.
★ 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 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.
★ Must-know
📌 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.
★ Must-know
📌 A non-correlated subquery can execute independently, whereas a correlated subquery refers to a column from the outer query.
📌 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.
Relational Algebra and Calculus
| Dimension | Relational algebra | Relational calculus |
|---|---|---|
| Query style | Procedural: specifies how | Non-procedural: specifies what |
| Expression tools | Operators such as σ, π, and ÷ | Predicates, variables, quantifiers, and logical conditions |
| Operation sequence | Explicit | Not specified |
| Relationship | Common foundation for query processing | Logical foundation of declarative query languages |
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?
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.
Upload your class notes and Revizly writes a study guide, a quiz and flashcards in about a minute.
Study guide maker