It 20303
This presentation is the property of its rightful owner.
Sponsored Links
1 / 64

IT 20303 PowerPoint PPT Presentation


  • 49 Views
  • Uploaded on
  • Presentation posted in: General

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.

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


It 20303

IT 20303

  • The Relational DBMS

  • Section 07


Relational database theory

Relational Database Theory

  • Using a Relational DBMS


Relational database theory1

Relational Database Theory

  • Data Types

    • Depends on the conventions used by a particular database

    • ORACLE uses:

      • NUMBER

      • CHAR - Characters

      • VARCHAR2 -Characters

      • DATE/TIME

      • LOB


Relational database theory2

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


Relational database theory3

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)


Relational database theory4

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


Relational database theory5

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)


Relational database theory6

Relational Database Theory

  • SQL Standards

    • Major standards organizations work together

      • American National Standards Institute (ANSI)

      • International Organization for Standardization (ISO)


Relational database theory7

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


Relational database theory8

Relational Database Theory

  • Structured Query Language

    • DDL – Data Definition Language

      • CREATE

      • DROP

      • ALTER


Relational database theory9

Relational Database Theory

  • Structured Query Language

    • DML – Data Manipulation Language

      • SELECT

      • INSERT

      • DELETE

      • UPDATE


Relational database theory10

Relational Database Theory

  • Structured Query Language

    • DCL – Data Control Language

      • GRANT

      • REVOKE


Relational database theory11

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


Relational database theory12

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)


Relational database theory13

Relational Database Theory

  • Example: DEPT table

EMPLOYEE

Supervises

DEPT

Works for

DEPARTMENT


Relational database theory14

Relational Database Theory

  • Example: EMP table

EMP


Relational database theory15

Relational Database Theory

  • Table Definition: ANSI SQL

    • CREATE TABLE DEPT

      • (deptnoNUMBER(2) NOT NULL,

      • dname VARCHAR2(14),

      • loc VARCHAR2(13) DEFAULT ‘Key West’);


Relational database theory16

Relational Database Theory

  • Table Definition: ANSI SQL

    • CREATE TABLE EMP

      • (empno NUMBER(4) NOT NULL,

      • ename VARCHAR2(10),

      • job VARCHAR2(10),

      • mgrNUMBER(4),

      • hiredateDATE,

      • salNUMBER (7,2),

      • commNUMBER (7,2),

      • deptnoNUMBER(2) NOT NULL);


End 10 10 05

End 10-10-05


Relational database theory17

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


Relational database theory18

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


Relational database theory19

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


Relational database theory20

Relational Database Theory

  • The System Tables

    • RDBMS accesses the system tables on every SQL request

      • Updated by DDL statements

      • Referenced on DML statements


Relational database theory21

Relational Database Theory

  • SQL: SELECT is used for all queries

    • SELECTData Items

    • FROMTable(s)

    • WHERECondition is True


Relational database theory22

Relational Database Theory

  • Manipulation: SELECT Example

    • Example 01:

      • SELECT *

      • FROM EMP

      • WHERE job = ‘ANALYST’;

      • Results


Relational database theory23

Relational Database Theory

  • Manipulation: SELECT Example

    • Example 02:

      • SELECT ename, job, hiredate

      • FROM EMP

      • WHERE hiredate > ’31-DEC-99’;

      • Results


Relational database theory24

Relational Database Theory

  • Manipulation: SELECT Example

    • Example 03:

      • SELECT empno, ename, loc

      • FROM EMP, DEPT

      • WHERE emp.deptno = dept.deptno

      • AND loc = ‘CHICAGO’;

      • Results


Relational database theory25

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’;


Relational database theory26

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;


Relational database theory27

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;


Relational database theory28

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


Relational database theory29

Relational Database Theory

  • SQL: SELECT…ORDER BY

    • SELECTData Items

    • FROMTable(s)

    • WHERECondition is True

    • ORDER BYData Item [DESC | ASC]

    • Presents the result set in sorted order


Relational database theory30

Relational Database Theory

  • SQL: SELECT…ORDER BY

    • Example:

    • SELECT empno, ename, sal

    • FROM emp

    • WHERE sal > 2500

    • ORDER BY sal;


Relational database theory31

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’;


End 10 12 05

End 10-12-05


Relational database theory32

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


Relational database theory33

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


Relational database theory34

Relational Database Theory

  • Updating the Database

    • INSERT

      • Append new rows to a table

      • Example:

      • INSERT INTO dept (deptno, dname, loc)

      • VALUES (40, ‘OPERATIONS’, ‘BOSTON’);


Relational database theory35

Relational Database Theory

  • Updating the Database

    • DELETE

      • Removes one or more rows from a table

      • Example:

      • DELETE FROM dept

      • WHERE loc = ‘DETROIT’;


Relational database theory36

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’;


Relational database theory37

Relational Database Theory

  • SQL Query and Manipulation Summery (ANSI Standard)

  • Reading

    • SELECT

  • Updating

    • INSERT

    • DELETE

    • UPDATE


Relational database theory38

Relational Database Theory

  • SQL Query and Manipulation Summery (ANSI Standard)

  • Grouping

    • GROUP BY

    • HAVING

  • Sequencing

    • ORDER BY


Relational database theory39

Relational Database Theory

  • SQL Query and Manipulation Summery (ANSI Standard)

  • Built-in Functions

    • MIN

    • MAX

    • SUM

    • AVG

    • COUNT

    • DISTINCT


Relational database theory40

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


Relational database theory41

Relational Database Theory

  • SQL Query and Manipulation Summery (ANSI Standard)

  • Arithmetic Operators

    • +

    • -

    • /

    • *


Relational database theory42

Relational Database Theory

  • SQL Query and Manipulation Summery (ANSI Standard)

  • Comparison Operators

    • =

    • <> (not equal)

    • <

    • >

    • <=

    • >=


Relational database theory43

Relational Database Theory

  • Views

    • Purpose of Views

      • Simplify

      • Function

      • Security


Relational database theory44

Relational Database Theory

  • View Definition

    • CREATE VIEWview_name AS

      • SELECTdata_items

      • FROMtable(s)

      • WHEREcondition is true

    • View definition is saved in system tables

    • View is a Virtual Table

      • Database is accessed only when view is referenced


Relational database theory45

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


Relational database theory46

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;


Relational database theory47

Relational Database Theory

  • View Example

    • List all the analysts showing their annual compensation

      • SELECT *

      • FROM acct

      • WHERE position = ‘ANALYST’;

    • Here the Query and View are merged for query execution


Relational database theory48

Relational Database Theory

  • Updating through Views

    • Update through view modifies data in the underlying table

      • Updates can be made through views based on one table

      • Updates can sometimes be made through views based on more than one table

    • UPDATE acct

    • SET position = ‘SALESMAN’

    • WHERE ename = ‘JAMES’;


Relational database theory49

Relational Database Theory

  • Database Security is based on Password and Authorizations

    • Need is to protect the database from unauthorized users

    • Authorization is granted to individual users to

      • Access (log on to) the DBMS

      • Access specified attributes in the database

      • Execute specified operations on each attribute

    • The DBA is responsible for Passwords, and Authorizations


Relational database theory50

Relational Database Theory

  • The Role of the DBA

    • DBA privileges vary greatly from product to product

    • Users can GRANT privileges on a table provided:

      • User is the owner, executed CREATE TABLE for that table or

      • User was granted privileges WITH GRANT OPTION


Relational database theory51

Relational Database Theory

  • The Role of the DBA

    • DBA has permissions to execute other statements

      • Can CREATE, DROP, or ALTER users, user profiles, tablespaces, tables, etc

    • Some installations create a hierarchy of DBAs

      • Entire Database

      • Application area DBAs

    • Good practice is to create a user_id for each specific DBA role


Relational database theory52

Relational Database Theory

  • DCL – Data Control Language

    • Commands:

      • GRANT

      • REVOKE


Relational database theory53

Relational Database Theory

  • Security and SQL

    • GRANT statement

      • GRANTprivileges

      • ONa table or view

      • TOa user

      • (WITH GRANT OPTION)

    • REVOKE statement

      • REVOKEprivileges

      • ONa table or view

      • FROMa user


Relational database theory54

Relational Database Theory

  • ANSI SQL GRANT privileges

    • ALL

    • SELECT

    • INSERT

    • UPDATE

    • DELETE

    • CONNECT


Relational database theory55

Relational Database Theory

  • See Example data for Employee & Departments:

    • Let Miller read all of the EMP table

    • Part 01

      • GRANT Connect

      • TO miller

      • IDENTIFIED BY 382miller;

    • Part 02

      • GRANT Select

      • ON emp

      • TO miller;


Relational database theory56

Relational Database Theory

  • See Example data for Employee & Departments:

    • Let Jones read and update the data for employees in her department

    • Part 01

      • CREATE VIEW jones AS

      • SELECT *

      • FROM emp

      • WHERE mgr = 7566;


Relational database theory57

Relational Database Theory

  • See Example data for Employee & Departments:

    • Part 02

      • GRANT Select, Update

      • ON jones

      • TO jones;


Relational database theory58

Relational Database Theory

  • See Example data for Employee & Departments:

    • Let King read the data for all employees in the emp table and update the salary, commission, and job attributes.

      • GRANT Select, Update(sal, comm, job)

      • ON emp

      • TO king;


Relational database theory59

Relational Database Theory

  • Database Security:

    • Security is a mentality

    • Security is environment-specific

    • The DBA has a key role

      • Builds the database

      • Grants user/password access into DBMS

      • Defines user views

      • Grants user access and authorization to data

    • 95% of all security violations can be avoided by attention to detail

      • Change master passwords after installation

      • Change passwords frequently


Relational database theory60

Relational Database Theory

  • Questions?


  • Login