Base Data Types

1. Numbers Integers Reals Text Length International Date/Time Images Bitmap Vector Sound Samples MIDI Video Base Data Types Input Process Output Numbers, Text, and Dates 20 000001100 000001000 12 + 8 = 20 ---------------- 000010100 0010000000000000000 0100000000000001001 0110000011000011011 0111111111111001111 Images 1111111111111011111 1111111111100011111 pitch, volume Sound 8 9 20 7 8 19 5 6 15 time 000001000 000001001 000010100 ..... 00101010111 00101010111 00101010111 Video 11010101010 11010101010 11010101010 01010101010 01010101010 01010101010 11110100011 11110100011 11110100011 00101011011 00101011011 00101011011 00101010111 00101010111 11010101010 11010101010 01010101010 01010101010 11110100011 11110100011 00101011011 00101011011

2. Object Definition--encapsulation. Object Name Properties Methods Most existing DBMS do not handle inheritance. Combine into one table. Use multiple tables and link by primary key. More efficient. Need to add rows to many tables. Customer CustomerID Address Phone AddCustomer DropCustomer Commercial Government Contact VolumeDiscount Contact BalanceDue ComputeDiscount BillLateFees AddCustomer Objects Class name Properties Methods Inheritance Polymorphism

3. Separate inherited classes. Link by primary key. Adding a new customer requires new rows in each table. Definitely need cascade delete. Objects in a Relational Database Customer CustomerID Address Phone CommercialCustomer GovernmentCustomer CustomerID Contact VolumeDiscount CustomerID Contact BalanceDue

4. OO Difficulties: Methods IBM Server Unix Server Database Object Personal Computer Database Object How can a method run on different computers? Different processors use different code. Possibility: Java Customer Method: Add New Customer Application Customer Name Address Phone Program code

5. Abstract data type User defined data types. Equality and ordering functions. Encapsulation: Public, Private, Protected. Inheritance. Sub-tables that inherit all columns from another table. Persistent Stored Modules (Programming Language). Create methods. SQL and extensions. External language. User defined operators. Triggers for events. External language support Call-Level Interface (CLI) Direct access to DBMS Embedded SQL SQL commands in an external language. SQL3: OO Features

6. Abstract Data Types GeoPoint Latitude Longitude Altitude Procedure: DrawRegion { Find region components. SQL: Select … For each component { Fetch MapLine Set line attributes MapLine.Draw } } GeoLine NumberOfPoints ListOfGeoPoints

7. SQL3 Sub-Tables CREATE SET TABLE Customer ( CustomerID INTEGER, Address VARCHAR, Phone CHAR(15) ) Customer CustomerID Address Phone Inherits columns from Customer. CREATE SET TABLE CommercialCustomer ( Contact VARCHAR, VolumeDiscount NUMERIC(5,2) ) UNDER Customer; CommercialCustomer Contact VolumeDiscount

8. Nested Tables CREATE TYPE Person AS OBJECT ( LastName VARCHAR2(15), FirstName VARCHAR2(15), Phone VARCHAR2(15) ); CREATE TYPE SaleItem AS OBJECT ( ItemID NUMBER, Quantity NUMBER, Price NUMBER); CREATE TYPE Sale AS OBJECT ( SaleID NUMBER, Customer PERSON, SaleItems SaleItem ); Then, create the actual table: CREATE TABLE Sale_table OF Sale NESTED TABLE SaleItems STORE AS SaleItems_table;

9. SQL3: Programming Database External Programs Data Types Tables, … Embedded SQL Call-Level Interface Persistent Stored Modules SQL Extended SQL code External language code CURSOR … SELECT … FETCH …

10. OODBMS Vendors GemStone Systems, Inc. Hewlett-Packard, Inc. (OpenODB) IBEX Corporation, SA. Illustra (Informix, Inc.) Matisse Software, Inc. O2 Technology, Inc. Objectivity, Inc. Object Design, Inc. ONTOS, Inc. POET Software Corporation UniSQL Unisys Corporation (OSMOS) Versant Object Technology

11. Choose the best tool for the job. DBMS: Store, retrieve, and share data. Spreadsheet: Computations, analysis, and graphs. Word processor: Formatting, pagination, and reports. Graphics: Charts and presentations. Calendars and Project management: Scheduling. Integration Linking and sharing data objects. Setting object properties. Calling object methods. Manual integration Copy objects by hand. Automatic integration Dynamically link objects. Program access to objects. Integrated Applications

12. Accessing Application Objects Database Application Retrieve data SELECT…. GROUP BY … Statistical calculations. Transfer to spreadsheet. Execute statistical routines. Retrieve and store results. Spreadsheet Application Put data in cells. Perform regression analysis.

13. Copy data from one application to another. Server Container Changing the original does not affect the copy. Steps to create: In original data Mark data objects Select Edit | Copy In container document Move to insert location Select Edit | Paste or Edit | Paste Special Static Data Links Database table Container Document e.g., Spreadsheet copy Embedded object Query results

14. Original data file and compound document are linked. If the original data file is changed, the linked document automatically updates the content. Can have multiple links. User must have access to all of the applications. Dynamic Links Database Query Link Spreadsheet Sheet Graph Word processor Final document Table Graph

15. Storing Objects in the Database An Access form that holds revisions of a spreadsheet object.

16. Programming Links Dim wsExcel As Object Dim dbl As Double Set wsExcel = CreateObject("Excel.Sheet") ' Start Excel wsExcel.Application.Visible = True ' (optional) make Excel visible ' Tell Excel to gen random data wsExcel.ActiveSheet.Range("A1:A8").Formula = "=NORMSINV(RAND())” wsExcel.ActiveSheet.Cells(9,1).Formula = "=Sum(A1:A8)” ' Compute the total dbl = wsExcel.ActiveSheet.Range("A9").Value ' Return the result MsgBox dbl ' Temporary test, display value wsExcel.SaveAs strFileName ' (optional) Save Worksheet wsExcel.DisplayAlerts = False ' Stop “unsaved” warnings wsExcel.Quit ' (optional)Close Excel Set wsExcel = Nothing ' Free up memory (in Access)

17. The Object Browser Displays objects, properties, and methods from other software. Only available from the code window. Must first set Tools | References and check the software package (e.g., Microsoft Excel).

18. Examples Database AccountValue ChartOfAccounts AccountID Date Value AccountID Title Description Summary Statement Level Retrieve current data. Past data. Spreadsheet 2000 Balance Sheet 2000 Income Statement Forecast 2001 Balance Sheet 2001 Income Statement

19. Financial Forecast Excel Crosstab query linked to spreadsheet: =MSAccess|'C12Finance.mdb;Query Query4'!All

20. Word Report Example Start Word. Add a document. Set tab stops. Open query. Read each row. Format and “Print” to Word. End Loop. Close Query. Define Footer. Save and Close Word document. Clear variables. Access

21. Word Report: Setup Dim objWord As Word.Application Dim cnn As ADODB.Connection Dim rst As ADODB.Recordset Dim rngfoot As Variant If (Tasks.Exists("Microsoft Word") = True) Then Set objWord = GetObject(, "Word.Application") Else Set objWord = CreateObject("Word.Application") End If objWord.Visible = True objWord.Documents.Add Set dbs = CurrentProject.Connection Set rst = CreateObject(“ADODB.Recordset”) rst.Open “Select * From Query5”,cnn Define variables. Start Word. Add a document.

22. Word Report: Loop ' Set the tabs for the columns Selection.ParagraphFormat.TabStops.Add Position:=InchesToPoints(0.5), _ Alignment:=wdAlignTabLeft, Leader:=wdTabLeaderSpaces Selection.ParagraphFormat.TabStops.Add Position:=InchesToPoints(3), _ Alignment:=wdAlignTabRight, Leader:=wdTabLeaderSpaces ' Read all of the data rows and put them into the document Do While Not rst.EOF Selection.TypeText Text:=rst(“AccountID”) & vbTab & rst(“Title”) _ & vbTab & Format(rst(“Value”), "Currency") Selection.TypeParagraph rst.MoveNext Loop rst.Close Set tab stops. Open query. Read each row. Format and “Print” to Word. End Loop. Close query.

23. Word Report: Footer ' Add a footer for each page with File name and creation Date Set rngfoot = objWord.ActiveDocument.Sections(1). _ Footers(wdHeaderFooterPrimary).Range With rngfoot .Delete .Fields.Add Range:=rngfoot, Type:=wdFieldFileName, Text:="\p" .InsertAfter Text:=vbTab & vbTab .Collapse Direction:=wdCollapseStart .Fields.Add Range:=rngfoot, Type:=wdFieldCreateDate End With 'objWord.Documents.Save 'objWord.Close Set objWord = Nothing Define Footer. Save and Close Word document. Clear variables.

24. Word Report: Output Footer 6/15/01 Accounting Summary In practice, you will add more code for conditions and formatting.

25. MS Office 97/Visual Basic Programmer’s Guide ISBN: 1-57231-340-4 http://www.microsoft.com With (long object) .property .method End With For Each c IN Range c.Do Something Next c Office Integration Tips For Each c in wsExcel.ActiveSheet.Range("A1:F8") If Abs(c.Value) < 0.01 Then c.Value = 0 Next

26. Sally’s Pet Store: Income Statement Retrieve from database Entered by hand Formula for calculations should be generated by the template.

27. Pet Store: Income Statement Form Need code to (1) Start Excel (2) Enter template Text/labels Calculations (3) Use SQL to total sales Within given dates Transfer to spreadsheet

28. Pet Store: Open Excel Global goExcel As Excel.Application Public Function OpenMSExcel() As Variant On Error Resume Next Set goExcel = GetObject(, "Excel.Application") If (goExcel Is Nothing) Then Set goExcel = New Excel.Application End If If (goExcel Is Nothing) Then MsgBox "Can't start Excel", , "Unexpected Error" OpenMSExcel = False Else If (Not goExcel.Visible) Then goExcel.Visible = True End If OpenMSExcel = True End If DoEvents End Function

29. Pet Store: Code to Build Template Private Sub cmdIncome_Click() Dim cnn As ADODB.Connection ‘ Declare variables If Not OpenMSExcel() Then ' Open Excel if we can Exit Sub End If On Error GoTo Err_cmdIncome_Click goExcel.Workbooks.Add ' Create a new workbook With goExcel.ActiveSheet ' Which sets a default sheet .Cells(1, 1).ColumnWidth = 30.5 ' Set up the basic template/text .Cells(1, 1).Value = "Sally's Pet Store" .Cells(1, 1).Font.Bold = True .Cells(8, 2).Value = "=B6+B7” ‘ One of many calculations .Range("B6:B28").Select ‘ Format the cells goExcel.Selection.NumberFormat = "$#,##0.00;($#,##0.00)" .Range("B2,B4").Select goExcel.Selection.NumberFormat = "m/d/yy"

30. Pet Store: Code to Retrieve Data strWhere = "Between #" & [StartDate] & "# And #" & [EndDate] & "#);" ' First do the Animal Sales strSQL = "SELECT Sum(SaleAnimal.SalePrice) AS SumOfSalePrice " strSQL = strSQL & "FROM Sale INNER JOIN SaleAnimal ON Sale.SaleID = SaleAnimal.SaleID " strSQL = strSQL & "WHERE (Sale.SaleDate " & strWhere Set cnn = CurrentProject.Connection Set rst = CreateObject(“ADODB.Recordset”) rst.Open strSQL, cnn, adOpenStatic, adLockReadOnly rst.MoveFirst .Cells(6, 2).Value = rst(“SumOfSalePrice”) rst.Close ' Second do the Merchandise sales ' Third do the Animal purchases ' Fourth do the Merchandise purchases End With Exit_cmdIncome_Click: Exit Sub Err_cmdIncome_Click: MsgBox Err.Description, , "Unexpected Error" Resume Exit_cmdIncome_Click End Sub