1.3.2 Databases Flashcards
(152 cards)
1
Q
A
2
Q
What is a database?
A
An organized collection of data that allows easy adding, modification, deletion, and searching of data.
3
Q
What benefits do electronic databases offer compared to paper-based ones?
A
Electronic databases are easier and quicker to update, easier to back up and copy, and can be accessed by multiple people simultaneously from different locations.
4
Q
How is data stored in a database?
A
Data is stored in tables (also called entities or files), which contain records (rows or tuples) and fields (columns or attributes).
5
Q
What terms does the exam board use for table
A
record
6
Q
What is a flat file database?
A
A flat file database contains only a single table and is simple to set up, requiring little expertise; often saved as a CSV file.
7
Q
What are typical uses for flat file databases?
A
Storing small amounts of data like contact details, small product databases, or personal collections.
8
Q
What are the drawbacks of flat file databases?
A
They contain a lot of repetitive data, take up unnecessary space, become slow to query, and difficult to maintain as data grows.
9
Q
What is a relational database?
A
A database that splits information into multiple related tables to reduce redundancy and improve efficiency.
10
Q
How do humans instinctively link data across tables in a relational database?
A
By visually matching related data, for example matching a tutor group ID in one table to student records in another.
11
Q
What is a relationship in database terms?
A
A link between tables that allows them to be connected logically.
12
Q
What is a one-to-many relationship?
A
One record in a table relates to many records in another table, e.g., one tutor group has many students.
13
Q
What field is required for a relationship between two tables?
A
A common field (column) that appears in both tables to link them.
14
Q
What is a primary key?
A
A field in a table that uniquely identifies each record.
15
Q
What is a foreign key?
A
A field in one table that refers to the primary key in another table, used to establish relationships.
16
Q
What are the three types of relationships in a relational database?
A
One-to-one, one-to-many, and many-to-many.
17
Q
What is an entity relationship diagram (ERD)?
A
A visual representation of tables (entities) and their relationships using boxes and lines.
18
Q
Give an example of a one-to-one relationship
A
One student has one student planner, or one student planner belongs to one student.
19
Q
Give an example of a one-to-many relationship
A
One tutor group contains many students, or many students belong to one tutor group.
20
Q
Give an example of a many-to-many relationship
A
One student can be taught by many teachers, and one teacher can teach many students.
21
Q
Why is a many-to-many relationship considered poor database design?
A
Because it complicates data structure and should be resolved through normalization.
22
Q
What is the purpose of indexing in a database?
A
To allow quick retrieval of records by maintaining an index of primary keys.
23
Q
What is a secondary key?
A
A field that is indexed besides the primary key, often used for frequent search criteria, like a student’s surname.
24
Q
Why might a secondary key be used instead of the primary key for searches?
A
Because users often remember fields like surnames rather than unique ID numbers.
25
What key question should you be able to answer after watching this video?
What are the key terms associated with databases?
26
What are the four distinct ways to handle data in databases?
Capturing
27
What is capturing data in the context of databases?
It is how we get the data into the database in the first place.
28
How is data often captured using paper-based forms?
A human reads the form and types the information into a computer system manually.
29
What tactics help reduce errors when using paper-based data capture forms?
Clear labels
30
What is Optical Character Recognition (OCR)?
Technology that automatically reads printed text by interpreting the shape of the letters.
31
Where is OCR commonly used?
The post office to read postcodes and route mail
32
What is Optical Mark Recognition (OMR) used for?
Multiple choice tests and lottery tickets to quickly collect data while reducing human error.
33
Name some other automated methods of capturing data from the real world.
Magnetic strips on cards
34
What is SQL?
Structured Query Language
35
What simple SQL commands are commonly used?
SELECT
36
What is Query By Example (QBE)?
A graphical query language that allows users to build queries visually without needing to know SQL syntax.
37
Which database software commonly uses Query By Example concepts?
Microsoft Access and other graphical front-end databases.
38
What common features do SQL and QBE provide?
Specifying tables
39
What is Database Manipulation Language (DML) used for?
Modifying data after initial setup
40
What are some DML commands?
UPDATE
41
What does a Database Management System (DBMS) provide?
An abstraction layer that hides data structure
42
Why is data exchange between systems important?
To share data between different databases and applications like spreadsheets or accounting software.
43
Name two common human-readable data formats for exchanging data.
XML and JSON.
44
What is CSV format?
Comma Separated Values format
45
What are some manual methods of exchanging data?
Using memory sticks
46
What is Electronic Data Interchange (EDI)?
A protocol that allows two systems to communicate and exchange data directly and automatically.
47
Give an example of an EDI use case.
Automatic order placement systems when shop stock runs low.
48
How do exam boards exchange result data with schools?
Automatically via electronic systems so schools can see exam results in their systems on results day.
49
What is a major risk when automating data exchange?
Errors in data can be easily propagated and replicated across multiple systems.
50
What is the key question answered by this video?
How can data be captured and exchanged for databases?
51
What is the topic discussed in the video?
How to normalize a database to third normal form.
52
What is the first step when designing a relational database?
Consider the list of fields and decide which could result in repeating data across multiple records.
53
Why is repeating data problematic in a database?
It takes up additional storage space unnecessarily and worsens as the database grows.
54
How can repeating data issues be prevented?
By splitting the table into two tables and creating relationships between them.
55
What types of relationships exist between tables in a relational database?
One-to-one, one-to-many, and many-to-many.
56
Which type of relationship is not considered good practice in database design?
Many-to-many relationships.
57
What is a primary key?
A field in each record that is guaranteed to be unique and identifies the record.
58
What can be done if no existing field is suitable as a primary key?
Create a composite primary key from two or more fields or add a new unique field.
59
What is database normalization?
The process of arranging data in tables to move through first, second, and third normal forms.
60
How many normal forms should an A-Level student understand?
Three: first, second, and third normal forms.
61
What are the five rules a table must follow to be in first normal form (1NF)?
Field names should be unique, values in fields should be from the same domain, values should be atomic, no two records can be identical, and each table needs a primary key.
62
What does it mean for field values to be atomic?
Each field should contain a single value, not multiple values.
63
Why should all field names be unique?
To avoid confusion when retrieving or performing actions on the stored data.
64
What problem was identified with the 'lecturer' field in the example?
It contained two types of data (initials and full name), violating the same domain rule.
65
How was the lecturer field fixed?
By splitting it into two fields: lecturer initials and lecturer name.
66
Why might the 'name' field also violate normalization rules?
Because it contains first name and surname combined, which are different data types.
67
What does rule four in 1NF require?
No two records can be identical.
68
What issue arises when adding a 'student number' field as a primary key?
A single student could have multiple student numbers for different records, so it isn't unique per student.
69
What is a composite primary key?
A primary key made from a combination of two or more fields that together uniquely identify a record.
70
What is a potential problem with using 'name' and 'course number' as a composite primary key?
Two students with the same name cannot take the same course.
71
How can adding 'date of birth' to the composite key help?
It makes the composite key more unique by distinguishing students with the same name and course.
72
What two rules must be met for second normal form (2NF)?
The data must already be in first normal form and all partial dependencies must be removed.
73
What does it mean to remove partial dependencies?
Ensuring that each table serves its own purpose and that non-key fields depend on the whole primary key, not just part of it.
74
What should be done if splitting tables creates many-to-many relationships?
Fix those relationships by creating junction tables or redesigning the schema.
75
What is meant by 'dependency' in a database table?
That a field's value depends on another field, usually the primary key.
76
What is the role of a primary key in fetching records?
It uniquely identifies each record, allowing retrieval of the correct data even if other fields are the same.
77
What is the first rule when normalizing to First Normal Form (1NF)?
All field names must be unique.
78
Are the values in each field in the example table from the same domain?
Yes
79
What problem was found with the answer field regarding 1NF?
The answer field was not atomic because it stored multiple answers.
80
How was the answer field fixed to meet 1NF?
It was made fully atomic by storing only one answer per record.
81
What is the fourth rule of 1NF?
No two records can be identical.
82
What primary key was chosen for the first normal form table?
A composite primary key of topic + question + answer.
83
What must be removed to achieve Second Normal Form (2NF)?
Partial dependencies must be removed.
84
What partial dependencies were found in the example?
Question depends only on topic
85
How was the topic field handled to move to 2NF?
It was moved into its own table with a new primary key topic_id.
86
What fields does the question table contain in 2NF?
Question and difficulty fields
87
What fields were moved into the answer table?
Answer
88
What relationship is shown in the entity relationship diagram after 2NF?
One topic has many questions; one question has many answers.
89
What must be removed to achieve Third Normal Form (3NF)?
Transitive dependencies must be removed.
90
What transitive dependency was found?
Marks depend on whether the answer is correct
91
How was the transitive dependency fixed?
The correct field was moved into its own table with a primary key mark_id
92
What does the final 3NF entity relationship diagram show?
One topic has many questions; one question has many answers; one mark can apply to many answers.
93
How are primary and foreign keys represented in the example?
Primary keys are underlined; foreign keys link tables together.
94
What notation style was used to represent the tables in the example?
Table name in capitals followed by fields in brackets
95
What is the key rule of 3NF regarding field dependencies?
All fields must depend on the key
96
What does SQL stand for?
Structured Query Language
97
When was SQL developed?
In the 1970s
98
What is the primary purpose of SQL?
To allow fast and efficient retrieval, deletion, and manipulation of data in relational databases using a simple set of commands
99
Is SQL a declarative or procedural language?
SQL is primarily a declarative language
100
What does declarative mean in the context of SQL?
It expresses what needs to be achieved rather than how to achieve it
101
What are the main scopes of SQL?
Query data, manipulate data, define data, and control data access
102
Which SQL commands are you expected to be familiar with for exams?
Commands listed on the screen (select, insert, delete, update, etc.); other commands will be introduced in exam questions
103
How is information stored in a database?
In records
104
How do you access records in SQLite using Python?
By opening a connection to the database and issuing SQL commands to find the data
105
Give an example of a simple SQL select statement from the video.
"SELECT population FROM world WHERE name = 'Germany'"
106
What does the SQL command SELECT do?
It retrieves specific fields from a table based on given criteria
107
How do you select multiple fields in SQL?
List the fields separated by commas after SELECT
108
What does the * (asterisk) symbol mean in a SELECT statement?
It selects all fields from the table
109
How would you select all fields for countries starting with 'A' and with a population over 1 million?
SELECT * FROM world WHERE name LIKE 'A%' AND population > 1000000
110
What SQL command orders the output shown to the user?
ORDER BY
111
How would you select countries starting with 'A' or 'B' using SQL?
SELECT name, population FROM world WHERE name LIKE 'A%' OR name LIKE 'B%'
112
What is nested selection in SQL?
Using a SELECT statement inside another SELECT statement
113
Explain the nested select example from the video.
"SELECT name FROM world WHERE population > (SELECT population FROM world WHERE name = 'Algeria')"
114
What is the SQL command to insert new records into a table?
INSERT INTO
115
What is the syntax for inserting data in SQL?
INSERT INTO table_name (fields) VALUES (values)
116
How do you write strings in SQL commands?
Strings are enclosed in quotation marks
117
How do you delete records from a table?
Using DELETE FROM table_name WHERE condition
118
Write a delete command to remove all records with a population larger than 10 million.
"DELETE FROM world WHERE population > 10000000"
119
What is the SQL command to update records?
UPDATE
120
What is the syntax for updating data in SQL?
UPDATE table_name SET field1 = value1, field2 = value2 WHERE condition
121
Write an update command to change the population to 2 million and name to Wonderland where the capital is Emerald City.
"UPDATE world SET population = 2000000
122
How do you perform an SQL query involving more than one table?
By using the JOIN keyword
123
Explain the JOIN syntax.
"JOIN table_name ON table1.field = table2.field"
124
Give an example of a JOIN query from the video.
"SELECT student.surname
125
What is the SQL command to delete an entire table?
DROP TABLE
126
What does the DROP TABLE command do?
Deletes the entire table and all information within it
127
Why should you be careful when using DROP TABLE?
Because it results in complete loss of all data stored in the table
128
What is the main topic discussed in the video?
Referential integrity in databases.
129
Why do large databases need to support multiple users?
Because they hold vast amounts of information and need to allow simultaneous access.
130
Give examples of organizations that use large databases with millions of records.
The NHS and police.
131
What are some different access rights users can have in a database?
Some can only query and run reports
132
Why is it important that multiple transactions do not cause a database to become inconsistent?
Because inconsistency leads to loss of accuracy and potential corruption of the database.
133
What is data integrity?
The maintenance and consistency of data in a data store ensuring it reflects the reality it represents.
134
What ensures data integrity during any type of transaction in a database?
The database management system ensures data consistency.
135
What is referential integrity?
It is the accuracy and consistency of data within a relationship between tables in a relational database.
136
In the bank example
what two tables are used to explain referential integrity?
137
What problem arises if an employee is deleted from one table but not the related tables?
Orphaned entries are left
138
How does a cascade delete restraint help maintain referential integrity?
It automatically deletes related records in other tables when a record in the primary table is deleted.
139
Can referential integrity also prevent adding invalid records?
Yes
140
Why must cascade delete restraints be used with caution?
Because deleting a record could unintentionally delete many related records
141
What example is given to illustrate a potential problem with cascade delete?
Deleting tutor group 10e would also delete all students in that group
142
What key question should viewers be able to answer after watching the video?
What do we mean by the term referential integrity?
143
What is transaction processing in databases?
Transaction processing is any information processing divided into individual, indivisible operations called transactions, each of which must succeed or fail as a complete unit.
144
What does CRUD stand for in relational databases?
Create, Read, Update, Delete.
145
What SQL statements correspond to CRUD operations?
INSERT/CREATE (Create), SELECT (Read), UPDATE (Update), DELETE (Delete).
146
What rules ensure data integrity in transaction processing?
The ACID rules: Atomicity, Consistency, Isolation, and Durability.
147
What does the 'A' in ACID stand for and what does it mean?
Atomicity: A change to a database is either completely performed or not at all. There should be no partially completed transactions.
148
What does the 'C' in ACID stand for and what does it mean?
Consistency: A transaction must retain the overall state of the database. For example, transferring funds between accounts should not cause money to disappear.
149
What does the 'I' in ACID stand for and what does it mean?
Isolation: A transaction must not be interrupted by another transaction. This is ensured by record locking in the DBMS.
150
What is record locking in isolation?
A technique where records affected by a transaction are locked to prevent access by others until the transaction is complete.
151
What does the 'D' in ACID stand for and what does it mean?
Durability: Once a transaction is complete, its effects must be saved to permanent storage and not lost due to system failure.
152
How is durability achieved in databases?
By writing the effects of a transaction immediately to permanent secondary storage instead of volatile memory.
