Q. 3.
(a) For this question assume an unspanned memory organisation, fixed record length, a blocking factor of 2, and 7 records with associated primary keys which are integers and are as follows:
16, 26, 29, 49, 52, 55, 75
(i) With the aid of diagrams, and using the mod function with hash key 13, compare and contrast the placement of the records, with the primary keys given, into blocks using sequential file organisation and hashed file organisation. You can assume that a sufficient number of blocks are available and that linear probing is used. (8)
(ii) Using the sequential file organisation of the records from part (i), and with the aid of diagrams, differentiate between dense indexing and non-dense indexing of the given records. (6)
(b) The following table, parkRun, keeps track of details on 5k park run events, registered runners and the runner's times per park run event. Each park run takes place at a certain location (parkRunLocation), which has its own ID (parkRunID), and a number (parkRunNum) for each park run event at that location:
parkRun (parkRunID, parkRunNum, runnerID, parkRunDate, parkRunLocation, parkRunPostcode, parkRunCountry, runnerFirstName, runnerSurname, runnerDOB, runnerGender, runnerEmail, runnerMobile, runnerTime)
Also stored in the parkRun table are further details on the location (location name, postcode and country) and details on registered users (first name, surname, date of birth, gender, email address, and mobile phone number). Each time a runner completes a park run event, the time taken by that runner to complete the run is stored (runnerTime).
(i) By using the primary key given in the parkRun table, draw a functional dependency diagram showing the functional dependencies between all attributes and the primary key attributes, stating any assumptions made. Clearly indicate on the diagram all full, partial, and transitive dependencies, if they exist. (8)
(ii) Normalise the parkRun table to third normal form, explaining, in your own words, the steps involved at each stage. (8)