ISD3

1
ISD3

• Chris Wallace
• www.cems.uwe.ac.uk/cjwallac

2
Next 6 Weeks

• Extended Relational Model
• Object Orientation
• Matching systems
• 3 tier architecture
• Technology - Oracle 9i

3
Week 1

• Review Relational Model
• Tricky applications
• Advances in Extended Relational DBs
• Review Standard Data types
• User defined types in Oracle 9i
• The Mayday System

4
Relations

• There is only one data structure in the
  relational data model - the relation
• Every relation in a database must have a
  distinct name.
• Every column in a relation must have a distinct
  name within the relation.
• All entries in a column must be of the same
  kind.
• The ordering of columns in a relation is not
  significant.
• Each row in a relation must be distinct.
• The ordering of rows is not significant.
• Each cell or column/row intersection in a
  relation should contain only a so-called atomic
  value.

5
Additional features

• Third Normal Form (3NF) Relations are
  non-redundant - say it once in one place
• Operations using DML (insert, update, delete)
• Unique primary key ensures unique rows
• Supplied column types (String, Integer, Date.. )
• Relationships created dynamically through join
  operation
• SELECT ENAME, DNAME
• FROM DEPT, EMP
• WHERE EMP.DEPTNO DEPT.DEPTNO

6
Common applications

• Core business processing
• Accounting systems
• Order processing
• Stock control
• Administrative systems
• student records
• admissions
• bookings

7
Tricky application domains

• Design systems - CAD, CAM, CASE
• Text searching - search engine
• Multi-media, hyper-media systems -images, video,
  audio, complex networks
• Spatial data - Geographic Information Systems
• Decision support systems - analysis of large body
  of static data
• Real-time active systems - air-traffic control

8
Challenges

• Complex entities
• application specific datatypes
• poorly mapped to multiple relations
• Entities not defined by data alone
• Complex relationships
• Specialised processing
• searching - text, images, maps
• handling aggregated data
• long transactions, terabytes of data, rapid
  response
• automated response to conditions in DB
• deduction of information

9
Responses

• Develop application specific data management
  system-but all DBMS share common problems
• handling transactions
• backup and recovery
• indexing, query processing
• Object oriented database - general purpose but
  better matched to problem structures
• Extend Relational model
• Hybrid systems

10
Extended Relational Model

• Procedural extensions
• Stored Procedures
• Triggers
• Object-oriented features
• User-defined data types
• Types as records in a table
• Generalisation/Specialisation
• Aggregations
• Object-references

11
User-defined types

• Codds relational model has domains
• commercial RDBMS provide only standard types
  with standard functions
• Applications require
• restrictions on standard types e.g. restricted
  values - can sometimes use Constraints to enforce
  
• types defined by several values bound together
• eg international currency requires amount and
  currency code
• functions which operate on these types
• eg to convert between 2 international currencies

12
Oracle Datatypes

• Oracle Built-in Datatypes are directly supported
  by the Oracle DB
• Compatibility with other DBs is also provided
  e.g. ANSI standard types
• These can be mixed (as in my applications!)

13
Summary of Oracle Datatypes

• Strings
• VARCHAR2(n) - variable length string
• CHAR(n) - fixed length string
• Numbers
• NUMBER(p,s) - number having precision p and
  scale s e.g. NUMBER(4,2) allows 12.34
• NUMBER - as ANSI REAL
• Dates
• DATE
• TIMESTAMP
• INTERVAL
• BLOB up to 4 GB

14
User defined Datatypes

• Applications require their own types which must
  be built from these basic types
• In the Mayday Application, we need to record the
  position of a boat
• Positions given in Latitude and Longitude, and
  each of these is recorded as Degrees and Minutes
• e.g Bristol is at
• latitude N 51degrees 28 minutes
• longitude W 2 degrees 35 minutes

15
Mayday types

• We need two data types
• dm for degrees and minutes
• comprises integer for degrees, real for minutes
• sign of degree indicates N/S or W/E
• functions to convert to minutes only, to a string
• latlong for the latitude/longitude
• comprises one dm for the latitude, one for the
  longitude
• functions to convert to string, to find distance
  between two latlongs

16
dm in Rational Rose
17
Defining the dm type
create or replace type dm as object ( degrees
number(3) , minutes real , member function
asMin return real , member function asRad
return real , member function asString return
varchar )
Attributes
Functions
18
Defining the dm Functions
member function asMin return real is begin
return degrees60sign(degrees)minutes
end member function asRad return real is
begin return (degreessign(degrees)
(minutes/60)(3.141593/180)
end member function asString return varchar
is begin return degrees ''
minutes end
19
Template for defining a new type
-- comment to describe the type create or
replace type ltnamegt as object ( ltatt1gt
lttypegt, .. member function ltnamegt ( ltparamgt
lttypegt) return lttypegt,.. ) / -- to execute
this definition create or replace type body
ltnamegt as member function ltnamegt (ltparamgt
lttypegt) return lttypegt is -- declarations
begin -- PL/SQL
statements end end /
20
Using the dm type
Constructor
Function call
select dm(180,0 ) from dual select dm(-180,0)
from dual select dm(30,30).asMin() from dual
select dm(-30,30).asMin()from dual select
dm(30,30).asRad() from dual select
dm(-30,30).asRad() from dual
Navigation
21
Tutorial

• Find my web page
• www.cems.uwe.ac.uk/cjwallac
• Find the section for the module
• Find the first tutorial
• read through the Mayday application - before the
  tutorial
• create the dm type in your own Oracle DB
• test it
• modify it

22
Next Week

• Lecture
• Lat/long data type
• Objects, Generalisation/Specialisation
• 3 tier architecure
• Tutorial
• install the Mayday application