Subject: database assignment: hints - Part I > Allow me to quote parts of the test. Most of you probably would only > like to skim it, or grab a cup of coffee during this commercial break. > > "Once upon a time, there was a college. There were faculty who had > names and social security numbers and offices and advisees and were > part of departments, Of course all faculty members were created equal > and the most equal was called the chairperson. The faculty taught > students in courses. > > [ snip ] > > There is much more, but the "entertaining" part is mostly quoted. > > But the fun part was that we were given a pencil, some paper, no > computer, and an hour-and-a-half. welcome to the wild, wonderful world of database theory and the Entity Attribute Relationship theory of systems modeling. this kind of thing is utterly fundamental to object oriented programming, so here are a few professional tips that should help you sift through the information in search of a working model. the first step, when facing the kind of description your instructor gave you, is to identify what are officially known as the "domain objects". basically, those are the things in the real world which you're trying to model on the computer. the fastest, best, and simplest way to start identifying domain objects is to take a description like the one above, and pull out all the nouns and noun phrases. for the paragraph above, that list would be: college faculty names social security numbers offices advisees departments faculty members chairperson students courses and those identify your first list of candidates for Entities. this description is much better than the kind you'd get normally, because all the nouns are clear categories, and there's no redundancy at all. in real-world situations, you have to do a *lot* of sifting to get clear, meaningful entities, but the process is generally the same.. just slower. once you've got the entities, you start listing the Relationships which exist between them. many times, you can use the verbs from the description as the identifiers, but sometimes you have to extrapolate a little: [ faculty member ] | |- is employed by ->[ college ] |----- is part of ->[ faculty ] |---------- has a ->[ name ] |---------- has a ->[ social security number ] |----------- uses ->[ office ] |-------- advises ->[ advisee ] |----- is part of ->[ department ] |---------- has a ->[ chairperson ] |------- talks at ->[ student ] |-------- teaches ->[ course ] [ college ] | |------------ has a ->[ faculty ] |------------- owns ->[ office ] |------------ has a ->[ department ] |---------- employs ->[ faculty member ] |- takes money from ->[ student ] |----------- offers ->[ course ] etcetera. laying out the initial relationships between entities involves some judgement calls and tradeoffs. there's no mechanical way to generate a "correct" solution. OTOH, it's fairly easy to play with relationship mappings, because they're small and it doesn't cost much to throw them away and start over. once you have the basic relationships worked out, you go back and look for the ones which say "has a". that relationship is very easy to model, and gives you a first glimpse of the overall structure in your model: [ college ] | |---[ department ] | | | |---[ chairperson ] | |---[ faculty member ] | | |---[ faculty ] | |---[ faculty member ] | |---[ name ] |---[ social security number ] |---[ chairperson ] and in this case, shows some of the mistakes (well, bad guesses) in the relationships i chose. i have the [faculty member] and [chairperson] entities appearing in more than one part of the diagram, which makes things complicated. it would be simpler to lay things out like so: [ college ] | |---[ department ] | |---[ chairperson ] | |---[ faculty ] | |---[ faculty member ] | |---[ name ] |---[ social security number ] because this vesion has no redundancies. the structural model is one of your key design tools, because it gives you a framework for inventing new entities which will produce "has a" relationships for all the other relationships in your list: [ faculty member ] | |---[ office assignment ] | | | |---[ office ] | |---[ advisee list ] | | | |---[ advisee ] | |---[ course list ] | |---[ course ] | |---[ roll ] | |---[ student ] technically, all relationships can be reduced to the simple, "has a" form by putting another entity in the middle. the only case where that isn't also the easiest way to handle things is generalization.. the "is a" relationship. when you get in and look at things, a [chairperson] is just another [faculty member] with some additional stuff thrown in: [ chairperson ] | |---[ (name, office, etc) ] | |---[ departmental responisiblity 1 ] |---[ departmental responisiblity 2 ] |---[ departmental responisiblity 3 ] but that hasn't been spelled out explicitly in the description. now, there are two, equally valid ways to arrange objects which have "is a" relationships. the first way is to use what's called 'inheritance'. you define a very simple entity which has the features common to all members in the tree, then use that as a starting point for all further definitions: [ person ] | |---[ name ] |---[ social security number ] | |<|-[ faculty member ] | | | |---[ office ] | | | |<|-[ instructor ] | | | | | |---[ course list ] | | | |<|-[ chairperson ] | | | |---[ departmental stuff ] | | |<|-[ student ] | |---[ housing ] |---[ class list ] the good side of inheritance is that it allows you to keep the features common to a category in one place. if you want to change the things a generic person has, those changes automatically roll down to (are "inherited by") all the derived entities. the bad side is that inheritance can be hard to implement. it's true in object-oriented programming, and even more true in relational databases. changing the relationships an entity has means moving it to a different table, or doing wierd JOINs, and just gets cumbersome. fortunately, you can always model an inheritance relationship by adding another entity, called the 'specifier': [ person ] | |---[ name ] |---[ social security number ] |---[ classificiation ] | |---( faculty member ) |---( student ) [ faculty member ] | |---[ office ] |---[ title ] | |---( instructor ) |---( chairperson ) [ student ] | |---[ course list ] |---[ advisor ] |---[ level ] | |---( undergraduate ) |---( graduate ) |---( postgraduate ) etcetera. at that point, you're pretty darn close to your basic table definitions for the overall system. throw a few back references into the specified tables so you can get back to the personal information about each individual: [ student ] | |---[ identity {person} ] |---[ course list ] |---[ etc ] and you'll have places to store all the information you need to handle a specific type of query.