Transcript Database Design 1

OO ANALYSIS & DESIGN FOR
DATABASE-DRIVEN SOFTWARE
• Larger software products typically use a relational
database or several of them.
• The databases may exist before the software is
being built.
• New databases may be created.
• Old databases may be modified, most typically
they are extended.
• Often the database represents the biggest
permanent value (software changes on top of the
same database).
15.1.2003
Software Engineering 2003
Jyrki Nummenmaa
1
Classes And Associations
owns
uses
Piece
0..1
Player
0..1
*
*
located
1
0..1
Treasure
0..1
{ordered}
play
Game
Special place
1
2
end
cover
0..1
1
contains
Place
*
*
1
Map
Card
*
Flight
Connection
*
connected
15.1.2003
Software Engineering 2003
Jyrki Nummenmaa
2
Game ER Diagram
Connected
Flight
N
Located
Place
M
Walk
Special
Place
Covers
N
M
M
Is a
N
Card
N
Player
Is a
Is a
Is a
Treasure
Jewel
Bandit
Piece
Has
15.1.2003
Owns
Software Engineering 2003
Jyrki Nummenmaa
3
Analysis Class Diagrams
Player
Piece
color: Integer
use
0..1
move(p: Place)
getPlace(): Paikka
0..1
*
Dice
throw(): Integer
located
1
In no
relation
Card
name: String
funds: Integer
effect (p: Pelaaja)
isAtAirport(): Boolean
hasMoney(): Boolean
getPlace(): Place
move(p: Place)
isWinner(): Boolean
hasTreasure(): Boolean
giveTreasure(): Treasure
takeTreasure(t: Treasure)
pay()
clearFunds()
own
0..1
*
0..1
Players
1
addPlayer(n: String)
nextPlayer(): Player
treasurePlayer(p: Place):
Player
initialize()
addPlayer(n: String)
throwDice()
movePlayer(p: Paikka)
takeCard(p: Place)
end()
1
Jewel
value: Integer
play
1
FlightTraffic
Bandit
{ordered}
Game
getDestinations(p: Place):
set of Place
Treasure
0..1
1
place
Place
hasCard():Boolean
giveCard()
In one
relation
Map
*
follow
cover
SpecialPlace
giveAdjacent(p: Place,
n: Integer): set<Paikka>
NormalPlace
*
15.1.2003
FlightRoute
*
2
Software Engineering 2003
Jyrki Nummenmaa
4
Database Design – Motivation 1
• This is not a database course, so doing the actual
database design is not really our main concern
here.
• However, it would be very good to have some
expertise on this subject.
• If you do not know how a database should be
designed, then find someone who does.
• Unfortunately, such people are not always easy to
find.
15.1.2003
Software Engineering 2003
Jyrki Nummenmaa
5
Database Design – Motivation 2
• Many universities give quite little teaching on
databases and data management.
• Ours gives quite a lot. Therefore, there may be
strong expectations towards our students in this
respect. (In other words, people may be hopeful
that you might have some database skills!)
• Ok, so you have decided to study that issue more.
• In the meantime, I will give a couple of alternative
database designs for our game application based
on our ER diagram.
15.1.2003
Software Engineering 2003
Jyrki Nummenmaa
6
Database Design 1
• This design is based on the idea, that we want to
have fewer relations.
• This will speed up processing.
• There is nothing to say that the speed-up is
meaningful or necessary…
• On the other, we end up with a database, which
may contain null values.
• Null values are conceptually more difficult, that is,
more difficult to understand.
• Design one:
http://www.cs.uta.fi/~jyrki/se04/game1/
15.1.2003
Software Engineering 2003
Jyrki Nummenmaa
7
Database Design 2
• This design is based on the idea, that we do not
want to have null values.
• This will make things easier to understand.
• However, we will end up with more relations.
• Design two:
http://www.cs.uta.fi/~jyrki/se04/game2/
• In this particular case, either design feels possible,
there is no strict reason to rule out either of them.
• However, we will take Design 1 as our starting
point for software design.
15.1.2003
Software Engineering 2003
Jyrki Nummenmaa
8
From relations to objects
• We will probably want our software to access the
data in the relations.
• Conceptually, it will be straightforward and
understandable to have one class for each relation,
to access the data.
• You may also design the classes differently, but
then the connection between the relations and the
objects will be more complicated.
• However, if the relations reflect your ER-diagram,
then many of them will be quite close to ”intuitive
objects”.
15.1.2003
Software Engineering 2003
Jyrki Nummenmaa
9
Relations-to-classes finetuning/1
• In fact, we may like to generate two classes for
each relation. Assume that we have relation
X(A,B,C)
1. A ”Db” class DbX, which has the attribute data
includes methods for inserting, updating, deleting
and retrieving a single row from relation X.
2. The ”real” class X, which inherits DbX. All the
manually programmed code goes here.
• It may be possible to regenerate BaseX and DbX
so that we do not need to change X!
• The database design examples contain also
examples of Java source files to make database
access easier for programming.
15.1.2003
Software Engineering 2003
Jyrki Nummenmaa
10
Relations-to-classes finetuning/2
• Why would we want to regenerate DbX?
- Datatypes of attributes may change.
- New attributes may be added or old attributes
may be removed.
• What about our class diagram?
- Suppose a project contains 100 relations.
- The total number of methods in automatically
generated classes is several hundreds.
- We certainly want to generate some parts of our
class diagrams automatically.
15.1.2003
Software Engineering 2003
Jyrki Nummenmaa
11
An Initial Class Diagram
Piece
Place
15.1.2003
Player
Walk
Software Engineering 2003
Jyrki Nummenmaa
Connected
Card
Special place
Flight
12
Associations? / 1
• Based on our ER-diagram and the relation
attributes, it would now be possible to add
associations to the class diagram.
• Should we do so?
• On the other hand, they give information about
the relationships between classes.
• However, the associations are typically used for
navigation.
• In a database-driven application, this may be a big
mistake!
15.1.2003
Software Engineering 2003
Jyrki Nummenmaa
13
Associations? / 2
• Consider, for instance, relations player and place.
Suppose, for instance, that one player has a
treasure and is two steps away from the starting
place.
• We want to check up , whether any other player
still has theoretical chances to win the game.
• Any such player needs to steal the treasure in at
most two rounds.
• For this, we need to check all players, their places,
and distances to the player with the treasure.
• Thus, we need to access at least relations player,
place and walk (maybe also flight).
15.1.2003
Software Engineering 2003
Jyrki Nummenmaa
14
Associations? / 3
• Suppose our class diagram shows associations:
Player
Place
Walk
• Associations suggest navigation:
for each player {
get pl=player.place
for each pl.walk …
• Q: What would this mean?
• A: It would mean database retrieval row-by-row.
15.1.2003
Software Engineering 2003
Jyrki Nummenmaa
15
Database retrieval row-by-row?
• Each retrieval may mean scanning of a whole
relation: O(n) operations, if relation has n rows.
• At best, it means traversing a search structure
such as a search tree: O(log n) operations.
• Relational databases are meant for set-based
retrieval: get all data in one query.
• Conclusion: The associations were misleading.
• These kinds of stupid solutions just lead to poor
performance and some people may even imagine
that the database is slow!
15.1.2003
Software Engineering 2003
Jyrki Nummenmaa
16
Set-based database retrieval
• Rather, make a query to retrieve all data in one
go: select * from player, place, …
• Maybe make this definition into a view (which is
like a predefined query).
• Define a new class for the query. The class does
not need to have connections with place or player,
although for the conceptual information it should.
• Use an object to go through the results of the
query row by row, which means just one execution
of the query in the database.
15.1.2003
Software Engineering 2003
Jyrki Nummenmaa
17
Architectural design
• Design the basic system architecture (in our
example project we use servlets on top of a
database).
• Identify the system components necessary for the
architecture.
• Test your basic technological solutions (e.g., write
one servlet, which accesses the database and …)
• Once you are sure that the architecture and the
technological solutions are sound, move on to
implementing the functionalities.
15.1.2003
Software Engineering 2003
Jyrki Nummenmaa
18
Design
• Study the requirement specification document and
use cases with use case sequence diagrams.
• Choose a suitable set for the next increment.
• Design the execution of a use
cases/functionalities:
- If database needs to be accessed, add new
classes for queries if the existing ones are not
enough.
- Add necessary classes that the system needs to
to function (e.g. dice in our example).
• Implement, test, and get feedback from
users/customers.
15.1.2003
Software Engineering 2003
Jyrki Nummenmaa
19
Sequence Diagram for ”Take
Card”
Application
User
Choose to take card
Show player’s funds with one unit taken
Show Card
Show player’s funds with jewel value added
15.1.2003
Software Engineering 2003
Jyrki Nummenmaa
20
Sequence diagrams are ok also
here
This is a new class
User
GameController
Choose to take card
Also a new class, but for a query
Player
CardForPlaceQuery
reduceFunds(price)
showFunds(p:Player)
getCard()
showCard(card)
addFunds(value)
showFunds(p:Player)
15.1.2003
Software Engineering 2003
Jyrki Nummenmaa
21
Operation design
• At the bottom level, you will encounter similar
tasks as in the OO design discussed in our earlier
slides.
• You will need to do “classic” OO design for the
non-database classes.
• There, the rules discussed on previous lectures
apply.
• Take, as an example, operation design, which is
discussed in the following slides.
15.1.2003
Software Engineering 2003
Jyrki Nummenmaa
22
Example: treasurePlayer(p:
Place)
Class: Players
Operation: treasurePlayer(p: Place): Player
Description:
If there is a player in place p with the treasure,
returns that player.
Result:
Returns player Q, for which Q.owns  nil and
Q.uses.located = p, if such Q exists,
otherwise nil.
Assumes:
p  nil
15.1.2003
Software Engineering 2003
Jyrki Nummenmaa
23
Example: treasurePlayer(p:
Place) – database formulation
Reads:
Player, Piece
Exceptions:
Player has no piece.
Algorithm:
SELECT player_no FROM player, place
WHERE player.place_no=place.place_no AND
hasTreasure = true AND
place_no = p
if result-set contains player_no
then return player_no
else
return nil
15.1.2003
Software Engineering 2003
Jyrki Nummenmaa
24
Example: treasurePlayer(p:
Place) – old formulation
Reads:
Player, Piece
Exceptions:
Player has no piece.
Algorithm:
while players not processed do
Q = unprocessed player;
if Q.hasTreasure() then
if Q.getPlace() == p then
return Q
end
end
end;
return nil
15.1.2003
Software Engineering 2003
Jyrki Nummenmaa
25