9 10 DROP DATABASE my PERSONNEL CREATE DATABASE

9

10

DROP DATABASE my. PERSONNEL; CREATE DATABASE mypersonnel; USE mypersonnel; CREATE TABLE DEPT(DEPTNO INT(2) NOT NULL, DNAME VARCHAR(14), LOC VARCHAR(14), PRIMARY KEY(DEPTNO)); CREATE TABLE EMP(EMPNO INT(4) NOT NULL, ENAME VARCHAR(10), JOB VARCHAR(25), HIREDATE, MGR INT(4), SAL FLOAT(7, 2), COMM FLOAT(7, 2), DEPTNO INT(2), PRIMARY KEY(EMPNO), FOREIGN KEY(DEPTNO) REFERENCES DEPT(DEPTNO)); CREATE TABLE PROJ (projno INT(3) NOT NULL, pname VARCHAR(15), budget FLOAT(12, 2), PRIMARY KEY(projno)); CREATE TABLE ASSIGN( EMPNO INT(4) NOT NULL, PROJNO INT(3) NOT NULL, PTIME INT(3), PRIMARY KEY(EMPNO, PROJNO), FOREIGN KEY(EMPNO) REFERENCES EMP(EMPNO), FOREIGN KEY(PROJNO) REFERENCES PROJ(PROJNO)); SHOW TABLES; 11

Classic 12

UML 13

Crow’s foot 14

15

Υλοποίηση με χρήση My. SQL, Oracle: Συγκριτικός Πίνακας διαφορών my. SQL Oracle CREATE DATABASE new_personnel; USE new_personnel; CREATE TABLE DEPT(DEPTNO INT(2) NOT NULL, DNAME VARCHAR(14), LOC VARCHAR(14)); CREATE TABLE EMP(EMPNO INT(4) NOT NULL, ENAME VARCHAR(10), JOB VARCHAR(25), HIREDATE, MGR INT(4), SAL FLOAT(7, 2), COMM FLOAT(7, 2), DEPTNO INT(2)); INSERT INTO DEPT(DEPTNO, DNAME, LOC) VALUES (10, 'ACCOUNTING', 'NEW YORK'); INSERT INTO EMP VALUES (10, 'CODD', 'ANALYST', '1989/01/01', 15, 3000, NULL, 10); SELECT * FROM EMP; SELECT * FROM DEPT; DROP TABLE EMP; DROP TABLE DEPT; DROP DATABASE NEW_PERSONNEL; SHOW TABLES; CREATE TABLE DEPT(DEPTNO NUMBER(2) NOT NULL, DNAME VARCHAR 2(14), LOC VARCHAR 2(14)); CREATE TABLE EMP(EMPNO NUMBER(4) NOT NULL, ENAME VARCHAR 2(10), JOB VARCHAR 2(25), HIREDATE, MGR NUMBER(4), SAL NUMBER(7, 2), COMM NUMBER(7, 2), DEPTNO NUMBER(2)); INSERT INTO DEPT(DEPTNO, DNAME, LOC) VALUES (10, 'ACCOUNTING', 'NEW YORK'); INSERT INTO EMP VALUES (10, 'CODD', 'ANALYST', '01/01/1989', 15, 3000, NULL, 10); SELECT * FROM EMP; SELECT * FROM DEPT; DROP TABLE EMP; DROP TABLE DEPT; SELECT * FROM Tab; 18

Δημιουργία βάσης δεδομένων • Για να δημιουργηθεί αυτό το σχήμα της βάσης δεδομένων μπορούμε να χρησιμοποιήσουμε τις παρακάτω δηλώσεις (statements) σε γλώσσα SQL (oracle): CREATE TABLE dept(deptno NUMBER(2) NOT NULL, dname CHAR(14), No_of_employees NUMBER(5), PRIMARY KEY(deptno)); CREATE TABLE emp(empno NUMBER(4) NOT NULL, ename CHAR(10), job CHAR(10), sal NUMBER(5), deptno NUMBER(2), PRIMARY KEY(empno), FOREIGN KEY(deptno) REFERENCES dept(deptno)); 19

Δημιουργία trigger σε Oracle • Δημιουργήστε τον trigger Insert_Trig: Στην περίπτωση εισαγωγής των στοιχείων υπαλλήλου στον πίνακα emp θα ενεργοποιείται αυτόματα ο trigger και θα προστίθεται 1 στην αντίστοιχη θέση της στήλης No_of_employee του πίνακα dept (σε oracle χρήση NVL, : NEW) (σε My. SQL, IFNULL, NEW): 1. CREATE TRIGGER Insert_Trig 2. BEFORE INSERT ON emp 3. FOR EACH ROW 4. BEGIN 5. UPDATE dept 6. SET No_of_employees=NVL(No_of_employees, 0)+1 7. WHERE deptno=: NEW. deptno; 8. END; Trigger created. 0. 01 seconds 20

Ανάλυση παραδείγματος trigger σε My. SQL DROP DATABASE TRIG_EXAMPLE; CREATE DATABASE TRIG_EXAMPLE; USE TRIG_EXAMPLE; CREATE TABLE dept(deptno int(3) NOT NULL, dname varchar(14), No_of_employees int(5), PRIMARY KEY(deptno)); CREATE TABLE emp(empno int(4) NOT NULL, ename CHAR(10), job CHAR(10), sal int(5), deptno int(3), PRIMARY KEY(empno), FOREIGN KEY(deptno) REFERENCES dept(deptno)); 21

delimiter // CREATE TRIGGER Insert_trig BEFORE INSERT ON emp FOR EACH ROW BEGIN UPDATE dept SET No_of_employees=IFNULL(No_of_employees, 0)+1 WHERE deptno=NEW. deptno; end; // delimiter ; 22

insert into dept(deptno, dname) values(10, 'sales'); insert into emp(empno, ename, job, sal, deptno) values(1, 'popi', 'salesman', 900, 10); select * from dept; select * from emp; 23

Χρήση SQL INSERT INTO dept(deptno, dname) VALUES(10, ‘ΛΟΓΙΣΤΗΡΙΟ’); INSERT INTO emp VALUES(10, ‘ΧΡΗΣΤΟΥ’, ‘ΑΝΑΛΥΤΗΣ’, 2500, 10); SELECT empno, ename, job, sal, deptno FROM emp WHERE deptno IN (SELECT deptno FROM dept WHERE dname= ‘ΠΩΛΗΣΕΙΣ’); SELECT empno, ename, job, sal, emp. deptno, dname FROM emp, dept WHERE emp. deptno=deptno ORDER BY job, ename; SELECT empno, ename, job, sal, emp. deptno, dname FROM emp JOIN dept ON emp. deptno=deptno ORDER BY job, ename; SELECT job, COUNT(*) No_of_employees FROM emp GROUP BY job; 24

27

30

32

33

34

DROP DATABASE exams; CREATE DATABASE exams; USE exams; CREATE TABLE dept(Dno INT(2) NOT NULL, Dept VARCHAR(40), PRIMARY KEY(Dno)); CREATE TABLE job(Jno INT(2) NOT NULL, job VARCHAR(40), sal FLOAT(7, 2), PRIMARY KEY(Jno)); CREATE TABLE emp(Ename VARCHAR(30), Eno INT(4) NOT NULL, JNo INT(3), Comm FLOAT(7, 2), Dno INT(2), PRIMARY KEY(Eno), FOREIGN KEY(JNo) REFERENCES job(JNo), FOREIGN KEY(Dno) REFERENCES dept(Dno)); INSERT INTO dept(Dno, Dept) VALUES (10, 'ΛΟΓΙΣΤΗΡΙΟ'), (20, 'ΠΩΛΗΣΕΙΣ'), (30, 'ΠΡΟΣΩΠΙΚΟΥ'); INSERT INTO job(JNo, job, Sal) VALUES (100, 'ΑΝΑΛΥΤΗΣ', 2500), (200, 'ΠΩΛΗΤΗΣ', 2000); INSERT INTO emp(Ename, Eno, JNo, Comm, Dno) VALUES ('ΣΠΥΡΟΥ', 100, 150, 10), ('ΧΡΗΣΤΟΥ', 20, 100, 250, 10), ('ΧΡΗΣΤΟΥ', 30, 200, NULL, 20), ('ΝΙΚΟΥ', 40, 100, NULL, 10); SELECT * FROM DEPT; SELECT * FROM JOB; SELECT * FROM EMP; 36

Δήλωση SELECT που δείχνει τα παρακάτω στοιχεία για κάθε υπάλληλο: eno, ename, job, sal, dno, dept ταξινομημένα ανά όνομα υπαλλήλου (ename). SELECT eno, ename, jno, dept. dno, dept FROM emp JOIN dept ON emp. dno=dept. dno AND dept='ΛΟΓΙΣΤΗΡΙΟ' ORDER BY ename; 38

Πότε μια view δεν είναι ενημερώσιμη (is not updatable) Όταν περιλαμβάνει: 1. Aggregate functions (SUM(), MIN(), MAX(), COUNT(), …) 2. 3. 4. 5. 6. DISTINCT GROUP BY HAVING UNION, INTERSECT, MINUS Subquery (select … select ) 7. Πράξεις κ. λπ. στη λίστα του select πχ select sal+comm. … 8. SELECT που χρησιμοποιεί συνδέσεις (Certain joins). Ισχύει στις 9. περισσότερες περιπτώσεις, όχι σε όλες Μη ενημερώσιμη όψη στο FROM (Non updatable view in the FROM clause 10. Υποαναζήτηση (SELECT) στο WHERE που αναφέρεται σε πίνακα στο FROM (A subquery in the WHERE clause that refers to a table in the FROM clause) 42

Παραχώρηση δικαιωμάτων πρόσβασης σε χρήστες GRANT {CONNECT|RESOURCE|DBA} TO username [IDENTIFIED BY userpassword]; και αφαίρεση δικαιωμάτων πρόσβασης από χρήστες REVOKE {CONNECT|RESOURCE|DBA} FROM username; 50

Μηχανισμός ROLLBACK Στο επόμενο διάλογο (session) με το σύστημα μπορούμε να κατανοήσουμε καλύτερα την εντολή (1) SELECT NAME FROM CUSTOMER WHERE CUSTNO=7654; (2) Επιστρέφει E. CODD (3) UPDATE CUSTOMERS SET NAME = ‘P. D. JAMES’ WHERE CUSTNO=7654; (4) SELECT NAME FROM CUSTOMER WHERE CUSTNO=7654; (5) Επιστρέφει P. D. JAMES (6) ROLLBACK; (7) SELECT NAME FROM CUSTOMER WHERE CUSTNO=7654; (8) Επιστρέφει E. CODD 53

Πρωτόκολο COMMIT / ROLLBACK Στο επόμενο διάλογο (session) με το σύστημα μπορούμε να κατανοήσουμε καλύτερα το πρωτόκολλο (1) SELECT NAME FROM CUSTOMER WHERE CUSTNO=7654; (2) Επιστρέφει E. CODD (3) UPDATE CUSTOMERS SET NAME = ‘P. D. JAMES’ WHERE CUSTNO=7654; (4) COMMIT; (5) SELECT NAME FROM CUSTOMER WHERE CUSTNO=7654; (6) Επιστρέφει P. D. JAMES (7) ROLLBACK; (8) SELECT NAME FROM CUSTOMER WHERE CUSTNO=7654; (9) Επιστρέφει P. D. JAMES 54