A Level Computer Science > Databases > Flashcards

Databases Flashcards

(68 cards)

Study These Flashcards
1
Q

what is a database?

A

They are structured sets of data. They are organised in such a way that they can be easily managed, accessed, updated and analysed.
Eg. a relational database - made up of tables

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
2
Q

what is a flat file?

A

The simplest form of a database - it is a single file, normally organised in a table structure with rows and columns

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
3
Q

how would you write an entity description?

A

Entity1 (Attribute1, Attribute2, … )

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
4
Q

what is a relational database?

A

a set of tables whose records are linked by certain fields. Each table contains data on one entity. the tables can be linked together using foreign keys (a relationship)

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
5
Q

what is the purpose of an entity identifier?

A

it is used to uniquely identify the entity - it is the primary key in a relational database

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
6
Q

What is a record?

A

All the fields relevant to an entity - kind of like a row in a table

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
7
Q

what is an entity?

A

it is a category of object, person, event or thing of interest to an organisation, about which data is to be recorded

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
8
Q

what is a field/attribute?

A

it is a property or characteristic of an entity - it is kind of like a column in a table

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
9
Q

what is the primary key?

A
  • it is an entity identifier that uniquely identifies records in an entity. Data in the entity will be sorted by this by default
  • denoted by being underlined in the entity description: eg. Dentist (DentistID, Title, Firstname, Surname, Qualification)
How well did you know this?
1
Not at all
2
3
4
5
Perfectly
10
Q

what is a composite primary key?

A
  • its a primary key made up of multiple fields - used to maintain uniqueness
  • eg. a customer order with multiple order lines would have OrderNumber + OrderLine as composite primary key
How well did you know this?
1
Not at all
2
3
4
5
Perfectly
11
Q

what is a secondary key?

A
  • any other fields in the table that aren’t the primary key
  • makes sure that records in the database remain searchable since a customer/user may not know the relevant primary key value
How well did you know this?
1
Not at all
2
3
4
5
Perfectly
12
Q

what is a foreign key?

A
  • its an attribute that creates a join between tables
  • the attribute is common to both tables
  • foreign key in one table = primary key in the table its linked to
  • denoted by being in italics - eg. Patient (PatientID, Title, Firstname, Surname, Address, Telephone, DentistID)
How well did you know this?
1
Not at all
2
3
4
5
Perfectly
13
Q

How can entities be related + how are they represented in an entity relationship diagram?

A

One-to-one:
- eg. husband + wife, country + prime minister
- shown by 2 rectangles (for entities) joined by one straight line

One-to-many:
- eg. school + pupils, mother + child
- shown by 2 rectangles joined by a straight line on one side and birds legs on the other

Many-to-many:
- avoided where possible (eg, through normalisation)
- eg. actors + films, products + components, student + course
- represented by 2 rectangles joined by birds legs on either end

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
14
Q

what is an entity relationship diagram?

A

it is a diagramatic way of representing the relationships between entities in a database

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
15
Q

how would you remove a many-to-many relationship?

eg. you have 2 tables:
- student (studentID, name, address)
- course (courseID, subject, level)

A

create a middleman table eg:
- student (studentID, name, address)
- enrolment (_studentID_, _courseID_)
- course (courseID, subject, level)

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
16
Q

what is referential integrity?

A

no foreign key in one table can reference a non-existent record in another table - eg. can’t add a subscription for a customer if the customer ID doesnt exist in a record

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
17
Q

what is relational database structure?

A
  • tables in the database = relations
  • one database will have one or more relations
  • relation has rows - aka records
  • each record has fields/attributes - like columns
How well did you know this?
1
Not at all
2
3
4
5
Perfectly
18
Q

how are databases indexed?

A
  • records automatically indexed by primary key
  • can also be indexed by any other secondary key (secondary index)
How well did you know this?
1
Not at all
2
3
4
5
Perfectly
19
Q

what is normalisation?

A
  • it is the process used to come up with the best possible database design
  • 3 stages: 1st normal form (1NF), 2nd normal form (2NF), 3rd normal form (3NF)
How well did you know this?
1
Not at all
2
3
4
5
Perfectly
20
Q

what should a normalised database look like?

A
  • no data is unnecessarily duplicated
  • data is consistent throughout the database
  • table structure should be flexible enough for you to enter as many or as few items as required
  • structure should allow as complex queries to be made
How well did you know this?
1
Not at all
2
3
4
5
Perfectly
21
Q

what are the properties required of a database in first normal form?

A
  • no repeating attributes/groups of attributes
  • must have a primary key
  • atomic - only one data item in one field
How well did you know this?
1
Not at all
2
3
4
5
Perfectly
22
Q

how can you make a many to many relationship into 1NF?

A

use a link table/middleman table

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
23
Q

what are the features of a database in second normal form?

A
  • already in 1NF
  • no partial dependencies - one or more attributes depends on only part of the primary key (only works if it is a composite primary key)
How well did you know this?
1
Not at all
2
3
4
5
Perfectly
24
Q

what are the features of a database in third normal form?

A
  • already in 2NF
  • contains no non-key dependencies - the vvalue of an attribute is determined by the value of another attribute which is not part of the key
  • “all attributes are dependent on the key, the whole key, and nothing but the key”
How well did you know this?
1
Not at all
2
3
4
5
Perfectly
25
what are the advantages of a normalised database?
- no data redundancy (data that appears in more than one table) - less inefficinecy + inconsistencies in data - data integrity is maintained - no duplication of data + only need to modify 1 table if a change needs to be made - smaller tables with fewer fields = faster searching, sorting + indexing - saves storage space- data only held once - can't delete records accidentally on the one side of a one to many relationship
26
what is SQL?
- stands for Structured Query Language - declarative language - used to query + update tables in a relational database
27
how is data for a database captured?
- survey results may be manually inputted - magnetic ink character recognition (MICR) on bank cheques - optical mark recognition (OMR) used on multiple choice tests - Optical character recognition (OCR)
28
how is data exchanged?
- using EDI - electronic data interchange - enables data transfer from one computer to another without using human interaction
29
what is the SELECT statement used for?
to extract a collection of fields from a given table(s)
30
what is the syntax for the SELECT statement?
`SELECT` (the fields to be displayed - * if all) `FROM` (list the table(s) data will come from) `WHERE` (search criteria, the conditions) `ORDER BY` (list the fields that results are to be sorted on, ascending by default)
31
what is the wild card operator syntax?
`LIKE "comp%"` the percentage is the important bit - looks for anything in the database starting with comp followed by anything
32
in the WHERE clause, how do you determine a range?
`BETWEEN ... AND` including the 2 values that define the limit
33
in the WHERE clause, what does the IN operator do?
specify multiple possible specific values for a column for the record to be considered
34
how do you reference an empty field?
`IS NULL`
35
how do you write a data in SQL?
`#DD/MM/YYYY#` you need to enclose it in the hashes
36
when are semicolons used?
at the end of each SQL statement - the standard way to separate them DONT put it within a statement
37
what orders can be specified using ORDER BY?
`ASC` - ascending `DESC` - descending
38
what is the syntax used to extract and combine data from 2 or more tables (without using join)?
```SELECT table1.fieldName, table2.fieldName, ... FROM table1, table2, ... WHERE (table1.fieldName1 = table2.fieldName1)```
39
what is the syntax used to extract and combine data from 2 or more tables (using join)?
```SELECT table1.fieldName, table2.fieldName, ... FROM table1 JOIN table2 ON table1.fieldName1 = table2.fieldName1 WHERE ...```
40
how do you define a table using SQL?
using `CREATE TABLE` ```CREATE TABLE ( column1 DATATYPE, column2 DATATYPE, column3 DATATYPE, ... )```
41
what are the most commonly used data types when creating a table with SQL?
- `CHAR(n)` - character string of fixed length n - `VARCHAR(n)` - character string of variable length, max n - `BOOLEAN` - TRUE or FALSE - `INTEGER, INT` - integer - `FLOAT(a,b)` - number with floating decimal point - a = max no of digits before decimal point, b = max no of digits after decimal point - `DATE` - stores day, month, year values - `TIME` - stores hour, minute, second values - `CURRENCY` - formats numbers in currency used in your region
42
how to add a column using SQL?
```ALTER TABLE table ADD column1 DATATYPE```
43
how to delete a column using SQL?
```ALTER TABLE table DROP COLUMN column1```
44
how to change data type of a column in SQL?
```ALTER TABLE table MODIFY COLUMN column1 NEWDATATYPE```
45
how to create a foreign key using SQL?
done during CREATE TABLE ```FOREIGN KEY column1 REFERENCES table2(column1)```
46
how to set the primary key in SQL?
done during CREATE TABLE: ```PRIMARY KEY (column1, ...)```
47
how to add a record to a table in SQL?
```INSERT INTO table1 (column1, column2 ...) VALUES (value1, value2 ...)```
48
how to update a record in a table in SQL?
```UPDATE table1 SET column1 = value1, column2 = value2, ... WHERE columnX = value```
49
how to delete a record in a table in SQL?
```DELETE FROM table1 WHERE columnX = value```
50
what are some methods of capturing data to be input into a database?
- transcribing data from a form already filled in - eg. market researcher filling in forms on high street - magnetic ink character recognition (MICR) - eg. cheques - bank number, account number + cheque number are printed in special magnetic ink, clerks manually fill out amount of money - optical mark recognition (OMR) - eg. lottery tickets, multiple choice exams - optical character recognition (OCR) - eg. other types of form - smart card scanners - eg. airport scanners for your passport - barcode reader/scanner
51
how can data be selected and managed?
- before adding to database - select based on whether it matches specific criteria - eg. speed cameras only photograph vehicles going above the threshold - in the database - SQL used to select data from tables which match required criteria - selected data -> produce reports, send letters (post/email), automatic reorder of stock items, records added, updated, deleted
52
How can data be exchanged?
* **EDI**: Electronic Data Interchange * Transfers data between computer systems via the internet without human intervention. * Uses standardized message formats. * Transaction software processes information; receiving software looks up details. * Applications include exam boards sending results to schools and insurance companies checking driver's licenses.
53
what is a transaction?
a single logical operation on data eg. a customer booking a cinema ticket and making online payment using a credit card is a single transaction even though it has multiple parts
54
what are the ACID principles?
- atomicity - consistency - isolation - durability
55
what is the purpose of the ACID principles?
- a set of properties that guarantee transactions will be processed reliably - should ensure that you can't complete only part of a transaction - eg. book a cinema ticket without paying for it
56
what is atomicity?
- a transaction must be processed in its entirety or not at all - in any situation, including power cuts or hard disk crashes, it shouldnt be possible to process only part of a transaction
57
what is consistency?
- no transaction can violate any of the defined validation rules for maintaining the integrity of the database - upholds all referential integrity rules specified between linked tables - eg. you can't add a mark in the results table for a student not in the students table in the database
58
what is isolation?
- concurrent execution of transactions leads to the same results as if the transactions were processed one at a time
59
what is durability?
- once a transaction has been committed, it will remain so, even in the event of a power cut - as each part of a transaction is completed, it is held in a buffer on disk until all parts are completed - then changes to database tables are made - eg. if a cinema ticket is in the process of being bought, it shouldnt be possible for the number of seats sold to be updated but the customer's debit card not processed.
60
what are some issues with multi-user databases?
- if multiple users simultaneously update a database table, one of the updates may be lost unless there are protective measures - when an item is updated, the whole block that the record is in copied into the user's workstation local memory - when changes are saved, the block is rewritten to the file server
61
what are some protective measures for multi-user databases to prevent data loss?
- record locking - serialisation - timestamp ordering - committment ordering - redundancy
62
what is record locking?
- prevent simultaneous access to objects in a database to prevent updates being lost or inconsistencies in data - simple: record is locked when a user edits/updates it - access denied for everyone else until the transaction is completed or cancelled
63
what is the problems with record locking?
- deadlock - 2 users might be trying to update 2 records so neither can proceed - can be resolved using serialisation, timestamp ordering or commitment ordering
64
what is serialisation?
- technique to ensure transactions dont overlap in time so they cant interfere with each other or lead to updates being lost - a transaction cant start until the previous one is finished - can be implemented with time stamp ordering
65
what is timestamp ordering?
- if 2 transactions affect the same object, the transaction with the earlier timestamp should be applied first - every object in database has a read timestamp + write timestamp - updated every time database is read/written
66
how does timestamp ordering work?
- transaction starts - read data from a record -> read timestamp set - needs to write updated data to the record - check read timestamp - if this is not the same as the value saved from the start of transaction, it will know another is also happening on the record
67
what is commitment ordering?
- serialisation technique - ensures transactions are not lost when 2 or more users are simultaneously trying to access the same database object - transactions ordered in terms of dependencies on each other + time initiated - prevents deadlocks by blocking one request after another is completed
68
what is redundancy?
- some organisations eg. banks, airports, hospitals maintain 2 or 3 identical systems in different geographical locations - every transaction is written into 2/3 storage facilities - built in hardware redundancy - protects against data loss from power failure or other disasters - if one system fails, the backup system automatically takes over and processing can continue
A Level Computer Science
flashcards
Decks in class (17)
# Cards
Brainscape helps you reach your goals faster, through stronger study habits.
© 2026 Bold Learning Solutions. Terms and Conditions