Procedure¶
Creating tables with SQL¶
Final Step: Implementation of the ER Diagram¶
Mapping the ER model to SQL commands to create the tables
- Create the tables
- For every attribute of every relation, find a sensible data type
- Determine whether attributes are nullable or non-nullable
- Define the primary key
- Define foreign keys
- Declare additional indexes to optimize performance
- Define access rights
To create the table, you need the CREATE TABLE SQL command.
This does not insert any data yet, but only creates an empty scaffold.
DDL - Data Definition Language¶
Allows operations on the definitions of data structures:
CREATE– creating tablesALTER– modifying tablesDROP– deleting tablesTRUNC– truncating data but not deleting the table
The possible data structures are:
TABLE– tables for storing dataVIEW– dynamically generated views on tablesMATERIALIZED VIEW– tables that are derived from other tables
Creating Tables in SQL¶
CREATE TABLE table_name (
attribute1_name attribute1_type attribute1_constraints,
attribute2_name attribute2_type attribute2_constraints,
…,
table_constraints
)
Data types in SQLite vs. Python¶
| SQLite | Python | Description |
|---|---|---|
Boolean (INTEGER) |
bool |
Boolean values |
INTEGER |
int |
Integer |
REAL |
float |
Floating-point number |
NUMERIC |
- | Interprets arbitrary input data as numbers |
TEXT |
str |
Text string, stored in UTF-8 or UTF-16 |
BLOB |
bytes |
Arbitrary block of binary data |
NULL |
None |
No data |
Example: Creating Tables in SQLite¶
For our geometry example, the following CREATE TABLE statements are produced:
CREATE TABLE Points (
point_id INTEGER,
x REAL,
y REAL
);
CREATE TABLE Lines (
lines_id INTEGER,
start INTEGER,
end INTEGER
);
Keys and Constraints in SQL¶
Primary and secondary keys are usually represented by INTEGER columns.
The primary key of every table must be explicitly marked by the PRIMARY KEY constraint.
Other constraints that must be observed when inserting new data:
NOT NULL– Values must not be nullAUTOINCREMENT– Numeric primary keys are generated automaticallyUNIQUE– Values must be unique; duplicates are not allowedCHECKED– Additional logical constraint for new valuesFOREIGN KEY– Value of an existing foreign key
Example: Primary Keys, Foreign Keys, and Constraints¶
CREATE TABLE Points (
point_id INTEGER PRIMARY KEY AUTOINCREMENT,
x REAL NOT NULL,
y REAL NOT NULL
);
CREATE TABLE Lines (
lines_id INTEGER PRIMARY KEY AUTOINCREMENT,
start INTEGER NOT NULL,
end INTEGER NOT NULL,
FOREIGN KEY(start) REFERENCES Points(point_id),
FOREIGN KEY(end) REFERENCES Points(point_id)
);
CREATE TABLE Advanced Features¶
Additional options:
DEFAULT: Set the default value for new elementsWITHOUT ROWID: Tables without a ROWIDSTRICT: Enforces strict data types for the table
Adding data to tables¶
Data is added to tables using the SQL command INSERT
Add data in the order of the column names:
INSERT INTO Points VALUES(1, 54.083336, 12.108811);
INSERT INTO Points VALUES(2, 12.094167, 54.075211);
INSERT INTO Lines VALUES(1, 1, 2);
or with explicit column names:
INSERT INTO Points(x, y) VALUES(54.083336, 12.108811);
INSERT INTO Points(x, y) VALUES(12.094167, 54.075211);
INSERT INTO Lines(start, end) VALUES(1, 2);
Create a table from existing data¶
CREATE TABLE tablename AS
SELECT … FROM … WHERE …
It has no primary key; it can only be defined afterwards.
ALTER TABLE - Statement¶
Renaming tables and columns, and adding and dropping columns
ALTER TABLE tablename TO newtablename
Example:
ALTER TABLE Points RENAME TO Punkte
ALTER TABLE - Example¶
Rename owner to customer, add a column for account number
ALTER TABLE tablename ADD COLUMN column_name column_type
Example:
ALTER TABLE PolygonType ADD COLUMN maxPoints INTEGER
