CCA-M-DBS Database Systems
Referred Coursework July 2011
Faculty of BusinessReferred/Deferred Coursework 2010/11
/CCA-M-DBS Database Systems
UNIT LEADER: George Ubakanma ()
COURSE(S):
MSc Information Technology
MSc Enterprise Computing
MSc Internet & Database Systems
If not available please contact the course administrator
PG courses: Yasmin Nessa ()
who will attempt to locate an academic member of staff who can advise.
Please note the non-availability of advice is not a valid reason for non-completion of the coursework as it repeats knowledge and skills delivered during the unit.
The coursework must be printed and submitted in a suitable folder accompanied by a CD or DISC containing electronic copies of all documents, with a standard coursework submission front sheet to:
Business Faculty Office LR105,
marked for the attention of the unit leader shown above.
For Submission Deadline Date see your results letter or visit
the faculty student intranet http://bus.lsbu.ac.uk/students/
EXAMPLE CASE STUDY
LSBU HEALTH CLUB
The health club needs a database to manage information about club activities. You are required to design and build an object-relational database prototype for the health club.
The health club has members and each member has an account with the health club. Each member account has an account number, the date the account was opened and a discount percentage which is applied to all fees charged to the membership account. Each acount belongs to one or more members, a member may have several accounts.. The club maintains information about each member, such as the member id, first name, last name, date of birth, monthly fee, address and phone number.
The health club offers several classes, such as yoga, step aerobics, and spin classes. The database needs to maintain information about each type of class, such as the class id, class name, class price and class description. Each class is offered several times a week. For example, yoga might be offered at 7a.m. on Mondays, midday on Wednesdays, and 5p.m. on Fridays, the class price may be 10 pounds per member. The database needs to maintain the time and day schedule for each class. The schedule should indicate the instructor for each offering of the class.
To monitor member activity, the health club also needs to maintain attendance for each date on which the class occurs. For example, the club would like to generate an attendance list (members attendence) for the 6 a.m. Monday yoga class when it was taught on Monday, July 4, and also on Monday, July 11. Members of the health club can attend any of the classes offered by the club.
The health club needs to maintain information about instructors of classes. The information includes an instructor identifier, first name, last name, and classes that an instructor is scheduled to teach.
In order to keep the storage od data efficient , any common information (characteristics) that instuctors or members have in common like: first name, lastname, date of birth, address and phone number should be kept in a common data structure. The differing (specific) details such members having a membership id and a renewal date... or instructors having a staff id, job title and pay grade should be kept in separate data structures.
Instructors can also work as personal trainers for the members of the club. As a personal trainer, the instructor works one on one with the members, teaching muscle-conditioning techniques. Personal trainers charge a fee to members who contract their services for a specified duration (one month, three months, or six months). The health club has agreed to maintain information about personal-training services between instructors and members, such as the duration, fee, and start date of each contracted service.
MARKING SCHEME AND TASKS
Complete ALL tasks listed below.
TASK / Mark / Weight / TOTAL1 / Draw a complete Enhanced Entity Relationship OR UML Class Diagram for the case study. Include a complete list of all entities/objects/types (ensure that where appropriate you add object relational features) and their attributes including all primary/foreign keys as part of your answer. (Consider carefully the attributes required to complete task 3 when you are choosing appropriate attributes). / 10 / 2 / 20
2 / Using suitable relational database software (either: Oracle 10g / 11g), Write SQL & PL/SQL scripts to set-up all tables/objects/types, including your own, well-designed test data (minimum 5 records per table), to implement your EER/UML Class model as an object-relational database. You must provide printouts of all SQL for creation of tables/ objects/ types & test data. / 10 / 2 / 20
3 / Set-up and test all of the following tasks using Structured Query Language (SQL) or Procedural Langauge SQL (PL/SQL). You must provide printouts of all SQL and PL/SQL for each task AND the task output:
a) Write a query to find the total number of classes provided by the health club during June 2011.
b) Write a query to find which members attended personal training sessions last month.
c) Write a query to list all of the membership details for members attending yoga classes.
d) Write a stored procedure to create an annual statement of all transactions for each membership account.
e) Write a stored procedure that accepts an instructor id as input and creates (as output) attendance lists of the instructors classes for the past 7 days / 10
10
10
10
10 / 0.6
0.6
0.8
1
1 / 6
6
8
10
10
4 / Write an essay ( Min. LENGTH: 1000 WORDS) to critically evaluate the analysis, design and implementation processes that you have worked through in completing this coursework. Specifcally you must highlight any technical strengths/weakness of the prototype database that you have created. / 10 / 2 / 20
100%
Remember to print ALL task output & essay. Also include a copy of all files on a CD/ DISC for submission.
Marking Scheme Criteria Guidelines
The following will be used as a guideline in marking the assignment.
Each task results in a mark on a 0-10 scale, which is multiplied by a weighting in order to obtain a score for that task.
MARKSTASK / 0-4 MARKS (REALLY QUITE INADEQUATE, ‘FAIL’) / 4-5 MARKS (ADEQUATE BUT NO MORE) / 5-6 MARKS (GOOD/ AVERAGE WORK) / 6-7 MARKS (VERY GOOD, WELL ABOVE THE AVERAGE) / 7-10 MARKS (QUITE EXCEPTIONAL)
UML Class Diagram/ EER Diagram / Significant notational and interpretation errors / Notational errors, poor interpretation / Notation correct, reasonable interpretation / Notation correct, reasonable interpretation, good supporting narrative / Excellent in all respects. Supporting narrative accurate and perceptive
Program Code
(Database Creation) / Weak, poorly coded, basic structural omissions, little or no attempt at object relational implementation, probably little relation to Design Diagrams / Acceptable code Basic Relational implementation or Basic attempt at Object relational implementation with some omissions errors and/or inconsistencies / Essentially correct use of code notation, probably some omissions. Clear attempt to implement Object-Relational features / Good use of coding techniques full and accurate. Well presented, good use of Stored Procedures, Functions / Absolute accuracy and completeness/ consistency. Program code clearly capable of successful task completion. Supporting narrative accurate and perceptive.
SQL (OR PL/SQL) Query tasks / Weak SQL (OR PL/SQL) with substantial errors in query structure. Highly inaccurate attribute/function definitions / Essentially correct SQL (OR PL/SQL) notation but probably weak interpretation. Attribute/function definitions may have errors. / SQL (OR PL/SQL) that is basically correct and interprets scenario task reasonably. Could have errors in complex join operations. Attribute/function definitions satisfactory. / All aspects of SQL (OR PL/SQL) fundamentally correct including Attribute/function definitions. Clear and appropriate attempts to implement object relational query functions/constructs. / All SQL (OR PL/SQL) aspects correct or only very minor errors/omissions. Supporting narrative accurate and perceptive.
Short Report / Presentation below acceptable standard in most respects. Weak or omitted commentary. / A barely acceptable standard with omissions etc. Probably incomplete commentary / All features acceptable if not good. Clear evaluative commentary on some aspects of the system / A high standard; neat, accurate English, good style, well presented diagrams, contents etc. Consistent use of Examples from code. Good complete commentary / All aspects outstanding. Excellent use of examples from code. Commentary provides clear evidence of self evaluative appraisal.
ALL WORK MUST BE BOUND IN A SUITABLE FOLDER & SUBMITTED TO THE FACULTY OFFICE BY THE SUBMISSION DATE.
Unit Leader: George Ubakanma
Email :