Tutorial 3

• • • This tutorial went through how to convert multiplicity numbering used in UML modelling to ERD, and vice versa.

In the exam and assignments, use ERD please!

We also discussed how to map ERD into schema using Assignment 2 Q2 as an example.

Multiplicity of Relationship

• • • • • At least m, at most n (m .. n) – Sometimes, you may have (0 .. n), which means the two entities does not have to be related all the time.

* indicating any number, at least m is (m .. *).

* is also a short hand for (0 .. *).

1 is a short hand for (1 .. 1).

Default is 1 to 1. • • • • • m .. n 0 .. n m .. * 0 .. * (*) 1 .. 1 (1)

ERD

• • • Entities participate at most once in the relationship would specify key constraints, represented with an arrow. Always pointing from the entities to relationships. In the case of M-1, it is always from Many table pointing to the relationship. k e y • • • • One to One Many to One Many to Many Complete (full participation) – Complete 1-1 – Complete M-1 – Complete M-M One Many Many One Many R R R R R One One Many Entities participate at least once in the relationship would specify full participation constraints.

One One Many R Many

Types of relationships - UML

• • • • One to One Many to One Many to Many Complete (full participation) – Complete 1-1 – Complete M-1 – Complete M-M Entity_A Attribute1:P K Attribute2 ….

Entity_A Attribute1:P K Attribute2 ….

* * 0 .. 1 1 1 .. * 1 .. * relationship relationship Entity_B 0 .. 1 0 .. 1 * Attribute1:PK Attribute2 ….

Entity_B 1 1 .. 1 1 .. * Attribute1:P K Attribute2 ….

Question – Assignment 2 Q2, Q3

• Draw and Entity-Relationship Diagram for the following scenario, a real

estate firm employs personnel and lists for sale properties that have

owners. The following describes the organisation: – The firm is made up of several sales offices in various locations. Attributes of the sales office include an office number (identifier) and a location.

– Each sales office has one or more employees. Each employee has an employee id (identifier) and a name. Each employee can only be assigned to one sales office.

– Each sales office has one of its assigned employees designated as the manager of that office.

– The firm lists properties for sale. Each property has a property id (identifier), and location information consisting of a street address, the city, state, and post code.

– Each property is exclusively listed with one sales office. Each sales office may have many properties listed, including none.

– Each property has one or more owners. The percentage of ownership is an attribute of the relationship between an owner and the property. Each owner has an owner id (identifier) and a name. An owner may own one or more properties.

Attribute or Entity?

Model Answer

Entities participate at least and at most once – exactly once in the relationship are good candidates for the relationship to merge into as single tables.

Direct Translations – 8 Tables (both entities are relationships) After considering key and participation constraints – 5 Tables

List relationship Works-in relationship Manages relationship