Base Data Types

1
Base Data Types

• Numbers
• Integers
• Reals
• Text
• Length
• International
• Date/Time
• Images
• Bitmap
• Vector
• Sound
• Samples
• MIDI
• Video

Input
Process
Output
Numbers, Text, and Dates
20
000001100
000001000
12 8 20
----------------
000010100
0010000000000000000
0100000000000001001
0110000011000011011
Images
0111111111111001111
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
Objects

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

Class name
Properties
Methods
Inheritance
Polymorphism
3
Objects in a Relational Database

• Separate inherited classes.
• Link by primary key.
• Adding a new customer requires new rows in each
  table.
• Definitely need cascade delete.

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
SQL3 OO Features

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

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 VAR
CHAR, 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 SaleI
tem ) 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
Integrated Applications

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

12
Accessing Application Objects
Database Application Retrieve data SELECT. GRO
UP BY Statistical calculations. Transfer to
spreadsheet. Execute statistical
routines. Retrieve and store results.
Spreadsheet Application Put data in
cells. Perform regression analysis.
13
Static Data Links

• 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

Database table
Container Document e.g., Spreadsheet
copy
Embedded object Query results
14
Dynamic Links
Database

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

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("A1A8"
).Formula "NORMSINV(RAND()) wsExcel.ActiveShe
et.Cells(9,1).Formula "Sum(A1A8) ' Compute
the total dbl wsExcel.ActiveSheet.Range("A9").V
alue ' Return the result MsgBox dbl ' Temporary
test, display value wsExcel.SaveAs
strFileName ' (optional) Save Worksheet wsExcel.D
isplayAlerts 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 Leve
l
Retrieve current data.
Past data.
Spreadsheet
2000 Balance Sheet
2000 Income Statement
2001 Balance Sheet
2001 Income Statement
Forecast
19
Financial Forecast
Excel
Crosstab query linked to spreadsheet MSAccess'C
12Finance.mdbQuery 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("Microsof
t Word") True) Then Set objWord
GetObject(, "Word.Application") Else Set
objWord CreateObject("Word.Application") End
If objWord.Visible True objWord.Documents.A
dd 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.Paragraph
Format.TabStops.Add PositionInchesToPoints(0.5),
_ AlignmentwdAlignTabLeft,
LeaderwdTabLeaderSpaces Selection.ParagraphForma
t.TabStops.Add PositionInchesToPoints(3), _
AlignmentwdAlignTabRight, LeaderwdTabLeaderSpa
ces ' Read all of the data rows and put them
into the document Do While Not rst.EOF
Selection.TypeText Textrst(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
Rangerngfoot, TypewdFieldFileName,
Text"\p" .InsertAfter TextvbTab vbTab
.Collapse DirectionwdCollapseStart
.Fields.Add Rangerngfoot, TypewdFieldCreateDat
e End With 'objWord.Documents.Save 'objWord.C
lose 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
Office Integration Tips

• MS Office 97/Visual Basic Programmers 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

For Each c in wsExcel.ActiveSheet.Range("A1F8")
If Abs(c.Value) lt 0.01 Then c.Value 0 Next
26
Sallys 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
"B6B7 One of many calculations
.Range("B6B28").Select Format the cells
goExcel.Selection.NumberFormat
",0.00(,0.00)"
.Range("B2,B4").Select goExcel.Selection.N
umberFormat "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