SIS_Student
PK studentID NUMBER
Student NN firstname NVARCHAR2(50)
General Constraint:
Information NN lastname NVARCHAR2(50)
All phone numbers System CK,NN status NVARCHAR2(2)
should be in the format Physical Model NN status_date DATE
sis_Student_course_record
999.999.9999 where 9 status = {A,AP, S, E} CK,NN phone NCHAR(12)
is a digit CK,NN email NVARCHAR2(100)
PK,FK1 crn NUMBER
All email address PK,FK1 semester_code NCHAR(6)
should be pattern PK,FK2 studentid NUMBER
matched to ensure
FK3,NN credential# NUMBER
they are valid
FK4, NN course_code NCHAR(7)
letter_grade NVARCHAR2(2)
sis_scheduled_course
letter_grade = {A+,
sis_Student_Credential PK CRN NUMBER(5)
sis_Credential A, A-, B+, B, B-, C+,
PK.FK1 studentid NUMBER PK semester_code NCHAR(7) C, C-, D+, D, F, i}
PK credential# NUMBER
PK,FK2 credential# NUMBER FK,NN course_code NCHAR(7)
NN school_name NVARCHAR2(50)
NN startdate DATE CK,NN section_code NCHAR(1)
NN name NVARCHAR2(50)
completion_date DATE
CK, NN type NVARCHAR2(2)
NN credential_status NCHAR(1)
NN gpa NUMBER(3,2)
type = {MI, FT, CT, DP, semester_code must
AD, D} credential_status = be in the format sis_instructor_course
{A,G,E} LLLL999 where L is a PK,
crn NUMBER
letter and 9 is a digit FK1
section_code must a PK,
semester_code NCHAR(7)
single letter of the FK1
alphabet (A-Z) PK,
instructorid NUMBER
FK2
sis_courses_within_cred sis_Instructor
PK, FK1 credential# NUMBER PK instructorid NUMBER
PK, FK2 course_code NCHAR(7) NN firstname NVARCHAR2(50)
CK,NN type_flag NUMBER(1) NN lastname NVARCHAR2(50)
NN address NVARCHAR2(100)
NN city NVARCHAR2(40)
CK, NN prov NCHAR(2)
type_flag = {0, 1}
CK, NN postal_code NCHAR(6)
sis_course CK, NN phonenumber NCHAR(12)
PK,CK course_code NCHAR(7) CK, NN email NVARCHAR2(100)
NN name NVARCHAR2(100)
NN num_of_credits NUMBER(2,1) prov should be a valid 2
character province code
FK prereq_course_code NCHAR(7)
postal_code should match
the pattern L9L9L9 where
prereq
L is a letter and 9 is a digt
course_code must be
in the format LLLL999
where L is a letter and
9 is a digit
