SBA 2014-2015 Paper One

SAINT JOHN’S COLLEGE

INFORMATION TECHNOLOGY

SCHOOL BASED ASSESSMENT (SBA)

2014-2015

Written by Ms. Marcelli Young

Overall Project

You and three of your friends have formed an event planning business, and are currently planning several upcoming events for the remainder of this year.  Each of you has a team of six individuals that you manage. Your team has been selected to plan an Exclusive New Year’s Eve Countdown Event; you have assigned the basic planning to other members of your team, and your focus is on the VIP guest list.  You are responsible to prepare a budget forecast, VIP invitation, interactive program, and database for the event.  You are to present a report to the team at your next meeting.

Paper One – Database

1.      Create a database with three tables labeled as VIP Information, Membership, and Event Record.

A.     Populate the VIP Information table with thirty records.  Each record should have the full data for a VIP guest that is invited to the Exclusive New Year’s Eve Countdown Event.  Data should include:

·         GuestID (unique code given to each member; must be 4 characters; example VP23)

·         First and Last Name (2 separate fields)

·         Home Street Address

·         Residing District

·         Contact Number (example: 639-9839  format to include the dash)

·         Age

·         Professional Title / Occupation

·         Work Address

·         Membership Type

o   Platinum, Diamond or Gold Membership (drop list)

·         Attending Current Event (yes/no field)

B.     Populate the Membership table with the following fields:

·         MembershipID

o   Platinum membership has the membership ID P001

o   Diamond membership has the membership ID D001

o   Gold membership has the membership ID G001

·         Membership Type

·         Membership Fee

o   Platinum $2500.00 per event

o   Diamond $1800.00 per event

o   Gold $1200.00 per event

C.     Populate the Event Record table with the following fields

·         GuestID (should be identical to the VIP Information table)

·         Number of events attended (maximum is 5)

·         5 fields -Event Name (you create an event name) (yes/no fields as check boxes)

example:  Poker Night        Spring Fling

Ensure to select appropriate data types for each field, and choose an appropriate field size where applicable.  No text field is to be left as the default field size.

§  Select appropriate primary key for each table.

§  Make a copy of the VIP Information Table as back up.  This back-up should not be used from this point forward.

D.     From the original VIP Information Table, delete the Age field and any 5 records.  (Show proof of deletion either by print screens or using a delete query) 

E.      After deletion, sort the table in an ascending order based on last name, then first name.  (Show proof of sorting by use of print screens)

2.      Create a relationship among the three tables and perform the following queries.

a.       Using all three tables, extract the data on all VIP guests attending the current event.  Create a new table with this data and label the table ATTENDING.  Save the query as MTQuery.

b.      Using two tables, extract all Platinum members that are attending the event; show the total fee they will pay.  Save as SQuery.

c.       Create a query to increase all Gold membership fees by $200.00.  Save as UQuery.

d.      Create a calculated field query to show the total amount of money spent by each VIP guest based on the number of events they attended.  Save the query as CQuery2.

3.      Generate a report to show a list of all VIP guests that will be attending the New Year’s Eve Countdown Event.  Include GuestID, names, contact number, membership type, and membershipID.  Group the guests by Membership Type.  Sort in an ascending order based on last name.  Show the number of guest that will attend, along with the total expected income from these guests based on membership fees.  The report should have a two-line title as follows:

New Year’s Eve Countdown

VIP Guests