G51DBS 2008-2009 Coursework 6

This is an optional catch-up coursework, worth 5 points, but you cannot get more than 25 points for the coursework in total.

Exercise

Create a file cw6.sql containing SQL statements which do the following.
  1. Drop table Enrolment and view ContactABC
  2. Create a table Enrolment which has two attributes, student's username which is 6 characters long, and module code which is also 6 characters long. No need to create keys.
  3. Put the following data into the table Enrolment: student abc09u is enrolled in G51PRG, G51DBS, G51IAI, student def09u is enrolled in G51PRG, G51FUN, G51WPS, G51MCS, student ghi09u is enrolled in G51IAI, G52IVG, G51FUN, G52ADS, student jkl09u is enrolled in G52CON and G51DBS, and students mno09u and pqr09u are enrolled in G51FUN, G52ADS and G53IDS.
  4. Create a view ContactABC which is just one column, containing usernames of students who take at least one of the modules that abc09u takes (so they could have come into contact with abc09u). ContactABC should be defined using a SELECT query which only mentions as a constant abc09u (there should be no fixed module codes in the query).
  5. Grant me (nza) privileges so that I can look at your Enrolment and ContactABC, but cannot insert/delete/update anything in them.

Submission

Submit to cw, coursework id 209. The deadline is 12 May at 23:59.