database_design21.ppt

(1)

(2)

Agenda



Announcements



Chapter 17 material – Physical Database

Design



15 Minute Break

(3)

Annoucements



PMI meeting this evening at the IT Club

12/29

 Discuss actions to date  Organize future activities

 _{Collect emails for further interest}



Project Designs due 1/8 or ealier

 Any questions?



Project Presentations start 1/5

(4)

Review: 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

 _{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

(5)

Review: 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

(6)

Review: 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}

(7)

Review: 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

(8)

Review: 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

(9)

Review: Non-Normal Form

ORDER-NO ORDER-DATE ORDER-LINES

Ord1

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

(10)

Review: 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

(11)

Review: 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}

(12)

Review: 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

(13)

Review: 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

(14)

Review: Normal Forms

 _1NF_{: each column is a discrete value}

 _2NF_{: All non-key attributes must be functionally}

dependent on the whole of each relation key

 _3NF:_{There can be no functional dependencies}

between non-key attributes

 _{BCNF (Optimal Normal Form):}_{set of relations}

that have no single-value redundancy

 _{4NF/5NF and beyond}_{: address problems}

arising from multivalued dependencies

 _{Mutlivalued dependency}_{: where one value of}

(15)

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

 _{A transaction system that is denormalized will result}

(16)

Chapter 17: Physical DB Design

 _{Databases are implemented using database}

management systems (DBMS)

 _{Each DBMS has its own properties and}

proprietary technologies

 _{The benefit of moving from logical DB design to}

physical DB design

 Ensures that design is free of proprietary solutions

 Materialized views are unique to Oracle

(17)

Chapter 17: Physical DB Design

 _{Major questions of physical DB design: how will}

records be stored? how will records be accessed?

 _{The default option is the}_{table scan}_{: row by row}

search for data in the order of storage

 In general table scans are horrible for performance!!!

 _{Direct access}_{: Retrieval of records based on}

indexed fields (keywords)

 _Index_{: uses one or more fields as a key to find}

records quickly

(18)

Chapter 17: Database Types

 _{Relational Databases}

 Oracle

 _{SQL Server}

 MySQL

 _{Hierarchical Databases}

 IBM Information Management System (IMS)

 _{Network Databases}

 _{CODASYL Standard (Consortium on Data Systems}

Languages)

 _{Object Databases}

(19)

Chapter 17: Convert LRS



Converting the Logical Record Structure to a

data model supported by the DBMS

 RDBMS – simply convert LRS into relations

that will become tables in database

 _{Network DB}

 Logical records become record types  Logical record links become set types

 Hierarchical DB

 Only one parent

(20)

Chapter 17: Access

Requirements

 _{How will the data be accessed?}  _{Example: the login screen}

 _{Every login screen accepts a username / password}

 You can be sure that the database table behind

this application has an index on the username field

 SELECT * FROM USER WHERE

USERNAME=‘TOM’;

 The USERNAME field should be indexed so that

(21)

Chapter 17: Access



The record types accessed by each request

 Which tables? How often?



The sequence in which records are accessed

 ASC, DESC, ordered on multiple fields



The access keys

 What fields need to be indexed?



The items retrieved from each record

 _{What fields need to be pulled back?}



The number of records accessed

(22)

Chapter 17: DB Structures

 _{Indexes are only part of the story of physical}

design

 _{Tablespaces are containers for tables that affect}

how data can be accessed

 _{Defines the storage characteristics for data including}

 Maps to logical containers to physical files and devices

 Allocates new storage when needed in extents

 _{Tablespaces can be spread across several physical}

devices

 _{Indexes and Tables can be stored on separate}

(23)

Chapter 17: DB Structures

 _{Block Sizes – the chunks of data allocated to a}

table

 _{Block Size should be multiple of Disk Block Size}  _{Block Size should be tuned to record length}

 _{Buffers, Buffer Pools, Cache, Cache-hit ratio}  _{Stored Procedures, Triggers, Distributed}

Transactions

 _{DB Configuration and DB Design}

 In order to meet your performance requirements,

you may need to tweak design purity

(24)

Chapter 17: Design Trade-Offs

 _{Performance vs. Storage}_{– Indexes require}

space. You can always lean on storage to improve performance

 _{Performance vs. Structure}_{– Redundancy in}

data can improve performance in certain areas.

 _{Read vs. Write}_{– Indexes improve retrieval}

metrics, but degrade the performance of

insert/update because the index must be rebuilt

 _{Performance vs. Administration}_{– Indexes}

require maintenance

 Rebuild indexes periodically

(25)

Chapter 17: Database Design



We have talked about the differences

between

 _{OLTP Databases}  Data warehouses



Let’s review these differences in light of

(26)

Break



15 Minute Break



When we get back we’ll discuss Program

(27)

Chapter 16: Program Design



We have captured business logic in a number

of different ways:

 DFD

 Sequence Diagrams  _{Transition Diagrams}  _{Structured English}  Decision Tables



How do we turn these analysis and design

(28)



Identify automation boundaries in a DFD

 This highlights which part of a process should

be automated

 _{Deciding which tasks should be automated vs.}

manual can influence the feasibility of a project



Identify component subsystem boundaries

 What are the major components?

 Can we work on these concurrently?

 How will we test these? Can they be tested

(29)

 _{After subsystems are chosen, process}

specifications are refined for each DFD process

 _{DFD processes are converted into program}

modules

 _{Program modules are arranged on a structure}

chart

 _{Structure chart}_{displays program modules and}

their interconnection

(30)

Chapter 16: Program Design

 _{Program Design Metrics}

 How do we know if our modules, packages,

programs make sense in terms of design?

 We use coding metrics to help us

 _{Static Analysis Tools}  _PMD

 CheckStyle

 _{Dynamic Analysis Tools}  Findbugs

(31)



Coupling

describes the nature, direction,

and quantity of parameters passed between

modules

 loose coupling



Cohesion

describes how system functions

are coded into modules

 One function, one module = high strength



Loose coupling and high strength =

(32)

Chapter 16: Coupling

 _{Content coupling}_{: when a module makes direct}

references to the contents of another module

 _{These should be passed as parameters}

 Imagine if an object had all public variables!!!  _{This is horrible!!!}

 _{Common-Environment Coupling}_{: when}

modules refer to the same data structure in a common environment

 _{Shared files}

(33)

Chapter 16: Coupling



Control coupling

: when a module passes a

control element to another module

 Control flags, function codes, switches

 Changes made to called function require

changes to calling function



Data coupling

: when a module passes data

through a well-defined interface to another

module

 Most desirable form of coupling

(34)

Chapter 16: Cohesion

 _{Coincidental Strength}_{: there is no meaningful}

relationship between the parts in a module

 _{Logical Strength}_{: all of the elements in a module}

perform similar tasks: edit, read, write

 _{Temporal Strength}_{: all functions are grouped}

according to when they are used: initialization, etc.

 _{Procedural Strength}

 _{Communication Strength}_{: all functions that}

communicate with each other are in the same module

(35)

Chapter 16: Code Complexity

 _McCabe’s_{Cyclomatic Complexity Number}

measures the number branches in code to estimate the complexity of code.

 The higher the number the more complicated the code  _{Coupling/Cohesion metrics can help plan for testing}

 _{Modules that have many other modules that depend on}

them should be more thoroughly tested

 Modules that have many other modules that depend on

them may need to be restructured

 _{Quality metrics}

(36)

Software Architecture Notes



Transactions across multiple systems

 Distributed Transactions

 Oracle

 SQL Server

 Message Queue  LDAP system

 Transaction coordinator uses 2 Phase Commit

Protocol to ensure that all members in the transaction agree

(37)

Software Architecture Notes

 _{Sacrificing design purity for performance}

 The SQL Server 2000 driver debacle

 SQL Server 2000 Driver for Java had a bug in its code

that made statement caching not work

 The driver rebuilt a query from scratch each time it was

called

 Executed 100,000 times

 Worked perfectly with other platforms Oracle, SQL Server 2005

 For performance, added if it’s SQL Server 2000 logic

then do this other crazy thing!!!

(38)

Software Architecture Notes

 _{Sacrificing design purity for ease of maintainence}

 _{Validation engine required frequent changes due to} new and changing requirements

 Rather than change interfaces again and again we invented the bag interface

 Rules for the bag: you can put anything you want into it

 _{Every validator accepted a bag as a parameter with} its configuration items inside