ISD3

1. ISD3 Chris Wallace www.cems.uwe.ac.uk/~cjwallac

2. Next 6 Weeks • Extended Relational Model • Object Orientation • Matching systems • 3 tier architecture • Technology - Oracle 9i

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

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

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

6. Common applications • Core business processing • Accounting systems • Order processing • Stock control • Administrative systems • student records • admissions • bookings

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

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

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

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

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

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

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

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

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

16. dm in Rational Rose

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

18. 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;

19. 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; /

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

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

22. Next Week • Lecture : • Lat/long data type • Objects, Generalisation/Specialisation • 3 tier architecure • Tutorial • install the Mayday application