Class Diagrams and

1

• Class Diagrams and
• Database
• Design

2
We have considered how to build class diagrams
3
Why Bother?

• Good Question
• It turns out that class diagrams can help us
  design
• Databases
• Object Oriented Programs
• Distributed Systems (e.g. eCommerce applications)

4
First things first

• What is a
• Database?

5
Informal Definition

• A database is a collection of information. This
  information is stored in a very structured
  manner.
• By exploiting this known structure, we can access
  and modify the information quickly and correctly

6
Databases are Everywhere

• When you go to the library and look up a book on
  their computer, you are accessing the librarys
  book database.
• When you go on-line and purchase some product,
  you are accessing the web merchants product
  database.

7
Databases are Everywhere

• Your friendly bank keeps all your financial
  records on their database.
• When you receive your monthly statement, the bank
  generates a database report.
• When you call to make a dental appointment, the
  receptionist looks into their database for
  available times.

8
Databases are Everywhere

• When you go to your car dealer for repairs, the
  technician calls up your past work record on the
  garage database.
• At the supermarket, when the checker scans each
  product, the price is found in the stores
  database, where inventory control is also
  performed.

9
The world is full of things
10

• We want to store data about these things in
  databases
• The most popular type of databases are based on
  the relational model

11
Relational databases store data about things in
tables

• Name
• Steve Wade
• Al Pacino
• Robert De Niro
• Winona Ryder

• Emp_No
• 1234
• 75473
• 99921
• 007

12
A Simple Database

• Swillbuckets Club for Gentlefolk
• Membership Secretary, Frank Hovis, holds data
  about club members on postcards in a shoe box

13
Example Postcard
14
A Table for Frank
15

• If we were to create this table in the relational
  database system Microsoft Access we would do so
  through a screen that looks like this

16
Building Relationships between Tables

• Most databases are bigger than Frank's
• ? Library management software
• ? Personnel systems
• ? Stock Control
• These databases are made up of more
• than one table.

17

• We might view a list of the tables in a
  Microsoft Access application through a screen
  like this

18

• Each of these tables might be viewed in the same
  way as Franks

19

• We need to show the relationships between
  tables in an application

20
Two Related Tables

• Licensed Pit Bull Terrier
• License No
• Dog Name
• Sex
• Owner Name
• License Date

• Dog Owner
• Owner Name
• Address

21

• The name of the relationship between these two
  tables is "owns"
• ? Dog owner "owns" dog
• The relationship can be navigated because both
  tables have owner name in common.

22
In Microsoft Access we can view a database
as a set of tables linked together
23
Deja Vu

• That looks a bit like a class diagram
• Its a sort of map of the database. We need to
  develop a model like this before we proceed to
  implement the database.
• So developing a class diagram is a first step
  towards designing a database

24
A non-UML information model

• Entity Relationship Models are widely used for
  database design
• They are not part of the UML
• They can be derived from class diagrams
• We need to use a different notation for entity
  models so we dont confuse them with class
  diagrams

25
Developing an Entity Relationship Model

• The idea of an ERM is to describe
• the
• Entities (i.e. tables or files)
• Attributes of those entities
• Relationships between entities

26
One to One

• Each department is managed by one (and only one)
  manager.
• Each manager manages one (and only one)
  department.

27
How this might look in Access
28
One to many

• Each order has been placed by one (and only one)
  customer.
• Each customer can place one or more orders.
• Note that we cannot have an order that isnt
  connected to a customer.

29
How this might look in Access
30
Many to Many

• Each supplier supplies one or more products.
• Each product is supplied by one or more
  suppliers.

31
Consider this..

• Remember that in the database the relationship
  between two entities (or tables) is implemented
  by ensuring that they have an attribute (or
  column) in common
• What problems does this cause for the
  implementation of Many-to-Many relationships?

32
Many to Many

• We cannot implement a MM relationship in a
  database directly
• Instead, we must resolve the relationship into
  two 1M relationships
• This involves inventing (or more often
  "discovering") a link entity

33
Resolving Many to Many

• A patient may be prescribed many drugs and drugs
  may be prescribed to many patients
• If we include the link entity "Prescription" we
  don't have to think too hard about the additional
  attributes

34
Resolving Many to Many

• PATIENT PRESCRIPTION
  DRUG
• K Patient-No K Patient-No
  K Drug-No
• Name Date
  Name
• Address Dosage
  Supp-id
• etc
  Strength etc.
• K Drug No

35
How this might look in Access
36
An Implemented Database (in Access)
37
How we got here

• Entities became tables (relations)
• Attributes became columns in the tables
• Relationships implemented through foreign key
  dependencies

38
Using Access Wizards

• When you enter MS Access you are given the
  opportunity to create database applications using
  a wizard
• By selecting the Databases tab...

39
(No Transcript)
40
Experiment with these wizards

• Have a look at the generated tables and the
  relationships between them
• How would you represent these database structures
  in UML?

41
How would you convert this into a database?
42
Problems with Entity-Relationship Models

• Fan trap
• When the model represents a relationship between
  entity types but the pathway between certain
  entity occurrences is ambiguous
• Chasm trap
• When a model suggests the existence of a
  relationship between entity types but the pathway
  does not occur between certain entity occurrences

43
Problems with Entity-Relationship Model
Fan trap
B3
B7
B5
Branch
Operates
D1
D2
Division
IsAllocated
SG37
SA9
SL21
Staff
Which staff work at which branches?
44
Problems with Entity-Relationship Model
Fan trap (continued)
Division
D1
D2
Operates
B3
B7
B5
Branch
IsAllocated
SG37
SA9
SL21
Staff
Now clear which staff work at which branches
45
Problems with Entity-Relationship Model
Chasm trap
B3
B7
B5
Branch
IsAllocated
SG37
SA9
SL21
Staff
Oversees
PG36
PA14
PL94
Property
For convenience, some staff oversee properties on
other branches' books. Which branches have which
properties on their books?
46
Problems with Entity-Relationship Model
Chasm trap (continued)
B3
B7
B5
Branch
IsAllocated
Has
SG37
SA9
SL21
Staff
Oversees
PG36
PA14
PL94
Property
Now clear which branches have which properties on
their books