IS1S466-Information Management, Assurance and Security
Assessment Task:
Using the given ERD, analyse the requirements and then:
a.Produce the table definitions (in SQL) for the following tables:
1.Course Type
2.Allocated Student
3.Current Course
4.Allocation
5.Room
6.Trainer
Remember to define a primary key and foreign key(s) where necessary.
b.Populate your tables with sensible data.
c.Write the SQL statements necessary to satisfy the following queries. Remember to use sensible headings where appropriate:
1.Produce a list of currently running courses by course code then date order.
2.Produce a list of courses that have ‘Introduction’ as part of their title.
3.List the most expensive and the least expensive course that GBT provides. Use sensible headings.
4.Produce a list of the number of students on each of the currently allocated courses. (This should be calculated by the query NOT held as a piece of data)
5.Produce a list of the current courses between two dates. Make sure you choose dates that provide MEANINGFUL output from your data
6.Produce a list of the number of current courses of each type between two dates. Your output should be similar to:
Course CodeNumber of actual courses running
CIMW 2
CAMW 5
CIES 5
CAES 5
7.Produce a list of the rooms (include type of room: e.g av facilities or not) and trainers (by name) allocated to each course for each week.
8.Produce a list of students (include student name) currently allocated to an actual course (use your data to select an actual course – E.g CIMW starting on
Learning Outcomes
1) To demonstrate a practical understanding of the design and implementation of information systems.
2) To demonstrate the ability to recognise any risks or safety aspects that may be involved in the operation of computing equipment within a given context.
