1 / 72

Lecture plan

Lecture plan. Oracle architecture SQL Data definition Queries Insert, delete and update. Oracle. Relational database Each table column is independent and identified by name Ordering of rows is unimportant All operations should be relational, i.e. generate new relations from old ones

quyn-whitley
Download Presentation

Lecture plan

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 plan • Oracle architecture • SQL • Data definition • Queries • Insert, delete and update

  2. Oracle • Relational database • Each table column is independent and identified by name • Ordering of rows is unimportant • All operations should be relational, i.e. generate new relations from old ones • System supports at least one JOIN operation • Based on a client-server architecture

  3. Oracle architecture • Oracle server consists of: • Database (raw data) • Logical structure = database schema • Physical structure = file structure • Instance (processes and system memory)

  4. Logical database structure • Organised into: • Tablespaces • Schemas • Data blocks • Extents • Segments

  5. Tablespaces • Group related logical structures together • SYSTEM tablespace created automatically • Holds the data dictionary • Meta-data in machine-readable format • Includes security information, schema object information, space allocation, etc • May also have one or more user tablespaces • Each table belongs to a specific tablespace

  6. Schemas • A named collection of schema objects associated with a particular user • Equivalent to a user’s personal space • Created automatically when a user account is set up

  7. Data blocks • Corresponds to a specific number of bytes of disk space • Size can be set for each database at creation

  8. Extents • Specific number of contiguous data blocks allocated for storing a specific type of information

  9. Segments • A set of extents allocated for a certain logical structure • Oracle dynamically allocates extents to segments as existing extents fill up

  10. Physical database structure • Consists of: • Datafiles (e.g. table and index data) • One or more datafiles form a tablespace • Redo log files (usually multiplexed) • Record all changes made to the data • Used in recovery • Control files (usually multiplexed) • Contain a list of all other files in the database

  11. Oracle instance • Consists of: • Processes • User processes • Oracle processes • Shared memory used by processes

  12. User processes • Manipulate the user’s input • Communicate with the Oracle server process • Display the information requested by the user

  13. Oracle processes • Perform functions for users: • Server processes handle requests from connected user processes • Background processes perform asynchronous I/O and provide increased parallelism

  14. Shared memory [1] • Used for caching data, indexes and storing shared program code • Organised into memory structures of a fixed size created on instance startup: • System global area (SGA) • Used to store data and control information for one Oracle instance • Holds database buffer cache, redo log buffer and shared pool

  15. Shared memory [2] • Program global area • Used to store data and control information for the Oracle server processes

  16. SQL • Standard for commercial relational DBs • High-level declarative language interface • User specifies what the result should be • Optimisation and query execution decisions left to DBMS • Based on tuple relational calculus, with some relational algebra features

  17. SQL versions • Standard version accepted by ANSI / ISO • Current version is SQL3 • Not all relational DBMSs support SQL3 (may support SQL-92, i.e. version 2) • Contains some object-oriented features

  18. SQL in Oracle • Oracle’s own version of SQL - SQLPlus • DDL and DML statements • View definition • Security and authorisation specification • Definition of integrity constraints • Transaction control specification • Session and system control statements • Embedding SQL into programming languages

  19. Data definition • Objects • Table • Commands • CREATE • ALTER • DROP

  20. Tables in SQL [1] • Created by CREATE TABLE statement CREATE TABLE EMPLOYEE • Known as base tables • Attributes ordered by creation order • Rows not ordered

  21. Tables in SQL [2] • CREATE TABLE specifies new relation by • Relation name • Attributes • Name • Data type • Attribute constraints

  22. Tables in SQL [3] • Key/entity/referential integrity constraints • Can be specified in CREATE TABLE • Can be added later using ALTER TABLE • Table can be deleted by DROP TABLE statement

  23. Data types [1] • Numeric • Integer: INTEGER • Real: FLOAT • Character-string • Fixed length: CHAR(n) • Varying length: VARCHAR(n) / VARCHAR2 (n)

  24. Data types [2] • DATE • Has main components YEAR, MONTH, DAY • Also stores century, hour, minute, second • Has format DD-MON-YYYY E.g. 05-FEB-2001

  25. Domains • Like a type declaration • Advantages • Easier to change data type • Improves schema readability • Can have optional default specification CREATE DOMAIN SSN_TYPE AS CHAR(9);

  26. Attribute constraints • Specified by CONSTRAINT • Example constraint: NOT NULL • Should always be specified for primary keys • Constraint may be given optional name • Specified by CONSTRAINT <name> • Must be unique within a schema

  27. Default values • Specified by DEFAULT <value> • Used if no explicit value assigned to attribute • NULL unless otherwise stated

  28. Table constraints [1] • PRIMARY KEY • UNIQUE (secondary key) • FOREIGN KEY (referential integrity) • Referential integrity constraints can be violated by • Insertion or deletion of tuples • Foreign key value modified

  29. Table constraints [2] • Referential triggered action • Can be added to foreign key constraint to cause automatic update ON DELETE • Options are SET NULL, CASCADE and SET DEFAULT

  30. DROP TABLE • Option • CASCADE CONSTRAINTS DROP TABLE DEPENDENT CASCADE CONSTRAINTS;

  31. ALTER TABLE [1] • Command which allows • Adding column ALTER TABLE EMPLOYEE ADD JOB VARCHAR(12); • Dropping column ALTER TABLE EMPLOYEE DROP ADDRESS CASCADE;

  32. ALTER TABLE [2] • Changing column definition (add/drop default) ALTER TABLE DEPARTMENT ALTER MGRSSN DROPDEFAULT; ALTER TABLE DEPARTMENT ALTER MGRSSN SETDEFAULT ‘11111111’;

  33. ALTER TABLE [3] • Adding / dropping table constraints ALTER TABLE EMPLOYEE DROPCONSTRAINT EMPSUPERFK CASCADE; ALTER TABLE EMPLOYEE ADD CONSTRAINT EMPSUPERFK; FOREIGN KEY(SUPERSSN) REFERENCES EMPLOYEE(SSN) ON DELETESET NULL;

  34. Queries • SQL allows two or more identical tuples in a relation • Table is thus a multi-set / bag of tuples • Table can be constrained to be a set by • Use of a key constraint • DISTINCT option

  35. SELECT statement [1] • Basic syntax is SELECT <attribute list> FROM <table list> WHERE <condition>

  36. SELECT statement [2] • Example - query 0 • Similar effect to relational algebra SELECT-PROJECT combination • SELECT clause specifies projection attributes • WHERE clause specifies selection condition • SQL may retrieve duplicate tuples, however

  37. SELECT statement [3] • Example - query 1 • Similar effect to relational algebra SELECT-PROJECT-JOIN combination • SELECT clause specifies projection attributes • WHERE clause specifies selection condition • Condition DNUMBER = DNO is join condition

  38. SELECT statement [4] • Example - query 2 • Multiple select and join conditions possible

  39. Ambiguous attribute names • Attributes with same name in different relations • Names must be qualified with relation name • Example - query 1A

  40. Aliasing [1] • Queries can refer to same relation twice • One-level recursive query • Not possible to have infinitely recursive query • Aliases / tuple variables can be declared • Example - query 8

  41. Aliasing [2] • Also possible to rename relation attributes EMPLOYEE AS E (FN, MI, LN, SSN, BD, ADDR, SEX, SAL, SSSN, DNO) • This ‘shorthand’ can be used in any query • Example - query 1B

  42. No WHERE clause • No condition on tuple selection • Example - query 9 • More than one relation in FROM clause means cross product • Example - query 10 • Similar to relational algebra cross product - PROJECT combination

  43. Use of asterisk • Used to retrieve all attribute values in SELECT clause • Examples - queries 1C, 1D, 10A

  44. Tables as sets [1] • Duplicate elimination not automatic • Expensive • Sometimes unnecessary or unwise • Not suitable for use with aggregate functions • Can be achieved by DISTINCT in SELECT clause • Example - queries 11, 11A

  45. Tables as sets [2] • Set union (UNION) and other set operations sometimes available, e.g. EXCEPT, CONTAINS, but are non-standard • Example - queries 3, 4

  46. Substring comparison • Uses LIKE comparison operator • % replaces any number of characters • _ replaces a single character • Examples - queries 12, 12A

  47. Arithmetic operators • Standard arithmetic operators can be applied • Example - query 13

  48. Other operators • String concatenation || • Numeric value range BETWEEN • Example - query 14 • Ordering by value of one or more attributes • Example - query 15

  49. Nested queries [1] • Complete SELECT-FROM-WHERE block inside WHERE of outer query • Ambiguity among attributes of same name • Assumed they belong to relation in innermost nested query • Correlated queries • Condition in inner WHERE references “outer” attribute

  50. Nested queries [2] • Queries with nested SELECT-FROM-WHERE blocks using IN can usually be expressed as single block query • Example - query 4A

More Related