Intr to Database Systems
Intr to Database Systems CS 4400
Popular in Course
Popular in ComputerScienence
This 0 page Class Notes was uploaded by Alayna Veum on Monday November 2, 2015. The Class Notes belongs to CS 4400 at Georgia Institute of Technology - Main Campus taught by Shamkant Navathe in Fall. Since its upload, it has received 7 views. For similar materials see /class/234045/cs-4400-georgia-institute-of-technology-main-campus in ComputerScienence at Georgia Institute of Technology - Main Campus.
Reviews for Intr to Database Systems
Report this Material
What is Karma?
Karma is the currency of StudySoup.
You can buy or earn more Karma at anytime and redeem it for class notes, study guides, flashcards, and more!
Date Created: 11/02/15
1 Chapters 8 9 SQL and QBE NOTES V SQL A Relational Database Language and Query By Example QBE Chapters 8 9 from book Data De nition in SQL 2 Retrieval Queries in SQL 00lOU IIgtL J 21 Simple SQL Queries 22 Aliases and DISTINCT Unspeci ed WHERE clause 23 Set Operations Nesting of Queries Set Comparisons 24 The EXISTS function NULLs Explicit Sets 25 Aggregate Functions and Grouping 26 Substring Comparisons Arithmetic ORDER BY 27 Summary of SQL Queries Specifying Updates in SQL Relational Views in SQL Creating Indexes in SQL Embedding SQL in a Programming Language Recent Advances in SQL Query By Example QBE Relational Database Review Ramez Elmasri and Shamkant B Navathe Chapters 8 9 SQL and QBE 1 Data Definition in SQL Used to CREATE DROP and ALTER the descriptions of the tables relations of a database CREATE TABLE Speci es a new base relation by giving it a name and specifying each of its attributes and their data types INTEGER FLOAT DECMALij CHARn VARCHARn A constraint NOT NULL may be speci ed on an attribute CREATE TABLE DEPARTMENT DN AME VARCHARl 0 NOT NULL DNUMBER INTEGER NOT NULL MGRSSN CHAR9 MGRSTARTDATE CHAR9 In SQL2 can use the CREATE TABLE command for specifying the primary key attributes secondary keys and referential integrity constraints foreign keys Key attributes can be speci ed Via the PRHVIARY KEY and UNIQUE phrases CREATE TABLE DEPT DNAME VARCHAR10 NOT NULL DNUMBER INTEGER NOT NULL MGRSSN CHAR9 MGRSTARTDATE CHAR9 PRIMARY KEY DNUMBER UNIQUE DNAME Relational Database Review 5 2 Ramez Elmasri and Shamkant B Navathe Chapters 8 9 SQL and QBE FOREIGN KEY MGRSSN REFERENCES EMP Relational Database Review 5 3 Ramez Elmasri and Shamkant B Navathe Chapters 8 9 SQL and QBE DROP TABLE Used to remove a relation base table and its de nition The relation can no longer be used in queries updates or any other commands since its description no longer exists Example DROP TABLE DEPENDENT ALTER TABLE Used to add an attribute to one of the base relations The new attribute will have NULLs in all the tuples of the relation right after the command is executed hence the NOT NULL constraint is not allowed for such an attribute Example ALTER TABLE EMPLOYEE ADD JOB VARCHAR12 The database users must still enter a value for the new attribute JOB for each EMPLOYEE tuple This can be done using the UPDATE command Relational Database Review 5 4 Ramez Elmasri and Shamkant B Navathe Chapters 8 9 SQL and QBE Features Added in SQLZ CREATE SCHEMA Specifies a new database schema by giving it a name CREATE SCHEMA COMPANY REFERENTIAL INTEGRITY OPTIONS In SQL2 we can specify CASCADE or SET NULL or SET DEFAULT on referential integrity constraints foreign keys CREATE TABLE DEPT DNAME VARCHAR10 NOT NULL DNUMBER INTEGER NOT NULL MGRSSN CHAR9 MGRSTARTDATE CHAR9 PRIMARY KEY DNUMBER UNIQUE DNAME FOREIGN KEY MGRSSN REFERENCES EMP ON DELETE SET DEFAULT ON UPDATE CASCADE CREATE TABLE EMP E NAME VARCHAR30 NOT NULL ESSN CHAR9 BDATE DATE DNO INTEGER DEFAULT 1 SUPERSSN CHAR9 PRIMARY KEY ESSN FOREIGN KEY DNO REFERENCES DEPT ON DELETE SET DEFAULT ON UPDATE CASCADE FOREIGN KEY SUPERSSN REFERENCES EMP ON DELETE SET NULL ON UPDATE CASCADE Relational Database Review 5 5 Ramez Elmasri and Shamkant B Navathe Chapters 8 9 SQL and QBE Many other features eg joined relations all not likely to be all implemented Relational Database Review 5 6 Ramez Elmasri and Shamkant B Navathe Chapters 8 9 SQL and QBE Additional Data Types in SQLZ Has DATE TIME and TIMESTAMP data types DATE Made up of yearmonthday in the format yyyymmdd TIME Made up of hourzminutezsecond in the format hhmmss TIMEi Made up of hourzminutezsecond plus i additional digits specifying fractions of a second format is hhmmssiii TIMESTAMP Has both DATE and THVIE components INTERVAL Speci es a relative value rather than an absolute value Can be DAY TIME intervals or YEARMONTH intervals Can be positive or negative when added to or subtracted from an absolute value the result is an absolute value Relational Database Review 5 7 Ramez Elmasri and Shamkant B Navathe Chapters 8 9 SQL and QBE 2 Retrieval Queries in SQL SQL has one basic statement for retrieving information from a database the SELECT statement This is not the same as the SELECT operation of the relational algebra Important distinction between SQL and the formal relational model SQL allows a table relation to have two or more tuples that are identical in all their attribute values Hence an SQL relation table is a mullisel 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 Basic form of the SQL SELECT statement is called a mapping or a SELEC T FROM WHERE block SELECT ltattribute listgt FROM lttable listgt WHERE ltconditiongt o ltattribute listgt is a list of attribute names whose values are to be retrieved by the query 0 lttable listgt is a list of the relation names required to process the query 0 ltconditiongt is a conditional Boolean expression that identi es the tuples to be retrieved by the query Relational Database Review 5 8 Ramez Elmasri and Shamkant B Navathe Chapters 8 9 SQL and QBE 21 Simple SQL Queries Basic SQL queries correspond to using the SELECT PROJECT and JOIN operations of the relational algebra All subsequent examples use the COMPANY database Example of a simple query on one relation Query 0 Retrieve the birthdate and address of the employee whose name is 39John B Smith39 Q0 SELECT BDATE ADDRESS FROM EMPLOYEE WHERE FNAME39J0hn39 AND MINIT39B39 AND LNAME39Smith39 Similar to a SELECTPROJECT pair of relational algebra operations the SELECTclause speci es the projection attributes and the WHEREclause speci es the selection condition However the result of the query may contain duplicate tuples Relational Database Review 5 9 Ramez Elmasri and Shamkant B Navathe Chapters 8 9 SQL and QBE Query 1 Retrieve the name and address of all employees who work for the 39Research39 department Q1 SELECT FNAME LNAME ADDRESS FROM EMPLOYEE DEPARTMENT WHERE DNAME39Research39 AND DNUMBERDNO Similar to a SELECTPROJECTJOIN sequence of relational algebra operations DNAME39Research39 is a selection condition corresponds to a SELECT operation in relational algebra DNUTVIBERDNO is a join condition corresponds to a JOIN operation in relational algebra Query 2 For every project located in 39Stafford39 list the project number the controlling department number and the department manager39s last name address and birthdate Q2 SELECT PNUMBER DNUM LNAME BDATE ADDRESS FROM PROJECT DEPARTMENT EMPLOYEE WHERE DNUMDNUMBER AND MGRSSNSSN AND PLOCATION39Staff0rd39 In Q2 there are two join conditions The join condition DNUTVIDNUTVIBER relates a project to its controlling department The join condition MGRS SNS SN relates the controlling department to the employee who manages that department Relational Database Review 5 l 0 Ramez Elmasri and Shamkant B Navathe Chapters 8 9 SQL and QBE 22 Aliases and DISTINCT Empty WHEREclause In SQL we can use the same name for two or more attributes as long as the attributes are in di erenl relations A query that refers to two or more attributes with the same name must quali the attribute name with the relation name by prefixing the relation name to the attribute name Example EMPLOYEELNAME DEPARTMENTDNAME ALIASES Some queries need to refer to the same relation twice In this case aliases are given to the relation name Query 8 For each employee retrieve the employee39s name and the name of his or her immediate supervisor Q8 SELECT EFNAME ELNAME SFNAME SLNAME FROM EMPLOYEE E S WHERE ESUPERSSNSSSN In Q8 the alternate relation names E and S are called aliases for the ElVIPLOYEE relation We can think of E and S as two di erenl copies of the EMPLOYEE relation E represents employees in the role of supervisees and S represents employees in the role of supervisors Aliasing can also be used in any SQL query for convenience Can also use the AS keyword to specify aliases Q8 SELECT EFNAME ELNAME SFNAME SLNAME FROM EMPLOYEE AS E EMPLOYEE AS S Relational Database Review 5 l 1 Ramez Elmasri and Shamkant B Navathe Chapters 8 9 SQL and QBE WHERE ESUPERSSNSSSN Relational Database Review 5 12 Ramez Elmasri and Shamkant B Navathe Chapters 8 9 SQL and QBE UNSPECIFIED WHEREclause A missing WHEREclause indicates no condition hence all tuples of the relations in the FROMclause are selected This is equivalent to the condition WHERE TRUE Quegy 9 Retrieve the SSN values for all employees Q9 SELECT SSN FROM EMPLOYEE If more than one relation is speci ed in the FROMclause and there is no join condition then the CART ESIAN PRODUCT of tuples is selected Example Q10 SELECT SSN DNAME FROM EMPLOYEE DEPARTMENT It is extremely important not to overlook specifying any selection and join conditions in the WHEREclause otherwise incorrect and very large relations may result USE OF To retrieve all the attribute values of the selected tuples a is used which stands for all the attributes Examples Relational Database Review 513 Ramez Elmasri and Shamkant B Navathe Chapters 8 9 SQL and QBE QlC SELECT FROM EMPLOYEE WHERE DNO5 QlD SELECT FROM EMPLOYEE DEPARTMENT WHERE DNAME39Research39 AND DNODNUMBER USE OF DISTINCT SQL does not treat a relation as a set duplicate luples can appear To eliminate duplicate tuples in a query result the keyword DISTINCT is used For example the result of Qll may have duplicate SALARY values whereas Ql lA does not have any duplicate values SELECT FROM Q11 Q11A SELECT FROM SALARY EMPLOYEE DISTINCT SALARY EMPLOYEE Relational Database Review Ramez Elmasri and Shamkant B Navathe Chapters 8 9 SQL and QBE 3 Set Operations Nesting of Queries Set Comparisons SET OPERATIONS SQL has directly incorporated some set operations There is a union operation UNION and in some versions of SQL there are set difference MINUS and intersection INTERSECT operations The resulting relations of these set operations are sets of tuples duplicate tuples are eliminated from the result The set operations apply only to union compatible relations the two relations must have the same attributes and the attributes must appear in the same order Que 4 Make a list of all project numbers for projects that involve an employee whose last name is 39Smith39 as a worker or as a manager of the department that controls the project Q4 SELECT PNAME FROM PROJECT DEPARTMENT EMPLOYEE WHERE DNUMDNUMBER AND MGRSSNSSN AND LNAME39Smith39 UNION SELECT PNAME FROM PROJECT WORKSON EMPLOYEE WHERE PNUMBERPNO AND ESSNSSN AND LNAME39Smith39 NESTING OF QUERIES Relational Database Review 5 15 Ramez Elmasri and Shamkant B Navathe Chapters 8 9 SQL and QBE A complete SELECT query called a nested query can be speci ed within the WHEREclause of another query called the outer query Many of the previous queries can be speci ed in an alternative form using nesting Que 1 Retrieve the name and address of all employees who work for the 39Research39 department Q1 SELECT FNAME LNAME ADDRESS FROM EMPLOYEE WHERE DNO IN SELECT DNUMBER FROM DEPARTMENT WHERE DNAME39Research39 The nested query selects the number of the 39Research39 department The outer query select an EMPLOYEE tuple if its DNO value is in the result of either nested query The comparison operator IN compares a value v with a set or multiset of values V and evaluates to TRUE if v is one of the elements in V In general we can have several levels of nested queries A reference to an unquali ed attribute refers to the relation declared in the innermost nested query In this example the nested query is not correlated with the outer query Relational Database Review 5 16 Ramez Elmasri and Shamkant B Navathe Chapters 8 9 SQL and QBE CORRELATED NESTED QUERIES If a condition in the WHEREclause of a nested query references an attribute of a relation declared in the outer query the two queries are said to be correlated The result of a correlated nested query is di erent for each tuple or combination of tuples of the relations the outer query Que 12 Retrieve the name of each employee who has a dependent with the same rst name as the employee Q12 SELECT EFNAME ELNAME FROM EMPLOYEE AS E WHERE ESSN IN SELECT ESSN FROM DEPENDENT WHERE ESSNESSN AND EFNAMEDEPENDENTNAME In Q12 the nested query has a different result for each tuple in the outer query A query written with nested SELECT FROM WHERE blocks and using the or IN comparison operators can always be expressed as a single block query For example Q12 may be written as in Q12A Q12A SELECT EFNAMEELNAME FROM EMPLOYEE E DEPENDENT D WHERE ESSNDESSN AND EFNAMEDDEPENDENTNAME Relational Database Review 5 17 Ramez Elmasri and Shamkant B Navathe Chapters 8 9 SQL and QBE The original SQL as speci ed for SYSTEM R also had a CONTAINS comparison operator which is used in conjunction with nested correlated queries This operator was dropped from the language possibly because of the dif culty in implementing it ef ciently Most implementations of SQL do not have this operator The CONTAINS operator compares two sets of values and returns TRUE if one set contains all values in the other set reminiscent of the division operation of algebra Que 3 Retrieve the name of each employee who works on all the projects controlled by department number 5 Q3 SELECT FNAME LNAME FROM EMPLOYEE WHERE SELECT PNO FROM WORKSON WHERE SSNESSN CONTAINS SELECT PNUMBER FROM PROJECT WHERE DNUM5 In Q3 the second nested query which is not correlated with the outer query retrieves the project numbers of all projects controlled by department 5 Relational Database Review 5 l 8 Ramez Elmasri and Shamkant B Navathe Chapters 8 9 SQL and QBE The rst nested query which is correlated retrieves the project numbers on which the employee works which is different for each employee luple because of the correlation 4 The EXISTS function NULLs Explicit Sets THE EXISTS FUNCTION EXISTS is used to check whether the result of a correlated nested query is empty contains no tuples or not We can formulate Query 12 in an alternative form that uses EXISTS as Q12B below Query 12 Retrieve the name of each employee who has a dependent with the same rst name as the employee Q12B SELECT FNAMELNAME FROM EMPLOYEE WHERE EXISTS SELECT FROM DEPENDENT WHERE SSNESSN AND FNAMEDEPENDENTNAME Que 6 Retrieve the names of employees who have no dependents Q6 SELECT FNAME LNAME FROM EMPLOYEE WHERE NOT EXISTS SELECT FROM DEPENDENT WHERE SSNESSN Relational Database Review 5 l 9 Ramez Elmasri and Shamkant B Navathe Chapters 8 9 SQL and QBE In Q6 the correlated nested query retrieves all DEPENDENT tuples related to an EMPLOYEE tuple If none exist the EMPLOYEE tuple is selected EXISTS is necessary for the expressive power of SQL EXPLICIT SETS It is also possible to use an explicit enumerated set of values in the WHEREclause rather than a nested query Quegy l3 Retrieve the social security numbers of all employees who work on project number 1 2 or 3 Q13 SELECT DISTINCT ESSN FROM WORKS0N WHERE PNO IN 12 3 NULLS IN SQL QUERIES SQL allows queries that check if a value is NULL missing or unde ned or not applicable SQL uses IS or IS NOT to compare NULLS because it considers each NULL value distinct from other NULL values so equality comparison is not appropriate Quegy l4 Retrieve the names of all employees who do not have supervisors Q14 SELECT FNAME LNAME FROM EMPLOYEE WHERE SUPERSSN IS NULL Relational Database Review 5 20 Ramez Elmasri and Shamkant B Navathe Chapters 8 9 SQL and QBE Note If a join condition is speci ed tuples with NULL values for the join attributes are not included in the result 25 Aggregate Functions and Grouping AGGREGATE FUNCTIONS Include COUNT SUM MAX MIN and AVG Query 15 Find the maximum salary the minimum salary and the average salary among all employees Q15SELECT MAXSALARY MIN SALARY AVGSALARY FROM EMPLOYEE Some SQL implementations may not allow more than one function in the SELECTclause Que 16 Find the maximum salary the minimum salary and the average salary among employees who work for the 39Research39 department Q16 SELECT MAXSALARY M1NSALARY AVGSALARY FROM EMPLOYEE DEPARTMENT WHERE DNODNUMBER AND DNAME39Research39 Queries 17 and 18 Retrieve the total number of employees in the company Q17 and the number of employees in the 39Research39 department Q18 Q17 SELECT COUNT FROM EMPLOYEE Q18 SELECT COUNT Relational Database Review 5 21 Ramez Elmasri and Shamkant B Navathe Chapters 8 9 SQL and QBE FROM EMPLOYEE DEPARTMENT WHERE DNODNUMBER AND DNAME39Research39 Relational Database Review 5 22 Ramez Elmasri and Shamkant B Navathe Chapters 8 9 SQL and QBE GROUPING In many cases we want to apply the aggregate functions to subgroups of tuples in a relation Each subgroup of tuples consists of the set of tuples that have the same value for the grouping attributes The function is applied to each subgroup independently SQL has a GROUP BYclause for specifying the grouping attributes which must also appear in the SELEC T clause Que 20 For each department retrieve the department number the number of employees in the department and their average salary Q20 SELECT DNO COUNT AVG SALARY FROM EMPLOYEE GROUP BY DNO In Q20 the EMPLOYEE tuples are divided into groupseach group having the same value for the grouping attribute DNO The COUNT and AVG functions are applied to each such group of tuples separately The SELECTclause includes only the grouping attribute and the functions to be applied on each group of tuples Relational Database Review 5 23 Ramez Elmasri and Shamkant B Navathe Chapters 8 9 SQL and QBE A join condition can be used in conjunction with grouping Que 21 For each project retrieve the project number project name and the number of employees who work on that project Q21 SELECT PNUMBER PNAME COUNT FROM PROJECT WORKSON WHERE PNUMBERPNO GROUP BY PNUMBER PNAME In this case the grouping and functions are applied after the joining of the two relations THE HAVINGCLAUSE Sometimes we want to retrieve the values of these functions for only those groups that satis certain conditions The HAVINGclause is used for specif ying a selection condition on groups rather than on individual tuples Query 22 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 Q22 SELECT PNUMBER PNAME COUNT FROM PROJECT W 0RKS0N WHERE PNUMBERPNO GROUP BY PNUMBER PNAME HAVING COUNT gt 2 Relational Database Review 5 24 Ramez Elmasri and Shamkant B Navathe Chapters 8 9 SQL and QBE 26 Substring Comparisons Arithmetic ORDER BY SUBSTRING COMPARISON The LIKE comparison operator is used to compare partial strings Two reserved characters are used 3939 or 3939 in some implementations replaces an arbitrary number of characters and 3939 replaces a single arbitrary character Query 25 Retrieve all employees whose address is in Houston Texas Here the value of the ADDRESS attribute must contain the sub string 39HoustonTX39 Q25 SELECT FNAME LNAME FROM EMPLOYEE WHERE ADDRESS LIKE 39HoustonTX o39 Query 26 Retrieve all employees who were born during the 1950s Here 39539 must be the 8th character of the string according to our format for date so the BDATE value is 39 539 with each underscore as a place holder for a single arbitrary character Q26 SELECT FNAME LNAME FROM EMPLOYEE WHERE BDATE LIKE 39 539 The LIKE operator allows us to get around the fact that each value is considered atomic and indivisible hence in SQL character string attribute values are not atomic Relational Database Review 5 25 Ramez Elmasri and Shamkant B Navathe Chapters 8 9 SQL and QBE ARITHMETIC OPERATIONS The standard arithmetic operators 3939 3939 3939 and 3939 for addition subtraction multiplication and division respectively can be applied to numeric values in an SQL query result Que 27 Show the effect of giving all employees who work on the 39ProductX39 project a 10 raise Q27 SELECT FNAME LNAME 11SALARY FROM EMPLOYEE WORKS0N PROJECT WHERE SSNESSN AND PNOPNUMBER AND PNAME39Pr0ductX39 ORDER BY The ORDER BY clause is used to sort the tuples in a query result based on the values of some attributes Que 28 Retrieve a list of employees and the projects each works in ordered by the employee39s department and within each department ordered alphabetically by employee last name Q28 SELECT DNAME LNAME FNAME PNAME FROM DEPARTMENT EMPLOYEE WORKS0N PROJECT WHERE DNUMBERDNO AND SSNESSN AND PNOPNUMBER ORDER BY DNAME LNAME The default order is in ascending order of values We can specify the keyword DESC if we want a descending order the keyword ASC can be used to explicitly specify ascending order even though it is the default Relational Database Review 5 26 Ramez Elmasri and Shamkant B Navathe Chapters 8 9 SQL and QBE 27 Summary of SQL Queries A query in SQL can consist of up to six clauses but only the rst two SELECT and FROM are mandatory The clauses are speci ed in the following order SELECT ltattribute listgt FROM lttable listgt WHERE ltc0nditi0ngt GROUP BY ltgr0uping attributesgt HAVING ltgr0up c0nditi0ngt ORDER BY ltattribute listgt The SELECTclause lists the attributes or functions to be retrieved The FROMclause speci es all relations or aliases needed in the query but not those needed in nested queries The WHEREclause speci es the conditions for selection and join of tuples from the relations speci ed in the FROM clause GROUP BY speci es grouping attributes HAVING speci es a condition for selection of groups ORDER BY speci es an order for displaying the result of a query A query is evaluated by rst applying the WHEREclause then GROUP BY and HAVING and nally the SELECT clause Relational Database Review 5 27 Ramez Elmasri and Shamkant B Navathe Chapters 8 9 SQL and QBE 3 Specifying Updates in SQL There are three SQL commands to modify the database INSERT DELETE and UPDATE INSERT In its simplest form it is used to add one or more tuples to a relation Attribute values should be listed in the same order as the attributes were speci ed in the CREATE TABLE command Example U1 INSERT INTO EMPLOYEE VALUES 39Richard3939K3939Marini39 3965329865339 3930DEC5239 3998 Oak F0restKatyTX39 370003998765432139 4 An alternate form of INSERT speci es 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 U1A INSERT INTO EMPLOYEE FNAME LNAME SSN VALUES 39Richard39 39Marini39 3965329865339 Important Note Only the constraints speci ed in the DDL commands are automatically enforced by the DBMS when updates are applied to the database Relational Database Review 5 28 Ramez Elmasri and Shamkant B Navathe Chapters 8 9 SQL and QBE Another variation of INSERT allows insertion of multiple tuples resulting from a query into a relation Example Suppose we want to create a temporary table that has the name number of employees and total salaries for each department A table DEPTSINFO is created by U3A and is loaded with the summary information retrieved from the database by the query in U3B U3A CREATE TABLE DEPTSINFO DEPTNAME VARCHAR10 N00FEMPS INTEGER TOTALSAL INTEGER U3B INSERT INTO DEPTSINFO DEPTNAME N00FEMPS TOTALSAL SELECT DNAME COUNT SUM SALARY FROM DEPARTMENT EMPLOYEE WHERE DNUMBERDNO GROUP BY DNAME m The DEPTSINFO table may not be uptodate if we change the tuples in either the DEPARTMENT or the EMPLOYEE relations after issuing U3B We have to create a view see later to keep such a table up to date Relational Database Review 5 29 Ramez Elmasri and Shamkant B Navathe DELETE Chapters 8 9 SQL and QBE Removes tuples from a relation Includes a WHEREclause to select the tuples to be deleted Tuples are deleted from only one table at a time unless CASCADE is speci ed on a referential integrity constraint A missing WHEREclause specifies that all tuples in the relation are to be deleted the table then becomes an empty table The number of tuples deleted depends on the number of tuples in the relation that satisfy the WHEREclause Referential integrity should be enforced Examples U4A DELETE FROM WHERE U4B DELETE FROM WHERE U4C DELETE FROM WHERE U4D DELETE FROM Relational Database Review EMPLOYEE LNAME39Br0wn39 EMPLOYEE SSN3912345678939 EMPLOYEE DNO IN SELECT DNUMBER FROM DEPARTMENT WHERE DNAME39Research39 EMPLOYEE Ramez Elmasri and Shamkant B Navathe Chapters 8 9 SQL and QBE UPDATE Used to modify attribute values of one or more selected tuples A WHEREclause selects the tuples to be modi ed An additional SETclause speci es the attributes to be modi ed and their new values Each command modi es tuples in the same relation Referential integrity should be enforced Example Change the location and controlling department number of project number 10 to 39Bellaire39 and 5 respectively U5 UPDATE PROJECT SET PLOCATION 39Bellaire39 DNUM 5 WHERE PNUMBER10 Example Give all employees in the 39Research39 department a 10 raise in salary U6 UPDATE EMPLOYEE SET SALARY SALARY 11 WHERE DNO IN SELECT DNUMBER FROM DEPARTMENT WHERE DNAME39Research39 In this request the modi ed SALARY value depends on the original SALARY value in each tuple The reference to the SALARY attribute on the right of refers to the old SALARY value before modi cation The reference to the SALARY attribute on the left of refers to the new SALARY value after modi cation Relational Database Review 5 31 Ramez Elmasri and Shamkant B Navathe Chapters 8 9 SQL and QBE 4 Relational Views in SQL A View is a singlevirlual table that is derived from other tables The other tables could be base tables or previously de ned Views A View does not necessarily exist in physical form which limits the possible update operations that can be applied to Views There are no limitations on querying a View The CREATE VIEW command is used to specify a View by specifying a Virtual table name and a de ning query The View attribute names can be inherited from the attribute names of the tables in the de ning query Examples V1 CREATE VIEW WORKS0N1 AS SELECT FNAME LNAME PNAME HOURS FROM EMPLOYEE PROJECT WORKSON WHERE SSNESSN AND PNOPNUMBER V2 CREATE VIEW DEPTINFO DEPTNAME N00FEMPS TOTALSAL AS SELECT DNAME COUNT SUM SALARY FROM DEPARTMENT EMPLOYEE WHERE DNUMBERDNO GROUP BY DNAME In V1 the names of the View attribute names are inherited Relational Database Review 5 32 Ramez Elmasri and Shamkant B Navathe Chapters 8 9 SQL and QBE In V2 the View attribute names are listed using a onetoone correspondence with the entries in the SELECTclause of the de ning query Relational Database Review 5 33 Ramez Elmasri and Shamkant B Navathe Chapters 8 9 SQL and QBE QUERIES ON VIEWS Example Retrieve the last name and rst name of all employees who work on 39ProjectX39 QVl SELECT PNAME FNAME LNAME FROM WORKS0N1 WHERE PNAME39Pr0jectX39 Without the view WORKS0N1 this query speci cation would require two join conditions A view can be de ned to simplify frequently occurring queries The DBMS is responsible for keeping the view always upto date if the base tables on which the view is de ned are modi ed Hence the view is not realized at the time of view de nition but rather at the time we specify a query on the view A view is removed using the DROP VIEW command Example V1A DROP VIEW WORKS0N1 VZA DROP VIEW DEPTINFO Views can also be used as a security and authorization mechanism see Chapter 20 Relational Database Review 5 34 Ramez Elmasri and Shamkant B Navathe Chapters 8 9 SQL and QBE UPDATING OF VIEWS A view update operation may be mapped in multiple ways to update operations on the de ning base relations The topic of updating views is still an active research area Example Suppose we issue the command in UVl to update the WORKS0N1 view by modifying the PNAME attribute of 39John Smith39 from 39ProductX39 to 39ProductY39 UV1 UPDATE WORKS0N1 SET PNAME 39ProductY39 WHERE LNAME39Smith39 AND FNAME39J0hn39 AND PNAME39Pr0ductX39 This can be mapped into several updates on the base relations to give the desired update on the view Two possibilities are 1 Change the name of the 39ProductX39 tuple in the PROJECT relation to 39ProductY39 It is quite unlikely that the user who speci ed the view update UVl wants the update to be interpreted this way 1 UPDATE PROJECT SET PNAME 39ProductY39 WHERE PNAME 39ProductX39 2 Relate 39John Smith39 to the 39ProductY39 PROJECT tuple in place of the 39ProductX39 PROJECT tuple This is most likely the update the user means Relational Database Review 5 35 Ramez Elmasri and Shamkant B Navathe Chapters 8 9 SQL and QBE 2 UPDATE WORKSON SET PNO SELECT PNUMBER FROM PROJECT WHERE PNAME39Pr0ductY39 WHERE ESSN SELECT SSN FROM EMPLOYEE WHERE LNAME39Smith39 AND FNAME39J0hn39 AND PNO SELECT PNUMBER FROM PROJECT WHERE PNAME39Pr0ductX39 Some View updates may not make much sense for example modifying the TOTALSAL attribute of DEPTINFO as in UV2 UV2 MODIFY DEPT1NFO SET TOTALSAL100000 WHERE DNAME39Research39 In general we cannot guarantee that any View can be updated A View update is unambiguous only if one update on the base relations can accomplish the desired update effect on the View If a View update can be mapped to more than one update on the underlying base relations we must have a certain procedure to choose the desired update We can make the following general observations 0 A View with a single de ning table is updatable if the View attributes contain the primary key 0 Views de ned on multiple tables using joins are generally not updatable o Views de ned aggregate functions are not updatable 5 Creating Indexes in SQL Relational Database Review 5 36 Ramez Elmasri and Shamkant B Navathe Chapters 8 9 SQL and QBE An SQL base relation generally corresponds to a stored flle Statements can create and drop indexes on base relations These statements have been removed from SQL2 because they specify physical access paths not logical concepts One or more indexing attributes are speci ed for each index The CREATE INDEX statement is used Each index is given an index name 11 CREATE INDEX LNAMEINDEX ON EMPLOYEE LNAME The index entries are in ascending ASC order of the indexing attributes DESC speci es descending order An index can be created on a combination of attributes 12 CREATE INDEX NAMESINDEX ON EMPLOYEE LNAME ASC FNAME DESC MINIT Two options on indexes are UNIQUE and CLUSTER To specify the key constraint on the indexing attribute or combination of attributes the keyword UNIQUE is used 13 CREATE UNIQUE INDEX SSNINDEX ON EMPLOYEE SSN This is best done before any tuples are inserted in the relation An attempt to create a unique index on an existing base table will fail if the current tuples in the table do not obey the constraint Relational Database Review 5 37 Ramez Elmasri and Shamkant B Navathe Chapters 8 9 SQL and QBE A second option on index creation is to specify that the index is a clustering index using the keyword CLUSTER A base relation can have at most one clustering index but any number of nonclustering indexes Example 14 CREATE INDEX DNOINDEx 0N EMPLOYEE DNO CLUSTER A clustering and unigue index in SQL is similar to the primary index of Chapter 5 A clustering but nonunigue index in SQL is similar to the clustering index of Chapter 5 A nonclustering index is similar to the secondagy index of Chapter 5 Each DBMS will have its own index implementation technique in most cases some variation of the Btree data structure is used To drop an index we issue the DROP INDEX command The index name is needed to refer to the index when it is to be dropped Example 15 DROP INDEX DNOINDEX Relational Database Review 5 3 8 Ramez Elmasri and Shamkant B Navathe Chapters 8 9 SQL and QBE 6 Embedding SQL in 21 Programming Language SQL can also be used in conjunction with a general purpose programming language such as PASCAL COBOL or PLI The programming language is called the host language The embedded SQL statement is distinguished from programming language statements by pre xing it with a special character or command so that a preprocessor can extract the SQL statements In PLI the keywords EXEC SQL precede any SQL statement In some implementations SQL statements are passed as parameters in procedure calls We will use PASCAL as the host programming language and a quot35quot sign to identify SQL statements in the program Within an embedded SQL command we may refer to program variables which are pre xed by a quotquot sign The programmer should declare program variables to match the data types of the database attributes that the program will process These program variables may or may not have names that are identical to their corresponding attributes Relational Database Review 5 39 Ramez Elmasri and Shamkant B Navathe Chapters 8 9 SQL and QBE Example Write a program segment loop that reads a social security number and prints out some information from the corresponding EMPLOYEE tuple E1 LOOP 39Y39 while LOOP 39Y39 do begin writeln39input social security number 39 readlnSOCSECNUM SELECT FNAME MINIT LNAME SSN BDATE ADDRESS SALARY INTO EFNAME EMINIT ELNAME ESSN EBDATE EADDRESS ESALARY FROM EMPLOYEE WHERE SSNSOCSECNUM writeln EFNAME EMINIT ELNAME ESSN EBDATE EADDRESS ESALARY writeln39m0re social security numbers Y or N 39 readlnLOOP end In El a single luple is selected by the embedded SQL query that is why we are able to assign its attribute values directly to program variables In general an SQL query can retrieve many tuples The concept of a cursor is used to allow tupleatatime processing by the PASCAL program Relational Database Review 5 40 Ramez Elmasri and Shamkant B Navathe Chapters 8 9 SQL and QBE CURSORS We can think of a cursor as a pointer that points to a single tuple row from the result of a query The cursor is declared when the SQL query command is speci ed A subsequent OPEN cursor command fetches the query result and sets the cursor to a position before lhefzrsl row in the result of the query this becomes the current row for the cursor Subsequent FETCH commands in the program advance the cursor to the next row and copy its attribute values into PASCAL program variables speci ed in the FETCH command An implicit variable SQLCODE communicates to the program the status of SQL embedded commands An SQLCODE of 0 zero indicates successful execution Different codes are returned to indicate exceptions and errors A special ENDOFCURSOR code is used to terminate a loop over the tuples in a query result A CLOSE cursor command is issued to indicate that we are done with the result of the query When a cursor is defined for rows that are to be updated the clause FOR UPDATE OF must be in the cursor declaration and a list of the names of any attributes that will be updated follows The condition WHERE CURRENT OF cursor speci es that the current tuple is the one to be updated or deleted Relational Database Review 5 4l Ramez Elmasri and Shamkant B Navathe Chapters 8 9 SQL and QBE Example Write a program segment that reads inputs a department name then lists the names of employees who work in that department one at a time The program reads a raise amount for each employee and updates the employee39s salary by that amount E2 writeln39enter the department name39 readlnDNAME SELECT DNUMBER INTO DNUMBER FROM DEPARTMENT WHERE DNAMEDNAME DECLARE EMP CURSOR FOR SELECT SSN FNAME MINIT LNAME SALARY FROM EMPLOYEE WHERE DNODNUMBER FOR UPDATE OF SALARY 0PEN EMP FETCH EMP INTO ESSN EFNAME EMINIT ELNAME ESAL while SQLCODE 0 do begin writeln39employee name 39 EFNAME EMINIT ELNAME writeln39enter raise amount 39 readlnRAISE UPDATE EMPLOYEE SET SALARY SALARY RAISE WHERE CURRENT OF EMP FETCH EMP INTO ESSN EFNAME EMINIT ELNAME ESAL end CLOSE CURSOR EMP Relational Database Review 5 42 Ramez Elmasri and Shamkant B Navathe Chapters 8 9 SQL and QBE 7 Joined Relations Feature in SQLZ Can specify a quotjoined relationquot in the FROMclause Looks like any other relation but is the result of a join Allows the user to specify different types of joins regular quotthetaquot JOIN NATURAL JOIN LEFT OUTER JOIN RIGHT OUTER JOIN CROSS JOIN etc Examples Q8 SELECT EFNAME ELNAME SFNAME SLNAME FROM EMPLOYEE E S WHERE ESUPERSSNSSSN can be written as Q8 SELECT EFNAME ELNAME SFNAME SLNAME FROM EMPLOYEE E LEFT OUTER JOIN EMPLOYEE S ON ESUPERSSNSSSN Q1 SELECT FNAME LNAME ADDRESS FROM EMPLOYEE DEPARTMENT WHERE DNAME39Research39 AND DNUMBERDNO could be written as Q1 SELECT FNAME LNAME ADDRESS FROM EMPLOYEE JOIN DEPARTMENT ON DNUMBERDNO WHERE DNAME39Research39 or as Q1 SELECT FNAME LNAME ADDRESS FROM EMPLOYEE NATURAL JOIN DEPARTMENT AS DEPTDNAME DNO MSSN MSDATE WHERE DNAME39Research39 Q2 SELECT PNUMBER DNUM LNAME BDATE ADDRESS Relational Database Review 5 43 Ramez Elmasri and Shamkant B Navathe Fundamentals of DATABASE SYSTEMS FOURTH EDITION Chapter 1 Introduction and Conceptual Modeling Types of Databases and Database Applications 0 Numeric and Textual Databases 0 Multimedia Databases 0 Geographic Information Systems GIS 0 Data Warehouses O Realtime and Active Databases A number 0fth ese databases and applications are described later in the book see Chapters 242829 Baum Edmrm v sudwieam Basic Definitions 0 Database A collection of related data 0 Data Known facts that can be recorded and have an implicit meaning a O Mini world Some part of the real world about which data is stored in a database For example student grades and transcripts at a university 0 Database Management System DBMS A software package system to facilitate the creation and maintenance of a computerized database 0 Database System The DBMS software together with the data itself Sometimes the applications are also included Emirquot Edman n S39lidenlnt i Typical DBMS Functionality 0 Define a database in terms of data types structures and constraints 0 Construct or Load the Database on a secondary storage medium 0 Manipulating the database querying generating reports insertions deletions and modifications to its content 0 Concurrent Processing and Sharing by a set of users and programs yet keeping all data valid and consistent 5 th Edman 39 l39ilidlz l a Typical DBMS Functionality Other features Protection or Security measures to prevent unauthorized access Active processing to take internal actions on data Presentation and Visualization of data Emirquot smmm n S39lide39ls i Example of a Database with a Conceptual Data Model 0 lVIiniWorld for the example Part of a UNIVERSITY environment 0 Some miniworld entities 7 STUDENTS 7 COURSES 7 SECTIONS of COURSES 7 academic DEPARTMENTS 7 INSTRUCTORS Note The above could be expressed in the ENTITY RELATIONSHIP data model Baum Edman 7 amplidw1 l Example of a Database with a Conceptual Data Model 0 Some miniworld relationships 7 SECTIONS are of Speci c COURSES 7 STUDENTS take SECTIONS 7 COURSES have prerequisite COURSES 7 INSTRUCTORS teach SECTIONS 7 COURSES are a ered by DEPARTMENTS 7 STUDENTS major in DEPARTMENTS Note The above could be expressed in the ENTITY RELATIONSHIP data model Faun smmm Esme195 l Main Characteristics of the Database Approach 0 Selfdescribing nature of a database system A DBMS catalog stores the description of the database The description is called metadata This allows the DBMS software to work with different databases 0 Insulation between programs and data Called programdata independence Allows changing data storage structures and operations without haVing to change the DBMS access programs Baum Edman Slid3 1 599 i Main Characteristics of the Database Approach 0 m A data model is used to hide storage details and present the users with a conceptual view of the database 0 Support of multiple Views of the data Each user may see a different View of the database which describes only the data of interest to that user Emirquot Edman n sudt1eaao i Main Characteristics of the Database Approach 0 Sharing of data and multiuser transaction processing allowing a set of concurrent users to retrieve and to update the database Concurrency control within the DBMS guarantees that each transaction is correctly executed or completely aborted OLTP Online Transaction Processing is a major part of database applications h man V SIide l Database Users Users may be divided into those who actually use and control the content called Actors on the Scene and those who enable the database to be developed and the DBMS software to be designed and implemented called Workers Behind the Scene h m MW H chili Actors on the scene Database Users Database administrators responsible for authorizing access to the database for co ordinating and monitoring its use acquiring software and hardware resources controlling its use and monitoring ef ciency of operations Database Designers responsible to de ne the content the structure the constraints and functions or transactions against the database They must communicate with the endusers and understand their needs End users they use the data for queries reports and some of them actually update the database Baum 5mmquot h v 4Slide1slf3m Categories of Endusers 0 Casual access database occasionally when needed 0 Na39l39ve 0r Parametric they make up a large section of the enduser population They use previously welldefined functions in the form of canned transactions against the database Examples are banktellers or reservation clerks who do this activity for an entire shift of operations Emirquot smnm n quotswim1in
Are you sure you want to buy this material for
You're already Subscribed!
Looks like you've already subscribed to StudySoup, you won't need to purchase another subscription to get this material. To access this material simply click 'View Full Document'