//$PH/06f522/asgnt3/TBUpdatedSchooldb.sch - RJL060926; 
//TBD: augment fields as noted at end.
// copied from  $CASE/gen/ver_10dfltSuper/updated_schooldb.sch
// 96s523/gen/lechner/updated_schooldb.sch 	- RJL 96/5/10
// adapted from /nfs/jupiter/proj3/case/96s523/gen/mmurphy/chgen10_log/Test

teacher CC /* teachers */
{
 CCid    teacher_id       c8      1 /* primary key field */
 tname  teacher_name      t30     0 /* teacher's name */
}
room  DD /* rooms for courses */
{
DDid     room_id          c8      1 /* primary key field */
room    room_num          i2      0 /* room number */
}
classes BB /* courses with assignments to teachers & rooms */
{
 BBid course_id         c8      1 /* primary (surrogate) key field */
 CCid teacher_id        c8      s /* one teacher per course */
 DDid where_id          c8      s /* one room per course */        
 cname  course_name     t30     0 /* catalog name of course */
}
students AA /* students */
{
 AAid student_id        c8      1 /* primary (surrogate) key field*/
 fname  first_name      t30     0 /* first name of student */
 lname  last_name       t30     0 /* last name of student */
}

enrollment EE /* enrollment of students in courses */
{
 EEid record_id       c8      1 /* primary key */
 AAid student         c8      1 /* fkey to student */
 BBid course          c8      1 /* fkey to course */
}

// Schema drawing:
//
//      CC teacher      DD room         
//              \          /
//               \        /
//                BB course     AA Student 
//                     \            /
//                      \          /
//                      EE Enrollmt

//Note added for 06f522/asgnt3: - RJL060926
// Fields to dd to produce more composite candidate keys
// phoneNo in AA and CC; sectionNo in BB;
// Change BB to CS = CourseSection with sectionNo (e.g.291)
// Add CD = CourseDescription with courseNo (e.g. 522)
// Add DP = Department with deptNo (eg. 91)
// Note: candidate key 91.522.291 does not directly appear in CS.
// Add CK = CandidateKey and CA = CK-to-TA association tables.