ObjectOriented Programming

1
Object-Oriented Programming Relational Design

• Clash of the Paradigms.

Stuart R AinsworthGladiator Technology
ServicesatlantaMDF June 12, 2006
2
About Me

• American Cancer SocietyBehavior Research Center
• Research Analyst, 1995-1998
• Project Manager, 1998-2000
• Onity
• Report Analyst, 2000-2001
• Database Administrator, 2001-2002

3
About Me

• Gladiator Technology Services, Inc
• Lead Developer, 2002-2003
• Senior DBA, 2003-2005
• Database Developer, 2005-Present

4
Goals

• Introduce basic concepts of OOP
• Compare Contrast OOP and ERD
• Historical
• Design Implications
• Discuss Relevance to SQL Pros

5
Goals

• Provide Concepts Code
• Design Scenarios
• Prognosticate on the future of design

6
Precepts of Good Design

• Isolate database development from application
  development.
• Encapsulation
• Loose Coupling

7
Layered Design
APPLICATION/INTERFACE LAYER
BUSINESS OBJECTS LAYER
DATA ACCESS LAYER
DATA STORAGE LAYER
8
History Lesson

• Set the WABAC machine, Sherman!
• -Mr. Peabody

9
History-Rel Paradigm

• Relational design based on work of E.F.Codd
• A Relational Model of Data For Large Shared Data
  Banks 1970 ACM
• Codds 12 Rules for Relational DBs (1985)
• Implementation
• Ingres (1974)
• Relational Software (Oracle 1979)

10
History-Rel Paradigm

• Context
• Hierarchical databases prevalent
• Tree structure
• Redundant data in attributes
• Expense of computer hardware
• Limited storage capability
• Limited expansion possibilities

11
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.

12
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.

13
History-OOP

• Alan Kay
• Smalltalk (1971)
• Biological metaphors cells in a body
• Building blocks code reduction
• Recent Work
• http//www.squeakland.org/

14
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.

15
Historical Comparison

• Relational
• Optimize data storage
• Optimize data retrieval
• Data-centric efficiency

• OOP
• Optimize development time
• Optimize application support
• Coder-centric efficiency

16
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
17
OOP Coding Concepts

• All programmers are playwrights and all computers
  are lousy actors.
• -Unknown

18
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
19
Definitions

• ObjectInstance 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
20
Definitions

• MethodsVerbs associated with classes actions
  performed by objects

Public Class Book Public Function GetPage End
Class
Dim TextOnPage6 as String TextOnPage6
fairyTales.GetPage(6)
21
Definitions

• PropertiesAttributes of class descriptive

Public Class Book Public Property Title()as
String Get Set End Class
fairyTales.Title Goldilocks
22
Definitions

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

23
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
24
Definitions

• EncapsulationInternal functionality of object is
  hidden from callers. Black box programming.
• Loose coupling
• No assumptions between applications or objects

25
Definitions

• EncapsulationInternal functionality of object is
  hidden from callers. Black box programming.
• Loose coupling
• No assumptions between applications or objects

26
Definitions

• HierarchySuperclasses subclasses objects
  relate to each other in a hierarchical fashion
  (e.g., ducks are birds, which are animals, which
  are organisms)

27
Definitions

• InheritanceProcess by which a subclass is
  derived from a superclass methods in particular
  are inherited.
• Ducks Fly() because Birds Fly(), and Ducks
  inherit from Birds

28
Definitions

• InheritanceProcess by which a subclass is
  derived from a superclass methods in particular
  are inherited.
• Ducks Fly() because Birds Fly(), and Ducks
  inherit from Birds

29
Definitions

• OverridingSubclass methods are used before
  Superclass methods.
• PolymorphismCommon method across different
  objects with different implementations.Ducks
  Swim(), Fish Swim()

30
Where are we?

• OOP Developers are bored.
• SQL Developers are challenged.
• DBAs are wondering how the systems are doing
  back at the office.

31
Common OOP/RP Clashes

• Computers are useless. They can only give you
  answers.
• -Pablo Picasso

32
Five Common Issues

• Data Access Layer Debate
• Inheritance Design
• Iteration
• Triggers
• CLR misconceptions

33
Layered Design
APPLICATION/INTERFACE LAYER
BUSINESS OBJECTS LAYER
DATA ACCESS LAYER
DATA STORAGE LAYER
34
DAL Debate

• Role of Database in DAL
• Stored Procedures
• Security
• Typically Parameterized
• Encapsulation

35
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

36
Five Common Issues

• Data Access Layer Debate
• Inheritance Design
• Iteration
• Triggers
• CLR misconceptions

37
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 ltgt reality

38
Inheritance in ERD

• Vertical Mapping
• 1-1 Joins Common Attributes grouped as a single
  entity.
• Horizontal Mapping
• Classic ERD EntitiesTables, regardless of
  inheritance.
• Filter Mapping
• Single Parent Entity allow NULL values

39
Vertical Integration
40
Horizontal Integration
41
Filtered Integration
42
Filtered Integration
EMPLOYEES
43
Filtered Integration
CUSTOMERS
44
Deciding Factors

• How does your business define entities?
• People vs Customers/Employees
• What will you report on most?
• NULLs allowed or Not?
• Design impacts performance
• Alternate Example Car Dealership
• Alternate Solution XML

45
Over-Normalization

• 1NF
• Primary Key
• Remove duplicate columns
• 2NF
• Remove subsets of data
• Foreign Key relationships
• 3NF
• Remove columns independent of primary key

46
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)

47
Over-Normalization

• SELECT o1.name, o1.description,
• o2.name, o2.description
• FROM Cars c JOIN Objects o1 ON c.IDo1.ID
• JOIN Wheels w ON c.WheelIDw.ID
• JOIN Objects o2 ON w.IDo2.ID
• SELECT c.name, c.description,
• w.name, w.description
• FROM Cars c JOIN Wheels w ON c.WheelIDw.ID

48
Five Common Issues

• Data Access Layer Debate
• Inheritance Design
• Iteration
• Triggers
• CLR misconceptions

49
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

50
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

51
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

52
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

53
Five Common Issues

• Data Access Layer Debate
• Inheritance Design
• Iteration
• Triggers
• CLR misconceptions

54
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 DBAs hate triggers
• Voodoo design.

55
Five Common Issues

• Data Access Layer Debate
• Inheritance Design
• Iteration
• Triggers
• CLR misconceptions

56
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

57
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

58
Perspectives

• The future will be better tomorrow.
• -Dan Quayle

59
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

60
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

61
Layered Design
APPLICATION/INTERFACE LAYER
BUSINESS OBJECTS LAYER
DATA ACCESS LAYER
DATA STORAGE LAYER
62
Questions?

• Nothing endures but change.
• -Heraclitus