database programming · 2013. 12. 28. · stearsa se va sterge si linia din tabela copil. daca fk...

Database programmingInstructor Sanda Popescu

CONSTRANGERI

• pot fi considerate ca reguli ale bazei de date• previn introducerea datelor invalide• previn stergerea datelor daca exista dependenta cu alte tabele• valoarea FK nu poate fi introdusa fara a fi introdusa inainte valoarea PK• toate constrangerile sunt memorate in dictionarul de date• se recomanda ca ele sa primeasca un nume dat de utilizator pentru a fi identificate mai usor, altfel sistemul ii aloca un nume de forma SYS_Cnumar.unic greu de retinut• pot fi definite atat la crearea tabelei, dar pot fi definite sau actualizate si dupa

-nn

-uk

-pk

-fk

-cksufixe

Toate constrangerile pot fi definite

atat la nivel de coloana cat si la

nivel de tabela, cu exceptia lui

NOT NULL care se defineste

numai la nivel de coloana

CONSTRÂNGERI

Fiecare constrangere estedenumita si definita inacelasi timp cu definireacoloanei.Atentie! La virgula, care nuapare pana ce definitia uneicoloane nu este completa!!!

Sintaxe FK

Utilizarea optiunii ON DELETE CASCADE cand definesti o FK activeaza dependenta liniilor din tabela copil, cand o linie din tabela parinte este stearsa se va sterge si linia din tabela copil. Daca FK nu are optiunea, liniile referite din tabela parinte nu pot fi sterse.In alte cuvinte aceasta optiune permite stergerea liniilor din tabela parinte cu stergerea automata a liniilor din tabela copil.

Este mai bine sa setezi pe NULL daca nu doresti stergerea din tabela copil.

TRY IT / SOLVE IT NOT NULL and UNIQUE constraints

NOT NULL and UNIQUE constraints

Solutie:

NOT NULL and UNIQUE constraints

Solutie:

Primary Key, Foreign Key and Check constraints

Solutie:

Primary Key, Foreign Key and Check constraints

Solutie:

Managing constraints

Solutie:

ATENTIE NOT NULL numai cu MODIFY

Si poate fi adaugat numai daca tabela este goala

sau toate liniile au valori NOT NULL

Aici putem vedea cum adaugamconstrangeri DUPA ce o tabela a fostcreata.Exercitiul ar trebui testat in aplicatiaExpress.Primele doua exemple (de sus) folosescADD cuvant rezervat, pe cand pentru aadauga o constrangere de tip NOT NULL(stanga) folosim MODIFY. Si ATENTIE!NOT NULL nu poate fi adaugata decatdaca coloana nu contine valori null.Sintaxa la MODIFY cere paranteze, ADDcere cuvantul CONSTRAINT imediatdupa.

Dezactivarea este temporara. Oconstrangere poate fi reactivata…

CASCADE este o optiune care poatefi folosita pentru a dezactiva sausterge orice constrangere caredepinde de aceasta constrangere.Este folosita deseori cand se stergeo PK.

Putem folosi Data Dictionary sa aflam informatii despre o constrangere.Exista un view numit USER_CONSTRAINTS care contine coloane referitoarela nume, tip, status pentru fiecare constrangere

ViewsCe este un view?

- O tabelă virtuala , un obiect al bazei de date, ce conţine un subset logic de date sau o combinaţie de date bazate pe o tabelă sau pe un alt view

- O “fereastră” într-o tabela care poate fi vizualizata sau schimbata

- Prezintă datele dintr-o tabela sau din mai multe tabele întrun singur loc

- Există doua tipuri de view-uri:

- Simple

- Complexe

View-urile pot fi folosite pentru a reduce complexitatea unor comenzi. Deexemplu, creatorul view-ului poate construi join-uri care să preia date dintabele multiple.Utilizatorii nici nu trebuie să vada tabelele de bază, nici cum a fost creat view-ul, prin view pot să folosească simple interogări. De asemenea, utilizatorii potvedea aceleaşi date în moduri diferite.

Se pot crea 2 tipuri de tabele:

Cererea care defineste un view poate contine sintaxa complexa a unui SELECT

Cererea care defineste view NU POATE contine o clauza ORDER BY

Clauza ORDER BY este folosită pentru a ordona datele la iesire. View-ul nu este un output, ci o definiţie despre cum pot fi vizualizate datele

Odată creat, view-ul nu mai poate fi modificat cu o comandă ALTER VIEW, aşa cum modifici cu ALTER TABLE o tabelă. De aceea opţiunea OR REPLACE este folosită pentru a înlocui un view cu un altul nou

Alias-uri de coloane pot fi folosite in subquery

-opţiunea NOFORCE este implicită. -Opţiunea FORCE se foloseşte pentru a crea un view indiferent dacă tabela de bază există sau nu.- Alias specifica numele pentru fiecare expresie(coloana) selectata de cerere- WITH CHECK OPTION este o constângere care spune că numai rândurile accesibile view-ului pot fi inserate, modificate sau şterse.(constraint_name este numele asignat ptr. constrangerea CHECK OPTION)- WITH READ ONLY este o constrângere care nu permite nici unui utilizator, altul decât creatorul, să facă modificări asupra view-ului.

SINTAXA

View-urile simple permit operaţii DML, atâta vreme cât opţiunea WITH READ ONLY NU face parte din definiţia view-ului

View-urile complexe nu permit întotdeauna operaţii DML

De fapt, nu poţi modifica un view.Ce poţi face este să-l recreezi,folosind opţiunea OR REPLACE. Înacest exemplu, se va elimina vechiulview şi se va crea cel nou, cu o nouăstructură

Se pot face operaţii DML întrun viewsimplu. Dar ce se întamplă este că de faptdatele din tabela de bază se schimbă.Amintiţi-vă ca view-ul nu conţine de faptdate, ci este un mod de a vizualiza dateledin tabela de bază.Dacă proprietarul view-ului sau DBAdoreşte să restricţioneze accesulutilizatorilor la datele din view, se voradăuga la creare două constrângeri: WITHCHECK OPTION şi WITH READ ONLY. Primaasigură ca operaţiile DML aplicate view-uluirămân în domeniul view-ului(numai asupraliniilor din view). Cea de-a doua face ca nicio operaţie DML să nu fie permisă în view.

create or replace view view_dept50as select department_id, employee_id, first_name, last_name, salary

from employeeswhere department_id=50

update view_dept50set department_id=190where employee_id=141

Select * from employees

Angajatul cu departamentul modificat nu va mai aparea in view( orice modificare in tabela de baza are efect si asupra viziunii)

Exemplu de incălcare a constrângerii WITH CHECK

OPTION

NU POT FACE MODIFICARI ASUPRA LINIILOR EXISTENTE IN VIEW

Modific view-ul

creat anterior

Exemplu de incălcare a constrângerii WITH READ ONLY

Pseudocoloane

-ROWNUM:- poate restrictiona nr. de linii afisate si numeroteaza automat

-LEVEL: la structura arborescenta, returneaza nivelul pe care lucrez

-NEXTVAL:- interogheza o secventa, returneaza urm.valoare

-CURRVAL:- returneza ultima valoarea generata automat de o secventa, nu se poate folosi daca nu s-a apelat cel putin o data NEXTVAL

Deoarece un view nu conţine date , ştergerea unui view nu afectează datele din tabela de bază.

Este un view fără nici o permanenţă, datele returnate de subcerere au un alias, care este utilizat in combinatie cu coloanele selectate de cererea principala

INLINE VIEWSubcereri in clauza

FROM

În exemplu se vor afişaprimele două cele mai maricosturi ale evenimentelor

Analizând comanda:-Inner query SELECT plasează cel mai mare cost la începutul listei returnată.

Atenţie!!! Este singura situaţie când poţi folosi ORDER BY în subquery.-Outer query afişează o listă care conţine o pseudocoloană ROWNUM în care cel mai mare cost are numărul de ordine 1, continuând să numeroteze în jos următoarele rânduri în ordine descrescătoare a costurilor.-Când adăugăm şi clauza WHERE lista returnată va conţine primele două evenimente în ordine descrescătoare a costurilor acestora.- se poate folosi DISTINCT pentru a elimina duplicatele din subcerere

ROWNUM:- numeroteaza liniile automat

SELECT ROWNUM as "Nr.crt" ,last_name,first_name,salaryFROM ( SELECT last_name,first_name,salary

FROM employeesORDER BY salary)

select ROWNUM as "Nr.crt" ,last_name,first_name,salaryFROM ( SELECT last_name,first_name,salary

FROM employeesORDER BY salary)

WHERE ROWNUM

Section 9 Managing constraints

Solutie:

TRY IT / SOLVE IT

Section 10 Vederi

Solutie:

Section 10 Vederi

Solutie:

1. The FACULTY table contains these columns:FACULTYID VARCHAR2(5) NOT NULL PRIMARY KEY

FIRST_NAME VARCHAR2(20)

LAST_NAME VARCHAR2(20)

ADDRESS VARCHAR2(35)

CITY VARCHAR2(15)

STATE VARCHAR2(2)

ZIP NUMBER(9)

TELEPHONE NUMBER(10)

STATUS VARCHAR2(2) NOT NULL

The COURSE table contains these columns:

COURSEID VARCHAR2(5) NOT NULL PRIMARY KEY

SUBJECT VARCHAR2(5)

TERM VARCHAR2(6

FACULTYID VARCHAR2(5) NOT NULL FOREIGN KEY

You have been asked to compile a report that identifies all adjunct professors who will be teaching classes in the upcoming term. You want to create a view

that will simplify the creation of this report. Which CREATE VIEW statements will accomplish this task?

CREATE VIEW

(SELECT first_name, last_name, status, courseid, subject, term

FROM faculty, course

WHERE facultyid = facultyid);

CREATE VIEW pt_view ON


FROM faculty f and course c

WHERE f.facultyid = c.facultyid);

CREATE VIEW pt_view IN


FROM faculty course);

CREATE VIEW pt_view AS


FROM faculty f, course c


2.Evaluate this CREATE VIEW statement:

CREATE VIEW pt_view AS


FROM faculty f, course c


Which type of view will this statement create?

nested

simple

inline

complex (*)

3. Which of the following statements is a valid reason for using a view?

Views allow access to the data because the view displays all of the columns from the table.

Views provide data independence for ad hoc users and application programs. One view can be used to retrieve data from several

tables. Views can be used to provide data security. (*)

Views are used when you only want to restrict DML operations using a WITH CHECK OPTION.

Views are not valid unless you have more than one user.

4. Evaluate this CREATE VIEW statement:

CREATE VIEW emp_view

AS SELECT SUM(salary) FROM employee;

You cannot update data in the EMPLOYEE table using the EMP_VIEW view. (*)

5. In order to query a database using a view, which of the following statements applies?

Use special VIEWSELECT Keyword

You can retrieve data from a view as you would from any table. (*)

You can never see all the rows in the table through the view.

The tables you are selecting from can be empty, yet the view still returns the original data from those tables

6. You cannot modify data in a view if the view contains the DISTINCT keywordYou cannot modify data in a view if the view contains a group function

You cannot insert data through a view if the view includes GROUP BY clause

An "inline view" is an unnamed select statement found :Enclosed in parenthesis within the from clause of a surrounding query

An inline view is a subquery with an alias

7. What is the purpose of including the WITH CHECK OPTION clause when creating a view?

To make sure that data in rows not visible through the view are changed or to make sure no rows returned by

the view are updated outside the scope of the view

TRY IT / SOLVE IT

SequencesSunt obiecte ale bazei de date folosite

în primul rând pentru a crea valori cheilor primare surogat (PK). Generează automat numere unice(implicit NOCYCLE).

-asigura concurenta maxima, dar lasa goluri (gap)

Increment by- specifică o raţieStart with- specifică prima valoare destart care va fi generată-implicit 1Maxvalue- specifică cea mai marevaloare pe care secvenţa o poategenera, implicit 1027 pentru valoricrescătoare şi -1 pentru valoridescrescătoareMinvalue- specifică cea mai micăvaloare pe care secvenţa o poategenera, implicit -1027 pentru valoridescrescătoare şi 1 pentru valoricrescătoare(se aplica pe o tabela careare deja date)Cycle- specifică dacă secvenţacontinuă să genereze dacă s-a ajunsla valoare minimă/maximă. Vacontinua cu prima valoaregenerată.NU se foloseste niciodataptr.PK(acestea tb sa fie unice)Cache- specifică câte valori prealocăserverul Oracle care vor fi păstrate înmemorie. Implicit sunt 20.

Atenţie!!! când se crează o secvenţă să nu existe conflict între minvalue, maxvalue şi start

CREATE SEQUENCE line_item_id_seq

MINVALUE 100 MAXVALUE 130 INCREMENT BY -10 CYCLE;

What will be the first five numbers generated by this sequence?

130120110100130

When used in a CREATE SEQUENCE statement, which keyword specifies that a range

of sequence values will be preloaded into memory?

CACHE

Construcţia emp_emp_id_seq.nextval caută pe server următoarea valoare disponibila de secvenţa emp_emp_id_seq

NEXTVAL trebuie să fie utilizat cel putin o data astfel incat CURRVAL să returneze o valoare. Aceasta deoarece, când este generatnumărul următor din secvenţă,numărul curent este automat plasatin CURRVAL(ultima valoare).

Atenţie!! NEXTVAL şi CURRVALTREBUIE să fie precedate de numelesecvenţei.(prefixate)Referirea la o secvenţă se face folosindpseudocoloanele NEXTVAL şi CURRVAL

Când foloseşti /nu folosesti

NEXTVAL şi CURRVAL

Atentie!

The NEXTVAL virtual column increments a sequence by a predetermined value

The CURRVAL virtual column will display the integer that was most recently supplied by a sequence.

O secvenţă poate fi modificată câtă vreme nu există un conflict între o valoare şi secvenţa care există deja.

În urma Comenzii ALTER SEQUENCE valorile viitoare sunt afectate dar valoarea curentă şi valorile folosite în trecut nu sunt influenţate de comanda ALTER.

Alterarea unei secvenţe nu schimbă valorile existente în baza de date.

Nu poţi schimba valoarea de început a secvenţei cu comanda ALTER.

Ca să poţi şterge o secvenţă trebuie să fii proprietarul acesteia sau să ai privilegii acordate în acest scop.

Secvente

Solutie:

Indexes Indexii sunt moduri sistematizate de aorganiza datele în baza de date.Dacă datele sunt organizate, atunciaccesul la ele este mai rapid. Acestaeste efectul unui index.

Există două tipuri: indexi unici şi non-unici.

Un index unic este creat automat decâte ori este creată o PK sau UK.

Un index non-unic este creat de cătreutilizator cu scopul de a mări vitezade acces la rândurile dintro coloană.

De exemplu, pentru a putea optimizajoin-uri, poţi crea un index pecoloana FK. Lucru ce măreşteviteza de căutare în coloana PK cucare este asociată în join.

Atentie!

ATENTIE!

USER_IND_COLUMN-> indexii din schema proprie pe o anumita coloana

You want to create a composite index on the FIRST_NAME and

LAST_NAME columns of the EMPLOYEES table. Which SQL

statement will accomplish this task?

R : CREATE INDEX fl_idx ON employees(first_name,last_name)

A function_based index

Pentru acces mai rapid la anumite inregistrari

SELECT * FROM employees WHERE (salary * 12) > 100000Create a function-based index on (salary * 12)

1.Which of the following best describes the function of an index

R : An index can increase the performance of SQL queries that search large tables

2. What is the correct syntax for creating an index

R: CREATE INDEX index_name ON table_name(column_name)

3. Evaluate this statement:

CREATE INDEX sales_idx ON oe.sales

R: The CREATE INDEX statement creates a nonunique index

4. What would you create to make the following statement execute faster?

SELECT *

FROM employees

WHERE UPPER(last_name) = ‘KING‘

R: A function_based index

5. An index is created automatically when a PRIMARY KEY constraint is created

6. Which of the SQL statements will display the index name, table name, and the uniqueness of the

index for all indexes on the EMPLOYEES table

R: SELECT index_name, table_name, uniqueness FROM user_indexes

WHERE table_name = 'EMPLOYEES‘

7. An index can increase the performance of SQL queries that search large tables

Synonyms (alias)Sunt nume alternative(alias-uri)pentru obiectele din baza de date.Un sinonim poate fi creat pentruorice obiect din baza de date. Deobicei sunt folosite pentru tabele,cand numele acestora suntcomplexe

TRY IT / SOLVE IT

DCL- Data Control Language

DCL este limbajul folosit pentru a controla accesul la baza de date. Numim asta accesul şi controlul privilegiilor.

Sunt de două tipuri:1. Privilegii sistem: dau drepturi de acces la baza de date şi la obiectele acestuia;sunt, în general, garantate de DBA; CONNECT, CREATE sunt exemple; dacă nu aiprivilegiul de CONNECT nu ai acces la baza de date. Cu CREATE ai dreptul de a

construi tabele Exemple: CREATE TABLE, CREATE PROCEDURE…..(slide 29)

2. Privilegii obiect: dau dreptul de a manipula conţinutul obiectelor în baza de date sau doar de vizualizare; sunt oferite de către proprietarul obiectului dau de către DBA; Exemple: SELECT, DELETE, INSERT,EXECUTE; etc (vezi slide -30)

Which data dictionary view shows which system privileges have been

granted to a user? USER_SYS_PRIVS

Un rol este un grup de privilegii cu un anumit nume. Pot fi oferite către utilizatori. Definind roluri este mai simplu sa oferi sau sa retragi privilegii.În exemplu:-Mai întâi s-a creat un rol numit manager-Se oferă apoi privilegii pentru manager (în acest exemplu doar de creare a unei tabele)-Se oferă apoi acest rol unui utilizator.

Numai DBA poate crea un rol

1. You use keyword PUBLIC to grant an object privilege to all database users

2. To join a table in your database to a table on a second (remote) Oracle database, you need to use:

R : A database link

ROLURI

Rolurile simplifica procesul de acordare si revocare a drepturilor de acces

Se pot oferi privilegii unei tabele saunumai unor coloane din tabelă.Privilegiile se pot oferi unui user, unuirol sau pentru toate tipurile deutilizatori(PUBLIC)WITH GRANT OPTION dă posibilitateasă ofere şi altor utilizatori (roluri)privilegii

Retragerea privilegiilor se poate faceuna câte una sau în bloc, pentru toţiutilizatorii sau pe rând, pentru maimulte roluri sau pe rând,

Privilegii

Solutie:

TRY IT / SOLVE IT

TransactionsSGBD Oracle se bazeaza pe tranzactii.Unele au Commit automat (finalizare) altele nu .

TRANZACTIE= din punct de vedere al codului sursa, o tranzactie este o unitate logica de lucru continand una sau mai multe comenzi DML sau DDL. Din punct de vedere functional, daca setul de comenzi se executa normal, efectele tranzactiei sunt salvate in baza de date (finalizate- commit).Daca cel putin o comanda nu se poate finaliza(din diverse motive), efectele tranzactiei nu sunt salvate in baza de date (anulare- rollback) iar tranzactia se incheie.

Doua comenzi principale sunt utilizate in cadrul lucrului tranzactional: COMMIT care forteaza o finalizare cu salvare a modificarilor efectuate pana in acel moment si ROLLBACK care anuleaza modificarile efectuate(de asemenea finalizand tranzactia). Ambele au efect in functie de modul cum a fost setata schema proprie, generata la crearea contului.

"read consistency“= It prevents other users from seeing changes to a table until

those changes have been committed

The best describes the purpose of the REFERENCES object privilege on a table

It allows a user's session to read from the table but only so that foreign key

constraints can be checked.

If a database crashes, all uncommitted changes are automatically rolled back

User BOB's CUSTOMERS table contains 20 rows.

BOB inserts two more rows into the table but does not

COMMIT his changes. User JANE now executes:

SELECT COUNT(*) FROM bob.customers;

What result will JANE see? => 20

A transaction makes several successive changes to a table. If required, you want to be able to rollback the later changes while

keeping the earlier changes. What must you include in your code to do this?

R: A savepoint

Examine the following statements:

UPDATE employees SET salary = 15000;

SAVEPOINT upd1_done;

UPDATE employees SET salary = 22000;

SAVEPOINT upd2_done;

DELETE FROM employees;

You want to retain all the employees with a salary of

15000; What statement would you execute next?

ROLLBACK TO SAVEPOINT upd1_done;

1. You grant user AMY the CREATE SESSION privilege. Which type of privilege have you granted to AMY?

R: A system privilege

2. You want to grant user BOB the ability to change other users' passwords. Which privilege should you grant to

BOB?

R: The ALTER USER privilege

3. User CARMEN creates an EMPLOYEES table, and then creates a view EMP_VIEW which shows only the

FIRST_NAME and LAST_NAME columns of EMPLOYEES. User CORI needs to be able to access employees'

names but no other data from EMPLOYEES. Which statement should CARMEN execute to allow this?

R: GRANT SELECT ON emp_view TO cori ; (*)

4. Evaluate this statement: ALTER USER carmen IDENTIFIED BY instructor ; Which statement about the result

of executing this statement is true?

R: A new password is assign to user carmen

5. User Carmen wants to create indexes on tables in her schema. What privilege must be granted to Kate so

that she can do this?

R: users do not need extra privileges to create indexes on tables in their own schema

6. User CARMEN has created a CUSTOMERS table and wants to allow all other users to SELECT from it.

Which command should CARMEN use to do this?

R : GRANT SELECT ON customers TO PUBLIC;

7. You are the database administrator. You want to create a new user CARMEN with a password of

INSTRUCTOR, and allow this user to create his own tables. Which of the following should you execute?

R : CREATE USER carmen IDENTIFIED BY instructor;

GRANT CREATE SESSION TO carmen;

GRANT CREATE TABLE TO carmen;

8. User BOB's schema contains an EMPLOYEES table. BOB executes the following statement:

GRANT SELECT ON employees TO mary WITH GRANT OPTION;

Which of the following statements can MARY now execute successfully?

SELECT FROM bob.employees; (*)

REVOKE SELECT ON bob.employees FROM bob;

GRANT SELECT ON bob.employees TO PUBLIC; (*)

DROP TABLE bob.employees;

9. Which statement would you use to grant privileges to a role

R: GRANT

10. User CHANG has been granted SELECT, UPDATE, INSERT and DELETE privileges on the EMPLOYEES table. You now want to prevent Chang from adding or deleting rows from the table, while still

allowing him to read and modify existing rows. Which statement should you use to do this?

R : REVOKE INSERT, DELETE ON employees FROM chang;

11. Which of the following best describes a role in an Oracle database?

R: A role is a name for a group of privileges

12. User ADAM has successfully logged on to the database in the past, but today he receives an

error message stating that (although he has entered his password correctly) he cannot log on. What

is the most likely cause of the problem?

R: ADAM's CREATE SESSION privilege has been revoked.

Transactions

Solutie:

Controlling user access

Solutie:

database programming · 2013. 12. 28. · stearsa se va sterge si linia din tabela copil. daca fk...

Documents