# Lecture 8: SQL Queries

Today’s lecture was the final one on Structure Data and covered a range of database topics: ACID properties for transactions; the NoSQL movement; nested SQL queries, set operations, and aggregate queries; ultimate physical limits to computation; the wonders of nature captured in SkyServer; and the idea of doing scientific research and experiments from inside the database.
Continue reading Lecture 8: SQL Queries

# Tutorial 2 Solutions and Tutorial 3 Questions

I have posted solutions and notes on Tutorial 2 to the course web pages. Click on the “Notes” field in the table of tutorials.

You’ll also find there a new set of tutorial exercises for next week: these ones are working with relational algebra and with tuple-relational calculus.

# Lecture 7: SQL

Today’s lecture introduced the basic structure and format of SQL queries: SELECT … FROM … WHERE …. That’s enough to write a huge range of queries, from single summary statistics to large integrated views that bring together multiple tables.
Continue reading Lecture 7: SQL

# Lecture 6: Tuple Relational Calculus

Another lecture, another language. This one is the Tuple Relational Calculus for specifying queries that describe information to be extracted from the linked tables of a relational database. There’s a separation of roles here: the tuple relational calculus is good for succinctly stating what we want to find out; while relational algebra from the last lecture describes how to combine and sift tables to extract that information from the data. We distinguish what information we want from how to compute it.
Continue reading Lecture 6: Tuple Relational Calculus

# Lecture 5: Relational Algebra

This morning’s lecture presented a mathematical language for slicing and dicing the structured tables of the relational model: selection, projection, renaming; union, intersection, difference; cross product, join, equijoin and natural join. A key feature of this relational algebra is that just six of these operations are enough to capture an extremely wide range of queries and transformations of data. Database implementors work hard to build highly efficient engines to carry out these operations, which can then support many different kinds of user application.
Continue reading Lecture 5: Relational Algebra

# Specialised ER Tools for the Tutorial

Fabian Peternek, the course Teaching Assistant, has built specialised ER toolsets for two of the drawing tools recommended in the last question on this week’s tutorial exercises.
Continue reading Specialised ER Tools for the Tutorial

# Lecture 4: From ER Diagrams to Relational Models

Today’s lecture reviewed the high-level conceptual language of ER diagrams and the more concrete structures of the relational model; followed by some recipes for translating from the first into the second. This isn’t always an exact match, and for any particular ER diagram we might go back to its original scenario description to decide how to best represent it as a relational model. Even so, this kind of step-by-step staging towards a fully formal representation is an effective route to capturing the subtleties of real-world systems.
Continue reading Lecture 4: From ER Diagrams to Relational Models

# Tutorial Exercises

I have uploaded the first set of tutorial exercises. Click on the thumbnail to the right, or go to the tutorial web page to download them. Please read the information box on the opening page before you start work on these.

I have a large number of requests from students to change tutorial groups. That’s fine, but it’s taking a while to plan a set of revisions that will avoid everyone’s clashes and still keep the group sizes reasonable. Please be patient: you should all be in appropriate groups as requested before next week.

# Lecture 3: The Relational Model

This first part of morning’s lecture picked up from Lecture 2 to complete an overview of Entity-Relationship modelling and ER-diagrams. The remainder set out the basic elements of the Relational Model for structured data. Where ER diagrams aim to give a conceptual language for describing things as they are, and have applications well outside databases for general organisation and management, the relational model is explicitly intended as a mathematically precise scheme for the computer-assisted building and querying of large datasets.
Continue reading Lecture 3: The Relational Model