PLEASE ANSWER THIS QUESTION DRAWING AN ERD DIAGRAM
Mini Case: South Bank University SBU is a large institution with several campuses. Each campus has a different name, address, distance to the city centre and the only bus running to the campus. Each campus has one club. The name of the club, the building in which the club is located, the phone number of the club and the multiple sports which club offers, should all be recorded. The University consists of several faculties, such as the Art Faculty, the Science Faculty, and so on. Each faculty has a name, dean and building. A faculty may be divided into several schools, for example, the Science Faculty has a School of Physics and a School of Chemistry. Each school belongs to one faculty only and is located on just one campus, but one campus maybe the location of many schools. Every school has a name and a building assigned to. Each school offers different programmes, and each programme can be offered by only one school. Each programme has a unique code, title, level and duration. Each programme comprises several courses, different programmes have different courses. Each course has a unique code and course title. Some courses may have one or more prerequisite courses and one course can be the prerequisite course of some other courses. Each of the students is enrolled in a single programme of study which involves a fixed core of courses specific to that programme as well as several electives taken from other programmes. Students work on courses and are awarded a grade in any course if he/she passes the course. Otherwise, the student must re-take the failed course. The system needs to record the year and term in which the course was taken, and the grade awarded to the student. Every student has a unique ID. The system also keeps the student's name, birthday and the year he/she enrolled in the course. The school employs lecturers to teach the students. A lecturer is allowed to work for one school only. Each lecturer is assigned an ID which is unique across the whole university. The system keeps the lecturer's name, title and the office room. A supervisor maybe in charge of several lecturers, but a lecturer, however reports to only one supervisor. A lecturer can teach many different courses. A course may also have been taught by many different lecturers. The university is operated by committees. Each faculty must have a number of committees with the same titles across the university, such as the Faculty Executive, the Post Graduate Studies Committee, the Health and Sanity Committee, and so on. The committees meet regularly, such as weekly or monthly. The frequency is determined by the faculty involved. A committee's members are all lecturers. A lecturer may be a member of several committees. In this part, you are asked to design a database to support a university system that provides a platform for students to enrol in a single programme of study and complete a number of fixed core of courses specific to that programme as well as a number of electives taken from other programmes. This design will be the basis for part of the forthcoming Assignment 2. The major business requirements are summarised below in the Mini Case: University of Western Sydney. You are asked to develop a detailed Entity-Relationship model for this mini case. Your ER model should consist of a detailed ER diagram integrated with itemised discussions on the features of the entities and relationships and all the assumptions you made. The ER diagram and the accompanying document should identify keys, constraints, entity types, relationship types, specialisation/generalisation, etc. The ER diagram should in general have more than 5 entity types and no more than 15 . This means that students will have to selectively identify those most important entity types and relationship types for their work and for the fulfillment of the key business functionalities. More concretely, your deliverables should include: i. A list of supplementary business rules or assumptions that underpin your particular database design. When pertinent, link them to the part of your database design with short comments. (1 mark) ii. A detailed ER diagram for the model, highlighting primary keys, multiplicity constraints, generalisation/specialisation, important attributes, and other pertinent details. a. You must use the same notation scheme for the ER diagram as the textbook, and the ER diagram should be strictly in the sense the textbook uses. b. The ER diagram should include, among others, representative attributes for all entity types, proper subclassing, and correct participation multiplicities for the relationship types. It should be meaningful and well designed and should also include all relevant and necessary aspects. (2 marks)
