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.
- Drop table Enrolment and view ContactABC
- 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.
- 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.
- 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).
- 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.