Designing and Implementing a Database System for Midlands Technological University Students Union (M

Read the following case study and then carry out the required tasks.

Midlands Technological University (MTU) is a 3rd level provider of Level 6,7,8 and 9 QQI recognised programs serving principally the counties of the midlands area of Ireland. MTU Students Union (MTU SU) is the representative body for all students (full time and part time ) in MTU. All registered students are automatically registered as members of the Students Union (SU). MTU has a student enrolment of approximately 9000 students.

MTU SU is entirely independent of MTU but operated from offices based in the college. MTU SU’s primary focus is to provide support, information and advice on a wide range of issues to students. The class rep system is important to the day-to-day running of the students union and class reps provide a direct link between the student body and the students union executive committee.

he Class Representative governing council consists of individual class reps elected by each year of a course. For example, Business Information Systems course will have a class rep for each year of the course e.g. 1st year class rep, 2nd year class rep, 3rd year class rep). Class reps meet on a monthly basis to make representation and lobby on behalf of the class. Class reps are elected informally in class at the start of each academic year. 

The SU President has recruited you as a team of consultants to design and implement a database system which will help them better utilise their resources. This is part of a large project to cloud-enable many aspects of the SU activities and the SU President hopes it will provide accurate reporting facilities, which at the moment is very difficult to achieve.

The requirements gathering phase of the project has already been completed and the following requirements have been identified for the new database:

?Keep track of Class Reps (who they are, contact details, class they are representing, date of class rep training, academic year etc…).

?Maintain a record of the contact details for all students in the college.

?Maintain a record of clubs and societies in the college.

?Record which students have registered/signed up for each club/society.

Record student queries and resolutions (where appropriate)

The following is a sample of the requirements which have been identified for the new database system.  Produce 5 reports as follows:

1.Provide a list of all class reps

2.Identify any classes without a class rep

3.Produce a monthly calendar of events for students

4.Produce a report showing the numbers of students signed up to each club/society.

5.Produce a report to identify the most common types of enquiries dealt with by the SU (with a view to producing an FAQ section on their website).

Each student is required to produce an Entity Relationship Diagram for the proposed database system. Each ERD should be clear and you must state any assumptions made. ERD’s must be drawn using the crows feet notation. You may draw the diagram using any software you are familiar with (Visio or Draw.io).

A.The ERD must include:

a.List of Entities and Attributes (to include Primary keys for each entity).  For each entity you are required to identify at least five suitable attributes and data types.

b.Binary Relationships and written description of multiplicity (participation and cardinality)

c.Conceptual Entity Relationship Diagram

d.Logical Entity Relationship Diagram to include Foreign keys and associate tables (junction tables)

e.Diagrams must be drawn using the Crows Feet notation shown in class and produced using Visio or Draw.io.

B.Using the ERD developed in Part 1, each student must develop a corresponding database using MS Access.

a.Creation of suitable tables with well-chosen primary and foreign keys.

b.Implementation of required relationships, as per your ERD. 

c.Populate tables with at least 5 records each.C.Provide the following details in a MS Word document.

C.Provide the following details in a MS Word document.

a.A suitable cover page, table of contents, headers/footers etc...

b.A copy of the ERD produced and clearly state any assumptions made.

c.A copy of the logical diagram.

d.A brief report of what the newly designed system will offer the SU in terms of functionality, meeting the requirements, additional features. Basically, explain to the SU why they should choose your database design.

e.Screenshot of the tables created using MS Access.

Screenshots of the queries, with a brief explanation as to what each query was seeking to achieve

d.Create 5 queries and 5 reports (see above) which would meet the requirements of MTU SU.