Case Study 1 - Adult Education Department

Essay

Case Study 1 - Adult Education Department
An Adult Education Department runs various courses during the daytime and evenings, and at different times of the year. For example, 'Spanish level 1' is offered on Monday mornings, Monday evenings or Wednesday evenings, and runs over 25 weeks from October to March. On the other hand, 'Introduction to Digging Up Your Ancestors' only runs for 8 weeks, but is offered on Tuesday or Wednesday evenings from October to December, January to March, and April to June, with an optional field week in August.
There is always a maximum number of places for each course offering, which is dependent on the individual tutor. For example, 'Spanish level 1' on Monday evenings may be limited to 20 places, but on Wednesday evenings the limit may be 25. Each course offering is only taken by one tutor, however, a tutor may take different courses, for example, 'French level 1' and 'Spanish level 2'. To guarantee enrolment, prospective students must pay the fee before the start of the first class. There is a special reduction for those unemployed. All applicants are kept on a register for subsequent mailshots.
-Given the above information:
(a) Develop an Entity-Relationship model to illustrate the logical database design.Produce a set of tables from your Entity-Relationship model, clearly identifying the primary keys. State any justifications or assumptions you make.
(b) Show that your data model supports the following transactions:
(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.