HTML tags), including the newline after the first tag and before the last tag.



 Server-Side Input Handling -- CGI The form contents will be assembled into an encoded query string. Using the GET method, this string is available in the environment variable QUERY_STRING. It is actually passed to the program through the URL -- examine the URL for the first of the forms above: http://asdf.asdf.asdf/asdf?thisinput=default&thisbox=on&radio1=2



Everything after the '?' is the query string. You'll see that a number of expressions appear concatenated by & symbols -- each expression assigns a string value to each form object. In this case, the text field named "thisinput" has the value "default", which is what was typed into the field, the checkbox "thisbox" has the value "on", and the radio button group "radio1" has the value "2" (the second button is checked -- note that this is the value I gave it, not a default value. The default is "on").



Let's look at another example from the second form: http://zxcv.zxcv.zxcv/zxcv?menu=option+4&scroller=option+1&scroller=option+2



The menu has option 4 selected, and the scroller has option 1 and option 2 selected. Note that spaces are converted to '+' symbols in the URL string. The character '+' is converted to its hex value %2B. Other characters similarly converted are & (to %26), % (to %25), and $ (to %24). This conversion is automatic. Using GET is not recommended, however. Some systems will truncate the URL before passing it to the CGI program, and thus the QUERY_STRING environment variable will contain only a prefix of the actual query string. Instead, you should use the POST method. The POST query string is encoded in precisely the same form as the GET query string, but instead of being passed in the URL and read into the QUERY_STRING variable, it is given to the CGI program as standard input, which you can thus read using ANSI functions or regular character reading functions. The only quirk is that the server will not send EOF at the end of the data. Instead, the size of the string is passed in the environment variable CONTENT_LENGTH, which can be accessed using the normal stdlib.h function: char *value; int length; value = getenv("CONTENT_LENGTH"); sscanf(value, "%d", &length);



Decoding the data is thus just a question of walking through the input and picking out the values. These values can then be used to determine what the user wants to see. We have written a very simple, linear-search-based mechanism for parsing the input string. These are located, as mentioned above, at cgiparse.c. You might want to cut and paste these into your own code or to use the .h file provided. You can use this in your CGI programs by calling Initialize() at the beginning of your code, and then calling GetFirstValue(key) and GetNextValue(key) to retreive the bindings for each of the FORM parameters. See the comments in the file for more details.



 Server-Side Input Handling -- Java



Java handles GET and POST slightly differently. The parsing of the input is done for you by Java, so you are separated from the actual format of the input data completely. Your program will be an object subclassed off of HttpServlet, the generalized Java Servlet class for handling web services. Servlet programs must override the doGet() or doPost() messages, which are methods that are executed in response to the client. There are two arguments to these methods, HttpServletRequest request and HttpServletResponse response. Let's take a look at a very simple servlet program, the traditional HelloWorld (this time with a doGet method): import import import import import



java.io.*; java.text.*; java.util.*; javax.servlet.*; javax.servlet.http.*;



public class Hello extends HttpServlet { public void doGet(HttpServletRequest request, HttpServletResponse response) throws IOException, ServletException { response.setContentType("text/html"); PrintWriter out = response.getWriter(); out.println(""); out.println(""); String title = "Hello World"; out.println(""); out.println(""); out.println(""); out.println("
" + title + "
"); String param = request.getParameter("param"); if (param != null) out.println("Thanks for the lovely param='" + param + "' binding.");



}



out.println(""); out.println("");



}



We'll discuss points in this code again in the section on Java Output, but for now, we will focus on the input side. The argument HttpServletRequest request represents the client request, and the values of the parameters passed from the HTML FORM can be retrieved by calling the HttpServletRequest getParameter method. This method takes as its argument the name of the parameter (the name of the HTML INPUT object), and returns as a Java String the value assigned to the parameter. In cases where the parameter may have multiple bindings, the method getParameterValues can be used to retrieve the values in an array of Java Strings -- note that getParameter will return the first value of this array. It is through these mechanisms that you can retrieve any of the values entered or implicit in the form.



 As might be inferred from the example above, Java returns null if the parameter for whose name you request does not have a value. Recall that unchecked buttons' bindings are not passed in a POST message -- you can check for null to determine when buttons are off.



Returning Output to the User In your project, you are going to be concerned with returning HTML documents to the user. The documents will be dynamically created based on the output of the query. You can format it however you like, using ordinary HTML formatting routines



CGI Output The only work you have to do apart from constructing an HTML document on the fly with the output from the query is to add a short header at the top of the file. Your header will represent the MIME type for HTML, and consists of a single line of text followed by a blank line: content-type: text/html  ... file ... 



There are, of course, many other types that you can return, but this is all you'll need to return your database queries. CGI returns the HTML document to the user through standard output from the program, so you can just use a regular printf function in your C programs. The format for setting the content type is just: printf("content-type: text/html\n\n");



Java Output Let's look back at our Java code example. You'll see a number of differences between the Servlet code and the CGI approach. Output is all handled by the HttpServletResponse object, which allows you to set the content type through the setContentType method. Instead of printing the HTTP header yourself, you tell the HttpServletResponse object that you want the content type to be "text/html" explicitly. All HTML is returned to the user through a PrintWriter object, that is retrieved from the response object using the getWriter method. HTML code is then returned line by line using the println method. Assuming that you all have a basic background in Java, so we won't provide a detailed treatment of exceptions here, but do note that IOException and ServletException both must either be handled or thrown.



Sample Code and Coding Tips



 I recommend that everyone attempt to play around a little bit with both of the methods, Java Servlet and CGI, if you have the time and inclination (though you only have to implement your database interface in one of them, of course).



CGI Sample Code Here is a demonstration of a PRO*C CGI program. You can also check out the source code. The HTML page demonstrates a few input features, though the only ones that do anything are the username and password fields. These are used to log onto your Oracle account when the CGI program is executed, create a table, do some insertions, demonstrate the production of HTML formatting through queries on the data (including a demonstration of constructing a new form, which may provide some of you with ideas of how to make a really cool interface), and then drop the table from your database. You may freely cannibalize whatever portions you find useful.



CGI Setup Your CGI script will be run from cgi-courses.stanford.edu. The URL for your CGI executable will be: http://cgi-courses.stanford.edu/~username/cgi-bin/scriptname You will need to perform the following actions before a CGI program will run: •



Get an account on cgi-courses.stanford.edu.



•



Create a directory in your home folder to hold your cgi binary executables: mkdir cgi-bin



•



Set access levels on your cgi-bin directory for your cgi-courses.stanford.edu account ("username" should be replaced with your username): fs setacl cgi-bin username.cgi write



•



• • • • • •



Make sure that your executeables correctly set all environment variables (normally this is done by /usr/class/cs145/all.env, but this is not available on the cgi machine, so you have to do it explicitly). Here is an example function that you should run before attempting to connect to the database (this function is in C, but you can pretty much just lift the settings and paste them into Perl or PHP, which also need them to connect to the database): void SetEnvs(void) { putenv("ORACLE_SID=SHR1_PRD"); putenv("ORACLE_HOME=/usr/pubsw/apps/oracle/8.1.7"); putenv("ORACLE_TERM=xsun5"); putenv("TNS_ADMIN=/usr/class/cs145/sqlnet"); putenv("TWO_TASK=SHR1_PRD"); }



•



Move the executable into your cgi-bin folder.



•



Change the permissions on your cgi executable: chmod 701 scriptname



 •



Use HTML forms to access your new program at http://cgicourses.stanford.edu/~username/cgi-bin/scriptname.



Here is the homepage of the leland CGI service, which has a FAQ and gives some information about the capabilities of the system. Please check here first if your CGI programs are giving you errors.



CGI Debugging Due to popular demand, a new cgi debugging feature was just added to the cgi service. It's not in the leland CGI docs yet. If you access your script like so: http://cgi-courses/cgi-bin/sboxd/~username/scriptname



The script will execute with extra debug info: •



All STDERR goes to the browser



•



A header is included, so lack of any output or lack of Content Type will not cause Internal Server Error.



If still receiving Internal Server Error, consult the cgi FAQ or look in the server log: http://cgi-courses/logs/error_log.



Note, the log shows only several recent entries, due to system issues. An alternative method is to run your cgi program from command-line, without using the web browser. Put your CGI input into the environment variable QUERY_STRING and run your program. For example (assuming your program is called cgiprog and expects two parameters name1 and name2): cd ~/cgi-bin setenv QUERY_STRING 'name1=abc&name2=def' cgiprog



Note: If you want to use debugging tools such as dbx or gdb, you need to modify Makefile to add the flag -g after cc or g++.



Java Sample Code You can provide your HTML FORMs on permanent webpages in your personal WWW directory -- though this isn't recommended because you then have to hard code the Servlet addresses -- or in the webpages subdirectory where you run your Servlet (see below in the Servlet setup section). Alternatively (or additionally) you can integrate FORMs into Servlets by creating a FORM on the fly in your Servlet program, which will be invoked when doPost() or doGet() are invoked by the client. An example of a program that creates a FORM on the fly can be found at RequestParamExample.java. An example that uses JDBC to implement an interface for querying information about a certain US state (based on the JDBC example programs provided in PDA assignment 5) can be found at StateQuerier.java.



 The following two examples implement the state query, but it separates the query form from the answer form, providing these services with two different Servlets: StateQueryForm.java and StateQueryAns.java. You can find the very simple example given above in the text at Hello.java. One last example demonstrates the concept of a Session, which we do not cover in this handout, but you can use to liven up your interface can be found at HelloSession.java.



Java Compilation in Unix Compiling Servlets in UNIX requires a few changes to your PATH and CLASSPATH environment variables. These changes have been made for you in the source file /afs/ir/class/cs145/all.env. They include the following additions: setenv PATH /afs/ir/class/cs145/jsdk2.1:/usr/pubsw/apps/jdk1.2/bin:${PATH} setenv CLASSPATH /afs/ir/class/cs145/jsdk2.1/servlet.jar:$CLASSPATH



If there are any difficulties, let us know. These have been tested on the elaine machines and are assumed to be operational on the leland Sparc machines (elaine, myth, epic, saga).



You also have to set up a specific directory structure to provide Servlets. The directory structure required by Servlets is essentially: [anydir] [servletdir] webpages WEB-INF servlets



A shell script to build this hierarchy is provided at /afs/ir/class/cs145/code/bin/buildServletDirectory (after you run source /afs/ir/class/cs145/all.env (which you probably should just add to your .cshrc file), you can run buildServletDirectory by just typing the command). You can store .html documents in your webpages directory, and they will be accessible at your Servlet address (see below), while all Servlets you write have to be located in the servlets directory to be recognized. Further information on the Java Servlet API can be found at Servlet Package Documentation page.



Servlet Setup The directory structure for your servlets and HTML documents was outlined in the previous section. Static HTML documents may be placed in the webpages directory and are accessible from the web at the address http://machinexx:portnum/page.html, where machinexx refers to the machine from which you're running the webserver (e.g. elaine12, saga22, myth7, etc.), portnum is a specific port (see below), and page.html is the name of the HTML page that you are serving. You may find it useful to create a static HTML document or a hierarchy of static



 documents to serve as the jumping off point for your Servlets, where your HTML FORMs that start the interaction with the database are found. Servlets will be found in the directory servletdir/webpages/WEB-INF/servlets, and will just be the .class files that you compile from your .java files using javac. These may be reached on the web using the URL http://machinexx:portnum/servlet/servletname. Note that the servlet directory is singular in the URL but plural in Unix, while the Servlet itself loses its .class in the URL. HTML and other documents contained in the servlets directory cannot be accessed over the web. Once you have your directory set up and your Servlets compiled, you have to run the Java JSDK 2.1 webserver manually on a specific leland machine in order to provide these documents over the web. The steps involved in starting the server are as follows: •



Choose a port number in the range 5000-65000. This will bind your server application to that port for the machine on which you're running your server. Try to choose a random number and remember it -- you will be the only person on that machine who can use that port, and you will need it to have access over the web.



•



From the root of your servlet directory (if you run our buildServletDirectory script, then it will be called servletdir), start the server by calling startserver -port portnum from the Unix command line, where portnum is the port number you chose above. The server will begin in the background, and you can see it using the ps command. If you do not enter a port number, the default port number, 8080, will be chosen for you (you can actually set the default yourself -- after you've run the server once, it will create a configuration file called "default.cfg" for you -- it finds the default port number here).



•



From your browser, enter the URL of a webpage or servlet contained in your servletdir hierarchy using the address structure mentioned above. Now you can play with your interface.



•



If you would like to stop the server, issue the command stopserver.



•



If you want to recompile your servlets, you have to stop the server and restart it again. Static HTML pages that you are hosting from the webpages directory, however, can be changed at will.



Handling Special Characters The special characters &, <, and >, need to be escaped as &, <, and >, respectively in HTML text (see NCSA Beginner's Guide to HTML). Moreover, special characters appearing in URL's need to be escaped, differently than when they appear in HTML text. For example, if you link on text with special characters and want to embed them into extended URLs as parameter values, you need to escape them: convert space to + or %20, convert & to %26, convert = to %3D, convert % to %25, etc. (In general, any special character can be escaped by a percent sign followed by the character's hexadecimal ASCII value.) Important: Do NOT escape



 the & that actually separates parameters! For example, if you want two parameters p1 and p2 to have the values 3 and M&M, you should write something like: http://cgi-courses.stanford.edu/~username/cgi-bin/cgiprog?p1=3&p2=M%26M



Be careful not to confuse the escape strings for HTML text with those for URL's. This document was written by Nathan Folkert (with help from Vincent Chu) for Prof. Jennifer Widom's CS145 class in Spring 2000; revised by Calvin Yang for Prof. Widom's CS145 class in Spring 2002.



Oracle: Frequently Asked Questions •



What built-in functions/operators are available for manipulating strings?



•



Can I print inside a PL/SQL program?



•



Is it possible to write a PL/SQL procedure that takes a table name as input and does something with that table?



•



What is the correct syntax for ordering query results by row-type objects?



•



How do I kill long-running queries in sqlplus, Pro*C, and JDBC?



•



In Pro*C, why do I get a strange "break outside loop or switch" error message?



What built-in functions/operators are available for manipulating strings? The most useful ones are LENGTH, SUBSTR, INSTR, and ||: •



LENGTH(str) returns the length of str in characters.



•



SUBSTR(str,m,n) returns a portion of str, beginning at character m, n characters long. If n is omitted, all characters to the end of str will be



returned. •



INSTR(str1,str2,n,m) searches str1 beginning with its n-th character for the m-th occurrence of str2 and returns the position of the character in str1 that



is the first character of this occurrence. •



str1 || str2 returns the concatenation of str1 and str2.



The example below shows how to convert a string name of the format 'last, first' into the format 'first last': || ||



SUBSTR(name, INSTR(name,',',1,1)+2) ' ' SUBSTR(name, 1, INSTR(name,',',1,1)-1)



For case-insensitive comparisons, first convert both strings to all upper case using Oracle's built-in function upper() (or all lower case using lower()).



 Can I print inside a PL/SQL program? Strictly speaking PL/SQL doesn't currently support I/O. But, there is a standard package DBMS_OUTPUT that lets you do the trick. Here is an example: -- create the procedure CREATE PROCEDURE nothing AS BEGIN DBMS_OUTPUT.PUT_LINE('I did nothing'); -- use TO_CHAR to convert variables/columns -- to printable strings END; . RUN; -- set output on; otherwise you won't see anything SET SERVEROUTPUT ON; -- invoke the procedure BEGIN nothing; END; . RUN;



Then you should see "I did nothing" printed on your screen.



is very useful for debugging PL/SQL programs. However, if you print too much, the output buffer will overflow (the default buffer size is 2KB). In that case, you can set the buffer size to a larger value, e.g.: DBMS_OUTPUT



BEGIN DBMS_OUTPUT.ENABLE(10000); nothing; END; . RUN;



Is it possible to write a PL/SQL procedure that takes a table name as input and does something with that table? For pure PL/SQL, the answer is no, because Oracle has to know the schema of the table in order to compile the PL/SQL procedure. However, Oracle provides a package called DBMS_SQL, which allows PL/SQL to execute SQL DML as well as DDL dynamically at run time. For example, when called, the following stored procedure drops a specified database table: CREATE PROCEDURE drop_table (table_name IN VARCHAR2) AS cid INTEGER; BEGIN -- open new cursor and return cursor ID cid := DBMS_SQL.OPEN_CURSOR; -- parse and immediately execute dynamic SQL statement -- built by concatenating table name to DROP TABLE command DBMS_SQL.PARSE(cid, 'DROP TABLE ' || table_name, dbms_sql.v7);



 -- close cursor DBMS_SQL.CLOSE_CURSOR(cid); EXCEPTION -- if an exception is raised, close cursor before exiting WHEN OTHERS THEN DBMS_SQL.CLOSE_CURSOR(cid); -- reraise the exception RAISE; END drop_table; . RUN;



What is the correct syntax for ordering query results by row-type objects? As a concrete example, suppose we have defined an object type PersonType with an ORDER MEMBER FUNCTION, and we have created a table Person of PersonType objects. Suppose you want to list all PersonType objects in Person in order. You'd probably expect the following to work: SELECT * FROM Person p ORDER BY p;



But it doesn't. Somehow, Oracle cannot figure out that you are ordering PersonType objects. Here is a hack that works: SELECT * FROM Person p ORDER BY DEREF(REF(p));



How do I kill long-running queries in sqlplus, Pro*C, and JDBC? Sometimes it is necessary to stop long-running queries, either because they take longer to run than you'd like, or because you realize you've made a mistake. It is important kill off such queries properly so that they don't take up extra computational resources and prevent others from using the system, especially near project deadlines when resources are most strained.



As a general precautionary measure, please be sure to test your queries under sqlplus prompt before running them through CGI or JDBC. It is much easier to kill a query in sqlplus than in CGI or JDBC. If your test query takes a long time to run under sqlplus, you can simply hit CtrlC to terminate it. Never close an ssh or telnet or xterm window without properly logging out. Always quit your programs (including sqlplus), stop Java servlets, and type "exit" or "logout" to quit. If you forceclose your ssh/telnet/xterm window, there may still be processes running in the background, and you may be taking up system resources without knowing it. If, for some reason, you cannot logout normally (for example, the system is not responding), you should open another window, login to the same machine where you have the problem, and kill the processes that is causing trouble: Type "ps -aef | grep [username]" to find the Process IDs of your processes (replace [username] with your leland user name), and kill the processes you want to



 terminate using "kill [processID]". Always use the "kill" command without the -9 flag first. Use -9 flag only if you cannot kill it otherwise. If you closed the window by mistake and do not remember which sweet hall machine you were logged into, open another window immediately and log into any sweet hall machine, then type "sweetfinger [username]" (replace [username] with your actual leland user name). It will give you the machine names you were on a few minutes ago. Then, log in to the appropriate machine and kill your processes there.



If you issued a query through JDBC that is taking a long time to execute and you want to kill it, you should stop your Java servlet. In most cases this will kill the query. You can also use the setQueryTimeout([time in seconds]) method on a statement object to stop queries that run too long. If you issued a query through CGI that is taking a long time to execute, normally the CGI service will kill it for you within 10 seconds. However, the above occasionally fails to work, and we do not know of any better way of killing runaway queries issued by JDBC or CGI (other than asking the administrator to kill them for you). That's why we ask you to always test your queries under sqlplus first. It is much easier to kill queries there.



In Pro*C, why do I get a strange "break outside loop or switch" error message? If you get an error message "break" outside loop or switch



when compiling your Pro*C program, chances that you have the following statement somewhere before a loop: EXEC SQL WHENEVER NOT FOUND DO break;



After the loop, you should insert the following statement: EXEC SQL WHENEVER NOT FOUND CONTINUE;



This would cancel the previous WHENEVER statement. If you do not do this, you may get the error message at subsequent SQL calls. This document was written originally by Jun Yang for CS145 in Spring, 1999. Additions by Antonios Hondroulis and Calvin Yang in Spring, 2002



The Behavior of NULL's in SQL



 •



NULL Basics



•



MAX()



•



a >= ALL()



•



EXCEPT



•



NOT IN



•



EXCEPT NULL



•



Database Implementation Compliance



•



Conclusion



Rarely is the full behavior of the NULL value in SQL taught or described in detail, and with good reason: Some of the SQL rules surrounding NULL can be surprising or unintuitive. Unfortunately, if you have deal with NULL in real databases, the results can be downright frustrating. The SQLite project, for example, uses trial and error to determine how a database behaves in the presence of NULL values. Fortunately, Date and Darwen's A Guide to the SQL Standard (fourth edition) [1] describes SQL's rules concerning NULL in good detail.



NULL Basics Intuitively, NULL approximately represents an unknown value. •



An arithmetic operation involving a NULL returns NULL. For example, NULL minus NULL yields NULL, not zero. [2]



•



A boolean comparison between two values involving a NULL returns neither true nor false, but unknown in SQL's three-valued logic. [3] For example, neither NULL equals NULL nor NULL not-equals NULL is true. Testing whether a value is NULL requires an expression such as IS NULL or IS NOT NULL.



•



An SQL query selects only values whose WHERE expression evaluates to true, and groups whose HAVING clause evaluates to true.



•



The aggregate COUNT(*) counts all NULL and non-NULL tuples; COUNT(attribute) counts all tuples whose attribute value is not NULL. Other SQL aggregate functions ignore NULL values in their computation. [4]



A Simple Case Here is what the SQL standard mandates for some operations involving sets and multisets. For a simple relation R CREATE TABLE R (a INTEGER);



the following queries attempt to reliably determine the maximum known value of the attribute a in the table R.



For brevity below, let •



empty refer to an empty result of no rows, and



 •



NULL (in the context of a table or result) refer to a table with one row holding a NULL value.



MAX() This is the obvious query: SELECT MAX(a) FROM R



•



If R is empty, the query must return NULL, not empty. [5]



•



If R is a one-row table holding NULL, Date and Darwen don't specifically declare what MAX() should return if its argument consists of exclusively NULL values.



•



If R is a table holding NULL and non-NULL integers, NULLs are ignored, and MAX() returns the maximum integer.



a >= ALL() This expression of the maximum seems consistent with mathematical logic, but fails completely in SQL: SELECT DISTINCT a FROM R WHERE a >= ALL (SELECT * FROM R)



•



If R is empty, the query returns empty. The >= ALL test is vacuously true with an empty subquery [6], but there is no value of a to exploit the test.



•



If R holds a NULL value, the query returns empty, because the test a >= ALL(...) returns unknown (not false!) for any NULL or maximum non-NULL integer value of a if the subquery includes a NULL value. [7]



EXCEPT This expression is one derivation of maximum as computed in relational algebra: subtract all the non-maximum values from the table R, leaving the maximal ones: (SELECT DISTINCT * FROM R) EXCEPT (SELECT R.a FROM R, R AS S WHERE R.a < S.a)



•



If R is empty, the query returns empty.



•



If R holds a NULL value, the query returns NULL, in addition to whatever maximal integer is present (if any). The lower subquery never includes NULL, so NULL is never subtracted from R.



NOT IN This expression is another writing of maximum as computed in relational algebra: find values not in the non-maximum values of R: SELECT DISTINCT * FROM R WHERE a NOT IN (SELECT R.a FROM R, R AS S WHERE R.a < S.a)



This writing turns out to be subtly different from the last one.



 •



If R is empty, the query returns empty.



•



If R holds one integer, and at least one NULL value, the query returns NULL, in addition to whatever maximal integer is present (if any). In this case, the subquery is always empty; the one available integer is compared only to NULL values, so do not participate in the subquery's result. NULL NOT IN (empty) is vacuously true as it is in mathematics [8], so NULL is selected as part of the result.



•



If R holds more than one integer, the query returns the maximal integer. In this case, the subquery includes at least one value. Now that the subquery is not empty, NULL NOT IN (nonempty result) evaluates to unknown (not false!), and is no longer selected as part of the result. As an aside, NULL NOT IN (nonempty result) returns unknown even if the nonempty result includes NULL. [9]



EXCEPT NULL Because it is somewhat awkward to have an expression for MAX return two rows whose values do not equal, the following expression adjusts the EXCEPT expression to exclude NULL from the answer: (SELECT DISTINCT * FROM R) EXCEPT (SELECT R.a FROM R, R AS S WHERE R.a < S.a OR R.a IS NULL)



•



If R is empty, the query returns empty.



•



If R holds NULL, the query returns the maximal integer, or empty if R has no integers. EXCEPT will remove NULL from the result if NULL appears in the bottom subquery, even though NULL is not equal to NULL. [10] Similarly, DISTINCT, UNION, and INTERSECT always returns at most one NULL.



Database Implementation Compliance



PostgreSQL 7.2.2 Where SQL mandates a behavior for a query above, PostgreSQL complies. •



MAX() of NULLs only: returns NULL (consistent with ignoring NULLs, then computing the MAX() of an empty remainder).



Oracle 9i Where SQL mandates a behavior for a query above, Oracle complies. •



MAX() of NULLs only: returns NULL (consistent with ignoring NULLs, then computing the MAX() of an empty remainder).



Conclusion No pair of the queries from the list above are equivalent when faced with NULLs in relational data, despite their conceptual similarity.



 The two implementations tested, PostgreSQL and Oracle, seem to comply with NULL behavior for the set and multiset operations tested here, even when such behavior is sometimes subtle or unintuitive. Consider the above a good reason to define away NULLs from relational schema whenever possible. Footnotes for this page



[1] C. J. Date and Hugh Darwen A Guide to the SQL Standard. Fourth edition, Addison-Wesley, Reading, Massachusetts, 1997. (ISBN 0-201-96426-0)



[2] page 236.



[3] page 239.



[4] page 236-237.



[5] page 237.



[6] page 176.



[7] page 244-245.



[8] page 176.



[9] page 244.



[10] page 249.



 This document was written originally by Wang Lam for Prof. Jennifer Widom's CS145 class, Spring 2003.



Ajayajayajayajay See More About:



• • • • •



database normalization 1nf 2nf 3nf bcnf



Sponsored Links SQL Backup ManagerSecure Online Backup for Businesses From MozyPro. Sign Up Now & Save!www.Mozy.com Master Data SearchMatchMaker brings fast fuzzy search to Master Data Management solutionswww.exorbyte.com The CAIA DesignationThe only global credential for alternative investment specialists.www.caia.org Database Ads Data Normalization Database Normalization ActionScript Guide Criminal Database RDBMS If you've been working with databases for a while, chances are you've heard the term normalization. Perhaps someone's asked you "Is that database normalized?" or "Is that in BCNF?" All too often, the reply is "Uh, yeah." Normalization is often brushed aside as a luxury that only academics have time for. However, knowing the principles of normalization and applying them to your daily database design tasks really isn't all that complicated and it could drastically improve the performance of your DBMS. In this article, we'll introduce the concept of normalization and take a brief look at the most common normal forms. Future articles will provide in-depth explorations of the normalization process.



What is Normalization?



Normalization is the process of efficiently organizing data in a database. There are two goals of the normalization process: eliminating redundant data (for example, storing the same data in more than one table) and ensuring data dependencies make sense (only storing related data in a table). Both of these are worthy goals as they reduce the amount of space a database consumes and ensure that data is logically stored.



The Normal Forms



The database community has developed a series of guidelines for ensuring that databases are normalized. These are referred to as normal forms and are numbered from one (the lowest form of normalization, referred to as first normal form or 1NF) through five (fifth normal form or 5NF). In practical applications, you'll often see 1NF, 2NF, and 3NF along with the occasional 4NF. Fifth normal form is very rarely seen and won't be discussed in this article.



 Before we begin our discussion of the normal forms, it's important to point out that they are guidelines and guidelines only. Occasionally, it becomes necessary to stray from them to meet practical business requirements. However, when variations take place, it's extremely important to evaluate any possible ramifications they could have on your system and account for possible inconsistencies. That said, let's explore the normal forms.



First Normal Form (1NF)



First normal form (1NF) sets the very basic rules for an organized database:



• •



Eliminate duplicative columns from the same table. Create separate tables for each group of related data and identify each row with a unique column or set of columns (the primary key).



Second Normal Form (2NF)



Second normal form (2NF) further addresses the concept of removing duplicative data: •



Meet all the requirements of the first normal form.



•



Remove subsets of data that apply to multiple rows of a table and place them in separate tables.



•



Create relationships between these new tables and their predecessors through the use of foreign keys.



Third Normal Form (3NF)



Third normal form (3NF) goes one large step further: •



Meet all the requirements of the second normal form.



•



Remove columns that are not dependent upon the primary key.



Fourth Normal Form (4NF)



Finally, fourth normal form (4NF) has one additional requirement: •



Meet all the requirements of the third normal form.



•



A relation is in 4NF if it has no multi-valued dependencies. Remember, these normalization guidelines are cumulative. For a database to be in 2NF, it must first fulfill all the criteria of a 1NF database. If you'd like to ensure your database is normalized, explore our other articles in this series



COLUMN: Definition: Database tables are composed of individual columns corresponding to the attributes of the object.



ROW: Definition: In a relational database, a row consists of one set of attributes (or one tuple) corresponding to one instance of the entity that a table schema describes. Also Known As: Record



PRIMARY KEY: Definition: The primary key of a relational table uniquely identifies each record in the table. It can either be a normal attribute that is guaranteed to be unique (such as Social Security Number in a table with no more than one record per person) or it can be generated by the DBMS (such as a globally unique identifier, or GUID, in Microsoft SQL Server). Primary keys may consist of a single attribute or multiple attributes in combination. Examples:



 Imagine we have a STUDENTS table that contains a record for each student at a university. The student's unique student ID number would be a good choice for a primary key in the STUDENTS table. The student's first and last name would not be a good choice, as there is always the chance that more than one student might have the same name. For more information on keys, read the article Database Keys. For more on selecting appropriate primary keys for a table, read Choosing a Primary Key. DATA BASE: As you may already know, databases use tables to organize information. (If you don’t have a basic familiarity with database concepts, read What is a Database?) Each table consists of a number of rows, each of which corresponds to a single database record. So, how do databases keep all of these records straight? It’s through the use of keys.



Primary Keys



The first type of key we’ll discuss is the primary key. Every database table should have one or more columns designated as the primary key. The value this key holds should be unique for each record in the database. For example, assume we have a table called Employees that contains personnel information for every employee in our firm. We’d need to select an appropriate primary key that would uniquely identify each employee. Your first thought might be to use the employee’s name. This wouldn’t work out very well because it’s conceivable that you’d hire two employees with the same name. A better choice might be to use a unique employee ID number that you assign to each employee when they’re hired. Some organizations choose to use Social Security Numbers (or similar government identifiers) for this task because each employee already has one and they’re guaranteed to be unique. However, the use of Social Security Numbers for this purpose is highly controversial due to privacy concerns. (If you work for a government organization, the use of a Social Security Number may even be illegal under the Privacy Act of 1974.) For this reason, most organizations have shifted to the use of unique identifiers (employee ID, student ID, etc.) that don’t share these privacy concerns. Once you decide upon a primary key and set it up in the database, the database management system will enforce the uniqueness of the key. If you try to insert a record into a table with a primary key that duplicates an existing record, the insert will fail. Most databases are also capable of generating their own primary keys. Microsoft Access, for example, may be configured to use the AutoNumber data type to assign a unique ID to each record in the table. While effective, this is a bad design practice because it leaves you with a meaningless value in each record in the table. Why not use that space to store something useful?



Foreign Keys



The other type of key that we’ll discuss in this course is the foreign key. These keys are used to create relationships between tables. Natural relationships exist between tables in most database structures. Returning to our employees database, let’s imagine that we wanted to add a table containing departmental information to the database. This new table might be called Departments and would contain a large amount of information about the department as a whole. We’d also want to include information about the employees in the department, but it would be redundant to have the same information in two tables (Employees and Departments). Instead, we can create a relationship between the two tables. Let’s assume that the Departments table uses the Department Name column as the primary key. To create a relationship between the two tables, we add a new column to the Employees table called Department. We then fill in the name of the department to which each employee belongs. We also inform the database management system that the Department column in the Employees table is a foreign key that references the Departments table. The database will then enforce referential integrity by ensuring that all of the values in the Departments column of the Employees table have corresponding entries in the Departments table. Note that there is no uniqueness constraint for a foreign key. We may (and most likely do!) have more than one employee belonging to a single department. Similarly, there’s no requirement that an entry in the Departments table have any corresponding entry in the Employees table. It is possible that we’d have a department with no employees



Candidate Key Definition: A candidate key is a combination of attributes that can be uniquely used to identify a database record without any extraneous data. ...



 CHOOSING APRIMARY KEY: Databases depend upon keys to store, sort and compare records. If you’ve been around databases for a while, you’ve probably heard about many different types of keys – primary keys, candidate keys, and foreign keys. When you create a new database table, you’re asked to select one primary key that will uniquely identify records stored in that table. The selection of a primary key is one of the most critical decisions you’ll make in the design of a new database. The most important constraint is that you must ensure that the selected key is unique. If it’s possible that two records (past, present, or future) may share the same value for an attribute, it’s a poor choice for a primary key. When evaluating this constraint, you should think creatively. Let’s consider a few examples that caused issues for real-world databases: •



ZIP Codes do not make good primary keys for a table of towns. If you’re making a a simple lookup table of cities, ZIP code seems to be a logical primary key. However, upon further investigation, you may realize that more than one town may share a ZIP code. For example, four cities in New Jersey (Neptune, Neptune City, Tinton Falls and Wall Township) all share the ZIP code 07753.



•



Social Security Numbers do not make good primary keys for a table of people for many reasons. First, most people consider their SSN private and don’t want it used in databases in the first place. Second, some people don’t have SSNs – especially those who have never set foot in the United States! Third, SSNs may be reused after an individual’s death. Finally, an individual may have more than one SSN over a lifetime – the Social Security Administration will issue a new number in cases of fraud or identity theft. So, what makes a good primary key? If you’re unable to find an obvious answer, turn to your database system for support. A best practice in database design is to use an internally generated primary key. The database management system can normally generate a unique identifier that has no meaning outside of the database system. For example, you might use the Microsoft Access AutoNumber datatype to create a field called RecordID. The AutoNumber datatype automatically increments the field each time you create a new record. While the number itself is meaningless, it provides a great way to reference an individual record in queries. Those are the basics on primary keys. Remember to choose carefully, as it’s difficult to change the primary key in a production table. For a more in-depth look at all the types of database keys, read Database Keys.



DATABASE RELATIONSHIP” Definition: A relationship exists between two database tables when one table has a foreign key that references the primary key of another table.