Database Design Enhanced entityrelationship modelling John Wordsworth Department

Database Design Enhanced entity-relationship modelling John Wordsworth Department of Computer Science The University of Reading J. B. Wordsworth@rdg. ac. uk Room 129, Ext 6544 April 2002 2/CS/3 X 1

Lecture objectives • To review other kinds of relationships between entities, especially hierarchical relationships • To consider how constraints on relationships are expressed • To review the notion of object-oriented design and its relationship to database design April 2002 2/CS/3 X 2

UML diagram for different kinds of rooms Survey of rooms available to the university: Department Office April 2002 0. . 1 1. . * Lecture Theatre 1. . * Room Seminar Room 2/CS/3 X 1. . 1 Building Laboratory 3

Analysis • The diagram shows a simple application in which rooms are of various types (Office, Lecture Theatre etc. ) • An Office is a room; a Lecture Theatre is a room, etc. • Some of the attributes of rooms (those common to all types) are recorded in Room • Others are recorded in Office, Lecture Theatre etc. April 2002 2/CS/3 X 4

Superclasses and subclasses • Room is a superclass: it is an entity type that includes distinct subclasses • Office, Lecture Theatre etc. are subclasses: they have distinct roles and are all members of the superclass Room April 2002 2/CS/3 X 5

Hierarchies • type hierarchy • specialization hierarchy – a top-down approach – start with the highest level of superclass – establish the set of subclasses by identifying attributes that are peculiar to each subclass • generalization hierarchy – a bottom-up approach – establish superclasses by identifying common attributes of a group of entity types • IS-A hierarchy (an office is a room, and so shares the properties of a room) April 2002 2/CS/3 X 6

Hierarchical relationships • IS-A links a class representing a generic type of object with a class representing a specific category of object • HAS-A links a class representing components with a class representing assemblies (of components) April 2002 2/CS/3 X 7

Constraints An annotation {participation, disjointness} describes the constraints on a hierarchical relationship. • participation – mandatory: an entity is obliged to belong to one of its subclasses – optional: an entity need not belong to any of its subclasses • disjointness – or: the subclasses do not overlap; an entity may be in only one subclass – and: the subclasses overlap; an entity may be in more than one subclass April 2002 2/CS/3 X 8

Object-oriented design • Each entity type is modelled as an object class. • An object class is associated with attributes and operations (methods). • Inheritance: each subclass has the attributes and methods of its superclass, and might have attributes and methods of its own. April 2002 2/CS/3 X 9

A class hierarchy Furniture Dimensions Weight Cost Location Upholstered Furniture Sofa Dimensions Weight Cost Location Fabric Pattern Seats Dimensions Weight Cost Location Fabric Pattern Buy Sell Move Dimensions Weight Cost Location Finish Buy Sell Move Polish Buy Sell Move Re-cover Extending Table Dimensions Weight Cost Location Finish Seats. Max Seats. Min Buy Sell Move Polish Extend Contract Buy Sell Move Re-cover April 2002 Wood Furniture 2/CS/3 X 10

Key points • UML can be used to express hierarchical relationships between entity types. • Annotations specify the participation and disjointness of entity types in a hierarchical relationship. • The hierarchical relationships of entity types are very similar to the inheritance relationships among object-oriented classes. April 2002 2/CS/3 X 11