ISD3

Chris Wallace

www.cems.uwe.ac.uk/~cjwallac

Next 6 Weeks

• Extended Relational Model

• Object Orientation

• Matching systems

• 3 tier architecture

• Technology - Oracle 9i

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

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.

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

Common applications

• Core business processing– Accounting systems– Order processing– Stock control

• Administrative systems– student records– admissions– bookings

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

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

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

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

User-defined types• Codd’s 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

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!)

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

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

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

dm in Rational Rose

dm

degrees : number(3)minutes : real

asMin() : realasString() : StringasRad() : real

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

)

AttributesAttributes

FunctionsFunctions

Defining the dm Functions

member function asMin return real is

begin

return degrees*60+sign(degrees)*minutes;

end;

member function asRad return real is

begin

return (degrees+sign(degrees)*

(minutes/60)*(3.141593/180);

end;

member function asString return varchar is

begin

return degrees || ':' || minutes;

end;

Template for defining a new type

-- comment to describe the typecreate or replace type <name> as object ( <att1> : <type>, .. member function <name> ( <param> : <type>) return <type>,..)/ -- to execute this definition

create or replace type body <name> as member function <name> (<param> : <type>) return <type> is -- declarations begin -- PL/SQL statements end;end;

/

Using the dm type

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;

ConstructorConstructor

Function callFunction call

NavigationNavigation

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

Next Week

• Lecture :– Lat/long data type– Objects, Generalisation/Specialisation– 3 tier architecure

• Tutorial – install the Mayday application