Assessment Task: Using the scenario given in Appendix A and part of your ERD from milestone 1, analyse the requirements and then: a) Produce the table definitions (in SQL) for the following tables:...

Assessment Task:

Using the scenario given in Appendix A and part of your ERD from milestone 1, analyse the requirements and then:



a)    Produce the table definitions (in SQL) for the following tables:
    (Refer to the later queries in order to determine sensible attributes for each of

these tables.)


    Course Type
    Allocated Student
    Actual Course
    Student Allocation on Course
    Room
    Trainer



Only the above tables need to be created for this coursework.
Remember to define a primary key and foreign key(s) where necessary.

(Hint: Refer to ERD already given in milestone 1 for most of these)

Produce hard copy of each of these table definitions in SQL DDL.

Produce the Access relationship diagram generated by these table definitions.


b)      Populate your tables with sensible data.

(Remember you have to use this data in order to satisfy the following queries.)


    List the data contained in each of these tables.

(Use the datasheet view in Access)


    Produce hard copy of all these lists.


c)    Write the SQL statements necessary to satisfy the following queries. Remember to use sensible headings where appropriate:

    DO NOT use the QBE grid!


(Produce hard copy of the SQL together with the corresponding datasheet view of the resulting output)


(1)    Produce a list of currently running courses by course code then date order.
(4 marks)



(2)    Produce a list of courses that have ‘Introduction’ as part of their title.
(4 marks)



Assessment Task: Cont.
(3)    List the most expensive and the least expensive course that GBT provides. Only one query should be used for this task. Use sensible headings.
(6 marks)


(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)
(6 marks)


(5)    Produce a list of the current courses between two dates. Make sure you choose dates that provide MEANINGFUL output from your data.
(6 marks)


(6)    Produce a list of the number of current courses of each type between two dates. Your output should be similar to:


    Course Code        Number of actual courses running
    CIMW            2
    CAMW            5
    CIES            5
    CAES            3
(7 marks)


(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.

(Check your output here as this will test whether you have entered sensible data)
(7 marks)


(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 12/11/16)
(7 marks)


(9)     Produce a list of the current courses together with the total revenue that each course would have/ will  brought/bring in.

I.e the number of students on each course multiplied by the number of students taking that course.


E.g

    CourseCode    Date Run  CourseCost      No of Students    Revenue
CIMW        12/11/16    100            20           2000
CIMW        12/12/16    100            25           2500
CAMW                11/01/17    200            10           2000
Etc


(8 marks)


Up to 10 extra marks are available for answers to the above queries that are exceptional.