UMass Lowell Computer Science
Department
Assignments,
Part I, Fall 2003 (03f522)
http://www.cs.uml.edu/~lechner/03f522/03f522Asgnts2and3.htm
Data Models: http://www.cs.uml.edu/~lechner/DataModels2htm/
COOL: http://www.cs.uml.edu/~lechner/COOL/
JPSim: http://www.cs.uml.edu/~lechner/97f522/jp2htm//DEMO/
BDE UserGuide: http://www.cs.uml.edu/~lechner/bdeUG2htm/
GENv11
User Guide ..\COOL-GEN\chgenv11.doc
GENv12 Update Report: http:/www.cs.uml.edu/~lechner/chgenv12.doc
This is a traditional information modeling and database design exercise. Its goals are to illustrate information modeling (the EERD and equivalent UML class diagram) and its manual conversion to a completely defined database schema and a fully-conformant test database.
Deliverables (Based on class discussion, handouts and URLs): Part
1C below. (Parts 1A and 1B have already been done in 02s522 (see the SRS Project
report handout).
|
1A |
UML Class Diagram or normalized ERD |
Drawn with a drawing tool. |
|
1B |
SIS.sch schema file |
Table and attribute defs (saved as PLAIN-TEXT). |
|
1C |
SIS.dat test data file |
Sample rows for each table (TBD: Edit and save as PLAIN-TEXT). |
NOTE 2.1: Normalized SIS Database Information Requirements:
Your EERD, schema and test data must include at least the following 6 tables,
with sample data for student enrollments and instructor assignments in course
section offerings spanning multiple terms or semesters. If a student retakes a
course, retain each grade: I expect all your data files to be compatible
because they have the same SIS.sch definition; in particular, surrogate pfkeys
with disjoint typed values (see Note 3 below).
|
DE = DEpartment, TM = TerM |
Two top-level or root tables with pkeys and non-key attrs. |
Non-key attributes (if sufficient for the reports): deptNbr (c2), deptName (t60); ); termCode (c3: e.g."02s"); |
|
ST = STudent, FA = FAculty, CD = Course Description |
Three level-two tables with pkey + fkey to DEpartment + non-key attributes |
studentId (i4 = 32-bit signed int, maxVal > 10**9); lastNameInitial (c24 - no spaces); GPA (f4 - 32-bit float); courseNbr (c3); courseDescr (t80: name + comments); |
|
CS = Course
Section |
Ternary relation with pkey + fkeys to CD, FA and TM |
Non-key attributes: sectionNbr
(c3), location (c20), timeslot (t20), etc. |
|
EN = Enrollment |
Binary relation with pkey + fkeys to ST and CS |
Non-key attribute: grade (c2) |
[Review
Question: How would this schema (and its EERD) change if PErson is added as a
superclass of FA and ST, or if an M:N binary Pre-Requisite relation is added
between CD-instance pairs?]
Theses rules produce compact
and readable layouts. Be sure to follow them!
2.2.1 Attribute names belong inside UML class (entity) rectangles in EERDs and UML Class Diagrams. An Entity node is not a wheel with oval icons attached by spokes to a rectangular hub! An EERD layout cluttered with attached oval attribute icons is completely unacceptable and will get no credit.
2.2.2 The layout of entities or classes on an ERD should always be top-down, from super-class to subclass and from parent container to child components. Most designers violate this rule, although it is the greatest contributor to diagram comprehensibility (after rule 2.1 above :-). For example, compare OLC Figure 5.2.2 pp.90-91 to our topologically sorted JPSim + BDE + LCP layout (handout and at http://www.cs.uml.edu/~lechner/97f522/jp2htm/ksuresh/DEMO .With a block diagram editor like BDE that retains connectivity, most EERD's can be rearranged top-down, even for industrial-strength problems (assuming well-partitioned domains).
2.2.3 Always 'normalize' data models by eliminating repeating groups and redundant fields. This requires splitting multi-valued attributes from a parent entity and creating multiple related linked child entities. Eliminating redundant copies of field values and adding extra fkey links usually saves space, and it also eliminates error-prone 'double-maintenance' problems.
2.2.4 Always 'formalize' (OLC 2.4) or 'refactor' each M:N relation, by converting it into two 1:M relations directed toward an associative entity that we add to contain relation attributes. (Foreign keys (fkeys) in this associative entity replace OODB's need to maintain two multi-valued or 'set-of' reference attributes for two-way multi-valued relationships. Associative entities are recommended in many relational database texts -
e.g., the IDEF1X handout by Gale, the 91.522 recommended text by Sanders, the 91.309 text by Connolly and Begg, and the IDEF1X text by Bruce - see 01F522 bibliography).
2.2.5 Normalized relation link instances have no attributes but links still must be annotated with two multiplicity range pairs (minOccurs .. maxOccurs) , one pair at each end, UML style: (0..1, 1..1, 0..*, and 1..*) as in FOOD 4.2. [You may also use arrow head/tail to represent maxOccurs = *, no head/tail for maxOccurs = 1. Do not use single and double arrowheads as in OLC2.3. MinOccurs = 0 or 1 can also be represented by dashed vs. solid half-lines or by over-printing o or | near the link end.]
2.2.6 Inheritance relations should use the T-diagram layout (vertical lines connect entities to a horizontal bar with superclass on top, subclasses underneath). An open arrowhead on its link segment identifies the superclass in case it's not the only class above the bar.
Both SIS.dat and SIS.sch must follow chgen-defined attribute order and
format exactly. SIS.sch will be parsed by chgen and copied into rows of tables
TT and TA respectively. SIS.dat is parsed when it is read by the pr_load
method.
2.3.1 Meta-attributes of a .sch
table declaration (non-key fields of table TT) are tablename, tableAbbrev,
and tableDescription. Their formats are c32, c2 and t80. (Don't omit
tableDescription!)
2.3.2 Meta-attributes of a .sch attribute declaration (non-key fields of table TA) are: fieldName, defaultValue, format, is_key, and fieldDescription. Formats: c32, c20, c4, c2 and t80. (Don't omit fieldDescription!).
2.3.3 Allowable field formats are c8 (pkey or fkey), cnn (word of length nn), I4 (32-bit integer), F4 (32-bit real), and Tnn (text string of max length nn, terminated by end-of-line, allowed in last column only).
2.3.4 Use the is_key field to discriminate between a scalar (non-key) attribute (is_key = 0) or a reference (primary or foreign key) attribute (is_key = 1 or -1 for no back-pointers), or a reference to a instance superclass (is_key = s in a singleton child).
2.3.5 White-space is the field separator; therefore, only the last field of any table may have the text or tnn format that allows embedded white-space. (Other columns can use the word or cnn field format which does not allow whitespace.)
2.3.6 (New 030903). Disjoint sets of primary keys: In order to merge test data from each 03f522 student, no two pkeys can be the same inside any table. But course 91.522 may be entered by two people and it MUST have the same pkey in both. (Duplicate pkeys are rejected on the first-come, first-served principle).
Same-named students must also have the same pkey.
So I propose you all use pkey "CO091522" for course 522 OOAD, and similarly for the others. Use only section numbers 201 and 202 with pkeys SEttt201 and SEttt202 where ttt is a term code like 021 for 02s, 022 for 02f, 031 for 03s, etc. and TErm=semester records have similar pkey numbering schemes.
Faculty and student pkeys should be assigned based on radix-26 codes for the two initials: e.g., for student AZ append the 6-digit number 0*26+25 to "ST". BB becomes 1*26+1 or ST000027. (I'll show you how a pre-filter can add offsets to the row or instance number at import time. At present the data loader cannot do this automatically.) Finally, associations like PRerequisite and ENrollment and SEction keys can get 2 or 3 digits from each of the tables they relate together. (For example, enroll STudent ST000332 in SEction SE031201 by creating an ENrollment record with pkey EN332-31-1 (omit the hyphens :-).
This will solve most but not all key overlaps and permit merging of most data.
How much data? Each of you should create no more than 12 records, including at least one and at most 4 from each table type. That will give us a few hundred records total.
The purpose of this implementation exercise is to appreciate the relative complexity of analysis vs. design vs. re-use by using genv12 to convert your SIS.sch file into an API (Application Programmer Interface) code library, and using it and COOL's standardized data structures, naming conventions and table navigation methods to implement two report generators.
(1) Use chgen to generate object-relational database code and metadata from your schema definition. Deliverables: Code directory file stats and schema content (tables TT and TA)
(2) Write a report generator in C or C++ and compile it linked to the pr_util library API from chgen. Deliverables: source code and compilation typescript with warnings from stderr (log of build and test procedures) e.g. via this command ('&' merges stderr with stdout in typescript).
'script; col -bx < typescript > logfile'
(3) Your program should import (pr_load) your test database and produce two reports.
[Hint: Use table_loop and child_loop macros (for-loops) to navigate the
memory-resident tables in two ways: CS--->EN>---ST to produce rosters.dat
(a student roster for each section), and ST---->EN>---CS to produce
grades.dat (a grade report for each student).]
(Do not insert a page-break
(<FF> or ^L) to separate each roster or grade report page - it only
wastes paper.)
3.1 Read the chgen User Manual in $CASE/gen/ver_12/chgen/doc/chgenv12.doc, particularly the schema format and the (over-simplified) school database example. Use genv12 which is upward- compatible from genv8, and has the -log option to report database changes.
3.2 Log into cs = saturn via a vt100 (SSH) or xterm (UNIX) window and type these two commands or add them to your .login file: (setenv is for shell csh/tcsh; use set + export in sh)
setenv CASE
'/usr/proj3/case';
alias gen ' $CASE/gen/ver_12/jaganat/executables/chgen'
3.3 Using the environment variable $CASE, the command alias gen will invokes genv12 for your current platform ($HOSTTYPE) from your current working directory. Then you can type
'gen -msdat -log -ansi
SIS.sch' to run genv12 on this schema.sch file.
3.4 Running genv12 on a correctly formatted SIS<userid>.sch' will generate tables TT and TA in metaschema.dat and an ANSI-C code library of files named pr_*.c.
3.5 Please note: It is not mandatory to get running code - [but DO save and submit a col -bx < typescript logfile to report the status of your compilation]. The quality of your design documentation is more important. Quality depends on adequati requirements, planning, design and verification. The latter includes pre- and post-condition assertions and test data which exercises the ERD relationship access paths.