Databases: Design and Organize Your Data
Understand what a database is, the difference between relational and non-relational databases (documents, key-value, wide-column and graphs), how to choose a good database system for each scenario, how to create, read, update and delete data with SQL and with documents, and how to design well-related, normalized data. Practice with a real SQLite inside your browser.
코스 배지 Databases 모든 레슨을 완료하면 받을 수 있어요.
강좌 내용
Getting started
4 레슨What is a database?
Why a shared spreadsheet stops working, what a database is, what SQL is, and your first query in a real SQLite.
Tables, rows, columns… and documents
The pieces of a relational database (table, column, row, record, value, schema) and their equivalents in a document database (collection, document, field).
Data types
Why every column has a type: integers, decimals, text, booleans, dates and NULL; what they're called in SQLite, PostgreSQL, MySQL and JSON documents, and how to choose the right type.
What a database system does for you
What a database management system (DBMS) is, the work it does for you (rules, queries, many users at once, transactions, security, backups) and where it lives: inside the app, on a server or in the cloud.
Relational and non-relational
4 레슨Two ways of thinking about data
The same order stored in a relational database (several tables joined when you query) and in a document database (everything in one record). What each one gains and loses, where the name NoSQL came from and why the line between them is blurring.
The four NoSQL families
Documents, key-value, wide-column and graphs: how each family stores data, how it's queried, what it's good at and where it struggles, with the store as the example. Plus: time series, search engines and vector databases.
Consistency and scale: ACID and BASE
What a database guarantees when things go wrong (ACID), what changes when data is spread across many servers (BASE, eventual consistency, the CAP theorem) and how to tell what each piece of data needs.
How to choose a database system
Six questions to choose a project's database, real scenarios (online store, chat, offline app, sensors, social network, search, AI) and why large systems combine several.
CRUD with SQL
5 레슨Creating tables
The C in CRUD starts with structure: CREATE TABLE, how to name tables and columns, the primary key, the NOT NULL, UNIQUE, DEFAULT and CHECK rules, and how to change (ALTER TABLE) or delete (DROP TABLE) a table.
Inserting data: INSERT
The C in CRUD: INSERT INTO with columns and values, text in quotes (and with apostrophes), NULL, several rows at once, default values, the automatic id, RETURNING and how to read the rules' errors.
Querying data: SELECT
The R in CRUD: choosing columns and computing new ones, filtering with WHERE (comparisons, AND, OR, NOT, IN, BETWEEN, LIKE, IS NULL), sorting with ORDER BY, limiting with LIMIT and removing duplicates with DISTINCT.
Summarizing data: COUNT, SUM and GROUP BY
More of the R in CRUD: counting, adding, averaging and finding minimums and maximums with aggregate functions; grouping with GROUP BY, filtering groups with HAVING and how it differs from WHERE.
Updating and deleting: UPDATE and DELETE
The U and D in CRUD: changing data with UPDATE … SET … WHERE, deleting rows with DELETE, the danger of forgetting the WHERE, how to work with a safety net (SELECT first, transactions, .changes) and soft deletes.
Keys and relationships
4 레슨Types of keys
Superkey, candidate key, primary and alternate keys; natural or surrogate; simple or composite; foreign and unique. What makes a good primary key and how keys are declared in SQL.
Types of relationships
One-to-one, one-to-many and many-to-many: how to recognize them, cardinality and participation (required or optional), how each is built with foreign keys and junction tables, and what happens on delete (ON DELETE).
Querying several tables: JOIN
Joining related tables in one query: INNER JOIN, table aliases, ambiguous columns, LEFT JOIN to include what has no match, several joins in a row, JOIN with GROUP BY and many-to-many relationships.
Entity-relationship diagrams
The conceptual, logical and physical model; how to read and draw an ER diagram in crow's foot notation; a method to go from a description in words to the diagram and from the diagram to SQL.
Normalization
5 레슨Repeated data and anomalies
Why a table with repeated data breaks down with use: insertion, update and deletion anomalies, shown step by step, and the idea that leads to normalization.
Functional dependencies
The formal tool of normalization: what X → Y means, how to check it with SQL, Armstrong's axioms, the attribute closure X⁺ and how to find candidate keys; full, partial and transitive dependencies.
First and second normal form
1NF: atomic values, no lists or repeating groups. 2NF: no partial dependencies on the key. How to decompose a table with SQL (INSERT … SELECT) without losing information and how to check it.
Third normal form and BCNF
3NF: no transitive dependencies. BCNF (Boyce-Codd): every determinant is a superkey. Formal definitions, examples, decomposition with SQL, the case where 3NF and BCNF differ and a look at 4NF and 5NF.
When to create a new table and when to denormalize
A practical guide to deciding when to split data into a new table; the cost of normalizing; indexes, views and triggers as alternatives, and when to repeat data on purpose (denormalize) without losing control.
NoSQL in practice
4 레슨CRUD with documents
Creating, reading, updating and deleting in a document database with the MongoDB console: insertOne, find with filters and operators ($gt, $in, $or…), projection and sorting, updateOne with $set, $inc and $push, deleteMany, unique indexes and validation rules. With their SQL equivalents.
Modeling documents: embed or reference
The big decision in document design: putting the data inside (embedding) or pointing to another document (referencing). What 1:1, 1:N and N:M look like in documents, the extended reference, size limits, and how to join collections with aggregate and $lookup.
Designing from the queries
The method for designing document databases: start with the screens and their queries. Useful patterns (subset, computed values, buckets), when to create a new collection, a subcollection or embed, and how it applies in Firestore.
Key-value and graphs in practice
Redis in depth: key names, value types, expiry and the cache, session, rate-limiting and ranking patterns. Graphs in depth: nodes, relationships, Cypher, and the same traversals in SQL with WITH RECURSIVE to understand when a graph database pays off.
Final project
2 레슨Final project (1): the store in SQL
The whole course in one project: from an online store's requirements to the ER diagram, the check of the normal forms, the SQL with its rules and indexes, a sale with a transaction and the reports the business needs.
Final project (2): the same store in documents
The Online Corner Store in a document database: design from the screens, documents with embedded data and extended references, a sale with conditional updates, reports with aggregate, and the final comparison between the two models.