Preview only show first 10 pages with watermark. For full document please download
Database Design With Uml And Sql | Relational Model | Relational ...
Tom Jewett Department of Computer Engineering and Computer Science, Emeritus California State University, Long Beach www.cecs.csulb.edu/~jewett/ email: ..... Orders cphone orderdate soldby cfirstname clastname Alvaro Monge 562-333-4141 2003-07-14 Patrick Wayne Dick 562-777-3030 2003-07-14 Patrick Alvaro ...
WHERE ORDER BY ;
• Of the four clauses, only the first two are required. When you are learning to build queries, it is helpful to follow a specific step-by-step sequence, look at the data after each modification to the query, and be sure that you understand the results at each step. We’ll build a list of customers who live in a specific zip code area, showing their first and last names and phone numbers and listing them in alphabetical order by last name. 1. Look at all of the relevant data—this is called the result set of the query, and it is specified in the FROM clause. We have only one table, so the result set should consist of all the columns (* means all attributes) and rows of this table. SELECT * FROM customers;
Customers cfirstname clastname
cphone
cstreet
czipcode
Tom
Jewett
714-555-1212 10200 Slater
92708
Alvaro
Monge
562-333-4141 2145 Main
90840
Wayne
Dick
562-777-3030 1250 Bellflower 90840
2. Pick the specific rows you want from the result set (for example here, all customers who live in zip code 90840). Notice the single quotes around the string you’re looking for—search strings are case sensitive! SELECT * FROM customers WHERE cZipCode = '90840';
Customers in zip code 90840 cfirstname clastname
cphone
cstreet
czipcode
Alvaro
Monge
562-333-4141 2145 Main
90840
Wayne
Dick
562-777-3030 1250 Bellflower 90840
3. Pick the attributes (columns) you want. Notice that changing the order of the columns (like showing the last name first) does not change the meaning of the data.
SELECT cLastName, cFirstName, cPhone FROM customers WHERE cZipCode = '90840';
Columns from SELECT cLastame cFirstame
cPhone
Monge
Alvaro
562-333-4141
Dick
Wayne
562-777-3030
4. In SQL, you can also specify the order in which to list the results. Once again, the order in which rows are listed does not change the meaning of the data in them. SELECT cLastName, cFirstName, cPhone FROM customers WHERE cZipCode = '90840' ORDER BY cLastName, cFirstName;
Rows in order cLastame cFirstame
cPhone
Dick
Wayne
562-777-3030
Monge
Alvaro
562-333-4141
Why SQL works: the RA select and project Like all algebras, RA applies operators to operands to produce results. RA operands are relations; results are new relations that can be used as operands in building more complex expressions. We’ll introduce two of the RA operators following the example and sequence above. 1. To represent a single relation in RA, we only need to use its name. We can also represent relations and schemes symbolically with small and capital letters, for example relation r over scheme R. In this case, r = customers and R = the Customers scheme. 2. The select (RA) operator (written σ) picks tuples, like the SQL WHERE clause picks rows. It is a unary operator that takes a single relation or expression as its operand. It also takes a predicate , θ, to specify which tuples are required. Its syntax is σθr , or in our example: σcZipCode='90840'customers. The scheme of the result of σθr is R—the same scheme we started with—since we haven’t done anything to change the attribute list. The result of this operation includes all tuples of r for which the predicate θ evaluates to true. 3. The project (RA) operator (written π) picks attributes, confusingly like the SQL SELECT clause. It is also a unary operator that takes a single relation or expression as its operand. Instead of a predicate, it takes a subscheme, X (of R), to specify which attributes are required. Its syntax is πXr , or in our example: πcLastName, cFirstName, cPhonecustomers.
The scheme of the result of πXr is X. The tuples resulting from this operation are tuples of the original relation, r , cut down to the attributes contained in X. • For X to be a subscheme of R, it must be a sub set of the attributes in R, and preserve the assignment rule from R (that is, each attribute of X must have the same domain as its corresponding attribute in R). • If X is a super key of r , then there will be the same number of tuples in the result as there were to begin with in r . If X is not a super key of r , then any duplicate (non-distinct) tuples are eliminated from the result. Just as in the SQL statement, we can apply the project operator to the output of the select operation to produce the results that we want: πXσθr or πcLastName, cFirstName, cPhone σcZipCode='90840'customers. Since RA considers relations strictly as sets of tuples, there is no way to specify the order of tuples in a result relation.
Basic SQL statements: DDL and DML In the first part of this tutorial, you’ve seen some of the SQL statements that you need to start building a database. This page gives you a review of those and adds several more that you haven’t seen yet. • SQL statements are divided into two major categories: data definition language (DDL) and data manipulation language (DML) . Both of these categories contain far more statements than we can present here, and each of the statements is far more complex than we show in this introduction. If you want to master this material, we strongly recommend that you find a SQL reference for your own database software as a supplement to these pages.
Data definition language DDL statements are used to build and modify the structure of your tables and other objects in the database. When you execute a DDL statement, it takes effect immediately. • The create table statement does exactly that: CREATE TABLE
( , ... );
The data types that you will use most frequently are character strings, which might be called VARCHAR or CHAR for variable or fixed length strings; numeric types such as NUMBER or INTEGER, which will usually specify a precision; and DATE or related types. Data type syntax is variable from system to system; the only way to be sure is to consult the documentation for your own software.
• The alter table statement may be used as you have seen to specify primary and foreign key constraints, as well as to make other modifications to the table structure. Key constraints may also be specified in the CREATE TABLE statement. ALTER TABLE
ADD CONSTRAINT PRIMARY KEY ();
You get to specify the constraint name. Get used to following a convention of tablename_pk (for example, Customers_pk), so you can remember what you did later. The attribute list contains the one or more attributes that form this PK; if more than one, the names are separated by commas. • The foreign key constraint is a bit more complicated, since we have to specify both the FK attributes in this (child) table, and the PK attributes that they link to in the parent table. ALTER TABLE
ADD CONSTRAINT FOREIGN KEY () REFERENCES ();
Name the constraint in the form childtable_parenttable_fk (for example, Orders_Customers_fk). If there is more than one attribute in the FK, all of them must be included (with commas between) in both the FK attribute list and the REFERENCES (parent table) attribute list. You need a separate foreign key definition for each relationship in which this table is the child. • If you totally mess things up and want to start over, you can always get rid of any object you’ve created with a drop statement. The syntax is different for tables and constraints. DROP TABLE
;
ALTER TABLE
DROP CONSTRAINT ;
This is where consistent constraint naming comes in handy, so you can just remember the PK or FK name rather than remembering the syntax for looking up the names in another table. The DROP TABLE statement gets rid of its own PK constraint, but won’t work until you separately drop any FK constraints (or child tables) that refer to this one. It also gets rid of all data that was contained in the table—and it doesn't even ask you if you really want to do this! • All of the information about objects in your schema is contained, not surprisingly, in a set of tables that is called the data dictionary. There are hundreds of these tables most database systems, but all of them will allow you to see information about your own tables, in many cases with a graphical interface. How you do this is entirely system-dependent.
Data manipulation language DML statements are used to work with the data in tables. When you are connected to most multi-user databases (whether in a client program or by a connection from a Web page script), you are in effect working with a private copy of your tables that can’t be seen by
anyone else until you are finished (or tell the system that you are finished). You have already seen the SELECT statement; it is considered to be part of DML even though it just retreives data rather than modifying it. • The insert statement is used, obviously, to add new rows to a table. INSERT INTO
VALUES (, ... );
The comma-delimited list of values must match the table structure exactly in the number of attributes and the data type of each attribute. Character type values are always enclosed in single quotes; number values are never in quotes; date values are often (but not always) in the format 'yyyy-mm-dd' (for example, '2006-11-30'). Yes, you will need a separate INSERT statement for every row. • The update statement is used to change values that are already in a table. UPDATE
SET = WHERE ;
The update expression can be a constant, any computed value, or even the result of a SELECT statement that returns a single row and a single column. If the WHERE clause is omitted, then the specified attribute is set to the same value in every row of the table (which is usually not what you want to do). You can also set multiple attribute values at the same time with a comma-delimited list of attribute=expression pairs. • The delete statement does just that, for rows in a table. DELETE FROM
WHERE ;
If the WHERE clause is omitted, then every row of the table is deleted (which again is usually not what you want to do)—and again, you will not get a “do you really want to do this?” message. • If you are using a large multi-user system, you may need to make your DML changes visible to the rest of the users of the database. Although this might be done automatically when you log out, you could also just type: COMMIT;
• If you’ve messed up your changes in this type of system, and want to restore your private copy of the database to the way it was before you started (this only works if you haven’t already typed COMMIT), just type: ROLLBACK;
Although single-user systems don’t support commit and rollback statements, they are used in large systems to control transactions , which are sequences of changes to the database. Transactions are frequently covered in more advanced courses.
Privileges If you want anyone else to be able to view or manipulate the data in your tables, and if your system permits this, you will have to explicitly grant the appropriate privilege or privileges (select, insert, update, or delete) to them. This has to be done for each table. The most common case where you would use grants is for tables that you want to make available to scripts running on a Web server, for example: GRANT select, insert ON customers TO webuser;
Basic query operation: the join SQL syntax In most queries, we will want to see data from two or more tables. To do this, we need to join the tables in a way that matches up the right information from each one to the other—in our example, listing all of the customer data along with all of the order data for the orders that each customer has placed. In the latest SQL standard, the join is specified in the FROM clause: SELECT * FROM customers NATURAL JOIN orders;
Customers joined to Orders cfirstname clastname
cphone
cstreet
czipcode orderdate soldby
Alvaro
Monge
562-333-4141 2145 Main
90840
2003-07-14 Patrick
Wayne
Dick
562-777-3030 1250 Bellflower 90840
2003-07-14 Patrick
Alvaro
Monge
562-333-4141 2145 Main
90840
2003-07-18 Kathleen
Alvaro
Monge
562-333-4141 2145 Main
90840
2003-07-20 Kathleen
• The NATURAL JOIN keyword specifies that the attributes whose values will be matched between the two tables are those with matching names; with very rare exceptions, these will be the pk/fk attributes, and they have to have matching data types (and domains) as well. The join attributes that are matched—here {cfirstname, clastname, cphone}—are shown only once in the result, along with all other attributes of both tables. • Notice that all of the customer information is repeated for each order that the customer has placed; this is expected because of the one-to-many relationship between Customers and Orders. Notice also that any customer who has not placed an order is missing from the results; this is also expected because there is no fk in the Orders table to match that customer's pk in the Customer's table.
• The NATURAL JOIN shown here matches the RA join definition precisely. Unfortunately, not all database systems support the NATURAL JOIN keyword, so you may have to use a different syntax and may see slightly different results. We will discuss other join types in a later page.
How it works The easiest way to understand the join is to think of the database software looking one-byone at each pair of rows from the two tables. Customers cfirstname clastname
cphone
cstreet
czipcode
Tom
Jewett
714-555-1212 10200 Slater
92708
Alvaro
Monge
562-333-4141 2145 Main
90840
Wayne
Dick
562-777-3030 1250 Bellflower 90840 Orders
cfirstname clastname
cphone
orderdate soldby
Alvaro
Monge
562-333-4141 2003-07-14 Patrick
Wayne
Dick
562-777-3030 2003-07-14 Patrick
Alvaro
Monge
562-333-4141 2003-07-18 Kathleen
Alvaro
Monge
562-333-4141 2003-07-20 Kathleen
• Look at the first row of Orders and the first row of Customers. Do the join attributes (pk/fk pair) match? No, so nothing happens; these rows are not part of the join results. Now look at the first row of Orders and the second row of Customers. Do the join attributes (pk/fk pair) match? Yes, they do. All of the attributes of each row are pasted together (with one copy of the matching pk/fk pair). You can see this in the first row of the SQL join example above. First row of Orders and third row of Customers? No match, so no resulting row. • Next, look at the second row of Orders and the first row of Customers. No match, no resulting row. Same with second row of Orders to second row of Customers. Second row of Orders to third row of Customers? Yes; these are pasted together and included in the result. • Repeat this for the third and fourth rows of Customers and all of the rows of Orders. Two matches are found, which results in two pasted rows in the result. Notice that none of the Orders rows have matched the first row of Customers. • If you have taken even the most basic course in algorithms, you will realize that SQL doesn’t really implement the join in the way that we’ve just described it. However, this mental model will help you to follow the RA definition (below), with the inside workings of the database software left to a more advanced course or tutorial.
RA syntax The RA join of two relations, r over scheme R and s over scheme S, is written r s, or in our example, customers orders. The scheme of the result, exactly as you have seen in the SQL
syntax, is the union of the two relation schemes, R ∪S. The join attributes are found in the intersection of the two schemes, R∩S. Clearly, the intersection attributes must inherit the same assignment rule from R and S; this makes the two schemes compatible. • The result of the RA join consists of the pairwise paste of all tuples from the two relations, written paste(t,u) for any tuple t from relation r over scheme R and u from relation s over scheme S. The result of the paste operation is exactly as explained in the preceeding section. • In the very, very rare case where there is no intersection between schemes R and S (that is, R∩S = {null}), the schemes are still compatible and every tuple from relation r is pasted to every tuple from relation s, with all of the attributes from both schemes contained in the resulting tuples. In set theory, this is a Cartesian product of the two relations; in practice, it is almost always nonsense and not what you want. The Cartesian product can also be written in RA as r × s, or intentionally specified in SQL with the CROSS JOIN keyword. • You might want to visualize the three possible results of the paste operation using the graphic representation of schemes and tuples that we presented in an earlier page.
Other views of this diagram: Large image - Description (text)
SQL technique: multiple joins and the distinct keyword It is important to realize that if you have a properly designed and linked database, you can retrieve information from as many tables as you want, specify retrieval conditions based on any data in the tables, and show the results in any order that you like.
Example: We’ll use the order entry model. We’d like a list of all the products that have been purchased by a specific customer. • Unless you can memorize the exact spelling of every attribute in the database, along with all the PKs and FKs (I can’t), you should keep a copy of the relation scheme diagram handy when you build the query. We’ll show it again here, circling the attributes that we need for this query.
Other views of this diagram: Large image - Description (text) • Our query is still a simple one that could be expressed in RA using only the select, project, and join operators: πcLastName, cFirstName, prodName σcFirstName='Alvaro' and cLastName='Monge' (customers orders orderlines products) • The information that we need is found in the Products table. You could think of this as “output” from the query that will be part of the SELECT clause attribute list. The retrieval condition, or “input” to the query, is based on the Customers table—these attributes will be needed for the WHERE clause. You should include these attributes in the SELECT list, so that you are sure that your query is showing the data that you want. • We will also need to include the intervening tables in the FROM clause of the query, since this is the only way to correctly associate the Customers data with the Products data. (You can’t join Customers to Products, because they don’t have any common attributes.) • Another way to think about this is to simply “follow the PK-FK pairs” from table to table until you have completely linked all of the information you need. If you look carefully at the relation scheme for this query, you will realize that we could have bypassed the Orders table (since the custID is also part of the OrderLines scheme). If there were an orderID, or if we needed data from the Orders table, it would have to be included, so we’ll keep it here for
illustration. In SQL, we’ll build the query using the same step-by-step procedure that you have seen before. 1. Look at the result set (all of the linked data). We won’t show the result here because of space limitations on the web page. SELECT * FROM customers NATURAL JOIN orders NATURAL JOIN orderlines NATURAL JOIN products;
• Your database system might not support the NATURAL JOIN syntax that we show here. We’ll discuss this issue further when we look at join types. The multiple natural joins in our example work correctly because there are no non-pk/fk attributes in any of our tables that have the same name. In larger, more complicated databases, this might not be true. 2. Pick the rows you want, and be sure that all of the information makes sense and is really what you are looking for. (This one is still too wide for the page.) SELECT * FROM customers NATURAL JOIN orders NATURAL JOIN orderlines NATURAL JOIN products WHERE cFirstName = 'Alvaro' AND cLastName = 'Monge';
3. Now pick the columns that you want, and again check the results. Notice that we are including the retreival condition attributes in the SELECT clause, to be sure that this really is the right answer. SELECT cFirstName, cLastName, prodName FROM customers NATURAL JOIN orders NATURAL JOIN orderlines NATURAL JOIN products WHERE cFirstName = 'Alvaro' AND cLastName = 'Monge';
Products purchased cFirstame cLastame
prodame
Alvaro
Monge
Hammer, framing, 20 oz.
Alvaro
Monge
Hammer, framing, 20 oz.
Alvaro
Monge
Screwdriver, Phillips #2, 6 inch
Alvaro
Monge
Screwdriver, Phillips #2, 6 inch
Alvaro
Monge
Pliers, needle-nose, 4 inch
The distinct keyword Oops! We only wanted a list of the individual product names that this customer has purchased, but some of them are listed more than once. What went wrong?
• If the RA version of our query could have actually been executed, each row of the result table above would be distinct—remember that a relation is a set of tuples, and sets can’t have duplicates—and there would of course be fewer rows than you see here. • SQL doesn’t work the same way. The reason for the duplicates is that the SELECT clause simply eliminated the unwanted columns from the result set; it left all of the rows that were picked by the WHERE clause. • The real problem in SQL is that the SELECT attribute list is not a super key for the result set. Look again very carefully at the relation scheme to understand why this is true. Any time that this happens, we can eliminate the duplicate rows by including the DISTICT keyword in the SELECT clause. While making this revision, we’ll also list the product names in alphabetical order. SELECT DISTINCT cFirstName, cLastName, prodName FROM customers NATURAL JOIN orders NATURAL JOIN orderlines NATURAL JOIN products WHERE cFirstName = 'Alvaro' AND cLastName = 'Monge' ORDER BY prodName;
Distinct products cFirstame cLastame
prodame
Alvaro
Monge
Hammer, framing, 20 oz.
Alvaro
Monge
Pliers, needle-nose, 4 inch
Alvaro
Monge
Screwdriver, Phillips #2, 6 inch
• A sloppy way to be sure that you never have duplicate rows would be to always use the DISTINCT keyword. Please don’t do this—it just keeps you from understanding what is really going on in the query. If the SELECT attribute list does form a super key of the FROM clause (result set), the DISTINCT keyword is not needed, and should not be used.
SQL technique: join types Inner join All of the joins that you have seen so far have used the natural join syntax—for example, to produce a list of customers and dates on which they placed orders. Remember that if this syntax is available, it will automatically pick the join attributes as those with the same name in both tables (intersection of the schemes). It will also produce only one copy of those attributes in the result table. SELECT cFirstName, cLastName, orderDate FROM customers NATURAL JOIN orders;
• The join does not consider the pk and fk attributes you have specified. If there are any non pk/fk attributes that have the same names in the tables to be joined, they will also be included in the intersection of the schemes, and used as join attributes in the natural join. The results will certainly not be correct! This problem might be especially difficult to detect in cases
where many natural joins are performed in the same query. Fortunately, you can always specify the join attributes yourself, as we describe next. • Another keyword that produces the same results (without the potential attribute name problem) is the inner join. With this syntax, you may specify the join attributes in a USING clause. (Multiple join attributes in the USING clause are separated by commas.) This also produces only one copy of the join attributes in the result table. Like the NATURAL JOIN syntax, the USING clause is not supported by all systems. SELECT cFirstName, cLastName, orderDate FROM customers INNER JOIN orders USING (custID);
• The most widely-used (and most portable) syntax for the inner join substitutes an ON clause for the USING clause. This requires you to explicitly specify not only the join attribute names, but the join condition (normally equality). It also requires you to preface (qualify) the join attribute names with their table name, since both columns will be included in the result table. This is the only syntax that will let you use join attributes that have different names in the tables to be joined. Unfortunately, it also allows you to join tables on attributes other than the pk/fk pairs, which was a pre-1992 way to answer queries that can be written in better ways today. SELECT cFirstName, cLastName, orderDate FROM customers INNER JOIN orders ON customers.custID = orders.custID;
• You can save a bit of typing by specifying an alias for each table name (such as c and o in this example), then using the alias instead of the full name when you refer to the attributes. This is the only syntax that will let you join a table to itself, as we will see when we discuss recursive relationships. SELECT cFirstName, cLastName, orderDate FROM customers c INNER JOIN orders o ON c.custID = o.custID;
• All of the join statements above are specified as part of the 1992 SQL standard, which was not widely supported for several years after that. In earlier systems, joins were done with the 1986 standard SQL syntax. Although you shouldn’t use this unless you absolutely have to, you just might get stuck working on an older database. If so, you should recognize that the join condition is placed confusingly in the WHERE clause, along with all of the tests to pick the right rows: SELECT cFirstName, cLastName, orderDate FROM customers c, orders o WHERE c.custID = o.custID;
Outer join One important effect of all natural and inner joins is that any unmatched PK value simply drops out of the result. In our example, this means that any customer who didn’t place an order isn’t shown. Suppose that we want a list of all customers, along with order date(s) for
those who did place orders. To include the customers who did not place orders, we will use an outer join, which may take either the USING or the ON clause syntax. SELECT cFirstName, cLastName, orderDate FROM customers c LEFT OUTER JOIN orders o ON c.custID = o.custID;
All customers and order dates cfirstname clastname orderdate
Tom
Jewett
Alvaro
Monge
2003-07-14
Alvaro
Monge
2003-07-18
Alvaro
Monge
2003-07-20
Wayne
Dick
2003-07-14
• Notice that for customers who placed no orders, any attributes from the Orders table are simply filled with NULL values. • The word “left” refers to the order of the tables in the FROM clause (customers on the left, orders on the right). The left table here is the one that might have unmatched join attributes— the one from which we want all rows. We could have gotten exactly the same results if the table names and outer join direction were reversed: SELECT cFirstName, cLastName, orderDate FROM orders o RIGHT OUTER JOIN customers c ON o.custID = c.custID;
• An outer join makes sense only if one side of the relationship has a minimum cardinality of zero (as Orders does in this example). Otherwise, the outer join will produce exactly the same result as an inner join (for example, between Orders and OrderLines). • The SQL standard also allows a FULL outer join, in which unmatched join attributes from either side are paired with null values on the other side. You will probably not have to use this with most well-designed databases.
Evaluation order Multiple joins in a query are evaluated left-to-right in the order that you write them, unless you use parentheses to force a different evaluation order. (Some database systems require parentheses in any case.) The schemes of the joins are also cumulative in the order that they are evaluated; in RA, this means that r1
r2
r3 = (r1
r2)
r3.
• It is especially important to remember this rule when outer joins are mixed with other joins in a query. For example, if you write: SELECT cFirstName, cLastName, orderDate, UPC, quantity FROM customers LEFT OUTER JOIN orders USING (custID) NATURAL JOIN orderlines;
you will lose the customers who haven’t placed orders. They will be retained if you force the second join to be executed first: SELECT cFirstName, cLastName, orderDate, UPC, quantity FROM customers LEFT OUTER JOIN (orders NATURAL JOIN orderlines) USING (custID);
Other join types For sake of completeness, you should also know that if you try to join two tables with no join condition, the result will be that every row from one side is paired with every row from the other side. Mathematically, this is a Cartesian product of the two tables, as you have seen before. It is almost never what you want. In pre-1992 syntax, it is easy to do this accidently, by forgetting to put the join condition in the WHERE clause: SELECT cFirstName, cLastName, orderDate FROM customers, orders;
• If your system is backward-compatible (most are), you might actually try this just to prove to yourself that the result is pure nonsense. However, if you ever have an occasion to really need a Cartesian product of two tables, use the new cross join syntax to prove that you really mean it. Notice that this example still produces nonsense. SELECT cFirstName, cLastName, orderDate FROM customers CROSS JOIN orders;
• It is possible, but confusing, to specify a join condition other than equality of two attributes; this is called a non-equi-join. If you see such a thing in older code, it probably represents a WHERE clause or subquery in disguise. • You may also hear the term self join, which is nothing but an inner or outer join between two attributes in the same table. We’ll look at these when we discuss recursive relationships.
SQL technique: functions Sometimes, the information that we need is not actually stored in the database, but has to be computed in some way from the stored data. In our order entry example, there are two derived attributes (/subtotal in OrderLines and /total in Orders) that are part of the class diagram but not part of the relation scheme. We can compute these by using SQL functions in the SELECT statement. There are many, many functions in any implementation of SQL—far more than we can show here. Unfortunately, many of the functions are defined quite differently in different database packages, so you should always consult a reference manual for your specific software.
Computed columns
We can compute values from information that is in a table simply by showing the computation in the SELECT clause. Each computation creates a new column in the output table, just as if it were a named attribute. Example: We want to find the subtotal for each line of the OrderLines table, just as shown in the UML class diagram. Obviously, the total of each line is simply the unit sale price times the quantity ordered, so we don’t even need a function yet—just the computation. We have included all three of the OrderLines PK attributes in the SELECT clause attribute list, to be sure that we show the subtotal for each distinct line. SELECT custID, orderDate, UPC, unitSalePrice * quantity FROM orderlines;
Order line subtotals custid orderdate
upc
unitsaleprice * quantity
5678 2003-07-14 51820 33622 11.95 9012 2003-07-14 51820 33622 23.90 9012 2003-07-14 11373 24793 21.25 5678 2003-07-18 81809 73555 18.00 5678 2003-07-20 51820 33622 23.90 5678 2003-07-20 81809 73555 9.00 5678 2003-07-20 81810 63591 24.75 Computations are not limited just to column names; they may also include constants. For example, unitsaleprice * 1.06 might be used to find the sale price plus sales tax. Notice that the computation itself is shown as the heading for the computed column. This is awkward to read, and doesn’t really tell us what the column means. We can create our own column heading or alias using the AS keyword as shown below. (In fact, we could simply write the new name of the column without saying AS. Please don’t do this—it hurts readability of your code.) If your want your column alias to have spaces in it, you will have to enclose it in double quote marks. SELECT custID, orderDate, UPC, unitSalePrice * quantity AS subtotal FROM orderlines;
Order line subtotals custid orderdate
upc
subtotal
5678 2003-07-14 51820 33622 11.95 9012 2003-07-14 51820 33622 23.90 9012 2003-07-14 11373 24793 21.25 5678 2003-07-18 81809 73555 18.00 5678 2003-07-20 51820 33622 23.90 5678 2003-07-20 81809 73555 9.00 5678 2003-07-20 81810 63591 24.75
Aggregate functions
SQL aggregate functions let us compute values based on multiple rows in our tables. They are also used as part of the SELECT clause, and also create new columns in the output. Example: First, let’s just find the total amount of all our sales. To compute this, all we need is to do is to add up all of the price-times-quantity computations from every line of the OrderLines. We will use the SUM function to do the calculation. The output table, as you should expect, will contain only one row. SELECT SUM(unitSalePrice * quantity) AS totalsales FROM orderlines;
Sales totalsales
132.75 Next, we’ll compute the total for each order (the derived attribute shown in the UML Order class). We still need to add up order lines, but we need to group the totals for each order. We can do this with the GROUP BY clause. This time, the output will contain one row for every order, since the customerID and orderDate form the PK for Orders, not OrderLines. Notice that the SELECT clause and the GROUP BY clause contain exactly the same list of attributes, except for the calculation. In most cases, you will get an error message if you forget to do this. SELECT custID, orderDate, SUM(unitSalePrice * quantity) AS total FROM orderlines GROUP BY custID, orderDate;
Order totals custid orderdate total
5678 2003-07-14 11.95 5678 2003-07-18 18.00 5678 2003-07-20 57.65 9012 2003-07-14 45.15 Other frequently-used functions that work the same way as SUM include MIN (minimum value of those in the grouping), MAX (maximum value of those in the grouping, and AVG (average value of those in the grouping). The COUNT function is slightly different, since it returns the number of rows in a grouping. To count all rows, we can use the * (for example, to find out how many orders were placed). SELECT COUNT(*) FROM orders;
Orders COUT(*)
4 We can also count groups of rows with identical values in a column. In this case, COUNT will ignore NULL values in the column. Here, we’ll find out how many times each product has been ordered.
SELECT prodname AS "product name", COUNT(prodname) AS "times ordered" FROM products NATURAL JOIN orderlines GROUP BY prodname;
Product orders product name
times ordered
Hammer, framing, 20 oz.
3
Pliers, needle-nose, 4 inch
1
Saw, crosscut, 10 tpi
1
Screwdriver, Phillips #2, 6 inch 2 A WHERE clause can be used as usual before the GROUP BY, to eliminate rows before the group function is executed. However, if we want to select output rows based on the results of the group function, the HAVIG clause is used instead. For example, we could ask for only those products that have been sold more than once: SELECT prodname AS "product name", COUNT(prodname) AS "times ordered" FROM products NATURAL JOIN orderlines GROUP BY prodname HAVING COUNT(prodname) > 1;
Other functions Most database systems offer a wide variety of functions that deal with formatting and other miscellaneous tasks. These functions tend to be proprietary, differing widely from system to system in both availability and syntax. Most are used in the SELECT clause, although some might appear in a WHERE clause expression or an INSERT or UPDATE statement. Typical functions include: • Functions for rounding, truncating, converting, and formatting numeric data types. • Functions for concatenating, altering case, and manipulating character data types. • Functions for formatting dates and times, or retrieving the date and time from the operating system. • Functions for converting data types such as date or numeric to character string, and viceversa. • Functions for supplying visible values to null attributes, allowing conditional output, and other miscellaneous tasks.
SQL technique: subqueries Sometimes you don’t have enough information available when you design a query to determine which rows you want. In this case, you’ll have to find the required information with a subquery.
Example: Find the name of customers who live in the same zip code area as Wayne Dick. We might start writing this query as we would any of the ones that we have already done: SELECT cFirstName, cLastName FROM customers WHERE cZipCode = ???
• Oops! We don’t know what zip code to put in the WHERE clause. No, we can’t look it up manually and type it into the query—we have to find the answer based only on the information that we have been given! • Fortunately, we also know how to find the right zip code by writing another query: SELECT cZipCode FROM Customers WHERE cFirstName = 'Wayne' AND cLastName = 'Dick';
Zip code czipcode
90840 • Notice that this query returns a single column and a single row. We can use the result as the condition value for cZipCode in our original query. In effect, the output of the second query becomes input to the first one, which we can illustrate with their relation schemes:
• Syntactically, all we have to do is to enclose the subquery in parenthses, in the same place where we would normally use a constant in the WHERE clause. We’ll include the zip code in the SELECT line to verify that the answer is what we want: SELECT cFirstName, cLastName, cZipCode FROM customers WHERE cZipCode = (SELECT cZipCode FROM customers WHERE cFirstName = 'Wayne' AND cLastName = 'Dick');
Customers cfirstname clastname czipcode
Alvaro
Monge
90840
Wayne
Dick
90840
A subquery that returns only one column and one row can be used any time that we need a single value. Another example would be to find the product name and sale price of all products whose unit sale price is greater than the average of all products. We can see that the
DISTINCT keyword is needed, since the SELECT attributes are not a super key of the result set: SELECT DISTINCT prodName, unitSalePrice FROM Products NATURAL JOIN OrderLines WHERE unitSalePrice > the average unit sale price
• Again, we already know how to write another query that finds the average: SELECT AVG(unitSalePrice) FROM OrderLines;
Average AVG(unitsaleprice)
10.621428 • We can visualize the combined queries with their relation schemes, and write the full syntax as before:
SELECT DISTINCT prodName, unitSalePrice FROM Products NATURAL JOIN OrderLines WHERE unitSalePrice > (SELECT AVG(unitSalePrice) FROM OrderLines);
Above average prodname
unitsaleprice
Hammer, framing, 20 oz. 11.95 Saw, crosscut, 10 tpi
21.25
Subqueries can also be used when we need more than a single value as part of a larger query. We’ll see examples of these in later pages.
SQL technique: union and minus Set operations on tables
Some students initially think of the join as being a sort of union between two tables. It’s not (except for the schemes). The join pairs up data from two very different tables. In RA and SQL, union can operate only on two identical tables. Remember the Venn-diagram representation of the union and minus operations on sets. Union includes members of either or both sets (with no duplicates). Minus includes only those members of the set on the left side of the expression that are not contained in the set on the right side of the expression.
• Both sets, R and S, have to contain objects of the same type. You can’t union or minus sets of integers with sets of characters, for example. All sets, by definition, are unordered and cannot contain duplicate elements. • SQL and RA set operations treat tables as sets of rows. Therefore, the tables on both sides of the union or minus operator have to have at least the same number of attributes, with corresponding attributes being of the same data type. It’s usually cleaner and more readable if you just go ahead and give them the same name using the AS keyword.
Union For this example, we will add a Suppliers table to our sales data entry model. “A supplier is a company from which we purchase products that we will re-sell.” Each supplier suppliers zero to many products; each product is supplied by one and only one supplier. The supplier class attributes include the company name and address, plus the name and phone number of the representative from whom we make our purchases. • We would like to produce a listing that shows the names and phone numbers of all people we deal with, whether they are suppliers or customers. We need rows from both tables, but they have to have the same attribute list. Looking at the relation scheme, we find corresponding first name, last name, and phone number attributes, but we still need to show what company each of the supplier representatives works for.
• We can create an extra column in the query output for the Customers table by simply giving it a name and filling it with a constant value. Here, we’ll use the value 'Customer' to distinguish these rows from supplier representatives. SQL uses the column names of the first part of the union query as the column names for the output, so we will give each of them aliases that are appropriate for the entire set of data.
• Build and test each component of the union query individually, then put them together. The ORDER BY clause has to come at the end. SELECT cLastName AS "Last Name", cFirstName AS "First Name", cPhone as "Phone", 'Customer' AS "Company" FROM customers UNION SELECT repLName, repFName, repPhone, sCompanyName FROM suppliers ORDER BY "Last Name";
Phone list Last ame First ame
Phone
Company
Bradley
Jerry
888-736-8000 Industrial Tool Supply
Dick
Wayne
562-777-3030 Customer
Jewett
Tom
714-555-1212 Customer
Monge
Alvaro
562-333-4141 Customer
O'Brien
Tom
949-567-2312 Bosch Machine Tools
Minus Sometimes you have to think about both what you do want and what you don’t want in the results of a query. If there is a WHERE clause predicate that completely partitions all rows of interest (the result set) into those you want and those you don’t want, then you have a simple query with a test for inequality. • The multiplicity of an association can help you determine how to build the query. Since each product has one and only one supplier, we can partition the Products table into those that are supplied by a given company and those that are not. SELECT prodName, sCompanyName FROM Products NATURAL JOIN Suppliers WHERE sCompanyName <> 'Industrial Tool Supply';
• Contrast this to finding customers who did not make purchases in 2002. Because of the optional one-to-many association between Customers and Orders, there are actually four possibilities: 1. A customer made purchases in 2002 (only). 2. A customer made purchases in other years, but not in 2002. 3. A customer made purchases both in other years and in 2002. 4. A customer made no purchases in any year. • If you try to write this as a simple test for inequality, SELECT DISTINCT cLastName, cFirstName, cStreet, cZipCode FROM Customers NATURAL JOIN Orders WHERE TO_CHAR(orderDate, 'YYYY') <> '2002';
you will correctly exclude group 1 and include group 2, but falsely include group 3 and falsely exclude group 4. Please take time to re-read this statement and convince yourself why it is true! • We can show in set notation what we need to do: {customers who did not make purchases in 2002} = {all customers} − {those who did } There are two ways to write this in SQL. • The easiest syntax in this case is to compare only the customer IDs. We’ll use the NOT IN set operator in the WHERE clause, along with a subquery to find the customer ID of those who did made purchases in 2002. SELECT cLastName, cFirstName, cStreet, cZipCode FROM Customers WHERE custID NOT IN (SELECT custID FROM Orders WHERE TO_CHAR(orderDate, 'YYYY') = '2002');
• We can also use the MINUS operator to subtract rows we don’t want from all rows in Customers. (Some versions of SQL use the keyword EXCEPT instead of MINUS.) Like the UNION, this requires the schemes of the two tables to match exactly in number and type of attributes. SELECT cLastName, cFirstName, cStreet, cZipCode FROM Customers MINUS SELECT cLastName, cFirstName, cStreet, cZipCode FROM Customers NATURAL JOIN Orders WHERE TO_CHAR(orderDate, 'YYYY') = '2002';
Other set operations SQL has two additional set operators. UNION ALL works like UNION, except it keeps duplicate rows in the result. INTERSECT operates just like you would expect from set theory; again, the schemes of the two tables must match exactly.
SQL technique: views and indexes A view is simply any SELECT query that has been given a name and saved in the database. For this reason, a view is sometimes called a named query or a stored query. To create a view, you use the SQL syntax: CREATE OR REPLACE VIEW < view_name> AS SELECT ;
• The view query itself is saved in the database, but it is not actually run until it is called with another SELECT statement. For this reason, the view does not take up any disk space for data
storage, and it does not create any redundant copies of data that is already stored in the tables that it references (which are sometimes called the base tables of the view). • Although it is not required, many database developers identify views with names such as v_Customers or Customers_view. This not only avoids name conflicts with base tables, it helps in reading any query that uses a view. • The keywords OR REPLACE in the syntax shown above are optional. Although you don’t need to use them the first time that you create a view, including them will overwrite an older version of the view with your latest one, without giving you an error message. • The syntax to remove a view from your schema is exactly what you would expect: DROP VIEW ;
Using views A view name may be used in exactly the same way as a table name in any SELECT query. Once stored, the view can be used again and again, rather than re-writing the same query many times. • The most basic use of a view would be to simply SELECT * from it, but it also might represent a pre-written subquery or a simplified way to write part of a FROM clause. • In many systems, views are stored in a pre-compiled form. This might save some execution time for the query, but usually not enough for a human user to notice. • One of the most important uses of views is in large multi-user systems, where they make it easy to control access to data for different types of users. As a very simple example, suppose that you have a table of employee information on the scheme Employees = {employeeID, empFName, empLName, empPhone, jobTitle, payRate, managerID}. Obviously, you can’t let everyone in the company look at all of this information, let alone make changes to it. • Your database administrator (DBA) can define roles to represent different groups of users, and then grant membership in one or more roles to any specific user account (schema). In turn, you can grant table-level or view-level permissions to a role as well as to a specific user. Suppose that the DBA has created the roles managers and payroll for people who occupy those positions. In Oracle®, there is also a pre-defined role named public, which means every user of the database. • You could create separate views even on just the Employees table, and control access to them like this: CREATE VIEW phone_view AS SELECT empFName, empLName, empPhone FROM Employees; GRANT SELECT ON phone_view TO public; CREATE VIEW job_view AS SELECT employeeID, empFName, empLName, jobTitle, managerID FROM Employees; GRANT SELECT, UPDATE ON job_view TO managers;
CREATE VIEW pay_view AS SELECT employeeID, empFName, empLName, payRate FROM Employees; GRANT SELECT, UPDATE ON pay_view TO payroll;
• Only a very few trusted people would have SELECT, UPDATE, INSERT, and DELETE privileges on the entire Employees base table; everyone else would now have ex actly the access that they need, but no more. • When a view is the target of an UPDATE statement, the base table value is changed. You can’t change a computed value in a view, or any value in a view that is based on a UNION query. You may also use a view as the target of an INSERT or DELETE statement, subject to any integrity constraints that have been placed on the base tables.
Materialized views Sometimes, the execution speed of a query is so important that a developer is willing to trade increased disk space use for faster response, by creating a materialized view. Unlike the view discussed above, a materialized view does create and store the result table in advance, filled with data. The scheme of this table is given by the SELECT clause of the view definition. • This technique is most useful when the query involves many joins of large tables, or any other SQL feature that could contribute to long execution times. You might encounter this in a Web project, where the site visitor simply can’t be kept waiting while the query runs. • Since the view would be useless if it is out of date, it must be re-run, at the minimum, when there is a change to any of the tables that it is based on. The SQL syntax to create a materialized view includes many options for when it is first to be run, how often it is to be rerun, and so on. This requires an advanced reference manual for your specific system, and is beyond the scope of this tutorial.
Indexes An index, as you would expect, is a data structure that the database uses to find records within a table more quickly. Indexes are built on one or more columns of a table; each index maintains a list of values within that field that are sorted in ascending or descending order. Rather than sorting records on the field or fields during query execution, the system can simply access the rows in order of the index. Unique and non-unique indexes: When you create an index, you may allow the indexed columns to contain duplicate values; the index will still list all of the rows with duplicates. You may also specify that values in the indexed columns must be unique, just as they must be with a primary key. In fact, when you create a primary key constraint on a table, Oracle and most other systems will automatically create a unique index on the primary key columns, as well as not allowing null values in those columns. One good reason for you to create a unique index on non-primary key fields is to enforce the integrity of a candidate key, which otherwise might end up having (nonsense) duplicate values in different rows.
Queries versus insertion/update: It might seem as if you should create an index on every column or group of columns that will ever by used in an ORDER BY clause (for example: lastName, firstName). However, each index will have to be updated every time that a row is inserted or a value in that column is updated. Although index structures such as B or B+ trees allow this to happen very quickly, there still might be circumstances where too many indexes would detract from overall system performance. This and similar issues are often covered in more advanced courses. Syntax: As you would expect by now, the SQL to create an index is: CREATE INDEX ON (, ...);
To enforce unique values, add the UNIQUE keyword: CREATE UNIQUE INDEX ON (, ...);
To specify sort order, add the keyword ASC or DESC after each column name, just as you would do in an ORDER BY clause. To remove an index, simply enter: DROP INDEX ;
Glossary [ Skip navigation ] Show: [ all keywords ] [ A–C ] [ D–H ] [ I–N ] [ O–R ] [ S–Z ] Glossary keyword
definition
page_link
aggregate function
(SQL) Function that operates on a group of rows, for example, SUM.
aggregation
(UML) An association in which one class represents an assembly of components from one or more other class aggregate.php types. Components may also exist without being part of the assembly.
alias
(SQL) An alternate, short name for a table in the FROM clause of a SELECT statement.
jointypes.php
ALTER TABLE
(SQL) Statement to change structure, constraints, or other properties of a table.
tables.php
AS
(SQL) Keyword to specify a column alias in the SELECT clause.
functions.php
assignment rule
(RM) The association of each attribute in a scheme with class.php its domain.
association
(UML) The way that two classes are functionally connected to each other.
association.php
association class
(UML) A class that contains attributes which are the
manymany.php
functions.php
Glossary keyword
definition
page_link
properties of an association rather than a regular class. association, manyto-many
See many-to-many association.
manymany.php
association, one-toSee one-to-many association. many
association.php
association, recursive
See recursive association.
recursive.php
attribute
(UML,ER,RM) One piece of information that characterizes each member of a class.
class.php
attribute, derived
See derived attribute.
manymany.php
attribute, descriptive See descriptive attribute.
class.php
attribute, discriminator
See discriminator attribute.
loan.php
attribute, multivalued
See multivalued attribute.
hobbies.php
attribute, repeated
See repeated attribute.
phone.php
base table
(SQL) A table that is referenced by a view definition.
views.php
BCNF
(RM) Boyce-Codd normal form: a database with no subkey in any relation (with no exceptions).
subkeys.php
candidate key (CK)
(RM) A minimal super key, candidate to become rimary key.
keys.php
cardinality
(ER) See multiplicity.
association.php
Cartesian product
(RA) The result of the join of two relations with no join attributes specified, as defined in set theory. See also join.php cross join.
child
(RM,TM) The relation on the "many" (FK) side of a one-to-many association.
association.php
class
(UML) Any "thing" in the enterprise that is to be represented in the database.
class.php
column
(TM) See attribute.
tables.php
COMMIT
(SQL) Statement to make changes to data permanent.
ddldml.php
compatible
(RA) Two schemes are compatible if their intersection is null or if the intersection attributes inherit the same join.php assignment rule from their respective schemes.
complete specialization
(UML) All members of a superclass must also be members of at least one subclass.
subclass.php
composition
(UML) Stronger form of aggregation in which components cannot exist without being part of the assembly.
aggregate.php
constraint
(RM,TM) Any restriction on the values that can be entered in a table.
tables.php
CREATE TABLE
(SQL) Statement to do just what the name implies.
class.php
Glossary keyword
definition
page_link
cross join
(SQL) Paste of every pair of tuples from each relation, jointypes.php disregarding join attributes. See also Cartesian product .
data definition language (DDL)
(SQL) Statements to create and modify tables and other ddldml.php database objects.
data dictionary
(SQL) System tables that hold information about the structure of the database.
data integrity
(TM) In part, the value entered in each field of a table is domains.php consistent with its attribute domain.
data manipulation language (DML)
(SQL) Statements to work with data in a table.
data types
(SQL) As in programming languages, the data type that an attribute can hold in a table. This is not the same as ddldml.php the attribute's domain.
DELETE
(SQL) Statement to remove some or all rows from a table.
ddldml.php
denormalization
(RM) Intentionally "breaking the rules" of normal orms.
normalize.php
derived attribute
(UML) An attribute that can be computed from data stored elsewhere in the database.
manymany.php
(UML) An attribute that provides real-world descriptive attribute information, relevant to the enterprise, about the class that we are modeling.
ddldml.php
ddldml.php
class.php
design pattern
(UML) Modeling situations that you will encounter frequently in database design.
manymany.php
discriminator attribute
(RM,TM) An attribute that allows us to discriminate between multiple pairings of the same two individuals from each side of a many-to-many association.
loan.php
disjoint specialization
(UML) Each member of a superclass may be a member subclass.php of no more than one subclass
DISTINCT
(SQL) Optional clause of the SELECT statement. Use when the SELECT attributes do not form a super key of multijoin.php the FROM clause.
domain
(RM) The set of legal values that may be assigned to an class.php attribute.
domain, enumerated See enumerated domain.
enum.php
DROP CONSTRAINT
(SQL) Optional clause of the ALTER TABLE statement.
ddldml.php
DROP TABLE
(SQL) Statement to delete a table and all of its contents. ddldml.php
entity
(ER) See class.
class.php
entity-relationship (ER) model
An enterprise modeling tool used in database design.
models.html
enumerated domain
(RM) A domain that may be specified by a welldefined, reasonably-sized set of constant values.
enum.php
Glossary keyword
definition
page_link
exclusive specialization
(UML) See disjoint specialization.
external key
(UML,RM) A surrogate or substitute key that has been defined by an external organization. May be treated as a keys.php descriptive attribute in your model.
FD, partial
See partial FD.
subkeys.php
FD, transitive
See transitive FD.
subkeys.php
foreign key (FK)
(RM,TM) A set of attributes that is copied from the PK association.php of a parent table into the scheme of a child table.
functional dependency (FD)
(RM) Formal definition of the super key property.
subkeys.php
generalization
(UML) (noun) A superclass. (verb) The process of designing superclasses from "bottom up."
subclass.php
grant
(SQL) Statement to assign privileges to a user.
ddldml.php
HAVING
(SQL) Optional clause that selects aggregated group information in a SELECT statement.
functions.php
incomplete specialization
(UML) Some members of a superclass might not be members of any subclass.
subclass.php
index
(SQL) A data structure that the database uses to find records within a table more quickly.
views.php
inner join
(SQL) Join of two tables with join attributes specified by the programmer.
jointypes.php
INSERT INTO
(SQL) Statement to add a row of data to a table.
tables.php
join (RA)
(RA) Formal definition on which the SQL join is based, the pairwise paste of tuples from the relations being join.php joined. Please see full definition on the page.
join (SQL)
(SQL) Operation that links two tables, specified in the FROM clause of a SELECT statement. Please see full join.php definition on the page.
join attributes
Attributes whose values are matched during the join operation.
join.php
join, cross
See cross join.
jointypes.php
join, natural
See natural join.
jointypes.php
join, non-equi-
See non-equi-join.
jointypes.php
join, outer
See outer join.
jointypes.php
join, self
See self join.
jointypes.php
junction table
(RM,TM) The table created to hold the linking attributes (FKs) from both sides of a many-to-many association.
manymany.php
key
See super key.
tables.php
key, candidate
See candidate key.
keys.php
key, external
See external key.
keys.php
subclass.php
Glossary keyword
definition
page_link
key, foreign
See foreign key.
association.php
key, primary
See primary key.
tables.php
key, sub
See subkey.
subkeys.php
key, substitute
See substitute key.
keys.php
key, super
See super key.
tables.php
key, surrogate
See surrogate key.
keys.php
lossless join decomposition
Decomposition (breaking apart) of a relation scheme to eliminate a subkey. The original scheme’s information subkeys.php can be recreated with the join in a query.
many-to-many association
(UML) An association with maximum multiplicity of *..*
manymany.php
materialized view
(SQL) A view that creates and stores a result table in advance of being used.
views.php
MINUS
(RA,SQL) Operation as defined in set theory, returning the difference of the tuples/rows of two relations/tables setops.php over the same scheme.
multiplicity
(UML) The minimum and maximum number of individuals of one class that may be associated with a single member of another class.
association.php
multivalued attribute
(UML) An attribute that contains more than one value from its domain. This is a design error.
hobbies.php
named query
(SQL) A view.
views.php
natural join
(SQL) Join of two tables with the intersection of their jointypes.php schemes used as join attributes.
non-equi-join
(SQL) Join based on some condition other than equality jointypes.php of the join attributes.
normal form, third
See third normal form.
subkeys.php
normal forms
(RM) A progression of rules for well-structured database design.
normalize.php
normalization
(RM) Following a set of rules to insure that a database is well designed. See also normal forms.
subkeys.php
NULL
(SQL) A special constant value, compatible with any data type, that means "this field doesn't have any value phone.php assigned to it."
object
(UML) The instantiation of a class in OO programming class.php languages.
one-to-many association
(UML) An association with maximum multiplicity of 1..*
association.php
outer join
(SQL) Join of two tables that retains unmatched join attributes from one or both sides.
jointypes.php
overlapping specialization
(UML) Any member of a superclass may be a member subclass.php of more than one subclass.
Glossary keyword
definition
page_link
parent
(RM,TM) The relation on the "one" (PK) side of a oneassociation.php to-many association.
partial FD
(RM) A subkey that is part of the primary key of a relation.
partial specialization (UML) See incomplete specialization.
subkeys.php subclass.php
paste
(RA) Tuple operation on which the RA join is based. Please see full definition on the page.
join.php
predicate
(RA) Conditional statement used in the RA select operstion.
queries.php
primary key (PK)
(RM,TM) The SK set of attributes that the designer picks as the unique identifier for a database table.
tables.php
project (in RA)
(RA) Unary operator that picks attributes from a relation.
queries.php
property
(UML) See attribute.
class.php
query
A request to retrieve data from the database. See SELECT (SQL).
queries.php
query, stored
See stored query.
views.php
query, sub
See subquery.
subqueries.php
recursive association
(UML) An association between a single class type (in one role) and itself (in another role).
recursive.php
referential integrity
(RM,TM) A constraint on a table that does not allow an association.php FK value to be entered without a matching PK value.
relation
(RM) A set of tuples over the same scheme.
tables.php
relational algebra (RA)
The formal language used to sympolically manipulate objects of the RM.
models.html
relational model (RM)
The formal mathematical model of a relational database.
models.html
relationship
(ER) See association.
association.php
repeated attribute
(UML) An attribute that occurs more than once in the same class definition; it may also have attributes of its phone.php own. This is a design error.
result set
(SQL) The intermediate table that results from queries.php execution of the FROM clause of a SELECT statement.
role
(SQL) A name for a group of database users who have been granted the same privileges.
ROLLBACK
(SQL) Statement to discard changes that have not been ddldml.php made permanent.
row
(TM) The information about one individual in a database table. See also tuple.
tables.php
scheme
(RM) A set of attributes, with an assignment rule.
class.php
select (in RA)
(RA) Unary operator that picks tuples from a relation.
queries.php
SELECT (in SQL)
(SQL) Statement used to retrieve data from a table.
queries.php
views.php
Glossary keyword
definition
page_link
self join
(SQL) Join of one table to itself.
jointypes.php
specialization
(UML) (noun) A subclass. (verb) The process of designing subclasses from "top down."
subclass.php
specialization constraints
(UML) Description of subclass membership (incomplete versus complete, disjoint versus overlapping).
subclass.php
specialization, complete
See complete specialization.
subclass.php
specialization, disjoint
See disjoint specialization.
subclass.php
specialization, exclusive
See disjoint specialization.
subclass.php
specialization, incomplete
See incomplete specialization.
subclass.php
specialization, overlapping
See overlapping specialization.
subclass.php
specialization, partial
See incomplete specialization.
subclass.php
stereotype
(UML) A designator before a class name that specifies enum.php the type of the class.
stored query
(SQL) A view.
views.php
structured query language (SQL)
The language used to build and manipulate relational databases.
models.html
subclass
(UML) A class that inherits common attributes from a parent class, but contains unique attributes of its own. See also superclass and specialization.
subclass.php
subkey
(RM) A set of attributes that is a super key for some, but not all, of the attributes in a relation.
subkeys.php
subquery
(SQL) A query (SELECT statement) that is embedded in another query.
subqueries.php
subscheme
(RA) A subset of the attributes in a scheme, preserving queries.php the assignment rule. Used in the RA project operation.
substitute PK
(RM) An artificial, somewhat meaningful, primary key made up by the database designer under certain limited keys.php conditions. See also surrogate PK .
SUM
(SQL) Aggregate function to sum the values in a column.
functions.php
super key (SK)
(RM,TM) Any set of attributes whose values, taken together, uniquely identify each row of a table.
tables.php
superclass
(UML) A class that contains attributes common to one or more child classes. See also subclass and subclass.php generalization.
surrogate PK
(RM) An artificial, meaningless, primary key made up keys.php
