-- CSG 130 Team 4 Create Tables Last Updated 11/15/2004 Bey Wang
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[user_profile]'))
drop table [dbo].[user_profile]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[coursesTaken]'))
drop table [dbo].[coursesTaken]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[clicked]'))
drop table [dbo].[clicked]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[coursePrefs]'))
drop table [dbo].[coursePrefs]
GO


if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[majorPrefs]'))
drop table [dbo].[majorPrefs]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[majors]'))
drop table [dbo].[majors]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[courses]'))
drop table [dbo].[courses]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[candidate_profile]'))
drop table [dbo].[candidate_profile]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[user_info]'))
drop table [dbo].[user_info]
GO 



CREATE TABLE dbo.user_info
(user_info_id int identity primary key, 
username varchar(8) not null,
pass varchar(32) not null,
fname varchar(20) not null,
lname varchar(20) not null,
email varchar(30) not null)

 




CREATE TABLE dbo.majors
(major_id int identity primary key, 
majorName varchar(30) not null)


-- populate start up data
insert into dbo.majors (majorName) 
values ('Computer Science') 
go
insert into dbo.majors (majorName) 
values ('Mechanical Engineering') 
go
insert into dbo.majors (majorName) 
values ('Electric Engineering') 
go
insert into dbo.majors (majorName) 
values ('MBA') 
go
insert into dbo.majors (majorName) 
values ('Pre MBA') 
go
insert into dbo.majors (majorName) 
values ('Mathematics') 
go


CREATE TABLE dbo.user_profile
(user_profile_id int identity primary key, 
user_info_id int foreign key references user_info,
gender char(1),
genderMate char(1),
DOB smalldatetime,
-- age int,(We should not record age in the system. Age should be calculated from current date with DOB
heightFt int,
heightIn int,
weight int,
imagePath varchar(255),
major_id int foreign key references dbo.majors,
yr char(4),
smoke bit,
vegetarian bit,
drink bit)

CREATE TABLE dbo.courses
(course_id int identity primary key,
courseNo varChar(8) not null,
courseName varchar(50) not null)
GO
-- populate start up data
insert into dbo.courses (courseNo, courseName) 
values ('CSG100','Data Structure') 
go
insert into dbo.courses (courseNo, courseName) 
values ('CSG130','Introduction to Database Systems') 
go



CREATE TABLE dbo.candidate_profile
(cp_id int identity primary key,
user_info_id int foreign key references dbo.user_info,
smoke bit,
vegetarian bit,
drink bit,
yrLower char(4),
yrUpper char(4),
ageLower int,
ageUpper int,
htLower int, 
htUpper int,
wtLower int,
wtUpper int,
defaultProf bit not null)
GO




CREATE TABLE dbo.coursesTaken (
course_id int foreign key references dbo.courses,
user_info_id int foreign key references dbo.user_info,
term varchar(2) not null,
year char(4) not null)
GO

CREATE TABLE dbo.clicked (
clicker_id int foreign key references dbo.user_info,
receiver_id int foreign key references dbo.user_info,
timeClicked datetime NOT NULL DEFAULT (getdate()))
-- By default, the current system date is entered.
GO


CREATE TABLE dbo.coursePrefs (
coursePrefs_id int identity primary key,
cp_id int foreign key references dbo.candidate_profile,
course_id int foreign key references dbo.courses)
GO

CREATE TABLE dbo.majorPrefs (
majorPrefs_id int identity primary key,
cp_id int foreign key references dbo.candidate_Profile,
major_id int foreign key references dbo.majors)
GO