-- 
-- colleges_v1.sql
--
--          [0,1]                 M
-- [college] --- <accomodates> --- [student] 
--
-- Accomodates is a one-to-many relationship 
-- where students ARE NOT required to be in a college 
-- 

drop table College if exists;
drop table Student if exists;
drop table Accommodates if exists;


create table College (
   CollegeID int not NULL, 
   cname varchar(100), 
   primary key (CollegeID)
);

create table Student (
   crsid varchar(10) not NULL, 
   first_name varchar(100), 
   last_name varchar(100), 
   primary key (Crsid)
);

create table Accommodates (
   crsid varchar(10) not NULL, 
   CollegeID int not NULL, 
   primary key (Crsid), 
   constraint accomodates_college foreign key (CollegeID) references College(CollegeID), 
   constraint accomodates_student foreign key (Crsid) references Student(Crsid)
);  

-- In this simple instance we have both colleges without students 
-- and students without colleges 
insert into College values (1, 'Kings');
insert into College values (2, 'Churchill');
insert into College values (3, 'Jesus');
insert into College values (4, 'Queens');
insert into Student values ('jd1000', 'John', 'Doe');
insert into Student values ('sm999', 'Sam', 'Smith');
insert into Student values ('kw1', 'Kate', 'Winslet');
insert into accommodates values ('jd1000', 1), ('sm999', 1); 