Revision approach
- Business rules → correct schema
- Constraints justify design
- Dependency tests
- Queries justify design
- Three official design outcomes
- Question-bank extensions
👁 Full explanation
Design a correct schema from explicit business rules, then use constraints, dependency tests and queries to justify it. This chapter covers all three official design outcomes alongside related database concepts. - Design workflow
- Requirements → ER/EER
- ER/EER → relational mapping
- Keys/FDs → normalization
- SQL → physical choices
👁 Full explanation
Work in order: requirements → ER/EER → relational mapping → keys/FDs → normalization → SQL and physical choices. - Registration/dormitory reconstruction
- Draw schema unaided
- Justify every key
- Justify every reference
👁 Full explanation
Practice drawing and constructing the registration/dormitory schema without looking; explain why every key and reference exists. - MCQ interpretation
- Literal syntax
- Required assumptions
- Overlapping choices
- Weak supplied keys
👁 Full explanation
For each MCQ, check literal syntax, assumptions and overlapping choices; justify the answer using database rules. - Recalculate rather than memorize
- Attribute closure
- Cartesian product counts
- Join preservation
- Group filters
- Concrete term contrasts
👁 Full explanation
Recalculate closure, product counts, join preservation and group filters; compare similar terms through concrete examples.
FDB01
Database approach, benefits and costs
- Database versus DBMS
- Coherent related application facts
- DBMS defines/stores/retrieves/updates
- DBMS protects/shares
- Content versus management software
- Database system applications/people/infrastructure
👁 Full explanation
A database is a coherent collection of related facts with meaning for an application. A DBMS is software that defines, stores, retrieves, updates, protects and shares that data. The database is the content; the DBMS manages it. A database system also includes applications, people and infrastructure. - File approach versus DBMS
- Application-specific formats/access procedures
- Repeated facts/update disagreement
- Data isolation
- Shared integrity difficulties
- Concurrent update difficulties
- Centralized metadata
- Multiple views/authorization/transactions
- Backup/recovery
- Controlled redundancy ≠ zero duplication
- Complexity/administration/storage/licensing costs
- Tiny fixed application tradeoffs
- Lightweight embedded DBMSs
- Useful concurrent access
- Uncontrolled concurrency → anomalies
👁 Full explanation
Traditional file applications define their own formats and access procedures. Separate files can repeat facts, disagree after updates, isolate useful data and make shared integrity or concurrent updates difficult. A DBMS centralizes metadata and supports multiple views, authorization, transactions, backup and recovery. It controls redundancy rather than guaranteeing zero duplication. Complexity, administration, storage and licensing can outweigh benefits for a tiny fixed application; lightweight embedded DBMSs also exist. Concurrent access itself is useful; uncontrolled concurrent access causes anomalies. - Database personnel
- DBA access authorization
- Backup/recovery/availability
- Monitoring/tuning/routine maintenance
- Coordinating database use
- Designers → schema
- Developers → user-facing programs
👁 Full explanation
The DBA manages access authorization, backup/recovery, availability, monitoring, tuning and routine maintenance, coordinating database use; designers define the schema and application developers build user-facing programs.
13 related practice questions
Note references: FDB-MOD
- Database versus DBMS
FDB02
Data models and product names
- Data model choices
- Structure/relationships/constraints/operations
- Conceptual ER → entities/relationships
- Relational → tables
- Hierarchical → parent–child tree
- Network → pointer-based owner/member links
- Network permits multiple parents
- Objects → identity/state/behavior
- Physical → layouts/access paths
- Representations ≠ query stages
👁 Full explanation
A data model supplies concepts for structure, relationships, constraints and operations. Conceptual ER models describe entities and relationships. Relational models use tables; hierarchical models organize records in a parent–child tree; classic network models connect records through pointer-based owner/member links, allowing multiple parents. Object models represent objects with identity, state and behavior. A physical model specifies record layouts and access paths. These are different representation choices, not stages of one query. - Products versus vendor/language
- Relational PostgreSQL/MySQL/Oracle Database
- Relational Microsoft SQL Server/IBM Db2
- MongoDB → document DBMS
- Java → programming language
- IBM → vendor
- IBM answer needs product name
👁 Full explanation
PostgreSQL, MySQL, Oracle Database, Microsoft SQL Server and IBM Db2 are relational DBMS products. MongoDB is a document DBMS. Java is a programming language. IBM alone names a vendor, not a database product. Distinguish a vendor such as IBM from a specific database product.
11 related practice questions
Note references: FDB-MOD, FDB-NOTE
- Data model choices
FDB03
Relations, schemas, instances and metadata
- Relation terminology
- Relation → set of tuples
- Tuple/row/record → attribute values
- Attribute/column/field → property
- Degree → columns
- Cardinality → rows
- Classical distinct tuples
- Atomic cells/attribute domains
- Meaningless row order
- Named attributes → meaningless column order
- SQL duplicates unless constrained
👁 Full explanation
A relation is a set of tuples, represented as a table. A tuple/row/record contains related attribute values; an attribute/column/field describes a property. Degree counts columns; cardinality counts rows. Classical relations have distinct tuples, atomic cells and domains for attributes; row order has no meaning. Column order has no logical meaning when attributes are identified by name. SQL tables can contain duplicate rows unless constrained. - Schema versus instance
- Schema → names/structures/constraints
- Instance/state/snapshot → current data
- Student insertion → state change
- Column addition → schema change
- Metadata → data describing data
- Catalog/data dictionary
- Definitions/types/constraints
- Privileges/optimizer statistics
- Schema diagram → tables/key references
- Database diagram terminology overlap
👁 Full explanation
A schema describes names, structures and constraints; an instance, state or snapshot is the data at a particular time. Inserting a student changes the state, while adding a column changes the schema. Metadata is data describing data. The catalog or data dictionary records table definitions, types, constraints and often access privileges and optimizer statistics. A schema diagram draws tables and key references; labels such as database diagram can overlap.
12 related practice questions
Note references: FDB-MOD
- Relation terminology
FDB04
Three-schema architecture and data independence
[Exam Focus]- Three-schema abstraction
- External → user-group views
- Conceptual → whole logical database
- Entity types/relationships/types/constraints
- Internal → files/placement/access paths
- Abstraction levels ≠ deployment tiers
👁 Full explanation
The external level gives each user group a selected view. The conceptual level describes the whole logical database: entity types, relationships, data types and constraints. The internal/physical level describes files, record placement and access paths. These are abstraction levels, distinct from client/server deployment tiers. - Data independence
- Physical → internal changes
- Index addition preserves conceptual schema
- Logical → conceptual changes
- Preserved external views/application interfaces
- Table split with compatible view
- Logical independence harder
- Applications depend on logical meaning
- Abstraction hides irrelevant detail
- Program–data insulation separates definitions/code
👁 Full explanation
Physical data independence permits changing internal storage without changing the conceptual schema, for example adding an index. Logical data independence permits conceptual changes while preserving affected external views and application interfaces, for example splitting a table while maintaining a compatible view. It is harder because applications depend on logical meaning. Data abstraction hides irrelevant details; program–data insulation separates data definitions from application code.
11 related practice questions
Blueprint: F1 · Note references: FDB-MOD
- Three-schema abstraction
FDB05
Requirements through implementation
[Exam Focus]- Requirements through implementation
- Business facts/transactions/reports
- Volumes/privacy/quality targets
- Conceptual design → ER/EER
- Logical design → relations/keys/constraints
- Logical normalization check
- Physical → organization/indexes/storage
- Implementation → create/load objects
- Testing → requirements
👁 Full explanation
First collect business facts, required transactions, reports, volumes, privacy rules and quality targets. Conceptual design turns them into an ER/EER model. Logical design maps the conceptual model to relations, keys and constraints and checks normalization. Physical design chooses file organization, indexes and storage. Implementation creates and loads the actual objects; testing checks requirements. - Design methodology qualifications
- Logical design tied to model
- Potential DBMS independence
- Later vendor-specific refinement
- Universal dependence claims need methodology
- Conceptual enterprise view → downward
- Normalization dependencies → upward
- Useful approaches ≠ mandatory directions
👁 Full explanation
Logical relational design is tied to a data model but can remain independent of a particular DBMS. Vendor-specific implementation details may be introduced during later refinement. Whether logical design is DBMS-dependent depends on the stated design methodology. Conceptual design usually proceeds from a whole enterprise view downward; normalization can refine structures from dependencies upward. These are useful approaches rather than mandatory directions for every modeling activity.
8 related practice questions
Blueprint: F1, F2, F3 · Note references: FDB-MOD
- Requirements through implementation
FDB06
Entities, attributes and ER notation
[Exam Focus]- ER concepts and Chen notation
- Entity → distinguishable real/conceptual thing
- Student/course instances
- Entity type → common properties
- Entity set → current collection
- Relationship → entity association
- Rectangle → entity type
- Diamond → relationship
- Ellipse → attribute
- Double ellipse → multivalued
- Dashed ellipse → derived
- Inspect diagram legend
👁 Full explanation
An entity is a distinguishable real or conceptual thing, such as one student or one course. An entity type describes common properties; its current collection of entities is an entity set. A relationship associates entities. In Chen notation rectangles represent entity types, diamonds relationships and ellipses attributes; double ellipses denote multivalued attributes and dashed ellipses derived attributes. Always inspect the diagram legend. - Independent attribute dimensions
- Simple versus composite
- Address → City/Street
- Single-valued versus multivalued
- Student → several phone numbers
- Composite single-valued address
- Age derived from BirthDate
- Derived can also be single-valued
- Names alone cannot establish multiplicity
👁 Full explanation
Simple versus composite asks whether a value has meaningful components: Address may split into City and Street. Single-valued versus multivalued asks how many values one entity may have: a student may have several phone numbers. These dimensions differ: one address may be composite and single-valued. Age derived from birth date is derived, even if also single-valued. Attribute names alone do not establish multiplicity.
11 related practice questions
Blueprint: F1 · Note references: FDB-MOD
- ER concepts and Chen notation
FDB07
Superkeys, candidate keys and primary keys
[Exam Focus]- Key hierarchy
- Superkey → tuple uniqueness
- Candidate key → minimal superkey
- Remove component → uniqueness lost
- Minimal ≠ globally fewest columns
- Primary → selected candidate
- Alternate → remaining candidate
- Composite → multiple attributes
- Composite need not be primary
👁 Full explanation
A superkey uniquely identifies a tuple. A candidate key is a minimal superkey: removing any component loses uniqueness. Minimal means no unnecessary attribute, not necessarily the fewest columns among all keys. A primary key is one selected candidate key; remaining candidates are alternate keys. A composite key contains multiple attributes and need not be the chosen primary key. - Key assumptions and traps
- Unique ID → {ID} superkey
- {ID,City} nonminimal superkey
- Several candidates/foreign keys
- At most one primary-key constraint
- Multicolumn primary key allowed
- String keys allowed
- Prefer stable compact identifiers
- Email/SSN/EmployeeID uniqueness needs rules
- Single-column options ≠ composite example
👁 Full explanation
If ID is unique, both {ID} and {ID,City} are superkeys, but only {ID} is minimal. A table can have several candidates and foreign keys but at most one declared primary-key constraint, possibly containing several columns. String columns can be keys. Prefer stable, compact identifiers when practical, but Email, SSN or EmployeeID is a candidate only if business rules guarantee uniqueness. A composite-key example must include multiple columns; a single-column key is not composite. - Worked example Eligible versus preferred primary keys
- CustID/Email/ContactNo candidate keys
- All eligible
- CustID preference needs stability/size policy
- Uniqueness alone cannot choose
👁 Full explanation
Worked exampleGiven candidate keys CustID, Email and ContactNo, all are eligible primary keys. CustID is preferable only with an explicit stability/size policy; uniqueness alone does not choose it.
18 related practice questions
Blueprint: F1 · Note references: FDB-MOD
- Key hierarchy
FDB08
Weak entities and identifying relationships
[Exam Focus]- Weak entity identification
- No independent complete key
- Owner normally strong entity
- Owner/strong entity-set terminology overlap
- Partial key = discriminator
- Distinguishes same-owner weak entities
- Owner key + partial key
👁 Full explanation
A weak entity lacks a complete independent key. Its owner is normally a strong entity, so owner entity set and strong entity set may both describe the associated set. Its partial key, also called discriminator, distinguishes weak entities of the same owner. Identification combines the owner key and partial key. - Dependent weak-entity mapping
- Names unique within employee
- Key (EmployeeID,DependentName)
- EmployeeID foreign key
- Total identifying participation
- Chen double rectangle
- Double identifying diamond
- Partial-key dashed underline
- Foreign key alone ≠ weak entity
👁 Full explanation
For Dependent(EmployeeID,DependentName,BirthDate), suppose names are unique only within one employee's dependents. Then (EmployeeID,DependentName) is the key, and EmployeeID is a foreign key. The weak entity participates totally in its identifying relationship. Chen notation uses a double rectangle, double identifying diamond and dashed underline for a partial key. A weak entity is not merely any table with a foreign key.
4 related practice questions
Blueprint: F1 · Note references: FDB-MOD
- Weak entity identification
FDB09
Cardinality, participation and direction
[Exam Focus]- Relationship cardinality and degree
- Maximum → 1:1/1:N/M:N
- Minimum zero → optional/partial
- Minimum ≥1 → mandatory/total
- Maximum one ≠ compulsory participation
- Degree → entity roles
- Unary recursive/binary/ternary
👁 Full explanation
Cardinality specifies maximum associated occurrences: 1:1, 1:N or M:N. Minimum cardinality specifies optional/partial participation (zero permitted) or mandatory/total participation (at least one). A maximum of one does not mean participation is compulsory. Relationship degree counts participating entity roles: unary recursive, binary or ternary. - Direction and notation assumptions
- Employee→Department N:1
- Department→Employee 1:N
- Building→Location possibly N:1
- Names alone insufficient
- Advisor arrows to both entities
- Common convention → maximum-one both ends
- Resulting 1:1
- Unknown convention → unsafe arrow inference
👁 Full explanation
Read business rules in both directions. If each employee belongs to one department and each department has many employees, Employee→Department is N:1; Department→Employee is 1:N. Building→Location could also be N:1, so names alone cannot select a unique example. Under the common arrow convention, arrows from advisor to both Instructor and Student impose maximum-one at both ends, giving 1:1. Without a notation convention, arrows are unsafe evidence.
7 related practice questions
Blueprint: F1 · Note references: FDB-MOD
- Relationship cardinality and degree
FDB10
Map entities, composite and multivalued attributes
[Exam Focus]- Strong-entity mapping
- One relation per entity type
- Preserve simple attributes
- Select candidate key
- Expand composite components
- Flat atomic relational design
- Calculate derived values
- Avoid stale stored copies
👁 Full explanation
Create one relation per strong entity type, preserve its simple attributes and select a candidate key. Expand composite attributes into their useful simple components; do not copy a composite ER structure unchanged into a flat atomic design. Derived values can usually be calculated, avoiding stale stored copies. - Multivalued-attribute mapping
- Separate owner-key/value relation
- StudentPhone(StudentID,Phone)
- Primary key (StudentID,Phone)
- StudentID foreign key
- Separate phone rows
- No comma-separated lists
- No Phone1/Phone2 repetition
- Explicit omitted mapping step
- Entity-based table names valid
👁 Full explanation
Map each multivalued attribute to a separate relation containing the owner key and one value. StudentPhone(StudentID,Phone) has primary key (StudentID,Phone) and a foreign key to Student. Store separate rows for two phone numbers, not a comma-separated list or repeated Phone1/Phone2 columns. Include the relational mapping step when translating an ER/EER design into tables. Table names may follow entity names; naming does not invalidate a mapping.
1 related practice question
Blueprint: F2, F3 · Note references: FDB-MOD, FDB-ER
- Strong-entity mapping
FDB11
Map 1:1 and 1:N relationships
[Exam Focus]- 1:N relationship mapping
- One-side key → many-side FK
- Relationship attributes on many side
- Department/Employee DeptID reference
- HireDate relationship attribute
- Total participation → NOT NULL
- Partial → nullable unassigned reference
👁 Full explanation
For 1:N, place the one-side key as a foreign key in the many-side table; store relationship attributes there. Department(DeptID) and Employee(EmpID,DeptID,HireDate) implement many employees per department. If participation is total, make the reference NOT NULL. If partial, a nullable reference can represent no assignment. - 1:1 relationship mapping
- Foreign key plus UNIQUE
- Prefer totally participating side
- Reduce NULLs
- NOT NULL → mandatory reference
- Merge only compatible lifecycles/participation
- A→B many-to-one reference in A
- Total or partial participation allowed
- Total-only mapping claim overstated
👁 Full explanation
For 1:1, one side may hold the other key with a UNIQUE constraint. Prefer the totally participating side to reduce NULLs; NOT NULL enforces that side's mandatory reference. Merging entities is reasonable only when their lifecycle and participation rules permit it. For A→B many-to-one, A can absorb the relationship reference under either total or partial participation; requiring total participation as the only possible case overstates the rule.
1 related practice question
Blueprint: F2, F3 · Note references: FDB-MOD
- 1:N relationship mapping
FDB12
Map M:N, recursive and n-ary relationships
[Exam Focus]- M:N association table
- Enrollment(StudentID,CourseID,Grade)
- References Student and Course
- One enrollment/pair → composite key
- Repeated attempts → Term/AttemptNo key
- Ordinarily three tables
👁 Full explanation
A binary M:N relationship needs an association table in addition to the two entity tables. Enrollment(StudentID,CourseID,Grade) references Student and Course; (StudentID,CourseID) is a key if one enrollment per pair is allowed. Repeated attempts require Term or AttemptNo in the key. This ordinarily produces three tables. - Recursive and ternary mapping
- UserLink(UserID,OtherUserID)
- Two User foreign keys
- Directed versus undirected links
- Self-link policy
- Employee nullable SupervisorID
- Recursive 1:N supervision
- Supply(SupplierID,PartID,ProjectID,Quantity)
- Preserve participating references/triples
- Binary replacements can lose triples
- Association key from actual cardinalities
- All references not automatically necessary
👁 Full explanation
A recursive M:N friendship uses UserLink(UserID,OtherUserID), two foreign keys to User; decide whether links are directed and whether self-links are allowed. A recursive 1:N supervision uses a nullable SupervisorID reference in Employee. For a ternary Supply(SupplierID,PartID,ProjectID,Quantity), preserve all participating references; binary replacements can lose which triple occurred. Choose the association key from actual cardinalities rather than automatically assuming every reference is necessary.
3 related practice questions
Blueprint: F2, F3 · Note references: FDB-MOD, FDB-ER
- M:N association table
FDB13
EER inheritance and category constraints
[Exam Focus]- EER inheritance constraints
- Superclass/subclass
- Inherited attributes/relationships
- Specialization → Undergraduate/Graduate
- Generalization → common Student properties
- Disjoint → no overlap
- Overlapping → shared members
- Total → every superclass member
- Partial → membership not compulsory
👁 Full explanation
EER extends ER with superclass/subclass structure. A subclass inherits the superclass attributes and relationships. Specialization divides Student into Undergraduate and Graduate; generalization combines common properties into Student. Disjoint subclasses cannot overlap; overlapping subclasses can. Total specialization requires every superclass member in some subclass; partial specialization does not. - Inheritance and category mapping
- Person(PersonID,Name)
- Student(PersonID,Program)
- Subclass PersonID both PK/FK
- Preserved inherited identity
- Category/union → Person or Company
- Intersection → both superclasses
- Object inheritance also supported
- ER answer requires EER scope
- Structural inheritance ≠ executable methods
👁 Full explanation
Map Person(PersonID,Name) and Student(PersonID,Program) with Student.PersonID both primary key and foreign key to Person. This preserves inherited identity. A category/union subclass draws members from a union of different superclasses, such as Owner drawn from Person or Company; it differs from an intersection subclass requiring membership in both. Object models also support inheritance; distinguish basic ER notation from EER when comparing models. Structural EER inheritance alone does not define executable object methods.
1 related practice question
Blueprint: F1, F3 · Note references: FDB-MOD
- EER inheritance constraints
FDB14
Fan traps and chasm traps
- Fan trap
- Common entity fans into two 1:N
- Department→Employee and Department→Project
- Path cannot determine WorksOn
- Department join → every employee/project pair
- Record WorksOn explicitly
👁 Full explanation
A fan trap occurs when two 1:N relationships fan from a common entity and a path suggests an association it cannot determine. Department→Employee and Department→Project do not reveal which employee works on which project; joining through Department pairs every employee with every project in that department. Record WorksOn explicitly when that is the fact needed. - Chasm trap
- Optional relationship breaks path
- Unstaffed branch property disappears
- Add required direct relationship
- Or revise model
- Multiple 1:N relationships legitimate
- Eliminating all fans ≠ universal repair
- Missing path → chasm
- Fan → association ambiguity
👁 Full explanation
A chasm trap occurs when an apparently useful path fails for some occurrences because an optional relationship breaks it. For example, a branch's property may be lost from a path that requires an assigned staff member when some properties have none. Add the required direct relationship or revise the model. Multiple 1:N relationships are legitimate; eliminating all fans is not a universal repair. A missing path describes a chasm, not the defining ambiguity of a fan.
1 related practice question
Note references: FDB-MOD, FDB-FAN
- Fan trap
FDB15
Integrity and constraint taxonomy
[Exam Focus]- Integrity categories
- Domain → type/allowed values
- Key → uniqueness
- Entity → every PK component non-NULL
- Referential → non-NULL FK matches key
- Eligible unique/candidate referenced key
- Self-references allowed
- Parent need not have child
👁 Full explanation
Domain integrity restricts type and allowed values. Key integrity enforces uniqueness. Entity integrity requires every primary-key component to be non-NULL. Referential integrity requires a non-NULL foreign-key value to match an eligible referenced unique/candidate key; self-references are allowed. A parent need not have a child. - Constraint mechanisms and interpretation
- Inherent → data model
- Schema-based → DDL
- Semantic/application → triggers/application logic
- DDL expressiveness limits
- Integrity → validity
- Authorization → allowed access
- Uniqueness/domain validation both integrity
- NULL Department PK → entity violation
- Manager=107 missing employee
- Declared FK → referential violation
- One INSERT → multiple violations
👁 Full explanation
Inherent constraints come from the data model; schema-based constraints are declared through the DDL; semantic/application rules may need triggers or application logic when DDL cannot express them. Integrity improves data validity; authorization controls who may access it. Uniqueness and domain validation are both integrity mechanisms. If a Department primary key is NULL and Manager=107 lacks a referenced employee, entity integrity certainly fails and referential integrity also fails if that foreign key is declared. One INSERT can violate multiple constraints.
13 related practice questions
Blueprint: F1 · Note references: FDB-MOD
- Integrity categories
FDB16
Write and enforce SQL constraints
[Exam Focus]- SQL constraint declaration
- Enrollment StudentID/CourseID INTEGER NOT NULL
- REFERENCES Student(StudentID)/Course(CourseID)
- Grade INTEGER CHECK
- Grade BETWEEN 0 AND 100
- PRIMARY KEY (StudentID,CourseID)
- Column name then type
- Commas separate declarations
- CHECK clause ≠ standalone GRANT-like statement
- POSITIVE not general SQL keyword
- CHECK (Amount > 0)
👁 Full explanation
Example: CREATE TABLE Enrollment (StudentID INTEGER NOT NULL REFERENCES Student(StudentID), CourseID INTEGER NOT NULL REFERENCES Course(CourseID), Grade INTEGER CHECK (Grade BETWEEN 0 AND 100), PRIMARY KEY (StudentID,CourseID)); Each column declaration gives its name then type, with commas between declarations. CHECK is a constraint clause, not a standalone GRANT-like statement. POSITIVE is not a general SQL constraint keyword; CHECK (Amount > 0) expresses positivity. - Constraint NULL and reference behavior
- PRIMARY KEY → unique/non-NULL
- UNIQUE need not imply NOT NULL
- UNIQUE NULL treatment DBMS-dependent
- CHECK unknown can pass
- NOT NULL forbids missing values
- NO ACTION/RESTRICT
- CASCADE
- SET NULL
- Referenced keys can change
- Children/action determine legality
👁 Full explanation
PRIMARY KEY implies uniqueness and non-NULL. UNIQUE does not generally imply NOT NULL; NULL treatment differs by DBMS. A CHECK can pass when its result is unknown, so add NOT NULL if missing values must be forbidden. Parent updates/deletes may use NO ACTION/RESTRICT, CASCADE or SET NULL. A referenced key is not inherently unchangeable: legality depends on matching children and the declared action.
4 related practice questions
Blueprint: F2, F3 · Note references: FDB-MOD, FDB-CONSTRAINT
- SQL constraint declaration
FDB17
Functional dependencies and attribute closure
[Exam Focus]- Functional dependencies
- X→Y: agreeing X implies agreeing Y
- Every legal tuple pair
- Business constraint ≠ sample correlation
- Full → all determinant attributes
- Partial → proper determinant subset
- Prime → some candidate key
- Nonprime → no candidate key
👁 Full explanation
X→Y means every legal pair of tuples agreeing on X also agrees on Y. It is a business constraint, not a correlation observed in one sample. Full dependency requires all determinant attributes; partial dependency holds through a proper subset. A prime attribute belongs to some candidate key; a nonprime attribute belongs to none. - Closure and Armstrong axioms
- Start X+ with X
- Applicable FD → add RHS
- Repeat until fixed point
- X+ covers relation ↔ superkey
- Remove/recompute → minimality
- Reflexivity: Y⊆X ⇒ X→Y
- Augmentation: X→Y ⇒ XZ→YZ
- Transitivity: X→Y,Y→Z ⇒ X→Z
👁 Full explanation
To compute X+, start with X and repeatedly add the right-hand side of any dependency whose left side is already present. Stop when nothing changes. X is a superkey exactly when X+ contains the whole relation. Remove attributes and recompute to test minimality. Reflexivity gives X→Y when Y⊆X; augmentation permits XZ→YZ from X→Y; transitivity derives X→Z from X→Y and Y→Z.
1 related practice question
Blueprint: F1, F2 · Note references: FDB-MOD
- Functional dependencies
FDB18
Find all keys before deciding normal form
[Exam Focus]- Find every candidate key
- R(A,B,C,D)
- C→D,C→B,B→C
- A absent from RHS
- Every key needs A
- (AB)+=ABCD via B→C→D
- (AC)+=ABCD
- AB/AC minimal
- BC cannot derive A
- Prime A/B/C
- Nonprime D
👁 Full explanation
For R(A,B,C,D), let C→D, C→B and B→C. A never appears on a right-hand side, so every key needs A. (AB)+ gives ABCD through B→C→D; (AC)+ also gives ABCD. AB and AC are minimal keys. BC cannot derive A and is not a key. A, B and C are prime; D is nonprime. - Highest normal form with partial dependencies
- C→D and B→D
- Proper subsets of AB/AC
- 2NF fails
- Atomic values → highest 1NF
- Read functional dependencies accurately
- C3D,C>PB,BOC alone insufficient
- Request actual FD set
- Never calculate guessed symbols
👁 Full explanation
D depends on C alone and also on B through transitivity, proper subsets of those keys. Thus 2NF fails; assuming atomic values, the highest form is 1NF. Use the stated functional dependencies to justify each key and normal-form conclusion. Confirm the actual dependency set before calculating closures; do not infer dependencies from unclear symbols. - Worked example Transitive dependency key test
- R(A,B,C), F={A→B,B→C}
- A+=ABC → key A
- Nonkey B → 3NF violation
- Candidate-key B changes conclusion
- Chain alone insufficient
👁 Full explanation
Worked exampleFor F={A→B,B→C} on R(A,B,C), A+=ABC makes A the key. The chain through nonkey B causes a 3NF violation. If instead B were a candidate key, a dependency chain would not by itself prove such a violation.
1 related practice question
Blueprint: F2 · Note references: FDB-MOD
- Find every candidate key
FDB19
Insertion, deletion and update anomalies
- Normalization anomalies
- Repeated Enrollment StudentName
- Update → every copy must change
- Missed copies → disagreement
- Insertion → student needs enrollment
- Deletion → last enrollment loses student
- Revoke ≠ normalization anomaly
👁 Full explanation
Suppose Enrollment repeats StudentName for every course. An update anomaly requires changing every copy of the student's name; missed copies disagree. An insertion anomaly prevents recording a new student until some enrollment exists. A deletion anomaly loses the only record of a student's details when their last enrollment is deleted. Revoke anomaly is not a normalization anomaly. - Normalization benefits and limits
- Separate facts by determinants
- Reduce repetition/anomalies
- Declared FDs suffice
- Bad current data unnecessary
- Generated row ID ≠ dependency repair
- Repeated facts persist
- Potential integrity/storage improvement
- Extra keys/joins
- Storage savings not guaranteed
- Faster retrieval not guaranteed
👁 Full explanation
Normalization separates facts according to their determinants, reducing avoidable repetition and associated anomalies. It does not require seeing bad current data: declared dependencies suffice. Adding a generated row ID may make insertion technically possible, but does not remove repeated facts or fix the dependency design. Reduced redundancy can improve integrity and storage use, but extra keys and joins mean neither storage savings nor faster retrieval is guaranteed.
10 related practice questions
Note references: FDB-MOD
- Normalization anomalies
FDB20
First and second normal forms
[Exam Focus]- First normal form
- Atomic chosen-domain values
- No repeating cell groups
- Primary-key declaration insufficient
- Correct composite/multivalued mapping → 1NF
- ER drawing ≠ higher-form guarantee
👁 Full explanation
1NF requires atomic values in the chosen relational domains and no repeating groups in cells. It does not mean merely declaring a primary key. A mapped ER design that correctly splits composite and multivalued structures gives ordinary 1NF tables; ER drawing alone guarantees no higher form. - Second normal form
- 1NF prerequisite
- No nonprime partial candidate-key dependency
- Enrollment key (SID,CID)
- SID→SName
- CID→CTitle
- Student(SID,SName)
- Course(CID,CTitle)
- Enrollment(SID,CID,Grade)
- Grade → whole pair
- Every candidate single-attribute → no partials
- No partials alone needs 1NF assumption
- Higher forms inherit 2NF
👁 Full explanation
2NF requires 1NF and no nonprime attribute partially dependent on any candidate key. For Enrollment(SID,CID,SName,CTitle,Grade), assume key (SID,CID), SID→SName and CID→CTitle. Split into Student(SID,SName), Course(CID,CTitle) and Enrollment(SID,CID,Grade). Grade still depends on the whole pair. A relation whose every candidate key is single-attribute has no nonprime partial-key dependency. Higher forms inherit 2NF, so no partial dependencies establishes the minimum guaranteed stage only when 1NF is also assumed.
7 related practice questions
Blueprint: F1, F2 · Note references: FDB-MOD
- First normal form
FDB21
Third normal form, BCNF and higher forms
[Exam Focus]- 3NF versus BCNF
- Every nontrivial X→A
- 3NF: X superkey or A prime
- BCNF: X superkey
- Whole-key mnemonic limited
- Overlapping keys need full tests
- Remove nonprime/nonkey-determinant dependencies
👁 Full explanation
For every nontrivial FD X→A, 3NF permits it when X is a superkey or A is prime. BCNF requires X to be a superkey. The mnemonic whole key and nothing but the key describes the common single-key case; it cannot replace the full tests when keys overlap. Remove dependencies of nonprime facts on nonkey determinants. - Sale normalization diagnosis
- Key (Date,Customer,Product)
- Product→Vendor
- Vendor→VendorCity
- Product proper key subset
- Vendor/VendorCity violate 2NF
- Atomic → highest 1NF
- Transitivity does not imply highest 2NF
- 4NF → nontrivial MVDs
- Nonsuperkey MVD determinants
- 5NF → join dependencies
- MVD ≠ list inside cell
👁 Full explanation
In Sale(Date,Customer,Product,Vendor,VendorCity,SalesRep), assume key (Date,Customer,Product), Product→Vendor and Vendor→VendorCity. Product is a proper subset of the key, so Vendor and VendorCity violate 2NF. With atomic values the highest form is 1NF, even though a transitive dependency also exists. 4NF addresses nontrivial multivalued dependencies with nonsuperkey determinants; 5NF addresses join dependencies. Do not confuse a multivalued dependency with a list stored inside a cell. - Worked example Transitive Employee decomposition
- EID→DeptID
- DeptID→DeptName
- Employee(EID,DeptID)
- Department(DeptID,DeptName)
- Department name stored once
👁 Full explanation
Worked exampleEmployee(EID,DeptID,DeptName), EID→DeptID and DeptID→DeptName, splits into Employee(EID,DeptID) and Department(DeptID,DeptName). Each department name is stored once.
8 related practice questions
Blueprint: F1, F2 · Note references: FDB-MOD
- 3NF versus BCNF
FDB22
Decomposition: lossless and dependency preserving
[Exam Focus]- Lossless decomposition
- Join reconstructs exact legal tuples
- Lossy → spurious combinations
- Binary FD losslessness test
- Common attributes determine R1 or R2
- Shared column alone insufficient
👁 Full explanation
A lossless decomposition reconstructs exactly the original legal tuples by joining its parts; a lossy one creates spurious combinations. For a binary decomposition into R1 and R2 under FDs, it is lossless when their common attributes determine all of R1 or all of R2. Sharing a column without this condition is insufficient. - Preservation and decomposition algorithms
- Dependency preservation → local constraint enforcement
- No join required
- Separate from losslessness
- 3NF synthesis → minimal FD cover
- Relation per determinant/dependents
- Remove contained relations
- Add candidate-key relation if absent
- BCNF → offending X→Y
- Separate XY and remainder with X
- BCNF may sacrifice dependency preservation
👁 Full explanation
Dependency preservation means the original constraints can be enforced on the individual relations without joining them. Losslessness and dependency preservation are separate properties. A practical 3NF synthesis uses a minimal FD cover, creates a relation for each determinant and its dependent attributes, removes contained relations, and adds a relation containing a candidate key if none already does. BCNF decomposition removes an offending X→Y by separating XY and the remaining attributes with X; it can sacrifice dependency preservation.
Blueprint: F1, F2, F3 · Note references: FDB-MOD, FDB-NORMAL
- Lossless decomposition
FDB23
Choose data types and storage levels
[Exam Focus]- Data types by meaning
- CHAR(n) fixed length
- VARCHAR(n) variable bounded length
- DATE → date
- BLOB → binary/images
- INTEGER → whole numbers
- DECIMAL(p,s) → exact decimal
- Money needs specified scale
- FLOAT/REAL/DOUBLE approximate
- Names/widths/precision DBMS-dependent
- Reject universal numeric-width claims
👁 Full explanation
CHAR(n) is fixed length; VARCHAR(n) has variable length up to a limit. DATE represents a date and BLOB binary content such as images. INTEGER represents whole numbers; DECIMAL(p,s) represents exact decimal values, making it suitable for money when scale is specified. FLOAT/REAL/DOUBLE are approximate; names, widths and precision depend on the DBMS. Numeric type widths depend on the DBMS and type; do not assume one universal width. - Storage hierarchy and type selection
- Primary → RAM/processor cache
- Usually volatile
- Secondary → persistent online SSD/HDD
- Pages copied to CPU buffers
- Tertiary → tape/removable archives
- Types from meaning/range/accuracy
- Integers not universally faster than strings
👁 Full explanation
Primary storage includes RAM and processor cache and is usually volatile. Secondary storage includes persistent SSDs/HDDs used online; database pages are copied into buffers before CPU processing. Tertiary storage includes tape or removable archive media. Choose types from meaning, range and accuracy requirements, not a blanket rule that integers are always faster than strings.
1 related practice question
Blueprint: F2 · Note references: FDB-MOD
- Data types by meaning
FDB24
Records, pages and primary file organization
- Pages and blocking calculations
- Page/block → I/O unit
- Several records/page
- Fixed unspanned record size r
- Usable page space B
- Blocking factor = floor(B/r)
- Pages = ceil(n/blocking_factor)
- Headers/slots reduce usable space
👁 Full explanation
A page/block is a unit of database I/O and holds several records. For fixed unspanned records of size r in usable page space B, the blocking factor is floor(B/r), and n records occupy ceil(n/blocking factor) pages. Page headers and slots reduce usable space. - File organization tradeoffs
- Heap → unordered/simple inserts
- Heap search → possible full scan
- Sorted → range scans/binary search
- Ordering-field search
- Costly inserts/overflow handling
- Hashed → hash(key) bucket
- Equality lookup/collisions/overflow
- Hash order unsuitable for ordinary ranges
- Choose from reads/writes/predicates
- Partitioning → split
- Replication → copy
- Migration → move
- Archiving → older data leaves active service
👁 Full explanation
A heap has no search-key order and supports simple inserts, but searches may scan all pages. A sorted file supports efficient range scans and binary search on the ordering field, with more costly insertions or overflow handling. A hashed file chooses a bucket by hash(key), supports equality lookup well and requires collision/overflow handling; hash order does not support ordinary ranges. Choose organization from actual reads, writes and predicates. Partitioning splits a large dataset; replication copies it; migration moves it; archiving removes older data from active service.
1 related practice question
Note references: FDB-MOD
- Pages and blocking calculations
FDB25
Ordered, dense, sparse, and multilevel indexes
- Index purpose and costs
- Search keys plus pointers/row identifiers
- Selective retrieval benefit
- Space overhead
- Write maintenance
- Tiny table → scan can win
- Most rows → scan can win
- Index ≠ referential-integrity enforcement
👁 Full explanation
An index stores search keys with pointers or row identifiers. It often speeds selective retrieval but consumes space and must be maintained on writes. A full scan can win for a tiny table or a query returning most rows. Indexes do not themselves establish referential integrity. - Index varieties and assumptions
- Primary ordered → file ordering key
- One entry/page → sparse
- Clustering → nonunique ordering field
- Often one entry/distinct value
- Secondary → alternative access paths
- Every searchable value represented
- Record pointers/pointer lists
- Dense → each search value
- Sparse → ordered intervening data
- Multilevel → indexes on indexes
- B/B+ trees → ordered/range search
- Hashing → equality
- Bitmap → some analytic workloads
- No universally best index
👁 Full explanation
In ordered-file indexing terminology, a primary ordered index is on the file’s ordering key and can have one entry per page, hence sparse. A clustering index is on a nonunique ordering field, often with an entry per distinct value. Secondary indexes provide other access paths and must represent all searchable values with record pointers or pointer lists. A dense index represents each search value; sparse indexes rely on ordered data between entries. Multilevel indexes index lower index levels. B/B+ trees support ordered and range search; hashing targets equality; bitmap indexes suit some analytic workloads. No one type is universally best.
5 related practice questions
Note references: FDB-MOD
- Index purpose and costs
FDB26
SQL categories and exact scope
- SQL command categories
- DDL → structure
- CREATE/ALTER/DROP
- Textbook RENAME/TRUNCATE often DDL
- DML → retrieve/change data
- SELECT/INSERT/UPDATE/DELETE
- SELECT sometimes DQL
- DCL → GRANT/REVOKE privileges
- TCL → COMMIT/ROLLBACK/SAVEPOINT
- Classify by effect, not length
👁 Full explanation
DDL changes structure: CREATE, ALTER, DROP, commonly RENAME and TRUNCATE in textbook classifications. DML retrieves or changes data: SELECT, INSERT, UPDATE, DELETE. Some texts separate SELECT as DQL. DCL manages privileges through GRANT and REVOKE. TCL includes COMMIT, ROLLBACK and SAVEPOINT. Classify a statement by its effect, not its length. - Deletion and dialect scope
- DELETE → rows removed/table retained
- DROP TABLE → object removed
- ALTER → definition changed
- TRUNCATE → table emptied
- Implementation-specific mechanisms
- PostgreSQL TRUNCATE rollback allowed
- No-rollback claim not universal
- SQL Server DROP DATABASE Sales, suppliers
- Comma list vendor-specific
- SQLite shell .databases → names/files
- .database undocumented command
👁 Full explanation
DELETE removes rows and keeps the table; DROP TABLE removes the table object; ALTER changes its definition. TRUNCATE empties a table using implementation-specific mechanisms. PostgreSQL allows transactional rollback of TRUNCATE, so no rollback information is retained is not a universal SQL property. SQL Server permits DROP DATABASE Sales, suppliers; that comma-list syntax is vendor-specific. SQLite's .databases lists attached names/files in its command-line shell; .database is not the documented command.
26 related practice questions
Note references: FDB-MOD, FDB-TRUNCATE, FDB-MSSQL, FDB-SQLITE
- SQL command categories
FDB27
SELECT and its result columns
- SELECT output semantics
- SELECT Name,Address FROM Customer
- Mailing-label fields only
- SELECT * → all columns
- DISTINCT → duplicate result removal
- AS → alias
- ALL → duplicate permission
- ALL ≠ all columns
- Default SQL preserves duplicates
- Column reads require appropriate FROM
- Literal SELECT 1 can omit FROM
👁 Full explanation
SELECT Name,Address FROM Customer; produces only those mailing-label fields. SELECT * selects all columns; DISTINCT removes duplicate result rows; AS supplies an alias; ALL permits duplicates and does not mean all columns. SQL normally preserves duplicates without DISTINCT. A query reading table columns needs an appropriate FROM source. A literal-only query such as SELECT 1 may legally omit FROM. - Written versus conceptual query order
- SELECT/FROM/WHERE/GROUP BY/HAVING/ORDER BY
- FROM/JOIN → WHERE
- GROUP BY → HAVING
- SELECT expressions → ORDER BY
- SELECT normally preserves state
- WHERE alone ≠ executable query
- Clause/statement distinction
👁 Full explanation
Typical written clause order is SELECT … FROM … WHERE … GROUP BY … HAVING … ORDER BY …. Conceptually form FROM/JOIN rows, filter WHERE, group, filter HAVING, select expressions and then order the result. SELECT alone normally reads without changing the database state. WHERE alone is a clause rather than a complete read-only statement, so it cannot be compared as an equivalent executable command.
4 related practice questions
Note references: FDB-MOD
- SELECT output semantics
FDB28
Predicates, strings and NULL
- SQL predicate syntax
- Strings → single quotes
- WHERE ID = 'B001'
- Standard double quotes → identifiers
- Vendor string modes differ
- E.Department resolves ambiguity
- Comparisons =/<>/</>
- AND/OR/NOT
- Parentheses clarify precedence
- LIKE 'Ab%' → prefix
- BETWEEN includes endpoints
👁 Full explanation
Use single quotes for SQL string literals: WHERE ID = 'B001'. Standard double quotes delimit identifiers, although some vendor modes accept them for strings. Qualify ambiguous columns with aliases such as E.Department. Conditions use =, <>, <, >, AND, OR and NOT; use parentheses to make precedence clear. LIKE 'Ab%' matches a prefix, and BETWEEN includes its endpoints. - NULL and timestamp boundaries
- NULL → missing/unknown
- Salary = NULL → unknown
- IS NULL/IS NOT NULL
- WHERE retains true only
- BirthDate >= DATE '2000-01-01'
- BirthDate < DATE '2001-01-01'
- Half-open timestamp year interval
- No December 31 time omissions
- Expression indexes support functions
- Functions not universally unindexable
👁 Full explanation
NULL represents a missing/unknown value. Comparisons such as Salary = NULL are unknown, not true; use IS NULL or IS NOT NULL. WHERE retains only true conditions. An index-friendly year predicate for timestamps is BirthDate >= DATE '2000-01-01' AND BirthDate < DATE '2001-01-01', avoiding omitted times on December 31. Expression indexes can support functions, so functions do not universally prevent indexing.
Note references: FDB-MOD
- SQL predicate syntax
FDB29
INSERT, UPDATE and DELETE
- INSERT column/value correspondence
- INSERT INTO Hotel(HotelID,HotelName,SubCity)
- VALUES (1,'Hilton','Yeka')
- Counts/types must match
- Keys/constraints still apply
- No column list → declared order
👁 Full explanation
INSERT INTO Hotel(HotelID,HotelName,SubCity) VALUES (1,'Hilton','Yeka'); pairs each named column with a compatible value. Counts and types must match; keys and other constraints still apply. Without a column list, values must follow the table's declared column order. - UPDATE and DELETE scope
- UPDATE Book SET Price = 600
- WHERE ID = 'B001'
- Matching rows only
- No UPDATE WHERE → every row
- DELETE FROM Publisher WHERE Name='XWZ'
- Reference policies still apply
- DELETE FROM r → all rows
- Table definition remains
- Clear entries/delete rows synonymous
- Several UPDATEs can be valid
- Appropriateness needs requested change/dialect
👁 Full explanation
UPDATE Book SET Price = 600 WHERE ID = 'B001'; changes matching rows. UPDATE without WHERE targets every row. DELETE FROM Publisher WHERE Name = 'XWZ'; deletes matching publisher rows, subject to reference policies. DELETE FROM r; removes every row but leaves r defined. Both clear relation entries and delete rows describe that effect. Several UPDATE statements can all be valid while performing different tasks; most appropriate requires a requested change and a specified quoting dialect.
9 related practice questions
Note references: FDB-MOD
- INSERT column/value correspondence
FDB30
CREATE and ALTER tables
- CREATE TABLE and primary keys
- Employee EmpID INTEGER PRIMARY KEY
- Name VARCHAR(80)
- Name/type stay together
- Commas between column declarations
- ALTER TABLE Employee ADD PRIMARY KEY (EmpID)
- Existing values unique/non-NULL
- Second PK requires replacing existing constraint
👁 Full explanation
CREATE TABLE Employee (EmpID INTEGER PRIMARY KEY, Name VARCHAR(80)); declares two columns. The name and its type stay together; the comma separates whole column declarations. To add a primary key later: ALTER TABLE Employee ADD PRIMARY KEY (EmpID); existing data must be unique and non-NULL. A table cannot gain a second primary key without replacing the existing constraint. - ALTER and malformed syntax
- ADD COLUMN Email VARCHAR(120)
- PostgreSQL-style syntax
- DROP COLUMN Email
- ADD CONSTRAINT uq_email UNIQUE (Email)
- Named constraint
- DALTER malformed token
- Column-name/type comma malformed
- Repair ≠ printed statement executes
👁 Full explanation
ALTER TABLE Employee ADD COLUMN Email VARCHAR(120); adds a column in PostgreSQL-style syntax. ALTER TABLE Employee DROP COLUMN Email; removes it. ALTER TABLE Employee ADD CONSTRAINT uq_email UNIQUE (Email); adds a named constraint. Use ALTER rather than DALTER, and do not separate column_name and column_type with a comma; validate the resulting SQL before execution.
7 related practice questions
Note references: FDB-MOD
- CREATE TABLE and primary keys
FDB31
Join behavior and preservation
- Join matching semantics
- Inner → matching ON pairs
- Equijoin → equality
- Theta join → other comparison
- Natural → every same-named column
- Ordinarily inner equality join
- Natural merges duplicate join columns
- USING (DeptID) explicit shared columns
- CROSS JOIN → every pair
- No matching condition
👁 Full explanation
An inner join returns matching pairs satisfying ON. An equijoin uses equality; a theta join permits another comparison. A natural join is ordinarily an inner equality join on every same-named column and merges those duplicate columns. JOIN … USING (DeptID) specifies shared join columns explicitly. CROSS JOIN forms every pair without a matching condition. - Outer joins and join arity
- LEFT preserves unmatched left
- RIGHT preserves unmatched right
- FULL preserves both
- Missing-side columns NULL-padded
- Inner/natural-inner discard unmatched rows
- Self-join → aliases/base-table occurrences
- Operator combines two inputs
- Inputs may be earlier joins
- Query may join many tables
- Base-table count versus occurrence count
- Binary arity distinction
👁 Full explanation
LEFT OUTER JOIN also preserves unmatched left rows, padding right columns with NULL; RIGHT preserves right rows and FULL preserves both. Therefore inner and natural inner joins both discard unmatched rows. A self-join uses aliases for two occurrences of one base table. Each ordinary join operator combines two inputs, but its inputs can be earlier joins, so one query can join many tables. Distinguish base-table count, table occurrences and binary operator arity.
5 related practice questions
Note references: FDB-MOD, FDB-JOIN
- Join matching semantics
FDB32
Aggregation, GROUP BY and HAVING
- Aggregation and group filtering
- COUNT(*) → rows
- COUNT(x) → non-NULL x
- SUM/AVG → non-NULL numeric values
- AVG, not AVERAGE
- GROUP BY → distinct-value groups
- WHERE filters individual rows
- HAVING filters aggregate groups
👁 Full explanation
COUNT(*) counts rows, COUNT(x) counts non-NULL x values; SUM and AVG summarize non-NULL numeric values. SQL's usual average function is AVG, not AVERAGE. GROUP BY creates one group per distinct grouping value; HAVING tests aggregate groups after WHERE has filtered individual rows. - Grouped query validity
- Department/AVG(Salary) output
- WHERE Department <> 'Sales'
- GROUP BY Department
- HAVING AVG(Salary) > 35000
- Ordinary aggregate predicate invalid in WHERE
- EmpID/FirstName/LastName not grouped
- Unless DBMS establishes functional dependence
- Grouping column need not be selected
- Damaged query defect ≠ omitted grouping output
👁 Full explanation
SELECT Department, AVG(Salary) FROM Employee WHERE Department <> 'Sales' GROUP BY Department HAVING AVG(Salary) > 35000; lists qualifying department averages. An ordinary AVG(Salary) predicate cannot be placed directly in WHERE. Selecting EmpID, FirstName and LastName while grouping only by Department is also invalid unless the DBMS can establish the required functional dependence. The grouping column need not itself appear in SELECT; check the functional dependence of other selected columns.
2 related practice questions
Note references: FDB-MOD, FDB-GROUP
- Aggregation and group filtering
FDB33
Subqueries and views
- Subquery forms and NULL traps
- Nested SELECT
- WHERE/HAVING/FROM locations
- IN → membership
- EXISTS → qualifying row existence
- Correlated → outer-row reference
- Scalar → appropriate single value
- NOT IN with possible NULL
- NOT EXISTS often safer absence test
👁 Full explanation
A subquery is a SELECT nested inside another statement. It may appear in WHERE, HAVING or FROM, not only the first two. IN tests membership; EXISTS tests whether at least one qualifying row exists. Correlated subqueries refer to the outer row. A scalar subquery must produce an appropriate single value. Beware NOT IN when its subquery can return NULL; NOT EXISTS often expresses the intended absence more safely. - Views and rewrite tradeoffs
- CREATE VIEW CSStudent AS SELECT
- StudentID/Name WHERE Major='CS'
- Stored query definition/virtual table
- Repeated-query simplification
- Derived values
- Configured access restriction
- Ordinary view need not store result
- Materialized view stores result
- Subquery→join can change duplicates
- Rewrite not guaranteed faster
👁 Full explanation
CREATE VIEW CSStudent AS SELECT StudentID,Name FROM Student WHERE Major = 'CS'; stores a query definition and presents a virtual table. Views can simplify repeated queries, expose derived values and restrict access when configured correctly. An ordinary view does not necessarily store its result; a materialized view does. Rewriting a subquery as a join can change duplicates, and does not guarantee faster execution.
2 related practice questions
Note references: FDB-MOD
- Subquery forms and NULL traps
FDB34
Set operations and SQL conversion
- SQL set operations
- UNION → compatible results/deduplication
- UNION ALL → duplicates retained
- UNION DISTINCT dialect-dependent
- PostgreSQL accepts explicit DISTINCT
- Not separate algebra rule
- INTERSECT → common rows
- EXCEPT → first-only rows
- Same column count
- Compatible corresponding types
👁 Full explanation
SELECT ID FROM A UNION SELECT ID FROM B; combines compatible result columns and removes duplicate rows by default. UNION ALL retains duplicates. Explicit UNION DISTINCT is accepted in some dialects, including PostgreSQL; it is not a separate algebra rule. INTERSECT returns common rows; EXCEPT returns first-result rows absent from the second. Inputs need the same column count and compatible corresponding types. - Conversion and UNION repair
- CAST('42' AS INTEGER)
- Value must permit conversion
- CONVERT vendor-specific conversion
- Both options need dialect
- UNION xyz FROM table2 malformed
- Missing second SELECT
- SELECT expressions versus table names
- xyz ≠ table name
👁 Full explanation
CAST('42' AS INTEGER) converts an expression to a requested type if the value permits it. CONVERT is vendor-specific and can also perform type conversion; specify the SQL dialect when comparing it with CAST. In SELECT xyz FROM table1 UNION xyz FROM table2, the second SELECT is missing. In the corrected query, xyz is a selected column/expression, not a table name.
4 related practice questions
Note references: FDB-MOD, FDB-UNION, FDB-CAST
- SQL set operations
FDB35
Relational algebra: calculate results
- Relational algebra operators
- Selection σ → tuple filtering
- Projection π → attributes
- Set projection removes duplicates
- Rename ρ → relation/attribute names
- Union/difference/intersection → compatible schemas
- R−S not commutative
- Union/intersection commutative
- Inner joins commutative with adjusted predicates
- Up to attribute order
- LEFT OUTER not generally commutative
- Vague join needs variant
👁 Full explanation
Selection σ filters tuples; projection π retains attributes and removes duplicate tuples under set semantics. Rename ρ changes relation or attribute names. Union, difference and intersection require union-compatible schemas. Difference R−S is not commutative; union and intersection are. Inner joins are commutative up to attribute order when predicates are adjusted; left outer joins are not generally commutative. Identify the join variant before predicting its behavior. - Product counts and algebra basis
- |R×S| = |R|×|S|
- Product degree = degree(R)+degree(S)
- Rename conflicting attributes
- 200×100=20,000
- 5×7=35
- Join = selection over product
- Intersection derivable from difference
- Basis σ/π/union/difference/product/rename
- Assignment useful, not indispensable
- Textbook primitive lists vary
👁 Full explanation
A Cartesian product has |R|×|S| tuples and degree degree(R)+degree(S), renaming conflicting attributes if necessary. Thus 200×100=20,000 and 5×7=35. Join equals selection over a product; intersection can be derived from difference. A common minimal basis contains selection, projection, union, difference, product and rename. Assignment is a useful notation, not an indispensable primitive; lists of basic operations vary across textbooks.
8 related practice questions
Note references: FDB-MOD
- Relational algebra operators
FDB36
Relational calculus: state the desired facts
- Algebra versus calculus
- Algebra → operation sequence
- Procedural at logical level
- Calculus → desired-result predicate
- Declarative/nonprocedural
- Tuple variables → tuples
- Domain variables → attribute values
- Safe/range-restricted expressions
- Results bounded by active domain
👁 Full explanation
Relational algebra specifies a sequence of operations and is procedural at the logical level. Relational calculus specifies a predicate describing desired results and is declarative/nonprocedural. A tuple variable ranges over tuples; a domain variable ranges over individual attribute values. Safe/range-restricted expressions produce results bounded by the database's active domain. - Calculus forms and quantifiers
- {s | Student(s) AND s.Major='CS'}
- Tuple result → CS students
- Domain result → names n
- ∃i,m (Student(i,n,m) AND m='CS')
- Existential → some matching tuple
- Universal → every required case
- SQL declarative
- Optimizer chooses procedural execution plan
👁 Full explanation
Tuple form {s | Student(s) AND s.Major='CS'} selects student tuples with that major. A domain form can request names n satisfying ∃i,m (Student(i,n,m) AND m='CS'). Existential quantification means some matching tuple exists; universal quantification means every required case meets a condition. SQL is declarative even though an optimizer later chooses a procedural execution plan.
1 related practice question
Note references: FDB-MOD
- Algebra versus calculus
FDB37
Transactions and ACID
- Transactions and ACID
- Transaction → logical operation unit
- Atomicity → all effects or none
- Consistency → correct invariant preservation
- Isolation → concurrent interference control
- Serializable → equivalent serial execution
- Durability → committed crash survival
- Durable logging typical
- Immediate page forcing unnecessary
👁 Full explanation
A transaction groups operations into one logical unit. Atomicity applies all its effects or none. Consistency means a correct transaction preserves declared invariants. Isolation controls interference between concurrent transactions; serializable isolation provides behavior equivalent to some serial execution. Durability preserves committed effects after a crash, typically through durable logging rather than forcing every data page immediately. - Transfer and transaction control
- Subtract/add 100 commit together
- Lost half → atomicity violation
- Possible total-balance invariant violation
- BEGIN → start
- COMMIT → complete
- ROLLBACK → undo uncommitted work
- SAVEPOINT → intermediate rollback point
- Other committed transactions remain
- Syntax/error-handling DBMS-dependent
- @@ERROR/IF SQL Server-specific
👁 Full explanation
For a transfer, subtracting 100 from one balance and adding 100 to another must commit together; losing either half violates atomicity and may violate the total-balance invariant. BEGIN starts work, COMMIT completes it, ROLLBACK undoes uncommitted work and SAVEPOINT marks an intermediate rollback point. A failed transaction may roll back while other users' committed transactions remain. SQL transaction syntax and error-handling constructs differ by DBMS; @@ERROR/IF is SQL Server-specific.
1 related practice question
Note references: FDB-MOD, FDB-NOTE
- Transactions and ACID
FDB38
Choose techniques from requirements
[Exam Focus]- Match technique to requirement
- Normalization → dependency anomalies
- Indexing → search paths
- Denormalization → repeat/precompute facts
- Lower selected read costs
- Higher maintenance cost
- Partitioning → rows/columns divided
- Replication → copies
- Locality/availability benefits
- Update complexity
- Techniques can coexist
- E-commerce/gaming label insufficient
👁 Full explanation
Normalization reduces dependency-driven anomalies. Indexing supplies search paths. Denormalization deliberately repeats or precomputes facts to reduce selected read costs, increasing maintenance work. Partitioning divides rows or columns; replication creates copies to improve locality or availability while complicating updates. These techniques can coexist. An e-commerce or gaming label alone does not select one. - Security and quality requirements
- Privacy → threat model
- Authentication → identity
- Role authorization → records/actions
- Encryption → at rest/in transit
- Auditing → activity records
- Patient data → encryption/access control
- Scalability/availability/retrieval speed
- Redundancy control/security
- Functional → behavior
- Nonfunctional → quantified quality
- 99.9% uptime/response time
- Mechanisms alone cannot classify requirement
👁 Full explanation
Privacy requires a threat model. Authentication verifies identity; role-based authorization limits allowed records/actions; encryption protects data at rest or in transit; auditing records activity. Both encryption and access control can protect patient data. Scalability, availability, fast retrieval, redundancy control and security are all reasonable design considerations. Functional requirements describe behavior; nonfunctional requirements quantify qualities such as 99.9% uptime or response time. Authentication and encryption mechanisms alone do not uniquely establish that distinction. - CMS meaning versus integrity
- Tags/categories → meaning
- Foreign keys → valid references
- ContentTag(ContentID,TagID)
- Multiple tags/item
- Complementary functions
👁 Full explanation
In a CMS, tags/categories describe meaning, while foreign keys preserve valid references between content and tags. A ContentTag(ContentID,TagID) association models multiple tags per item; these functions complement each other.
9 related practice questions
Blueprint: F2, F3 · Note references: FDB-MOD
- Match technique to requirement
FDB39
Registration and dormitory design
[Exam Focus]- Registration/dormitory business rules
- Student → one department
- Student/course → many-to-many
- Student → at most one current dorm
- Dorm → at most six students
- Department/Student/Course/Dorm entities
- Enrollment relationship with Grade
👁 Full explanation
State the rules first: each student belongs to one department, may enroll in many courses, and has at most one current dorm assignment. Each course accepts many students; each dorm holds at most six students. Identify Department, Student, Course and Dorm entities; Enrollment is the M:N relationship with Grade. - Integrated schema and capacity enforcement
- Department(DeptID,Name)
- Student(SID,Name,DeptID,DormID)
- Course(CID,Title)
- Enrollment(SID,CID,Grade)
- Dorm(DormID,FloorNumber)
- Entity IDs → primary keys
- Student DeptID/DormID → parent FKs
- Enrollment PK (SID,CID)
- Enrollment → both parent references
- Nullable DormID until assignment
- StudentPhone(SID,Phone) → multiple contacts
- Department/course facts in owning tables
- FK alone cannot enforce six capacity
- Concurrency-safe trigger/allocation transaction
- Or six unique slots/dorm
👁 Full explanation
Use Department(DeptID,Name), Student(SID,Name,DeptID,DormID), Course(CID,Title), Enrollment(SID,CID,Grade) and Dorm(DormID,FloorNumber). Declare all entity IDs primary keys; Student.DeptID and Student.DormID reference their parents; Enrollment has composite key (SID,CID) and both references. Make DormID nullable until assignment. Separate StudentPhone(SID,Phone) for multiple contacts. Normalize repeated department/course facts into their owning tables. A simple foreign key does not enforce the six-student capacity; use a concurrency-safe trigger, allocation transaction or explicit slot design with six unique slots per dorm. - Worked example Dorm reference direction
- FOREIGN KEY (DormID) REFERENCES Dorm(DormID)
- STUDENT → referencing child
- DORM → referenced parent
- Consistent names
- Student.DormID required before execution
👁 Full explanation
Worked exampleComplete the reference as FOREIGN KEY (DormID) REFERENCES Dorm(DormID). STUDENT is the referencing child; DORM is referenced. Consistent names and an added Student.DormID column are required before execution.
1 related practice question
Blueprint: F2, F3 · Note references: FDB-MOD
- Registration/dormitory business rules
FDB40
Check SQL syntax and question assumptions
- Check supplied answer keys
- Owner/strong entity both possible
- Row/record synonyms
- Difference/set-difference synonyms
- Negative DROP question → multiple answers
- DELETE/UPDATE/invalid REMOVE
- Highlighting does not establish correctness
👁 Full explanation
Justify each answer using the schema, SQL rules and stated assumptions. Owner and strong entity can both describe the entity supporting a weak entity; row and record are synonyms in a relational table; difference and set-difference are the same operator. DELETE, UPDATE and the invalid command REMOVE are not DROP; a question asking which is not DROP can therefore admit several choices. Compare all choices with the relevant rule before selecting an answer. - Resolve question defects
- Damaged token → corrected form
- Printed malformed SQL remains invalid
- Unsupported cardinality → rules/direction
- Best/efficient/important → workload/goal/baseline
- Diagram conclusions need symbols/description
- Question index → exceptions/minimal clarification
- Unsupported unique answer ≠ useless teaching
👁 Full explanation
For invalid SQL, identify the syntax error and validate the corrected statement before analyzing its result. For unsupported cardinalities, request business rules and direction. For best/efficient/important questions, specify workload, quality goal or comparison baseline. A diagram-dependent conclusion needs the missing diagram or an explicit description of its relevant symbols. Identify the smallest clarification needed when a question does not support a unique answer.
61 related practice questions
Note references: FDB-MOD
- Check supplied answer keys
No matching topics. Clear the search or change the filter.
Blueprint learning outcomes
- F1 · Understand the principles of database design
FDB04, FDB05, FDB06, FDB07, FDB08, FDB09, FDB13, FDB15, FDB17, FDB20, FDB21, FDB22 - F2 · Apply the database concepts to real world database design
FDB05, FDB10, FDB11, FDB12, FDB16, FDB17, FDB18, FDB20, FDB21, FDB22, FDB23, FDB38, FDB39 - F3 · Design database systems for real world scenarios
FDB05, FDB10, FDB11, FDB12, FDB13, FDB16, FDB22, FDB38, FDB39