# Database Systems: The Complete Book- P4

Chia sẻ: Thanh Cong | Ngày: | Loại File: PDF | Số trang:50

0
122
lượt xem
5

## Database Systems: The Complete Book- P4

Mô tả tài liệu

Database Systems: The Complete Book- P4: Database Systems and Database Design and Application courses offered at the junior, senior and graduate levels in Computer Science departments. Written by well-known computer scientists, this introduction to database systems offers a comprehensive approach, focusing on database design, database use, and implementation of database applications and database management systems

Chủ đề:

Bình luận(0)

Lưu

## Nội dung Text: Database Systems: The Complete Book- P4

2. 278 CHAPTER 6. THE DATABASE LANGUAGE SQL 6.4. FULL-RELATION OPER4TIONS 279 listed in StarsIn (so the movie appeared in three different tuples of StarsIn), The Cost of Duplicate Elimination then that movie's title and year would appear four times in the result of the One might be tempted to place DISTINCT after every SELECT,on the theory that it is harmless. In fact, it is very expensive to eliminate duplicates from As for union, the operators INTERSECT ALL and EXCEPT ALL are intersection a relation. The relation must be sorted or partitioned so that identical and difference of bags. Thus, if R and S are relations, then the result of tuples appear next to each other. These algorithms are discussed starting in Section 15.2.2. Only by grouping the tuples in this way can we determine whether or not a given tuple should be eliminated. The time it takes to R INTERSECT ALL S sort the relation so that duplicates may be eliminated is often greater than the time it takes to execute t.he query itself. Thus, duplicate elimination is the relation in which the number of times a tuple t appears is the minimum should be used judiciously if we want our queries to run fast. of the number of times it appears in R and the number of times it appears in The result of expression Then, t.hc list of producers will have duplicate occurrences of names elirilinated before printing. R EXCEPT ALL S Incidentally, the query of Fig. 6.7, where we used subqueries, does not nec- essarily suffer from the problem of duplicate answers. True, the subquery at has tuple t as many times as the difference of the number of times it appears in line (4) of Fig. 6.7 will produce the certificate number of George Lucas several R minus the number of times it appears in S1provided the difference is positive. times. However, in the "main" query of line (I), we examine each tuple of Each of these definitions is what we discussed for bags in Section 5.3.2. MovieExec once. Presumably, there is only one tuple for George Lucas in that relation, and if so, it is only this tuple that satisfies the WHERE clause of line (3). 6.4.3 Grouping and Aggregation in SQL Thus, George Lucas is printed only once. In Section 5.4.4, we introduced the grouping-and-aggregation operator y for our extended relational algebra. Recall that this operator allo\\-s us to partition 6.4.2 Duplicates in Unions, Intersections, and Differences the tuples of a relation into "groups," based on the values of tuples in one or more attributes, as discussed in Section .3.4.3. lye are then able to aggregate Unlike the SELECT statement, which preserves duplicates as a default and only certain other columns of the relation by applying "aggregation" operators to eliminates them when instructed to by the DISTINCT keyword. the union. inter- those columns. If there are groups, t,hen the aggregation is done separately for section, and difference operations, which tve introduced in Sectio~l 6.2.3: nor- each g o u p . SQL provides all the capability of the 7: operator tlirough the use mally eliminate duplicates. That is, bags are converted to sets, and the set of aggregation operators in SELECT clauses and a special GROUP BY clause. \-c,rsion of the operation is applied. In order to prevent t,he eliminat,ionof dupli- cates, 13-emust follow the operator UNION, INTERSECT,or EXCEPT by the keyn-ord ALL. If we do, then we get the bag semantics of these operators as was discussed 6.4.4 Aggregation Operators in Section 5.3.2. SQL uses the five aggregation operators SUM, AVG. MIN. MAX. and COUNT that rve Exanlpie 6.28 : Consider again the union expression fro111 Esanlple 6.13. but niet in Section 5.4 2. These operators are used by applying them to a scalar- ilo\\- add the kq~vord ALL, as: valued espression. typically a colu~iin nanie. in a SELECT clause. One exception , is the expression COUNT(*). 11-hich counts all the tuples in the relation that is (SELECT title, year F O Movie) RM constructed from the F O clause and WHERE clause of the query. RM UNION ALL In addition, 11-e have the option of eliminating duplicates from the column (SELECT movieTitle AS title, movieyear AS year F O StarsIn); RM before applying the aggregation operator by using the keyrx-ord DISTINCT. That is, an expression such as COUNT(DIST1NCT x) counts the number of distinct ~o~~~ title and year will appear as many times in the result as it appears in a values in column x. \Ve could use any of the other operators in place of COUNT each of the relations Movie and StarsIn put toget,her. For instance, if a movie here, but expressions such as SUM(D1STINCT x) rarely make sense: since it asks appeared once irl the Movie relation and there ~i-ere three stars for that movie us to sum the different values in colunin s. Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
3. 280 CHAPTER 6. THE DAT.4BASE LANGUAGE SQL 6.4. FliLL-RELtlTION OPERATIONS E x a m p l e 6.29 : The following query finds the average net worth of all movie E x a m p l e 6.31 : The problem of finding, from the relation executives: M o v i e ( t i t l e , y e a r , l e n g t h , i n c o l o r , s t u d i o l a m e , producerC#) SELECT AVG(netWorth1 F O MovieExec; RM the sum of the lengths of all movies for each studio is expressed by Note that there is no W E E clause a t all, so the keyword W E E is properly HR HR SELECT studioName, SUM(1ength) omitted. This query examines the n e t w o r t h column of the relation F O Movie RM MovieExec(name, a d d r e s s , c e r t # , networth) GROUP B studioName; Y sums the values found there, one value for each tuple (even if the tuple is a We may imagine t h a t the tuples of relation Movie arc reorganized and grouped duplicate of some other tuple), and divides the sum by the number of tuples. so that all the tuples for Disney studios are together, all those for MGM are If there are no duplicate tuples, then this query gives the average net worth together, and so on, as was suggested in Fig. 5.17. The sums of the length as we expect. If there were duplicate tuples, then a movie executive whose components of all the tuples in each group are calculated, and for each group, tuple appeared n times would have his or her net worth counted n times in the the studio name is printed along with that sum. average. Observe in Example 6.31 how the SELECT clause has t ~ v o kinds of terms. E x a m p l e 6.30 : The following query: 1. Aggregations, where a n aggregate operator is applied to a n attribute or SELECT C U T (*) ON expression involving attributes. As mentioned, these terms are evaluated FO StarsIn; RM on a per-group basis. counts the number of tuples in the S t a r s I n relation. The similar query: 2. Attributes, such as studioName in this example, that appear in the GROUP B clause. In a SELECT clause that has aggregations, only those attributes Y SELECT C U T (starName) ON that are mentioned in the GROUP B clause may appear unaggregated in Y FO StarsIn; RM the SELECT clause. counts the number of values in the starName column of the relation. Since, While queries il~volvi~ig GROUP BY generally have both grouping attributes duplicate values are not eliminated when we project onto the starName coltimn and aggregations in the SELECT clause, it is technically not necessary to have in SQL, this count should be the same as the count produced by the query with both. For example, we could m i t e C U T (*) . ON If we want to be certain that we d o not count duplicate values more than SELECT studioName once, we can use the keyword DISTINCT before the aggregated attribute. as: F O Movie RM SELECT COUNT(DIST1NCT starName) GROUP B studioName; Y FO StarsIn; RM This query rvould group the tuples of Movie according t o their studio name and Sox~\., star is counted once, no matter in how many movies they appearcc!. each then print the studio name for each group, no matter how many tuples there are with a gii-en studio name. Thus, the above query has the same effect as SELECT DISTINCT studioName 6.4.5 Grouping F O Movie; RM To group tuples, vie use a GROUP B clause; follo~ving W E E clause. The Y the H R It is also possible to use a GROUP B clause in a query about several relations. Y ke~~l-ordsOUP BY are followed by a list of grouping attributes. In tlle simplest G R Such a query is interpreted by the following sequence of steps: situation, there is only one relation reference in the F O clause, and t,his relation RM has its tuples grouped according to their values in the grouping attributes. 1. Evaluate the relation R expressed by the F O and W E E clauses- That RM HR li-hateyer aggregation operators are used in the SELECT clause are applied only is, relation R is the Cartesian product of the relations mentioned in the within groups. FROM clause. t o which the selection of the W E E clause is applied. HR Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
4. 282 CHAPTER 6. THE DATABASE LANGUAGE SQL 6.4. FULL-RELATION OPER~~TIOIVS 283 - -- - -- - 2. Group the tuples of R according to the attributes in the GROUP BY clause. Grouping, Aggregation, and Nulls 3. Produce as a result the attributes and aggregations of the SELECT clause. as if the query were about a stored relation R. When tuples have nulls, there are a few rules we must remember: Example 6.32 : Suppose we wish to print a table listing each producer's total The value NULL is ignored in any aggregation. It does not contribute lcngth of film produced. l i e need to get information from the two relations to a sum, average, or count, nor can it be the minimum or masi- mum in its column. For example, COUNT(*) is always a count of the Movie(title, year, length, incolor, studioName, producerC#) number of tuples in a relation, but COUNT(A1 is the number of t~iples MovieExec(name, address, certtt, networth) with non-NULL values for attribute A. so we begin by taking their theta-join, equating the certificate numbers from On the other hand, NULL is treated as an ordinary value in a grouped the two relations. That step gives us a relation in which each MovieExec tuple attribute. For example, SELECT a, AVG(b) FROM R GROUP BY a is paired with the Movie tuples for all the movies of that producer. Note that will produce a tuple with NULL for the value of a and the aI7erage an executive who is not a producer will not be paired with any movies: and value of b for the tuplcs with a = NULL, if there is at least one tuple therefore will not appear in the relation. Now, we can group the selected tuplcs in R with a component NULL. of this relation according to the name of the producer. Finally, we sum the lengths of the movies in each group. The query is shown in Fig. 6.13. HAVING MIN(year) < 1930 SELECT name, SUM (length) FROM MovieExec, Movie The resulting quer3; shown in Fig. 6.14, ~vouldremove froin the grouped relation W E R E producerC# = cert# all those groups in which every tuple had a year component 1930 or lliglier. GROUP BY name; Figure 6.13: Computing the length of movies for each produce1 SELECT name, SUM(1ength) FROM MovieExec, Movie 6.4.6 HAVING Clauses WHERE producerC# = cert# GROUP BY name Suppose that we did not wish to include all of the producers in our table of HAVING MIN(year) < 1930; Example 6.32. We could restrict the tuples prior to grouping in a way that \\-ould make undesired groups empty. For instance, if we only wanted the total length of movies for producers with a net worth of more than $10.000,000. we Figure 6.14: Computing the total length of film for early producers could change the third line of Fig. 6.13 to WHERE producerC# = cert# AND networth > 1OOOOOOO There are several rules we must remember about HAVING clauses: Ho~ve\-cr:sometinies we want to choose our groups based on some aggrt.gatt3 * i n aggregation in a HAVING clause applies only to the tuples of the group Property of the group itself. Then we follo117 the GROUP BY clause xvith a HAVING being tested. clause. The latter clausc consists of the keyword HAVING followed by a conditioll about the group. Any attribute of relations in the FROM clause may be aggregated in the HAVING clause, but only those attribut,es that are in the GROUP BY list Example 6-33: Suppose we want to print the total film length for only thosc may appear unaggregated in the HAVING clause (the same rule as for the producers who made at least one film prior to 1930. I r e may append to Fig. 6.13 SELECT clause). the clause Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark. 5. 284 CHAPTER 6. THE DATABASE ,CAlVGUAGE SQL 6.4. FULL-RELATION OPER4T10ArS 285 - *! f) Find for each manufacturer, the average screen size of its laptops. Order of Clauses in SQL Queries ! g) Find the manufacturers that make at least three different models of PC. Me have now met all six clauses that can appear in an SQL "select-from- 7 where" query: SELECT, F O , W E E G O P BY, HAVING, and O D R BY. R M HR, R U RE ! h) Find for each manufacturer who sells PC's the maximum price of a PC. Only the first two are required, but you can't use a HAVING clause without a G O P BY clause. Whichever additional clauses appear must be in the RU *! i) Find, for each speed of PC above 800, the average price. order listed above. !! j) Find the average hard disk size of a PC for all those manufacturers that make printers. 6.4.7 Exercises for Section 6.4 Exercise 6.4.7 : Write the following queries, based on the database schema Exercise 6.4.1: Write each of the queries in Exercise 5.2.1 in SQL, making Classes ( c l a s s , type, country, numGuns , bore, displacement) sure that duplicates are eliminated. Ships(name, c l a s s , launched) B a t t l e s (name, date) Exercise 6.4.2: Write each of the queries in Exercise 5.2.4 in SQL, making Outcomes (ship, b a t t l e , r e s u l t ) sure that duplicates are eliminated. ! Exercise 6.4.3: For each of your answers to Exercise 6.3.1, determine whether of Exercise 5.2.4, and evaluate your queries using the data of that exercise. or not the result of your query can have duplicates. If so, rewrite the query a) Find the number of battleship classes. to eliminate duplicates. If not, write a query without subqueries that has the same, duplicate-free answer. b) Find the average number of guns of battleship classes. ! Exercise 6.4.4: Repeat Exercise 6.4.3 for your answers to Exercise 6.3.2. ! c) Find the average number of guns of battleships. Xote the difference be- *! Exercise 6.4.5 : In Example 6.27, we mentioned that different versions of the t~veen(b) and (c); do 11-e weight a class by the number of ships of that query "find the producers of Harrison Ford's movies" can hare different answers class or not'? as bags, even though they yield the same set of answers. Consider the version ! d) Find for each class the year in which the first ship of that class was of the query in Example 6.22, where we used a subquery in the F O clause. RM launched. Does this version produce duplicates, and if so, why? ! e) Find for each class the number of ships of that class sunk in battle. Exercise 6.4.6: Write the following queries, based on the database schema !! f) Find for each class with at least three ships the number of ships of that Product (maker, model, type) class sunk in battle. PC(mode1, speed, ram, hd, r d , price) Laptop(mode1, speed, ram, hd, screen, price) !! g) The n-eight (in pounds) of the shell fired from a naval gun is approximately Printer(mode1, color, type, price) one half the cube of the bore (in inches). Find the average weight of the shell for each country's ships. of Exercise 3.2.1. and evaluate your queries using the data of that exercise. * a) Find the average speed of PC's. Exercise 6.4.8 : In Example 5.23 Xe gave an example of the query: "find?for v each star ~ h has appeared in at least threc movies, the earliest year in which o 1)) Find the at-erage speed of laptops costing over$2000. they appeared." \\e wrote this query as a y operation. Write it in SQL. c) Find the average price of PC's made by manufacturer "A." *! Exercise 6.4.9 : The y operator of estended relational algebra does not have ! d) Find the average price of PC's and laptops made by manufacturer '.D..' a feature that corresponds to the HAVING clause of SQL. Is it ~ossible mimic to an SQL query n-ith a HAVING clause in relational algebra? If so, how n'ould we e) Find, for each different speed the average price of a PC. do it in general? Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
6. 286 CHAPTER 6. THE DATABASE LANGUAGE SQL -5. DATABASE AIODIFIC.4TIOiS 287 6.5 Database Modifications ~ f as in Example 6.34, we p r o ~ i d e , values for all attributes of the relation, n we may omit. the list of attributes that follows the relation name. That is, To t.his point, we have focused on the normal SQL query form: the select-from- where st,atement. There are a number of other statement forms that do not return a result, but rather change the state of the database. In this section, we INSERT INTO S t a r s I n shall focus on three types of st.atements that allow us to VALUES('The Maltese F a l c o n ' , 1942, 'Sydney G r e e n s t r e e t ' ) ; 1. Insert tuples into a relation. Howvever, if we take t,his option, we must b e sure t h a t the order of the values 2. Delete certain tuples from a relation. is the same as the standard order of attributes for the relation. We shall see in Section 6.6 how relation schemas are declared, and we shall see that as we d o so 3. Update values of certain components of certain existing tuples. we provide an order for the attributes. This order is assumed when matching values t o attributes, if the list of attributes is missing from a n INSERT statement. We refer to these three types of operations collectively as modifications. If you are not sure of t h e standard order for the attributes, it is best to 6.5.1 Insertion list them in the INSERT clause in the order you choose for their values in the VALUES clause. The basic form of insertion statement consists of: 1. The keywords INSERT INTO, The simple INSERT described above only puts one tuple into a relation. Instead of using explicit values for one tuple, we can compute a set of tuples to 2. The name of a relation R, . be inserted, using a subquery. This subquery replaces t,he keyrvord VALUES and the tuple expression in the INSERT statement form described above. 3. A parenthesized list of attributes of the relation R, 4. The keyword VALUES, and E x a m p l e 6.35 : Suppose we want to add t o the relation 5. A tuple expression, that is, a parenthesized list of concrete values, one for Studio(name, address, presC#) each attribute in the list (3). all movie studios that are mentioned in the relation That is, the basic insertion form is M o v i e ( t i t l e , y e a r , l e n g t h , i n c o l o r , studioName, producerC#) INSERT INTO R(.41,. . . , A,) VALUES (vl;. . . ,v,) ; but do not appear in Studio. Since there is no way t o determine a n address or A tuple is created using the value vi for attribute Ai,for i = 1,2,. . . ,n. I f a president for such a studio, we shall have to be content with value NULL for the list of attributes does not include all attributes of the relation R , then the attributes a d d r e s s and presC# in the inserted S t u d i o tuples. -4 Ivay t o make tuple created has default values for all missing attributes. The most common this insertion is shown in Fig. 6.15. default wlue is NULL, the null value, but there are other options to be discussed in Sect,ion 6.6.4. .1) INSERT INTO Studio(name) E x a m p l e 6.34: Suppose we wish to add Sydney Greenstreet to t,he list of SELECT DISTINCT studioName stars of The hfaltese Falcon. IVe say: F O Movie RM 1) INSERT INTO StarsIn(movieTitle, movieyear, starName) W E E studioName N T I N HR O 2 ) VALUES('The Maltese Falcon',1942, 'Sydney G r e e n s t r e e t ' ) ; (SELECT name F O Studio) ; RM The effect of executing this statement is that a tuple with the three components on line (2) is inserted into the relation S t a r s I n . Since all attributes of S t a r s I n are mentioned on line (I), there is no need to add default components. The Figure 6.1.5: Xdding new studios values on line (2) are matched with the attributes on line (1) in the order given, so 'The Maltese Falcon' becomes the value of the component for attribute Like most SQL statements with nesting, Fig. 6.1.5 is easiest t o examine from movieTitle, and so on. 0 the inside out. Lines (5) and (6) generate all the studio names in the relation Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
7. 288 CHAPTER 6. T H E DATABASE LANGUAGE SQL 6.5. D..IT..1BASE AIODIFICATIOArS That is, the form of a deletion is The Timing of Insertions DELETE FROM R WHERE ; Figure 6.15 illustrates a subtle point about the semantics of SQL state- The effect of executing this statement is that every tuple satisfying the condition ments. In principle, the evaluation of the query of lines (2) through ( 6 ) should be accomplished prior to executing the insertion of line (1). Thus? (4) will be deleted from relation R. there is no possibility that new tuples added to Studio a t line (1) will Example 6.36 : We can delete from relation affect the condition on line (4). However, for efficiency purposes, it is pos- sible that an implementation will execute this statement so that changes S t a r s I n ( m o v i e T i t l e , movieyear, starName) t o Studio are made as soon as new studios are found, during the execution of lines (2) through (6). the fact that Sydney Greenstreet w s a star in The Maltese Falcon by the SQL a In this particular example, it does not matter whether or not inser- tions are delayed until the query is completely evaluated. However, there are other queries where the result can be changed by varying the timing DELETE F O S t a r s I n RM of insertions. For example, suppose DISTINCT were removed from line (2) W E E movieTitle = 'The Maltese Falcon' AND HR of Fig. 6.15. If we evaluate the query of lines (2) through (6) before doing movieyear = 1942 AND any insertion, then a new studio name appearing in several Movie tuples starName = 'Sydney G r e e n s t r e e t ' ; would appear several times in the result of this query and therefore would Notice that unlike the insertion statement of Example 6.34, we cannot sirnply be inserted several times into relation Studio. However, if we inserted specify a tuple t o b e deleted. Rather, we must describe the tuple exactly by a new studios into Studio as soon as we found them during the evaluation W E E clause. HR of the query of lines (2) through (6), then the same new studio would not be inserted twice. Rather, as soon as the new studio was inserted once, its Example 6.37: Here is another example of a deletion. This time, we delete name would no longer satisfy the condition of lines (4) through (6), and from relation it would not appear a second time in the result of the query of lines (2) through (6). MovieExec(name , a d d r e s s , c e r t # , networth) several tuples at once by using a condition that can be satisfied by more than one tuple. The statement Studio. Thus, line (4) tests that a studio name from the Movie relation is none of these studios. DELETE F O MovieExec RM Now, we see that lines (2) through (6) produce the set of studio names W E E networth < 10000000; HR found in Movie but not in Studio. The use of DISTINCT on line (2) assures that each studio will appear only once in this set, no matter how many movies it deletes all movie eseciltives whose net worth is low - less than ten million 0'-ns. Finally, line (1) inserts each of these studios, with NULL for the attributes dollars. address and presC#, into relation Studio. 0 6.5.3 Updates 6.5.2 Deletion U-hile we migllt think of both insertions and deletions of tuples as "updates" to the d a t a b a ~ r .an ~lprlatein SQL is a very specific kind of change to the -4 deletion statement consists of: database: olle or lllore t,lplcs that alreatly esist in thc database have some of 1. The keywords DELETE FROM, their colnponcIits changed. The general form of an update statement is: 2. The name of a relation, say R, 1. The keyword UPDATE, 3. The keyword WHERE, and 2. .A relation name, say I?, 1. A condition. 3. The key\\-ord SET, Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
8. 290 CHAPTER 6. THE DATABASE LANGUAGE SQ . DriTABASE MODIFIC-4TIOXS 291 4. A list of formulas that each set an attribute of the relation R equal to til Exercise 5.2.1. Describe the effect of the modifications on the data of that value of a n expression or constant, 5. The keyword WHERE, and a) Using two INSERT statements store in the database the fact that P C model 1100 is made by manufacturer C, has speed 1800, RAM 256, hard disk 6. A condition. 80, a 20x DVD, and sells for $2499. That is, the form of an update is ) Insert the facts that for every P C there is a laptop with the same manu- UPDATE R SET 9. - - 292 CHAPTER 6. T H E DATABASE LANGUAGE SQL DEFI;I'IXTG 4 RELATION SCHEAM IN SQL 293 6.6 Defining a Relation Schema in SQL of bits permitted may be less, depending on the inlplementation (as with the types i n t and s h o r t i n t in C). In this section we shall begin a discussion of data definition, the portions of SQL that involve describing the structure of information in the database. In contrast, 5. Floating-point numbers can be represented in a variety of ways. We may the aspects of SQL discussed previously - queries and modifications - are use the type FLOAT or REAL (these are synonyms) for typical floating- often called data m a n i p ~ l a t i o n . ~ point numbers. A higher precision can be obtained with the type DOUBLE The subject of this section is declaration of the schemas of stored relations. PRECISION; again the distinction between these types is as in C. SQL also We shall see how to describe a new relation or table as it is called in SQL. has types that are real numbers with a fixed decimal point. For exam- Section 6.7 covers the declaration of "views," which are virtual relatiorls thar ple, DECIMAL(n,d) allolvs values that consist of n decimal digits, with the are not really stored in the database, while some of the more complex issues decimal point assumed to be d positions from the right. Thus, 0123.45 regarding constraints on relations are deferred to Chapter 7. is a possible value of type DECIMAL(6,2). NUMERIC is almost a syllollym for DECIMAL, although there are possible implementation-dependent dif- 6.6.1 Data Types 6. Dates and times can be represented by the data types DATE and TIME, To begin, let us introduce the principal atomic'data types that are supported respectively. Recall our discussion of date and time values in Section by SQL systems. All attributes must have a data type. 6.1.4. These values are essentially character strings of a special form. itre may, in fact, coerce dates and times to string types, and we may do the 1. Character strings of fixed or varying length. The type CHAR(n) dcnoies a fixed-length string of n characters. That is, if an attribute has type reverse if the string "makes sense" as a dabe or time. CHAR(n1, then in any tuple the component for this attribute will be a string of n characters. VARCHAR(n1 denotes a string of u p t o n characters. 6.6.2 Simple Table Declarations Components for a n attribute of this type will he strings of between 0 and n characters. SQL permits reasonable coercions between values of The simplest form of declaration of a relation schema consists of the keyrl-ords character-string types. Sormally, a string is padded by trailing bl;lnks CREATE TABLE follo\$:ed by the name of the relation and a parenthesized list of if it becomes the value of a component t,hat is a fixed-length st,ring of the attribute names and their types. greater length. For example, the string f o o ' , if it became the value of Example 6.39: The relation schema for our example Moviestar relation, a component for an attribute of type CHAR(5), would assume the valiie 'foo ' (with two blanks following the second 0). The padding blanks which ,\-as described informally in Section 5.1, is expressed in SQL as in Fig. 6.16. The first two attributes, name and address, have each been declared t o be can then be ignored if the value of this conlponent were compared (see Section 6.1.3) with another string. character strings. However, with the name, we have made the decision t o use a fixed-length string of 30 characters: padding a name out with blanks a t the end 2. Bit strings of fixed or varying length. These strings are analogous to fised if necessary and truncating a name to 30 characters if it is longcr. In contrast, and varying-length character st,rings, but their values are strings of bits ti-e have declared addresses t o be variable-length character strings of up to 255 rather than characters. The type BIT(n) denotes bit strings of length n. c h a r a ~ t e r s . ~ is not clear that these two choices are the best possible, but we It while B I T VARYING(^) denotes bit.strings of length up to n. use them to illustrate two kinds of string dat,a types. The gender attribute has values that are a single letter, M or F. Thus: we 3. The type BOOLEAN denotes an attribute ~i-hose value is logical. The po.4- can safe1)- use a single character as the type of this attribute. Fi~lally.the ble values of such an attribute are TRUE. FALSE, and - although it ~~-oulrl b i r t h d a t e attribute naturally deserves the data type DATE. If this type w r e surprise George Boole - U K O N NNW. not available ill a system that did not conforrn to the SQL standard, we could use CHAR(10) instead, since all DATE values arc actual1:- strings of 10 characters: The type INT or INTEGER (these nanies are synonj-ms) denotes typical eight digits and two hyphens. integer values. The type SHORTINT also denotes integers, but the number SThe number 255 is not the result of some weird notion of what typical addresses look like. the material of this section is in the realm of database design, and thus should r\ single byte can store integers between 0 and 255, so it is ~ o s s i b l e represent a v a ~ i n g - to have been 'Overed earlier in the book, like the analogous ODL for object-oriented databases. length character string of rip to 255 bytes by a single byte for the count of characters pills the H"'vever7 there are good reasons to group all SQL study together, so we took the liberty of bytes t o store the string itself. Commercial systems generally support longer varying-length \-iolating our own organization. strings, howe\-er. Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
18. 6.7. VIEW DEFINITIONS Moviestar (name, address, gender, b i r t h d a t e ) MovieExec(name, address, c e r t # , networth) Studio(name, address, presC#) Construct the following views: * a) A view RichExec giving the name, address, certificate number and net worth of all executives with a net worth of a t least $10,000,000. b) A view StudioPres giving the name, address, and certificate number of all executives who are studio presidents. c) A view Executivestar giving the name, address, gender, birth date, cer- tificate number, and net worth of all individuals who are both executives and stars. Exercise 6.7.2 : Which of the views of Exercise 6.7.1 are updatable? Figure 6.21: Exercise 6.7.3: Write each of the queries below, using one or more of the views from Exercise 6.7.1 and no base tables. The two selections can also be combined. In general, two consecutive se- lections can be replaced by one selection for the AND of their conditions. The a) Find the names of females who are both stars and executives. resulting expression tree is shown in Fig. 6.22. It is the tree that we would * b) Find the names of those executives who are both studio presidents and obtain from the query worth at least$10,000,000. SELECT t i t l e ! c) Find the names of studio presidents who are also stars and are worth at F O Movie RM least \$50,000,000. W E E studioName = 'Paramount' AND year = 1979; HR *! Exercise 6.7.4 : For the view and query of Example 6.48: directly. 0 I a) Show the expression tree for the view Movieprod. I b) Show the expression tree for the query of that example. I c) Build from your answers to (a) and (b) an expression for the query in terms of base tables. (T year = 1979 AND smdioName = ' Paramount ' I Movie I d) Explain how to change your expression from (c) so it is an equivalent expression that matches the suggested solution in Example 6.48. ! Exercise 6.7.5 : For each of the queries of Exercise 6.7.3, express the query and views as relational-algebraexpressions, substitute for the uses of the view in the Figure 6.22: Simplifying the query over base tables query expression, and simplify the resulting expressions as best you can. Write SQL queries corresponding to your resulting expressions on the base tables. Exercise 6.7.6 : Using the base tables 6.7.6 Exercises for Section 6.7 Classes(class, type, country, numGuns, bore, displacement) Exercise 6.7.1 : From the following base tables of our running example Ships (name, c l a s s , launched) Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.