1 / 22

ISD3 - PowerPoint PPT Presentation

  • Uploaded on

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

I am the owner, or an agent authorized to act on behalf of the owner, of the copyrighted work described.
Download Presentation

PowerPoint Slideshow about ' ISD3' - meadow

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

  • 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


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




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


  • 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

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


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

  • 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

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

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

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

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

  • Lecture :

    • Lat/long data type

    • Objects, Generalisation/Specialisation

    • 3 tier architecure

  • Tutorial

    • install the Mayday application