G51DBS Database Systems 2006-2007

Assessed coursework

• cw1: Coursework on entity-relationship diagrams , deadline 14/03 at 16:00.
• Model solution and feedback for cw1.
• cw2: Coursework on SQL, creating tables - marked in the lab on 28/02.
  • How to create an Oracle account
  • Exercise on creating tables and inserting tuples
  • Model solution (published 7/03)
  • Feedback on exercise and common problems.
    There is not much to comment really. The main problem people had in doing it was forgetting semicolons or quotation marks or putting in extra commas. A common mistake was trying to declare a seven digit integer as int(7). There is no such datatype in SQL. All integers are the same length (38 bit). Another misconception was that if you have foreigh keys as in Enrolment, then the table will get populated automatically. It won't - and we don't want it deciding for us which student is taking which module anyway.
• cw3: Coursework on SQL queries - marked in the lab on 7/03. Clarification of cw3 added on 2/03 (to do with natural join and renaming a column).
• Model solution for cw3 (with comments in the file).
• cw4: Coursework on SQL queries - marked in the lab on 21/03
• Model solution for cw4 (with comments in the file).
• cw5: Coursework on normalising tables - deadline 27/04
• Model solution and feedback for cw5.
• cw6 (optional, worth 5 marks, but you cannot get more than 25 all together for the coursework) Coursework on views and privileges - deadline midnight 15 May (strict- no extensions), submission by email.
• Model solution for cw6.
• Answer to the additional relational algebra exercises from the revision lecture.

Labs

Lectures

• Lecture 1 (introduction)
  First paper about relational model: E.F. Codd. A Relational Model of Data for Large Shared Data Banks. Pdf file can be found here. (I should not be doing this and my apologies to ACM, but I could not find a reliable link on the web so saved the paper in my directory.)
• Lecture 2 (relational algebra) Contains informal exercise on relational algebra.
• Lecture 3 (relational data integrity)
• Lecture 4 (entity-relationship diagrams) First assessed coursework announced.
• Lecture 5 (SQL data definition) Second assessed coursework (first SQL exercise) announced
• Lecture 6 (SQL: modifying tables; SELECT)
• Lecture 7 (SQL: SELECT, various joins)
• Lecture 8 (SQL: more SELECT, aliases, subqueries)
• Lecture 9 (SQL: more SELECT)
• Lecture 10 (Missing information)
• Lecture 11 (Normalisation to 3NF)
• Lecture 12 (Relational algebra revision, lossless decomposition and BCNF)
• Lecture 13 Physical DB issues.
• Lecture 14 Transactions and recovery.
• Lecture 15 Concurrency control (locks).
• Lecture 16 Concurrency control (timestamping).
  Here is a shorter version of the slides without animations.
• Lecture 17 Security and integrity.
• Lecture 18 Other kinds of databases.
• Lecture 19 Revision.
  Answer to the additional relational algebra exercises from the revision lecture.
• Lecture 20 Java and SQL.