CCA-2-IED Information Extraction & Databases
Referred Coursework July 2011
Faculty of BusinessReferred/Deferred Coursework 2010/11
/CCA-2-IED Information Extraction & Databases
UNIT LEADER: George Ubakanma ()
COURSE(S):
BSc Computing, BSc BIT, eBIT
FdSc BIT, HNC/D BIT
HNC/D Computing
If not available please contact the course administrator
Amy Lynn ()
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/
ALL WORK MUST BE BOUND IN A SUITABLE FOLDER & SUBMITTED TO THE FACULTY OFFICE BY THE SUBMISSION DATE.
Unit Leader: George Ubakanma
Email :
CCA-2-IED Information Extraction & Databases
Referred Coursework July 2011
EXAMPLE CASE STUDY
LSBU Outward Bound
LSBU runs an Outward Bound Centre based in the Lake District wishes to build a database to store and manage information about its business. The Outward Bound Centre takes bookings from clients for adventure holidays and team building events.
Clients are organisations such as schools, youth groups and businesses. Information stored about clients includes their unique client number, the client’s name (School name, business name, etc.), and the name and contact details of the person representing the client organisation.
The centre has a number of accommodation options: a four star hotel; a hostel with large dormitories for children and smaller shared rooms for supervising adults; and a small camping ground. The name and type of these options is stored, along with the total number of people they can accommodate.
Each booking has a unique booking number, the date that booking was made, and the dates of the stay. When making a booking, clients state how many people will be staying, and what type of accommodation is required. They also say whether they will use the restaurant or self-catering facilities. The booking will also include any additional facilities required by the client.
The centre contains a number of additional facilities for hire. These include lecture theatres, teaching rooms and computing labs with Internet access. Portable multi-media projection and presentation equipment are also available for hire.
There are various outdoor pursuits facilities including: obstacle course, climbing-wall with safety equipment, paintball-wargames, canoes, hill walking... etc. Details of these various facilities, including any hire charges, need to be stored on the database. During any booking, the client may use these facilities. The date when a facility is booked for use must be recorded.
Use of outdoor pursuit facilities is supervised by one or more trained members of staff. Staff may be trained to supervise more than one outdoor pursuit facility. Each member of staff has a unique staff number, and their name and contract details (full or part time) are also recorded
MARKING SCHEME AND TASKS
Complete ALL tasks listed below.
Every task requires a supporting narrative (max: half a side A4 per task)
TASK / Mark / Weight / TOTAL1 / Using UML notation draw a complete Entity Relationship Diagram for the case study. Include a complete list of all entities and their attributes including all primary/foreign keys as part of your answer. (Consider carefully the attributes required to complete task 4 when you are choosing appropriate attributes). / 10 / 2 / 20
2 / Draw a complete set of functional Dependency Diagrams for the case study to prove that all of the attributes within your entities are in BCNF / 10 / 1 / 10
3 / Using suitable relational database software (either: Oracle 10g / 11g), Write SQL & PL/SQL scripts to set-up all tables including your own, well-designed test data (minimum 5 records per table), to implement your entity model as a database. Provide printouts of all SQL creation statements, also the sample tables & data. / 10 / 2 / 20
4 / 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 which Customer has made the most bookings in the last 6 last months
b) Write a query to find what is the average age of staff who are assigned to work on the outdoor pursuits
c) Write a query to produce a list of all customers who have rented self-catering accommodation in the past year.
d) Write a stored procedure to calculate a final bill for a given customer who has used various facilities during their stay
e) Write a stored procedure to provide a monthly report showing the total revenue raised by each outdoor pursuit / 10
10
10
10
10 / 0.6
0.6
0.8
1.5
1.5 / 6
6
8
15
15
100%
Remember to print ALL task output. 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 (WELL ABOVE THE AVERAGE) / 7-10 MARKS (QUITE EXCEPTIONAL)
ER Diagram / Significant notational and significant interpretation errors / Notational errors, poor interpretation
of ER modelling technique / Notation correct, reasonable interpretation
of ER modelling technique / Notation correct, reasonable interpretation, good supporting narrative / Excellent in all respects. Supporting narrative accurate and perceptive
FD Diagram / Significant notational and significant interpretation errors / Notational errors, poor interpretation of FD diagram technique / Notation correct, reasonable interpretation of FD diagram technique / Notation correct, reasonable interpretation, good supporting narrative / Excellent in all respects. Supporting narrative accurate and perceptive
Program Code
(Database Creation) / Weak SQL (OR PL/SQL), poorly coded, basic structural omissions probably little relation to Design Diagrams / Acceptable code SQL (OR PL/SQL) Basic Relational implementation with some omissions errors, inconsistencies / Essentially correct SQL (OR PL/SQL) notation, probably some omissions. / Good use of coding techniques SQL (OR PL/SQL) full and accurate. Well presented, good use of Validation Controls etc… / SQL (OR PL/SQL) has absolute accuracy and completeness/ consistency. Program code clearly capable of successful task completion. Supporting narrative accurate and perceptive.
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 query functions/constructs. / All SQL (OR PL/SQL) aspects correct or only very minor errors/omissions. Supporting narrative accurate and perceptive.
ALL WORK MUST BE BOUND IN A SUITABLE FOLDER & SUBMITTED TO THE FACULTY OFFICE BY THE SUBMISSION DATE.
Unit Leader: George Ubakanma
Email :