i have created these table can you please look at them and tell me if there are right, or can you find a better way of doing them
CSS TABLE
CSSID Number primarykey
Surname Text
DOB Date/Time
Comment Text
pencil table
PencilNumber Number
CSSID Number
Type Text
DateIssues Date/Time
LengthIssues Number
history
CSSID Number
PencilNumber Number
Type Text
DateIssues Date/Time
DateReturn Date/Time
Weight Number
the question is
In Oblivia the cost of pencils is absolutely outrageous and so their usage by fifty (or so) Civil Service Scribes (CSS) is carefully monitored. Once an idea has been discussed in each of the relevant committees, and with anybody else who cares to exercise their democratic right to participate, then a report must be written. To write the report they must first acquire a pencil which is done by presenting themselves in person at the reception desk of the Pencil Loan Department (PLD). The people behind the desk use one of their carefully-guarded pencils to fill in the Pencil Loan Form (PLF).
Firstly they check that the CSS does not currently have an outstanding PLF ; if they don’t then a pencil is selected of the type (H, HB, B) chosen by the CSS from the pencil box. The payroll number of the CSS, the unique pencil number (lovingly hand-engraved on each pencil) and the current date are written onto the PLF as well as the current length of the pencil.
When the CSS returns the pencil they must also hand over the document that they have written so that it can be weighed (this will be weight of paper + lead) on a very sensitive set of scales. The paper is of a standard weight, and so by counting the number of pages the PLD can determine the weight of the paper and hence deduce the weight of the lead writing on the paper by a subtraction. They also measure the length of the pencil on return and the date, pencil length and calculated weight of lead (on the paper) are written onto the PLF which is then filed.
If the pencil is below a certain length (2cms) on return then it is deemed no longer available for use and sent to the Pencil Audit Department.
As part of an efficiency drive it has been decided to replace the use of pencils within the PLD and to use a computer system in its place.
Features of the Required System
File maintenance of the CSS file:- the CSS number is issued by the payroll office and the system should also store the surname, date of birth and a comments field.
File maintenance of the pencil file- the system should generate new pencil numbers (numeric) and store the type of pencil plus other fields you may consider necessary.
Allow relevant data to be recorded for issue and return of pencils. The computer will NOT used to calculate the weight of lead on each document, this is a manual system, and will stay as such. The weight will need to be stored.
A cross-tabulation which presents the list of CSSs down the side of the page and the pencil types along the top of the page. The display should be capable of analysing all PLF completed forms between any two dates, typically used for the previous month. Each entry should represent a value for a specific CSS (based on the row) and a type of pencil (based on the column). The entry is a ratio of two sums expressed as a decimal number. The first sum is the total weight of pencil lead used by that employee for that type of pencil. The second sum is the total of all length usage by that CSS for that type of pencil. The ratio- in the tutors day, ratios were fractions, but fractions are considered too divisive, so divide weight by the length and print the answer in a suitable format.
The inspector can glance at this and see if any person has a value that is different than the others- this will be suspicious.
NB this can be achieved with a screen display, but a printed report is an added bonus