Film Society

A film society has over 300 members who are able to borrow from its library of more than 1000 classic films. There is more than one copy of some films. The films may be held in video cassette form, DVD form, or, in some cases, both.

Members pay an annual membership fee that allows them to borrow films free of charge. Members can only have one film out on loan at a time. Loans are not permitted to extend beyond seven days.

The film society needs a database to contain details of its members, its films and those films currently out on loan.

The table of members is to contain appropriate contact details for each member and the date on which their membership fee next becomes due. It should also ensure that a member can only borrow one film at a time.

The table of films is to contain information about each film copy. It should store the name of the film, its director, its year of release and running time, as well as a brief synopsis of the storyline.

The loans table will identify which films are out on loan and to whom.

(a) Members Table

• Create a table called Member to hold details of members of the film society.
• Explain the reason for including each attribute (field). Give the data type of each attribute.
• Identify the key. [6]

(b) Film Table

• Create a table called Film to hold details of films owned by the film society.
• Explain the reason for including each attribute (field). Give the data type of each attribute.
• Identify the key. [5]

(c) Loan Table

• Create a table called Loan to hold information about films out on loan at any one time.
• Explain the reason for including each attribute (field).
• Give the data type of each attribute.
• Identify the key. [5]

(d) Data Input

Create suitable data for each of your tables. Although the film society has over 300 members and more than 1000 films, your tables need only show a sample of the records the film society would hold. You should not attempt to create the whole database. Instead, your Member and Film tables should each hold at least 20 records. Your Loan table should hold at least 10 records. You should choose your data to be suitable for producing sensible results in the remaining questions.

Include a full printout of each of your tables. Screen shots are acceptable. [8]

(e) Database Queries

At the film society library, members should be able to interrogate the database. They should be able to: print a hard copy report of information about an individual film title; print a hard copy report listing all films made by an individual director; and run an alphabetical screen listing of all films currently available for loan. Create interfaces (of which there should be hard copy evidence) that allow a member to:

(i) input the name of a film and output to the printer the information held about that film. Provide at least two sample reports.

(ii) input the name of a director and output to the printer a list of the society’s films made by that director (with the years of release and running times). Provide at least two sample reports.

(iii) output to the screen a list, in alphabetical order, of all films currently available for loan. Provide hard copy of one sample page of your screen.

[9 Marks]

(f) Mail Merge

Create an interface that allows the film society to send mail merge letters to any members whose loans are shown to be overdue. Provide hard copy evidence of your standard letter (with mail merge fields) and show that your mail merge produces suitable letters to all members whose borrowed films are shown as overdue in the Loan table. [7]

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 2006.  All rights and copyright to OCR.  Please refer to the OCR Copyright Statement for further information.

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