44 total
By the end of this subtopic, you should be able to:
A file-based system is a system where data is stored in separate files, such as text files or spreadsheets. Each department or program may keep its own file. For example, a school might have one file for students, another for tutors, and another for attendance, with no proper link between them.
This may look simple at first, but it causes many problems when the amount of data grows.
One major problem is data redundancy. This means the same data is stored more than once. For example, if several students have the same tutor, the tutor’s name may be written again and again in many records. This wastes storage space.
Another problem is data inconsistency. This happens when the same piece of data appears in different places, but not all copies are updated. For example, if a tutor changes name and only some files are updated, different files will show different answers. This makes the data unreliable.
File-based systems also make updating data difficult. If the same information is stored in many places, every copy must be changed. This takes more time and increases the chance of mistakes.
They also have weak data integrity. Data integrity means keeping data accurate and sensible. In separate files, it is harder to check that related data matches properly.
Another weakness is poor security. Separate files often do not have strong central control. This makes it harder to control who can see, change, or delete data.
File-based systems are also bad at handling relationships between data. For example, it is hard to clearly link customers to their orders, students to their teachers, or products to sales.
Finally, file-based systems have limited scalability. Scalability means being able to grow without becoming difficult to manage. As more files and more data are added, the system becomes messy and slow to maintain.
A flat file database stores all data in one single table. This is a very basic form of file-based storage.
For example, imagine a student table with these fields:
At first, this seems easy to read. But if many students have the same tutor, the tutor’s name and form room will be repeated many times. That creates redundancy and makes updates harder.
If one tutor changes form room, every record containing that tutor must be found and changed. If even one record is missed, the data becomes inconsistent.
A relational database stores data in separate tables and links those tables together using keys.
This solves many of the problems of file-based systems.
For example, instead of storing tutor details again and again in the student table, we can split the data into two tables:
Student table
Tutor table
Now the tutor’s name and form room are stored only once in the Tutor table. The Student table stores only the TutorID. This TutorID links each student to the correct tutor.
This design is better because it reduces duplication, saves storage, and makes updating easier. If a tutor changes rooms, only one record needs to be edited.
A relational database has several important features that improve the storage and retrieval of data.
Instead of keeping everything in one large file, data is divided into tables based on entities. This reduces repeated data.
A primary key uniquely identifies each record in a table. A foreign key is used to link one table to another. This makes relationships clear and organised.
Because each fact is stored in the most suitable table, the same data does not need to be repeated many times.
When data is stored once, updates only need to happen in one place. This reduces the chance of mismatched data.
The database can check that links between tables are valid. For example, a student should not have a TutorID that does not exist in the Tutor table.
A database system usually provides central control over access. This means it is easier to decide who can view, add, change, or delete data.
Because the structure is organised, data is easier to update, search, and manage.
Relational databases are much better for big systems where many pieces of data are linked together.
You must know the meaning of the common terms used in relational databases.
An entity is a real-world thing we want to store data about. Examples include Student, Teacher, Customer, Book, or Order.
In a relational database, an entity usually becomes a table.
A table is a collection of data about one entity, arranged in rows and columns.
For example, a Student table stores information about students.
A record is one row in a table. It contains all the data about one single item.
For example, one row in a Student table may contain the details of one student.
A tuple is another word for a record. In exams, these two words mean the same thing.
A field is one column in a table. It stores one type of data.
For example, in a Student table, fields could include StudentID, Name, and DateOfBirth.
An attribute is another word for a field. So field and attribute mean the same thing in this topic.
A primary key is a field, or set of fields, that uniquely identifies each record in a table.
For example, StudentID can be the primary key in a Student table because each student has a different ID.
A good primary key must be unique and should not be empty.
A candidate key is a field, or combination of fields, that could be used as the primary key because it is unique.
A table may have more than one candidate key, but only one is chosen as the primary key.
For example, in a school system, both StudentID and ExamNumber might be unique. Each could be a candidate key, but only one will be chosen as the primary key.
A secondary key is a field used for searching, sorting, or grouping data. It does not need to be unique.
For example, Surname may be used as a secondary key when searching for all students called Khan.
A foreign key is a field in one table that matches the primary key in another table.
It is used to create links between related tables.
For example, TutorID in the Student table may be a foreign key linked to TutorID in the Tutor table.
A relationship is a link between two entities or tables.
Relationships show how records in one table connect to records in another table.
There are three main types.
In a one-to-one relationship, one record in one table is linked to one record in another table.
Example: one person may have one passport, and one passport belongs to one person.
In a one-to-many relationship, one record in one table is linked to many records in another table.
Example: one tutor teaches many students, but each student has one tutor.
This is one of the most common types of relationship.
In a many-to-many relationship, many records in one table can link to many records in another table.
Example: one student can study many courses, and one course can have many students.
This kind of relationship cannot be represented directly by just placing one foreign key in one table. It usually needs a link table.
For example:
The StudentCourse table stores StudentID and CourseID to connect the two tables.
Referential integrity means the links between tables must stay valid.
This means a foreign key value must match a real primary key value in the related table.
For example, if a Student table contains TutorID 7, then TutorID 7 must actually exist in the Tutor table.
This prevents broken links and incorrect data.
Indexing is a method used to make searching faster.
An index stores key values in an ordered way so the database can find records more quickly.
Without indexing, the system may need to check many records one by one. With indexing, searching becomes more efficient, especially in large tables.
Sign in to view full notes