所有课程
初级 17 hours 免费

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 完成所有课程即可获得。
Databases: Design and Organize Your Data

课程内容

模块 1

Getting started

4 课时
1

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.

20 min 免费
2

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).

25 min 免费
3

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.

30 min 免费
4

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.

25 min 免费
模块 2

Relational and non-relational

4 课时
5

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.

25 min 免费
6

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.

30 min 免费
7

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.

30 min 免费
8

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.

30 min 免费
模块 3

CRUD with SQL

5 课时
9

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.

30 min 免费
10

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.

25 min 免费
11

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.

40 min 免费
12

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.

35 min 免费
13

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.

30 min 免费
模块 4

Keys and relationships

4 课时
14

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.

35 min 免费
15

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).

35 min 免费
16

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.

40 min 免费
17

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.

40 min 免费
模块 5

Normalization

5 课时
18

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.

25 min 免费
19

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.

45 min 免费
20

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.

45 min 免费
21

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.

45 min 免费
22

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.

40 min 免费
模块 6

NoSQL in practice

4 课时
23

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.

45 min 免费
24

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.

45 min 免费
25

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.

40 min 免费
26

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.

45 min 免费
模块 7

Final project

2 课时
27

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.

60 min 免费
28

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.

60 min 免费

打造您的数字形象

今天就开始:给我们留言,我们会在 24 小时内回复。