Database Design Entityrelationship modelling John Wordsworth Department of

Database Design 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 Identify entities and relationships in informal descriptions. Explain what is meant by the degree and multiplicity of a relationship. Create a simple UML diagram to record entities and relationships. Describe the process of investigating entities and relationships in a domain. April 2002 2 CS 3 X 2

A reminder • • • Entity-set Entity type Attribute Domain data type Key: superkey, candidate key, foreign key April 2002 2 CS 3 X 3

Weak and strong • Strong entity types: arise from obvious sets of real-world objects • Weak entity types: arise because they are needed to make a workable database April 2002 2 CS 3 X 4

Relationships § An association between entity types § Example: a person owns zero or more cars, and each car is owned by one person § This is a one-to-many relationship people cars April 2002 2 CS 3 X 5

The Unified Modelling Language • Developed for modelling object-oriented relationships. • Can be used for entity-relationship diagrams. April 2002 2 CS 3 X 6

An E-R diagram in UML Person Car age name address reg. No make model April 2002 Owns 1. . 1 0. . * 2 CS 3 X 7

Cardinality of relationships The relationship A to B is: 1 -1: each A is related to at most one B, (and each B is related to at most one A). 1 -M: an A can be related to more than one B, but each B is related to at most one A. M-N: an A can be related to more than one B, and a B can be related to more than one A. April 2002 2 CS 3 X 8

Definitions • Degree: number of entity types paticipating in a relationship • Binary: people own cars. • Ternary: patients are given treatments by consultants. • Quaternary: lecturer teaches a class in a lecture theatre at a certain time. April 2002 2 CS 3 X 9

A ternary relationship Patron phone {PK} name address cc. Num Performance 1. . 1 Booking 1. . * date {PK} time {PK} piece 1. . * Seat location {PK} price April 2002 2 CS 3 X 10

Roles Arrives. At 0. . * 1. . 1 Airport Flight 0. . * departure April 2002 destination 1. . 1 Departs. From 2 CS 3 X origin 11

Constructing an E-R model • Identify the entity types. • Identify the key attributes. • Identify and draw the relationships, noting degree and cardinality. • Identify the non-key attributes of the entitites. April 2002 2 CS 3 X 12

Key points E-R models give an abstract view of an enterprise's data. Entities have attributes, some of which make up a primary key. Relationships are associations between entities, of various degrees and multiplicities. There is a simple (iterative) method of investigating an enterprise's entities and relationships. April 2002 2 CS 3 X 13