This cheat sheet covers the core database ideas that connect mathematical query models, schema design, and practical SQL programming. Students need these concepts to understand how relational databases store, retrieve, protect, and organize data. It is especially useful for comparing relational algebra expressions with SQL queries and for reviewing design rules before exams or projects.
Relational algebra provides formal operators such as selection, projection, join, union, difference, and rename. Schema design focuses on keys, dependencies, normalization, and avoiding redundancy or update anomalies. SQL applies these ideas through SELECT, JOIN, GROUP BY, constraints, indexes, and transaction control.
Transactions rely on ACID properties to keep database operations correct even when many users access data at the same time.
Key Facts
- Selection filters rows and is written as sigma condition(R), where only tuples in relation R that satisfy the condition are returned.
- Projection chooses columns and is written as pi attributes(R), and duplicate rows are removed in pure relational algebra.
- A theta join can be written as R join condition S and returns combined tuples from R and S that satisfy the join condition.
- A primary key uniquely identifies each row in a table, and a foreign key references a candidate key in another table to enforce relationships.
- A relation is in 1NF when every attribute value is atomic and each row-column position contains a single value.
- A relation is in 2NF when it is in 1NF and every non-key attribute depends on the whole candidate key, not just part of it.
- A relation is in 3NF when it is in 2NF and no non-key attribute depends transitively on a candidate key through another non-key attribute.
- A SQL aggregate query with GROUP BY forms groups first, then computes functions such as COUNT, SUM, AVG, MIN, and MAX for each group.
Vocabulary
- Relation
- A relation is a table-like set of tuples with named attributes and no duplicate tuples in the formal relational model.
- Tuple
- A tuple is one row in a relation, representing a single record with one value for each attribute.
- Functional Dependency
- A functional dependency X -> Y means that if two rows agree on attributes X, they must also agree on attributes Y.
- Candidate Key
- A candidate key is a minimal set of attributes that uniquely identifies every tuple in a relation.
- Join
- A join combines rows from two relations when a specified condition between their attributes is true.
- Transaction
- A transaction is a sequence of database operations treated as one logical unit that either fully succeeds or is rolled back.
Common Mistakes to Avoid
- Confusing selection and projection is wrong because selection filters rows, while projection selects columns.
- Using SELECT without a join condition in a multi-table query is wrong because it can create a Cartesian product with many unintended row combinations.
- Assuming GROUP BY happens after SELECT expressions is wrong because SQL groups rows before computing aggregate results for the final output.
- Treating every unique column as a primary key is wrong because a primary key should be minimal, stable, non-null, and chosen to identify rows reliably.
- Ignoring transaction isolation is wrong because concurrent updates can cause lost updates, dirty reads, or inconsistent results when operations overlap.
Practice Questions
- 1 Given Student(sid, name, major) with 120 rows, if 30 students have major = 'CS', how many rows are returned by sigma major = 'CS'(Student)?
- 2 Write the SQL query to list each department id and the number of employees in Employee(eid, name, dept_id, salary), showing only departments with more than 5 employees.
- 3 Given Orders(order_id, customer_id) with 200 rows and Customers(customer_id, name) with 50 rows, what is the maximum possible number of rows in Orders cross join Customers?
- 4 Explain why splitting a table Enrollment(student_id, student_name, course_id, course_title, instructor) into separate Student, Course, and Enrollment tables can reduce update anomalies.
Understanding Databases Relational Algebra and SQL
A useful way to understand a database query is to imagine a series of temporary tables. Each operation takes one or more input tables and produces a new result table. The order matters.
In a typical SQL query, rows are chosen from tables, matched through joins, filtered, grouped, filtered again at the group level, then finally arranged for display. SQL is written in a different order from this logical process. This explains why a condition about an aggregate belongs in HAVING rather than WHERE.
WHERE removes individual rows before groups exist. HAVING removes whole groups after values such as counts or averages have been calculated.
Joins need especially careful attention because they can quietly create extra rows. If one customer has three orders, joining Customers to Orders creates three rows for that customer. This is correct because each row represents one matching customer-order pair.
Problems arise when a join condition is missing or uses the wrong columns. Then every row in one table may pair with many unrelated rows in the other table. This is often called a Cartesian product.
Outer joins have another important purpose. A left join keeps every row from the table on the left, even when no matching row exists.
The missing values appear as NULL. This is useful for finding products with no sales, students with no enrollment record, or employees not assigned to a project.
NULL does not mean zero, an empty string, or false. It means the value is unknown, missing, or not applicable. Comparisons involving NULL do not behave like ordinary true or false tests.
A condition such as salary equals NULL will not find missing salaries. SQL uses IS NULL for that job. NULL also affects aggregates.
COUNT of a column ignores NULL values, while COUNT of rows counts every row. Students often make mistakes here when calculating attendance, grades, or survey responses. It is worth checking whether a missing value should be stored as NULL, a real zero, or a separate status value.
Good table design begins with functional dependencies. A dependency states that one fact determines another fact. For example, a course code may determine a course title and credit value.
If a table stores the course title repeatedly beside every student enrollment, a later title change must be made in many places. Miss one row and the database contradicts itself. Splitting the information into related tables reduces this risk.
Normalization is not a rule that every table must be split as far as possible. It is a method for making sure each fact has a clear home. In real systems, some duplicated data may be kept deliberately to speed up reading, but only after the consistency cost is understood.
Transactions matter whenever several changes must succeed together. Moving money between accounts, placing an order, or recording a library loan may update several tables. If a failure happens halfway through, a rollback should remove the incomplete work.
Concurrent users create a second challenge. Two people may try to reserve the last seat at nearly the same moment. Isolation controls reduce the chance that one transaction reads another transaction's unfinished changes or overwrites its result.
In coursework, trace a small example row by row. State the keys, expected matches, NULL cases, and final result. This habit catches more database errors than memorizing query keywords.