/* $CASE/06f522/lechner/asgnt3Rev061211.sch - RJL 061212 */ /* was: $PH/06f522/asgnt3/lechner/metatest3/metaCKplusSISrev061014.sch */ /* THis file appends SISrev061014.sch to metaschemaCK.sch from ../metatest2*/ /* $PH/06f522/asgnt3/lechner/metatest2/metaschemaCK.sch /* Adds CK and CA to $CASE/gen/ver_13/chgen/test/metaschema/metaschema.sch - RJL060903 */ /* Added CQ table to store query fvalue list for regression testing - NS */ SchemaVersion SV /* sv_type - schema version, data, path, applications? */ { SVid SV000000 c8 1 /* primary key field */ PVid PJ000000 c8 0 /* Project Ref - non-key field for now */ svname metaPlusCKeys t30 0 /* schema name [and version?] */ sformat TBD c6 0 /* schema format: CHGEN or GENDB[?] */ sversion 1.0 t12 0 /* schema tag or version (rev# i.j...? */ lastMod 061002 i4 0 /* date of last mod: yymmdd */ svpath $PH/06f522/asgnt3 c80 0 /* full path prefix or $DEFINED-SYMBOL */ descr asgnt3for06f522 t80 0 /* e.g., list of schema applications? */ } TableType TT /* tt_type - table description and child-set of attributes*/ { TTid TT000000 c8 1 /* primary key field */ SVid SV000000 c8 1 /* SVid is foreign key */ ttabb ?? c5 0 /* 2 or 4 UCLetters */ ttname ???? t30 0 /* table name */ descr ?????? t80 0 /* descriptive comment (table purpose) */ } TableAttribute TA /* ta_type - scalar or ref attribute of parent table */ { TAid TA000000 c8 1 /* primary key field */ TTid TT000000 c8 1 /* TTid is foreign key */ fname ?? c30 0 /* field name (column header) */ dfltVal ???? t30 0 /* default value (was altName) */ ftype i4 c10 0 /* field type (i4/f4/c##,t###/k8/k12) */ iskey 0 c4 0 /* 0:nonkey; 1/-1:pfkey; s:superClassRef */ comment // t132 0 /* descriptive comment (units/range?) */ } ViewVersion VV /* vv_type - subset of tables in this view */ { VVid VV000000 c8 1 /* primary key field */ SVid SV000000 c8 1 /* SV foreign key field */ vname schema t30 0 /* view name (schema = all tables) */ mode r c2 0 /* mode (r/w/u) */ num_tables 0 i2 0 /* number of tables in View */ } TableStatistics TS /* ts_type - statistics about this Table in View */ { TSid TS000000 c8 1 /* primary key field */ VVid VV000000 c8 1 /* fkey to ViewVersion for this TSrow */ TTid TT000000 c8 1 /* fkey to TTrow for this TSrow */ verno 0 i2 0 /* version number of TT in VV */ maxrow 0 i2 0 /* max row# in this table and VV */ vvrowcnt 0 i2 0 /* row-count of this TT in parent VV only*/ } CandidateKey CK /* list of candidate pkeys (has a child_list of CA rows) */ { CKid CK000000 c8 1 /* primary key field */ TTid TT000000 c8 1 /* fkey to TTrow for this CKrow */ CKname ?? c32 0 /* name of candidate key (default=CKid)*/ CArcount 0 i2 0 /* number of CA-children = CK-components */ } CandidateAttribute CA /* binary association of CK with TA */ { CAid CA000000 c8 1 /* primary key field */ TAid TA000000 c8 1 /* fkey to TArow for this CArow */ CKid CK000000 c8 1 /* fkey to CKrow for this CArow */ CAorder 0 i2 0 /* List order (0...) of this CK-component */ } CandidateQueries CQ /* Stored queries table */ { CQid CQid c8 1 /* Query ID Primary Key */ cqname cqname t30 0 /* A meaningful name */ CKname ?? c32 0 /* name of candidate key for this query */ values values t80 0 /* List of ckey fvalues - check if unique*/ } /*****************************************************************/ /* SISrev061011.sch in $CASE/02f522/SISProj/rjl_test - RJL061011 */ /* adapted from $CASE/02f522/SISProj/rjl_test/SIS.sch - RJL061011 */ /* (CourseTermSection was StudTermSe=ST, ENrollment=EN was ET, CS-->EN was CT-->EN)) */ /* added fields: CO.CourseNo, DE.deptNo, CS.sectionNo, AA.studentId */ /* topologically sorted into 5 levels */ Department DE /* Department Table */ { DEid NodeID c8 1 /* dEPARtment ID Primary Key */ deptNo DeptNumber i2 0 /* Uinversity-aqsssigned Number(e.g. 91) */ DEhead DeptHeadName c30 0 /* Name of Dept Head ()*/ DEname DeptName t60 0 /* Department Name (e.g. CompuerScience */ } Student AA /* Student Attribute Table */ { AAid NodeID c8 1 /* Student ID Primary Key */ DEid DeptNumber c8 1 /* Created via linked parent */ AAgpa AAgpa f4 0 /* Cumulative GPA, was f5 */ studentId studentIdNumber i4 0 /* assigned by University */ AAsnm AAstudname c32 0 /* Student Name () */ } Term TM /* Term or Semester Table */ { TMid semesterId c8 1 /* Semester ID Primary Key */ TMname TMName c15 0 /* Sem. Name, e.g. 06f */ } Faculty FY /* Faculty Table */ { FYid facultyId c8 1 /* Faculty ID Primary Key */ DEid DeptId c8 1 /*Created via linked parent*/ FYnm facyName c32 0 /* Faculty Name () */ } Course CO /* Course Table, independent of Term */ { COid NodeID c8 1 /* Course ID Primary Key */ DEid DeptId c8 1 /*Created via linked parent*/ courseNo courseNo i2 0 /* cousre number (Dept.-assigned) */ COtitle COtitle t24 0 /* Course Title */ } Address AD /* Student Addresse Table, was AA-AD */ { ADid NodeID c8 1 /* Address ID Primary Key */ AAid StudentID c8 1 /*Created via linked parent*/ ADcity ADcity c13 0 /* City */ ADstat ADstate c2 0 /* State */ ADzip ADzip c9 0 /* Zip Code */ ADctry ADcountry c2 0 /* Country */ ADstree ADstreet t60 0 /* StreetAddress (blanks OK; was c30 )*/ } StudentTerm RT /* Student- Term Table */ { RTid RegisteredTermId c8 1 /* Student-term ID Primary Key*/ AAid StudentId c8 1 /*Created via linked parent */ TMid TermId c8 1 /*Created via linked parent */ RTattem RTattempted i2 0 /* credits attempted*/ RTearne RTearned i2 0 /* credits earned */ RTgpa RTCumGPA f4 0 /* Current GPA */ } CourseTermSection CT /* Course-Term-Section Table*/ { CTid CTid c8 1 /* StudTermSe ID Primary Key*/ TMid TermID c8 1 /*Created via linked parent */ FYid FacultyID c8 1 /*Created via linked parent */ COid CourseID c8 1 /*Created via linked parent*/ CTmax CTmax i4 0 /* Maximum Enrollment */ CTmin CTmin i4 0 /* Minimum Enrollment */ CTcur CTcurrent i4 0 /* Current Enrollment */ CTstatu CTstatus c1 0 /* section status */ sectionNo sectionNo i2 0 /* section number(univ.-assigned)*/ } PreReqLinK PQ /* Prerequisite Table */ { PQid PQid c8 1 /* PreReqlink ID Primary Key*/ COid1 CourseID c8 1 /*Created via linked parent*/ COid2 PQcourseID c8 1 /* pre-requisite course ID */ } CourseSchedule CS /* CourseScheduleTerm, was STSD*/ { CSid CSid c8 1 /*Course- Sched ID Primary Key*/ CTid CTid c8 1 /* Created via linked parent */ SDdays SDdays c3 0 /* Lecture Day like MWF */ SDtime SDtime c6 0 /* Lecture Time like 430PM */ SDbldg SDbldg c3 0 /* Building */ SDroom SDroom c3 0 /* Room */ } Enrollment EN /* Enrollment Table (was EN, was RTSP, was before CS) */ { ENid ENid c8 1 /* Enrollment ID Primary Key*/ RTid StudentTermId c8 1 /*Created via linked parent */ CSid CourseTermSectionId c8 1 /*Created via linked parent */ SPcred SPcredits i4 0 /* Credits */ SPgrad SPgrade c3 0 /* Grade*/ sectionNo sectionNo i2 0 /* sectionNumber (univ.-assigned)*/ }