620 likes | 866 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
E N D
Object-Oriented Programming & Relational Design Clash of the Paradigms…. Stuart R AinsworthGladiator Technology ServicesatlantaMDF June 12, 2006
About Me • American Cancer SocietyBehavior 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
Layered Design APPLICATION/INTERFACE LAYER BUSINESS OBJECTS LAYER DATA ACCESS LAYER DATA STORAGE LAYER
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.”
Relational Optimize data storage Optimize data retrieval Data-centric efficiency OOP Optimize development time Optimize application support Coder-centric efficiency Historical Comparison
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 • 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
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)
Definitions • PropertiesAttributes of class; descriptive Public Class Book Public Property Title()as String… Get… Set… End Class fairyTales.Title = “Goldilocks”
Definitions • EventsA 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 • EncapsulationInternal functionality of object is hidden from callers. “Black box” programming. • Loose coupling No assumptions between applications or objects
Definitions • EncapsulationInternal functionality of object is hidden from callers. “Black box” programming. • Loose coupling No assumptions between applications or objects
Definitions • HierarchySuperclasses & subclasses; objects relate to each other in a hierarchical fashion (e.g., ducks are birds, which are animals, which are organisms…)
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
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
Definitions • OverridingSubclass methods are used before Superclass methods. • PolymorphismCommon 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
DataSets ADO.NET Stored Procs TABLES Layered Design APPLICATION/INTERFACE LAYER BUSINESS OBJECTS LAYER DATA ACCESS LAYER DATA STORAGE LAYER
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
PEOPLE Horizontal Integration
Filtered Integration EMPLOYEES
Filtered Integration 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