Show that your data model supports the following transactions:

Question

(i) Add a new course to the database, prior to it being offered on any particular day or from any particular date.
(ii) Enrol a new student on the ‘German level 2’ course that runs on Monday evenings commencing October 10 1994.

Bearing in mind the above transactions, explain how the physical database design might be influenced describing what changes you might make etc, and how the application (and transactions) would be affected. Your comments can apply to both computerized procedures and manual procedures.

Answer 1

The transactions can be shown in various ways. For example, a check could be made first to ensure no details have been entered. If separate tables exist for both Course and Offering, simply insert the new details into Course (identifier, name, and level). If the tables are combined, this table is simply checked for the existence of any records, and then the new details are inserted, leaving the rest of the attributes null.

For the second transaction, check whether Student details exist, and insert if necessary. Find the identifier for the Course, then repeatedly check Offering to find the correct Offering identifier for the day/time. Repeat read the Registration records, and if space, insert a new Registration record.

Alterations may be made to the physical design, such as adding a derived field to Offering indicating how many students are enrolled – alters procedures. To aid searching, set up secondary indexes on required fields.