1 / 64

IT 20303

IT 20303. The Relational DBMS Section 07. Relational Database Theory. Using a Relational DBMS. Relational Database Theory. Data Types Depends on the conventions used by a particular database ORACLE uses: NUMBER CHAR - Characters VARCHAR2 - Characters DATE/TIME LOB.

melantha-ganis
Download Presentation

IT 20303

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. IT 20303 • The Relational DBMS • Section 07

  2. Relational Database Theory • Using a Relational DBMS

  3. Relational Database Theory • Data Types • Depends on the conventions used by a particular database • ORACLE uses: • NUMBER • CHAR - Characters • VARCHAR2 - Characters • DATE/TIME • LOB

  4. Relational Database Theory • NUMBER • Numerical data • Guaranteed to 38 digits accuracy • NUMBER(10) – 10 digits allowed • CHAR • Character data • Fixed-Length up to 2,000 bytes • Good for 2 or 3 characters • Y/N, T/F, USA/CAN • CHAR(2) – 2 characters allowed

  5. Relational Database Theory • VARCHAR2 • Character data • Variable-Length up to 4,000 bytes • VARCHAR2(15) – up to 15 characters • DATE/TIME • Date & Time data • DATE – DD-MON-YY (Default) • TIME – HH:MN:SE (Default)

  6. Relational Database Theory • LOB • Large OBject data type • CLOB • Long variable length characters • BLOB • Binary objects – Video, Sound, Graphics • BFILE • Reference to an OS file • Up to 4GB of data per file

  7. Relational Database Theory • SQL: Structured Query Language • Command-driven, nonprocedural language • Origin: IBM, System R, Mid-1970s, SEQUEL (SQL) • Adopted by Oracle in late 1970s • Before IBM product • State of the art (state of the art does not mean ideal)

  8. Relational Database Theory • SQL Standards • Major standards organizations work together • American National Standards Institute (ANSI) • International Organization for Standardization (ISO)

  9. Relational Database Theory • SQL Standards • More than 25 approved and current standards • SQL language: verbs, concepts, clauses, operations • Embedding SQL in host language • Persistent stored modules (stored procedures) • SQL routines using JAVA • On-Line analytical processing

  10. Relational Database Theory • Structured Query Language • DDL – Data Definition Language • CREATE • DROP • ALTER

  11. Relational Database Theory • Structured Query Language • DML – Data Manipulation Language • SELECT • INSERT • DELETE • UPDATE

  12. Relational Database Theory • Structured Query Language • DCL – Data Control Language • GRANT • REVOKE

  13. Relational Database Theory • Characteristics of SQL • Capabilities to • Define database objects – DDL • Tables, Views, Indexes, Users, Constraints • Manipulate Data – DML • Query capability based on relational algebra set operations • Update capability • Control Data - DCL

  14. Relational Database Theory • Characteristics of SQL • Intended for use by • DBA • Application Developers • Some End Users • SQL is easily grasped, but not quickly mastered (Croft, 1990)

  15. Relational Database Theory • Example: DEPT table EMPLOYEE Supervises DEPT Works for DEPARTMENT

  16. Relational Database Theory • Example: EMP table EMP

  17. Relational Database Theory • Table Definition: ANSI SQL • CREATE TABLE DEPT • (deptno NUMBER(2) NOT NULL, • dname VARCHAR2(14), • loc VARCHAR2(13) DEFAULT ‘Key West’);

  18. Relational Database Theory • Table Definition: ANSI SQL • CREATE TABLE EMP • (empno NUMBER(4) NOT NULL, • ename VARCHAR2(10), • job VARCHAR2(10), • mgr NUMBER(4), • hiredate DATE, • sal NUMBER (7,2), • comm NUMBER (7,2), • deptno NUMBER(2) NOT NULL);

  19. End 10-10-05

  20. Relational Database Theory • Data Definition Statements • Define Table structure and constraints • CREATE TABLE makes entries in the data dictionary • Table Name • Attributes • Name, Datatype, Size, Nullability • Constraints • Primary Key • Foreign Key • Check Clause • ALTER TABLE updates the data dictionary • Adds or modifies attributes • Adds or modifies constraints • DROP TABLE removes entries from the data dictionary

  21. Relational Database Theory • The System Tables • Also called data dictionary, catalog • System tables are tables – just like the data tables • For example: • user_tables • user_constraints • user_indexes

  22. Relational Database Theory • The System Tables • System tables are queried by DBAs and Users via SQL • DBAs can write procedures, reports using system tables • Graphical DBA tools generate SQL to access the dictionary

  23. Relational Database Theory • The System Tables • RDBMS accesses the system tables on every SQL request • Updated by DDL statements • Referenced on DML statements

  24. Relational Database Theory • SQL: SELECT is used for all queries • SELECT Data Items • FROM Table(s) • WHERE Condition is True

  25. Relational Database Theory • Manipulation: SELECT Example • Example 01: • SELECT * • FROM EMP • WHERE job = ‘ANALYST’; • Results

  26. Relational Database Theory • Manipulation: SELECT Example • Example 02: • SELECT ename, job, hiredate • FROM EMP • WHERE hiredate > ’31-DEC-99’; • Results

  27. Relational Database Theory • Manipulation: SELECT Example • Example 03: • SELECT empno, ename, loc • FROM EMP, DEPT • WHERE emp.deptno = dept.deptno • AND loc = ‘CHICAGO’; • Results

  28. Relational Database Theory • Manipulation: SELECT Example • Class Practice 01: • Give me a list of all the salesman and their managers? • SELECT ename, job, mgr • FROM EMP • WHERE job = ‘SALESMAN’;

  29. Relational Database Theory • Manipulation: SELECT Example • Class Practice 02: • Give me a list of all clerks to include their employee number, manager, hiredate and salary for those who make more than $1000? • SELECT empno, ename, mgr, hiredate, sal • FROM emp • WHERE job = ‘CLERK’ AND sal > 1000;

  30. Relational Database Theory • Manipulation: SELECT Example • Class Practice 03: • Give me a list of employees who work in the accounting department including their salaries? • SELECT ename, sal, dname • FROM emp, dept • WHERE emp.deptno = dept.deptno • AND deptno = 10;

  31. Relational Database Theory • SELECT is used to Read Data • User “Designs” the query • Expresses it according to SQL syntax • One SELECT query can request multiple relational algebra operations • SELECT …. which columns • FROM …. which tables • WHERE …. which rows • Each SELECT query is optimized • Transformed into a query execution plan

  32. Relational Database Theory • SQL: SELECT…ORDER BY • SELECT Data Items • FROM Table(s) • WHERE Condition is True • ORDER BY Data Item [DESC | ASC] • Presents the result set in sorted order

  33. Relational Database Theory • SQL: SELECT…ORDER BY • Example: • SELECT empno, ename, sal • FROM emp • WHERE sal > 2500 • ORDER BY sal;

  34. Relational Database Theory • SELECT can present Derived Values • List all the analysts, showing their annual compensation • SELECT empno, ename, 12*sal • FROM emp • WHERE job = ‘ANALYST’;

  35. End 10-12-05

  36. Relational Database Theory • SQL Subqueries • Suppose we want to find out who makes more money than Jones • We could first find out Jones’ salary • Then find all employees whose salary is greater than that • Alternatively…SQL allows nesting subqueries

  37. Relational Database Theory • SQL Subqueries • Example: • SELECT empno, ename • FROM emp • WHERE sal > • (SELECT sal • FROM emp • WHERE ename = ‘JONES’); • The subquery is executed first

  38. Relational Database Theory • Updating the Database • INSERT • Append new rows to a table • Example: • INSERT INTO dept (deptno, dname, loc) • VALUES (40, ‘OPERATIONS’, ‘BOSTON’);

  39. Relational Database Theory • Updating the Database • DELETE • Removes one or more rows from a table • Example: • DELETE FROM dept • WHERE loc = ‘DETROIT’;

  40. Relational Database Theory • Updating the Database • UPDATE • Replaces values in one or more columns • Example: • UPDATE emp • SET sal = sal * 1.10 • WHERE ename = ‘MILLER’;

  41. Relational Database Theory • SQL Query and Manipulation Summery (ANSI Standard) • Reading • SELECT • Updating • INSERT • DELETE • UPDATE

  42. Relational Database Theory • SQL Query and Manipulation Summery (ANSI Standard) • Grouping • GROUP BY • HAVING • Sequencing • ORDER BY

  43. Relational Database Theory • SQL Query and Manipulation Summery (ANSI Standard) • Built-in Functions • MIN • MAX • SUM • AVG • COUNT • DISTINCT

  44. Relational Database Theory • SQL Query and Manipulation Summery (ANSI Standard) • Special Operators • NOT, AND, OR • BETWEEN, NOT BETWEEN • IN, NOT IN • LIKE, NOT LIKE • IS NULL, IS NOT NULL

  45. Relational Database Theory • SQL Query and Manipulation Summery (ANSI Standard) • Arithmetic Operators • + • - • / • *

  46. Relational Database Theory • SQL Query and Manipulation Summery (ANSI Standard) • Comparison Operators • = • <> (not equal) • < • > • <= • >=

  47. Relational Database Theory • Views • Purpose of Views • Simplify • Function • Security

  48. Relational Database Theory • View Definition • CREATE VIEW view_name AS • SELECT data_items • FROM table(s) • WHERE condition is true • View definition is saved in system tables • View is a Virtual Table • Database is accessed only when view is referenced

  49. Relational Database Theory • View Definition Cont’d • Each user “sees” the database through their view as if • It is only one table • It contains only the data items of interest • The data items of interest have that user’s names • The data items are in exactly the order desired • Multiple views can be based on the same table

  50. Relational Database Theory • View Example • Create a view for the accounting department to include: empno, ename, position, ancomp • CREATE VIEW acct (empno, ename, position, ancomp) AS • SELECT empno, ename, job, 12*(sal+nvl(comm, 0)) • FROM emp;

More Related