Database systems and Administration
Scenario:
PEC offers several programs. Each program is supervised / chaired by a Head of the department (HoD) who is a lecturer. One HoD can lead only one program. There are several departments for each program. Many modules are offered by each department. Few departments are non-academic departments which do not offer any module. A department may offer several sessions of the same module. A session is taught by a lecturer at a given class room in a building. Every lecturer may have a maximum of three sessions. Though every department may have several lecturers, only one lecturer will be in the position of a chairman. A lecturer involves in teaching or administration. A Student may be enrolled in several sessions and the management wishes to keep track of enrolment date. There are numerous students in each department and the major field of study is offered by that department. Each student has been assigned an advisor who is a lecturer. Advisors counsel the students.
Propose a conceptual model after an in-depth research on university/college information management system’s operations and processes. In order to come up with an appropriate design/solution, you must study the requirements, issues and best practices in this problem context. You can make relevant assumptions required to formulate business rules, security, administration and modelling.
Based on the above information execute the following tasks. You may have to make additional assumptions to execute each of the given tasks.
Task 1:
Design an Entity Relationship Diagram (ERD) to model the above scenario. Identify the different entities and attributes of each entity. (A minimum of 8 entities need to be identified). Suggest and justify the choice of relationship and cardinality of the relationship. State the assumptions made by you (if any) to develop the ERD based on your research. Provide detailed analysis and justification for the selection of entities and attributes based on your research.
Task 2:
Derive a set of relational tables from the ER diagram (Task 1), using appropriate choices for the table attributes. List the integrity rules and business rules, which you would recommend to ensure the quality of data. Indicate all necessary key constraints. Critically analyze the constraints and business rules based on your study.
Task 3:
Outline the kinds of anomalies that can arise in the scenario given above by using un-normalized tables. Show how the table created in task 2 could be re-organized into separate tables to avoid anomalies (Decompose the table structures to a set of 3NF tables).
