Database Concepts (Copy)
Cheat Sheet: A Level Computer Science – Database Concepts & Normalisation
1. Limitations of File-Based Data Storage
| Limitation | Explanation |
|---|---|
| Data Redundancy | Same data stored in multiple files |
| Data Inconsistency | Different versions of the same data |
| Lack of Data Integrity | No validation/enforcement rules |
| Lack of Concurrency Control | Multiple users may corrupt data |
| Difficulties in Data Access | Custom programs required for each task |
| Poor Security | Limited control over who accesses what data |
2. Features of a Relational Database
| Feature | How It Solves Limitations |
|---|---|
| Normalization | Reduces redundancy and ensures consistency |
| Relationships | Eliminates duplication using foreign keys |
| Data Integrity Rules | Enforces validity and consistency |
| Indexing | Improves search performance |
| Access Controls | Manages user rights and security |
| Query Languages (e.g. SQL) | Easy data manipulation and retrieval |
3. Relational Database Terminology
| Term | Definition |
|---|---|
| Entity | Real-world object or concept (e.g., Student) |
| Table | Collection of related data (entity set) |
| Record / Tuple | Single row in a table (instance of an entity) |
| Field / Attribute | Column in a table (property of an entity) |
| Primary Key | Uniquely identifies each record |
| Candidate Key | Field(s) that could serve as a primary key |
| Secondary Key | Used for indexing and faster searching |
| Foreign Key | Links to primary key in another table |
| Relationship | Connection between tables: |
| → One-to-One: Each A maps to one B | |
| → One-to-Many: Each A maps to many Bs | |
| → Many-to-Many: Multiple A map to multiple B | |
| Referential Integrity | Ensures foreign keys match valid primary keys |
| Indexing | Improves speed of data lookup operations |
4. Entity-Relationship (E-R) Diagrams
- Purpose: Visually model entities, their attributes, and relationships
- Notation:
- Rectangles: Entities
- Ellipses: Attributes
- Diamonds: Relationships
- Lines: Connect entities to relationships
5. Normalisation Process
| Form | Condition | Purpose |
|---|---|---|
| 1NF (First Normal Form) | No repeating groups or arrays; atomic values only | Removes multi-valued attributes |
| 2NF (Second Normal Form) | In 1NF + no partial dependencies | Ensures full functional dependency on primary key |
| 3NF (Third Normal Form) | In 2NF + no transitive dependencies | Attributes depend only on primary key |
6. How to Identify Normal Forms
| Check | Test for 3NF |
|---|---|
| Atomic values | ✅ 1NF |
| No partial dependencies | ✅ 2NF |
| No transitive dependencies | ✅ 3NF |
- Partial Dependency: Attribute depends on part of composite primary key
- Transitive Dependency: Attribute depends on non-primary attribute
7. Producing a Normalised Database Design
Steps:
- Identify Entities from the scenario
- List Attributes for each entity
- Assign Primary Keys
- Identify Functional Dependencies
- Apply Normalisation:
- 1NF → Make all fields atomic
- 2NF → Remove partial dependencies into new tables
- 3NF → Remove transitive dependencies into new tables
- Define Relationships with foreign keys
- Draw E-R Diagram