Scenario :

NSBM has 3 faculties; Computing Business and Engineering, and each faculty offers number of
degree programmes (awards). Each degree programme consists of number of levels(years) and
in each level consists of number of modules. Modules are sometimes shared among different
degree awards. Each module can have different assessment elements (final exam, incourse test,
assignments etc). Each year students are recruited to the degree programmes in two cohorts,
one in March and one in October.
NSBM’s academic calendar consists of 3 terms and modules are scheduled to run so that students
have approximately a regular workload across the year. Each term the cohorts of the degree
programmes are enrolled into a set of modules those scheduled to run in the term. Each module
is delivered by a single lecturer.

A system is required to support the daily operations of NSBM and generate necessary reports for
monitoring and management of the academic operations. The system should facilitate the
following reports.
a) Student attendance lists for module lessons (Lectures, Labs etc).
b) Module Mark sheets with individual marks for each assessment component and the final
grade.
c) Student examination eligibility list ( criteria is module attendance > = 70% )
d) Student transcript
e) Lecturer workload
f) Lecturer annual workload summary
Please clearly specify any assumptions you make.

Deliverables:

1. Report including,
• The problem scenario with any assumptions you made
• ER diagram
• Relational mapping
• Normalized tables
• Conclusion with a justification of your solution and limitations.
2. CD that includes
• a soft copy of your report
• an SQL script to generate the database with any constraints
• An SQL script to insert sample data for the tables
• An SQL script carrying the SQLs to generate all the required reports
Marking Criteria:
Following items carry a group mark.
1. Quality of the documentation – 10 Marks
2. Introduction to the problem and assumptions – 10 marks
Following items are assessed on individual student basis
3. ER Diagram - 20 Marks
4. Normalization - 10 Marks
5. SQLs to create the database and constraints - 20 Marks
6. SQLs to load the sample data – 10 Marks
7. SQLs to generate the reports – 20 Marks