Class Name B BUSINESS CLASS T TOURIST CLASS
Class Name B BUSINESS CLASS T TOURIST CLASS Fares (πίνακας ναύλου) Flight_Code Class Fare 1234 B 800 1234 T 400 4321 B 600 4321 T 300 Flights (πίνακας πτήσεων) Flight_Code Time Day Destination 1234 21: 30 ΤΡΙΤΗ ΠΑΡΙΣΙ 4321 22: 30 ΣΑΒΒΑΤΟ ΛΟΝΔΙΝΟ Flights_Program (πίνακας με τις προγραμματισμένες πτήσεις) Program_id Flight_Code Flight_date 1 1234 25/6/08 2 4321 21/6/08 3 4321 28/6/08 Customers (πίνακας πελατών) Customer_Code Name Surname 100 ΧΡΗΣΤΟΣ ΧΡΗΣΤΟΥ 200 ΣΠΥΡΟΣ ΣΠΥΡΟΥ Address ΘΗΣΕΩΣ ΑΡΙΑΔΝΗΣ Reservations (πίνακας κρατήσεως θέσεων) Program_id Seat_number Customer_Code 1 1 100 1 2 200 2 1 100 Phone 2101234567 2107654321 Class B T B
• Ναύλο (fares). Το ναύλο εξαρτάται από την πτήση (Flight_code) και την κατηγορία πτήσεων (class). • Class -- > Name Class B T Name BUSINESS CLASS TOURIST CLASS • Flight_Code, Class > Fares (πίνακας ναύλου) Flight_Code 1234 4321 Class B T Fare 800 400 600 300
Patient Patno Name Tno Wno 1000 2000 3000 100 200 300 500 600 700 SMITH JONES ADAM Therapy Tno Therapy 100 Radiotherapy 200 Medication 300 Chemotherapy Amount 5200 2500 9000 No_of _tests 2 1 Κύριο κλειδί: patno Ward Wno 500 600 700 Κύριο κλειδί: TNo Lab_tests Patno Lab t_date Results 1000 Med. A 2/1/15 Number of Abnormal blood tests 1000 MEDb 3/1/15 Prolactin was elevated 3000 Med 1/6/15 Normal blood tests Κύριο κλειδί: ? ? , foreign key: ? Ward alpha beta chi Κύριο κλειδί: Wno πίνακας Labs Lab Med. A MEDb Med Κύριο κλειδί: (Lab)
Πώς να μεταγράψουμε υποκλάση category Product platforms memory Software Product price manufacturer name age. Group topic isa Educational Product
Επιλογή 1: Η προσέγγιση οντοτήτων / σχέσεων (the E/R Approach) • Product (name, price, category, manufacturer) • Educational. Product ( name, age. Group, topic) • Software. Product ( name, platforms, required. Memory) Θυμηθείτε ότι το ίδιο όνομα στήλης μπορεί να εμφανίζεται σε πολλές σχέσεις. Θα μπορούσαμε να γράψουμε τους πίνακες και ως εξής: • Product (Pname, price, category, manufacturer) • Educational. Product ( EPname, age. Group, topic) • Software. Product ( SPname, platforms, required. Memory)
Οι τρεις Υπογλώσσες της γλώσσας SQL Η γλώσσα SQL περιλαμβάνει τις παρακάτω υπογλώσσες: Υπογλώσσα ορισμού δεδομένων Data Definition Language (DDL). Εντολές 1) 2) 3) 4) 5) 6) 7) 8) CREATE DATABASE ALTER DATABASE CREATE TABLE ALTER TABLE DROP TABLE CREATE INDEX CREATE UNIQUE INDEX DROP INDEX 9) 10) 11) 12) 13) 14) 15) CREATE TRIGGER DROP TRIGGER REPLACE TRIGGER CREATE PROCEDURE DROP PROCEDURE CREATE FUNCTION DROP FUNCTION
Μία κάπως σύνθετη εντολή SELECT ename, job, sal FROM EMP WHERE (job IN ('ANALYST', 'PROGRAMMER', ‘CLERK’)) AND (sal>= 1300 OR sal+NVL(comm, 0) >= 1500) AND ename LIKE ‘%ES’ AND hiredate BETWEEN ‘ 01/01/70’ AND ‘ 31/12/89’ ORDER BY job, ename, sal; NVL (ORACLE) IFNULL (My. SQL)
Υποαναζήτηση φωλιασμένη σε αναζήτηση SELECT ename, job, sal, deptno FROM EMP WHERE sal > (SELECT MIN(sal) FROM EMP WHERE deptno IN (10, 20)) ORDER BY deptno, ename; Η υποπρόταση SELECT MIN(sal) FROM EMP WHERE deptno IN (10, 20)) έχει ως αποτέλεσμα ΜΙΝ(SAL) 800 Επομένως η αρχική εκτελείται ως SELECT ename, job, sal, deptno FROM EMP WHERE sal > 800 ORDER BY deptno, ename;
Χρήση υποπρότασης GROUP BY SELECT deptno, COUNT(*) FROM emp GROUP BY DEPTNO; Ο παρακάτω πίνακας διαμερίζεται λόγω της υποπρότασης GROUP BY DEPTNO Empno 7369 7499 7521 7566 7654 7698 7782 7788 7839 7844 7876 7900 7902 7934 Ename SMITH ALLEN WARD JONES MARTIN BLAKE CLARK SCOTT KING TURNER ADAMS JAMES FORD MILLER Job CLERK SALESMAN MANAGER ANALYST PRESIDENT SALESMAN CLERK ANALYST CLERK Mgr 7902 7698 7839 7566 7698 7788 7698 7566 7782 Hiredate 17/12/00 20/02/01 22/02/01 02/04/01 28/09/01 01/05/01 09/06/01 19/04/07 17/11/01 08/09/01 23/05/07 03/12/01 23/01/02 Sal 800 1600 1250 2975 1250 2850 2450 3000 5000 1500 1100 950 3000 1300 Comm. 300 500 1400 Deptno 20 30 30 10 20 10 30 20 10
Ως εξής: 7782 CLARK 7839 KING 7934 MILLER MANAGER PRESIDENT CLERK 7839 7782 09/06/01 17/11/01 23/01/02 2450 5000 1300 Empno 7369 7566 7788 7876 7902 Ename SMITH JONES SCOTT ADAMS FORD Job CLERK MANAGER ANALYST CLERK ANALYST Mgr 7902 7839 7566 7788 7566 Hiredate 17/12/00 02/04/01 19/04/07 23/05/07 03/12/01 Sal 800 2975 3000 1100 3000 Comm. Deptno 20 20 20 7499 7521 7654 7698 7844 7900 ALLEN WARD MARTIN BLAKE TURNER JAMES SALESMAN MANAGER SALESMAN CLERK 7698 7839 7698 20/02/01 22/02/01 28/09/01 01/05/01 08/09/01 03/12/01 1600 1250 2850 1500 950 300 500 1400 0 30 30 30 deptno 10 Και το αποτέλεσμα είναι: 20 30 Count(*) 3 5 6 10 10 10
SELECT deptno, AVG(sal), COUNT(*) FROM emp GROUP BY DEPTNO; SELECT deptno, AVG(sal), COUNT(*) FROM emp GROUP BY DEPTNO HAVING deptno > 20 AND COUNT(*)>1; SELECT deptno, AVG(sal), COUNT(*) FROM emp GROUP BY DEPTNO HAVING (deptno > 20) AND (COUNT(*)>1 OR AVG(sal)>1200);
Έστω βάση Διοίκησης Προσωπικού αποτελούμενη από τους παρακάτω πίνακες SELECT * FROM emp; Empno 7369 7499 7521 7566 7654 7698 7782 7788 7839 7844 7876 7900 7902 7934 7998 Ename SMITH ALLEN WARD JONES MARTIN BLAKE CLARK SCOTT KING TURNER ADAMS JAMES FORD MILLER BATES SELECT * FROM dept; Job CLERK SALESMAN MANAGER ANALYST PRESIDENT SALESMAN CLERK ANALYST Deptno 10 20 30 40 Mgr 7902 7698 7839 7566 7698 7788 7698 7566 7782 7566 Hiredate 17/12/00 20/02/01 22/02/01 02/04/01 28/09/01 01/05/01 09/06/01 19/04/07 17/11/01 08/09/01 23/05/07 03/12/01 23/01/02 17/11/07 Dname ACCOUNTING RESEARCH SALES OPERATIONS Sal 800 1600 1250 2975 1250 2850 2450 3000 5000 1500 1100 950 3000 1300 1000 Comm. 300 500 1400 0 Loc NEW YORK DALLAS CHICAGO BOSTON Deptno 20 30 30 10 20 10 30 20 10
Απλές αναζητήσεις βασιζόμενες σε περισσότερους από έναν πίνακες Σε λογικό επίπεδο γίνεται σύνδεση των δύο πινάκων: Empno Ename Job Mgr Hiredate Sal 7369 7499 7521 7566 7654 7698 7782 7788 7839 7844 7876 7900 7902 7934 CLERK SALESMAN MANAGER ANALYST PRESIDENT SALESMAN CLERK ANALYST CLERK 7902 7698 7839 7566 7698 7788 7698 7566 7782 17/12/00 20/02/01 22/02/01 02/04/01 28/09/01 01/05/01 09/06/01 19/04/07 17/11/01 08/09/01 23/05/07 03/12/01 23/01/02 800 1600 1250 2975 1250 2850 2450 3000 5000 1500 1100 950 3000 1300 SMITH ALLEN WARD JONES MARTIN BLAKE CLARK SCOTT KING TURNER ADAMS JAMES FORD MILLER Com m 300 500 1400 0 Emp. Deptno 20 30 30 10 20 10 30 20 10 Dname Loc RESEARCH SALES ACCOUNTING RESEARCH ACCOUNTING SALES RESEARCH ACCOUNTING DALLAS CHICAGO NEW YORK DALLAS NEW YORK CHICAGO DALLAS NEW YORK
Και να η επιλογή στηλών (ή πράξεων μεταξύ των στηλών) για προβολή Empno 7902 7788 Ename FORD SCOTT Job Sal ANALYST 3000 Sal+NVL(Comm, 0) 3000 Emp. Deptno 20 20 Να και μία παραλλαγή της προηγούμενης εντολής. SELECT empno, ename, job, sal+nvl(comm, 0, emp. deptno, dname FROM dept, emp WHERE deptno= emp. deptno AND job IN ('ANALYST', 'PROGRAMMER') ORDER BY ename; Dname RESEARCH
Σε λογικό επίπεδο γίνεται σύνδεση των δύο πινάκων Emp no 7782 7839 7934 7369 7566 7788 7876 7902 7499 7521 7654 7698 7844 7900 Ename Job Mgr Hiredate Sal CLARK KING MILLER SMITH JONES SCOTT ADAMS FORD ALLEN WARD MARTIN BLAKE TURNER JAMES MANAGER PRESIDENT CLERK MANAGER ANALYST CLERK ANALYST SALESMAN MANAGER SALESMAN CLERK 7839 7782 7902 7839 7566 7788 7566 7698 7839 7698 09/06/01 17/11/01 23/01/02 17/12/00 02/04/01 19/04/07 23/05/07 03/12/01 20/02/01 22/02/01 28/09/01 01/05/01 08/09/01 03/12/01 2450 5000 1300 800 2975 3000 1100 3000 1600 1250 2850 1500 950 Com m 300 500 1400 0 Emp. Deptno 10 10 10 20 20 20 30 30 30 Dname Loc ACCOUNTING RESEARCH RESEARCH SALES SALES NEW YORK DALLAS DALLAS CHICAGO CHICAGO Και να η επιλογή στηλών (ή πράξεων μεταξύ των στηλών) για προβολή Empno Ename 7902 FORD 7788 SCOTT Job ANALYST Sal 3000 Sal+NVL(Comm, 0) 3000 Emp. Deptno 20 20 Dname RESEARCH
NVL (ORACLE) IFNULL (My. SQL) SELECT empno, ename, job, sal+nvl(comm, 0), emp. deptno, dname FROM emp, dept WHERE emp. deptno = deptno(+) AND job IN ('ANALYST', 'PROGRAMMER') Empno Ename Job Mgr Hiredate Sal Com Emp. Dname ORDER BY ename; Loc 7369 7499 7521 7566 7654 7698 7782 7788 7839 7844 7876 7900 7902 7934 7998 DALLAS CHICAGO NEW YORK DALLAS NEW YORK CHICAGO DALLAS NEW YORK SMITH ALLEN WARD JONES MARTIN BLAKE CLARK SCOTT KING TURNER ADAMS JAMES FORD MILLER BATES CLERK SALESMAN MANAGER ANALYST PRESIDENT SALESMAN CLERK ANALYST 7902 7698 7839 7566 7698 7788 7698 7566 7782 7566 17/12/00 20/02/01 22/02/01 02/04/01 28/09/01 01/05/01 09/06/01 19/04/07 17/11/01 08/09/01 23/05/07 03/12/01 23/01/02 17/11/07 800 1600 1250 2975 1250 2850 2450 3000 5000 1500 1100 950 3000 1300 1000 m 300 500 1400 0 Deptno 20 30 30 10 20 10 30 20 10 RESEARCH SALES ACCOUNTING RESEARCH ACCOUNTING SALES RESEARCH ACCOUNTING
Προσοχή η υπάλληλος 7998 συμπεριλαμβάνεται στη σύνδεση. Επομένως τα αποτελέσματα είναι: Empno 7998 7902 7788 Ename BATES FORD SCOTT Job ANALYST NVL (ORACLE) IFNULL (My. SQL) Sal 1000 3000 Sal+NVL(Comm, 0) 1000 3000 Emp. Deptno 20 20 Dname RESEARCH
Σύνθεση απλών αναζητήσεων με χρήση τελεστών της θεωρίας συνόλων UNION / INTERSECT / MINUS (διαφορές στην περίπτωση my. SQL) SELECT empno, ename, job, sal+nvl(comm, 0), emp. deptno, dname FROM emp, dept WHERE emp. deptno(+) = deptno UNION SELECT empno, ename, job, sal+nvl(comm, 0), emp. deptno, dname FROM emp, dept WHERE emp. deptno = deptno(+) ORDER BY ename;
Συνάρτηση DECODE (oracle) 'ACCOUNTING', 'ΛΟΓΙΣΤΗΡΙΟ', SELECT dname, DECODE(dname, 'RESEARCH', 'ΕΡΕΥΝΑ', 'SALES', 'ΠΩΛΗΣΕΙΣ', 'OPERATIONS', 'ΥΠΟΣΤΗΡΙΞΗ', 'ΑΛΛΟ ΤΜΗΜΑ' ) FROM Dept; SELECT * FROM Dept;
Θέμα Πίνακες μίας μη κανονικοποιημένης βάσης δεδομένων βιβλιοπωλείου: BOOKS (πίνακας στοιχείων βιβλίου) ISBN Title Publisher Διεθνής Αριθμός Βιβλίου 0 07 123057 2 0 13 727827 6 0 13 861337 0 Τίτλος Εκδότης Database Mc. GRAW HILL Management Systems The essence of PRENTICE HALL databases A first course in PRENTICE HALL database systems PUB_BOOKS_COUNT (πίνακας στατιστικών που αποθηκεύει πόσοι τίτλοι βιβλίων είναι διαθέσιμοι ανά εκδότη) Publication _year Έτος έκδοσης Price Sales Τιμή καταλόγου έκπτωση 2003 70 10 1998 30 15 1997 90 30 Publisher Mc. GRAW HILL PRENTICE HALL ACADEMIC PRESS No_of_Books 1 2
Ο περιορισμός Primary Key • Παρατηρήστε ότι μπορούμε να προσθέσουμε εκ των υστέρων περιορισμούς για κύρια και ξένα κλειδιά ALTER TABLE Persons ADD PRIMARY KEY (P_Id); • Δημιουργήστε περιορισμό δηλώνοντας και το όνομά του ALTER TABLE Persons ADD CONSTRAINT pk_Person. ID PRIMARY KEY(P_Id, Last. Name); • Κατάργηση περιορισμού PRIMARY KEY ALTER TABLE Persons DROP PRIMARY KEY; ALTER TABLE Persons DROP CONSTRAINT pk_Person. ID;
Ο περιορισμός Check CREATE TABLE Persons (P_Id int NOT NULL, Last. Name varchar(255) NOT NULL, First. Name varchar(255), Address varchar(255), City varchar(255), CHECK (P_Id>0)); Οι δηλώσεις CREATE TABLE που γράψατε είναι σωστές. Δοκιμάστε και δηλώσεις INSERT INTO. Τελικά ο περιορισμός CHECK δουλεύει στην περίπτωση της my. SQL; CREATE TABLE Persons (P_Id int NOT NULL, Last. Name varchar(255) NOT NULL, First. Name varchar(255), Address varchar(255), City varchar(255), CONSTRAINT chk_Person CHECK (P_Id>0 AND City='Sandnes'));
- Slides: 74