Database Design More entityrelationship modelling John Wordsworth Department

Database Design More 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 develop the notion of participation • To investigate the connection traps: – Fan traps – Chasm traps • To review the tuple relational calculus April 2002 2 CS 3 X 2

Participation constraints • Participation of an entity in a relationship ARB can be total or partial • The participation of A in R is total (or mandatory) if every known A is related to a known B by R. • The participation of A in R is partial (or optional) if there is at least one known A that is not related to a known B by R. April 2002 2 CS 3 X 3

Representing participation • Participation is represented as part of the cardinality in the UML diagram. • 0. . specifies partial participation. • 1. . or n. . specifies total participation. April 2002 2 CS 3 X 4

Connection traps § Fan trap: two 1: M relationships fan out from an entity, creating an ambiguous relationship § Chasm trap: a sequence of relationships leaves a gap between entity types for which we need another relationship April 2002 2 CS 3 X 5

Fan trap Staff April 2002 worksfor 1. . * 1 worksin 1. . * 1 Dept Bldg 2 CS 3 X occupies 1 1. . * belongsto 1. . * 1 Bldg Dept 6

Chasm trap (1) Univ 1 1. . * Faculty 1 1. . * Staff April 2002 1. . * 1 2 CS 3 X Dept 7

Chasm trap (2) Univ Faculty 1. . * 1 1. . * Staff April 2002 1. . * 1 2 CS 3 X Dept 8

Relational calculi • Tuple relational calculus: A predicate calculus whose variables range over tuples in one or more relations • Domain relational calculus: A predicate calculus whose variables range over values in attribute domains April 2002 2 CS 3 X 9

Tuple relational calculus (1) • Mathematical expressions are used to denote sets of tuples of interest. • { tuples descriptor | predicate } denotes the set of tuples satisfying the predicate. • { b | Book ( b ) } denotes the set of tuples in the relation Book. • { b. BNum, b. BTitle | Book (b) } denotes the set of tuples book number and book title in the relation Book. April 2002 2 CS 3 X 10

Tuple relational calculus (2) • { s. SName, s. SHall | Student (s) / s. LName = "ABC" } denotes the set of tuples of student names and halls for students whose tutor is ABC • { b | Book ( b ) / ( ½w )( Borrower ( w ) / b. WNum = w. WNum ) } denotes the set of books borrowed by someone in the Borrower table. April 2002 2 CS 3 X 11

Key points • Participation of entities in relationships has to be investigated and recorded. • There are some modelling connection traps that have to be avoided in E-R models. • The tuple relational calculus allows us to write implicit specifications of the information we need to get from relations. April 2002 2 CS 3 X 12