Practice A3.2 Database design with authentic IB Computer Science (First Exam 2027) exam questions for both SL and HL students. This question bank mirrors Paper 1, 2, 3 structure, covering key topics like programming concepts, algorithms, and data structures. Get instant solutions, detailed explanations, and build exam confidence with questions in the style of IB examiners.
A library management system tracks books, authors, members, loans, and reservations.
Create an Entity-Relationship (ER) diagram analysis for the library system:
| Entity | Primary Key | Attributes | Relationships | Cardinality | Business Rules |
|---|---|---|---|---|---|
| Book | BookID | — | — | — | — |
| Author | — | AuthorName, Biography | — | Many-to-Many | — |
| Member | MemberID | — | Borrows books | — | Max 5 books per member |
| Loan | — | — | — | — | — |
| Reservation | — | ReservationDate | — | One-to-Many | Max 3 reservations |
Analyze how normalization to Third Normal Form (3NF) would improve this library database design and identify potential denormalization decisions for performance optimization.
A healthcare analytics platform processes patient data from multiple hospitals for population health studies.
Design stored procedures for anonymizing patient data while preserving statistical validity for research purposes.
A manufacturing company tracks products, suppliers, purchase orders, and inventory levels.
Compare different approaches to handling hierarchical data structures such as product categories and organizational departments, including adjacency lists, nested sets, and materialized paths.
Explain how database views can simplify complex queries and provide data security in a manufacturing environment.
A financial trading platform processes millions of transactions daily and requires high-performance database operations. Analyze different SQL optimization techniques for improving query performance:
Analyze different SQL optimization techniques for improving query performance:
| Optimization Technique | Implementation Method | Performance Impact | Use Case | Potential Drawbacks |
|---|---|---|---|---|
| Index Optimization | - | High | - | Storage overhead |
| Query Rewriting | Complex joins | - | - | - |
| Partitioning | Horizontal/Vertical split | - | Large tables | - |
| Materialized Views | - | Medium | - | Data consistency |
Explain how database connection pooling and prepared statements improve application performance in high-transaction environments.
A gaming platform tracks player statistics, achievements, and leaderboards with real-time updates.
Compare different approaches to handling concurrent updates to player scores and rankings, including optimistic vs pessimistic locking strategies.
Explain how batch processing techniques can efficiently update aggregated statistics and leaderboards.
A healthcare clinic designs a database for patient records, appointments, treatments, and medical history.
Develop a comprehensive database design that addresses privacy requirements, audit trails, and regulatory compliance:
Explain how temporal databases can track changes in patient medical conditions over time and support historical reporting requirements.
A content management system handles articles, user comments, and search functionality across multiple languages.
Design a database solution for full-text search capabilities that supports multiple languages and relevance ranking:
Analyze how database cursors and pagination techniques handle large result sets efficiently in web applications.
A financial trading system requires ultra-low latency data access and high availability for real-time decision making.
Design a database architecture that minimizes query latency while ensuring data consistency for trading operations.
An e-commerce platform experiences rapid growth requiring database scalability and performance optimization.
Explain how database connection pooling and query optimization improve application performance under high load.
A customer relationship management (CRM) system requires complex reporting and data analysis capabilities.
Compare different approaches to database programming including embedded SQL, stored procedures, and ORM (Object-Relational Mapping) frameworks:
Write pseudocode for a stored procedure that calculates monthly sales performance metrics, including error handling and transaction management.