1 / 94

Chapter 8

Chapter 8. SQL-99: Schema Definition, Basic Constraints, and Queries. Data Definition, Constraints, and Schema Changes. Schema and Catalog Concepts in SQL2 (1/4).

poppy
Download Presentation

Chapter 8

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. Chapter 8 SQL-99: Schema Definition, Basic Constraints, and Queries

  2. Data Definition, Constraints, and Schema Changes

  3. Schema and Catalog Concepts in SQL2 (1/4) • Early versions of SQL did not include the concept of a relational database schema; all tables (relations) were considered part of the same schema. • The concept of an SQL schema was incorporated into SQL2 in order to group together tables and other constructs that belong to the same database application.

  4. Schema and Catalog Concepts in SQL2 (2/4) • An SQL schema is identified by a schema name, and includes an authorization identifier to indicate the user or account who owns the schema, as well as descriptors for each element in the schema. • Schema elements include the tables, constraints, views, domains, and other constructs (such as authorization grants) that describe the schema.

  5. Schema and Catalog Concepts in SQL2 (3/4) • A schema is created via the CREATE SCHEMA statement, which can include all the schema elements’ definitions. • Alternatively, the schema can be assigned a name and authorization identifier, and the elements can be defined later. • CREATE SCHEMA COMPANY AUTHORIZATION JSMITH;

  6. Schema and Catalog Concepts in SQL2 (4/4) • In addition to the concept of schema, SQL2 uses the concept of catalog—a named collection of schemas in an SQL environment. • A catalog always contains a special schema called INFORMATION_SCHEMA, which provides information on all the element descriptors of all the schemas in the catalog to authorized users. • Integrity constraints such as referential integrity can be defined between relations only if they exist in schemas within the same catalog. • Schemas within the same catalog can also share certain elements, such as domain definitions.

  7. The CREATE TABLE Command • The CREATE TABLE command is used to specify a new relation by giving it a name and specifying its attributes and constraints.

  8. Data Types and Domains in SQL2 (1/10) • The data types available for attributes include numeric, character-string, bit-string, date, and time.

  9. Data Types and Domains in SQL2 (2/10) • Numeric data types include integer numbers of various sizes (INTEGER or INT, and SMALLINT), and real numbers of various precision (FLOAT, REAL, DOUBLE PRECISION). • Formatted numbers can be declared by using DECIMAL(i,j)—or DEC(i,j) or NUMERIC(i,j)—where i, the precision, is the total number of decimal digits and j, the scale, is the number of digits after the decimal point. • The default for scale is zero, and the default for precision is implementation-defined.

  10. Data Types and Domains in SQL2 (3/10) • Character-string data types are either fixed-length—CHAR(n) or CHARACTER(n), where n is the number of characters—or varying-length—VARCHAR(n) or CHAR VARYING(n) or CHARACTER VARYING(n), where n is the maximum number of characters.

  11. Data Types and Domains in SQL2 (4/10) • Bit-string data types are either of fixed length n—BIT(n)—or varying length—BIT VARYING(n), where n is the maximum number of bits.

  12. Data Types and Domains in SQL2 (5/10) • The DATE data type has ten positions, and its components are YEAR, MONTH, and DAY typically in the form YYYY-MM-DD. • The TIME data type has at least eight positions, with the components HOUR, MINUTE, and SECOND, typically in the form HH:MM:SS.

  13. Data Types and Domains in SQL2 (6/10) • In addition, a data type TIME(i), where i is called time fractional seconds precision, specifies i + 1 additional positions for TIME—one position for an additional separator character, and i positions for specifying decimal fractions of a second.

  14. Data Types and Domains in SQL2 (7/10) • A TIME WITH TIME ZONE data type includes an additional six positions for specifying the displacement from the standard universal time zone, which is in the range + 13:00 to - 12:59 in units of HOURS:MINUTES. • If WITH TIME ZONE is not included, the default is the local time zone for the SQL session.

  15. Data Types and Domains in SQL2 (8/10) • A timestamp data type (TIMESTAMP) includes both the DATE and TIME fields, plus a minimum of six positions for fractions of seconds and an optional WITH TIME ZONE qualifier.

  16. Data Types and Domains in SQL2 (9/10) • The INTERVAL data type specifies an interval—a relative value that can be used to increment or decrement an absolute value of a date, time, or timestamp. • Intervals are qualified to be either YEAR/MONTH intervals or DAY/TIME intervals.

  17. Data Types and Domains in SQL2 (10/10) • In SQL2, it is possible to specify the data type of each attribute directly. • Alternatively, a domain can be declared, and the domain name used. • CREATE DOMAIN SSN_TYPE AS CHAR(9);

  18. Specifying Constraints and Default Values in SQL2 (1/5) • Because SQL allows NULLs as attribute values, a constraint NOT NULL may be specified if NULL is not permitted for a particular attribute.

  19. Specifying Constraints and Default Values in SQL2 (2/5) • It is also possible to define a default value for an attribute by appending the clause DEFAULT <value> to an attribute definition. • The default value is included in any new tuple if an explicit value is not provided for that attribute. • If no default clause is specified, the default default value (!) is NULL.

  20. Specifying Constraints and Default Values in SQL2 (3/5) • Following the attribute (or column) specifications, additional table constraints can be specified on a table, including keys and referential integrity. • The PRIMARY KEY clause specifies one or more attributes that make up the primary key of a relation. • The UNIQUE clause specifies alternate (or secondary) keys. • Referential integrity is specified via the FOREIGN KEY clause.

  21. Specifying Constraints and Default Values in SQL2 (4/5) • In SQL2, the schema designer can specify the action to be taken if a referential integrity constraint is violated upon deletion of a referenced tuple or upon modification of a referenced primary key value, by attaching a referential triggered action clause to any foreign key constraint. • The options include SET NULL, CASCADE, and SET DEFAULT. • An option must be qualified with either ON DELETE or ON UPDATE.

  22. Specifying Constraints and Default Values in SQL2 (5/5) • A constraint may be given a name, following the keyword CONSTRAINT. • The names of all constraints within a particular schema must be unique. • A constraint name is used to identify a particular constraint in case the constraint must be dropped later and replaced with another constraint

  23. The DROP SCHEMA and DROP TABLE Commands (1/2) • If a whole schema is not needed any more, the DROP SCHEMA command can be used. • There are two drop behavior options: CASCADE and RESTRICT. • For example, to remove the COMPANY database schema and all its tables, domains, and other elements, the CASCADE option is used as follows: • DROP SCHEMA COMPANY CASCADE; • If the RESTRICT option is chosen in place of CASCADE, the schema is dropped only if it has no elements in it; otherwise, the DROP command will not be executed.

  24. The DROP SCHEMA and DROP TABLE Commands (2/2) • If a base relation within a schema is not needed any longer, the relation and its definition can be deleted by using the DROP TABLE command. • For example, we can get rid of the DEPENDENT relation by issuing the command: • DROP TABLE DEPENDENT CASCADE; • If the RESTRICT option is chosen instead of CASCADE, a table is dropped only if it is not referenced in any constraints or views. • With the CASCADE option, all such constraints and views that reference the table are dropped automatically from the schema, along with the table itself.

  25. The ALTER TABLE Command (1/5) • The definition of a base table can be changed by using the ALTER TABLE command, which is a schema evolution command. • The possible alter table actions include adding or dropping a column (attribute), changing a column definition, and adding or dropping table constraints.

  26. The ALTER TABLE Command (2/5) • For example, to add an attribute for keeping track of jobs of employees to the EMPLOYEE base relations in the COMPANY schema, we can use the command: • ALTER TABLE COMPANY.EMPLOYEE ADD JOB VARCHAR(12); • We must still enter a value for the new attribute JOB for each individual EMPLOYEE tuple. • This can be done either by specifying a default clause or by using the UPDATE command. • If no default clause is specified, the new attribute will have NULLs in all the tuples of the relation immediately after the command is executed; hence, the NOT NULL constraint is not allowed in this case.

  27. The ALTER TABLE Command (3/5) • To drop a column, we must choose either CASCADE or RESTRICT for drop behavior. • If CASCADE is chosen, all constraints and views that reference the column are dropped automatically from the schema, along with the column. • If RESTRICT is chosen, the command is successful only if no views or constraints reference the column. • For example, the following command removes the attribute ADDRESS from the EMPLOYEE base table: • ALTER TABLE COMPANY.EMPLOYEE DROP ADDRESS CASCADE;

  28. The ALTER TABLE Command (4/5) • It is also possible to alter a column definition by dropping an existing default clause or by defining a new default clause. • The following examples illustrate this clause: • ALTER TABLE COMPANY.DEPARTMENT ALTER MGRSSN DROP DEFAULT; • ALTER TABLE COMPANY.DEPARTMENT ALTER MGRSSN SET DEFAULT "333445555";

  29. The ALTER TABLE Command (5/5) • Finally, one can change the constraints specified on a table by adding or dropping a constraint. • To be dropped, a constraint must have been given a name when it was specified. • ALTER TABLE COMPANY.EMPLOYEE DROP CONSTRAINT EMPSUPERFK CASCADE; • Adding a new constraint is specified by using the ADD keyword followed by the new constraint.

  30. Basic Queries in SQL

  31. The SELECT-FROM-WHERE Structure of SQL Queries (1/4) • SELECT <attribute list> FROM <table list> WHERE <condition>;

  32. The SELECT-FROM-WHERE Structure of SQL Queries (2/4) • QUERY 0Retrieve the birthdate and address of the employee(s) whose name is ‘John B. Smith’ • Q0:SELECT BDATE, ADDRESS FROM EMPLOYEE WHERE FNAME=‘John’ AND MINIT=‘B’ AND LNAME=‘Smith’;

  33. The SELECT-FROM-WHERE Structure of SQL Queries (3/4) • QUERY 1Retrieve the name and address of all employees who work for the ‘Research’ department. • Q1:SELECT FNAME, LNAME, ADDRESS FROM EMPLOYEE, DEPARTMENTWHERE DNAME=‘Research’ AND DNUMBER=DNO;

  34. The SELECT-FROM-WHERE Structure of SQL Queries (4/4) • QUERY 2For 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, ADDRESS, BDATE FROM PROJECT, DEPARTMENT, EMPLOYEE WHERE DNUM=DNUMBER AND MGRSSN=SSN AND PLOCATION=‘Stafford’;

  35. Dealing with Ambiguous Attribute Names and Renaming (Aliasing) (1/3) • QUERY 1Retrieve the name and address of all employees who work for the ‘Research’ department. • Q1A:SELECT FNAME, EMPLOYEE.NAME, ADDRESSFROM EMPLOYEE, DEPARTMENTWHERE DEPARTMENT.NAME=‘Research’ AND DEPARTMENT.DNUMBER= EMPLOYEE.DNUMBER;

  36. Dealing with Ambiguous Attribute Names and Renaming (Aliasing) (2/3) • QUERY 8For each employee, retrieve the employee’s first and last name and the first and last name of his or her immediate supervisor. • Q8: SELECT E.FNAME, E.LNAME, S.FNAME, S.LNAME FROM EMPLOYEE AS E, EMPLOYEE AS SWHERE E.SUPERSSN=S.SSN;

  37. Dealing with Ambiguous Attribute Names and Renaming (Aliasing) (3/3) • QUERY 1Retrieve the name and address of all employees who work for the ‘Research’ department. • Q1B: SELECT E.FNAME, E.NAME, E.ADDRESS FROM EMPLOYEE E, DEPARTMENT DWHERE D.NAME=‘Research’ ANDD.DNUMBER=E.DNUMBER;

  38. Unspecified WHERE-Clause and Use of Asterisk (*) (1/4) • QUERIES 9 and 10Select all EMPLOYEE SSNs (Q9), and all combinations of EMPLOYEE SSN and DEPARTMENT DNAME (Q10) in the database. • Q9: SELECT SSN FROM EMPLOYEE; • Q10: SELECT SSN, DNAME FROM EMPLOYEE, DEPARTMENT;

  39. Unspecified WHERE-Clause and Use of Asterisk (*) (2/4) • Q1C: SELECT *FROM EMPLOYEE WHERE DNO=5;

  40. Unspecified WHERE-Clause and Use of Asterisk (*) (3/4) • Q1D: SELECT * FROM EMPLOYEE, DEPARTMENTWHERE DNAME=‘Research’ AND DNO=DNUMBER;

  41. Unspecified WHERE-Clause and Use of Asterisk (*) (4/4) • QUERIES 10Select all combinations of EMPLOYEE SSN and DEPARTMENT DNAME (Q10) in the database. • Q10A: SELECT *FROM EMPLOYEE, DEPARTMENT;

  42. Tables as Sets in SQL (1/4) • SQL does not automatically eliminate duplicate tuples in the results of queries, for the following reasons: • Duplicate elimination is an expensive operation. One way to implement it is to sort the tuples first and then eliminate duplicates. • The user may want to see duplicate tuples in the result of a query. • When an aggregate function is applied to tuples, in most cases we do not want to eliminate duplicates.

  43. Tables as Sets in SQL (2/4) • QUERY 11Retrieve the salary of every employee (Q11) and all distinct salary values (Q11A). • Q11: SELECT ALL SALARY FROM EMPLOYEE; • Q11A: SELECT DISTINCT SALARYFROM EMPLOYEE;

  44. Tables as Sets in SQL (3/4) • SQL has directly incorporated some of the set operations of relational algebra. There is a set union operation (UNION), and in SQL2 there are also set difference (EXCEPT) and set intersection (INTERSECT) operations. • The relations resulting from these set operations are sets of tuples; that is, duplicate tuples are eliminated from the result.

  45. Tables as Sets in SQL (4/4) • QUERY 4Make a list of all project numbers for projects that involve an employee whose last name is ‘Smith’, either as a worker or as a manager of the department that controls the project. • Q4: (SELECT DISTINCT PNUMBER FROM PROJECT, DEPARTMENT, EMPLOYEEWHERE DNUM=DNUMBER AND MGRSSN=SSN AND LNAME=‘Smith’)UNION(SELECT DISTINCT PNUMBERFROM PROJECT, WORKS_ON, EMPLOYEEWHERE PNUMBER=PNO AND ESSN=SSN AND LNAME=‘Smith’);

  46. Substring Comparisons, Arithmetic Operators, and Ordering (1/5) • QUERY 12Retrieve all employees whose address is in Houston, Texas. • Q12: SELECT FNAME, LNAME FROM EMPLOYEE WHERE ADDRESS LIKE ‘%Houston,TX%’;

  47. Substring Comparisons, Arithmetic Operators, and Ordering (2/5) • QUERY 12AFind all employees who were born during the 1950s. • Q12A: SELECT FNAME, LNAME FROM EMPLOYEE WHERE BDATE LIKE’_ _ 5 _ _ _ _ _ _ _’;

  48. Substring Comparisons, Arithmetic Operators, and Ordering (3/5) • QUERY 13Show the resulting salaries if every employee working on the ‘ProductX’ project is given a 10 percent raise. • Q13: SELECT FNAME, LNAME, 1.1*SALARYFROM EMPLOYEE, WORKS_ON, PROJECTWHERE SSN=ESSN AND PNO=PNUMBER AND PNAME=‘ProductX’;

More Related