Object-Oriented Programming & Relational Design

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 Pro’s

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 • Codd’s 12 Rules for Relational DB’s (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. Relational Optimize data storage Optimize data retrieval Data-centric efficiency OOP Optimize development time Optimize application support Coder-centric efficiency Historical Comparison

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. • DBA’s 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. DataSets ADO.NET Stored Procs TABLES 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 <> reality

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

39. Vertical Integration

40. PEOPLE 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? • NULL’s 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.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

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