G51DBS Database Systems 2008-2009

Labs

Coursework

• Coursework 1 on relational algebra and E/R modelling: cw1 . Submission deadline: 13 February at 16:00 to the School office.
• Model solution to cw1 By the way, solutions which removed an M:M relationship by creating an extra entity between Order and Product (similarly to an Enrolment entity we created between Students and Modules in lecture 4) are better, because they are easier to transform into database tables ( here is an example). However, since the question did not explicitly require an ER diagram without M:M relatioships leaving it as in the model solution was not penalised.
• Coursework 2 on SQL (creating tables): cw2 . Submission deadline: 20 February at 23:59 to cw system (coursework id is 183). Please try to get it marked in the lab on the 18th. Late submissions are allowed until 16:00 on the 27th of February but will have some penalty points subtracted.
  Model solution for cw2 . The main problems were getting used to sqlplus, getting passwords, and finding typos. The only real mistake I remember from the labs was that when a foreign key should match some combination of columns in the referenced table which uniquely identifies a tuple there, like (Cdate, Place) in Result uniquely identifies a tuple in Competition, some students declared two separate foreign keys from Result into Competition, one Cdate and another Place. Also, the order of dropping tables matters, the tables which are referenced (like Competition) will not be dropped while the referencing table (like Result) is still there. If you are not sure why, read up on referential integrity.
• Coursework 3 on SQL (select): cw3 . Submission deadline: 6 March at 23:59 to to cw system (coursework id is 189). Please try to get it marked in the lab on the 25th of February or the 4th of March.
• Model solution to cw3 . The most common mistake was to look up IDs of directors or movies and then hard-wire them into your SQL statement.
• Coursework 4 on SQL (select, group by): cw4 . Submission deadline: 20 March at 23:59 to to cw system (coursework id is 191).
• Model solution to cw4 .
• Coursework 5 on normalisation: cw5 . Submission deadline: 24 April (Friday) at 16:00, submitted to the School office.
• Model solution to cw5 .
• Optional coursework 6: cw6 Submission deadline: 15 May at 23:59 to cw coursework ID 209.

Lecture slides

• Lecture 1 (Introduction)
  6 slides to a page
  First paper about relational model: E.F. Codd. A Relational Model of Data for Large Shared Data Banks. Pdf file kindly made available by the ACM can be found here.
• Lecture 2 (Relational algebra)
  6 slides to a page
• 4/02/09: Lecture 3 (Relational data integrity)
  6 slides to a page
• 6/02/09: Lecture 4 (Entity-relationship modelling)
  6 slides to a page
  
• 11/02/09: Lecture 5 SQL (creating tables)
  6 slides to a page
• 18/02/09: Lecture 6 SQL (inserting and modifying data)
  6 slides to a page
• 20/02/09: Lecture 7 SQL (select, joins)
  6 slides to a page
• 25/02/09: Lecture 8 SQL (select, subqueries). Mistake on slide 21: the query should be

  SELECT *
  FROM Employee E1
  WHERE EXISTS (SELECT * FROM Employee E2
                WHERE E1.Name = E2.Manager);
  

  
  6 slides to a page
• 27/02/09: Lecture 9 SQL (select, aggregates). Mistake on slide 3: the columns to order by do not have to be in the result.
  6 slides to a page
• 04/03/09: Lecture 10 (nulls)
  6 slides to a page
• 06/03/09: Lecture 11 (normal forms up to 3NF)
  6 slides to a page
• 11/03/09: Lecture 12 (lossless decomposition, BCNF) Typo: on slide 26, decomposition should be in (Student, Time) and (Time, Course).
  6 slides to a page
• 13/03/09: Lecture 13 (physical design, indexing, optimisation)
  6 slides to a page
• 18/03/09: Lecture 14 (integrity and security)
  6 slides to a page
• 20/03/09 Lecture 15 (transactions and recovery)
  6 slides to a page
• 22/04/09 Lecture 16 (concurrency - locks)
  6 slides to a page
• 24/04/09 Lecture 17 (concurrency - deadlocks; timestamping)
  6 slides to a page
• 28/04/09 Lecture 18 ('modern' databases)
  6 slides to a page
• 30/04/09 Lecture 19 (revision)
  6 slides to a page
• 5/05/09 Lecture 19 (java and SQL - optional lecture)
  6 slides to a page
  Example.java