1 / 58

Lecture 5 .

Lecture 5. More on SQL Joins What’s in a DBMS Data Dictionary Data Types. Lecture Content. In this lecture we will : Be looking at ‘mirror’ tables, Share some thoughts about types of joins Examine the function of ‘division’ Have a much closer examination of the facilities of a DBMS

lilah-buckner
Download Presentation

Lecture 5 .

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. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. Lecture 5. More on SQL Joins What’s in a DBMS Data Dictionary Data Types

  2. Lecture Content In this lecture we will : • Be looking at ‘mirror’ tables, • Share some thoughts about types of joins • Examine the function of ‘division’ • Have a much closer examination of the facilities of a DBMS • Introduce Data Dictionary and Database Administration • Develop Integrity and Integrity Constraints in Schemas • Examine an example of ‘Triggers’ and ‘Procedures’ • And have a quick look at the ‘Data Types’ which exist in Oracle

  3. Mirror Tables Base Tables (disk located) and Virtual Tables (memory) Consider a table ‘employee’ which contains staff detail supervisor detail manager details Select a.name,b.name,c.name,d.name,e.name from employee a, employee b, employee c, employee d, employee e; would produce a results table of all names in 5 columns of output

  4. Joining a Table to Itself Typical Query: For each employee, list the employee number, name Manager and Manager’s name Select X.EMPNO, X.NAME, X.MGR, Y.NAME from EMP X, EMP Y where X.MGR = Y.EMPNO Result: EMPNO NAME MGR NAME 10 SMITH 40 BROWN 20 JONES 40 BROWN 30 BLACK 40 BROWN 40 BROWN 50 WHITE The Primary Key and the Foreign are both in the same table Two virtual tables are created for joining (‘alias’ feature)

  5. Outer Join EMP DEP EmpNo Name Age DeptNo Mgr DeptNo Name Loc 10 smith 25 15 40 11 MIS Caulfield 20 jones 28 15 40 20 Finance Malvern 30 black 20 40 15 Market City 40 brown 46 11 50 17 Accounts Clayton 50 white 42 11 Select d.depno, e.name, e.age The + appends a null row to the EMP table From dep d , emp e for this query and it is used to join to the where d.depno = e.depno (+) DEP rows with no matching employees deptno name age deptno name age 11 brown 46 15 jones 28 11 white 42 20 black 15 smith 25 17

  6. DIVISION • Divides a BINARY relation by a UNARY relation and produces a UNARY relation as a result. skill-reqd result emp-skill empno empno skillcode skillcode E2 S2 S4 E1 E2 E3 E2 E5 E6 S1 S2 S3 S4 S5 S6 Divide emp-skill by skill-reqd to give result Special note: JOIN, INTERSECTION and DIVISION can be defined in terms of the other 5 operators (which are known as the ‘primitive’ operators).

  7. A DIVISION example In the Air Transport Industry, pilots records contain details of the aircraft they are qualified to fly. And there are also records of the number and types of aircraft in the hangars and which Company owns what. In this case, the table of pilot’s names and the planes they can fly is the dividend The details of the planes in the hangars is the quotient The query is to obtain the names of the pilots who can fly every type of plane in the hangars

  8. Suggested Solution • create table pilotskill (pilot vchar (150) not null, plane vchar(15) not null); • create table hangar (plane vchar(15)); • select pilot from pilotskill ps1, hangar h1 where ps1.plane = h1.plane group by ps1.pilot having count(ps1.plane = select count(*) from hangar); [notice the absence of any ‘division’ operator - this is effectively performed by the execution plan]

  9. Division Examples Result A B C J K L 1 3 1 J 1 K 1 L 2 J 2 K 3 K 3 L 3 J

  10. Division Examples Name Degree D1 D2 B Sc M Sc D3 B Sc Jones B Sc Jensen B Sc Jensen M Sc Jensen PhD Smith B Sc Smith M Sc Rogers B Sc Rogers PhD M Sc B Sc PhD R3 Jones Jensen Smith Rogers R2 Jensen Smith R1 Jensen

  11. Data Base Design 4th Generation Environment - User Perception user terminal teleprocessing report query electronic monitor writer language mail application programs e-mail files data dictionary DBMS structured and non-structured data images, graphics, video,voice Database

  12. Command Levels DataBase Administrators Privileged set of commands. Sometimes called 'superuser' Data Administration Database Developers Application Developers Users with Query rights only Users with Table modification rights

  13. DBMS Outline Multi Server Multi Server Multi Server Dbms Dbms Dbms Multi-Server Logging and Locking System Data Base

  14. DBMS Block Diagram C General Communications o GCF m p Sequencer / Dispatcher a SCF t i b Parser Optimiser Query i Execution l PSF OPF QEF i t y L i Relation Query Abstract b Storage Data Type Description r QSF ADF a RDF r y Data Manipulation DMF

  15. DBMS Functions

  16. Inspecting a DBMS

  17. DBMS Functions 1. Data Storage, Retrieval and Update 2. A User-Accessible Catalogue (Dictionary) 3. Support for Shared Update 4. Backup and Recovery Services 5. Security 6. Integrity 7. Data Independence 8. Utility Services

  18. DBMS Functions The Primary Objectives of a DBMS are to provide facilities for : 1. Definition of Database Logical Structures 2. Definition of Physical Structures 3. Access to the Database 4. Definition of Storage Structures to store user data These components are known as the ‘database architecture’

  19. Data Dictionary

  20. Data Dictionary A DATA DICTIONARY contains the fundamental definitions, characteristics and uses of data It describes: What the data is Characteristics Uses of Data User Permits / Restrictions A DATA DIRECTORY contains information relating to Physical Data Storage

  21. Data Dictionary A Data Dictionary SYSTEM stores maintains provides access to the Data Dictionary. It is a set of software Also known as the Catalog Function The Dictionary contains information on Data Processes Environment

  22. Data Dictionary A Data Dictionary is a DATABASE about the data held in the USER DATABASE Term Used : META DATA The Dictionary tables are variously known as Catalog or System Tables

  23. Data Dictionary A Data Dictionary can provide data about 1. Relationships between dictionary entity types : item uses item ,module table uses item, group, module module uses item, group, file, module program uses file, module system uses program, system 2. Listing of all entities Relationship reports (Which programs use record zzz) Versioning support Password support User access and exits

  24. Data Dictionary system planning Requirements definition analysis Design Implementation Testing Operations and maintenance D ATA D I C TIONARY data base

  25. Data Dictionary Database Application End Administration Programmers Users Human Interfaces Software and DBMS Interfaces Compilers Application Integrity PreCompilers Programs/ Constraints Report Generators Data Dictionary - - - - - - - - - - - - - - - - - - - - -

  26. Data Dictionary Some Benefits from Data Dictionary Use: 1. Better data management - Redundancies, Standards, Documentation 2. Reduction in system development time - Cross reference listings, Auto copy libraries 3. Reduction in maintenance costs 4. Quicker and More Accurate changes possible 5. Documentation standards 6. Data Audit - cross references, 'where used' listings

  27. Database Administration - Oracle • Some of the Dictionary Tables available to DBA, SYS, SYSTEM and (some) USERS • All_Tables: User accessible tables • DBA_Tables : All tables in the database • DBA_Tablespaces : All tablespaces • User_tables : A users own tables • User_Tablespaces : Accessible tablespaces • User_catalog : tables,views,synonyms,sequences (primary key generation) • User_views • User_constraints • User_indexes • User_triggers ................................. and so on.

  28. Integrity

  29. Integrity Integrity is a collection of processes, procedures and techniques which are used to ensure that data held in a database is COMPLETE ACCURATE CLEAR thus ensuring that Information derived from the database also has these characteristics

  30. Integrity C.R.U.D.E. C Column Integrity - Linked to Domain Integrity R Referential Integrity U User Defined Integrity D Domain Integrity - A user defined datatype E Entity Integrity

  31. Database Integrity Some terms you will encounter: Entity Integrity Referential Integrity Functional Dependency (constraints between determinants and attributes. For each value of the determinant there is only one value for each of the attributes it determines) Multivalued Dependency Join Dependency Domain Constraints Cardinality Constraint User Defined Constraints

  32. Data Integrity General Principle: Data compliance with a set of rules Rules Location: Best embodied in the DBMS If they are contained in an application, there is the danger of saturating a network and causing degraded performance. This is particularly so in client / server computing CONSTRAINTS: Declarative approach where integrity constraints are ‘declared’ as part of a table specification. ANSI SQL-89 and SQL-92, 93 and 99 standards include specifications for integrity constraints syntax and behaviour

  33. Integrity Constraints DATABASE INTEGRITY Refers to correctness and consistency of data Quality Assurance Usually expressed in terms of CONSTRAINTS - consistency rules which must not be subverted

  34. Forms of Constraints 1. Entity Integrity - Primary Key Value NO attribute of a primary key value may be NULL 2. Referential Integrity - Foreign Key Values If a FOREIGN KEY exists in a relation, then either (1) the foreign key value MUST match the Primary Key value of some row in its home (or Primary) relation OR (2) the FOREIGN KEY must be NULL 3. Functional Dependency - Determinant For each value of the DETERMINANT, there must be only ONE value for each of the attributes which it determines

  35. Forms of Constraints 4. Multivalued Dependencies If A,B and C are three sets of attributes, then A multidetermines B if and only if the set of B values associated with each A value is independent of the C values 5. Join Dependency - Relation Reconstruction A relation can be reconstructed by taking the join of its projections 6. Domain Constraint - Value restrictions Possible values of a data item are restricted to a specific set called the DOMAIN

  36. Forms of Constraints 7. Cardinality Constraint The number of entities which can be related is subject to a constraint 8. Set Retention Constraint The deletion of records is subject to limitations 9. Existence Dependency Hierarchical model (also OODB). Dependency of a child on the parents limits insertion and deletion of segments

  37. Forms of Constraints 10. General Constraints Those restrictions which can be expressed as arbitrary predicates about the data. e.g. no class may be scheduled for Room A.112 after 2.00pm on Fridays General Comments: DBMS’ have deficiencies in their ability to express and enforce constraints. Ingres uses the “Rules and Procedures’ facility, Oracle uses ‘Triggers and Constraints’ and later versions of SQL use a mechanism called ASSERTIONS.

  38. Referential Integrity Foreign Key Concept - An attribute (or set of attributes)in one table (the referencing table) occurs as the Primary Key of another table (the Primary, Lookup or Referenced table) Referential Integrity Constraint: The Value of a Foreign Key Must Be a Key Value in the Referenced Table OR The Value of the Foreign Key Must Be Undefined (Null) This cannot occur if the Foreign Key is part of the Primary Key of the Referencing Table

  39. Possible Referential Integrity Processes 1. LIMITED INSERT : If an incoming Foreign Key DOES NOT EXIST as a referenced table Primary Key: ABORT TRANSACTION - REPORT 2. LIMITED UPDATE : If an incoming Foreign Key DOES NOT EXIST as a referenced table Primary Key TERMINATE PROCESS 3. RESTRICTED DELETE : If there are referencing FOREIGN KEYS in a referencing table TERMINATE DELETE PROCESS ON REFERENCED TABLE

  40. Possible Referential Integrity Processes 4. RESTRICTED UPDATE : If there are referencing Foreign Keys in a referencing table INHIBIT UPDATE OPERATION ON THE REFERENCED KEY 5. CASCADE DELETE : If there are Referenced Keys INITIATE DELETION OPERATION ON REFERENCED TABLE BY DELETING ALL REFERENCING ROWS 6. CASCADE UPDATE : Commence an UPDATE on the REFERENCED TABLE by UPDATING the Foreign Keys on all Referencing Rows in the Referencing Table(s)

  41. Possible Referential Integrity Processes 7. NULLIFY DELETE : Commence a DELETE operation on the REFERENCED table by setting ALL the FOREIGN KEYS on the Referencing Table(s) to NULL (watch Data Types) 8. NULLIFY UPDATE : Set all of the Foreign Keys of the Referencing Table to NULL. This will invalidate any referencing of the Referenced Key (which must not be NULL) 9. DEFAULT UPDATE : Invalidate references to Updated Referenced Keys by setting all Referencing Table Foreign Keys to a DEFAULT value

  42. Possible Referential Integrity Processes 10. DEFAULT DELETE : Invalidate references to the deleted Referencing Key Value(s) by setting all Referencing Foreign Key values to a DEFAULT value 11. WARNING DELETE : Permit the deletion BUT Warn the user of the Unattached Foreign Keys which are now present in the Referencing Table(s) 12. WARNING UPDATE : Permit the Update BUT Warn the User of Unattached Foreign Keys which are now present in the Referencing Table(s)

  43. Some Integrity Schema Examples Create table monash1( city varchar2(13) not null, studydate date not null, noonread number(4,1), midnightread number(4,1), rainfall number, unique (city,studydate) ); Creates a table with the candidate key of city,studydate There may be a number of Unique constraints

  44. Some Integrity Schema Examples Create table monash1( city varchar2(13) not null, studydate date not null, noonread number(4,1), midnightread number(4,1), rainfall number, primary key (city,studydate) ); Creates a table with the Primary Key key of city,studydate and there is only 1 such set of values in the table. There may be a number of Unique constraints.

  45. Some Integrity Schema Examples Create table monash1( city varchar2(13) not null, studydate date not null, noonread number(4,1), midnightread number(4,1), rainfall number, constraint pk_citystudy primary key (city,studydate) ); Creates a table with the Primary Key key of city,studydate and names the constraint citystudy in the Constraints table.

  46. Some Integrity Schema Examples Create table worker ( name char(25) primary key, age number check (Age between 18 and 65), address char(15) references lodging (Lodging) ); • There is a referenced table named Lodging with an attribute named lodging. It is a Primary Key in that table and a Foreign key in the worker table

  47. Triggers • Oracle triggers are used to include more processing power to the DBMS function for events which affect a database. • In the following example a Trigger will be set which ensures that changes to employee records will only take place during business hours on working days ( security ?) • See if you agree ...

  48. Triggers Create trigger emp_permit_change before delete or insert or update on emp declare dummy integer; begin /* if today is a Saturday or Sunday, then return an error*/ if (to_char(sysdate, ‘dy’) = ‘sat’ or to_char (sysdate, ‘dy’) = ‘sun’) then raise_application_error (-20501, ‘May not change employee table during the weekend’); end if;

  49. Triggers Perhaps we need this as well :- If (to_char(sysdate, ‘hh24’) < 8 or to_char(sysdate, ‘hh24’) >= 18) then raise application_error (-20502, ‘May only change employee table during working hours’); end if; end; which raises and interesting point - what happens with flexible time and enterprise bargaining ?

  50. And now - The Data Types

More Related