email reply to nsonawan's group msg at
http://tech.groups.yahoo.com/group/UML_CS522_f2006_OOA/message/15

RJLRef: $PH/06f522/asgnt3/CQ_CK_CAcomments2ns.061014.txt

From RJL 061014:2255
[As you can see, I don't know how yahoo mailers will distinguish
(or not:-) between your original text and my comments below]
----
Depat[sic]rment DE
(DeptNumber)
(DeptName)
(DeptHeadName)
-------
These seem like good names for Dept CK's.
If you mean these to be schema fields 
(names in DEpt table of SISrev061011.sch) 
then parens enclose a list of field names
and U/L case is significant. Yours are not consistent
with $PH/COOL-GEN/SISrev061011.sch 
so I need to know if you-all are using it
as I plan to do in metatest3.???

Below, change all to the FIRST column name (studentId; 
schema column 2 is an unused and deprecated name TBReplaced
by an ASCII default value.
I don't like most truncated TAcurr->fnames in the SIS schema, 
but I know their motivation: At that time bde's data model
did not allow enough chars in the HA-row text field
where schema attributes were declared for bde2sch (b2t|t2s).

I only changed a few field names (and one link parent) to more 
sensible ones in $PH/COOL-GEN/SISrev061011.sch.
Each such change is incompatible with potentially reusable 
transaction code in 02f522/SISproj/*/*.sch.

--------------------------------
Student AA
(StudentIdNumber)

[Others like studentName or LName,Fname single or a pair of fields
are possible but it's not really necessary to add all CKs]

Term TM
(TMName)

Looks like you mean e.g. 06f (my pref) or Fall2006 (theirs)
so surrogate [pkeys are not included.
That may make or CK specs technically incomplete 
since pkey 'must' (RDB rules?) be one of the CKeys 
(i.e. the first or default one). Leaving it out 
may cause more trouble later that is worth.
(TBD but not now)

StudentAddress AD
(StudentID)
I agree - if an address exists then studentId (not "StudentId")
is an alternate key for table AD whose surrogate pkey is ADid.
But we don't have to check every single-field candidate key
in every table. Composite CKeys and CKeys with remote ancestral field
values are more interesting (and user-relevant) for our normalized 
data models.
E.g., as when two depts have same FYnm (not facyName):
Faculty FY
(DeptId, FacyName)
-----------------------

CourseTermSection CT

Your assumption 'A faculty member will teach one course' 
is not relevant here - In each TM, one FY can teach
multiple CT sections of one COurse. FY,TM,CO is NOT a CKey for CT
without a sequence no (sectionNo), just as MOther,FAther is not a CKey
for any child, unless you add birthday (plus handle twins etc.:-).
You could make the FY,TM,CO intersection table CT a singeton which 
owns a sequence of sections instead of using sectionNo to distinguish 
CT-row instances. (These CT-rows have different surrogate CTid pkeys,) 

CT is M::N between each pair: TM,FY; CO,FY; TM,CO. 
(3NF does not constrain this relation further like BCNF or 4NF 
or 5NF does. For some really unjustified hyperbole by Curt Monash 
against C.J.Date, which also downplays normal forms beyond 3NF, see 
http://www.dbms2.com/2005/10/29/oh-dear-chris-date-is-displeased-with-me/ 
(but read the opposition responses also).

sectionNo is an ESSENTIAL part of a CK for table CT, because
that partitions big courses into small sections and identifies
that section to registered students via the CSid in table EN.  
(CSid in table EN is aa typo/artifact - it should be CTid.)
ST was an ERROR before I changed it to CT, because EN, not CT,  
relates students to sections, and Student was not a proper part 
of table name ST (StudentTermSection), the former name of CT.
(See $PH/COOL-GEN/SISrev051011.ppt). 

Prerequisite PQ
(CourseID, PQcourseId) would be OK
if these were really values of COcurr->courseNo in table PQ.
But they're  not: Their real column1 names are COid1 and COid2
(surrogate pkeys). (This is another reason to deprecate alternate names
and replace this field with a default field value.)

CourseSchedule CS
(SDbldg, SDroom, SDtime, SDdays) - 
OK. you are correct assuming this precludes two classes (or even 2 sections)
sharing the same room and timeslot (because this CKey would no longer  discriminate between these sections).

Enrollment EN
(StudentTermId, CourseTermSectionId)
I agree. Each EN-row is a single student,secton pairing. 
They are accessed row-wise or column-wise 
(sparse matrix representation of this relation.

---------------

Nitin's test driver application in two stages:

I agree - do step 1 first. I hope CQ entries are enough to drive keyExists 
queries. Step 2 can drive higher-level commands [TBD by late contributors.]

BTWay the schema definition that I recommended for CQ entries 
would also apply to your CK spec entries. 
iff you split them into CK-rows with  CA-child-rows.

Since it's messy to define pkey and fkeys for these tables,
I'd minimize that by adopting the technique shown in the last slide
"Compression (Depth-First)" of $PH/DataModels05fr1.{ppt,mht}.
Here BB,EB are labeled, nestable brackets, capable of handling
any tree structured subset of a .sch ERDiagram. 
like XML's tree-structured compositions. So the pkey and first fkey
can easily be inserted by an awk-style filter as long as pkeys 
can be consecutively numbered (rowno++) and the fkey stays constant 
(same parent, as for CKs of one TT and CAs of one CK).
Old project assignments produced such filters.  

iAfter you loaded table  CA you would have to replace the 
default value TAid=TA000000 with the correct one by manually searching
TA-rows of TT for the selected fname in your CA instance.

[Oops! my metaschemaCK.sch omitted this (redundnt) field of CA - 
it's not essential but it certainly simlifies readable test data 
entry and debugging. I would recommend you add fname to CA from TA
in metaschemaCK.sch if you want to group CK, CA and CQ rows in [nested]  BB=BeginBlock and EB=EndBlock brackets.]

-------------------------------
 
Re: Nitin's attached copy of file SISrev061011.sch:

As is, it will fail, with gratuitous extra word-wraps:
chgen's schema parser treats '/*' and '//' alike;
it assumes '\n' = EOL terminates the last field.
(Sorry about that. Parsing multi-line comments is important in 
TA-definition 'lines' but is a deferrable option  for chgen.)

I am used to printing .sch files in landscape mode: 
(<= 132 chars/line from old line-printer days, smaller fonts nowadays.)

That's all until tomorrow, I think.

Bob Lechner