A 38-year-old's MYSQL primer 08 - relational model, normalization, table design, transactions
This looks like the last primer installment, at least for the basics.
This round we'll keep practicing queries across multiple tables.
(Heads-up: I use the same practice tables, but my examples won't match the book's one-to-one. ><)
1. The relational model
In SQL, a table = a relation = the fundamental element of the relational model.
A relation (table) in the relational model consists of columns / attributes + rows / tuples / records.
The relational model defines a data structure: a relation is a set of tuples, and operations on a relation (table) correspond to operations on sets.
= Relational algebra theory
Rule 1. Operate on one or more relations.
Rule 2. The result of an operation is also a relation.
Rule 3. Operations may be nested.
+ α ) Turns out operators come in two types. I cross-checked with outside sources.
Type 1) Pure relational operators
Select - extracting rows (tuples); the WHERE clause filters data; of the rows present in a relation, it produces the subset satisfying the selection predicate, yielding a new relation. Because it operates on rows, it's also called a horizontal operation.
SELECT * from sample72_y WHERE y < 2;
Project - extracting columns (attributes); specifies which columns to return in the SELECT clause. Project extracts only the attributes listed from the given relation. Since it operates on columns, it's also called a vertical operator.
select y from sample72_y;
Join - in SQL, this refers to inner join.
select * from sample72_y INNER JOIN sample411 ON sample72_y.y = sample411.no;
+ α ) As a bonus, the outer join version is below.
Division - given two relations, it's the operation that finds, among the rows of A whose attribute values fully include all of B's attribute values, the attributes of A excluding B's attributes.
Type 2) General set operators
Union - addition across relations = SQL's UNION
select * from sample72_x UNION select * from sample72_y;Intersect - common part of relations = SQL's INTERSECT
select * from sample72_x INTERSECT select * from sample72_y;Difference - subtraction across relations = SQL's EXCEPT
select * from A EXCEPT select * from B;Cartesian product - produces every pairing between two relations.
select * from A, B; select * from A CROSS JOIN B;
2. Database design
Also called schema design. It covers deciding the table names, columns, data types, primary keys, etc., and a key part of it is considering how one table will relate to another when linked.
Column names carry both a logical name and a physical name. The name used in SQL is the physical name; the logical name is the descriptive or design-level name. Why keep both? Physical names are usually in the English alphabet, and if they get long SQL becomes hard to read — so lengths tend to be capped. (And other reasons too.)
Data types declare what each column in each table can store.
Design usually follows conceptual → logical → physical design.
3. Normalization
Normalization means reshaping and splitting tables into a proper form. 'Proper form' is somewhat subjective, but normalization is a guide built up from many developers' accumulated know-how — it's worth referencing.
My personal intuition: you know how in Excel, if you shove a sentence into one field instead of a word, you can't run calculations on it? Numbers, dates, and key terms have to live in separate columns. Normalization feels similar — slicing data into separable cells.
For example, the field written in Method 1 is almost unusable once a lot of such data piles up. But data accumulated in the shape of Method 2 can be sliced and diced endlessly to build new databases. A simple example: an Excel laid out as Method 2 can be quantified without much trouble. Turning raw material into information.
The crux, I think, is whether your data is processable. OK, enough of my own takes — back to the book. Normalization has steps. Let me list them.
1) First normal form
(1) Store only one value per cell.
(2) Extend repeating data vertically (rows), not horizontally (columns).
(3) This means splitting the table and setting up PKs and FKs so related tables can be joined.
2) Second normal form
(1) Find functionally dependent attributes (those that can be determined by the primary key) and split the table accordingly.
3) Third normal form
(1) Check for dependencies among non-key fields.
(2) There's up to 5NF, but in practice most people stop at 3NF.
4) The goal of normalization
With the principle 'one piece of data in one place,' you find repetition and duplication, split tables accordingly, and link them back via PKs. We do this so that data lives in one place — if it's changed or added somewhere else, related data can stay consistent automatically.
4. Transactions
1) start transaction - start a transaction (analogy: while writing a blog post, enabling the save-draft button? something like that)
start transaction2) commit - apply changes to the database (analogy: while writing a blog post, clicking the save-draft button? something like that)
commit;3) rollback - revert the database (analogy: while writing a blog post, the back button? something like that)
rollback;Note: unlike ORACLE, MySQL requires you to explicitly declare and manage transactions. I'll link some MySQL transaction-related blogs(link 1).
Oh, this one is good too(link 2). It explains things concisely.
Phew~ that's it~ Did you slice your radish well?
I don't know what kind of knife each of us wields,
but once the blade is drawn,
let's agree not to cut others with it~
Enjoy your radish~
Thanks for staying with me — coming up soon,
we'll move on to the advanced SQL exercises~
The advanced section covers how to use what we've learned more quickly and efficiently.
Have a warm day today too~
The end.
