UMass Lowell Computer Science
Department
Assignments Part I, Spring 2002 (02s522)
http://www.cs.uml.edu/~lechner/02s522/02s522Asgnts1and2.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/
Ref: Data Models: http://www.cs.uml.edu/~lechner/DataModels2htm/
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 1/29 and 2/5 class discussion, handouts and
URLs):
|
1A |
UML Class Diagram or normalized ERD |
Draw by hand or with a drawing tool. |
|
1B |
SIS.sch schema file |
Table and attribute defs (edit and save as PLAIN-TEXT). |
|
1C |
SIS.dat test data file |
Sample rows for each table (edit and save as PLAIN-TEXT). |
NOTE 1.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!
1.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.
1.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).
1.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.
1.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).
1.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.]
1.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.
1.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!)
1.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!).
1.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).
1.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).
1.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.)
The purpose of this implementation exercise is to appreciate the relative complexity of analysis vs. design vs. re-use by using genv11 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.)
2.1 Read the chgen User Manual in $CASE/gen/ver_8/chgen/doc/, particularly the schema format and the (over-simplified) school database example. Use genv11 which is upward- compatible from genv8, and has the -log option to report database changes.
2.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_11/executables/genv11'
2.3 Using the environment variable $CASE, the command alias gen will invokes genv11 from your current working directory. Then you can type
'gen -msdat
-log -ansi SIS.sch' to run gev11.
2.4 Running genv11 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.
2.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 adequate requirements, planning, design and verification. The latter includes pre- and post-condition assertions and test data which exercises the ERD relationship access paths.