Management Information Systems Assignment Report

115. 107 Management Information Systems Assignment Report Contents Part 1 – ER Diagram2 Part 2 – Tables and Keys3 Part 3 – Queries4 Part 4: Spreadsheet filters and pivot tables7 List of Figures Figure 1 – ER diagram for event management system of hotel2 Figure 2 – Query 1 QBE for staff allocation4 Figure 3 – Query 1 output for staff allocation4 Figure 4 – Query 2 QBE for scheduled events beginning of October5 Figure 5 – Query 2 output for scheduled events beginning of October5 Figure 6 – Query 2 database report for scheduled events beginning of October6Figure 7 – Query 3 QBE for total number of registered participants6 Figure 8 – Query 3 output for total number of registered participants7 Figure 9 – Spreadsheet data with no filers or conditional formatting applied7 Figure 10 – Spreadsheet with the â€Å"show only events commencing after 15th October filter applied7 Figure 11 – Spreadshe et with the ‘show only events commencing after 1st November as underlined' conditional formatting applied8 Figure 12 – Pivot table8 Figure 13 – Pivot table as a bar chart8 List of TablesTable 1 – Attributes for ER diagram for event management system of hotel2 Table 2 – Attributes for ER diagram for event management system of hotel3 Part 1 – ER Diagram StaffMember Venue Event Registration Attendee Reserved for Manages Accept Makes Applies to 1.. * 1.. * 1 1 1 0.. * 0.. * 0.. * 0.. * Payment Accept 0.. * 0.. * 1 Gains entry Client System Presents 0.. * Record Details 1 0.. * Inputs 1.. * 1.. * Figure [ 1 ] – ER diagram for event management system of hotel Table [ 1 ] – Attributes for ER diagram for event management system of hotel Entity Type| Attributes| Key attribute|Venue| venueID, venueCapacity, venueDescription| venueID| Event| eventID, eventName, eventType, startDate, endDate, startTime, endTime, availableSpaces, eventFee| e ventide| StaffMember| staffID, firstName, lastName, jobTitle | staffID| Registration | registrationID, registrationDate, eventID | registrationID| Attendee| attendeeID, firstName, lastName, title, companyName, contactAddress, phoneNo | attendeeID| Payment| paymentAmount, paymentDate, paymentType, paymentCredit, creditcardID, creditcardName, creditcardExpiry| paymentID| Client| clientName, companyName, contactAddress, contactPhone, clientIndustry, clientNeeds| clientID| System| systemClient, systemAttendee, systemVenues, systemInvoice, | systemID| Part 2 – Tables and Keys Table [ 2 ] – Attributes for ER diagram for event management system of hotel Table| Column| Primary Key| Comments|VenueTbl| venueID, venueCapacity, venueDescription| venueID| | EventTbl| eventID, eventName, eventType, startDate, endDate, startTime, endTime, availableSpaces, eventFee, staffID| eventID| staffID is a foreign key into StaffMemberTbl. | StaffMemberTbl| staffID, firstName, lastName, jobTitle | staffID| | RegistrationTbl| registrationID, registrationDate ,eventID, attendeeID| registrationID| eventID is a foreign key into EventTbl while attendeeID is a foreign key into AttendeeTbl| AttendeeTbl| attendeeID, firstName, lastName, title, companyName, contactAddress, phoneNo, eventID, registrationID| attendeeID| RegistrationID is a foreign key into RegistrationTbl while event ID is a foreign key into EventTbl. ReservedforTbl| eventID, venueID, attendeeID, | reserveID| eventID is a foreign key into EventTbl while attendeeID is a foreign key into AttendeeTbl| Part 3 – Queries Query 1 -StaffAllocation Figure [ 2 ] – Query 1 QBE for staff allocation Figure [ 3 ] – Query 1 output for staff allocation Query 2 – YearEndSchedule Figure [ 4 ] – Query 2 QBE for scheduled events beginning of October Figure [ 5 ] – Query 2 output for scheduled events beginning of October **For unknown reasons, event 9, 16 & 17 is not listed in the search result e ven when the date falls within the range as set out in the criteria. I have attempted to modify the criteria selection to cover between the date range of 30/09/2011 – 01/01/2050 etc but it has still failed to come up. Query 2 – Year End ReportFigure [ 6 ] – Query 2 database report for scheduled events beginning of October Query 3 – Registration Numbers Figure [ 7 ] – Query 3 QBE for total number of registered participants Figure [ 8 ] – Query 3 output for total number of registered participants Part 4: Spreadsheet filters and pivot tables Figure [ 9 ] – Spreadsheet data with no filTers or conditional formatting applied Figure [ 10 ] – Spreadsheet with the show only events commencing after 15th October filter applied Figure [ 11 ] – Spreadsheet with the ‘show only events commencing after 1st November as underlined' conditional formatting applied Figure [ 12 ] – Pivot table Figure [ 13 ] – Pivot table a s a bar chart