Object-Oriented Programming & Relational Design - Atlanta.mdf

handprintSoftware and s/w Development

Nov 18, 2013 (3 years and 4 months ago)

204 views

Object
-
Oriented Programming
& Relational Design

Clash of the Paradigms….

Stuart R Ainsworth

Gladiator Technology Services

atlantaMDF

June 12, 2006

About Me


American Cancer Society

Behavior Research Center


Research Analyst, 1995
-
1998


Project Manager, 1998
-
2000


Onity


Report Analyst, 2000
-
2001


Database Administrator, 2001
-
2002

About Me


Gladiator Technology Services, Inc


Lead Developer, 2002
-
2003


Senior DBA, 2003
-
2005


Database Developer, 2005
-
Present

Goals


Introduce basic concepts of OOP


Compare & Contrast OOP and ERD


Historical


Design Implications


Discuss Relevance to SQL Pro’s

Goals


Provide Concepts & Code


Design Scenarios


Prognosticate on the future of design

Precepts of Good Design


Isolate database development from
application development.


Encapsulation


Loose Coupling

DATA STORAGE LAYER

DATA ACCESS LAYER

BUSINESS OBJECTS LAYER

APPLICATION/INTERFACE LAYER

Layered Design

History Lesson

“Set the WABAC machine,
Sherman!”

-
Mr. Peabody

History
-
Rel Paradigm


Relational design based on work of E.F.Codd


A Relational Model of Data For Large Shared
Data Banks


1970 ACM


Codd’s 12 Rules for Relational DB’s (1985)


Implementation


Ingres (1974)


Relational Software (Oracle; 1979)

History
-
Rel Paradigm


Context:


Hierarchical databases prevalent


Tree structure


Redundant data in attributes


Expense of computer hardware


Limited storage capability


Limited expansion possibilities

History
-
Rel Paradigm


“By the time UNIX began to become popular
(1974), a well configured PDP
-
11 had 768 Kb of
core memory,
two 200 Mb moving head disks
(hard disks),

a reel to reel tape drive for backup
purposes, a dot
-
matrix line printer and a bunch of
[dumb] terminals. This was a high end machine,
and even a minimally configured PDP
-
11 cost about
$40,000
. Despite the cost, 600 such installations
had been put into service by the end of 1974,
mostly at universities.”


History
-
Rel Paradigm


“In 1973, IBM developed what is considered to be
the first true sealed hard disk drive... It used
two
30 Mb platters
. Over the following decade, sealed
hard disks (often called Winchester disks) took their
place as the primary data storage medium, initially
in mainframes, then in minicomputers, and finally in
personal computers starting with the IBM PC/XT in
1983.”

History
-
OOP


Alan Kay


Smalltalk (1971)


Biological metaphors; cells in a body


Building blocks; code reduction


Recent Work


http://www.squeakland.org/

History
-
OOP


“Smalltalk (and object
-
oriented
programming in general) was a way to
achieve these goals. In theory, programs
would be easier to write because they were
modeled on things that were easier to
understand…. Additionally, programs would
be easier to read, and hence, easier to
maintain, vastly reducing the largest costs
associated with software.”

Historical Comparison


Relational


Optimize data
storage


Optimize data
retrieval


Data
-
centric efficiency


OOP


Optimize
development time


Optimize application
support


Coder
-
centric efficiency

O/R Impedence


“The object
-
oriented paradigm is based on proven
software engineering principles.


The relational
paradigm, however, is based on proven
mathematical principles.


Because the underlying
paradigms are different the two technologies do not
work together seamlessly.


The impedance
mismatch becomes apparent when you look at the
preferred approach to access: With the object
paradigm you traverse objects via their
relationships whereas with the relational paradigm
you join the data rows of tables.



http://www.agiledata.org/essays/impedanceMismatch.html

OOP Coding Concepts

All programmers are
playwrights and all
computers are lousy actors.

-
Unknown

Definitions


Class

A storage medium for keeping size,
structure, and operations for the type.

Public Class Book


Public Text as String = “”


Public PageLength as Integer = 10

End Class

Definitions


Object

Instance of class; Run
-
time value that
stores state of a class

Dim fairyTales as Book

fairyTales = New Book()

fairyTales.Text = “Once upon a time…”

fairyTales.PageLength = 8

Definitions


Methods

Verbs associated with classes; actions
performed by objects


Public Class Book


Public Function GetPage…

End Class

Dim TextOnPage6 as String = “”

TextOnPage6 = fairyTales.GetPage(6)

Definitions


Properties

Attributes of class; descriptive

Public Class Book


Public Property Title()as String…


Get…


Set…

End Class

fairyTales.Title = “Goldilocks”

Definitions


Events

A signal that an action (method) has
occurred. Events are handled.

Public Class StartEventArgs


Inherits System.EventArgs


'Provide constructors, fields and


'accessors for the arguments.

End Class


Public Class Sender


Public Event Start(ByVal sender As Object, ByVal e As
StartEventArgs)


Protected Overridable Sub OnStart(ByVal e As StartEventArgs)


RaiseEvent Start(Me, e)


End Sub


'...

End Class


Public Class Receiver


Friend WithEvents MySender As Sender


Private Sub MySender_Start(ByVal sender As Object, _


ByVal e As StartEventArgs) Handles MySender.Start


'...


End Sub

End Class

Definitions


Encapsulation

Internal functionality of object is
hidden from callers. “Black box”
programming.


Loose coupling


No assumptions between applications
or objects

Definitions


Encapsulation

Internal functionality of object is
hidden from callers. “Black box”
programming.


Loose coupling


No assumptions between
applications or objects

Definitions


Hierarchy

Superclasses & subclasses; objects
relate to each other in a hierarchical
fashion (e.g., ducks are birds, which
are animals, which are organisms…)

Definitions


Inheritance

Process by which a subclass is derived
from a superclass; methods in
particular are inherited.



Ducks Fly() because Birds Fly(), and
Ducks inherit from Birds

Definitions


Inheritance

Process by which a subclass is
derived from a superclass;
methods in particular are
inherited.



Ducks Fly() because Birds Fly(), and
Ducks inherit from Birds

Definitions


Overriding

Subclass methods are used before
Superclass methods.


Polymorphism

Common method across different
objects with different
implementations.

Ducks Swim(), Fish Swim()

Where are we?


OOP Developers are bored.


SQL Developers are challenged.


DBA’s are wondering how the systems
are doing back at the office.

Common OOP/RP Clashes

Computers are useless. They
can only give you answers.

-
Pablo Picasso


Five Common Issues


Data Access Layer Debate


Inheritance Design


Iteration


Triggers


CLR misconceptions

DATA STORAGE LAYER

DATA ACCESS LAYER

BUSINESS OBJECTS LAYER

APPLICATION/INTERFACE LAYER

Layered Design

Stored Procs

TABLES

DataSets

ADO.NET

DAL Debate


Role of Database in DAL


Stored Procedures


Security


Typically Parameterized


Encapsulation

DAL Debate


Role of OOP in DAL


Transformation from data to data objects


ADO.NET, ADO, RDO


Parameterized SQL


Sometimes necessary


Developers must collaborate with DBA


Raises encapsulation concerns

Five Common Issues


Data Access Layer Debate


Inheritance Design


Iteration


Triggers


CLR misconceptions

Inheritance in ERD


Entity: some unit of data that can be
classified and have stated relationships
to other entities.


Like objects, entities are nouns:


“We sell cars and trucks.”


Entities DO NOT INHERIT from entities


Can be emulated


Emulation <> reality

Inheritance in ERD


Vertical Mapping


1
-
1 Joins; Common Attributes grouped as
a single entity.


Horizontal Mapping


Classic ERD; Entities=Tables, regardless
of inheritance.


Filter Mapping


Single Parent Entity; allow NULL values

Vertical Integration

PEOPLE
PK
ID
FirstName
LastName
EMPLOYEES
PK,FK1
ID
Department
CUSTOMERS
PK,FK1
ID
DateOfFirstPurchase
Horizontal Integration

EMPLOYEES
PK
ID
FirstName
LastName
Department
CUSTOMERS
PK
ID
FirstName
LastName
DateOfFirstPurchase
PEOPLE

Filtered Integration

PEOPLE
PK
ID
FirstName
LastName
Department
DateOfFirstPurchase
Filtered Integration

PEOPLE
PK
ID
FirstName
LastName
Department
DateOfFirstPurchase
EMPLOYEES

Filtered Integration

PEOPLE
PK
ID
FirstName
LastName
Department
DateOfFirstPurchase
CUSTOMERS

Deciding Factors


How does your business define
entities?


People vs Customers/Employees


What will you report on most?


NULL’s allowed or Not?


Design impacts performance


Alternate Example: Car Dealership


Alternate Solution: XML

Over
-
Normalization


1NF


Primary Key


Remove duplicate columns


2NF


Remove subsets of data


Foreign Key relationships


3NF


Remove columns independent of primary key

Over
-
Normalization


Normalization stops at the Entity level


NO CONCEPT OF INHERITENCE IN SQL


Temptation is to have a “root” entity


Use 1
-
to
-
1 JOINS for all other entities


OBJECTS (ID, name, description)


CAR (ID, WheelID)


WHEELS (ID)

Over
-
Normalization

SELECT o1.name, o1.description,


o2.name, o2.description

FROM Cars c JOIN Objects o1 ON c.ID=o1.ID


JOIN Wheels w ON c.WheelID=w.ID


JOIN Objects o2 ON w.ID=o2.ID


SELECT c.name, c.description,




w.name, w.description

FROM Cars c JOIN Wheels w ON c.WheelID=w.ID

Five Common Issues


Data Access Layer Debate


Inheritance Design


Iteration


Triggers


CLR misconceptions

Iteration


In OOP design, the base unit is the
object


A set is represented by a collection


DataTable is collection of DataRows


DataSet is collection of DataTables


Property inspection is iterative


Row(0) to Rows.Count
-
1


Data Retrieval: Geographic orientation


Row(100), MoveNext


Iteration


In ERD, the base unit is the table


A set is represented by the table


A row is a set of 1, which is a subset of the
containing table


Data is filtered by JOINS and WHERE clause


Tables are order
-
less


No Row(0)


Data Retrieval: Content Orientation


WHERE id = 1

Iteration


In ERD implementations, iteration
should be nominal


Cursors are performance killers


WHILE loops should be unnecessary


SQL is declarative language


Things happen “all
-
at
-
once”

Iteration


Typical Iterative Example


Complex business logic affecting one row
of data at a time


Cursor calls that stored procedure for
every row in a table

Five Common Issues


Data Access Layer Debate


Inheritance Design


Iteration


Triggers


CLR misconceptions

Triggers


Most OOP developers love triggers


Analogous to Event handling


Most SQL developers dislike triggers


Keyhole Drill Bit; sometimes you HAVE to
use them.


Most DBA’s hate triggers


Voodoo design.

Five Common Issues


Data Access Layer Debate


Inheritance Design


Iteration


Triggers


CLR misconceptions

CLR Concerns


SQL 2005


Embedded CLR


OOP Developers reuse business objects in
app code; why not SQL CLR?


Different purposes (lower Layers of Design)


Objects in database are NOT part of GAC


SQL CLR belongs to database


Akin to XCOPY deployment


Object synchronicity issues

CLR Concerns


.NET Stored Procedures


Slower than T
-
SQL


Yet to find a good example of why they
are necessary


UDF Functions for formatting


Better system wide data collection


String munging

Perspectives

The future will be better
tomorrow.

-
Dan Quayle

Future Technologies


LINQ


SQL
-
like syntax for heterogeneous
datasources; Apps would become
datasource
-
agnostic


Database is an object; Tables are objects


O/RM


Thin layer between ADO.NET and
application, mapping objects to entities

Design Perspectives


Rod Paddock


Wither T
-
SQL


LINQ & CLR need to replace T
-
SQL


Jim Gray


Databases as Objects


Business logic moved to data storage


Adam Machanic


SP Interfaces


Stored Procedures act as OOP interfaces


Paul Nielsen


Nordic O/R design


OOP emulation in T
-
SQL

DATA STORAGE LAYER

DATA ACCESS LAYER

BUSINESS OBJECTS LAYER

APPLICATION/INTERFACE LAYER

Layered Design

Questions?

Nothing endures but
change.

-
Heraclitus