ISD3
Download
1 / 22

ISD3 - PowerPoint PPT Presentation


  • 103 Views
  • Uploaded on

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

loader
I am the owner, or an agent authorized to act on behalf of the owner, of the copyrighted work described.
capcha
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

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



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


ad