Making a Database with SQLite

It took me more than 5 search sources to figure out how to make a database from an ERD. It doesn’t need to.

Here’s a quick step by step of how I got my stuff together.

Photo by Kevin Ku on Unsplash

— I’m assuming you have an ERD already. Just need to get that sqlite working and its tables

1. SQLite is already on Macs so no install required *cheers*
2. create a new sqlite folder and database in root directory

navigate to root

>>touch sqlite
>>cd sqlite

create a database

>>sqlite3 dbName.db

3. open a text editor in this location (where the database is) and make a .sql file here

4. populate it with CREATE TABLE commands like so

DROP TABLE IF EXISTS tools ;
CREATE TABLE tools (
toolName VARCHAR(100) NOT NULL,
toolType VARCHAR(100) NULL,
language VARCHAR(100) NULL,
CONSTRAINT pk_toolName PRIMARY KEY (toolName)
);
DROP TABLE IF EXISTS websiteMedia ;
CREATE TABLE websiteMedia (
url VARCHAR(100) NOT NULL,
type VARCHAR(100) NOT NULL,
CONSTRAINT fk_url FOREIGN KEY (url) REFERENCES website (url),
CONSTRAINT fk_multimediaType FOREIGN KEY (type) REFERENCES multimedia (type)
);
DROP TABLE IF EXISTS officeLocation ;
CREATE TABLE officeLocation (
officeID INTEGER PRIMARY KEY AUTOINCREMENT,
postalCode VARCHAR(100) NOT NULL,
country VARCHAR(100) NULL,
city VARCHAR(100) NULL,
CONSTRAINT ck_officeLocation UNIQUE (postalCode)
);

— link to explain primary keys, foreign keys, candidate keys, datatypes, and others — https://www.sqlitetutorial.net/sqlite-primary-key/

5. HOW DO RELATIONS WORK?!

basically all the relations that you will ever need and their required number of tables

— also if you want to read more https://www.cs.uct.ac.za/mit_notes/database/htmls/chp06.html#converting-entity-relationships-into-relations

6. go back to the sqlite location turn on FKs

~/sqlite $ sqlite3 websiteCompanies.db
sqlite> .database
sqlite> PRAGMA foreign_keys = ON;

Use this to check: that 1 means the FKs are enabled
sqlite> PRAGMA foreign_keys;
1

7. Run that script!

sqlite> .read nameDB.sql

tables should be made, check with this
sqlite> .tables

8. Populate the database…

9. View them from terminal using

sqlite> select * from tableName;

10. make it easier to see tables in terminal

sqlite> .header on
sqlite> .mode columns

if you really want to see all the commands this db made

sqlite> .dump

need to recheck that table? say no more.

sqlite> PRAGMA table_info(tableName);

made a mistake? DROP THAT. — and recreate table and rerun the command to make the table columns

sqlite>DROP TABLE tableName

__________

last few steps I found in this really helpful youtube vid: https://www.youtube.com/watch?v=iyXYwNQC6ag

___________

Hope this helped :)