Agenda
Review Exam – Diagrams
Present Final Project Assignment GUI Design – Mockups
Database Design Object Design
Presentation
Chapter 15 material – Database Design 15 Minute Break
Review of Exam
Diagrams seemed to be troublesome for
people
Review the major diagram types Any questions
University policy requires that we keep the
Review: Object Class Diagrams
Object Class Diagram is a diagram that
shows object classes and relationships
between them. You will also see this referred to simply as a class diagram.
Class Name Properties Methods
UML notation is commonly used for object
Review: UML Notation for Object Class Diagram
PROJECTS
PROJECT-ID: string; MANAGER: string; START-DATE: date; BUDGET: real; PEOPLE-ASSIGNED(N):PERSONS; TASKS(N): PROJECT-TASKS; Add-project(project-no); Delete-project(project-no); Create-task(task-name,task-details); Change-budget(new-budget); a87: PROJECT
PROJECT-ID: a87; MANAGER: Melissa; START-DATE: 10-7-98; BUDGET: 45000.00;
PEOPLE-ASSIGNED(N):{Mary, Lin};
TASKS(N): {task20, task31}; Add-project(project-no);
Delete-project(project-no); Create-task(task-name,task-details);
Change-budget(new-budget);
Review: Object Class Diagram
PROJECT S WORK PERSON S PROJECT -TASKS MANAGER 1 1..*0 .. * manage 1
work-in
Review: Object Class Diagram
PROJECTS
PROJECT-NO: integer; MANAGER: PERSONS; START-DATE: date; BUDGET: 120000-00;
PEOPLE-ASSIGNED(N):PERSONS; TASKS(N): PROJECT-TASKS; Add-project(); Delete-project(); Create-task(); Change-budget(); PERSONS
SURNAME: string; DATE-OF-BIRTH: date; DATE-JOINED: date; POSITION: integer; PAY-RATE: number;
ASSIGNED-TO(N): PROJECTS; Add-person();
Delete-person(); Create-task(); Change-budget();
TASKS
TASK-NO: 4;
DATE-SET-UP: 20-6-97; DESCRIPTION: write-program;
PROGRESS: ref DOCUMENT; Update-progress();
manage
work-in
Review: Object Class Diagram
TASKS LOCAL-TASKS OUTSOURCE D-TASKS TASKSTASK-NO: integer; DATE-SET-UP: date; DESCRIPTION: string; STATUS: string;
COST-TO-DATE: real; Update-progress();
LOCAL-TASKS
TASK-MANAGER: string; PERSONS(N): string; record-time-spent(); update-cost();
OUTSOURCED-TASKS
ORGANIZATION: string; CONTRACT-NO: integer; CONTRACT-RATE: integer; get-report();
Review: Use Cases
The use case script is an informal description
of the steps followed in an activity.
In some development practices, use case
scripts are written on index cards.
Use case – Using the ATM
A greeting message is waiting on the ATM
The customer inserts their card into the machine The ATM asks the customer for their PIN
Review: Use Case Model
Customer
Operator
Withdraw Cash
Transfer funds
Deposit funds
Get Report
Bank System
Review: Sequence Diagram
Sequence diagram shows dynamic
relationships between objects.
They model messages exchanged between
objects in a use case.
Each object is drawn as a vertical line
Messages between objects are shown in their
time sequence from top to bottom
Review: Sequence Diagram
Customer ATM Bank System
Customer Card
Request for PIN
PIN
PIN and Card info
PIN OK w/ Accounts
Account Numbers
Selected account
Review: Transition Diagram
Another way to model use case dynamics
Each circle or ellipse represents the state of the
object
States change as the result of a message
State changes are shown with arrows labeled by
the message that caused the change of state
Review: Transition Diagram
Idle Readin g Card Waitin g for PIN Ask bank to check PIN Waiting for authorit y from bank Display accoun ts Waiting for selectio n Displa y limits Waiting for selectio n Print Receipt and give cash ReceiptCard RequestPIN PIN entered
Request security check
PIN OK w/ Accounts
Accounts Account selected
Limits
Assignments
GUI Design – Mockups Database Design
Object Design
All design documents due on 1/8
Chapter 15: Logical DB Design
Logical DB Design is about converting ERDs
or Object Models into a logical record structure
The logical record structure is then converted
to a relational model
Logical Record Structure: a way of
describing records at the system level
ERD to Logical Record Structure
Chapter 15: Relations
We use relational analysis to convert logical
record structure to a set of relations
Relational analysis is a standard technique
for reducing models into non-redundant
forms by reducing the data to a set of normal form relations.
Chapter 15: DB Design 101
If we wanted to do so, we could store every item in a student table just like that below.
What is wrong with this design?
STUDENT_ID NAME DOB CITA_110_GRADE CITA_205_GRAD E
Tlombardi Thomas Lombardi Feb 12, 1970 75 Olombardi Ola Lombardi Jun 13, 1972 89
JSmith John Smith May 27, 1975
Chapter 15: DB Design 101
Database Normalization: a technique for
designing relational databases that aims to reduce data duplication, null fields, and data anomalies
If the same piece of data is stored in more than 1
place, it is possible for an update to put the data into an inconsistent state thus violating data
integrity.
Sufficiently normalized tables preserve data
integrity
Different types of databases require different
Chapter 15: Relations
Relation: a table or list of values
Attribute in a relational model: a column
of the table of list of values
Relational Model: A set of tables that
describe the data in a system
Chapter 15: Relations
USER-ID USERNAME PASSWORD
1 Tlombardi Pwasd 2 Bob 23424jkd
GROUP-ID GROUPNAME
1 Admin 2 Member
GROUP-ID USER-ID
1 1
2 1
Chapter 15: Non-Normal Form
ORDER-NO ORDER-DATE ORDER-LINESOrd1
Ord2
6 June 1998
3 May 1998
PART-NO QTY-ORDERED p1 10 p6 30
PART-NO QTY-ORDERED p5 10
p6 50 p2 30
Chapter 15: 1st Normal Form
ORDER-NO
ORDER-DATE PART-NO QTY-ORDERED
Ord1 6 June 1998 p1
10
Ord1 6 June 1998 p6 30
Ord2 3 May 1998 p5 10
Ord2 3 May 1998 p6 50
Ord2 3 May 1998Each attribute has a discrete value!p2 30
Chapter 15: 1st Normal Form
ORDER-NO
ORDER-DATE PART-NO QTY-ORDERED
Ord1 6 June 1998 p1
10
Ord1 6 June 1998 p6 30
Ord2 3 May 1998 p5 10
Ord2 3 May 1998 p6 50
Ord2Notice: 1NF still contains redundancies, i.e., ORDER-DATE.3 May 1998 p2 30
Chapter 15: Functional Dependencies
Functional dependency: where one value of
an attribute determines a single value of another attribute.
For example, in our previous case,
ORDER-DATE is functionally dependent on ORDER-NO.
There can be only one ORDER-DATE for each
ORDER-NO!
In the same way, there can be only 1
Chapter 15: Relation Keys
Relation Key: a set of attributes whose
values identify a unique row in a relation
Can include more than 1 column
Relations can have more than 1 relation key Must apply for all possible contents of the
relation
Key attribute: an attribute is a key attribute
if it is at least part of one relation key
Non-key attribute: an attribute that is not
Chapter 15: Normal-Form Relations
Normal-form Relations: a set of relations that
describes the data in the system, but where each data component is a simple value
We would like relation keys to determine unique
values of non-key attributes
Data Redundancies
The design goal is to eliminate data redundancies Relations without such redundancy satisfy a
number of constraints
Chapter 15: 2nd Normal Form
2NF must meet the following conditions:
1NF requirements: each column is a discrete value All non-key attributes must be functionally
dependent on the whole of each relation key
Relations in 2NF cannot have non-key attributes
that depend on only part of the relation key
To create a 2NF relation, simply remove the
offending functional dependency
In our previous example, ORDER-DATE only
Chapter 15: 2nd Normal Form
ORDER-NO ORDER-DATE
Ord1 6 June 1998
Ord2 3 May 1998
ORDER-NO PART-NO QTY-ORDERED
Ord1 P1 10
Ord1 P6 30
Ord2 P5 10
Ord2 P6 50
Chapter 15: 3rd Normal Form
2NF can still contain redundancies!
3NF must satisfy the following constraints
All requirements of 2NF
There can be no functional dependencies
Chapter 15: 3rd Normal Form
REGISTRATION-NO OWNER MODEL MANUFACTURER NO-CYLINDERS
YX-01 George Laser Ford 4 YJ-77 Mary Falcon Ford 6 YW-30 George Corolla Toyota 4 YJ-37 Mary Laser Ford 4 YJ-83 Andrew Corolla Toyota 4
This is only 2NF because the NO-CYLINDERS is functionally dependent on the MODEL and
Chapter 15: 3rd Normal Form
REGISTRATION-NO OWNER MODEL MANUFACTURER
YX-01 George Laser Ford YJ-77 Mary Falcon Ford YW-30 George Corolla Toyota YJ-37 Mary Laser Ford YJ-83 Andrew Corolla Toyota
MODEL MANUFACTURER NO-CYLINDERS
Laser Ford 4
Falcon Ford 6
Chapter 15: BCNF
Normal forms must cover relations with more
than one relation key
In relations with more than one key, part of
one key may be functionally dependent on part of another key
In order to address this, we need another
normal form
Optimal normal form: set of relations that
have no single-value redundancy
Chapter 15: BCNF
PROJECT-ID
PERSON-ID MANAGER
TIME-SPENT
Proj1 J1 Vicki 30
Proj2 J1 Joe 12
Proj1 J2 Vicki 11
Proj2 J2 Joe 79
Proj3 J2 Belinda 17
Proj2 J3 Joe 3
3NF
Still contains redundancies
Chapter 15: BCNF
PROJECT MANAGER
Proj1 Vicki
Proj2 Joe
Proj3 Belinda
PROJECT-ID PERSON-ID TIME-SPENT
Proj1 J1 30
Proj2 J1 12
Proj1 J2 11
Proj2 J2 79
Proj3 J2 17
Chapter 15: Beyond BCNF
Normal forms to BCNF satisfy constraints
related to functional dependencies
Multivalued Dependencies introduce a whole
new set of problems
Multivalued dependency: where one value
of an attribute determines a set of values of another attribute
4NF and 5NF address the problems with
Normalization and its Discontents
Is a higher degree of normalization better?
The higher the degree of normalization the more the database is write optimized
The lower the degree of normalization the more the database is read optimized
Different types of databases require different levels A reporting system that is too normalized will
require more joins and impact performance adversely