Database Concepts (Cambridge (CIE) A Level Computer Science): Flashcards
Exam code: 9618
1/620Still learning
Know0
- FrontDatabase Models
Define file-based system.
Was this flashcard helpful?
Cards in this collection (62)
Define file-based system.
A file-based system is any system where data is stored in separate files, such as text files or spreadsheets.
Why does a file-based system have no central control?
Each application or department has its own files, which are often standalone, so there is no shared structure.
Define data redundancy.
Data redundancy is when the same data is stored in multiple files, leading to duplication.
Define data inconsistency.
Data inconsistency is when one file is updated but others are not, which makes the data unreliable.
Why are file-based systems difficult to update?
Changes must be made in several places, which increases the time taken and the risk of error.
Name three limitations of a file-based system.
Limitations include data redundancy, data inconsistency, lack of data integrity, poor security, difficulty updating, limited scalability, no central control and difficulty managing relationships.
True or False?
File-based systems are suitable for large volumes of data with complex relationships.
False.
File-based systems have limited scalability and cannot easily link related data across files.
Define flat file database.
A flat file database stores all of its data in a single table.
Define relational database.
A relational database organises data into multiple tables and uses keys to connect related data.
What is the difference between a flat file and a relational database?
A flat file stores everything in one table, which causes redundancy. A relational database uses multiple tables linked by keys, which reduces redundancy and is easier to maintain.
Give one advantage of a flat file database.
A flat file database is simple and easy to understand.
How does a relational database prevent inconsistent data?
Each piece of data is stored only once, so changing it updates it across the entire database.
A relational database uses keys to connect related data, which reduces data .
A relational database uses keys to connect related data, which reduces data redundancy.
Define entity.
An entity is a real-world object or concept that data is stored about, for example a student or a book.
A is a collection of data about an entity, organised in rows and columns.
A table is a collection of data about an entity, organised in rows and columns.
Define record (tuple).
A record is a single row in a table, representing one instance of an entity.
Define field (attribute).
A field is a single column in a table, storing one piece of data about the entity.
Define primary key.
A primary key is a unique identifier for each record in a table.
Define candidate key.
A candidate key is a field, or combination of fields, that could be used as a primary key.
Define secondary key.
A secondary key is a field used for searching or sorting, but which is not necessarily unique.
True or False?
A secondary key must be unique.
False.
A secondary key is used for searching or sorting and is not necessarily unique. It is the primary key that must uniquely identify each record.
What is the difference between a primary key and a foreign key?
A primary key uniquely identifies each record in its own table. A foreign key links to the primary key in another table to create a relationship.
Define referential integrity.
Referential integrity ensures that foreign keys match a primary key in the related table, which prevents broken links.
Define indexing.
Indexing is a technique to speed up searching in a table by creating an ordered list of key fields.
Name the three types of relationship between entities.
One-to-one, one-to-many and many-to-many.
Define entity-relationship (E-R) diagram.
An E-R diagram is a diagram that represents the entities (tables) that will be in a database and the relationships between those entities.
Define entity.
An entity is something worthy of capturing and storing data about, for example students, orders, products, courses or customers.
What do entities become in a relational database?
Entities become tables in a relational database.
Relational databases store different entities in tables.
Relational databases store different entities in separate tables.
What determines how tables are linked in a relational database?
Linking tables depends on the relationships between the entities.
What is another name for the type of a relationship?
The type of a relationship is sometimes called its degree.
How are entities drawn on an E-R diagram?
Entities are drawn as boxes, with the name of the entity inside the box.
What notation is used to draw relationships on an E-R diagram?
Relationships are drawn using crow's feet notation.
What is the relationship between a customer and an order?
It is one-to-many. One customer can make many orders, but each order relates to one customer.
What is the relationship between an order and a product?
It is many-to-many. An order can contain many products, and a product can appear on many orders.
True or False?
One-to-one relationships are common in databases.
False.
One-to-one relationships do exist, but they are not very common in databases.
The three types of relationship between entities are one-to-one, one-to-many and .
The three types of relationship between entities are one-to-one, one-to-many and many-to-many.
Define normalised design.
A normalised design is a database structure that organises data efficiently, eliminates unnecessary duplication, ensures data integrity and supports easy updates and accurate relationships.
Name three things you must be able to do when producing a normalised design.
Identify repeating groups or duplicated data, break the data into separate related tables, assign primary and foreign keys, show the database in 1NF, 2NF and 3NF, and clearly show the relationships between tables.
What usually indicates that more than one table is needed?
Repeated or duplicated values in the fields usually indicate the need for more than one table.
Why does every table need a primary key?
Every table needs a unique identifier for its records.
What is the purpose of a foreign key in a normalised design?
A foreign key links related tables and ensures referential integrity.
In a normalised design, a link table is used to handle a relationship.
In a normalised design, a link table is used to handle a many-to-many relationship.
True or False?
A normalised design can only be produced from a table of unnormalised data.
False.
A normalised design may be produced from a written description of a system, a table of unnormalised data, or a set of existing flat tables.
What order should the normal forms be applied in?
Apply 1NF first, then 2NF, then 3NF.
What four conditions must a table meet to be in first normal form?
It must contain atomic values, have no repeating groups, have unique column names, and have a unique identifier (primary key).
Define atomic value.
An atomic value is a single, indivisible value held in a column.
Why is a customers table with a single 'name' field not in first normal form?
The whole name is stored in one field, so it is not atomic. It should be split into forename and surname fields.
True or False?
A table with a column containing a list of values can be in first normal form.
False.
Columns must not contain arrays or lists of values, because a 1NF table must have no repeating groups.
For a table to be in first normal form, each column must have a name within the table.
For a table to be in first normal form, each column must have a unique name within the table.
Why must each row in a first normal form table have a unique identifier?
So that each row can be distinguished from the other rows in the table.
What must a table satisfy to be in second normal form?
It must fulfil all 1NF requirements, have full functional dependency, and have no partial dependencies.
Which tables does second normal form apply to?
Second normal form only applies to tables that have a compound (composite) primary key.
Define non-prime attribute.
A non-prime attribute is an attribute that is not part of the primary key.
Define partial dependency.
A partial dependency is when a non-prime attribute depends on only part of a composite primary key.
A Course table has the compound primary key (Course, Date) and also holds Course Title.
Why is this not in second normal form?
Course Title depends only on the Course code, not on the whole key of Course and Date, so it is a partial dependency.
How is a partial dependency removed?
Separate tables are created for the partially dependent attributes.
What must a table satisfy to be in third normal form?
It must fulfil all 2NF requirements and have no transitive dependencies.
Define transitive dependency.
A transitive dependency is when a non-prime attribute depends on another non-prime attribute rather than on the primary key.
True or False?
In third normal form, a non-key field may depend on another non-key field.
False.
Each non-prime attribute must depend solely on the primary key, not on other non-prime attributes.
A Film table holds FilmID, Title, Certificate and Description.
How is it put into third normal form?
The transitive dependency is removed by separating Description into its own table, so the Film table only stores fields that directly depend on FilmID. Certificate is then used as a foreign key to maintain the relationship.
How are tables linked after a transitive dependency has been separated out?
They are linked using a foreign key.
Sign up to unlock flashcards
or