Databases and data models
A data model describes the data of an application before its database is made, at three levels kept in step (DB-002..DB-004): the conceptual model (entities and associations), the logical model derived from it (relations and keys), and the physical model — the SQL that makes the tables.
New data model… in the command palette makes one, from an example (customers, orders, products); it is a .mcd file of the folder, opened like a document. Any .mcd file opens the same way.
The conceptual model
It is written as text, on the left; its diagram is drawn on the right as you type.
-- Customers, their orders and the products ordered.
entity Customer
#customer_id: serial
name: varchar(80)
entity Order
#order_id: serial
date: date
entity Product
#product_id: serial
label: varchar(80)
price: decimal(8,2)
association Places
Customer 0,n
Order 1,1
association Contains
Order 1,n
Product 0,n
quantity: integerentity Name, then its attributes, indented (Enter after a line keeps the indent);#before the identifier (one or more attributes); a type after:.association Name, then each entity it links with its cardinality —0,1,1,1,0,n,1,n(also written01,11,0N,1N) — and its own attributes. Two links to the same entity (a reflexive association) are told apart by a role in brackets:Employee 0,1 (report),Employee 0,n (manager).--starts a comment.entitéis understood asentity.- Types:
integer,serial(a counter),real,decimal(p,s),varchar(n),char(n),text,date,time,datetime,boolean,blob; an identifier without a type is aninteger, another attribute atext.
Mistakes are listed under the text with their line — an unknown entity, an entity without identifier, a name used twice; click one to go to its line. ⇩ Export the diagram (SVG) saves the drawing.
The logical model
Logical model shows the relations derived by the usual rules:
- each entity becomes a relation, its identifier the primary key;
- an association with a side
x,1(a binary association) becomes a foreign key of the entity of that side, with the association's attributes —NOT NULLfor1,1, empty allowed for0,1, unique when both sides arex,1; - any other association becomes a relation whose key is made of the keys of its entities.
Customer (customer_id, name)
Order (order_id, date, #customer_id)
Product (product_id, label, price)
Contains (#order_id, #product_id, quantity)The primary key is underlined, a foreign key follows #.
The physical model
Physical model (SQL) gives the CREATE TABLE statements for SQLite, PostgreSQL or MySQL / MariaDB, the tables in an order where each follows those it refers to, with their primary keys, foreign keys and unique constraints. Copy the SQL, or Save as a .sql file: the file is written beside the model, and runs with SQLite like any .sql file of the folder (see Code cells) — the tables are made and their keys checked.
SQLite databases
A SQLite database (.sqlite, .sqlite3, .db) opens as a document (DB-001); New SQLite database in the command palette makes an empty one. SQLite is downloaded the first time, after you agreed, checked, and runs in the sandbox of the code cells.
- Tables lists its tables and views.
- Data shows the rows of the table chosen, a hundred at a time. Double-click a cell — or Enter on it — to change it (emptied, it becomes
NULLwhen the column allows it); + New row adds a row with the default values, 🗑 deletes one. Views, and tables without rowid, are changed in SQL. - Structure shows the columns — type, key, required, default, the table a column refers to — and the SQL that made the table.
- SQL runs statements (Ctrl+Enter): queries show their rows, other statements change the database — tables made appear in the list. Run a .sql file… runs a script, such as the SQL saved from a data model: the tables of the model are made in the database.
- The foreign keys are checked: a row referring to a row that does not exist is refused.
Save writes the database back as a SQLite file, with the changes.
◇ Conceptual data model reads the conceptual model back from the tables (DB-005), as a .mcd file beside the database (or downloaded): a table whose key is made of references to other tables becomes an association between them, another table an entity, and each of its references an association with a side 1,1 (or 0,1 when the column may be empty).