4.2.1 Interacting with databases

• Databases


  Manage queries using specialised query languages
• Query languages


  Combine a very limited subset of English phrases with logical commands that allow items to be included or excluded from the query
• Structured Query Language (SQL)


  The most common query language, sometimes pronounced 'sequel'
• SQL was first released by IBM in 1974 and is now an international standard supported, at least in part, by most database applications</b>
• SQL's popularity


  Relatively small number of English-like commands allow users to create, access, modify and delete data without requiring any understanding of computer programming
• SQL was designed to allow business managers to interact with databases at a time when almost all computer users were highly trained specialists
• SQL query


  • SELECT * FROM College_Table WHERE Postcode LIKE 'AB6%';
• Explanation of SQL query commands


  • SELECT - to select some data from a database
  • * - means 'every column'
  • FROM College_Table - tells the query to look in College_Table
  • WHERE Postcode - another SQL instruction to look in the Postcode field of College_Table
  • LIKE 'AB6%' - to find items beginning 'AB6'
• The SQL query would look through the entirety of College_Table but only return information for Bobby Jones, Delia Edwards and Mary Evans since they are the only students in College_Table whose postcodes begin with 'AB6'
• Databases and query languages


  Underpin most websites
• Rather than storing web pages for every product in an online store or story in an online newspaper, a typical website stores components of the page – the text, pictures, prices, reviews, and so on – in one or more databases
• Following a request for a web page


  1. The site's server constructs the necessary query to retrieve relevant information from the database

  2. The data is then formatted into an HTML web page and sent to the user
• Users don't need to understand any query language, or even know that query languages exist; instead, queries are automatically created and edited as users follow links, click buttons, select menu items and submit searches
• Caching


  Storing the results of popular queries as formatted HTML pages and delivering the stored copy to users, rather than using a new query to generate a fresh copy of the page each time it is requested
• Caching can greatly improve the ability of a site to serve many simultaneous users because the site does not need to keep accessing the underlying database
• Cached pages are periodically replaced using queries to ensure they are always accurate and up-to-date
• Databases allow ecommerce sites to add new items or news sites to publish new stories at any time, and greatly reduce the risk of error when these changes are made
• Without a database, if a piece of data, such as a telephone number, is changed, every page using that data must be identified and corrected. With a database, only that entry in the database needs to be changed and every page referencing the record automatically reflects the change