Database and file concepts
Learn the core concepts and decision-making in Cambridge 9626 Topic 10, then practise recall, application and transfer with fictional scenarios.
Exam scope and assessment notes
This is original StudyForA teaching and fictional practice based on Cambridge syllabus outcomes, not copied syllabus text, an official question, a required software brand or a Cambridge mark scheme. Practical software tasks here are planning and reasoning exercises; StudyForA does not inspect files created in spreadsheet, database, graphics, audio/video or animation software, simulate the practical exam environment, award official marks or predict a grade. Follow the software and file-format requirements issued by your centre for your exam year.
Before you begin
- Check whether your route is AS Level (Topics 1–11) or full A Level (Topics 1–21).
Work on paper. Try each question before revealing help, and explain why your method works.
Quick readiness check
Before Topic 10: database and file concepts
What you will learn
- Choose text, alphanumeric, integer/decimal, date/time and Boolean data types and field sizes
- Design one-to-one and one-to-many relationships, keys and referential integrity; interpret conceptual, logical and physical ERDs
- Create validation, verification, simple/complex/nested/summary queries and static/dynamic parameters
- Explain normalisation through 3NF; compare flat files, file access, file formats and hierarchical/network/object/relational DBMS; explain MIS
Structure prevents avoidable inconsistency
Choose field types and sizes for the data: integers for whole counts, decimals for measured values, dates for calendar values, Boolean for true/false. A primary key uniquely identifies a record; a foreign key links records; compound/composite keys combine fields. Referential integrity prevents a link to a missing record. A many-to-many relationship usually needs a linking table. Normalisation reduces repeated data and update anomalies.
Queries and files serve different needs
Queries select, update, append, delete or summarise records. Criteria can be simple, combined, parameterised, nested or cross-tabulated. A static parameter is fixed in the query; a dynamic parameter is supplied at run time. Flat files suit simple standalone lists; relational DBMS support linked tables and constraints. Indexed sequential and direct access serve different retrieval patterns. Generic formats improve exchange; an MIS turns organisational data into information for decisions.
Sources for this lesson
Pause and explain
Which approach best demonstrates secure, reliable IT practice in database and file concepts?
Worked example
A club stores members, events and attendance. Design the key relationship that avoids repeating a member's name for every event.
Show the worked solution
- Create Member and Event tables with unique primary keys.
- Create an Attendance table with both foreign keys.
- Use the pair of keys to prevent duplicate attendance records.
Answer Member(MemberID, Name) and Event(EventID, Date, Venue) are linked by Attendance(MemberID, EventID, Status). Attendance uses both fields as a compound/composite primary key and each is also a foreign key. This resolves the many-to-many relationship and reduces repeated member and event details.
Try a topic test
Choose Easy, Normal or Difficult. Save your answers and open worked review after finishing.
Test this topic →From guided practice to a new situation
Write your answer and reasoning first. Use a hint only if you are stuck. The model solution is for self-checking; your written work is not automatically marked.
A product table stores CategoryName and SupplierAddress in every product row. Explain a likely update anomaly and a 3NF direction.
Give me a hint
Look for facts repeated for a category or supplier, not for a product.
Compare my reasoning
- Identify attributes that depend on a category/supplier rather than ProductID.
- Move those facts to their own entity tables.
- Link with foreign keys and preserve referential integrity.
If a supplier address changes, many product rows may need updating; a missed row creates inconsistent information. Store suppliers once in Supplier(SupplierID, Address) and categories once in Category(CategoryID, Name), then reference them from Product. This reduces repetition and update anomalies while retaining valid foreign-key links.
Look for these in your work
- Names a concrete anomaly
- Separates independent entities
- Uses valid key relationships
A database search asks the user to enter a year each time and should include records from that year onward. What query type and criterion are suitable?
Give me a hint
The value changes at run time and the comparison includes the boundary.
Compare my reasoning
- Use a dynamic parameter for the requested year.
- Apply a >= comparison to the year field.
- Test the exact boundary and one earlier/later value.
Use a dynamic parameter query asking for the year, with a criterion such as Year >= [Enter starting year]. Test a record exactly at the entered year, one before and one after. A static query would keep the value fixed and would not prompt for a new year.
Look for these in your work
- Selects dynamic parameter
- Uses the inclusive operator
- Tests boundaries
A school wants a report of the number of learners in each year group by tutor. Which summary approach should it use?
Give me a hint
Rows and columns can represent two categories while the value aggregates records.
Compare my reasoning
- Group records by year group and tutor.
- Count learner IDs rather than summing them.
- Check missing tutor/year values and labels.
Use a summary or cross-tab query with year group and tutor as grouping dimensions and count of LearnerID as the value. Check that each learner is counted once, handle missing assignments consistently and label totals clearly. A pivot table can provide a similar report where supported.
Look for these in your work
- Chooses a grouped summary
- Uses count correctly
- Checks data quality
Common mistakes
- Using a person's name as a unique key
- Creating a many-to-many link without a junction table
- Assuming normalisation removes every need for validation
What can you explain now?
A product table stores CategoryName and SupplierAddress in every product row. Explain a likely update anomaly and a 3NF direction.
Compare with the explanation
If a supplier address changes, many product rows may need updating; a missed row creates inconsistent information. Store suppliers once in Supplier(SupplierID, Address) and categories once in Category(CategoryID, Name), then reference them from Product. This reduces repetition and update anomalies while retaining valid foreign-key links.
Look for facts repeated for a category or supplier, not for a product.
After trying it yourself, choose your next review. This is your self-assessment.
Your review choice appears on Today. Sign in to sync it across devices.
Make it stick
How does a foreign key support referential integrity?
If you needed the solution, close it and solve the problem again from a blank page. A correct answer is stronger when you can explain the reason for each step.