# Database Management Systems > Database course notes covering ER modeling, the relational model, normalization, SQL querying, transaction management, database objects, indexing and access control. - Author: Muhammet Ali Köker - Language: en - Canonical: https://alikoker.com.tr/en/database-management-systems - Translation: https://alikoker.com.tr/veri-tabani-yonetim-sistemleri - Published: 2014-11-09T18:50:00+03:00 - Modified: 2026-07-24T20:35:00+03:00 - Verified: 2026-08-08T15:00:00+03:00 - Type: article I originally organized my database notes in the sequence of conceptual modeling, the relational model, SQL, and finally integrity, transactions and access control. This revision keeps that order. I rechecked key and normalization terminology in particular: in BCNF, the determinant of every non-trivial functional dependency must be a **superkey**, not necessarily a candidate key. ## Unit 1: Fundamental Concepts ### Classical file organization Before a DBMS manages data, an application can store records directly in files. The file approach is simple for isolated programs but becomes difficult when several applications need the same information, concurrent updates, shared constraints and independent query patterns. Common classical organizations include: - sequential files, - indexed files, - hash/direct-access files. A **record** groups fields describing one logical item; a **field** contains one attribute value. These terms remain useful even though a relational DBMS exposes rows and columns at a higher abstraction level. ### Sequential files Sequential organization places records in a defined order or in append order. Sequential scans are efficient, but locating one arbitrary record may require scanning unless an auxiliary index is available. ### Indexed files An index stores search keys and references to data locations. It accelerates selected access paths at the cost of storage and maintenance on insert/update/delete operations. ### Hash/direct-access files Hash organization computes a bucket/location from a key. Average equality lookup can be efficient when the hash distribution is good, but collisions must be handled and ordered/range scans are not its strength. ### Database systems A database system separates logical data management from application-specific file code. A DBMS typically provides: - data definition, - querying and modification, - constraints, - transactions, - concurrency control, - recovery, - authorization, - metadata/catalog management. The main advantage is not merely that data are stored in tables. It is that common integrity and access semantics can be enforced independently of one application program. ### Data models A data model defines the concepts used to represent data and relationships. Relational, hierarchical, network and object-oriented models are historical examples. Conceptual models such as ER are used during analysis, while a relational schema describes the logical implementation in tables. ## Unit 2: Entity-Relationship Model ### ER model The Entity-Relationship model describes a domain through entities, attributes and relationships before implementation details are fixed. ### Entity and entity set An **entity** is a distinguishable domain object, such as one employee or one course. An **entity set** groups entities of the same conceptual type. ### Relationships A relationship associates entities. A relationship set describes relationships of the same kind. Relationships can also have attributes when information belongs to the association itself rather than to either participating entity. ### Attributes and domains An attribute represents a property. Its **domain** specifies the set/type of admissible values. Attributes may be: - simple, - composite, - single-valued, - multivalued, - stored, - derived. A derived attribute can be calculated from other data; storing both the source and the derived value creates a consistency obligation. ### Roles When the same entity type participates more than once in a relationship, role names distinguish participation. A self-referential employee-manager relationship is a common example. ### Cardinality Typical mappings are: ```text 1 : 1 1 : N N : 1 M : N ``` Cardinality expresses how many entities on one side may relate to entities on the other side. Optional/mandatory participation adds a separate existence constraint. ### Keys A **superkey** is any attribute set that uniquely identifies an entity/tuple. A **candidate key** is a minimal superkey: no proper subset still uniquely identifies the entity. A chosen candidate key becomes the primary key in the relational schema. Other candidate keys can be enforced as alternate unique keys. ### Weak and strong entities A strong entity can be identified by its own key. A weak entity depends on an owner entity and is typically identified by an owner key plus a partial key/discriminator. ### Mapping ER to tables Common mapping rules are: - each regular entity set becomes a table, - simple attributes become columns, - composite attributes are decomposed as needed, - multivalued attributes often become a separate table, - 1:N relationships normally place a foreign key on the N side, - M:N relationships become an associative table containing foreign keys to both sides, - weak entities include the owner's key as part of their identifying key. These are design rules, not substitutes for checking actual business constraints. ## Unit 3: The Relational Data Model ### Relation The relational model represents data as relations. In implementation terminology, a relation is commonly presented as a table, a tuple as a row, and an attribute as a column, although the mathematical model has stricter semantics than every SQL table implementation. ### Schema and instance A database **schema** defines relations, attributes, domains and constraints. A database **instance/state** is the actual set of stored values at a particular time. ### Table properties In the mathematical relational model: - tuples are unordered, - attributes are identified by name, - duplicate tuples are not part of a relation, - each attribute draws values from a domain. SQL tables are bags by default for query results and can contain duplicates unless constraints/query operators prevent them, so SQL and pure relational algebra should not be treated as identical. ### Integrity Integrity constraints keep database states within the rules of the model/domain. Important types include: - domain constraints, - key/uniqueness constraints, - entity integrity, - referential integrity, - application/business constraints. ### Primary and foreign keys A primary key identifies a row and cannot contain null values under normal SQL semantics for a primary-key constraint. A foreign key requires referencing values to match a candidate/unique key in the referenced table, except for nullable cases permitted by the constraint definition. Referential actions determine what happens on parent changes/deletion where the DBMS supports them. ### Business rules and dependencies Not every rule is naturally a simple column constraint. Dependencies among attributes drive normalization, while cross-row/cross-table domain rules may require additional declarative constraints, application logic or carefully designed procedural enforcement. ## Unit 4: Relational Database Design ### Normalization Normalization decomposes relations based on dependencies to reduce redundancy and update anomalies while preserving information and, where possible, dependencies. The goal is not "more tables at any cost" but a schema in which facts have clear ownership and integrity is maintainable. ### Functional dependency For attributes sets `X` and `Y`: ```text X -> Y ``` means that tuples agreeing on `X` must agree on `Y` in every valid relation state satisfying the dependency. A dependency is **full** when no proper subset of the determinant is sufficient. It is **partial** when part of a composite determinant already determines the dependent attributes. ### First normal form 1NF requires attribute values to be atomic with respect to the chosen relational design and removes repeating groups from a single tuple structure. "Atomic" is relative to the data model: a value should be treated as one domain value rather than encoded as an ad-hoc repeating list that the DBMS must parse to recover relations. ### Second normal form 2NF requires 1NF and removes partial dependency of non-prime attributes on a proper subset of a candidate key. It is relevant when composite keys exist. ### Third normal form A common formulation of 3NF says that for every functional dependency `X -> A`, at least one of the following holds: - the dependency is trivial, - `X` is a superkey, - `A` is a prime attribute (part of some candidate key). The practical effect is to remove many transitive dependency anomalies among non-key facts. ### Boyce-Codd normal form BCNF is stronger: > For every non-trivial functional dependency `X -> Y`, `X` must be a superkey. The determinant does **not** need to be a minimal candidate key. Every candidate key is a superkey, but not every superkey is a candidate key. BCNF resolves some anomalies that can remain in 3NF, particularly in schemas with overlapping candidate keys. A lossless decomposition may sometimes sacrifice dependency preservation, so design tradeoffs should be explicit. ### Fourth and fifth normal forms 4NF addresses non-trivial multivalued dependencies where the determinant is not a superkey. 5NF addresses join dependencies that cannot be explained by candidate keys alone. These forms are important theoretically but appear less frequently in ordinary application schemas than 1NF through BCNF. ### Normalization and performance Denormalization can be justified for measured performance or reporting requirements, but it introduces duplicated facts and therefore synchronization/integrity responsibilities. It should be a controlled optimization, not a substitute for understanding dependencies. ## Unit 5: Relational Algebra Relational algebra is a formal collection of operators that transform relations. ### Selection Selection filters tuples: ```text sigma_condition(R) ``` It corresponds conceptually to a SQL `WHERE` predicate. ### Projection Projection selects attributes: ```text pi_A,B(R) ``` Pure relational projection removes duplicates; SQL `SELECT` does not unless `DISTINCT` is specified. ### Cartesian product ```text R x S ``` pairs every tuple of `R` with every tuple of `S`. Join operations can be understood as product plus restriction, although DBMS engines do not need to physically materialize the full product. ### Union, intersection and difference Set operations require compatible relation headings/domains. SQL provides `UNION`, `INTERSECT` and product-specific difference operators such as Oracle `MINUS` or standard `EXCEPT` in other systems. ### Natural join Natural join matches same-named attributes automatically. It is concise in theory but can be risky in evolving SQL schemas because adding a same-named column may silently change join semantics. Explicit join predicates are usually clearer in production SQL. ### Division Relational division models "for all" queries, such as finding entities related to every member of another set. SQL normally expresses it using nested `NOT EXISTS`, grouping/counting or equivalent formulations. ## Unit 6: SQL ### Role of SQL SQL combines data definition, querying, manipulation, access control and transaction-related facilities. It is declarative: the query specifies the required result, while the optimizer chooses an execution plan subject to DBMS semantics. ### SELECT structure A typical query is: ```sql SELECT column_list FROM table_source WHERE row_condition GROUP BY grouping_columns HAVING group_condition ORDER BY sort_expression; ``` Logical query processing order is not identical to the textual order, which explains why aliases visible in `ORDER BY` may not be available in every other clause. ### Expressions and precedence Arithmetic expressions can combine columns and literals. Parentheses should make intended precedence explicit when an expression is not obvious. ### NULL `NULL` represents missing/unknown/inapplicable information according to context; it is not zero or an empty string in the relational-theory sense. SQL comparisons with null participate in three-valued logic. Use: ```sql IS NULL IS NOT NULL ``` rather than `= NULL`. ### Aliases and concatenation Column aliases improve result readability. Oracle uses `||` for string concatenation: ```sql FIRST_NAME || ' ' || LAST_NAME ``` ### Duplicate rows `SELECT DISTINCT` removes duplicate result rows. Without it, SQL generally retains duplicate rows produced by the query. ### Predicates Common predicates include: ```text = <> < > <= >= BETWEEN IN LIKE IS NULL ``` `BETWEEN` is inclusive at both bounds. `LIKE` commonly uses `%` for any sequence and `_` for one character, subject to collation/character semantics of the DBMS. Logical operators are `NOT`, `AND`, `OR`; parentheses should be used when mixing them to avoid precedence mistakes. ### Ordering `ORDER BY` is what gives a query result a defined presentation order. Storage order or an index should not be treated as an implicit row-order guarantee. ## Unit 7: SQL Functions ### Character functions Functions such as `LOWER`, `UPPER` and `LENGTH` transform or inspect text. Exact length semantics can differ between characters and bytes for multibyte encodings; Oracle also provides byte-oriented variants where required. ### Numeric functions `ROUND`, `TRUNC` and `MOD` perform common numeric operations. Rounding and truncation semantics should be distinguished, especially for negative values and decimal positions. ### Date functions and conversion Dates should be handled as date/time types, not formatted strings, whenever possible. Explicit conversion makes format contracts visible: ```sql SELECT TO_DATE('2014-11-09', 'YYYY-MM-DD') FROM DUAL; ``` `TO_CHAR` formats values as text; `TO_NUMBER` and `TO_DATE` parse text according to format/NLS rules. Implicit conversion can make behavior dependent on session settings and should not be relied upon for stable application interfaces. ### General functions Oracle's `NVL` replaces null with another expression under its type-conversion rules. `COALESCE` is a standard alternative that selects the first non-null expression. `DECODE` is an Oracle-specific conditional function retained in many legacy queries. `CASE` is generally more expressive and portable. Functions can be nested; type conversion and null propagation should be checked at each layer rather than inferred from final syntax alone. ## Unit 8: Grouped Analysis Aggregate functions include: - `AVG`, - `SUM`, - `STDDEV`, - `VARIANCE`, - `MAX`, - `MIN`, - `COUNT`. Most aggregates ignore null input values, with details depending on the expression. `COUNT(*)` counts rows; `COUNT(column)` counts non-null values of that expression. `GROUP BY` partitions rows into groups before aggregates are calculated. Every selected non-aggregate expression must be compatible with the grouping rules of the DBMS. `HAVING` filters groups after grouping, whereas `WHERE` filters rows before grouping. Grouping by several columns forms groups for each distinct combination. ## Unit 9: Multiple Tables ### Joins Joins combine rows according to a relationship: ```sql SELECT ... FROM EMPLOYEE e JOIN DEPARTMENT d ON d.DEPARTMENT_ID = e.DEPARTMENT_ID; ``` A missing join predicate can create a Cartesian product. Qualified column names remove ambiguity when different tables contain the same column name. ### Non-equality and outer joins A join predicate need not use equality; range and inequality joins are possible where the model calls for them. Outer joins preserve unmatched rows from one or both sides and fill missing counterpart columns with null. Predicate placement matters because a `WHERE` condition can unintentionally eliminate the null-extended rows and change the effective semantics. ### Self join A table can be joined to itself through aliases, for example employee-to-manager relationships. ### Set operations `UNION` combines compatible results and removes duplicates. `UNION ALL` retains duplicates and avoids the duplicate-elimination requirement. `INTERSECT` keeps common rows. Oracle `MINUS` returns rows in the first result not present in the second. ## Unit 10: Complex Queries ### Subqueries A subquery supplies a value, row or relation to another query. It can appear in predicates, expressions, `FROM`, DML and other supported contexts. A scalar/single-row subquery must return at most one row where a scalar value is required. Multiple rows cause an error in such a context. ### Multi-row operators `IN` tests membership. `ANY`/`SOME` compares against at least one subquery value. `ALL` requires the comparison to hold for all returned values. Nulls can make `NOT IN` especially surprising: if the subquery contains null, three-valued logic may prevent expected matches. `NOT EXISTS` often expresses anti-join intent more safely when nullability is possible. ### Correlation and inline views A correlated subquery references values from the outer query and is conceptually reevaluated per outer row, although the optimizer may transform execution. A subquery in `FROM` creates an inline view/derived table that can be joined or aggregated like another relation. ## Unit 11: DML and Transaction Processing ### INSERT, UPDATE and DELETE `INSERT` adds rows, `UPDATE` changes matching rows and `DELETE` removes matching rows. Every DML statement should be checked for its predicate scope; an omitted `WHERE` can intentionally or accidentally affect all rows. Data can be inserted explicitly or from another query. Functions and explicit conversions can be used as part of inserted expressions. ### Transaction A transaction groups operations into a logical unit of work. `COMMIT` makes the transaction's changes durable/visible according to DBMS isolation rules. `ROLLBACK` cancels uncommitted work back to the transaction or savepoint scope supported by the DBMS. Atomicity does not mean every transaction is isolated from every concurrent effect; isolation level determines which interleavings are visible. Application design should define transaction boundaries around business invariants rather than commit arbitrarily inside reusable low-level routines. ## Unit 12: Database Objects ### Tables and data types `CREATE TABLE` defines columns and constraints. Names should be stable, consistent and meaningful within the schema conventions. A table can also be created from a query where the DBMS syntax supports it, but constraints, defaults, comments and other metadata may not be copied automatically. Schema evolution can add/modify columns subject to existing data and dependency rules. `DROP TABLE` removes a table object; `TRUNCATE` removes rows through DDL-like semantics that differ from row-by-row `DELETE`, including transaction/logging behavior depending on the DBMS. ### Constraints Common constraints are: - `NOT NULL`, - `UNIQUE`, - `PRIMARY KEY`, - `FOREIGN KEY`, - `CHECK`. Constraints should encode stable data invariants in the database whenever feasible so every writer is subject to the same rules. ### Views A view stores a query definition and presents it as a virtual relation. Views can: - simplify complex queries, - restrict exposed columns/rows, - stabilize an interface, - support security boundaries. Whether a view is directly updatable depends on its query structure and DBMS rules. ### Indexes An index is an access structure, not a logical requirement for a query. It can improve equality/range/join/order access patterns but adds storage and write-maintenance cost. An index is most useful when its key order/selectivity and the workload allow the optimizer to avoid substantial work. Very small tables or predicates returning most rows may favor scans. Dropping an index changes performance possibilities but should not change correct logical query results unless application code incorrectly depended on implicit ordering. ## Unit 13: User Access Control Database security separates authentication from authorization. Users/principals receive system privileges, object privileges and roles according to DBMS facilities. Privileges should follow least privilege. Granting broad schema/system authority because it is convenient during development creates a larger failure and compromise domain. Object privileges can govern operations such as `SELECT`, `INSERT`, `UPDATE`, `DELETE` or execution of program units. `REVOKE` removes previously granted rights subject to dependency/cascade semantics. Roles group privileges so policy can be managed at a meaningful organizational level rather than as many individual grants. Password management, external identity integration and privileged administrative accounts require stronger controls than ordinary application schemas. ## Unit 14: Integrating the Advanced SQL Topics The relational model, normalization, SQL and physical access structures belong to different layers: ```text domain facts ↓ conceptual ER model ↓ relational schema + constraints ↓ SQL queries and transactions ↓ indexes / execution plans / storage ``` A normalization problem is not solved by adding an index. A slow query is not automatically evidence that the schema should be denormalized. A transaction problem is not solved by hiding a `COMMIT` inside every procedure. The useful discipline is to identify which layer owns the problem: model the facts correctly, enforce their invariants, express the required set operation in SQL, and only then optimize the physical execution path with measured evidence. ## Cite This Work Köker, M. A. (2014). Database Management Systems. alikoker.com.tr. https://alikoker.com.tr/en/database-management-systems - BibTeX: https://alikoker.com.tr/en/database-management-systems.bib - RIS: https://alikoker.com.tr/en/database-management-systems.ris - CSL-JSON: https://alikoker.com.tr/en/database-management-systems.csl.json