object-oriented programming & relational design clash of the paradigms…. stuart r ainsworth...

Object-Oriented Object-Oriented Programming & Relational Programming & Relational DesignDesign

Clash of the Paradigms….Clash of the Paradigms….

Stuart R AinsworthGladiator Technology ServicesatlantaMDFJune 12, 2006

About MeAbout Me

American Cancer SocietyAmerican Cancer SocietyBehavior Research CenterBehavior Research Center– Research Analyst, 1995-1998Research Analyst, 1995-1998– Project Manager, 1998-2000Project Manager, 1998-2000

OnityOnity– Report Analyst, 2000-2001Report Analyst, 2000-2001– Database Administrator, 2001-2002Database Administrator, 2001-2002

About MeAbout Me

Gladiator Technology Services, Gladiator Technology Services, IncInc– Lead Developer, 2002-2003Lead Developer, 2002-2003– Senior DBA, 2003-2005Senior DBA, 2003-2005– Database Developer, 2005-PresentDatabase Developer, 2005-Present

GoalsGoals

Introduce basic concepts of OOPIntroduce basic concepts of OOP Compare & Contrast OOP and Compare & Contrast OOP and

ERDERD– HistoricalHistorical– Design ImplicationsDesign Implications

Discuss Relevance to SQL Pro’sDiscuss Relevance to SQL Pro’s

GoalsGoals

Provide Concepts & CodeProvide Concepts & Code Design ScenariosDesign Scenarios Prognosticate on the future of Prognosticate on the future of

designdesign

Precepts of Good Precepts of Good DesignDesign Isolate database development Isolate database development

from application development.from application development. EncapsulationEncapsulation Loose CouplingLoose Coupling

DATA STORAGE LAYER

DATA ACCESS LAYER

BUSINESS OBJECTS LAYER

APPLICATION/INTERFACE LAYER

Layered DesignLayered Design

History LessonHistory Lesson

““Set the WABAC Set the WABAC machine, Sherman!”machine, Sherman!”

-Mr. Peabody-Mr. Peabody

History-Rel ParadigmHistory-Rel Paradigm

Relational design based on work of Relational design based on work of E.F.CoddE.F.Codd– A Relational Model of Data For Large A Relational Model of Data For Large

Shared Data Banks – 1970 ACMShared Data Banks – 1970 ACM– Codd’s 12 Rules for Relational DB’s (1985)Codd’s 12 Rules for Relational DB’s (1985)

ImplementationImplementation– Ingres (1974)Ingres (1974)– Relational Software (Oracle; 1979)Relational Software (Oracle; 1979)


Context:Context:– Hierarchical databases prevalentHierarchical databases prevalent

Tree structureTree structure Redundant data in attributesRedundant data in attributes

– Expense of computer hardwareExpense of computer hardware Limited storage capabilityLimited storage capability Limited expansion possibilitiesLimited expansion possibilities


“ “By the time UNIX began to become popular By the time UNIX began to become popular (1974), a well configured PDP-11 had 768 Kb (1974), a well configured PDP-11 had 768 Kb of core memory, of core memory, two 200 Mb moving head two 200 Mb moving head disks (hard disks),disks (hard disks), a reel to reel tape drive a reel to reel tape drive for backup purposes, a dot-matrix line printer for backup purposes, a dot-matrix line printer and a bunch of [dumb] terminals. This was a and a bunch of [dumb] terminals. This was a high end machine, and even a minimally high end machine, and even a minimally configured PDP-11 cost about configured PDP-11 cost about $40,000$40,000. . Despite the cost, 600 such installations had Despite the cost, 600 such installations had been put into service by the end of 1974, been put into service by the end of 1974,

mostly at universities.”mostly at universities.”


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

History-OOPHistory-OOP

Alan KayAlan Kay– Smalltalk (1971)Smalltalk (1971)– Biological metaphors; cells in a bodyBiological metaphors; cells in a body– Building blocks; code reductionBuilding blocks; code reduction– Recent WorkRecent Work

http://www.squeakland.org/http://www.squeakland.org/

History-OOPHistory-OOP

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

Historical ComparisonHistorical Comparison

RelationalRelational– Optimize data Optimize data

storagestorage– Optimize data Optimize data

retrievalretrieval

Data-centric Data-centric efficiencyefficiency

OOPOOP– Optimize Optimize

development timedevelopment time– Optimize Optimize

application supportapplication support

Coder-centric Coder-centric efficiencyefficiency

O/R ImpedenceO/R Impedence

““The object-oriented paradigm is based on The object-oriented paradigm is based on proven software engineering principles. The proven software engineering principles. The relational paradigm, however, is based on relational paradigm, however, is based on proven mathematical principles. Because the proven mathematical principles. Because the underlying paradigms are different the two underlying paradigms are different the two technologies do not work together technologies do not work together seamlessly. The impedance mismatch seamlessly. The impedance mismatch becomes apparent when you look at the becomes apparent when you look at the preferred approach to access: With the object preferred approach to access: With the object paradigm you traverse objects via their paradigm you traverse objects via their relationships whereas with the relational relationships whereas with the relational paradigm you join the data rows of tables. ”paradigm you join the data rows of tables. ”

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

OOP Coding ConceptsOOP Coding Concepts

All programmers are All programmers are playwrights and all playwrights and all computers are lousy computers are lousy actors.actors.

-Unknown-Unknown

DefinitionsDefinitions

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

Public Class Book

Public Text as String = “”

Public PageLength as Integer = 10

End Class


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

Dim fairyTales as Book

fairyTales = New Book()

fairyTales.Text = “Once upon a time…”

fairyTales.PageLength = 8


MethodsMethodsVerbs associated with classes; Verbs associated with classes; actions performed by objectsactions performed by objects

Public Class Book

Public Function GetPage…

End Class

Dim TextOnPage6 as String = “”

TextOnPage6 = fairyTales.GetPage(6)


PropertiesPropertiesAttributes of class; descriptiveAttributes of class; descriptive

Public Class Book

Public Property Title()as String…

Get…

Set…

End Class

fairyTales.Title = “Goldilocks”


EventsEventsA signal that an action (method) A signal that an action (method) has occurred. Events are has occurred. Events are handled.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 SubEnd Class


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

Loose couplingLoose coupling

No assumptions between No assumptions between applications or objectsapplications or objects


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

Loose couplingLoose coupling

No assumptions between No assumptions between applications or objectsapplications or objects


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


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

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


OverridingOverridingSubclass methods are used Subclass methods are used before Superclass methods.before Superclass methods.

PolymorphismPolymorphismCommon method across different Common method across different objects with different objects with different implementations.implementations.Ducks Swim(), Fish Swim()Ducks Swim(), Fish Swim()

Where are we?Where are we?

OOP Developers are bored.OOP Developers are bored. SQL Developers are challenged.SQL Developers are challenged. DBA’s are wondering how the DBA’s are wondering how the

systems are doing back at the systems are doing back at the office.office.

Common OOP/RP Common OOP/RP ClashesClashes

Computers are useless. Computers are useless. They can only give you They can only give you answers.answers.

-Pablo Picasso-Pablo Picasso

Five Common IssuesFive Common Issues

Data Access Layer DebateData Access Layer Debate Inheritance DesignInheritance Design IterationIteration TriggersTriggers CLR misconceptionsCLR misconceptions

DATA STORAGE LAYER

DATA ACCESS LAYER




Stored Procs

TABLES

DataSets

ADO.NET

DAL DebateDAL Debate

Role of Database in DALRole of Database in DAL– Stored ProceduresStored Procedures

SecuritySecurity Typically ParameterizedTypically Parameterized EncapsulationEncapsulation

DAL DebateDAL Debate

Role of OOP in DALRole of OOP in DAL– Transformation from data to data Transformation from data to data

objectsobjects ADO.NET, ADO, RDOADO.NET, ADO, RDO

– Parameterized SQLParameterized SQL Sometimes necessarySometimes necessary Developers must collaborate with DBADevelopers must collaborate with DBA Raises encapsulation concernsRaises encapsulation concerns

Inheritance in ERDInheritance in ERD

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

Like objects, entities are nouns:Like objects, entities are nouns:– ““We sell cars and trucks.”We sell cars and trucks.”– Entities DO NOT INHERIT from entitiesEntities DO NOT INHERIT from entities

Can be emulatedCan be emulated Emulation <> reality Emulation <> reality

Inheritance in ERDInheritance in ERD

Vertical MappingVertical Mapping– 1-1 Joins; Common Attributes 1-1 Joins; Common Attributes

grouped as a single entity.grouped as a single entity. Horizontal MappingHorizontal Mapping

– Classic ERD; Entities=Tables, Classic ERD; Entities=Tables, regardless of inheritance.regardless of inheritance.

Filter MappingFilter Mapping– Single Parent Entity; allow NULL Single Parent Entity; allow NULL

valuesvalues

Vertical IntegrationVertical Integration

PEOPLE

PK ID

FirstNameLastName

EMPLOYEES

PK,FK1 ID

Department

CUSTOMERS

PK,FK1 ID

DateOfFirstPurchase

Horizontal IntegrationHorizontal Integration

EMPLOYEES

PK ID

FirstNameLastNameDepartment

CUSTOMERS

PK ID

FirstNameLastNameDateOfFirstPurchase

PEOPLE

Filtered IntegrationFiltered Integration

PEOPLE

PK ID

FirstNameLastNameDepartmentDateOfFirstPurchase


PEOPLE

PK ID


EMPLOYEES


PEOPLE

PK ID


CUSTOMERS

Deciding FactorsDeciding Factors

How does your business define How does your business define entities?entities?– People vs Customers/EmployeesPeople vs Customers/Employees

What will you report on most?What will you report on most? NULL’s allowed or Not?NULL’s allowed or Not?

– Design impacts performanceDesign impacts performance Alternate Example: Car DealershipAlternate Example: Car Dealership Alternate Solution: XMLAlternate Solution: XML

Over-NormalizationOver-Normalization

1NF1NF– Primary KeyPrimary Key– Remove duplicate columnsRemove duplicate columns

2NF2NF– Remove subsets of dataRemove subsets of data– Foreign Key relationshipsForeign Key relationships

3NF3NF– Remove columns independent of primary Remove columns independent of primary

keykey


Normalization stops at the Entity Normalization stops at the Entity levellevel– NO CONCEPT OF INHERITENCE IN SQLNO CONCEPT OF INHERITENCE IN SQL

Temptation is to have a “root” Temptation is to have a “root” entityentity– Use 1-to-1 JOINS for all other entitiesUse 1-to-1 JOINS for all other entities

OBJECTS (ID, name, description)OBJECTS (ID, name, description) CAR (ID, WheelID)CAR (ID, WheelID) WHEELS (ID)WHEELS (ID)


SELECT o1.name, o1.description,SELECT o1.name, o1.description, o2.name, o2.descriptiono2.name, o2.descriptionFROM Cars c JOIN Objects o1 ON c.ID=o1.IDFROM Cars c JOIN Objects o1 ON c.ID=o1.ID JOIN Wheels w ON c.WheelID=w.IDJOIN Wheels w ON c.WheelID=w.ID JOIN Objects o2 ON w.ID=o2.IDJOIN Objects o2 ON w.ID=o2.ID

SELECT c.name, c.description,SELECT c.name, c.description,w.name, w.descriptionw.name, w.description

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

IterationIteration

In OOP design, the base unit is the In OOP design, the base unit is the objectobject– A set is represented by a collectionA set is represented by a collection

DataTable is collection of DataRowsDataTable is collection of DataRows DataSet is collection of DataTablesDataSet is collection of DataTables

– Property inspection is iterativeProperty inspection is iterative Row(0) to Rows.Count-1Row(0) to Rows.Count-1 Data Retrieval: Geographic orientationData Retrieval: Geographic orientation

– Row(100), MoveNextRow(100), MoveNext

IterationIteration

In ERD, the base unit is the tableIn ERD, the base unit is the table– A set is represented by the tableA set is represented by the table

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

Data is filtered by JOINS and WHERE clauseData is filtered by JOINS and WHERE clause

– Tables are order-lessTables are order-less No Row(0)No Row(0) Data Retrieval: Content OrientationData Retrieval: Content Orientation

– WHERE id = 1WHERE id = 1

IterationIteration

In ERD implementations, iteration In ERD implementations, iteration should be nominalshould be nominal– Cursors are performance killersCursors are performance killers– WHILE loops should be unnecessaryWHILE loops should be unnecessary

SQL is declarative languageSQL is declarative language– Things happen “all-at-once”Things happen “all-at-once”

IterationIteration

Typical Iterative ExampleTypical Iterative Example– Complex business logic affecting Complex business logic affecting

one row of data at a timeone row of data at a time– Cursor calls that stored procedure Cursor calls that stored procedure

for every row in a tablefor every row in a table

TriggersTriggers

Most OOP developers love triggersMost OOP developers love triggers– Analogous to Event handlingAnalogous to Event handling

Most SQL developers dislike triggersMost SQL developers dislike triggers– Keyhole Drill Bit; sometimes you HAVE Keyhole Drill Bit; sometimes you HAVE

to use them.to use them. Most DBA’s hate triggersMost DBA’s hate triggers

– Voodoo design. Voodoo design.

CLR ConcernsCLR Concerns

SQL 2005 – Embedded CLRSQL 2005 – Embedded CLR– OOP Developers reuse business objects OOP Developers reuse business objects

in app code; why not SQL CLR?in app code; why not SQL CLR? Different purposes (lower Layers of Design)Different purposes (lower Layers of Design) Objects in database are NOT part of GACObjects in database are NOT part of GAC

– SQL CLR belongs to databaseSQL CLR belongs to database– Akin to XCOPY deploymentAkin to XCOPY deployment– Object synchronicity issuesObject synchronicity issues

CLR ConcernsCLR Concerns

.NET Stored Procedures.NET Stored Procedures– Slower than T-SQLSlower than T-SQL– Yet to find a good example of why Yet to find a good example of why

they are necessarythey are necessary UDF Functions for formattingUDF Functions for formatting Better system wide data collectionBetter system wide data collection String mungingString munging

PerspectivesPerspectives

The future will be better The future will be better tomorrow.tomorrow.

-Dan Quayle-Dan Quayle

Future TechnologiesFuture Technologies

LINQLINQ– SQL-like syntax for heterogeneous SQL-like syntax for heterogeneous

datasources; Apps would become datasources; Apps would become datasource-agnosticdatasource-agnostic

– Database is an object; Tables are Database is an object; Tables are objectsobjects

O/RMO/RM– Thin layer between ADO.NET and Thin layer between ADO.NET and

application, mapping objects to entitiesapplication, mapping objects to entities

Design PerspectivesDesign Perspectives

Rod Paddock – Wither T-SQLRod Paddock – Wither T-SQL– LINQ & CLR need to replace T-SQLLINQ & CLR need to replace T-SQL

Jim Gray – Databases as ObjectsJim Gray – Databases as Objects– Business logic moved to data storageBusiness logic moved to data storage

Adam Machanic – SP InterfacesAdam Machanic – SP Interfaces– Stored Procedures act as OOP Stored Procedures act as OOP

interfacesinterfaces Paul Nielsen – Nordic O/R designPaul Nielsen – Nordic O/R design

– OOP emulation in T-SQLOOP emulation in T-SQL

DATA STORAGE LAYER

DATA ACCESS LAYER




Questions?Questions?

Nothing endures but Nothing endures but change.change.

-Heraclitus-Heraclitus

object-oriented programming & relational design clash of the paradigms…. stuart r ainsworth...

Documents

future of design slide

present slide

peabody slide

codd relational design

relational model of

head disks hard disks

configured pdp

historyrel paradigm