Exam #3 ISM 4212 fall 2005 due via email ( wleighwleigh@yahoo.com ) by midnight 9/21. You may begin working on this exam as soon as it is posted on the web site.
Build an application in ACCESS with the following database structure:
golfer ( gid, gname )
teetime ( tttid, ttdatetime, ttmaxnumberofgolfers )
schedule ( sid, sgid, stttid, sttdatetime )
score ( scid, scsid, score )
[a primary key with 2 attributes is created like "constraint x02 primary key (tttid, ttdatetime)"]
[a foreign key with 2 attributes is created like "constraint x02 foreign key (stttid, sttdatetime) references teetime"]
[foreign keys must have the same number of attributes, with the same data types, as the primary keys that they reference.]
[tttid is a foreign key to tournament. sgid is a foreign key referencing golfer. stttid and sttdatetime together are a foreign key referencing teetime. scsid is foreign key referencing schedule. ]
Golfers sign up for times to tee off in a golf tournament. A maximum number of golfers is allowed at each tee time. When the golfers have completed playing that day they turn in their scores. A tournament usually has 4 rounds, that is, the golfers sign up for tee times and play 4 times.
Provide forms for data entry into each table. Have dropdown combo boxes for all foreign key attributes. Provide a main menu form.
Provide a multi-table form to enter all of the schedule rows for a golfer. Call this form "multitable scheduling form".
Provide reports: 1. Golfers scheduled by tee time for a given date. 2. Scores by golfer within tournament. 3. Average scores by golfer (use the average function in ACCESS SQL with group by) within tournament. 4. Average score by golfer by date. 5. All tee times which have more golfers signed up than allowed by the tmaxnumberofgolfers value (use the count function with group by and having ).
Include golfer name whenever golfer data appears on a report.
Build reports with SQL select statements as input to the report writer.
Use create table statements for all tables. Define primary keys and foreign keys in the SQL.
Save all SQL statements (create table and select) as queries.
Provide at least 10 rows of test data for each table.
Use captions to have user-friendly field names on all reports and forms.
Email the ACCESS mdb file to wleighwleigh@yahoo.com by midnight 9/21. Put "ISM 4212 Exam 3" and your name in the subject line of the email. (Use of the Yahoo mail account should avoid security screening problems with the university.)