use csc325;

create table faculty2008
( facultyID bigint(20) unsigned not null auto_increment,
  first     varchar(25) not null,
  last      varchar(25) not null,
  title     varchar(255) not null,
  username  varchar(255) not null,
  primary key (facultyID)
);

create table room
( roomID              bigint(20) unsigned not null auto_increment,
  roomNumber          varchar(255) not null,
  phone               bigint(20) unsigned,
  roomName            varchar(255) not null,
  roomType            bigint(20) unsigned,
  numberWorkstations  bigint(20) unsigned,
  primary key (roomID)

);

create table roomCategories
( roomType      bigint(20) unsigned not null auto_increment,
  description   varchar(255) not null,
  primary key (roomType)
);

create table facultyRoom
( facultyID      bigint(20) unsigned, 
  roomID         bigint(20) unsigned
);

INSERT INTO faculty2008 VALUES
   (1, "Henry", "Walker", 
       "Samuel R. and Marie-Louise Rosenthal Professor of Natural Science and Mathematics",
       "walker"),
   (2, "Janet", "Davis", "Assistant Professor of Computer Science",
       "davisjan"),
   (3, "Jerod", "Weinman", "Assistant Professor of Computer Science",
       "weinman"),
   (4, "John", "Stone", "Lecturer in Computer Science",
       "stone"),
   (5, "Marge", "Coahran", "Instructor in Computer Science",
       "coahranm"),
   (6, "Samuel", "Rebelsky", "Associate Professor of Computer Science",
       "rebelsky"),
   (7, "Soren", "Berg", "Mellon Post-Baccalaureate Research Assistant",
       "bergsore");

INSERT INTO room VALUES
   ( 1, "3807", 4796, "HCI Lab", 2, 4),
   ( 2, "3809", 4306, "Computer Science", 1, 1),
   ( 3, "3811", 4208, "Computer Science", 1, 1),
   ( 4, "3812", 4340, "Computer Science Research", 2, 4),
   ( 5, "3813", 3881, "CS Teaching Lab", 3, 19),
   ( 6, "3814", 4612, "CS Learning Center", 6, 5),
   ( 7, "3815", 4239, "Open Lab", 3, 19),
   ( 8, "3816", NULL, "Student Study Area", 4, 0),
   ( 9, "3817", NULL, "CS Commons", 5, 0),
   (10, "3818", 3982, "AI/Capstone Lab", 3, 5),
   (11, "3819", 4032, "Systems Lab", 3, 19),
   (12, "3820", 4611, "Math Teaching Lab", 3, 17),
   (13, "3821", 4891, "Classroom", 7, 1),
   (14, "3822", NULL, "Student Study Area", 4, 1),
   (15, "3823", NULL, "Multimedia Lab", 2, 4),
   (16, "3824", 4410, "Computer Science", 1, 1),
   (17, "3825", 9812, "Computer Science", 1, 1),
   (18, "3827", 3127, "Computer Science", 1, 1),
   (19, "3828", 4457, "Vision Lab", 2, 4),
   (20, "3829", 3181, "Computer Science", 1, 1),
   (21, "3830", 4990, "Computer Science Research", 2, 4);

INSERT INTO roomCategories VALUES
   (1, "Faculty Office"),
   (2, "Research Lab"),
   (3, "Teaching Lab"),
   (4, "Study Area"),
   (5, "Commons"),
   (6, "Learning Center"),
   (7, "Classroom");

INSERT INTO facultyRoom VALUES
   (2, "3807"),
   (2, "3809"),
   (1, "3811"),
   (1, "3812"),
   (6, "3823"),
   (6, "3824"),
   (3, "3825"),
   (3, "3830"),
   (5, "3827"),
   (4, "3829"),
   (4, "3830");