Databases Exam #3

DML (Data Manipulation Language)

A part of SQL that is used query, insert, update and delete and select data from a database.

DDL (Data Definition Language)

A part of SQL that is used to create and modify objects of a database such as tables, views, functions and stored procedures.

Unavailable or withheld value

A person has a home phone but does not want it to be listed, so it is withheld and represented as NULL in the database.

Unknown value

A person's date of birth is not known, so it is represented by NULL in the database. An example of the other case of unknown would be NULL for a person's home phone because it is not known whether or not the person has a home phone.

select-project-join

A query that involves only selection and join conditions plus projection attributes is known as: 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 birth date. Q2: SELECT Pnumber, Dnum, Lname, Address, Bdate FROM PROJECT, DEPARTMENT, EMPLOYEE WHERE Dnum = Dnumber AND Mgr_ssn = Ssn AND Plocation = 'Stafford';

single (scalar) value

If a nested query returns a single attribute and a single tuple, the query result will be a

Numeric data types

(INTEGER or INT, and SMALLINT) and floating-point (real) numbers of various precision (FLOAT or REAL, and 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.

Schema

(You don't always have to create a schema) is created via the CREATE SCHEMA statement, which can include all the schema elements' definitions. For example, the following statement creates a schema called COMPANY owned by the user with authorization identifier 'Jsmith'. Note that each statement in SQL ends with a semicolon. CREATE SCHEMA COMPANY AUTHORIZATION 'Jsmith';

inner join

(only pairs of tuples that match the join condition are retrieved, same as JOIN). The default type of join in a joined table. where a tuple is included in the result only if a matching tuple exists in the other relation. For example, in query Q8A, only employees who have a supervisor are included in the result; an EMPLOYEE tuple whose value for Super_ssn is NULL is excluded.

SELECT statement

<attribute list> is a list of attribute names whose values are to be retrieved by the query. Query 0. Retrieve the birth date 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';

WHERE clause

<condition> is a conditional (Boolean) expression that identifies the tuples to be retrieved by the query. In SQL, the basic logical comparison operators for comparing attribute values with one another and with literal constants are =, <, <=, >, >=, and <>. These correspond to the relational algebra operators =, <, ≤, >, ≥, and ≠, respectively, and to the C/C++ programming language operators =, <, <=, >, >=, and !=. Query 0. Retrieve the birth date 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';

FROM clause

<table list> is a list of the relation names required to process the query. Query 0. Retrieve the birth date 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';

Not applicable attribute

An attribute LastCollegeDegree would be NULL for a person who has no college degrees because it does not apply to that person.

AND, OR, and NOT

Boolean operators

a default value

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 for attributes that do not have the NOT NULL constraint.

CHECK constraint

For example, suppose that department numbers are restricted to integer numbers between 1 and 20; then, we can change the attribute declaration of Dnumber in the DEPARTMENT table: Dnumber INT NOT NULL CHECK (Dnumber > 0 AND Dnumber < 21);

HAVING

For example, suppose that we want to modify Query 25 so that only projects with more than two employees appear in the result. SQL provides a HAVING clause, which can appear in conjunction with a GROUP BY clause, for this purpose. HAVING provides a condition on the summary information regarding the group of tuples associated with each value of the grouping attributes. Only the groups that satisfy the condition are retrieved in the result of the query. This is illustrated by Query 26. Query 26. For each project on which more than two employees work, retrieve the project number, the project name, and the number of employees who work on the project. Q26: SELECT Pnumber, Pname, COUNT (*) FROM PROJECT, WORKS_ON WHERE Pnumber = Pno GROUP BY Pnumber, Pname HAVING COUNT (*) > 2;

Grouping (same as group by)

Grouping is used to create subgroups of tuples before summarization. Grouping and aggregation are required in many database applications, and we will introduce their use in SQL through examples.

referential triggered action clause

However, the schema designer can specify an alternative action to be taken 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. We illustrate this with the examples shown in Figure 6.2. Here, the database designer chooses ON DELETE SET NULL and ON UPDATE CASCADE for the foreign key Super_ssn of EMPLOYEE. This means that if the tuple for a supervising employee is deleted, the value of Super_ssn is automatically set to NULL for all employee tuples that were referencing the deleted employee tuple. On the other hand, if the Ssn value for a supervising employee is updated (say, because it was entered incorrectly), the new value is cascaded to Super_ssn for all employee tuples referencing the updated employee tuple.

INSERT Command

INSERT is used to add a single tuple (row) to a relation (table). We must specify the relation name and a list of values for the tuple. The values should be listed in the same order in which the corresponding attributes were specified in the CREATE TABLE command.

OUTER JOIN

If the user requires that all employees be included, a different type of join called. SELECT E.Lname AS Employee_name, S.Lname AS Supervisor_name FROM (EMPLOYEE AS E LEFT OUTER JOIN EMPLOYEE AS S ON E.Super_ssn = S.Ssn);

DROP VIEW

If we do not need a view anymore, we can use the DROP VIEW command to dispose of it. For example, to get rid of the view V1, we can use the SQL statement in V1A: V1A: DROP VIEW WORKS_ON1;

DISTINCT in the SELECT clause

If we do want to eliminate duplicate tuples from the result of an SQL query, we use the keyword DISTINCT in the SELECT clause, meaning that only distinct tuples should remain in the result. In general, a query with SELECT DISTINCT eliminates duplicates, whereas a query with SELECT ALL does not.

set intersection (INTERSECT)

Intersection (set theory) Intersection of two sets: In mathematics, the intersection A ∩ B of two sets A and B is the set that contains all elements of A that also belong to B (or equivalently, all elements of B that also belong to A), but no other elements.

ORDER BY clause

ORDER BY <attribute list> Query 15. Retrieve a list of employees and the projects they are working on, ordered by department and, within each department, ordered alphabetically by last name, then first name. Q15: SELECT D.Dname, E.Lname, E.Fname, P.Pname FROM DEPARTMENT AS D, EMPLOYEE AS E, WORKS_ON AS W, PROJECT AS P WHERE D.Dnumber = E.Dno AND E.Ssn = W.Essn AND W.Pno = P.Pnumber ORDER BY D.Dname, E.Lname, E.Fname;

BETWEEN

Query 14. Retrieve all employees in department 5 whose salary is between $30,000 and $40,000. Q14: SELECT * FROM EMPLOYEE WHERE (Salary BETWEEN 30000 AND 40000) AND Dno = 5; The condition (Salary BETWEEN 30000 AND 40000) in Q14 is equivalent to the condition ((Salary >= 30000) AND (Salary <= 40000)).

NULL

Rather than using = or <> to compare an attribute value to NULL, SQL uses the comparison operators IS or IS NOT. This is because SQL considers each NULL value as being distinct from every other NULL value, so equality comparison is not appropriate. It follows that when a join condition is specified. NULL values are discarded when aggregate functions are applied to a particular column (attribute); the only exception is for COUNT(*) because tuples instead of values are counted. Query 18. Retrieve the names of all employees who do not have supervisors. Q18: SELECT Fname, Lname FROM EMPLOYEE WHERE Super_ssn IS NULL;

CREATE ASSERTION

Specify a query that selects any tuples that violate the desired condition Use only in cases where it goes beyond a simpleCHECK which applies to individual attributes and domains. CREATE ASSERTION SALARY_CONSTRAINT CHECK ( NOT EXISTS ( SELECT * FROM EMPLOYEE E, EMPLOYEE M, DEPARTMENT D WHERE E.Salary>M.Salary AND E.Dno = D.Dnumber AND D.Mgr_ssn = M.Ssn ) );

CASE

Suppose we want to give employees different raise amounts depending on which department they work for; for example, employees in department 5 get a $2,000 raise, those in department 4 get $1,500 and those in department 1 get $3,000 (see Figure 5.6 for the employee tuples). Then we could re-write the update operation U6 from Section 6.4.3 as U6′: U6′: UPDATE EMPLOYEE SET Salary = CASE WHEN Dno = 5 THEN Salary + 2000 WHEN Dno = 4 THEN Salary + 1500 WHEN Dno = 1 THEN Salary + 3000 ELSE Salary + 0 ;

DELETE Command

The DELETE command removes tuples from a relation. It includes a WHERE clause, similar to that used in an SQL query, to select the tuples to be deleted. Tuples are explicitly deleted from only one table at a time. U4A: DELETE FROM EMPLOYEE WHERE Lname = 'Brown';

Group by (same as grouping)

The GROUP BY clause specifies the grouping attributes, which should also appear in the SELECT clause, so that the value resulting from applying each aggregate function to a group of tuples appears along with the value of the grouping attribute(s). Query 24. For each department, retrieve the department number, the number of employees in the department, and their average salary. Q24: SELECT Dno, COUNT (*), AVG (Salary) FROM EMPLOYEE GROUP BY Dno;

projection attributes

The SELECT clause of SQL specifies the attributes whose values are to be retrieved, which are called the ____________ ________in relational algebra

UNIQUE clause

The UNIQUE clause can also be specified directly for a unique key if it is a single attribute, as in the following example: Dname VARCHAR(15) UNIQUE,

UPDATE Command

The UPDATE command is used to modify attribute values of one or more selected tuples. As in the DELETE command, a WHERE clause in the UPDATE command selects the tuples to be modified from a single relation. However, updating a primary key value may propagate to the foreign key values of tuples in other relations if such a referential triggered action is specified in the referential integrity constraints of the DDL (see Section 6.2.2). An additional SET clause in the UPDATE command specifies the attributes to be modified and their new values. U5: UPDATE PROJECT SET Plocation = 'Bellaire', Dnum = 5 WHERE Pnumber = 10; An example is to give all employees in the 'Research' department a 10% raise in salary: UPDATE EMPLOYEE SET Salary = Salary * 1.1 WHERE Dno = 5;

reject

The default action that SQL takes for an integrity violation is to reject the update operation that will cause a violation, which is known as the RESTRICT option.

ECA (Event, Condition, Action) for the trigger

The event(s): These are usually database update operations that are explicitly applied to the database. In this example the events are: inserting a new employee record, changing an employee's salary, or changing an employee's supervisor. The person who writes the trigger must make sure that all possible events are accounted for. In some cases, it may be necessary to write more than one trigger to cover all possible cases. These events are specified after the keyword BEFORE in our example, which means that the trigger should be executed before the triggering operation is executed. An alternative is to use the keyword AFTER, which specifies that the trigger should be executed after the operation specified in the event is completed. The condition that determines whether the rule action should be executed: Once the triggering event has occurred, an optional condition may be evaluated. If no condition is specified, the action will be executed once the event occurs. If a condition is specified, it is first evaluated, and only if it evaluates to true will the rule action be executed. The condition is specified in the WHEN clause of the trigger. The action to be taken: The action is usually a sequence of SQL statements, but it could also be a database transaction or an external program that will be automatically executed. In this example, the action is to execute the stored procedure INFORM_SUPERVISOR.

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, as we discuss in Chapter 7. Giving names to constraints is optional.

base tables (or base relations)

The relations declared through CREATE TABLE statements. this means that the table and its rows are actually created and stored as a file by the DBMS.

CREATE VIEW

The view is given a (virtual) table name (or view name), a list of attribute names, and a query to specify the contents of the view. If none of the view attributes results from applying functions or arithmetic operations, we do not have to specify new attribute names for the view, since they would be the same as the names of the attributes of the defining tables in the default case. V1: CREATE VIEW WORKS_ON1 AS SELECT Fname, Lname, Pname, Hours FROM EMPLOYEE, PROJECT, WORKS_ON WHERE Ssn = Essn AND Pno = Pnumber; V2: CREATE VIEW DEPT_INFO(Dept_name, No_of_emps, Total_sal) AS SELECT Dname, COUNT (*), SUM (Salary) FROM DEPARTMENT, EMPLOYEE WHERE Dnumber = Dno GROUP BY Dname;

LIKE comparison operator

This can be used for string pattern matching. Partial strings are specified using two reserved characters: % replaces an arbitrary number of zero or more characters, and the underscore (_) replaces a single character.

set union (UNION)

UNION is an SQL operator which combines the result of two or more SELECT queries and provides the single set in the output.

what are advantages of sql?

You can go from one DBMS to another and be able to create and manipulate the database, if they are using the support the standard sql.

Catalog

a named collection of schemas.2 Database installations typically have a default environment and schema, so when a user connects and logs in to that database installation, the user can refer directly to tables and other constructs within that schema without having to specify a particular schema name. A catalog always contains a special schema called INFORMATION_SCHEMA, which provides information on all the schemas in the catalog and all the element descriptors in these schemas.

IN operator

a number of other comparison operators can be used to compare a single value v (typically an attribute name) to a set or multiset v (typically a nested query). The = ANY (or = SOME) operator returns TRUE if the value v is equal to some value in the set V and is hence equivalent to IN.

INTERVAL data type

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.

WITH

allows a user to define a table that will only be used in a particular query; it is somewhat similar to creating a view (see Section 7.3) that will be used only in one query and then dropped. Q28′: WITH BIGDEPTS (Dno) AS ( SELECT Dno FROM EMPLOYEE GROUP BY Dno HAVING COUNT (*) > 5) SELECT Dno, COUNT (*) FROM EMPLOYEE WHERE Salary>40000 AND Dno IN BIGDEPTS GROUP BY Dno; In Q28′, we defined in the WITH clause a temporary table BIG_DEPTS whose result holds the Dno's of departments with more than five employees, then used this table in the subsequent query. Once this query is executed, the temporary table BIGDEPTS is discarded.

SQL (Structured Query Language)

an international standard language for processing a database. Used to create and manipulate databases even when you go from one DBMSs.

EXISTS and UNIQUE

are Boolean functions that return TRUE or FALSE; hence, they can be used in a WHERE clause condition. The EXISTS function in SQL is used to check whether the result of a nested query is empty (contains no tuples) or not. The result of EXISTS is a Boolean value TRUE if the nested query result contains at least one tuple, or FALSE if the nested query result contains no tuples. We illustrate the use of EXISTS—and NOT EXISTS—with some examples.

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.

Aggregate functions

are used to summarize information from multiple tuples into a single-tuple summary. A number of built-in aggregate functions exist: COUNT, SUM, MAX, MIN, and AVG.2 The COUNT function returns the number of tuples or values as specified in a query. The functions SUM, MAX, MIN, and AVG can be applied to a set or multiset of numeric values and return, respectively, the sum, maximum value, minimum value, and average (mean) of those values. These functions can be used in the SELECT clause or in a HAVING clause (which we introduce later). The functions MAX and MIN can also be used with attributes that have nonnumeric domains if the domain values have a total ordering among one another.

Data Types

attributes include numeric, character string, bit string, Boolean, date, and time.

recursive relationship

between tuples of the same type is the relationship between an employee and a supervisor. This relationship is described by the foreign key Super_ssn of the EMPLOYEE relation in Figures 5.5 and 5.6, and it relates each employee tuple (in the role of supervisee) to another employee tuple (in the role of supervisor). An example of a recursive operation is to retrieve all supervisees of a supervisory employee e at all levels—that is, all employees e′ directly supervised by e, all employees e′ directly supervised by each employee e′, all employees e″′ directly supervised by each employee e″, and so on.

ASC

can be used to specify ascending order explicitly

LEFT OUTER JOIN

every tuple in the left table must appear in the result; if it does not have a matching tuple, it is padded with NULL values for the attributes of the right table

RIGHT OUTER JOIN

every tuple in the right table must appear in the result; if it does not have a matching tuple, it is padded with NULL values for the attributes of the left table. really don't use this one.

TIME data type

has at least eight positions, with the components HOUR, MINUTE, and SECOND in the form HH:MM:SS. 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.

DATE data type

has ten positions, and its components are YEAR, MONTH, and DAY in the form YYYY-MM-DD.

Boolean data type

has the traditional values of TRUE or FALSE. In SQL, because of the presence of NULL values, a three-valued logic is used, so a third possible value for a Boolean data type is UNKNOWN.

DESC

if we want to see the result in a descending order of values.

explicit set of values

in the WHERE clause, rather than a nested query. Such a set is enclosed in parentheses in SQL. Query 17. Retrieve the Social Security numbers of all employees who work on project numbers 1, 2, or 3.

TIMESTAMP data type

includes the DATE and TIME fields, plus a minimum of six positions for decimal fractions of seconds and an optional WITH TIME ZONE qualifier. Literal values are represented by single-quoted strings preceded by the keyword TIMESTAMP, with a blank space between data and time; for example, TIMESTAMP '2014-09-27 09:12:47.648302'.

joined table

incorporated into SQL to permit users to specify a table resulting from a join operation in the FROM clause of a query. This construct may be easier to comprehend than mixing together all the select and join conditions in the WHERE clause. For example, consider query Q1, which retrieves the name and address of every employee who works for the 'Research' department. It may be easier to specify the join of the EMPLOYEE and DEPARTMENT relations in the WHERE clause, and then to select the desired tuples and attributes. This can be written in SQL as in Q1A: Q1A: SELECT Fname, Lname, Address FROM (EMPLOYEE JOIN DEPARTMENT ON Dno = Dnumber) WHERE Dname = 'Research';

separate group

is created for all tuples with a NULL value in the grouping attribute. For example, if the EMPLOYEE table had some tuples that had NULL for the grouping attribute Dno, there would be a separate group for those tuples in the result.

Referential integrity

is specified via the FOREIGN KEY clause. A referential integrity constraint can be violated when tuples are inserted or deleted, or when a foreign key or primary key attribute value is updated.

CREATE TABLE command

is used to specify a new relation by giving it a name and specifying its attributes and initial constraints. The attributes are specified first, and each attribute is given a name, a data type to specify its domain of values, and possibly attribute constraints, such as NOT NULL.

constraint NOT NULL

may be specified if NULL is not permitted for a particular attribute. This is always implicitly specified for the attributes that are part of the primary key of each relation, but it can be specified for any other attributes whose values are required not to be NULL

set difference (EXCEPT)

set-difference is a relational operator that takes all Tuples in the relation 1, but not in relation 2 It's also known as:

Specifying Constraints on Tuples Using CHECK

table constraints can be specified through additional CHECK clauses at the end of a CREATE TABLE statement. These can be called row-based constraints because they apply to each row individually and are checked whenever a row is inserted or modified. For example, suppose that the DEPARTMENT table in Figure 6.1 had an additional attribute Dept_create_date, which stores the date when the department was created. Then we could add the following CHECK clause at the end of the CREATE TABLE statement for the DEPARTMENT table to make sure that a manager's start date is later than the department creation date. CHECK (Dept_create_date <= Mgr_start_date);

selection condition

the WHERE clause specifies the Boolean condition that must be true for any retrieved tuple, which is known as the _____________ ____________in relational algebra.

NATURAL JOIN

two relations R and S, no join condition is specified; an implicit EQUIJOIN condition for each pair of attributes with the same name from R and S is created.

CREATE TRIGGER

used to monitor the database. Suppose we want to check whenever an employee's salary is greater than the salary of his or her direct supervisor in the COMPANY database. R5: CREATE TRIGGER SALARY_VIOLATION BEFORE INSERT OR UPDATE OF SALARY, SUPERVISOR_SSN ON EMPLOYEE FOR EACH ROW WHEN ( NEW.SALARY > ( SELECT SALARY FROM EMPLOYEE WHERE SSN = NEW.SUPERVISOR_SSN ) ) INFORM_SUPERVISOR(NEW.Supervisor_ssn, NEW.Ssn );

nested queries

which are complete select-from-where blocks within another SQL query. That other query is called the outer query. These nested queries can also appear in the WHERE clause or the FROM clause or the SELECT clause or other SQL clauses as needed.