IS5413 Chap.8 Physical Design with File Organisation and Indexes
Physical Design with File Organisation and Indexes
The first stage whose inputs are measurements
Physical design turns the logical description into the technical specification for how data will be stored and fetched, with the goal of adequate performance alongside database integrity, security and recoverability.
Its published inputs make the change of register obvious: the normalised relations themselves, expected data volumes, the attribute definitions, the response times users expect, security requirements, what backup and recovery must support, the integrity rules to be enforced, and which management system will run it.
Up to this point a design was right or wrong; from here it is fast or slow for a stated pattern of use, and the pattern has to be supplied before the question can be answered.
Field design comes before file design
Each field gets a data type, and that is a storage and an integrity decision at once.
Variable length character types consume only the space a value needs while fixed length types do not, and large object types exist for content that does not belong in an ordinary column. Coding a repeated text value against a small lookup table saves space at the cost of one extra lookup to recover the value.
Range control, default values and null control are then attached so that the database itself rejects impossible data rather than trusting every application to be careful.
Regulatory pressure is named explicitly as a reason this matters, with financial reporting obligations requiring data to be secure, tamper free, logged and auditable.
Four organisations, and a counting note
A heap keeps no particular order, so adding is very easy and any search is impractical. A sequential file is ordered by the primary key, making ordered reading very fast and additions expensive.
An indexed organisation stores records with a structure that locates them, giving moderate speed in both directions at the price of index maintenance. A hashed organisation computes the position from the key, which is very fast for exact lookups and impractical for anything ordered.
The course teaches four in its comparison table while its objectives and summary name three, treating the heap as the absence of an organisation; if a question asks for three, give the three and mention the heap as the unordered case.
Indexes and denormalisation, both priced
An index locates the records satisfying a condition without reading the whole file.
Every index accelerates reads and slows writes, so the nine published rules are really nine conditions under which that trade is worth making, including indexing larger tables, indexing primary keys and frequent search fields, watching selectivity above roughly a hundred distinct values and below thirty, avoiding long values, and being careful where nulls are involved.
Denormalisation is the other lever: transforming normalised relations into non normalised physical records to reduce joins, justified in three named situations and paid for in wasted space, reintroduced anomalies and reprogramming when business rules change. The published advice is to try clustering, better file organisation and proper query design first, because those are reversible and a denormalised schema is not.
What this chapter covers
- 01
Inputs and goals of physical design
- 02
Data types, coding and field level integrity control
- 03
Heap, sequential, indexed and hashed organisations
- 04
The nine rules for deciding an index
- 05
Three published denormalisation situations
- 06
Horizontal and vertical partitioning
Worked example · free
Choose an organisation from an access profile
- 2Choose and justify an organisation for the event log.
- 2Choose and justify an organisation for the membership file.
- 2Choose and justify an organisation for the card file.
- 3Identify which file benefits from a secondary index, and name the condition it must meet.
Key terms
- Physical Database Design
- The stage that turns a logical description into storage and retrieval specifications good enough for the required performance while keeping integrity, security and recoverability.
- File Organisation
- The technique for physically arranging the records of a file on secondary storage, chosen from heap, sequential, indexed and hashed.
- Index
- A structure used to locate the records in a file that satisfy some condition, without reading the whole file. Primary keys are indexed automatically.
- Secondary Key
- A field or combination of fields indexed for retrieval without being the primary key, often used to group entities such as a category or a postcode.
- Denormalisation
- The deliberate transformation of normalised relations into non normalised physical records to reduce joins, traded against space, integrity risk and future reprogramming.
- Horizontal Partitioning
- Distributing the rows of one logical relation across several physical tables, useful when one group of users only ever reads one band of the rows.
- Vertical Partitioning
- Distributing the columns of one logical relation across several physical tables, with the primary key repeated in each, useful where user groups need different columns.
Physical Design with File Organisation and Indexes FAQ
When is an index not worth creating?
When the table is small enough to scan faster than it can be looked up, when the field holds very few distinct values, when the values are long, or when the field is frequently null and the system will not match nulls through an index.
The selectivity rule is the one most often forgotten: the published guidance puts the useful band above roughly a hundred distinct values and warns against fewer than thirty, because below that an index adds maintenance cost without narrowing the search much.
Is denormalisation the same as making a mistake on purpose?
It is making a trade on purpose, which is different. The published justification covers three situations, and the cost is named alongside the benefit: space, reintroduced anomalies, a higher chance of inconsistency and reprogramming when the rules change. What makes it legitimate is the order of operations.
Normalise fully at the logical level so the correct structure is known, then denormalise a named relation with a stated reason at the physical level.
Exam move
Write the four organisation comparison rows from memory, then invent an access profile for each row that would make it the obviously correct choice. Next take the nine index rules and sort them into two piles, those about the data and those about the platform, which makes them much easier to recall under pressure.
Finish by describing one denormalisation you would make in your own project database and, in the same breath, the anomaly you have just reintroduced.
Working through Physical Design with File Organisation and Indexes in IS5413? Sia is AskSia’s AI Information Technology tutor — ask any IS5413 Physical Design with File Organisation and Indexes question and get a clear, step-by-step explanation grounded in how IS5413 is taught and assessed. Read this chapter free, then take your hardest questions to Sia.
