ERD, Database

Detailed Specification This is an individual coursework. You must design and develop a database system for a school admissions system for the Gondwana Education Service (GES). A large capital city, Gondwana, requires a system to handle admission to its secondary schools. Pupils transfer to secondary schools in Gondwana in September of the academic year in which they turn 11. The academic year is defined as starting on 1 September and ending on 31 August the following year. Gondwana's schools are run by the GES and it is responsible for allocating applicants to school places. The city is divided into ten Divisions (Division 1, Division 2, etc.) and parents may apply for their children to go to a school in any of the divisions. The parents may apply to up to three schools. For example, Mary Davis, living in Division 1, may apply for her daughter Elizabeth to go to any of the schools which take girls in any of the Divisions. There are several different types of school in Gondwana. For example, they may be selective or non-selective. The selective schools have an admissions test which must be passed before an applicant can be offered a place. There are schools for boys only, girls only and mixed schools. There are also Special schools, for those applicants with Special Educational Needs. Some schools have one or more specialisations, for example, Business, Sport, or Music. One of the criteria for admission to schools is how far away the applicant lives from the school. Applications have to be made by the 1 November of the year preceding the proposed admission. Applicants will be informed of the result of their application on the 1 March following their application. It is possible that some applicants will not be offered a place at any school at this stage, and if so, they may be put on a waiting list and given information on schools which still have places available. You are not required to implement the allocation of applicants to schools. However, your system must be capable of producing statistics on, for example, the number of applicants who were offered places at their first choice schools, what schools were most and least popular etc. The queries you produce should focus on these and other realistic uses of the system. Requirements 1. Using Chen’s or Crow’s Foot notation or any case tool to create an Entity Relationship (ER) model for your relational database. All entity types, their attributes and relationships must be clearly shown. You will also be required to show all cardinality and participation constraints. Any assumptions must be clearly stated and justified. 2. Map the ER model devised in part (1) into a relational data model. You may use EER features to support any enhanced features in your model if you wish. 3. Using appropriate SQL commands create a set of database tables in an RDBMS of your choice. You should also show all constraints used in the creation of the tables. 4. Your database must contain one view and one trigger 5. Create 10 sample queries that demonstrate the expressiveness of your database system. Note: ◦ 10 queries that demonstrate the usefulness of the database. Also state why each query would typically be used. The following must be demonstrated by at least one of your queries: ▪ A query that uses ORDER BY ▪ A query that uses INNER JOINS ▪ A query that uses aggregate functions ▪ A query that uses the GROUP BY and HAVING clauses ▪ A query that uses a sub-query as a relation ▪ A query that uses a sub-query in the WHERE clause ▪ A query stored as a VIEW ▪ A query that uses a VIEW as a relation ▪ A query that uses partial matching in the WHERE clause ▪ A query that uses a self-JOIN