
--- sqlite3 commands to create a database
--- 
---    $ sqlite3 academics.db < create.sql
---

CREATE TABLE People (
  id INTEGER PRIMARY KEY NOT NULL,
  name TEXT NOT NULL,
  age INTEGER NOT NULL 
);
 
CREATE TABLE Courses (
  id INTEGER PRIMARY KEY NOT NULL,
  name TEXT NOT NULL 
);

CREATE TABLE Roles (
  id INTEGER PRIMARY KEY NOT NULL,
  name TEXT NOT NULL
);

CREATE TABLE Registrations (
  id INTEGER PRIMARY KEY NOT NULL,
  person INTEGER NOT NULL
    CONSTRAINT fk_People_id REFERENCES People(id),
  course INTEGER NOT NULL
    CONSTRAINT fk_Course_id REFERENCES Course(id),
  role INTEGER NOT NULL
    CONSTRAINT fk_Role_id REFERENCES Role(id),
  date TEXT NOT NULL
);
