drop table if exists students;
create table students (
   first varchar not null,
   last varchar not null,
   id int not null,
   primary key (id));

drop table if exists courses;
create table courses (
   coursenum varchar not null,
   title varchar not null,
   description varchar not null,
   primary key (coursenum));

drop table if exists enrollments;
create table enrollments (
   id int not null,
   coursenum varchar not null,
   semester varchar not null,
   year int not null,
   primary key (id,coursenum,semester,year),
   foreign key (id) references students(id),
   foreign key (coursenum) references courses(coursenum));

insert into students values ('Harry', 'Potter', 1);
insert into students values ('Hermione', 'Granger', 2);
insert into students values ('Ron', 'Weasley', 3);

insert into courses values ('P140', 'Intro to Potions', 'A course in potions.');
insert into courses values ('DA101', 'Intro to Dark Arts', 'A course in Dark Arts.');
insert into courses values ('HB100', 'Intro to Herbology', 'A course in Herbology.');

insert into enrollments values (1, 'DA101', 'Fall', 2010);
insert into enrollments values (1, 'P140', 'Fall', 2010);
insert into enrollments values (2, 'P140', 'Fall', 2011);
insert into enrollments values (3, 'P140', 'Fall', 2011);
insert into enrollments values (3, 'P140', 'Fall', 2012);