
CREATE DATABASE 'students.fdb';

-- Tables

CREATE TABLE Student (
  id INT,
  first_name VARCHAR(30),
  last_name CHAR(30),
  primary key (id)
);

CREATE TABLE Course (
  code CHAR(6),
  name CHAR(40),
  credits INT,
  PRIMARY KEY (code)
);

CREATE TABLE Enrolment (
  student_id INT,
  course_code CHAR(6),
  grade CHAR(1),
  FOREIGN KEY (student_id) REFERENCES Student(id),
  FOREIGN KEY (course_code) REFERENCES Course(code)
);

INSERT INTO Student VALUES ( 0, 'Ulf', 'Norell' );

INSERT INTO Course VALUES
  ( 'AFP101', 'Advanced Functional Programming', 5 );

INSERT INTO Enrolment VALUES
  ( 0, 'AFP101', '5' );

COMMIT;

SELECT * FROM Course;
SELECT * FROM Student;

