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

ISD3 PowerPoint PPT Presentation


  • 78 Views
  • Uploaded on
  • Presentation posted in: General

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

Download Presentation

ISD3

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


Isd3

ISD3

Chris Wallace

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


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


Relations

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

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

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


Challenges

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

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

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

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

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

)

Attributes

Functions


Defining the dm functions

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

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

begin

-- PL/SQL statements

end;

end;

/


Using the dm type

Constructor

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;

Navigation


Tutorial

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

Next Week

  • Lecture :

    • Lat/long data type

    • Objects, Generalisation/Specialisation

    • 3 tier architecure

  • Tutorial

    • install the Mayday application


  • Login