Introduction slide 3 Example DB slide 6 - PowerPoint PPT Presentation

������� ��� � Introduction slide 3 � Example DB slide 6 ���������� ����� �������� � Data Definition slide 8 ��� � Data Manipulation slide 14 � Retrieval slide 39 � Updates � Query Processing slide 43 2 � ��������������� ���������������� Introduction Introduction � Query Languages � Functionalities : � Data Definition Language � data definition and data manipulation in the relational format � Data Manipulation Language � data control � Formal Languages Manipulation � relational algebra � Manipulation language � relational calculus (based on predicate logic) � non procedural � user-oriented Query languages � borrowed to relational algebra and to the tuple relational calculus � Structured Query Language (SQL) � Power of the manipulation language � QUEry Language (QUEL) Relational Algebra � Query By Example (QBE) + � Link with programming languages Functions-Aggregates + Sorting � use of SQL statements inside a higher-level language � A SQL query (without functions and sorting) program is known as Embedded SQL (Pascal, C, ...) � Set of Relational Algebra operations 3 4 DBMS SQL - 1

��������������������� ������� !�"�#������ ! Introduction DB Example � History � SEQUEL language of the SYSTEM/R relational DBMS prototype (74-76) Employee( ESSN, LastName, FirstName, BirthDate, Address, E � IBM research lab at San José Sex, Salary, BonusAmount, SupervSSN , DepNumber ) � Normalization at ISO � The SQL1 norm (1986, 1989) Department( DepNumber, DepName, MgrSSN , D � The SQL2 norm (1992) MgrStartDate ) � The SQL3 norm DL Dept_locat( DepNumber , DLocation) � Query language of (quite) all relational DBMSs P � ORACLE (Oracle Corporation - 1977) Project( ProjNumber, ProjName, PLocation, DepNumber ) � INGRES (Ingres Technology - 1980) W � DB2 (IBM - 1984) Work_on( ProjNumber , ESSN , Nbh ) � INFORMIX (Informix Inc - 1981) � SYBASE (Sybase Inc - 1984) � MySQL (1995) 5 6 �$�%������&�#�����������'��� DB Example ��������� ���'��� ��� 1,1 supervised_by Supervise Employee Employee ENSS LastName FirstName BirthDate Address Sex Salary BonusAmount SupervSSN dDpNumber ESSN supervises 123456789 Smith John 1967-01-09 731 Fondren, Houston, TX M 300000 10000 333445555 5 LastName 0,n 333445555 Wong Franklin 1955-12-08 638 Voss, Houston, TX M 400000 5000 888665555 5 FirstName Works_for 999887777 Zelaya Alicia 1968-07-19 3321 Castel, Spring, TX F 250000 2000 987654321 4 0,1 Departement BirthDate 0,n 967654321 Wallace Jennifer 1941-06-20 291 Berry, Bellaire, TX F 430000 9000 888665555 4 Address DepNumber 666884444 Narayan Ramesh 1962-09-15 975 Fire Oak, Humble, TX M 380000 15000 333445555 5 0,1 453453453 English Joyce 1972-07-31 5631 Rice, Houston, TX F 250000 2500 333445555 5 Sex DepName 0,1 Manages 987987987 Jabbar Ahmad 1969-03-29 980 Dallas, Houston, TX M 250000 12000 987654321 4 Salary MgrStartDate 888665555 Borg James 1937-11-10 450 Stone, Houston, TX M 550000 8000 null 1 0,n BonusAmount 0,n Work_on ProjNumber ESSN Nbh 0,n 1 123456789 32.5 2 123456789 7.5 3 666884444 40.0 Work_on Locate 1 453453453 20.0 2 453453453 20.0 nbh 2 333445555 10.0 Project ProjNumber ProjName PLocation DepNumber 3 333445555 10.0 1 productx Bellaire 5 10 333445555 10.0 2 producty Sugarland 5 0,n 20 333445555 10.0 (1,1) 3 productz Houston 5 30 999887777 30.0 Project Dept-Locat 10 computerization Stanford 4 10 999887777 10.0 20 reorganization Houston 1 DLocation 10 987987987 35.0 ProjNumber Control 0,1 30 newbenefits Stanford 4 30 987987987 5.0 ProjName 30 987654321 20.0 PLocation 20 987654321 15.0 20 888665555 null 7 8 DBMS SQL - 2

��������� ���'��� ��� ���� �&������� Data Definition � Relation Schema Definition Dept_Locat DepNumber DLocation 1 Houston � Relational View Definition 4 Stanford 5 Bellaire 5 Sugarland � Integrity Constraint Definition 5 Houston � Right Definition Department DepNumber DepName MgrSSN MgrStarDate � Validation Process 5 R&D 333445555 1988-05-22 4 Administration 987654321 1995-01-01 1 Headquarters 888665555 1981-06-19 � data storage and index definition (not normalized => DBMS dependent !!) 9 10 !����� ������ $����������(��� Data Definition Data Definition � Numeric : � Creation INTEGER, SMALLINT � CREATE TABLE Project ( DECIMAL (m,n), NUMBER(m,n) � ProjNumber Integer , FLOAT, REAL ProjName Char(20), � � String : CHAR (n), VARCHAR(n) PLocation Char(40) ) � Temporal : DATE (SQL2 ! norm ) � Evolution add an attribute (SQL2 ! norm) � Specific DBMS have their own domains � ALTER TABLE Project ADD COLUMN DepNumber Integer � NULL : missing value � Delete (SQL2 ! norm ) DROP TABLE Project 11 12 DBMS SQL - 3

����������#���������� ���� ��)�*�$��������� %���� Data Definition � Rule that defines data consistency in the DB � Constraints defined in SQL � A finite set of domains � not null condition of an attribute value � optional key: � uniqueness of the value of an attribute or a set of attributes � default value for an attribute � Duplicate rows � domain constraint � The projection operator is different ( � � primary key (an attribute or a set of attributes) DISTINCT) � minimal referential integrity CREATE TABLE Project ( � A Relation is not a set ProjNumber integer PRIMARY KEY, ProjName char (20) NOT NULL, PLocation char (40) CONSTRAINT CPLocation CHECK (PLocation in ( ' Bellaire', 'Sugarland', 'Houston', 'Stanford' )), DepNumber integer CONSTRAINT CDepNumber CHECK (DepNumber between 1 and 5)) 13 14 ����������)�� +���������������&� ����$�����)�� Data Retrieval Data Retrieval � General retrieval syntax slide 16 Syntax How to fill the clauses ? � Selection and projection slide 17 � which result the user wants to see, SELECT schema result? � Join operator slide 21 <set of projected attributes > � Set-Theoretical Operations slide 24 � Where are the attributes that I need? FROM � Aggregate functions slide 25 <set of relations> � Table grouping slide 29 � Are there conditions on the values [WHERE used in my rule? Do I have several � Predicates and division slide 33 <set of restrictions and join relations in my FROM clause? criteria >] � Summary slide 38 � Complete example slide 40 15 16 DBMS SQL - 4

��������� ��� ���,������ ���,��������� Data Retrieval Data Retrieval � " Retrieve all employees who are involved on a " Find all the informations registered on the � project for more than 20 hours " Project records " SELECT ESSN, ProjNumber SELECT * FROM Work_on Wild-card character FROM Project WHERE Nbh > 20; ESSN ProjNumber Nbh instead of listing all columns 11234 1 32.5 16668 3 40 Result ProjNumber ProjName PLocation DepNumber 29998 27 30 1 productx Bellaire 5 19879 26 35 2 producty Sugarland 5 3 productz Houston 5 10 computerization Stanford 4 20 reorganization Houston 1 Number of output tuples : 4 30 newbenefits Stanford 4 17 18 ���,���������� ��������� Data Retrieval "Find all the employees who work for a department � " Determine all the project locations " whose number is between 1 and 4" SELECT * SELECT DISTINCT PLocation FROM Employee FROM Project WHERE DepNumber >=1 AND DepNumber <=4 To discard duplicate values SELECT * of tuples FROM Employee Result PLocation Bellaire WHERE DepNumber BETWEEN 1 AND 4 Sugarland Houston SELECT * Stanford FROM Employee WHERE DepNumber IN (1, 2, 3, 4) 19 20 DBMS SQL - 5

��������� ��� ������� �(��,������������� Data Retrieval Data Retrieval � "Find all the employees whose lastname begins with " Retrieve the ESSN values for all the employees 'b' or 'B' " working in the 'R&D' department at Houston " SELECT * SELECT E.ESSN FROM Employee FROM Employee E, Department D, Dep_Locat DL WHERE LastName LIKE ‘b%’ OR LastName LIKE ‘B%’ � " Retrieve the list of women ordered by their salary" WHERE E.DepNumber = D.DepNumber Cartesian SELECT * AND D.DepNumber=DL.DepNumber Product FROM Employee AND D.DLocation ='Houston' WHERE Sex ='F' AND E.DepName = ‘R&D’ join ORDER BY Salary conditions attribute name non ambiguous 21 22 �����������-��� .���/,��� Data Retrieval Data Retrieval SELECT ESSN � Join a relation with itself FROM Employee WHERE DepNumber IN ( � synonyms SELECT DepNumber "Employees who earn more than their supervisor " FROM Department SELECT EmpName WHERE DepName = 'R&D' FROM Employee E, Employee SUP and DepNumber IN ( WHERE E.supervSSN = SUP.ESSN SELECT DepNumber AND E.Salary > SUP.Salary FROM Dep_Locat WHERE DLocation = ‘Houston')) 23 24 DBMS SQL - 6