9618 AS Computer Science
A database is a structured collection of related data stored in an efficient and compact manner that can be accessed by different application programs. The word "Efficient" means stored data can be accessed very easily and quickly. The word "Compact" means stored data takes up as little space as possible.
In a file-based approach, data is stored in one or more flat files. Each application or department has its own files, often with redundant or duplicated data. There is no central control or shared structure - files are often standalone.
| Limitation | Explanation |
|---|---|
| Data Redundancy | Same data is stored in multiple files, leading to duplication - wasteful as it costs time and money |
| Data Inconsistency | If one file is updated but others aren't, data becomes unreliable and inconsistent |
| Data Dependency | Data formats are defined in application programs; changing formats requires changing whole programs |
| Poor Data Integrity | Harder to ensure data is accurate and consistent across all files |
| No Data Privacy | Data privacy would be properly handled by a database system, not file-based |
| No Data Security | Anyone can easily change or delete data stored in files |
| Difficult to Update | Same information stored at different places - never sure changes are made everywhere |
| Limited Scalability | Not suitable for large volumes of data or complex data relationships |
When asked about limitations of file-based approach, always mention data redundancy (duplication) and data inconsistency (different versions of same data). These are the most commonly tested points!
A relational database is a collection of relational tables where data items are linked by internal pointers. It solves the problems of the file-based approach through organized structure and relationships between tables.
| Advantage | How It Solves File-Based Problems |
|---|---|
| No Redundant Data | Storage space not wasted as data items stored only once |
| Data Consistency | Data altered in one application is available in another |
| Data Independence | Enquiries not dependent on structure of data and software used |
| Complex Queries | Easier to run complex queries across related tables |
| Reduced Dependency | Data separate from software - changes to data don't require programs to be re-written |
A large flat-file database is inefficient as it takes up more space and memory. It requires new data to be added every time you enter a new record. Data redundancy occurs when data is partially duplicated across records. Relational databases avoid these problems through proper normalization.
When creating a database, think about what data you need to store. A database is essentially a collection of details about different items called entities.
| Term | Definition |
|---|---|
| Entity | Anything that can have data stored about it - a person, place, event, or object (e.g., Student, Book, Film) |
| Table | A group of similar data organized in rows and columns; entity is implemented as a table |
| Record (Tuple) | A single row in a table representing one instance of an entity |
| Field (Attribute) | A single column in a table storing one piece of data about the entity |
| Primary Key | Unique identifier for each record in a table (e.g., StudentID) |
| Candidate Key | A field (or combination of fields) that could be used as a primary key |
| Secondary Key | A candidate key not selected as primary key; used for searching or sorting |
| Foreign Key | A field that links to the primary key in another table to create relationships |
| Composite Key | A primary key consisting of two or more attributes together |
| Index | A technique to speed up searching by creating an ordered list of key fields |
A relationship is formed when one table in a database has a foreign key that refers to a primary key in another table. The type of relationship determines how records relate to each other.
| Relationship Type | Description | Example |
|---|---|---|
| One-to-One (1:1) | Each record in one table is associated with exactly one record in another table | Country ↔ Capital City (each country has one capital) |
| One-to-Many (1:M) | One record can be associated with one or more records in another table | Student ↔ Class (many students in one class) |
| Many-to-Many (M:M) | Records in one table relate to many in another, and vice versa | Students ↔ Courses (students take many courses) |
Referential Integrity is a concept that ensures consistency and accuracy of relationships between tables. It uses foreign keys to ensure that a value can only be entered in one table when the same value already exists in the referenced table.
A foreign key can only have a single value, so it cannot handle many references. Solution: Create a junction table (link entity) that links two tables by having fields which are primary keys of both tables.
An E-R diagram provides an understandable visual representation of how entities in a database are related. It documents the design of a database before implementation.
| Type | Description | Notation |
|---|---|---|
| Optional | Relationship may or may not exist (zero or one) | 0 or 0..1 |
| Mandatory | Relationship must exist (one or many) | 1 or 1..M |
Normalization is a process used to construct a relational database that has integrity and in which data redundancy is reduced. Tables that are not normalized will be larger, harder to update, and more difficult to query.
| Normal Form | Key Requirement | What It Eliminates |
|---|---|---|
| First Normal Form (1NF) | Atomic values, no repeating groups, unique rows | Repeating groups and multi-valued attributes |
| Second Normal Form (2NF) | All non-key attributes fully dependent on entire primary key | Partial dependencies |
| Third Normal Form (3NF) | No non-key attribute depends on another non-key attribute | Transitive dependencies |
For 3NF, remember: "Each attribute must depend on the key, the whole key, and nothing but the key!"
For a table to be in First Normal Form, it must meet specific requirements that ensure each piece of data is properly organized.
Same type of data repeated across multiple fields within a single record.
Example: Fields like Phone1, Phone2, Phone3 in a table where each stores a phone number - violates 1NF because same information stored in separate fields.
A group of attributes is repeated for different instances within a single record.
Example: In a Student table, if RollNo, FirstName, LastName are repeated for each subject entry - represents a repeating group of attributes.
Ask yourself: "Can I split any cell into smaller pieces?" If yes, it's not in 1NF! Each cell should hold ONE value only.
For a table to be in Second Normal Form, it must fulfill all 1NF requirements AND have no partial dependencies. This only applies to tables with a composite primary key.
A partial dependency occurs when a non-key attribute depends on only part of a composite primary key, rather than the entire key.
Example: In a table with composite key (StudentName + Subject), if SubjectTeacher depends only on Subject (not StudentName), this is a partial dependency and violates 2NF.
If a table has a single-column primary key, it's automatically in 2NF (no partial dependencies possible)! 2NF issues only occur with composite keys.
For a table to be in Third Normal Form, it must fulfill all 2NF requirements AND have no transitive dependencies. This is the target normal form for most database designs.
A transitive dependency occurs when: A → B → C (A determines B, B determines C). If a non-key attribute depends on another non-key attribute, it violates 3NF.
Example: Staff(StaffID, StaffName, City, Country). Here, Country depends on City (not StaffID directly). If we know City, we can determine Country - this is a transitive dependency!
Ask: "Does any non-key attribute determine another non-key attribute?" If yes, split into separate tables!
Let's normalize a School Database held in a single unnormalized table.
Remove repeating groups (subjects and subject teachers) to separate table with foreign key.
STUDENT(StudentID, FirstName, SecondName, DateOfBirth, ClassID, Location, TeacherName, LicenceNumber, Address, TeacherDateOfBirth)
STUDENTSUBJECT(StudentID, SubjectName, SubjectTeacher)
Note: StudentID in STUDENTSUBJECT is both part of composite PK and a FK linking to STUDENT table.
In STUDENTSUBJECT table, SubjectTeacher depends only on SubjectName (partial dependency). Remove by creating SUBJECT table.
STUDENT(StudentID, FirstName, SecondName, DateOfBirth, ClassID, Location, TeacherName, LicenceNumber, Address, TeacherDateOfBirth)
STUDENTSUBJECT(StudentID, SubjectName)
SUBJECT(SubjectName, SubjectTeacher)
Remove non-key dependencies from STUDENT table:
Final 3NF Design:
STUDENT(StudentID, FirstName, SecondName, DateOfBirth)
CLASS(ClassID, Location, LicenceNumber)
TEACHER(LicenceNumber, TeacherName, Address, TeacherDateOfBirth)
STUDENTSUBJECT(StudentID, SubjectName)
SUBJECT(SubjectName, LicenceNumber)
Answer (any 3 limitations, 2 marks each):
Additional points for deeper understanding: No central control, hard to manage relationships, lack of data privacy, no data integrity enforcement.
Answer:
Additional points: Centralized control, better security through access controls, referential integrity enforcement, standard query language (SQL).
Answer:
Answer:
Additional points: Prevents orphaned records, maintains relationship validity, ensures all data is up-to-date.
Answer:
E-R Diagram: STUDENT ||---o{ STUDENTCOURSE }o---|| COURSE
Answer - 1NF Requirements:
Example violation: A table with columns: StudentName, Phone1, Phone2, Phone3 violates 1NF because it has repeating groups (Phone1, Phone2, Phone3 store the same type of data).
Additional points: Multi-valued fields like "Subjects: Math, Science, English" also violate 1NF. Each subject should be a separate row with the student ID.
Answer:
Additional points: Tables with single-column primary keys are automatically in 2NF - no partial dependencies possible.
Answer:
Additional points: This eliminates redundancy - DepartmentLocation stored once per department, not repeated for every staff member.
Answer:
Additional points: M:M relationships require a junction table. The "many" side always has the foreign key.
Answer:
Additional points: If a staff member is deleted, corresponding device records would need handling (cascade delete or restrict).
| Term | Definition |
|---|---|
| Attribute | A property or characteristic of an entity; represented as a column in a table |
| Atomic Value | A value that cannot be divided into smaller parts; single, indivisible data item |
| Candidate Key | A field or combination of fields that could serve as a primary key |
| Composite Key | A primary key made up of two or more attributes |
| Data Consistency | Ensuring data is the same across all copies and throughout the database |
| Data Redundancy | Unnecessary duplication of data in a database |
| Entity | A real-world object or concept about which data is stored (e.g., Student, Product) |
| E-R Diagram | Entity-Relationship diagram showing entities and their relationships graphically |
| Field | A column in a database table; stores one attribute of an entity |
| Flat File Database | A database stored in a single table, often as a CSV file |
| Foreign Key | An attribute in one table that references the primary key in another table |
| Index | A data structure that speeds up searching by creating ordered lists of key fields |
| Junction Table | A table that implements a many-to-many relationship between two other tables |
| Normalization | Process of organizing data to reduce redundancy and improve integrity |
| Partial Dependency | When an attribute depends on only part of a composite primary key |
| Primary Key | A unique identifier for each record in a table |
| Record (Tuple) | A row in a database table representing one instance of an entity |
| Referential Integrity | Ensuring foreign keys match valid primary keys in related tables |
| Relation | A table in a relational database |
| Secondary Key | A candidate key not selected as primary key; used for searching/sorting |
| Table | A collection of related data organized in rows and columns |
| Transitive Dependency | When a non-key attribute depends on another non-key attribute |
Remember: "The key, the whole key, and nothing but the key!"