-- 
-- colleges_v2.sql
--
--          [0,1]                 M
-- [college] --- <accomodates> --- [student] 
--
-- Accomodates is a one-to-many relationship 
-- where students ARE NOT required to be in a college. 
-- 
-- Instead of using a table accomodates (as in colleges_v2.sql), 
-- here we have a foriegn key in the student table, that can be NULL, 
-- pointing at the college table. 

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


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

--
-- If in the ER model the relationship 
-- had many attributes, then this approach to 
-- a relational implementation 
-- might be sub-optimal since many records
-- could have many null values. But in our case 
-- we might expect that most students will be associated 
-- with a college and the relation has we no 
-- additional attributes, so this is not a problem here.  
create table Student (
   crsid varchar(10) not NULL, 
   first_name varchar(100), 
   last_name varchar(100), 
   CollegeID int, 
   primary key (Crsid), 
   constraint student_college foreign key (CollegeID) references College(CollegeID), 
);

-- Again, 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', 1);
insert into Student values ('sm999', 'Sam', 'Smith', 1);
insert into Student values ('kw1', 'Kate', 'Winslet', null);
