SQL & Databases Relational Database Fundamentals Syllabus

Relational Database Fundamentals syllabus — full chapter and topic list

Expand any chapter to see its topics and sub-topics. This is the whole examinable outline for Relational Database Fundamentals in SQL & Databases, not a summary of it.

1. 1

   Database Concepts and Architecture

   4 topics

   • What Is a Database and a DBMS
   • Relational Model Foundations
   • Three-Schema Architecture
   • RDBMS Landscape
2. 2

   Keys and Integrity Constraints

   4 topics

   • Primary and Candidate Keys
   • Foreign Keys and Referential Integrity
   • Constraint Types
   • Entity and Domain Integrity
3. 3

   Data Types and Schema Definition

   4 topics

   • Numeric, Character, and Boolean Types
   • Date, Time, and Timestamp Types
   • Specialized Types
   • NULL Semantics and Three-Valued Logic
4. 4

   SQL Sublanguages Overview

   4 topics

   • DDL: Define Schema Objects
   • DML: Manipulate Data
   • DQL: Query Data
   • DCL and TCL

Relational Database Fundamentals flashcards for SQL & Databases

23 of 51 cards from the Relational Database Fundamentals deck — real questions with worked answers.

1. What is a database?

   An organized, persistent collection of related data stored so it can be efficiently accessed, managed, and updated. It models some part of the real world (the miniworld/universe of discourse).

2. What is a DBMS (Database Management System)?

   Software that lets users define, create, query, update, and administer databases. It sits between users/applications and the stored data, providing concurrency control, security, recovery, and integrity enforcement. Examples: PostgreSQL, MySQL, Oracle.

3. Name four key services a DBMS provides beyond simple file storage.

   1) Data independence (logical/physical), 2) Concurrency control for multiple users, 3) Recovery from failures (durability), 4) Integrity and security enforcement. Also querying via a declarative language.

4. Who proposed the relational model and when?

   E. F. (Ted) Codd of IBM proposed the relational model in 1970 in the paper 'A Relational Model of Data for Large Shared Data Banks.'

5. In the relational model, what are the formal terms for table, row, and column?

   Table = relation; row = tuple; column = attribute. The number of attributes is the degree, and the number of tuples is the cardinality.

6. What is a relation formally in terms of sets?

   A relation is a subset of the Cartesian product of the domains of its attributes: $R \subseteq D_1 \times D_2 \times \cdots \times D_n$. Each tuple is an element of that product, so a relation is a set of tuples.

7. What is a domain in the relational model?

   A domain is the set of all atomic (indivisible) values that an attribute may take, together with a data type and format (e.g., the domain of ages might be integers $0 \leq \text{age} \leq 150$).

8. What does the 'first normal form' / relational requirement of atomicity mean?

   Every attribute value must be atomic (single-valued and indivisible); relations cannot contain repeating groups, nested tables, or multivalued cells in the pure relational model.

9. What is the significance of relations being sets with respect to row order and duplicates?

   Because a relation is a set of tuples: tuples have no inherent order, and no two tuples can be identical (no duplicate rows). Attribute order is also logically immaterial since attributes are named.

10. What is the ANSI/SPARC three-schema architecture?

    A three-level model for data independence: 1) External (view) level — individual user views; 2) Conceptual (logical) level — the whole community's logical schema; 3) Internal (physical) level — how data is physically stored.

11. What is the difference between logical and physical data independence?

    Logical data independence: ability to change the conceptual schema without altering external views/applications. Physical data independence: ability to change the internal (physical storage) schema without altering the conceptual schema.

12. Which mapping in the three-schema architecture supports logical data independence, and which supports physical?

    The external/conceptual mapping supports logical data independence; the conceptual/internal mapping supports physical data independence.

13. What is an RDBMS?

    A Relational Database Management System — a DBMS based on the relational model that stores data in tables and typically uses SQL as its query language, enforcing relational integrity constraints.

14. Give examples of major RDBMS products, both open-source and commercial.

    Open-source: PostgreSQL, MySQL, MariaDB, SQLite. Commercial: Oracle Database, Microsoft SQL Server, IBM Db2. SQLite is notably an embedded, serverless RDBMS.

15. What is a candidate key?

    A minimal set of attributes that uniquely identifies each tuple in a relation. 'Minimal' means no attribute can be removed without losing uniqueness. A relation may have several candidate keys.

16. What is a primary key and how does it relate to candidate keys?

    The primary key is the one candidate key chosen by the designer to be the principal unique identifier of tuples. Its values must be unique and NOT NULL (entity integrity). The other candidate keys become alternate keys.

17. What is a superkey, and how does it differ from a candidate key?

    A superkey is any set of attributes that uniquely identifies tuples (uniqueness only). A candidate key is a minimal superkey — one with no removable attribute. Every candidate key is a superkey, but not vice versa.

18. What is a composite (compound) key?

    A key consisting of two or more attributes combined to uniquely identify a tuple, used when no single attribute is unique on its own.

19. What is a foreign key?

    An attribute (or set) in one relation whose values must match a primary key (or candidate key) value in another (or the same) relation, or be NULL. It establishes and enforces a link between the two tables.

20. What is referential integrity?

    The rule that every non-NULL foreign key value must reference an existing tuple in the referenced relation. It prevents 'orphan' rows that point to nonexistent parent records.

21. What referential actions can be specified for ON DELETE / ON UPDATE of a foreign key?

    CASCADE (propagate the change), SET NULL (set FK to NULL), SET DEFAULT (set FK to its default), RESTRICT / NO ACTION (reject the operation if references exist).

22. List the main types of constraints in a relational database.

    NOT NULL, UNIQUE, PRIMARY KEY, FOREIGN KEY (referential), CHECK (domain/value condition), and DEFAULT (supplies a value when none given). PRIMARY KEY = UNIQUE + NOT NULL.

23. What is the difference between a UNIQUE constraint and a PRIMARY KEY constraint?

    Both enforce uniqueness, but a table may have many UNIQUE constraints and only one PRIMARY KEY. UNIQUE columns may allow NULLs (typically one or many depending on the DBMS), whereas a PRIMARY KEY forbids NULLs.

See more Relational Database Fundamentals flashcards →

Planning Relational Database Fundamentals for SQL & Databases

Relational Database Fundamentals (SQL & Databases) FAQ