Sponsored Links
This presentation is the property of its rightful owner.
1 / 22

ISD3 PowerPoint PPT Presentation

  • Uploaded on
  • Presentation posted in: General

ISD3. Chris Wallace 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

Download Presentation


An Image/Link below is provided (as is) to download presentation

Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author.While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server.

- - - - - - - - - - - - - - - - - - - - - - - - - - E N D - - - - - - - - - - - - - - - - - - - - - - - - - -

Presentation Transcript



Chris Wallace

Next 6 weeks

Next 6 Weeks

  • Extended Relational Model

  • Object Orientation

  • Matching systems

  • 3 tier architecture

  • Technology - Oracle 9i

Week 1

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



  • 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

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:




Common applications

Common applications

  • Core business processing

    • Accounting systems

    • Order processing

    • Stock control

  • Administrative systems

    • student records

    • admissions

    • bookings

Tricky application domains

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



  • 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



  • 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

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

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


VARCHAR2(n) - variable length string

CHAR(n) - fixed length string


NUMBER(p,s) - number having precision p and scale s e.g. NUMBER(4,2) allows 12.34






BLOB up to 4 GB

Summary of Oracle Datatypes

User defined datatypes

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

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 in Rational Rose

Defining the dm type

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




Defining the dm functions

Defining the dm Functions

member function asMin return real is


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


member function asRad return real is


return (degrees+sign(degrees)*



member function asString return varchar is


return degrees || ':' || minutes;


Template for defining a new type

Template for defining a new type

-- comment to describe the type

create 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


-- PL/SQL statements




Using the dm type


Using the dm type

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;




  • Find my web page


  • 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

Next Week

  • Lecture :

    • Lat/long data type

    • Objects, Generalisation/Specialisation

    • 3 tier architecure

  • Tutorial

    • install the Mayday application

  • Login