130 likes | 254 Views
LBSC 690: Week 9 SQL, Web Forms. Discussion Points. Websites that are really databases Deep vs. Surface Web. HTML. SQL Query. CGI. Results. HTML. Putting the Pieces Together. Web Server. Browser. Database. Simple Examples. Let’s do some simple things together You are a publisher
E N D
Discussion Points • Websites that are really databases • Deep vs. Surface Web
HTML SQL Query CGI Results HTML Putting the Pieces Together Web Server Browser Database
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
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
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
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
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
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
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
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
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
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