DOC PREVIEW
Gordon CPS 211 - Database Querying and Updating

This preview shows page 1-2-3-4-5 out of 14 pages.

Save
View full document
View full document
Premium Document
Do you want full access? Go Premium and unlock all 14 pages.
Access to all documents
Download any document
Ad free experience
View full document
Premium Document
Do you want full access? Go Premium and unlock all 14 pages.
Access to all documents
Download any document
Ad free experience
View full document
Premium Document
Do you want full access? Go Premium and unlock all 14 pages.
Access to all documents
Download any document
Ad free experience
View full document
Premium Document
Do you want full access? Go Premium and unlock all 14 pages.
Access to all documents
Download any document
Ad free experience
View full document
Premium Document
Do you want full access? Go Premium and unlock all 14 pages.
Access to all documents
Download any document
Ad free experience
Premium Document
Do you want full access? Go Premium and unlock all 14 pages.
Access to all documents
Download any document
Ad free experience

Unformatted text preview:

CS211 Lecture: Database Querying and Updatinglast revised 10/30/2006Objectives:1. To introduce the SQL select statement2. To introduce the SQL insert, update, and delete statements Materials: 1. LIBRARY database and database to be used in lab (CS211) for on-line demos2. Projectable of LIBRARY database schema diagram3. Sample LIBRARY query handout4. Database schema diagram for Database query lab - handout5. Listing of contents of tables for database query lab6. SQL Syntax handout7. SQL documentation linked from course web pageI. IntroductionA. We have seen how entities and relationships can be represented by using the relational data model, in which information is stored in tables.1. Each table has a primary key, which is a set of attributes such that no two rows in the table have the same value.2. A table may represent either an entity or a relationship.a) For an entity, the table includes the entity’s primary key and other attributes.b) For a relationship, the table includes the primary keys of the entities being related (called foreign keys) plus any attributes of the relationship itself. The foreign keys, together, constitute the primary key of the table.B. One of the major strengths of the relational data model is that it supports ad-hoc operations - the ability to access information in the database in a simple way, without having to write a special program to do so. These operations are of two general kinds:1. Queries - access information without altering it2. Updates - add, delete, or modify informationC. We now have to consider how to actually perform queries and updates. To do this, we will make use of a query language.11. Over the years, a number of different query languages have been developed for use with relational databases. They fall into two broad categories:a) Formal languages that use mathematical notation, and are most useful for theoretical study.b) Commercial languages used in actual systems.c) We will learn Structured Query Language (SQL) - a commercial query language that has been standardized by ANSI, which is supported by many relational DBMS products, and which is used by Java’s JDBC (Java Database Connectivity) facility.2. We will also look at SQL facilities for updating a database. D. For our examples, we will utilize a simple database for a very small library, realizing the following schema diagram:BorrowerborrowerIDlastNamefirstNameCheckedOutcallNoborrowerIDdateDueBookcallNotitlePROJECT(Assume the library is so small that it has at most one copy of any book - hence callNo suffices as the primary key for Book.) In the case of the CheckedOut relation, the foreign keys of the two tables being related are borrowerID, callNo. However, because any given book can only be checked out to one borrower at a time, we use just callNo as the primary key in this case.DISTRIBUTE QUERY HANDOUTDEMO: mysql -h jonah.cs.gordon.edu -puse LIBRARY;show tables;2II. Querying a DatabaseA. Some simple examples1. One possible query is one that asks for a particular row of some table.Example: "What is the book whose call number is RZ12.905?"a) SQL formulation .select * from Book where callNo = 'RZ12.905';The keyword select is used for all queries, * specifies all columns of the selected row(s), and where specifies the condition the row(s) must meet. Note also that all SQL queries end with a semicolon, that comparison for equality uses = (not == as in Java) and that strings are enclosed in single quotes. It is good form to put each clause on a separate line, indented with respect to the first line - not required by the language, but facilitates reading.b) Result (Demo)21873 Fire Hydrants I Have Known Dog RZ12.9052. It is also possible to formulate a similar query that produces several rows as its result.Example: "What books are written by Dog?"a) SQL formulation:select * from Book where author = 'Dog';b) Result (Demo):21873 Fire-hydrants I have known Dog RZ12.90534938 21 ways to cook a cat Dog LM925.043. The above queries produce all columns from a one or more rows from the table. Sometimes, we want one or more columns from all rows in a table.Example: "List the names of all borrowers"3a) SQL formulation::select lastName, firstName from Borrower;the keyword select is still used, but we explicitly list the columns we want instead of using *, and we don't have a where clause.b) Result (Demo):Aardvark AnthonyCat CharleneDog DonnaFox FrederickGopher GertrudeZebra Zelda4. The operations of selecting both specific columns and specific rows can be combined in a single query.Example: "What is the title of the book whose call number is QA76.093?"a) SQL formulationselect title from Book where callNo = 'QA76.093';Note that both an explicit column list and a where clause are needed.b) Result (Demo)Wenham Zoo Guide5. The full power of relational database system comes in when we need to combine information from two or more tables. We will look at a couple of examples now, but will discuss this extensively in a bit.Example: When is/are the book(s) Charlene Cat has checked out due?"What tables do we need information from in order to answer this question?ASKBorrower - since the name only appears there - and CheckedOut - since the date due appears only there. The two tables are related by the common key borrowerID, which is the primary key of one and a foreign key in the other.4a) SQL formulation:select dateDue from Borrower natural join CheckedOut where lastName = 'Cat' and firstName = 'Charlene';(Note that SQL uses the word and, not && as in Java.)The "natural join" operation specifies that a new table is to be constructed by taking the Cartesian product of the two tables and then keeping only those rows which agree on their common attribute (called the join attribute): borrowerID. The fact that borrowerID is the join attribute is implicit in the fact that it has the same name in both tables.b) Result (Demo)2001-2-156. Joins can be used to combine information from more than two tables.Example (admittedly a bit contrived):"What borrowers have books checked out whose author has the same last name as they do?"What tables are required to answer this?ASKAll three!a) SQL formulation:select lastName, firstName from Borrower natural join CheckedOut natural join Book where lastName = author;b) Result (Demo)Dog DonnaDog Donnac) Note that the result is to produce two identical rows.. However, there is variant of the


View Full Document

Gordon CPS 211 - Database Querying and Updating

Download Database Querying and Updating
Our administrator received your request to download this document. We will send you the file to your email shortly.
Loading Unlocking...
Login

Join to view Database Querying and Updating and access 3M+ class-specific study document.

or
We will never post anything without your permission.
Don't have an account?
Sign Up

Join to view Database Querying and Updating 2 2 and access 3M+ class-specific study document.

or

By creating an account you agree to our Privacy Policy and Terms Of Use

Already a member?