Data Science Databases Syllabus

Databases syllabus — full chapter and topic list

Expand any chapter to see its topics and sub-topics. This is the whole examinable outline for Databases in Data Science, not a summary of it.

1. 1

   Introduction to Databases

   2 topics

   • What is a Database?
     • Definition and Purpose
     • Database vs. Spreadsheet
     • Types of Databases
   • Database Models
     • Relational Model
     • NoSQL Models
     • Object-Oriented Model
     • Hierarchical Model
     • Network Model
2. 2

   Relational Databases

   4 topics

   • Fundamentals of Relational Databases
     • Tables, Rows, and Columns
     • Primary and Foreign Keys
     • Relationships and Normalization
   • SQL (Structured Query Language)
     • Basic SQL Commands
     • Advanced SQL Queries
     • Joins and Subqueries
     • Indexes and Performance Tuning
   • Database Design
     • Entity-Relationship Diagrams
     • Normalization Techniques
     • Denormalization
   • Transaction Management
     • ACID Properties
     • Isolation Levels
     • Concurrency Control
3. 3

   NoSQL Databases

   5 topics

   • Introduction to NoSQL
     • What is NoSQL?
     • Types of NoSQL Databases
     • Use Cases for NoSQL
   • Document-Oriented Databases
     • MongoDB
     • CouchDB
   • Key-Value Stores
     • Redis
     • Riak
   • Column-Family Stores
     • Cassandra
     • HBase
   • Graph Databases
     • Neo4j
     • OrientDB
4. 4

   Big Data and Distributed Databases

   4 topics

   • Introduction to Big Data
     • Characteristics of Big Data
     • Big Data Technologies
   • Hadoop Ecosystem
     • HDFS
     • MapReduce
     • Hive
     • Pig
   • Spark
     • Introduction to Spark
     • Spark SQL
     • Spark Streaming
   • Distributed Databases
     • CAP Theorem
     • Data Partitioning and Sharding
     • Consistency Models
5. 5

   Data Warehousing

   4 topics

   • Introduction to Data Warehousing
     • Definition and Purpose
     • Data Warehouse vs. Database
   • ETL (Extract, Transform, Load)
     • ETL Process
     • ETL Tools
   • Data Warehouse Design
     • Star Schema
     • Snowflake Schema
     • Fact and Dimension Tables
   • OLAP (Online Analytical Processing)
     • OLAP Cubes
     • ROLAP vs. MOLAP
6. 6

   Database Security

   3 topics

   • Introduction to Database Security
     • Importance of Security
     • Common Threats
   • Security Mechanisms
     • Authentication and Authorization
     • Encryption
     • Auditing and Monitoring
   • Compliance and Regulations
     • GDPR
     • HIPAA
     • SOX
7. 7

   Advanced sub-topics

   3 topics

   • Data Integration
     • Data Lakes
     • Data Virtualization
   • Database Performance Tuning
     • Query Optimization
     • Indexing Strategies
     • Caching Mechanisms
   • Machine Learning with Databases
     • In-Database Machine Learning
     • Integration with ML Libraries

Databases flashcards for Data Science

25 of 50 cards from the Databases deck — real questions with worked answers.

1. What is a database?

   An organized, structured collection of data stored electronically so it can be efficiently accessed, managed, queried and updated, typically controlled by a Database Management System (DBMS).

2. What is a DBMS, and what core functions does it provide?

   A Database Management System is software that mediates between users/applications and the stored data. It provides data definition, storage, retrieval (querying), concurrency control, security/access control, backup/recovery and integrity enforcement.

3. List the main database models and one defining trait of each.

   Hierarchical (tree, parent-child), Network (graph of records with sets), Relational (tables/relations with keys), Object-oriented (objects), Document, Key-Value, Column-Family and Graph (NoSQL families).

4. In the relational model, define a relation, a tuple, and an attribute.

   A relation is a table; a tuple is a row (a single record); an attribute is a column (a named field). The number of attributes is the degree and the number of tuples is the cardinality.

5. What is a primary key, and what two constraints must it satisfy?

   A primary key is an attribute (or set of attributes) that uniquely identifies each tuple in a relation. It must be unique (no two rows share a value) and not null (entity integrity).

6. What is a foreign key and what integrity rule does it enforce?

   A foreign key is an attribute in one relation that references the primary key of another relation. It enforces referential integrity: a foreign-key value must either match an existing primary-key value or be null.

7. Distinguish a candidate key, primary key, and a superkey.

   A superkey is any set of attributes that uniquely identifies a tuple. A candidate key is a minimal superkey (no removable attribute). The primary key is the candidate key chosen as the main identifier; remaining candidate keys are alternate keys.

8. What are the four sublanguages of SQL and their purpose?

   DDL (Data Definition: CREATE, ALTER, DROP), DML (Data Manipulation: INSERT, UPDATE, DELETE), DQL (Data Query: SELECT), and DCL/TCL (Data/Transaction Control: GRANT, REVOKE, COMMIT, ROLLBACK).

9. In SQL, what is the logical evaluation order of the clauses FROM, WHERE, GROUP BY, HAVING, SELECT, ORDER BY?

   FROM (and JOINs) → WHERE → GROUP BY → HAVING → SELECT → ORDER BY. WHERE filters rows before grouping; HAVING filters groups after aggregation.

10. Name the four main SQL join types and what each returns.

    INNER JOIN returns only matching rows; LEFT (OUTER) JOIN returns all left rows plus matches; RIGHT JOIN returns all right rows plus matches; FULL OUTER JOIN returns all rows from both sides, with nulls where no match exists.

11. Differentiate WHERE from HAVING in SQL.

    WHERE filters individual rows before aggregation and cannot use aggregate functions; HAVING filters groups after GROUP BY and can use aggregates such as COUNT, SUM and AVG.

12. List the five core SQL aggregate functions.

    $\text{COUNT}$, $\text{SUM}$, $\text{AVG}$, $\text{MIN}$ and $\text{MAX}$. They compute a single summary value over a set of rows, often combined with GROUP BY.

13. What is the goal of normalization in database design?

    To organize attributes and relations to reduce data redundancy and eliminate insertion, update and deletion anomalies, by decomposing tables based on functional dependencies.

14. State the requirement of First Normal Form (1NF).

    Every attribute must hold a single atomic (indivisible) value, with no repeating groups or arrays; each row must be uniquely identifiable.

15. State the requirement of Second Normal Form (2NF).

    The relation must be in 1NF and have no partial dependency: every non-key attribute must depend on the whole composite primary key, not just part of it.

16. State the requirement of Third Normal Form (3NF).

    The relation must be in 2NF and have no transitive dependency: non-key attributes must depend only on the primary key, not on other non-key attributes.

17. What is Boyce-Codd Normal Form (BCNF)?

    A stricter form of 3NF in which, for every non-trivial functional dependency $X \to Y$, $X$ must be a superkey. It removes anomalies 3NF can still allow when there are multiple overlapping candidate keys.

18. In an ER diagram, what is the difference between an entity and an attribute?

    An entity is a real-world object or concept about which data is stored (becomes a table); an attribute is a property describing an entity (becomes a column). A relationship links entities.

19. What do the cardinality ratios 1:1, 1:N, and M:N represent in ER modeling?

    They describe how many instances of one entity relate to another: one-to-one, one-to-many, and many-to-many. An M:N relationship is implemented with a junction/bridge table holding two foreign keys.

20. What are the four ACID properties of a transaction?

    Atomicity (all-or-nothing), Consistency (valid state to valid state), Isolation (concurrent transactions don't interfere), and Durability (committed changes survive failures).

21. Define a database transaction.

    A single logical unit of work consisting of one or more operations that must execute completely or not at all, ending with either COMMIT (make permanent) or ROLLBACK (undo).

22. List the four SQL transaction isolation levels from weakest to strongest.

    Read Uncommitted, Read Committed, Repeatable Read, and Serializable. Higher levels prevent more concurrency anomalies but reduce concurrency/performance.

23. Match each concurrency anomaly to its cause: dirty read, non-repeatable read, phantom read.

    Dirty read: reading uncommitted data from another transaction. Non-repeatable read: a re-read row has changed (update). Phantom read: a re-run query returns new/removed rows (insert/delete).

24. What does the CAP theorem state about distributed data systems?

    A distributed system can simultaneously guarantee at most two of Consistency, Availability and Partition tolerance. Since network partitions are unavoidable, real systems trade off C versus A during a partition (CP or AP).

25. How does NoSQL generally differ from relational databases in schema and scaling?

    NoSQL is typically schema-flexible (schema-on-read) and scales horizontally across commodity nodes, often favoring availability and eventual consistency, whereas relational databases use fixed schemas, ACID transactions and scale mostly vertically.

See more Databases flashcards →

Planning Databases for Data Science

Databases (Data Science) FAQ