/* ======================================================================
/* SQL code for creating tables and constraints
/* ======================================================================

create table Usr /* for users of the system; this is a peer-to-peer application
(
    loginName                 varchar(10)            not null,
    password                  varchar(8)             not null,
    lastName                  varchar(30)            not null,
    firstName                 varchar(30)            not null,
    phone                     char(10)               not null,
    email                     varchar(30)            not null,
    constraint PK_USR primary key (loginName)
)

create table Student /* a student is a user
(
    loginName                 varchar(10)            not null,
    major                     varchar(30)            not null,
    status                    char(10)               not null,
    constraint PK_STUDENT primary key (loginName),
	/* each user record corresponds to at most one student record
    constraint CK_STATUS_STUDENT check (status in ('freshman','sophomore','junior','senior','graduate'))
)

create table FacultyStaff /* a faculty/staff is a user
(
    loginName                 varchar(10)            not null,
    department                varchar(30)            not null,
    position                  varchar(30)            not null,
    constraint PK_FACSTA primary key (loginName)
)

create table Lives /* this relates each user with a single address
(
    loginName                 varchar(10)            not null,
    addrId                    int                    not null,
    constraint PK_LIVES primary key (loginName)
	/* each user can live at at most one address
)

create table Address /* each address has an id
(
    addrId                    int                    not null,
    constraint PK_ADDRESS primary key (addrId)
)

create table CampusAddr /* a campus address is an address
(
    addrId                    int                    not null,
    street                    varchar(50)            not null,
    roomNo                    varchar(10)            not null,
    constraint PK_CAMPUSADDR primary key (addrId),
    constraint AK_ADDR_CAMPUSADDR unique (street, roomNo)
	/* stree and room nnumber together should be unique
)

create table ResidenceAddr /* a residence address is an address
(
    addrId                    int                    not null,
    street                    varchar(50)            not null,
    town                      varchar(30)            not null,
    state                     char(2)                default 'NY' not null,
    zipcode                   char(5)                not null,
    nearestIntersection       varchar(50)            not null,
    constraint PK_RESIDADDR primary key (addrId),
    constraint AK_ADDR_RESIDADDR unique (street,town,state,zipcode,nearestIntersection)
	/* address items together should be unique
)

/* all information above is required; the attribute values should be not null

create table OptionalInfo /* each user can have a set of optional information
(
    loginName                 varchar(10)            not null,
    birthYear                 int                    null    ,
    sex                       char(1)                null    ,
    smokerOrNot               char(1)                null    ,
    preferDriveOrRide         varchar(15)            null    ,
    commuteCostCompensation   int                    null    ,
    currentCommuteMethod      varchar(20)            null    ,
    constraint PK_OPTINFO primary key (loginName),
	/* each user can have at most one set of optional info
    constraint CK_SEX_OPTINFO check (sex in ('M','F')),
    constraint CK_SMOKERORNOT_OPTINFO check (smokerOrNot in ('Y','N')),
    constraint CK_BIRTHYEAR check (birthYear between 1000 and 9999),
	/* year is a 4-digit integer
    constraint CK_PREFDRIVEORRIDE_OPTINFO check (preferDriveOrRide in ('drive only','share driving','ride only'))
)

create table Owns /* this relates each user with the cars he/she owns
(
    loginName                 varchar(10)            not null,
    carId                     int                    not null,
    constraint PK_OWNS primary key (loginName,carId)
)

create table Car /* cars are uniquely identified with the information modeled
(
    carId                     int                    not null,
    manufacture               varchar(30)            not null,
    model                     varchar(20)            not null,
    year                      int                    not null,
    constraint PK_CAR primary key (carId),
    constraint AK_CARINFO_CAR unique (manufacture, model, year)
	/* manufacturer, model, year together should be unique
    constraint CK_CAR_YEAR check (year between 1000 and 9999),
)

create table DailyCommute /* daily commute requests posted by users
(
    loginName                 varchar(10)            not null,
    startTime                 datetime               not null,
    endTime                   datetime               not null,
    maxMinutesEarlyArrival    int                    default 0 null    ,
    maxMinutesLateDeparture   int                    default 0 null    ,
    year                      int                    not null,
    season                    char(10)               not null,
    constraint PK_DAILYCOMMUTE primary key (loginName),	
	/* each user can post at most one daily commute
    constraint CK_TIMES_DAILYCOMM check (startTime<endTime),
    constraint CK_DAILYCOMM_YEAR check (year between 1000 and 9999),
    constraint CK_SEASON_DAILYCOMM check (season in ('Fall','Spring','Summer'))
)

create table OtherTrip /* other trip requests posted by users
(
    tripId                    int                    not null,
    date                      datetime               not null,
    reason                    varchar(99)            null    ,
    fromAddr                  int                    not null,
    toAddr                    int                    not null,
    departureTime             datetime               not null,
    arrivalTime               datetime               not null,
    maxMinutesEarlyDeparture  int                    default 0 null    ,
    maxMinutesEarlyArrival    int                    default 0 null    ,
    numberOfPassengers        int                    default 1 null    ,
    maxNumberOfPassengers     int                    default 1 null    ,
    costCompensation          int                    null    ,
    loginName                 varchar(10)            not null,
    constraint PK_OTHERTRIP primary key (tripId)
)

/* below are foreign key constraints for tables that refer to other tables
each student record corresponds to at most one user record
alter table Student
    add constraint FK_STUDENT_ISA_USR foreign key (loginName)
       references Usr (loginName)

alter table FacultyStaff
    add constraint FK_FACSTA_ISA_USR foreign key (loginName)
       references Usr (loginName)

alter table Lives
    add constraint FK_LIVES_BY_USR foreign key (loginName)
	    references Usr (loginName),
    	constraint FK_LIVES_AT_ADDR foreign key (addrId)
	    references Address (addrId)

alter table CampusAddr
    add constraint FK_CAMPUSADDR_ISA_ADDR foreign key (addrId)
       references Address (addrId)

alter table ResidenceAddr
    add constraint FK_RESIDADDR_ISA_ADDR foreign key (addrId)
       references Address (addrId)

alter table OptionalInfo
    add constraint FK_OPTINFO_OF_USR foreign key (loginName)
       references Usr (loginName)

alter table Owns
    add constraint FK_OWNS_BY_USR foreign key (loginName)
   	    references Usr (loginName),
    	constraint FK_OWNS_OF_CAR foreign key (carId)
	    references Car (carId)

alter table DailyCommute
    add constraint FK_DAILYCOMM_BY_USR foreign key (loginName)
	    references Usr (loginName)

alter table OtherTrip
    add constraint FK_OTHERTRIP_FROM_ADDR foreign key (fromAddr)
	    references Address (addrId),
    	constraint FK_OTHERTRIP_TO_ADDR foreign key (toAddr)
	    references Address (addrId),
	constraint FK_OTHERTRIP_OF_USR foreign key (loginName)
	    references Usr (loginName)

go

/* Most constraints can be expressed using primary key, foreign key, and 
/* unique and check clauses in create table statements, as we've seen.
/* We may also use create rule statements of Sybase to express domain
/* constraints globally.
/* For example, we have used check clause in three places to enforce that
/* year is a 4-digit integer.  We could express the same as follow:
/* 1. add the following statements once and for all
/*
/*	execute sp_addtype T_year, 'int', 'null'
/*
/*	create rule R_year
/*	    as @y between 1985 and 3000
/*
/*	execute sp_bindrule R_year, T_year
/*
/* 2. replace type int in each attribute definition for year with T_year.
/* 3. remove every constraint that use check clause to check years.
/* 
/* You must have experienced the pain of having to delete something in
/* your database.  To remove the type and rule definitions, you may use
/*
/*	if exists(select 1 from dbo.systypes where name='T_year')
/*	execute sp_droptype T_year
/*
/*	if exists(select 1 from dbo.sysobjects where name='R_year' and type='R')
/*	drop rule R_year

/* Other constraints that require the use of general assertions can not be
/* expressed directly in Sybase, as Sybase does not support assertions.
/* Our particular example is that only car owners may offer rides.
/* Such constraints could in general be enforced using triggers or coded 
/* in the application program, as we will see later.

/* ======================================================================
/* SQL code for loading data
/* ======================================================================

/* Usr(loginName,password,lastName,firstName,phone,email)
insert Usr values('user01','01','Vincent','Jimmy','1234567890','jm@yahoo.com')
insert Usr values('user02','02','Cage','Nickle','0987654321','cn@hotmail.com')
insert Usr values('user03','03','Jordan','Michael','1112223333','mj@yahoo.com')
insert Usr values('user04','04','Gates','Bill','2223334444','gb@hotmail.com')

/* Student(loginName,major,status) 
insert Student values('user01','Computer Science','junior')
insert Student values('user02','Physics','graduate')

/* FacultyStaff(loginName,department,position)
insert FacultyStaff values('user03','Computer Science','professor')
insert FacultyStaff values('user04','Physics','professor')

/* Lives(loginName,addrId)
insert Lives values('user01',1)
insert Lives values('user02',1)
insert Lives values('user03',3)
insert Lives values('user04',4)

/* Address(id)
insert Address values(1)
insert Address values(2)
insert Address values(3)
insert Address values(4)
insert Address values(5)

/* CampusAddr(addrId,street,roomNo)
insert CampusAddr values(1,'Steeley','A101')
insert CampusAddr values(2,'Steeley','A102')

/* ResidenceAddr(addrId,street,town,state,zipcode,nearestIntersection) 
insert ResidenceAddr values(3,'Stony Brook Road','Stony Brook','NY','11790','Centerreach')
insert ResidenceAddr values(4,'Jerusalem Avenue','Sea Ford','NY','11794','Wantagh')
insert ResidenceAddr values(5,'South Station','Boston','MA','01800','South Station')

/* OptionalInfo(loginName,birthYear,sex,smokerOrNot,preferDriveOrRide,
/*	commuteCostCompensation,currentCommuteMethod) 
insert OptionalInfo values('user02',1976,'M','N','ride only',10,'bus')
insert OptionalInfo values('user04',1964,'F','N','drive only',10,'drive alone')

/* Owns(loginName,carId)
insert Owns values('user03',1)
insert Owns values('user04',2)

/* Car(carId,manufacture,model,year) 
insert Car values(1,'Honda','Civic',2000)
insert Car values(2,'Ford','Taurus',2001)

/* DailyCommute(loginName,startTime,endTime,
/*	maxMinutesEarlyArrival,maxMinutesLateDeparture,year,season)
/* January 1, 1800 is used as a dummy date
insert DailyCommute values('user03','1800-01-01 08:00:00','1800-01-01 18:00:00',10,10,2002,'Spring')
insert DailyCommute values('user04','1800-01-01 09:00:00','1800-01-01 17:00:00',10,10,2002,'Fall')

/* OtherTrip(tripId,date,reason,fromAddr,toAddr,departureTime,arrivalTime,
/*	maxMinutesEarlyDeparture,maxMinutesEarlyArrival,
/*	maxNumberOfPassengers,numberOfPassengers,costCompensation,loginName)
insert OtherTrip values(1,'2002-03-23','visiting friend',1,5,
	'2002-03-23 08:00:00','2002-03-23 12:00:00',10,30,0,2,30,'user01')
insert OtherTrip values(2,'2002-03-02','tennis game',3,5,
	'2002-03-02 09:00:00','2002-03-02 15:00:00',10,60,2,1,50,'user03')

/* ======================================================================
/* Results of querying table and constraint definitions (some white spaces removed)
/* ======================================================================

Name                           Owner                          Type
------------------------------ ------------------------------ ----------
Usr                            dbo                            user table

Data_located_on_segment        When_created                  
------------------------------ ----------------------------- 
default                        Mar 18 2002  8:55:17:806PM    

Column_name Type    Length Prec Scale Nulls Default_name Rule_name Identity
----------- ------- ------ ---- ----- ----- ------------ --------- --------
loginName   varchar 10     NULL NULL  0     NULL         NULL      0
password    varchar 8      NULL NULL  0     NULL         NULL      0
lastName    varchar 30     NULL NULL  0     NULL         NULL      0
firstName   varchar 30     NULL NULL  0     NULL         NULL      0
phone       char    10     NULL NULL  0     NULL         NULL      0
email       varchar 30     NULL NULL  0     NULL         NULL      0

index_name index_description                    index_keys index_max_rows_per_page 
---------- ------------------------------------ ---------- ----------------------- 
PK_USR     clustered, unique located on default loginName  0
Server message 0, Severity 10, State 1, Line 170
Server message 0, Severity 10, State 1, Line 67

(return status = 0)

name                defn
------------------- --------------------------------------------------------------
FK_OWNS_BY_USR      Owns FOREIGN KEY (loginName) REFERENCES Usr(loginName)
FK_LIVES_BY_USR     Lives FOREIGN KEY (loginName) REFERENCES Usr(loginName)
FK_STUDENT_ISA_USR  Student FOREIGN KEY (loginName) REFERENCES Usr(loginName)  
FK_OTHERTRIP_OF_USR OtherTrip FOREIGN KEY (loginName) REFERENCES Usr(loginName)
FK_FACSTA_ISA_USR   FacultyStaff FOREIGN KEY (loginName) REFERENCES Usr(loginName)
FK_OPTINFO_OF_USR   OptionalInfo FOREIGN KEY (loginName) REFERENCES Usr(loginName)
FK_DAILYCOMM_BY_USR DailyCommute FOREIGN KEY (loginName) REFERENCES Usr(loginName)
PK_USR              PRIMARY KEY INDEX (loginName) : CLUSTERED, FOREIGN REFERENCE

(return status = 0)

Name                           Owner                          Type
------------------------------ ------------------------------ ----------
Student                        dbo                            user table

Data_located_on_segment        When_created                  
------------------------------ ----------------------------- 
default                        Mar 18 2002  8:55:17:806PM    

Column_name Type    Length Prec Scale Nulls Default_name Rule_name Identity 
----------- ------- ------ ---- ----- ----- ------------ --------- -------- 
loginName   varchar 10     NULL NULL  0     NULL         NULL      0        
major       varchar 30     NULL NULL  0     NULL         NULL      0        
status      char    10     NULL NULL  0     NULL         NULL      0        

index_name index_description                    index_keys index_max_rows_per_page 
---------- ------------------------------------ ---------- ----------------------- 
PK_STUDENT clustered, unique located on default loginName  0
Server message 0, Severity 10, State 1, Line 170
Server message 0, Severity 10, State 1, Line 67

(return status = 0)

name               defn
------------------ ----------------------------------------------------------------------------------------------------
CK_STATUS_STUDENT  CONSTRAINT CK_STATUS_STUDENT CHECK (status in ('freshman','sophomore','junior','senior','graduate'))
FK_STUDENT_ISA_USR Student FOREIGN KEY (loginName) REFERENCES Usr(loginName)
PK_STUDENT         PRIMARY KEY INDEX (loginName) : CLUSTERED

(return status = 0)

Name                           Owner                          Type
------------------------------ ------------------------------ ----------
FacultyStaff                   dbo                            user table

Data_located_on_segment        When_created                  
------------------------------ ----------------------------- 
default                        Mar 18 2002  8:55:17:806PM    

Column_name Type    Length Prec Scale Nulls Default_name Rule_name Identity 
----------- ------- ------ ---- ----- ----- ------------ --------- -------- 
loginName   varchar 10     NULL NULL  0     NULL         NULL      0        
department  varchar 30     NULL NULL  0     NULL         NULL      0        
position    varchar 30     NULL NULL  0     NULL         NULL      0        

index_name index_description                   index_keys index_max_rows_per_page 
---------- ----------------------------------- ---------- ----------------------- 
PK_FACSTA clustered, unique located on default loginName  0
Server message 0, Severity 10, State 1, Line 170
Server message 0, Severity 10, State 1, Line 67

(return status = 0)

name              defn
----------------- --------------------------------------------------------------
FK_FACSTA_ISA_USR FacultyStaff FOREIGN KEY (loginName) REFERENCES Usr(loginName)
PK_FACSTA        PRIMARY KEY INDEX (loginName) : CLUSTERED 

(return status = 0)

Name                           Owner                          Type
------------------------------ ------------------------------ ----------
Lives                          dbo                            user table

Data_located_on_segment        When_created                  
------------------------------ ----------------------------- 
default                        Mar 18 2002  8:55:17:806PM    

Column_name Type    Length Prec Scale Nulls Default_name Rule_name Identity 
----------- ------- ------ ---- ----- ----- ------------ --------- -------- 
loginName   varchar 10     NULL NULL  0     NULL         NULL      0
addrId      int     4      NULL NULL  0     NULL         NULL      0

index_name index_description                    index_keys index_max_rows_per_page 
---------- ------------------------------------ ---------- -----------------------
PK_LIVES   clustered, unique located on default loginName  0
Server message 0, Severity 10, State 1, Line 170
Server message 0, Severity 10, State 1, Line 67

(return status = 0)

name             defn
---------------- -------------------------------------------------------
FK_LIVES_AT_ADDR Lives FOREIGN KEY (addrId) REFERENCES Address(addrId)  
FK_LIVES_BY_USR  Lives FOREIGN KEY (loginName) REFERENCES Usr(loginName)
PK_LIVES         PRIMARY KEY INDEX (loginName) : CLUSTERED

(return status = 0)

Name                           Owner                          Type
------------------------------ ------------------------------ ----------
Address                        dbo                            user table

Data_located_on_segment        When_created                  
------------------------------ ----------------------------- 
default                        Mar 18 2002  8:55:17:806PM    

Column_name Type Length Prec Scale Nulls Default_name Rule_name Identity 
----------- ---- ------ ---- ----- ----- ------------ --------- -------- 
addrId      int  4      NULL NULL  0     NULL         NULL      0        

index_name index_description                    index_keys index_max_rows_per_page 
---------- ------------------------------------ ---------- ----------------------- 
PK_ADDRESS clustered, unique located on default addrId     0
Server message 0, Severity 10, State 1, Line 170
Server message 0, Severity 10, State 1, Line 67

(return status = 0)

name                   defn
---------------------- -------------------------------------------------------------
FK_LIVES_AT_ADDR       Lives FOREIGN KEY (addrId) REFERENCES Address(addrId)
FK_OTHERTRIP_TO_ADDR   OtherTrip FOREIGN KEY (toAddr) REFERENCES Address(addrId)
FK_CAMPUSADDR_ISA_ADDR CampusAddr FOREIGN KEY (addrId) REFERENCES Address(addrId)
FK_OTHERTRIP_FROM_ADDR OtherTrip FOREIGN KEY (fromAddr) REFERENCES Address(addrId)
FK_RESIDADDR_ISA_ADDR  ResidenceAddr FOREIGN KEY (addrId) REFERENCES Address(addrId)
PK_ADDRESS             PRIMARY KEY INDEX (addrId) : CLUSTERED, FOREIGN REFERENCE

(return status = 0)

Name                           Owner                          Type
------------------------------ ------------------------------ ----------
CampusAddr                     dbo                            user table

Data_located_on_segment        When_created                  
------------------------------ ----------------------------- 
default                        Mar 18 2002  8:55:17:806PM    

Column_name Type    Length Prec Scale Nulls Default_name Rule_name Identity 
----------- ------- ------ ---- ----- ----- ------------ --------- -------- 
addrId      int     4      NULL NULL  0     NULL         NULL      0        
street      varchar 50     NULL NULL  0     NULL         NULL      0        
roomNo      varchar 10     NULL NULL  0     NULL         NULL      0        

index_name    index_description                    ndex_keys index_max_rows_per_page 
------------- ------------------------------------ --------- ----------------------- 
PK_CAMPUSADDR clustered, unique located on default addrId    0
Server message 0, Severity 10, State 1, Line 170
Server message 0, Severity 10, State 1, Line 67

(return status = 0)

name                           defn
------------------------------ ----------------------------------------------------------
FK_CAMPUSADDR_ISA_ADDR         CampusAddr FOREIGN KEY (addrId) REFERENCES Address(addrId)
PK_CAMPUSADDR                  PRIMARY KEY INDEX (addrId) : CLUSTERED

(return status = 0)

Name          Owner Type
------------- ----- ----------
ResidenceAddr dbo   user table

Data_located_on_segment        When_created                  
------------------------------ ----------------------------- 
default                        Mar 18 2002  8:55:17:806PM    

Column_name         Type    Length Prec Scale Nulls Default_name               Rule_name Identity 
------------------- ------- ------ ---- ----- ----- -------------------------- --------- -------- 
addrId              int     4      NULL NULL  0     NULL                       NULL      0
street              varchar 50     NULL NULL  0     NULL                       NULL      0
town                varchar 30     NULL NULL  0     NULL                       NULL      0
state               char    2      NULL NULL  0     ResidenceA_state_498100815 NULL      0
zipcode             char    5      NULL NULL  0     NULL                       NULL      0
nearestIntersection varchar 50     NULL NULL  0     NULL                       NULL      0

index_name   index_description                    index_keys index_max_rows_per_page 
------------ ------------------------------------ ---------- ----------------------- 
PK_RESIDADDR clustered, unique located on default addrId     0
Server message 0, Severity 10, State 1, Line 170
Server message 0, Severity 10, State 1, Line 67

(return status = 0)

name                       defn
-------------------------- -------------------------------------------------------------
ResidenceA_state_498100815 DEFAULT 'NY'
FK_RESIDADDR_ISA_ADDR      ResidenceAddr FOREIGN KEY (addrId) REFERENCES Address(addrId)
PK_RESIDADDR               PRIMARY KEY INDEX (addrId) : CLUSTERED
(return status = 0)

Name         Owner Type
------------ ----- ----------
OptionalInfo dbo   user table

Data_located_on_segment        When_created                  
------------------------------ ----------------------------- 
default                        Mar 18 2002  9:30:16:030PM    

Column_name             Type     Length Prec Scale Nulls Default_name Rule_name Identity 
----------------------- -------- ------ ---- ----- ----- ------------ --------- -------- 
loginName               varchar  10     NULL NULL  0     NULL         NULL      0        
birthYear               int      4      NULL NULL  1     NULL         NULL      0        
sex                     char     1      NULL NULL  1     NULL         NULL      0        
smokerOrNot             char     1      NULL NULL  1     NULL         NULL      0        
preferDriveOrRide       varchar  15     NULL NULL  1     NULL         NULL      0        
commuteCostCompensation int      4      NULL NULL  1     NULL         NULL      0        
currentCommuteMethod    varchar  20     NULL NULL  1     NULL         NULL      0        

index_name index_description                    index_keys index_max_rows_per_page 
---------- ------------------------------------ ---------- ----------------------- 
PK_OPTINFO clustered, unique located on default loginName  0
Server message 0, Severity 10, State 1, Line 170
Server message 0, Severity 10, State 1, Line 67

(return status = 0)

name                        defn
--------------------------- ----------------------------------------------------------------------------
CK_SEX_OPTINFO              CONSTRAINT CK_SEX_OPTINFO CHECK (sex in ('M','F'))
CK_BIRTHYEAR                CONSTRAINT CK_BIRTHYEAR CHECK (birthYear between 1000 and 9999)
CK_SMOKERORNOT_OPTINFO      CONSTRAINT CK_SMOKERORNOT_OPTINFO CHECK (smokerOrNot in ('Y','N'))
CK_PREFDRIVEORRIDE_OPTINFO  CONSTRAINT CK_PREFDRIVEORRIDE_OPTINFO CHECK (preferDriveOrRide in ('drive only','share driving','ride only'))
FK_OPTINFO_OF_USR           OptionalInfo FOREIGN KEY (loginName) REFERENCES Usr(loginName)
PK_OPTINFO                  PRIMARY KEY INDEX (loginName) : CLUSTERED

(return status = 0)

Name Owner Type
---- ----- ----------
Owns dbo   user table

Data_located_on_segment        When_created                  
------------------------------ ----------------------------- 
default                        Mar 18 2002  8:55:17:806PM    

Column_name Type    Length Prec Scale Nulls Default_name Rule_name Identity
----------- ------- ------ ---- ----- ----- ------------ --------- --------
loginName   varchar 10     NULL NULL  0     NULL         NULL      0       
carId       int     4      NULL NULL  0     NULL         NULL      0       

index_name index_description                     index_keys  index_max_rows_per_page 
---------- ------------------------------------- ----------- ----------------------- 
PK_OWNS    clustered, unique located on default  loginName  0
Server message 0, Severity 10, State 1, Line 170
Server message 0, Severity 10, State 1, Line 67

(return status = 0)

name            defn                                                         
--------------- ------------------------------------------------------
FK_OWNS_BY_USR  Owns FOREIGN KEY (loginName) REFERENCES Usr(loginName)       
FK_OWNS_OF_CAR  Owns FOREIGN KEY (carId) REFERENCES Car(carId)
PK_OWNS         PRIMARY KEY INDEX (loginName, carId) : CLUSTERED

(return status = 0)

Name Owner Type
---- ----- ----------
Car  dbo   user table

Data_located_on_segment        When_created                  
------------------------------ ----------------------------- 
default                        Mar 18 2002  8:55:17:806PM    

Column_name Type    Length Prec Scale Nulls Default_name Rule_name Identity 
----------- ------- ------ ---- ----- ----- ------------ --------- -------- 
carId       int     4      NULL NULL  0     NULL         NULL      0        
manufacture varchar 30     NULL NULL  0     NULL         NULL      0        
model       varchar 20     NULL NULL  0     NULL         NULL      0        
year        int     4      NULL NULL  0     NULL         NULL      0        

index_name     index_description                       index_keys               index_max_rows_per_page 
-------------- --------------------------------------- ------------------------ ----------------------- 
PK_CAR         clustered, unique located on default    carId                    0                       
AK_CARINFO_CAR nonclustered, unique located on default manufacture, model, year 0                       
Server message 0, Severity 10, State 1, Line 170
Server message 0, Severity 10, State 1, Line 67

(return status = 0)

name           defn
-------------- ---------------------------------------------------------
CK_CAR_YEAR    CONSTRAINT CK_CAR_YEAR CHECK (year between 1000 and 9999)
FK_OWNS_OF_CAR Owns FOREIGN KEY (carId) REFERENCES Car(carId)
AK_CARINFO_CAR UNIQUE INDEX (manufacture, model, year) : NONCLUSTERED
PK_CAR         PRIMARY KEY INDEX (carId) : CLUSTERED, FOREIGN REFERENCE

(return status = 0)

Name         Owner Type
------------ ----- ----------
DailyCommute dbo   user table

Data_located_on_segment        When_created                  
------------------------------ ----------------------------- 
default                        Mar 18 2002  8:55:17:806PM    

Column_name             Type     Length Prec Scale Nulls Default_name                Rule_name Identity
----------------------- -------- ------ ---- ----- ----- --------------------------- --------- --------
loginName               varchar  10     NULL NULL  0     NULL                        NULL      0        
startTime               datetime 8      NULL NULL  0     NULL                        NULL      0        
endTime                 datetime 8      NULL NULL  0     NULL                        NULL      0        
maxMinutesEarlyArrival  int      4      NULL NULL  1     DailyCommu_maxMin_642101328 NULL      0        
maxMinutesLateDeparture int      4      NULL NULL  1     DailyCommu_maxMin_658101385 NULL      0        
year                    int      4      NULL NULL  0     NULL                        NULL      0        
season                  char     10     NULL NULL  0     NULL                        NULL      0        

index_name      index_description                    index_keys index_max_rows_per_page 
--------------- ------------------------------------ ---------- ----------------------- 
PK_DAILYCOMMUTE clustered, unique located on default loginName  0
Server message 0, Severity 10, State 1, Line 170
Server message 0, Severity 10, State 1, Line 67

(return status = 0)

name                        defn
--------------------------- ---------------------------------------------------------------------------
CK_TIMES_DAILYCOMM          CONSTRAINT CK_TIMES_DAILYCOMM CHECK (startTime<endTime)
CK_DAILYCOMM_YEAR           CONSTRAINT CK_DAILYCOMM_YEAR CHECK (year between 1000 and 9999)
CK_SEASON_DAILYCOMM         CONSTRAINT CK_SEASON_DAILYCOMM CHECK (season in ('Fall','Spring','Summer'))
DailyCommu_maxMin_642101328 DEFAULT 0
DailyCommu_maxMin_658101385 DEFAULT 0
FK_DAILYCOMM_BY_USR         DailyCommute FOREIGN KEY (loginName) REFERENCES Usr(loginName)
PK_DAILYCOMMUTE             PRIMARY KEY INDEX (loginName) : CLUSTERED

(return status = 0)

Name      Owner Type
--------- ----- ----------
OtherTrip dbo   user table

Data_located_on_segment        When_created                  
------------------------------ ----------------------------- 
default                        Mar 18 2002  8:55:17:806PM    

Column_name              Type     Length Prec Scale Nulls Default_name               Rule_name Identity
------------------------ -------- ------ ---- ----- ----- -------------------------- --------- --------
tripId                   int      4      NULL NULL  0     NULL                       NULL      0
date                     datetime 8      NULL NULL  0     NULL                       NULL      0
reason                   varchar  99     NULL NULL  1     NULL                       NULL      0
fromAddr                 int      4      NULL NULL  0     NULL                       NULL      0
toAddr                   int      4      NULL NULL  0     NULL                       NULL      0
departureTime            datetime 8      NULL NULL  0     NULL                       NULL      0
arrivalTime              datetime 8      NULL NULL  0     NULL                       NULL      0
maxMinutesEarlyDeparture int      4      NULL NULL  1     OtherTrip_maxMin_738101670 NULL      0
maxMinutesEarlyArrival   int      4      NULL NULL  1     OtherTrip_maxMin_754101727 NULL      0
maxNumberOfPassengers    int      4      NULL NULL  1     OtherTrip_maxNum_786101841 NULL      0
numberOfPassengers       int      4      NULL NULL  1     OtherTrip_number_770101784 NULL      0
costCompensation         int      4      NULL NULL  1     NULL                       NULL      0
loginName                varchar  10     NULL NULL  0     NULL                       NULL      0

index_name   index_description                    index_keys index_max_rows_per_page
------------ ------------------------------------ ---------- -----------------------
PK_OTHERTRIP clustered, unique located on default tripId     0
Server message 0, Severity 10, State 1, Line 170
Server message 0, Severity 10, State 1, Line 67

(return status = 0)

name                       defn
-------------------------- ----------------------------------------------------------- 
OtherTrip_maxMin_738101670 DEFAULT 0
OtherTrip_maxMin_754101727 DEFAULT 0
OtherTrip_maxNum_786101841 DEFAULT 1
OtherTrip_number_770101784 DEFAULT 1
FK_OTHERTRIP_TO_ADDR       OtherTrip FOREIGN KEY (toAddr) REFERENCES Address(addrId)    
FK_OTHERTRIP_OF_USR        OtherTrip FOREIGN KEY (loginName) REFERENCES Usr(loginName)
FK_OTHERTRIP_FROM_ADDR     OtherTrip FOREIGN KEY (fromAddr) REFERENCES Address(addrId)  
PK_OTHERTRIP               PRIMARY KEY INDEX (tripId) : CLUSTERED

(return status = 0)

/* ======================================================================
/* Results of querying table content (some white spaces are removed)
/* ======================================================================

/* Usr
loginName password lastName firstName phone      email       
--------- -------- -------- --------- ---------- --------------
user01    01       Vincent  Jimmy     1234567890 jm@yahoo.com
user02    02       Cage     Nickle    0987654321 cn@hotmail.com
user03    03       Jordan   Michael   1112223333 mj@yahoo.com
user04    04       Gates    Bill      2223334444 gb@hotmail.com

/* Student
loginName  major               status
---------- ------------------- --------
user01     Computer Science    junior
user02     Physics             graduate
			     
/* FacultyStaff		     
loginName  department          position 
---------- ------------------- ---------
user03     Computer Science    professor
user04     Physics             professor

/* Lives
loginName addrId     
--------- ------
user01    1          
user02    1          
user03    3          
user04    4          

/* Address
addrId     
---------- 
1          
2          
3          
4          
5          

/* CampusAddr
addrId street  roomNo     
------ ------- ------
1      Steeley A101       
2      Steeley A102       

/* ResidenceAddr
addrId street           town        state zipcode nearestIntersection
------ ---------------- ----------- ----- ------- -------------------
3      Stony Brook Road Stony Brook NY    11790   Centerreach
4      Jerusalem Avenue Sea Ford    NY    11794   Wantagh
5      South Station    Boston      MA    01800   South Station

/* OptionalInfo
loginName birthYear sex smokerOrNot preferDriveOrRide commuteCostCompensation currentCommuteMethod
--------- --------- --- ----------- ----------------- ----------------------- --------------------
user02    1976      M   N           ride only         10                      bus
user04    1964      F   N           drive only        10                      drive alone

/* Owns
loginName carId
--------- -----
user03    1
user04    2

/* Car
carId manufacture model  year
----- ----------- ------ ----
1     Honda       Civic  2000       
2     Ford        Taurus 2001       

/* DailyCommute 
/* Wenxin found that in Sybase datatime type, year can not go below 1753
/* so we use that as a dummy year

loginName startTime                endTime                  maxMinutesEarlyArrival maxMinutesLateDeparture year season     
--------- ------------------------ ------------------------ ---------------------- ----------------------- ---- ------ 
user03    Jan 1 1800 8:00:00:000AM Jan 1 1800 6:00:00:000PM 10                     10                      2002 Spring     
user04    Jan 1 1800 9:00:00:000AM Jan 1 1800 5:00:00:000PM 10                     10                      2002 Fall       

/* OtherTrip
tripId  date                       reason          fromAddr toAddr departureTime             arrivalTime                maxMinutesEarlyDeparture maxMinutesEarlyArrival maxNumberOfPassengers numberOfPassengers costCompensation loginName  
------- -------------------------- --------------- -------- ------ ------------------------- -------------------------- ------------------------ ---------------------- --------------------- ------------------ ---------------- ---------- 
1       Mar 23 2002 12:00:00:000AM visiting friend 1        5      Mar 23 2002 8:00:00:000AM Mar 23 2002 12:00:00:000PM 10                       30                     0                     2                  30               user01
2       Mar  2 2002 12:00:00:000AM tennis game     3        5      Mar  2 2002 9:00:00:000AM Mar  2 2002  3:00:00:000PM 10                       60                     2                     1                  50               user03