Information Systems and Databases

PORTFOLIO ELEMENT 1:

Element 1 of the assignment portfolio is an open book, 25 question multiple choice test, covering
• Entity Relationship modelling and normalisation
• SQL queries and data definition language
• Database security issues

PORTFOLIO ELEMENT 2:

Database Design and Implementation assignment

You are required to design and implement a database using Oracle APEX software, to address the requirements of the following case study:
Case study: South London University's SU clubs

South London University's student union wishes to set up a database to record membership of its various student clubs/societies.

Currently there are over 100 clubs offered by the student union (eg Basket Ball club, Islamic Society, Debating Society, Cooking Club, etc), each with a President who oversees the club. Presidents are students who have offered to take on this role, and they are responsible for coordinating the various events organised by their club/society. It is possible for a single student to be President for a number of different clubs, although this is quite rare.

Students are allowed to be members of more than one club, and the date that they join each club needs to be recorded, as well as their student number,name, gender and contact details. Clubs/societies also charge a small fee to join, and the date that the student pays the fee needs to be recorded.

Presidents organise a number of events for their clubs over the course of the academic year. Information on the events needs to include a description, scheduled date, location and budgeted cost of the event. Only students who are members of the club can sign up for these events. A list is kept of the members who attend, and they are also required to provide feedback on the event, ranging from 1 for not very good, to 5 for an excellent event. These ratings are then used to determine the popularity of the event and whether to repeat it in the future.

The Student Union is hopeful that the database can provide a number of useful queries/reports, for example:
• Total membership of each club
• Whether members have paid their club fee or not
• Frequency of attendance at events
• Popularity of events
• How much money the club has raised through its joining fees or events
• Financial standing of the club
.... Etc, etc.

They are looking to you to design and set up a database to meet these requirements.

You are required to design a database to cover the above case study, and then to implement it using Oracle APEX. You must submit a report using the submission link provided, consisting of the following:

1. A header page which includes your name, student number and your Oracle APEX account details (user name/tablespaceand password) which containsyourimplemented tables, data and reports/queries.

2. A list of any assumptions/clarifications you have made that have affected the design of your database.

3. An ER diagram (using ‘Crow's foot' notation) showing the final design of your database, which shows entities, attributes, primary and foreign keys, and named relationships.

4. Entity specification forms documenting all your entities (using the template provided).

5. The SQL scripts you used to create your tables (including all constraints) which should match your entity specification forms.

6. A list of the SQLcommands(INSERT INTO ..) used to insert test data into each of your tables, annotated to show that there is sufficient breadth and volume of data to adequately test your queries/reports.

7. Six functional requirements,producing reports/output that effectively demonstrate the scope and functionality of the system. For each requirement:
a) State clearly what the aim of the report/query is, and who would use it.
b) Provide a screenshot of the SQL SELECT statement and the output it produces.
The queries you choose should be substantial queries illustrating the breadth and potential of the implemented database, as well as your mastery of SQL.Each query should be numbered and saved in your database. Most queries should display data from more than one table, ie use a JOIN.

8. An example of a VIEW you have set up for security or convenience purposes. Providea screenshot of the SQL used to produce the view, and an explanation of its purpose.