LBSC 690: Week 9 SQL, Web Forms

1. LBSC 690: Week 9SQL, Web Forms

2. Discussion Points • Websites that are really databases • Deep vs. Surface Web

3. HTML SQL Query CGI Results HTML Putting the Pieces Together Web Server Browser Database

4. Simple Examples • Let’s do some simple things together • You are a publisher • Create a table that will provide book info to libraries and book stores • Table Book • Title, author, year published, ISBN, sale price • Create a table to describe books for the accounting/marketing department • Table Money • ISBN, copies sold, publishing cost

5. Bringing Data Together • We want to figure out the profit for each book • ISBN, sale price, publishing cost • Join tables Book and Money • On ISBN • Restrict/Select • ISBN, sale price, publishing cost

6. Example 2 • Get the titles of 10 of Stephen King’s books • Select • Title • Restrict • Where author = “Stephen King” • (Don’t worry about syntax - quotes, etc) • LIMIT 10

7. Example 3 • Get the titles of Stephen King’s 10 most recent books • Select • Title • Restrict • Where author = “Stephen King” • (Don’t worry about syntax - quotes, etc) • ORDER BY • year • LIMIT 10

8. Example 4 • Get the title and data on the profit for Stephen King’s 10 most recent books • Join tables Book and Money on ISBN • Select • Title, sale price, publishing cost • Restrict • where author=“Stephen King” • Order by year • Limit 10

9. Utility Service Database • Design a database to keep track of service calls for a utility company: • Customers call to report problems • Call center manages “tickets”, assigning workers to jobs • Must match the worker’s specialty and service location (zip code) • Must balance number of assignments (give new job to appropriate worker with the lowest work load) • Workers call in and ask where their next jobs are • In SQL, you can perform the following operations: • Count the number of rows in a result set • Sort the result set according to a field • Find the maximum and minimum value of a field

10. A Possible Answer: Tables • Customer:cid, contact name, phone number, address, zip • Worker:wid, name, phone number, location code, specialty • Ticket:tid, problem, cid, wid, time assigned, status • Area: zip, location code

11. One Possible Answer: Queries • Customer calls: I have a problem! • Join customer, area, and worker tables on zip and location code; restrict on cid and specialty → available workers • For each worker, count outstanding tickets • Insert a record in the ticket table, assigning job to worker to fewest assigned jobs • Worker calls: Where’s my next job? And what’s the problem? • Join ticket and customer tables on cid; restrict on status = “not yet completed”, sort rows by time assigned

12. Expand Your Database • Include information on customer billing • Include a bill, the date of the bill, due date, cost, and past due status • Service requests require a customer’s account be current • Worker calls to check on the status of a customer’s account

13. Table • Billing Table • cid, bill date, due date, amount due, past due status Query • Join ticket and billing tables on cid • Restrict by ticket number • Select past due status