-- 
-- colleges_v2.sql
--
--           1                   M
-- [college] --- <accomodates> --- [student] 
--
-- Accomodates is a one-to-many relationship 
-- students ARE required to be in a college 
-- 
-- AS IN colleges_v2.sql, we use a  
-- foriegn key in the student table 
-- pointing at the college table, but now it cannot be null. 

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)
);

create table Student (
   crsid varchar(10) not NULL, 
   first_name varchar(100), 
   last_name varchar(100), 
   CollegeID int not NULL, 
   primary key (Crsid), 
   constraint student_college foreign key (CollegeID) references College(CollegeID), 
);

-- Now every student must have a college, but we 
-- can still have colleges without students. 
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', 4);


-- Some thoughts on the ER model. 

-- 1) As above, suppose we require every student 
--    to have a college and so add a foreign key 
--    pointing at the College table. 
--    Suppose that in our ER model the 
--    accomodates relationship has many attributes 
--    (room number, dietary info, and so on) 
--    and we don't want 
--    so store this in the Student table.  So, like 
--    colleges_v2.sql we use a table 
--    Accommodates with key crsid. 

--    In what way could instances of this 
--    schema become inconsistent? 

-- 2) [continued] To "fix" the problem just 
--    introduced it seems we need a "1-to-1" 
--    relationship between Accommodates 
--    and Students!  What is wrong? 

-- 3) All of the above should lead us to reconsider 
--    our ER model!  Perhaps those accommodates 
--    attributes are really student attributes.  
--    Discuss. 

