Patent Owner Finjan, Inc. — Ex. 2010, p. l
`
`

`
`Ac.qL1isitiuI1s Editor: Maire St:-u1't:z—Ri\-'a.~s
`Asrsuciate Editor: K:1the1‘int-: H:€l1'll[Ll1"liL-ll‘!
`
`Cowl‘ Design: Lynne Reed
`Clulnpnz-uitiutx: Northeast Cntnpnsituls
`l“1‘uc_:i'1‘t‘adc1': Diane Freed
`
`Cover Art: "C1‘yst.;1l Zonc"— -O1'i,¢,ri11z-1} }'m11'd-ptilltsti h1‘nitu;-ad ethtitm 5c1'ig1‘aph hyTctsu1‘o
`Sawatla. ExclL1si\-'c $:1\v;1tl'.1 pL1hh:;ht-:1‘ and t'1i:sI'.1'ih.L|tta1': BL|:«:h1c11/Mm\.rm't Fine Arts Ltd.,
`Main Flm_':1', 1445 West: Geu1‘gi;;l Street, Vz111L':c.111\»’a-:1‘, C;.:11z1da-1 V66 2T3 (604) 682-1234.
`
`Ce1'tz-Lin tiiaglmns haw: heen 1':;pmt'1uc.et1 ht:rci11 with the pt-r1ni5.~5io11 ntO1':1c;1e
`Cn1'pt11‘;1tit:n1. ('30py1'i;ghl' © 01':-lcle Cm'po1‘ation, 1997. All rights 1‘ese1‘ve(1.
`
`This hook M15 t\,-'p<:sct in F1‘;-11uL'MakeL' 5.5 on :1 [’t>we1' Maczintush U3. The flmts used
`WL"'l‘L" Bodega, Cutldy, and Lucida S3115.
`
`Cnpy1'igl1l' ©2000 by R:-nne: Ehm=1.~;1"L aml Shalnkant B. Navathe
`
`All ti htrs 1‘e.st'.1‘vct1. Nu .1L1tl' (at this mhlicatiun l‘l'1El he re 11‘-:}(h!CCL|, stnmtl in +1 datz-lb;-150
`g
`I
`L
`Y
`t
`or rt'.tricval systeny or t1'21115111ittct1 in any fu1'1n or by-' any 111-sum, uh-:cI_'I'on'1c. m.t‘Chanic.a1_.
`pl1ntoc.npyiI1g, recm'di1'1g. or any othtzr 111edia e1nh0t1i111t‘11ts now known or h(_’.1‘t_‘E1F[Cl‘ to
`lac-xume known, without the wrior writtcn e|'1ni5.~sin11 of the _W.L|1)[i:al1u1‘. Printed in the
`_
`1
`United States of Alncricn.
`
`Lihtary of Co1'1gr::.~;s Cz1tL1luging—in~PuhiiC21tiun DI€l1"r'1
`
`Elmasri, Rmncz.
`FL1mi;1111::11l:;1I$ (‘Jf d;1t'.1h'.~15e -.‘-}y5tt‘.ll1:~i / Ratm-3: E1Im1s1'i, Sl1a111l<nnt B.
`Nav:=athc. — 3rd ed.
`
`p.
`
`cm.
`
`Includes hih1in,L_=,1‘;1phi<;;-‘I1 1‘efi-31'em.'c:s.
`ISBN O—8053—l'.-355-4
`1. Datah;-15¢ 1112111:-1gcI11c11t.
`QA?6.9.D.3E5? 2000
`0U5.74—dr:2'l
`
`I. N3\’}1I}1C, Sham.
`
`II. Title.
`
`99.30620
`CIP
`
`ISBN O-b'05.3—1?55-4
`
`2 .3 4 5 6 ? 8 9 10-DOH»O_3020l0O
`
`Repr:'nred with cm‘:'ec'rmn.c. Jrme 2000
`
`

`
`SOL-The Relational
`lll Database Standard
`
`The SQL language may be considered one of the major reasons for the success of rela—
`tional databases in the commercial world. Because it became a standard for relational
`databases, users were less concerned about migrating their database applications from
`other types of database systems~—for example, network or hierarchical systems—to rela-
`tional systems. The reason is that even if the user became dissatisfied with the particular
`relational DBMS product they chose to use, converting to another relational DBMS would
`not be expected to be too expensive and time consuming, since both systems would fol»
`low the same language standards. In practice, of course, there are many differences
`between various commercial relational DBMS packages. However, if the user is diligent in
`using only those features that are part of the standard, and if both relational systems faith-
`fully support the standard, then conversion between the two systems should be much sim-
`plified. Another advantage of having such a standard is that users may write statements in
`a database application program that can access data stored in two or more relational
`DBMSs without having to change the database sub~language (SQL)
`if both relational
`DBMSS support standard SQL.
`This chapter presents the main features of the SQL standard for commercial relational
`DBMSS, whereas Chapter 7 presented the most important formalisms underlying the rela—
`tional data model. In Chapter 7 we discussed the relational algebra operations; these opera-
`tions are very important for understanding the types of requests that may be specified on a
`
`

`
`[hapter 8 I SQL-The Relational Database Standard
`
`relational database. They are also important for query processi1'ig and optimization in a
`relational l)]3ME§, as we shall see in Chapter 18. However, the relational algehra operations
`are considered to he too technical for most commercial DBMS users. One reason is hecause a
`Liuery in relational algebra is written as a sequence of operations that, when executed, pro—
`duce the required result. llence, the user must specify how—that is,
`ii'L'1.t.»'lll£ILt’Ji'Li:’3t'»—tt‘) exe—
`cute the query operations. On the other hand.
`the SQI. language provides a high—level
`Ci(3Clt'I1‘£I1'ii.?€
`language iiatedace, so the user only specifies what the result is to be, leaving t.he
`actual optimization and decisions on how to execute the query to the 1)I‘.MS. SQ], includes
`some features from relational algehra, hut it is hased to a greater extent on the tuple relatitmtil
`caicttlus, which is another Formal query language for relational datahases that we shall
`desc.rihe in Section 9.3. The SQL syntax is more L1ser—fi'iendly than either of the two formal
`languages.
`The name SQI. is derived ftoin Structured Query l-z1tigLI2igt!. Originally, SQI. was called
`Sl;'QUEl. (for .‘§tructnred English QUl5ry l-a1‘iguage) and was designed and impleinented at
`IBM Researcl’1 as the interface for an experimental relational datahase systetn called SYS.
`TEM R. SQ}- is now the standard lallguage for commercial relational 1)I’;MSs. A joint. effort
`by ANSI (the American National Stzamlartls Institute) and 150 (the lnt.ernational St-an—
`dards Orgai“iization) has led to a standard version of SQL (ANSI i986), called SQI-—86 or
`SQI-l. A revised and much expanded standard called SQL2 (also referred to as SQI.-92) has
`stihsequently heen deveioped. Plans are already well underway for SQL3, which will hir-
`ther extend SQ]- with ohject-oriented and other recent database concepts.
`SQ]-
`is a comprehensive database language;
`it has statements for data definition,
`query, and update. Hence,
`it
`is both a DD}. tmti a DML.
`ln addition,
`it has i'acilities for
`defining views on the database, for specdying security and aLitho1‘izatio1*1, for detinin,r_t,
`integrity constraints, and for specifying; transaction controls. lt also has rules for embed-
`dingg; SQ]. staternents into a general-purpose prog1'amining language such as (T or P/\S('I/\I-.l
`We will discuss most of these topics in the following suhsections. ln our discussion, we
`will mostly follow SQL2. Features of SQL3 are overviewed in Section 13.4.
`Section 8.1 descrihes the SQLZ DD]. connnands for creating and rnoclifying scheinas,
`tahles, and constnlints. Sections 8.2 describes the hasic SQI. constructs for specifying:
`retrie\-*al queries and Section 8.3 goes over more complex features. Section 8.4 descrilaes
`the SQ}. commands for inserting, deleting and updating, and Section 8.5 discusses the
`concept of views (virtual tahles). Section 8.6 shows how general constraints may he spec-
`ified as assertions or triggers. Section 8.7 lists some SQI- features that are presented in
`other chapters of the hook; these include embedded SQL in Cliapter I0, transaction con—
`trol in Chapter 19, and security/authorization in Chapter 22. Section 8.8 summarizes the
`chapter.
`For the. reader who desires a less comprehensive introduction to SQL, parts or all of
`the following sections may be skipped: 8.2.5, 8.3, 8.5, 8.13, and 8.7.
`
`1. Originally, SQI. had statements for creating and dropping indexes on the tiles that represent I'ela~
`tions, hut these. have heen dropped from the current SQLZ standard.
`
`SQI. uses the
`\Y./e. will use
`niiion are Ci
`
`however, wt
`describes ho
`siI'ai11l‘S are s
`
`availahle in
`
`atti‘ihut_es, at
`lietails can l.
`
`8.1.!
`
`Sc
`
`liarly versiol
`mlwles (relati
`schema was i
`
`that helong I
`name, and it
`
`the schema,
`include the t
`
`[ion ggrants) t
`ment, which
`
`can he :-issigl
`l'.ite1'. For exa
`
`user with aut
`
`CREAT
`
`In additit
`
`lecrion oilsch
`|l\'l-I JRM/\Fl'l0
`
`sehemas in tl
`
`can he detiiiet
`mas within th
`
`The CREATE
`
`-“i‘ecit‘yii1ig its
`'.-attribute is gi
`Constraints 5|.
`
`straints can i
`
`

`
`n1izatio11 in 11
`
`1111 ope1'1=1t.io11s
`111 is hecause 21
`
`-‘xeeuted, pro-
`o1'de1‘~—to exe—
`
`s :1 high—leyel
`e, leaving the
`'. SQ}, i11cl11des
`. tuple relatioriai
`that we shall
`
`he two fi11‘111al
`
`C 3 was called
`
`nple111e11ted at
`In called SYS-
`
`.
`w
`A joint effi11't
`e.
`
`lled SQI-~86 or
`
`as SQ],-92) has
`*hich will fut‘-
`
`ata defi11itio11,
`
`as facilities for
`
`11, for defining
`Iles for e111l1ed—
`- C or PAS(iAi-.'
`discussion, we
`
`ifying scl'1e111as,
`' for speeifyi11g
`111 8.4 descrihes
`.5 discusses the
`
`ts may he spec-
`re presented i11
`ra11sactio11 con-
`
`s11111111arizcs t.l1e.
`
`., parts or all of
`
`1at represent 1'ela—
`
`8.]
`
`Data Definition, (onstraints, and Schema ihanqes in Sat?
`
`3.1 Data Definition, Constraints, and
`Schema Changes in SQL2
`
`HQI. uses tl1e terms table, row, and column for relation, tuple, and attribute, respectively.
`We will use tl1e. co1‘respo11di11g terms inte1'cha11geahly. The SQL2 co111n1ands for data defi-
`nition are CREATE, ALTER, and DROP; these are discussed in Sections 8.1.2 to 8.1.4. First,
`however, we discuss scliem-.1 and catalog concepts i11 Section 8.1.1. Section 8.1.2
`ilescrihes how tables are cleared, tl1e available data types for attril1utes, and how co11—
`st1'11i11ts are specified. Sections 8.1.3 and 8.1.4 describe the. schema evolution commands
`availahle. in SQL2, which can he used to alter the schema hy adding or dropping tahles,
`att1'ihur.es, and co11st1‘aints. We o11ly give a11 overview of t.l1e most i111po1‘ta11t features.
`Details can he found i11 the SQL2 docu111e11t.
`
`3.1.!
`
`Schema and Catalog Concepts in SQL2
`
`Early versions of SQL did not i11clude the concept of a relational database schema; all
`tahles (relations) were considered part of the sa111e schema. Tl1e concept of an SQL
`scheina was i11corpo1'ated i11to SQL2 i11 order t.o group together tables and other eo11st1‘ucts
`that helorig to the sa111e datahase applic-atit11'1. An SQL schema is identified by a schema
`name, and i11ei1.1des an autl1o1‘ization idenltifiei‘ to indicate the user or 11ccou11l'. who owns
`the scl1e111a, as well as descriptors for each element
`in tl1e. scl1e111a. Scl1e11'1a elements
`include the tables, constrai11ts, views. do111ai11s, and other constructs (such as authoriza—
`tion grants) that. describe t.l1e scl1e111a. A schema is created via the CREATE SCHEMA state-
`ment, which can include all the schema ele111ents' definitions. Alte1'11atiyely, the sche111a
`can he assig11ed a name and aut.l1o1'izatio11 identifier, a11d the ele111e11ts can he defined
`|:1te.r. For example, the fi1llowi11g statement creates 11 sche111a called COMPANY, owned by the
`user with autl1orizatio11 identifier JSMITI-1:
`
`CREATE SCHEMA COMPANY AUTHORIZATION JSMITH;
`
`I11 addition to the co11ce.pt ofscl1e111a, SQL2 uses the concept of catalog——a 11a111ed col—
`lec1:io11ofscl1emas in an SQL enviro11111ent. A catalog always contai11s a special schema called
`[NFOR.\»lAT10N_SCH EMA, which provides i11fi'1r111ation on all the element desc1‘ipto1's of all the
`scl1e111as in the catalog to authorized users. Integrity co11sl:1'e1i11ts such as 1'eFere11tial integrity
`can he defined between relations only if they exist in scl1e111as within the same catalog. Sche-
`mas within the sa111e catalog can also share ce1‘tai11 elenients, such as do111ai11 defi11it.io11s.
`
`8.1.2 The CREATE TABLE Command and SQL2 Data
`
`Types and Constraints
`The CREATE TABLE co111111and is used to speci y a new relation by givi11g it a name and
`511eeifyi11g its attrihiites and const1'aints. Tl1e attributes are specified first, a11d each
`:1tt1‘il1ute is given a na111e,
`11 data type to specify its domain of‘ values, and any att1'il1ute
`constrai11ts such as NOT NULL. The key, entity integrity, and reFe1'e11tial integrity con—
`sttaimzs can he speeified—witl1in the CREATE TABLE state11.1e11t—aiter the. attributes are
`
`

`
`(hapter 8 I S01-The Relational Database Standard
`
`relational datahase. They are also important for query processing and opti1rri:—:atio11 in a
`relational DBMS, as we shall see in Chapter 18. However, the relational algebra operations
`are considered to he too technical for most cornme1‘e.ial DBMS"-3 users. One reason is hecause a
`que.ry in relational algebra is written as a sequence of operations that, when executed, pro-
`duce the required result. Hence, the user must specify how——1'.hat is, in what order
`t.o exe-
`cute the query operations. On the other hand, the SQL language provides it high—le\-tel
`tleclarative language interface, so the user only specifies what the result is to he, leaving the
`actual optimization and decisions on how to exe.cut.e the query to the DBMS. SQ1- includes
`some features from relational algebra, but. it is based to a greater extent on the tuple relational
`calculus, which is another forrnal query language for relational databases that we shall
`describe in Section 9.3. The SQI- syntax is more userrfriendly t.han either of the two formal
`
`languages.
`The name SQI. is derived from Structured Query Language. Originally, SQI- was called
`SEQUIEI- (for Structured English Qblliry Language) and was designed and implemented at
`IBM Research as the. interface for an experimental relational database system called SYS-
`TEM R. SQ]- is now the standard language for cornmercial relational I)BMSs. A joint effort
`by ANSI (the American l\ia1'ion-al Standards institute) and 180 (the International Stan-
`dards Organizaition) has led to a standard version of SQ]. (ANSI 1986), called SQI.—86 or
`E-‘.QI-1. A revised and much expanded standard called SQL2 (also referred to as SQ_L—92) has
`suhsequently heen developed. Plans are already well underway for SQL3, which will fur-
`ther extend SQ]. with ohject—oriented and other recent datahase concepts.
`SQL is a comprehe.nsi\«'e database language;
`it has statements for data definition,
`query, and update. Hence,
`it
`is hoth-a DUI. and a Dr\*lI.. ln addition,
`it has facilities for
`defining views on the. database, for specifying sec.urity and authoriza1'ion, for defining
`integrity constraints, and for specifying transaction controls. It also has rules for embed-
`ding SQL statements into a general—purpose programming language such as C.‘ or PAS(':Al,.i
`We will discuss most of these topics in the following subsections. in our discussion, we
`will mostly follow SQL2. Features of SQL3 are ox-'e1'\»'iewetl in Section 1.3.4.
`Section 8.1 describes the. SQLZ DD]. commands for creating and modifying schem-as,
`tahles, and const1‘ain1'.s. Sections 8.2 describes the hasic SQI. const1'L1cts for specifying
`retrieval queries and Section 8.3 goes over more complex features. Section 8.4 describes
`the SQ], commands for inserting, deleting and updating, and Section 8.5 discusses the
`concept of views (virtual tables). Section 8.6 shows how general constraints may be spec-
`ified as assertions or triggers. Section 8.7 lists some SQ]. features that are presented in
`other chapters of the book; these include embedded SQL in Chapter 10, transaction con-
`trol in Chapter 19, and seeL11‘ity/authorization in Chapter 22. Section 8.8 summarizes the
`chapter.
`For the reader who desires a less comprehensive introduction to 8Ql., parts or all of
`the following sections may he sl<ipped: 8.2.5, 8.3, 8.5, 8.6, and 8.7.
`
`1. Originally, SQL had statements for creating and dropping indexes on the files that represent rela-
`tions, hut these have heen dropped from the curre.nt SQL2 standard.
`
`8.1 Dat
`
`Sch
`
`HQI, uses the. te-
`We will use th
`nition are (IRE
`
`l*ioweve1', we
`
`tlescrihes how
`
`straints are sp-
`
`avtrilahlc '
`artrihutes, ant
`
`Details can be
`
`3.1.]
`
`Sc
`
`Early versions
`tahles (relatio
`schenra was in
`
`that helong tt.
`name, and int
`the schenla,
`;.
`include the ta
`
`rion grants) tl
`ment, which C
`
`can he assign
`later. For exai
`
`user with autl
`
`ln additio
`
`lecrion of sche
`l.i\l FOR lvl r’\TlON
`
`schemas in th
`
`can be defined
`
`mas within th
`
`The CREATE '
`
`specifying its
`
`attribute is gi
`const.raint.s SL
`
`sir-aints can l
`
`

`
`(hapier 8 I SQi—Th9 Relational Database Standard
`
`declared, or they can be added later using the ALTER TABLF. command (see Section 8.1.4).
`Fi,9;ure 8.1(a) shows sample data definition statements in SQI. for the relational database
`schema shown in Figure 7.7. Typically,
`the SQ]. schema in which the relations are
`declared is implicitly specified in the environment in which the CREATE TABLE state-
`Inents are executed. Alternatively, we can explicitly attach the schema name to the rela—
`tion name, separated by a period. For example, by writing:
`
`CREATE T
`
`CREATE TABLE COMI-‘ANYEMPLCIYEE
`
`rather than
`
`CREATE TABLE EMPLOYEE
`
`as in Figure 8.l(a), we can explicitly (rather than implicitly) mal<c the EMPLOYEE table part
`of the COMPANY schema.
`
`Data Types and Domains in SQL2. The data types available for attributes include
`numeric. character‘-string. bit—strir‘Ig, date, and time. Numeric data types include integer
`nuinbers of various sizes (INTEGER or INT, and SMALLINT), and real numbers of various
`
`precision {FI-OAT, REAL, 1)()UBI-E PRECISION). Formatted numbers can be declared by
`using DEClMAI,(i,j)——or DEC(i,j) or NUMERICfi,j)—where i, the precision, is the total num-
`ber 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.
`Character—string data types are either fixed—length—('2HAR(n) or {3HARACTER(n},
`where n is the number of characters—or varying"-length—VARCHAR(n) or CHAR VARY-
`lN(3(n) or CHARACTER V/\RYING(n), where n is the inaxiinurn number of characters. Bit-
`string data types are either of fixed length n———BlT(n)—or varying length—BlT VARY-
`lNG(n), where n is the maximum ntlrnber of bits. The default for n, the length of a charac—
`ter string or bit string, is one.
`There are new data types for date and time in SQL2. The DATE data type has ten posi-
`tions, and its components are YEAR, MONTH, and DAY typically in the form YYYY—Ml\rl—l)D.
`The TIME data type has at least eight positions, with the components HOUR, MINUTE. and
`SECOND, typically in the form HH:MM:SS. Only valid dates and times should be allowed by
`the SQL implementation. in addition, a data type TlME(i), where i is called time fractional
`seconds precision, specifies i + 1 additional positions for TIMF.~—one position for an addi—
`tional separator character, and i positions for specifying decimal fractions of a second. A
`TIME WITH TIME ZONE data type includes an additional six positions for specifying the dis-
`placement from the standard universal time zone, which is in the range + 13:00 to -12:59
`in units of HOURS:l\/IINUTES. If WITH TIME ZONE is not included, the default is the local
`time zone for the SQI. session. Finally, a timestamp data type (TIMESTAMP) includes both
`the DATE and TIME fields, plus a minimum ofsix positions for fractions of seconds and an
`optional WITH TIME zone qualifier.
`Another data type related to DATE, TIME, and TIMESTAMP is the INTERVAL data type.
`This specifies an interval—a relative value that can be used to increment or decrement an
`absolute value of a date, time, or tirnestamp. Intervals are qualified to be either YEAR]
`MONTH intervals or DAY/TIME intervals.
`In SQL2, it is possible to specify the data type of each attribute directly, as in Figure
`8.1(-a); alternatively, a domain can be declared, and the domain name used. This makes it
`
`

`
`=Cti0n 8.1.4).
`nal database
`
`relations are
`TABLE state-
`
`e to the reIa—
`
`was table part
`
`nets of various
`
`e decisred by
`the totai nu m—
`
`haracters. Bit-
`h~is1T vmw.
`
`on for an addi»
`of a Second. A
`eeifying the dis-
`.3:00 to -12:59
`
`ZRVAL data type.
`)1‘ decrement an
`
`be either YEAR]
`
`tly, as in Figure
`1d. This makes it
`
`Data Definition, (onstraints, and Sthema {hanges in 5012
`
`CREATE TABLE EMPLOYEE
`( FNAME
`MINIT
`LNAME
`SSN
`BDATE
`ADDRESS
`SEX
`SALARY
`SUPERSSN
`DNO
`'
`
`VARCHARU5)
`CHAR,
`VARCHAHU5)
`CHARQ)
`DATE,
`VARCHARQDL
`CHAR,
`DEC|MAL(10,2) ,
`CHARQL
`INT
`
`NOT NULL,
`
`NOTNULL,
`NOTNULL,
`
`NOTNULL,
`
`PRIMARY KEY (SSN) ,
`FOREIGN KEY (SUPERSSN) REFERENCES EMPLOYEE{SSN) ,
`FOREIGN KEY (DNO) REFERENCES DEPARTMENT(DNUIV|BER) ) ;
`CREATE TABLE DEPARTMENT
`VARCHARU5)
`( DNAME
`DNUMBER
`INT
`MGRSSN
`CHARQ)
`MGRSTAFITDATE
`DATE,
`PRIMARY KEY (DNUMBER) ,
`UNIQUE {DNAME) ,
`FOREIGN KEY (MGRSSN) REFERENCES EMPLOYEE(SSN} ) ;
`CREATE TABLE DEPT_LOCAT|ONS
`INT
`( DNUIVIBER
`DLOCATION
`VARCHARU5)
`PRIMARY KEY {DNUMBER, DLOCATION) ,
`FOREIGN KEY (DNUMBER) REFERENCES DEPARTMENT{DNUMBER) ) ;
`CREATE TABLE PROJECT
`
`NOTNULL,
`NOTNULL,
`NOTNULL,
`
`NOTNULL,
`NOTNULL,
`
`( PNAME
`PNUMBER
`PLOCATION
`DNUM
`
`vnRcHARua
`INT
`VARCHARUSL
`INT
`
`NOTNULL,
`NOTNULL,
`
`NOTNULL,
`
`PRIMARY KEY IPNUMBER) ,
`UNIQUE (PNAME) ,
`FOREIGN KEY (DNUM] REFERENCES DEPARTMENT(DNUMBER) )
`CREATE TABLE WORKS_ON
`( ESSN
`PNO
`HOURS
`
`CHAR(9)
`INT
`DEC|MAL(3,1}
`PRIMARY KEY (ESSN, PNO) .
`FOREIGN KEY (ESSN) REFERENCES EMPLOYEE(SSN) ,
`FOREIGN KEY (PNO) REFERENCES PROJECT(PNUMBER} ) ;
`CREATE TABLE DEPENDENT
`
`NOT NULL,
`NOT NULL,
`NOT NULL,
`
`:
`
`NOT NULL,
`NOT NULL,
`
`( ESSN
`DEPENDENT_NAME
`SEX
`BDATE
`RELATIONSHIP
`
`CHAR(9)
`VARCHAFK15)
`CHAR ,
`DATE ,
`VARCHAR(8) .
`PRIMARY KEY (ESSN, DEPENDENT_NAME) ,
`FOREIGN KEY (ESSN) REFERENCES EII.-‘IPLOYEE(SSN) ) ;
`II IJIJ I9 II.I (6) An illustration of SQL2 data definition. SQLZ statements defining
`the compare: scherna of Figure 17.7.
`
`

`
`In geners
`both ON DEL
`
`to NULL for S
`CASCADE ON
`
`(ZADE ON UP[
`
`key value fo
`choose the a
`
`CJADF. option
`resent multiv
`
`entity types s
`
`Figure 8.
`
`l<eyword CO
`
`CREATE TAB
`
`(....
`
`(hapttr B I SQl-The Relational Database Standard
`
`easier to change the data type for a domain that is used by numerous attributes in a
`schema, and improves schema readability. For example, we can create a domain SSN_TYPE
`by the following statement:
`
`CREATE DOMAIN SSN_TYPE AS CHAR(9);
`
`We can use SSN_TYPE in place of CHAR(9) in Figure 8.1(a) for the attributes SSN and
`SUPERSSN of EMPLOYEE, MGRSSN of DEPARTMENT, ESSN of wosi<s_oN, and ESSN of DEPENDENT. A domain
`can also have an optional default specification via a DEFAULT clause, as we will discuss
`later for attributes.
`
`as
`Specifying Constraints and Default Values in SQL2. Because SQL allows NULLs
`attribute values, a constraint NOT NULL may be specified if NULL is not permitted for a par—
`ticular attribute. This should always be specified for the primary key attributes of eac.h
`relation, as well as for any other attributes whose values are required not to be NULL, as
`shown in Figure 8.l(a). It
`is also possible to define a default cualue 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. Figure
`8.I(b) illustrates an example of specifying a default manager for a new department and a
`default department for a new employee. If no default clause is specified, the default default
`value (1) is NULL.
`Following the attribute (or column) specifications, additional table constraints can be
`specified on a table,
`including keys and referential
`integrity, as illustrated in Figure
`8.I{a).2 The PRIMARY KEY clause specifies one or more attributes that make up the pri-
`mary key ofa relation. The UNIQUE clause specifies alternate (or secondary) keys. Refer-
`ential integrity is specified via the FOREIGN KEY clause.
`As we discussed in Section 7.2.4, a referential integrity constraint can be violated
`when tuples are inserted or deleted or when a foreign key attribute value is modified. In
`SQL2, the schema designer can specify the action to be taken if a referential integrity con—
`straint 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. We illustrate this with the example
`shown in Figure 8.1(b). Here, the database designer chooses SET NULL ON DELETE and
`CASCADE ON UPDATE for the foreign key supsassu of EMPLOYEE. This means that if the tuple
`for a supervising employee is deleted, the value of SUPERSSN 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 incor-
`rectly), the new value is cascaded to SUPERSSN for all employee tuples referencing the
`updated employee tuple.
`
`2. Key and referential integrity constraints were not included in earlier versions ofSQL. In some ear-
`lier implementations, keys were specified implicitly at the internal level via the CREATE INDEX com—
`mand.
`
`

`
`B.l
`
`Data Definition, (onstraints, and Schema (hanqes in Sui?
`
`ln general, the action taken by the DBMS for SET NULL or SET DEFAULT is the same for
`both ON DELETE or ON Ui-‘DATE; the value of the affected referencing attributes is changed
`to NULL for SET NULL, and to the specified default value for SET DEFAULT. The action for
`CASCADE ON DELETE is to delete all the. referencing tuples, whereas the action for CAS-
`CADE ON UPDATE is to change the value of the foreign key to the updated (new) primary
`key value for all referencing tuples. lt is the responsibility of the database designer to
`choose the appropriate action and to specify it in the DDL. As a general rule, the CAS-
`CADE option is suitable for “relationship” relations such as w0III<s_DN, for relations that rep-
`resent multiyalued attributes such as DEPT_LOcATI0Ns, and for relations that represent weak
`entity types such as DEPENDENT.
`Figure 8.1(b) also illustrates how a constraint may be given a name, following the
`keyword CONSTRAINT. The names of all constraints within a particular schema must be
`
`CREATE TABLE EMPLOYEE
`
`( .
`
`,
`.
`.
`INT NOT NULL DEFAULT 1,
`DNO
`CONSTRAINT EMPPK
`
`PRIMARY KEY (SSN) ,
`CONSTRAINT EMPSUPERFK
`FOREIGN KEY (SUPERSSN) REFERENCES EMPLOYEE(SSN)
`ON DELETE SET NULL ON UPDATE CASCADE .
`CONSTRAINT EM PDEPTFK
`FOREIGN KEY (DNO) REFERENCES DEPARTMENT(DNUMBER)
`ON DELETE SET DEFAULT ON UPDATE CASCADE);
`
`CREATE TABLE DEPARTMENT
`
`(
`
`lVlGl:iSSN CHAFi(9) NOT NULL DEFAULT '888665555'.
`
`CONSTRAINT DEPTPK
`
`PRIMARY KEY (DNUMBER) ,
`CONSTRAINT DEPTSK
`
`UNIQUE (DNAME),
`CONSTRAINT DEPTMGRFK
`FOREIGN KEY (MGRSSN) REFERENCES EMPLOYEE(SSN)
`ON DELETE SET DEFAULT ON UPDATE CASCADE);
`
`CREATE TABLE DEPT_LOCAT|ONS
`
`I
`-
`-
`( .
`PRIMARY KEY (DNUMBER. DLOCATION),
`FOREIGN KEY (DNUMBER} REFERENCES DEPARTMENT(DNUMBEFi)
`ON DELETE CASCADE ON UPDATE CASCADE )
`;
`
`tted for a par-
`butes of each
`.0 be NULL, as
`
`an be violated
`. is modified. ln
`
`1 integrity cOn—
`of a referenced
`any foreign key
`An Option must
`
`ON DELETE and
`that if the triple
`y set to NULL for
`the other hand.
`s entered incor—
`referencing the
`
`t?SQL. In some ear—
`“REATE INDEX com-
`
`fl (In re fl.l(l)) An illustration of SQL2 data definition. Specifying default attribute
`values and referential triggered actions.
`
`

`
`{hapter 8 I SQl—Tha Relational Database Standard
`
`unique. A constraint name is used to identify a particular constraint in case the constraint
`must he dropped later and replaced with another const.1'aint. as we shall discuss in Section
`8.1.4. Giving names to constraints is optional.
`The relations declared through CREATE TABLE statements are called base tables (or
`hase relations); this means that the relation and its tuples are actually created and stored
`as a file hy the l)BMS. Base relations are distinguished from virtual relations, created
`through the CREATE VIEW statement (see Section 8.5), which may or may not correspond
`to an actual physical file. In SQL the attrihutes in a hase table are considered to he orclerecl
`in the sequence in which they are specified in the CTREATE TABLE statement. However, rows
`(triples) a1'e not coiisidered to he ordered within a relation.
`
`8.1.3 The DROP SCI-{EMA and DROP TABLE Commands
`
`if a whole schema is not needed any more, the DROP SCHEMA coinmand can he used.
`There are two clrop l3(£i1(l'L5if)1‘ options: ('.'AS( EADE and RESTRICT. For example, to remove the
`COMPANY database schema and all
`its tahles, domains, and other elements, the (EASCADF.
`
`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 he executed.
`if a hase relation within a scl‘ieIna is not needed any longer, the relation and its defi-
`nition can he deleted hy using the DROP TABL]-‘_~ command. For example. if we no longer
`wish to keep track of dependents of employees in the COMPANY database of Figure 7.6, we
`can get rid of the DEPENDENT relation hy issuing the command:
`
`DROP TABLE DEPENDENT CASCADE;
`
`If the RESTRICT option is chosen instead of CiASCAD[£, a tahle is dropped only if it is
`not referenced in any constraints (for example, hy foreign key definitions in another rela-
`tion) or views (see Section 8.5}.
`\X/ith the CASCAIEF. option, all such constraints and
`views that reference the table are dropped automatically from the schema, along with the
`table itself.
`
`8.1.1, The ALTER TABLE Command
`
`The definition of a hase table can he changed hy using the ALTER TABLE command, which
`is a schema evolution command. The possihle alter table actions include adding or drop-
`ping a column lattrihutel. Clranging a column definition, and adding or dropping tahle
`constraints. For example, to add an attrihute for keeping track ofjohs of employees to the
`EMPLOYEE hase relations in the COMPANY schema, we can use the command:
`
`ALTER TABLE COMPANYEMPLOYEE ADD JOB VARCI-lAR(12);
`
`We must still enter a value for the new attrihute JOB for each individual EMPLOYEE tuple.
`This can be done either hy specifying a default clause or hy using the UPDATE command
`(see Section 8.4). if no default clause is specified, the new attribute will have NULl.s in all
`
`the tuples o
`NULL constr;
`
`To drop
`C.’\f'iCAl)E is
`
`auromaticall
`
`mand is succ
`
`or hy clefinin
`
`ALTER
`
`DEFAU
`
`ALTER
`
`“33 3445.
`
`straint types
`
`tion eomman
`3
`
`5Q|. has one h
`ment. The SE1
`
`hra, which wa
`statement in
`
`Before pr:
`forinal relatio
`
`Or more tuple.
`table is not a '
`
`

`
`ie constraint
`
`ss in Section
`
`se tables (or
`
`rd and stored
`
`ions, creat.ed
`"ll". correspond
`to be ordered
`owever. rows
`
`can be used.
`to remove the
`t.he CASCADE
`
`sped only if it
`
`in and its defi-
`
`fwe no longer
`Figure 7.6, we
`
`med only if it is
`1 another rela-
`
`zonstraints and
`
`along with the
`
`dding or drop-
`dropping table
`mployces to the
`
`l2);
`
`1 EMPLOYEE tuple.
`_
`DATE command
`ave NUl.I-s in all
`
`8.2
`
`Basic Queries in set
`
`the triples of the relation immediately after the command is executed; hence, the NOT
`NULL constraint is not allowed in this case.
`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 com-
`mand 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 COMPANYEMPLOYEE DROP ADDRESS CASCADE;
`
`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”;
`
`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 specia
`fied. For example,
`to drop the constraint named EMPSUPERFK in Figure 8.1(b) from the
`EMPLOYEE relation, we write
`
`ALTER TABLE COMPAN Y. EM PLOYEE
`DROP CONSTRAINT EMPSUPERFK CASCADE;
`
`Once this is done, we can redefine a replacement constraint by adding a new con-
`straint to the relation. if needed. This is specified by using the ADD keyword followed by
`the new constraint, which can be named or unnamed and can be of any of the table con-
`straint types discussed in Section 8.1.2.
`The preceding subsections gave an overview of the data definition and schema evolu-
`tion commands of SQL2. There are many other details and options, and we refer the inter»
`esred reader to the SQL and SQLZ document.s listed in the bibliographical notes. The next
`two sections discuss the querying capabilities of SQL.
`
`3.2 Basic Queries in SQL
`
`SQI. has one basic statement for retrieving information from a database: the SELECT state-
`ment. The SELECT statement has no relationship to the SEI.EC':'l‘ operation of relational alge—
`bra, which was discussed in Chapter 7. There are many options and flavors to the SELECT
`statement in SQL. so we will introduce its features gradually. We will use example queries
`Specified on the schema of Figure 7.5 and will refer to the sample database state shown in
`Figure 7.6 to show the results of some of the example queries.
`Before proceeding, we must point out an important distinction between SQL and the
`formal relational model discussed in Chapter 7: SQ]- allows a table (relation) to have