Exam Pattern & Cheat Sheet
CSE 303 · Database Management · Midterm · Full marks 50 · 1 hr 30 min · answer all · partial grading is ON. Four parts, worked end-to-end in this chapter.
The paper at a glance
| Part | CLO | Topic | What it asks | Solved → |
|---|---|---|---|---|
| 1 | CLO1 | DB Concepts | Define DB & DBMS · relational/NoSQL/cloud examples · map DB users to roles | Part 1 |
| 2 | CLO2 | Languages & Rel. Algebra | DDL/DCL/DML/TCL table · Cartesian product · inner & outer joins · select | Part 2 |
| 3 | CLO3 | ER Diagram | Keys · attribute types · strong/weak entity · relationships · full ER diagram | Part 3 |
| 4 | CLO6 | SQL | CREATE + INSERT · ALTER · CASE update · SELECT · GROUP BY/HAVING · EXCEPT · COUNT | Part 4 |
Parts 3 & 4 share the same university schema — learn it once, it powers both. Part 1 and the CREATE-TABLE marks are the fastest, most certain points: bank them first.
Every mnemonic on one screen
PISMDB characteristics: Persistent · Integrated · Shared · Managed.
RI·IACSFile-processing drawbacks: Redundancy/inconsistency · Isolation · Integrity · Atomicity · Concurrency · Security.
σ π ∪ − × ρ6 relational-algebra basics: Select · Project · Union · Difference · Cartesian product · Rename. Joins are built from × + σ.
PLVAbstraction levels (bottom→top): Physical/internal → Logical/conceptual → View/external.
Super ⊇ Cand ⊇ PKKeys nest: a Super key that is minimal = Candidate key; one chosen candidate = Primary key. Foreign key = value must exist as a PK elsewhere.
One-page reference
Database languages → commands
| Acronym | Name | Purpose | Commands (≥3) |
|---|---|---|---|
| DDL | Data Definition | Define / change schema | CREATE · ALTER · DROP · TRUNCATE · RENAME |
| DML | Data Manipulation | Work with rows | SELECT · INSERT · UPDATE · DELETE |
| DCL | Data Control | Access / permissions | GRANT · REVOKE |
| TCL | Transaction Control | Manage transactions | COMMIT · ROLLBACK · SAVEPOINT |
Relational-algebra operators
| Symbol | Name | Does |
|---|---|---|
| σ | Select | Keeps rows matching a condition |
| π | Project | Keeps columns (drops duplicates) |
| ∪ · − · ∩ | Set ops | Union · difference · intersection (union-compatible) |
| × | Cartesian product | Every row of r paired with every row of s |
| ⋈ | Natural join | × then match same-named attribute(s) |
| ⟕ ⟖ ⟗ | Outer joins | Left / right / full — keep unmatched rows as NULL |
| ρ | Rename | Refer to a relation / column by another name |
ER notation
| Shape | Means |
|---|---|
| Rectangle / double rectangle | Entity / weak entity |
| Diamond / double diamond | Relationship / identifying relationship |
| Oval · double oval · dashed oval | Attribute · multivalued · derived |
| Underline · dashed underline | Key · discriminator (weak-entity partial key) |
| Double line to entity | Total participation (single line = partial) |
SQL clause order
SELECT → FROM → JOIN → WHERE → GROUP BY → HAVING → ORDER BYWHERE filters rows before grouping · HAVING filters groups after aggregation.
Exam-day strategy
Partial grading is on — write something for every part. Bank Part 1 and the SQL CREATE-TABLE marks first (fast, certain), then invest time in the relational-algebra result tables and the ER diagram, where a small slip costs the most. Whenever a question says “write / draw the resultant table,” actually draw it — those are separate marks.