Database Systems - Chapter 6: Database Design Theory & Methodology - Trương Quỳnh Chi

Nội dung text: Database Systems - Chapter 6: Database Design Theory & Methodology - Trương Quỳnh Chi

1. Contents 1 Introduction of Structured Query Language 2 DDL: create, drop, alter 3 DML: select, insert, update, delete 4 DCL: commit, rollback, grant, revoke 2
2. Introduction of Structured Query Language  Structured Query Language (SQL) is a standard computer language for relational database management and data manipulation.  Basic SQL:  Data Definition Language (DDL)  Create, Alter, Drop  Data Manipulation Language (DML)  Select, Insert, Update, Delete  Data Control Language (DCL)  Commit, Rollback, Grant, Revoke 4
3. Data Definition Language (DDL)  Permits specification of data types, structures and any data constraints  All specifications are stored in the database  Includes:  CREATE: make a new database object (database, table, index, user, stored query, )  ALTER: modify an existing database object  DROP: destroy an existing database object 6
4. Schema and Catalog Concepts in SQL  Schema: a group of tables and other constructs that belong to the same database application CREATE SCHEMA Schema_Name AUTHORIZATION Authorization_Identifier; CREATE SCHEMA Company AUTHORIZATION JSmith;  Catalog: a named collection of schemas 8
5. CREATE TABLE  Base tables (base relations)  Relation and its tuples are actually created and stored as a file by the DBMS.  Virtual relations  Created through the CREATE VIEW statement.  Some foreign keys may cause errors  Circular references  refer to a table that has not yet been created 10
6. Basic Data Types  Additional data types  Timestamp data type (TIMESTAMP)  Includes the DATE and TIME fields  Plus a minimum of six positions for decimal fractions of seconds  Optional WITH TIME ZONE qualifier  INTERVAL data type  Specifies a relative value that can be used to increment or decrement an absolute value of a date, time, or timestamp 12
7. Specifying Constraints  Basic constraints:  Key and referential integrity constraints  Attribute constraints  Constraints on individual tuples within a relation 14
8. Key and Referential Integrity Constraints  FOREIGN KEY clause FOREIGN KEY (listOfFKColumns) REFERENCES ParentTableName [(listOfCKColumns)] [ON UPDATE referentialAction] [ON DELETE referentialAction ]  Referential triggered actions: RESTRICT (default), SET NULL, CASCADE, and SET DEFAULT FOREIGN KEY Dno REFERENCES Department(Dnumber) ON DELETE CASCADE ON UPDATE CASCADE 16
9. The COMPANY Database 18
10. DROP Command  Used to drop named schema elements: tables, domains, constraints, and the schema itself  Drop behavior options:  CASCADE and RESTRICT DROP SCHEMA Company CASCADE; Or DROP SCHEMA Company RESTRICT; 24
11. ALTER Command  ALTER command: change the definition of a base table or of other named schema elements  Base tables: adding or dropping a column or constraints, changing a column definition. ALTER TABLE Employee ADD Job VARCHAR(15); ALTER TABLE Employee DROP COLUMN Address CASCADE; ALTER TABLE Department ALTER COLUMN Mgr_ssn SET DEFAULT ‘333445555’; ALTER TABLE Employee DROP CONSTRAINT Empsuperfk CASCADE; 26
12. SELECT Command  SELECT command: retrieve information from a database  SELECT command in SQL is the same as the SELECT operation in relational algebra.  SQL allows a table (relation) to have two or more tuples that are identical in all their attribute values  SQL relation (table) is a multi-set (sometimes called a bag) of tuples; it is not a set of tuples  SQL relations can be constrained to be sets by specifying PRIMARY KEY or UNIQUE attributes, or by using the DISTINCT option in a query 28
13. SELECT Command  Complete form: SELECT [DISTINCT | ALL] {* | [columnExpression [AS newName]] [, ] } FROM TableName [alias] [, ] [WHERE condition] [GROUP BY columnList] [HAVING condition] [ORDER BY columnList] 30
14. The COMPANY Database 32
15. SELECT Command Query 1: Retrieve the name and address of all employees who work for the 'Research' department. Q1: SELECT Fname, Lname, Address FROM Employee, Department WHERE Dname='Research' AND Dnumber= Dno;  Similar to a SELECT-PROJECT-JOIN sequence of relational algebra operations  (DNAME='Research'): selection condition (SELECT operation in relational algebra)  (DNUMBER=DNO): join condition (JOIN operation in relational algebra) 34
16. SELECT Command Query 2: For every project located in 'Stafford', list the project number, the controlling department number, and the department manager's last name, address, and birthdate Q2: SELECT Pnumber, Dnum, Lname, Bdate, Address FROM Project, Department, Employee WHERE Dnum = Dnumber AND MgrSSN = SSN AND Plocation='Stafford‘;  Two join conditions:  Dnum = Dnumber: relates a project to its controlling department  MgrSSN = SSN: relates the controlling department to the employee who manages that department 36
17. Ambiguous Attribute Names  In SQL, we can use the same name for attributes as long as the attributes are in different relations. Query referring to attributes with the same name must qualify the attribute name with the relation name by prefixing the relation name to the attribute name  Examples:  DEPARTMENT.DNUMBER and DEPT_LOCATIONS.DNUMBER 38
18. Aliases  Aliases can also be used in any SQL query for convenience. Can also use the AS keyword to specify aliases Q3b: SELECT E.Fname, E.Lname, S.Fname, S.Lname FROM Employee AS E, Employee AS S WHERE E.SuperSSN = S.SSN;  Renaming using aliases: Employee AS E(FN, M, LN, SSN, BD, Addr, Sex, Sal, SSSN, DNO) 40
19. Unspecified WHERE-clause  If more than one relation is specified in the FROM- clause and there is no join condition, then the CARTESIAN PRODUCT of tuples is selected Query 5: retrieve all combinations of Employee.SSN and Department.Dname Q5: SELECT SSN, Dname FROM Employee, Department;  It is extremely important not to overlook specifying any selection and join conditions in the WHERE-clause; otherwise, incorrect and very large relations may result 42
20. Use of DISTINCT  SQL does not treat a relation as a set: duplicate tuples can appear in a query result.  To eliminate duplicate tuples, use the keyword DISTINCT Query 8: Retrieve the salary of every employee (Q8A) and all distinct salary values (Q8B) Q8a: SELECT Salary FROM Employee; Q8b: SELECT DISTINCT Salary FROM Employee;  The result of Q8A may have duplicate SALARY values, but Q8B’s 44
21. Set Operations Query 9: Make a list of all project numbers for projects that involve an employee whose last name is 'Smith' as a worker or as a manager of the department that controls the project. Q10: (SELECT DISTINCT Pnumber FROM Project, Department, Employee WHERE Dnum = Dnumber AND MgrSSN = SSN AND Lname = 'Smith') UNION (SELECT DISTINCT Pnumber FROM Project, Works_on, Employee WHERE Pnumber = Pno AND ESSN=SSN AND Lname = 'Smith'); 46
22. Substring pattern matching and arithmetic operators  Standard arithmetic operators: +, -, *, / Query 12: show the resulting salaries if every employee working on “ProductX” is given 10% raise Q12: SELECT Fname, Lname, 1.1*Salary AS INC_SAL FROM Employee, Works_on, Project WHERE SSN = ESSN AND PNO = Pnumber AND Pname = ‘ProductX’; 48
23. SELECT Command SELECT [DISTINCT | ALL] {* | [columnExpression [AS newName]] [, ] } FROM TableName [alias] [, ] [WHERE condition] [GROUP BY columnList] [HAVING condition] [ORDER BY columnList] 50
24. Correlated Nested Queries  If a condition in the WHERE-clause of a nested query references an attribute of a relation declared in the outer query , the two queries are said to be correlated Query 14: Retrieve the name of each employee who has a dependent with the same first name as the employee. Q14: SELECT E.Fname, E.Lname FROM Employee E WHERE E.SSN IN ( SELECT ESSN FROM Dependent WHERE ESSN = E.SSN AND E.Fname = Dependent_name); 52
25. Correlated Nested Queries  A query written with nested SELECT-FROM-WHERE blocks and using IN comparison operator can always be expressed as a single block query  For example, Q14 may be written as in Q14A Q14a: SELECT E.Fname, E.Lname FROM Employee E, Dependent D WHERE E.SSN = D.ESSN AND E.Fname = D.Dependent_name; 54
26. More Comparison Operators  Operators that can be combined with ANY (or SOME), ALL: =, >, >=, Query 16: Retrieve all employees whose salary is greater than the salary of all employees in department 5 Q16: SELECT * FROM Employee WHERE Salary > ALL ( SELECT Salary FROM Employee WHERE DNO=5 ); 56
27. EXISTS Function Query 14: Retrieve the name of each employee who has a dependent with the same first name as the employee Q14b: SELECT Fname, Lname FROM Employee WHERE EXISTS ( SELECT * FROM Dependent WHERE ESSN = SSN AND FName = Dependent_name); 58
28. Enumerated Sets  An explicit (enumerated) set of values in the WHERE- clause Query 18: Retrieve the SSNs of all employees who work on project numbers 1, 2, or 3. Q18: SELECT DISTINCT ESSN FROM Works_on WHERE PNO IN (1, 2, 3); 60
29. Joined Tables and Outer Joins  Joined table  Permits users to specify a table resulting from a join operation in the FROM clause of a query Query 1: Retrieve the name and address of all employees who work for the 'Research' department. Q1a: SELECT Fname, Lname, Address FROM (Employee JOIN Department ON Dno = Dnumber) WHERE Dname = ‘Research’; Q1: SELECT Fname, Lname, Address FROM Employee, Department WHERE Dname='Research' AND Dnumber= Dno; 62
30. Joined Tables in SQL and Outer Joins (cont’d.)  Inner join  Default type of join in a joined table  Tuple is included in the result only if a matching tuple exists in the other relation  LEFT OUTER JOIN  Every tuple in LEFT table must appear in result  If no matching tuple • Padded with NULL values for attributes of RIGHTtable 64
31. Joined Relations - Examples Query 3: For each employee, retrieve the employee's name, and the name of his or her immediate supervisor. Q3a: SELECT E.Fname, E.Lname, S.Fname, S.Lname FROM Employee E S WHERE E.SuperSSN = S.SSN; Q3c: SELECT E.Fname, E.Lname, S.Fname, S.Lname FROM ( Employee E LEFT OUTER JOIN Employee S ON E.SuperSSN = S.SSN );  Compare two queries??? 66
32. Joined Relations - Examples Query 2: For every project located in 'Stafford', list the project number, the controlling department number, and the department manager's last name, address, and birthdate Q2a: SELECT Pnumber, Dnum, Lname, Bdate, Address FROM ((Project JOIN Department ON Dnum = Dnumber) JOIN Employee ON MGRSSN = SSN)) WHERE Plocation = 'Stafford’ ; 68
33. AGGREGATE FUNCTIONS Queries 20: Retrieve the total number of employees in the company Q20: SELECT COUNT (*) FROM Employee; Queries 21: Retrieve the number of employees in the 'Research' department Q21: SELECT COUNT (*) FROM Employee, Department WHERE Dno = Dnumber AND Dname = 'Research’;  Note: NULL values are discarded wrt. aggregate functions as applied to a particular column 70
34. SELECT Command SELECT [DISTINCT | ALL] {* | [columnExpression [AS newName]] [, ] } FROM TableName [alias] [, ] [WHERE condition] [GROUP BY columnList] [HAVING condition] [ORDER BY columnList] 72
35. GROUPING: Q22 result Result of Q22 74
36. GROUPING: THE HAVING-CLAUSE Query 23: For each project on which more than two employees work , retrieve the project number, project name, and the number of employees who work on that project. Q23: SELECT Pnumber, Pname, COUNT (*) FROM Project, Works_on WHERE Pnumber = Pno GROUP BY Pnumber, Pname HAVING COUNT (*) > 2; 76
37. SELECT Command SELECT [DISTINCT | ALL] {* | [columnExpression [AS newName]] [, ] } FROM TableName [alias] [, ] [WHERE condition] [GROUP BY columnList] [HAVING condition] [ORDER BY columnList] 78
38. Contents 1 Introduction of Structured Query Language 2 DDL: create, drop, alter 3 DML: select, insert, update, delete 4 DCL: commit, rollback, grant, revoke 80
39. Insert Command  Insert a tuple for a new EMPLOYEE: U1: INSERT INTO Employee VALUES ('Richard', 'K', 'Marini', '653298653', '30-DEC-52', '98 Oak Forest, Katy, TX', 'M', 37000, '987654321', 4);  An alternate form of INSERT specifies explicitly the attribute names that correspond to the values in the new tuple, attributes with NULL values can be left out  Example: Insert a tuple for a new EMPLOYEE for whom we only know the FNAME, LNAME, and SSN attributes. U2: INSERT INTO Employee (Fname, Lname, SSN) VALUES ('Richard', 'Marini', '653298653'); 82
40. Insert Command  Example: Suppose we want to create a temporary table that has the name, number of employees, and total salaries for each department. A table DEPTS_INFO is created by U3, and is loaded with the summary information retrieved from the database by the query in U3A U3: CREATE TABLE Depts_info ( Dept_name VARCHAR(10), No_of_emps INTEGER, Total_sal INTEGER); U3A: INSERT INTO Depts_info (Dept_name, No_of_emps, Total_sal) SELECT Dname, COUNT (*), SUM (Salary) FROM Department, Employee WHERE Dnumber = Dno GROUP BY Dname; 84
41. Delete Command - Examples U4A: DELETE FROM Employee WHERE Lname = 'Brown’; U4B: DELETE FROM Employee WHERE SSN = '123456789’; U4C: DELETE FROM Employee WHERE Dno IN (SELECT Dnumber FROM Department WHERE Dname = 'Research'); U4D: DELETE FROM Employee; 86
42. Update Command  Example: Change the location and controlling department number of project number 10 to 'Bellaire' and 5, respectively. U5: UPDATE Project SET Plocation = 'Bellaire', Dnum = 5 WHERE Pnumber = 10; 88
43. Advanced DDL: Assertions & Triggers  ASSERTIONs to express constraints that do not fit in the basic SQL categories  Mechanism: CREATE ASSERTION  components include: a constraint name, followed by CHECK, followed by a condition 90
44. Advanced DDL: Assertions & Triggers  Triggers: to specify the type of action to be taken as certain events occur and as certain conditions are satisfied  Details of triggers: presentation and lab 92
45. VIEWs  Specify a different WORKS_ON table (view) CREATE VIEW Works_on_new AS SELECT Fname, Lname, Pname, Hours FROM Employee, Project, Works_on WHERE SSN = ESSN AND Pno = Pnumber;  We can specify SQL queries on a newly create table (view): SELECT Fname, Lname From Works_on_new WHERE Pname = ‘Seena’;  When no longer needed, a view can be dropped: DROP VIEW Works_on_new; 94
46. Contents 1 Introduction of Structured Query Language 2 DDL: create, drop, alter 3 DML: select, insert, update, delete 4 DCL: commit, rollback, grant, revoke Lab 96
47. CREATE TABLE CREATE TABLE [SchemaName.]TableName {(colName dataType [NOT NULL] [UNIQUE] [PRIMARY KEY] [DEFAULT defaultOption] [CHECK searchCondition] [, ]} [PRIMARY KEY (listOfColumns),] {[UNIQUE (listOfColumns),] [ ,]} {[FOREIGN KEY (listOfFKColumns) REFERENCES ParentTableName [(listOfCKColumns)] [ON UPDATE referentialAction] [ON DELETE referentialAction ]] [, ]} {[CHECK (searchCondition)] [, ] }) 100
48. Exercise 2 1. For each employee, retrieve the employee’s first name and last name and the first and last name of his/her immediate supervisor. 2. Retrieve the names of all employees in the departments which are located in Houston 3. List the names of all employees who have a dependent with the same first name as themselves 4. For each project, calculate the total number of employees who work for it, and the total number of hours that these employees work for the project. 5. Retrieve the average salary of all female employees. 6. For each department whose average employee salary is more than $30.000, retrieve the department name and the number of employees work for that department. 102