Press Cutting Service

This is a software development task and an implementation task.

A group of Sixth Form students have decided to run a press cutting service for the students in their school. They intend to identify and take cuttings of topics related to each examination subject taught at the school.

They propose to hold basic information about each cutting on a database. Students will be able to access this database.

The teacher responsible for IT services at the school has agreed to provide storage space on the system for the database, but there will not be sufficient room to allow cuttings to be scanned in for reading online.

Cuttings will therefore be filed as paper documents, available on request in the school library.

To begin with, and to limit the cost of the exercise, the group of Sixth Form students will take cuttings only from those newspapers and magazines already purchased by the school library and from those they are able to bring in from home.

The school has agreed to cover any other costs for a trial period and to handle any copyright issues that may arise.

A database is required to hold details of the cuttings available and the students who borrow them. It has already been decided to hold the data in the following tables:

Cutting This table holds the following details of each cutting:

• The cutting ID
• Identification of the topic dealt with in the article
• A brief synopsis
• The main school subject to which it relates
• The newspaper or magazine from which it was taken
• The date of its publication

Student This table holds the following details:

• Student ID
• Name
• Tutor group

StudentCutting This table acts as a link between the Cutting and Student tables. It identifies who has accessed which cutting and when.

(a) Create a table called Cutting to hold the details of each cutting.

• Give the reason for including each of the attributes.
• Give the data type of each attribute.
• Identify the key.
• Show how validation has been used in the creation of the table.

[8 Marks]

(b) Create a table called Student to hold the details of each student using the service.

• Give the reason for including each of the attributes.
• Give the data type of each attribute.
• Identify the key.
• Show how validation has been used in the creation of the table.

[4 Marks]

(c) Create a table called StudentCutting to hold information about which students have accessed each cutting.

• Give the reason for including each of the attributes.
• Give the data type of each attribute.
• Identify the key.
• Show how validation has been used in the creation of the table.

[6 Marks]

(d) Create suitable data for each of your tables.

• Include at least 15 cuttings and 20 students.
• The StudentCutting table should have at least 30 entries.
• Choose your data to be suitable for producing sensible results in the remaining questions.
• Include hard copy of the whole of each of your tables. Screen shots are acceptable.

[5 Marks]

(e) Create a report that lists all the cuttings available for a particular school subject.

• The subject chosen must have at least 5 cuttings.
• Include evidence that the user has input a school subject. A screen shot is acceptable.
• Include evidence to show how your solution created the report. A screen shot is acceptable.

[6 Marks]

(f) Create a report that lists all students who have accessed cuttings since a given date, together with the number of cuttings each student has accessed.

• The date chosen must produce a report on at least 5 students.
• Include evidence that the user has input the date. A screen shot is acceptable.
• Include evidence to show how your solution created the report. A screenshot is acceptable.

[6 Marks]

After the database has been in use for some time and has grown substantially in size, it becomes apparent that it would be helpful if students could search the database for articles that contain specific keywords.
It is decided that the keywords, and therefore the searches, will be restricted to single words and that no wild cards will be allowed in the searches.

In order to include this new requirement, a new table is needed. 

CuttingKeyword This table consists of two columns:

• The cutting ID (to link to the Cutting table)
• Keyword
• These two attributes together form the primary key.
• Each row of the table can contain only one keyword.

(g) Develop your existing database to enable a user to undertake a keyword search.

• Clearly describe what you have done.
• Provide appropriate evidence of your implementation.
• Include sufficient data to test the system.
• Demonstrate that your revised database operates successfully.
• Provide a hard copy of a report to show the result of the search.

[9 Marks]

This task was worth 40 Marks.  It was a software development task and an implementation task that orginally appeared in OCR 2507 Task 1 Jun 2007.  All rights and copyright to OCR.  Please refer to the OCR Copyright Statement for further information.

IMAGE CREDITS: https://en.wikipedia.org/wiki/Printing_press