CREATE TABLE dbo



TABLE: Grad_Applicant

This table holds the information about the applicant. Name, address, phone number, date of birth etc. The nationality is used to determine the insternational student status. Residency determines whether the student is the resident of Ohio. The application date helps to determine whether the student is eligible to get the Graduate student assistance ship, which is offered at the Winter quarter. Accept date/ Reject date helps to create the Applicant history. REQ_SUPPORT is the field to see if the student applied for the supports like TA, RA or GAA. EXP_DEG is the expected degree (MS or PhD). The table also has the fields to note down whether the GAC (Graduate Associate Committee), Chair Person, FO, GrSch (Graduate School), Hrs ( Human resources ), and Traffic and Parking notified if the applicant is accepted.

CREATE TABLE Grad_Applicant

(

APP_ID numeric(18,0) NOT NULL,

LNAME varchar(16) NULL,

FNAME varchar(12) NULL,

MNAME varchar(12) NULL,

ADDRESS varchar(20) NULL,

CITY varchar(13) NULL,

STATE char(2) NULL,

ZIP char(5) NULL,

PHONE_NUMBER char(10) NULL,

DATE_OF_BIRTH datetime NULL,

EMAIL_ADDRESS varchar(255) NULL,

MARITAL_STATUS varchar(12) NULL,

SEX char(1) NULL,

NATIONALITY varchar(35) NULL,

ETHNICTY varchar(15) NULL,

RESIDENCY char(1) NULL,

APP_DATE datetime NULL,

ACCPT_DATE datetime NULL,

REJ_DATE datetime NULL,

RQD_SUPPORT char(1) NULL,

SUPPORT_TYPE varchar(20) NULL,

EXP_DEG varchar(5) NULL,

QTR_APPL varchar(5) NULL,

PREV_EDU varchar(255) NULL,

TEST_INFO varchar(100) NULL,

GAC_NTFY varchar(1) NULL,

CHAIR_NTFY varchar(1) NULL,

FO_NTFY varchar(1) NULL,

HRS_NTFY varchar(1) NULL,

GrSCH_NTFY varchar(1) NULL,

PRIMARY KEY (APP_ID)

)

TABLE: Grad_AppHistory

This table is to keep all the applicants information for previous quarters. All the attributes are the same as those in the Grad_Applicant table.

CREATE TABLE Grad_AppHistory

(

APP_ID numeric(18,0) NOT NULL,

LNAME varchar(16) NULL,

FNAME varchar(12) NULL,

MNAME varchar(12) NULL,

ADDRESS varchar(20) NULL,

CITY varchar(13) NULL,

STATE char(2) NULL,

ZIP char(5) NULL,

PHONE_NUMBER char(10) NULL,

DATE_OF_BIRTH datetime NULL,

EMAIL_ADDRESS varchar(255) NULL,

MARITAL_STATUS varchar(12) NULL,

SEX char(1) NULL,

NATIONALITY varchar(35) NULL,

ETHNICTY varchar(15) NULL,

RESIDENCY char(1) NULL,

APP_DATE datetime NULL,

ACCPT_DATE datetime NULL,

REJ_DATE datetime NULL,

RQD_SUPPORT char(1) NULL,

SUPPORT_TYPE varchar(20) NULL,

EXP_DEG varchar(5) NULL,

QTR_APPL varchar(5) NULL,

PREV_EDU varchar(255) NULL,

TEST_INFO varchar(100) NULL,

GAC_NTFY varchar(1) NULL,

CHAIR_NTFY varchar(1) NULL,

FO_NTFY varchar(1) NULL,

HRS_NTFY varchar(1) NULL,

GrSCH_NTFY varchar(1) NULL,

PRIMARY KEY (APP_ID)

)

TABLE: TA_Skills

When students apply for TA position, they have to provide the skills they have, such as languages, so that the TAC can decide which course they can be assigned to. The primary key is the combination of CIS_ID and SKILL. The following is the description for each attribute:

1. CIS_ID: The ID assigned to each person by the department

2. SKILL: One of the skills that the student has.

CREATE TABLE TA_Skills

(

CIS_ID numeric(18,0) NOT NULL,

SKILL varchar(255) NULL,

PRIMARY KEY (CIS_ID, SKILL)

)

TABLE:Course_Skill

This table is created for TAC which keeps track of the skills that are needed in each CIS course so that he/she can decide which student need to be assigned as a TA. The attributes are:

1. Course_ID: The ID that is assigned to each course.

2. CourseSkill: One of the skills needed for the course.

CREATE TABLE Course_Skill

(

Course_ID numceric(18,0) NOT NULL,

CourseSkill varchar(255) NOT NULL,

PRIMARY KEY (Course_ID, CourseSkill)

)

go

TABLE: TA_COURSE

This table is created to keep track of the courses that each TA teaches this quarter. The attributes are:

1. CIS_ID: The ID that is assigned to each course.

2. HIREDATE: The beginning date of the quarter.

3. TERMINATEDATE: The end date of the quarter.

4. Course_ID: The ID that is assigned to each course

CREATE TABLE TA_COURSE

(

CIS_ID numeric(18,0) NOT NULL,

HIREDATE datetime NOT NULL,

TERMINATEDATE datetime NOT NULL,

Course_ID numeric(18,0) NOT NULL,

PRIMARY KEY (CIS_ID, HIREDATE, TERMINATEDDATE, Course_ID)

)

go

TABLE:TA_COURHISTORY

This table keeps track of TA_COURSE history. All attributes are the same as TA_COURSE.

CREATE TABLE TA_COURHISTORY

(

CIS_ID numeric(18,0) NOT NULL,

HIREDATE datetime NOT NULL,

TERMINATEDATE datetime NOT NULL,

Course_ID numeric(18,0) NOT NULL,

PRIMARY KEY (CIS_ID, HIREDATE, TERMINATEDDATE, Course_ID)

)

go

TABLE: Avail_Support

This table is created to keep the information for each support type. The attributes are:

1. JOBCODE: The distinct number assigned to different type of student positions, such as TA, RA, etc.

2. DESCRIPTION: Other rules and restrictions on funding apply, such as time limits or progress.

3. SUMMER_SUPPORT: Whehter such funding has summer support.

CREATE TABLE Avail_Support

(

JOBCODE char(6) NOTNULL,

DESCRIPTION varchar(255) NOTNULL,

SUMMER_SUPPORT char(1) NULL

)

go

TABLE: GradPeople

We consider graduate student as a subclass of the people table and we want to add more information about the graduate student in the department, which is the main reason for this table. The description of the attributes are the following :

3. CIS_ID: The ID assigned to each person by the department

4. SPOENGTEST_DATE: The date of the Spoken English Test taken by a student

5. SPOENGTEST_SCORE: The score of the Spoken English Test

6. STARTING_DATE: The date that a student started the graduate program

7. SUPERVISOR_ID: The CIS_ID of the supervisor of a graduate student

8. COMPS_DATE: The date of the COMPS test taken by a student

9. QUALS_DATE: The date of the COMPS test taken by a student

10. CANTEST_DATE: The date of the Ph.D Candidacy test taken by a student.

CREATE TABLE GradPeople

(

CIS_ID numeric(18,0) NOT NULL,

SPOENGTEST_DATE datetime NULL,

SPOENGTEST_SCORE real NULL,

STARTING_DATE datetime NULL,

SUPERVISOR_ID numeric(18,0) NULL,

COMPS_DATE datetime NULL,

QUALS_DATE datetime NULL,

CANTEST_DATE datetime NULL,

PRIMARY KEY (CIS_ID)

)

go

TABLE : GradPeople

Some of the information for a student will be revised each quarter, which will be recorded in this table. The primary key for this table is CIS_ID, QUARTER and YEAR. The following is attributes information:

1. CIS_ID: The ID assigned to each person by the department

2. APPOINTMENT_ID: The ID assigned to employees including TA, RA, etc

3. QUARTER, YEAR: The quarter that this information is valid

4. SUPPORT: Whether the student has any scholarship or not

5. OFFI_ROOM, OFFI_BUILDING: Where is the office of the student

6. ENROLL_CREDIT: How many credits that a student is enrolled in

7. FO_NOTIFYING, HR_NOTIFYING, CF_NOTIFYING: whether Faculty Officer, Human Resource, and Computing Facility are notified for the new student who accept the offer but fail to enroll in classes.

CREATE TABLE GradAppointment

(

CIS_ID numeric(18,0) NOT NULL,

QUARTER varchar(4) NOT NULL,

YEAR char(4) NOT NULL,

SUPPORT char(1) NULL,

APPOINTMENT_ID numeric(18,0) NULL,

OFFI_ROOM varchar(10) NULL,

OFFI_BUILDING varchar(10) NULL,

ENROLL_CREDIT numeric(2,0) NULL,

FO_NOTIFYING char(1) NULL,

HR_NOTIFYING char(1) NULL,

CF_NOTIFYING char(1) NULL,

PRIMARY KEY (CIS_ID, QUARTER, YEAR)

)

go

The following tables are already created in the existing database which we will use in our own:

CREATE TABLE dbo.Account

(

AID numeric(18,0) NOT NULL,

CIS_ID numeric(18,0) NOT NULL,

TYPE varchar(10) NOT NULL,

NAME varchar(20) NOT NULL,

CREATION_DATE datetime DEFAULT GETDATE() NULL,

FLAG char(1) NULL,

ATTRIBUTE varchar(255) NULL,

SPONSOR numeric(18,0) NULL,

CONSTRAINT PK_ACCOUNT

PRIMARY KEY NONCLUSTERED (CIS_ID,TYPE,NAME),

CONSTRAINT UK_ACCOUNT

UNIQUE NONCLUSTERED (TYPE,NAME)

)

go

CREATE TABLE dbo.Appointment

(

APPOINTMENT_ID numeric(18,0) NOT NULL,

CIS_ID numeric(18,0) NOT NULL,

COMP_RATE decimal(11,3) NULL,

HIRE_DATE datetime NULL,

TERM_DATE datetime NULL,

DEPT_ID char(10) NULL,

EMPL_RCD int NULL,

JOBCODE char(6) NULL,

ORG char(10) NULL,

FUND char(6) NULL,

ACCOUNT char(6) NULL,

FUNCTION char(5) NULL,

PROJECT char(15) NULL,

PROGRAM char(5) NULL,

USER_DEFINED char(6) NULL,

UPDATE_DATE datetime DEFAULT GETDATE() NOT NULL

)

go

CREATE TABLE dbo.AppointmentHistory

(

APPOINTMENT_ID numeric(18,0) NOT NULL,

CIS_ID numeric(18,0) NOT NULL,

COMP_RATE decimal(11,3) NULL,

HIRE_DATE datetime NULL,

TERM_DATE datetime NULL,

DEPT_ID char(10) NULL,

EMPL_RCD int NULL,

JOBCODE char(6) NULL,

ORG char(10) NULL,

FUND char(6) NULL,

ACCOUNT char(6) NULL,

FUNCTION char(5) NULL,

PROJECT char(15) NULL,

PROGRAM char(5) NULL,

USER_DEFINED char(6) NULL,

UPDATE_DATE datetime NOT NULL

)

go

CREATE TABLE dbo.Classification

(

SID numeric(18,0) NOT NULL,

DESCRIPTION varchar(255) NULL,

NAME varchar(50) NULL,

EXPIRES int NULL,

CREATION_DATE datetime DEFAULT GETDATE() NULL,

PID numeric(18,0) NOT NULL,

RESOURCE bit NOT NULL,

HAS_MEMBERS bit NOT NULL,

ENSURE_ACCOUNT bit NOT NULL,

ATTRIBUTE varchar(255) NULL,

METHOD varchar(255) NULL

)

go

CREATE TABLE dbo.People

(

CIS_ID numeric(18,0) NOT NULL,

BUCK_ID char(10) NULL,

SSN char(9) NOT NULL,

EMPLOYEE_ID char(8) NULL,

NAME varchar(50) NULL,

LNAME varchar(16) NULL,

FNAME varchar(12) NULL,

MNAME varchar(12) NULL,

ADDRESS varchar(20) NULL,

CITY varchar(13) NULL,

STATE char(2) NULL,

ZIP char(5) NULL,

PHONE_NUMBER char(10) NULL,

DATE_OF_BIRTH datetime NULL,

EMAIL_ADDRESS varchar(255) NULL,

OSU_DIRECTORY_ID varchar(25) NULL,

OSU_NET_ID varchar(25) NULL,

SPOUSE varchar(12) NULL,

SEX char(1) NULL,

NATIONALITY varchar(35) NULL,

ETHNICITY varchar(15) NULL,

RESIDENCY char(1) NULL,

CREATION_DATE datetime DEFAULT GETDATE() NULL,

DEACTIVATION_DATE datetime NULL,

CONSTRAINT PK_PEOPLE

PRIMARY KEY CLUSTERED (CIS_ID)

)

go

................
................

In order to avoid copyright disputes, this page is only a partial summary.

Google Online Preview   Download