1.3.2

Question 1

database

Answer 1

an organised collection of data that allows for easy:
Adding
Modification
Deletion
Searching

Question 2

relational database

Answer 2

2 databases that are linked together to share data

Question 3

flat file

definition

Answer 3

a single table
can be created very easily using either database or spreadsheet software and is often saved as a CSV file
(used for small and normally personal use details)

Question 4

flat file

cons

Answer 4

Inefficient and normally has repeated data so:
Take up unnecessary space
Be slow to query
Become difficult to maintain

Question 5

entity relationship diagram

Answer 5

1:1 (one-to-one)
1:M (one-to-many)
M:M (many-to-many)

— 1:1
/
— 1:M
\
\ /
— M:M
/ \

Question 6

primary key

Answer 6

a unique identifier

Question 7

composite primary key

Answer 7

2 or more fields combined

Question 8

secondary key

Answer 8

a second field to be indexed (can have repeats)
often used as a search

Question 9

foreign key

Answer 9

a relational link
(the same on both databases but one has to be a primary key)

Question 10

indexing

Answer 10

keeping an index of the primary keys so they can be searched for quickly
each index has the position of its primary key to save time

Question 11

capturing data

Answer 11

typically done manually and then has to be read by a computer (normally using a data capture form)
using Optical Character Recognition (OCR)
or
using Optical Mark Recoignition (OMR)
or
chip and pin/barcodes/qr codes/etc…
can now be filled in on a computer

Question 12

managing data

Answer 12

use SQL (DML) or DBMS to change the data

Question 13

selecting data

Answer 13

use SQL (QBE) to choose data to see

Question 14

exchange data

Question 15

normalisation

Answer 15

splitting tables in a database and arranging the data to move it from 1NF 🡺 2NF 🡺 3NF

Question 16

first normal form

Answer 16

All field names must be unique
Values in fields should be from the same domain
Values in fields should be atomic
No two records can be identical
Each table needs a primary key

Question 17

second normal form

Answer 17

The data is already in 1NF
Any partial dependencies have been removed

Fix any M:M relationships created as a result

partial dependency means one or more of the fields depend on only part of the primary key
this issue can arise if the primary key is a composite key comprised of more than one field

Question 18

third normal form

Answer 18

The data is already in 2NF
Any transitive dependencies have been removed (remove any non-key dependencies)

non-key dependency in one where the value of a field is determined by the value of another field that is not part of the primary key

Question 19

SQL

Answer 19

Structured Query Language
allows for the fast and efficient retrieval, deletion, queries, definition, control and manipulation of data using a simple set of commands

Question 20

SQL - QBE

Answer 20

Query by Example
the first graphical query language, making use of visual tables
allows the user would enter commands and conditions

Question 21

SQL - DML

Answer 21

Database Manipulation Language
easy ways to:
Add new data
Edit/modify existing data
Delete data

Question 22

SQL - DBMS

Answer 22

DataBase Management System
hides the underlying structure of the data and ensures it remains integral by:
Preventing the creation of duplicate primary keys
Enforcing validation rules
Providing secure access
Providing encryption
Providing program data independence
Managing multiple users

Question 23

referntial integrity

Answer 23

refers to the accuracy and consistency of data within a relationship

Question 24

transaction processing

Answer 24

Any information processing that is divided into individual, indivisible operations (transactions)
Each transaction must succeed or fail as a complete unit (can’t be partially complete)

Question 25

transaction processing - CRUD

Answer 25

Create 🡺 INSERT/CREATE
Read 🡺 SELECT
Update 🡺 UPDATE
Delete 🡺 DELETE
All relational databases have certain base functionality

Question 26

A.C.I.D

Answer 26

Atomicity
Consistency
Isolation
Durability
ensure data integrity, transaction processing in all DBMSs

Question 27

A.C.I.D - A

Answer 27

Atomicity - A change to a database is either completely performed or not at all

Question 28

A.C.I.D - C

Answer 28

Consistency - Any change in the database must retain the overall state of the database

Question 29

A.C.I.D - I

Answer 29

Isolation - A transaction must not be interrupted by another transaction. The transaction must occur in isolation so other users or processes cannot access the data concerned

Question 30

A.C.I.D - D

Answer 30

Durability - Once a change has been made to a database, it must not be lost due to a system failure.
by making sure the DBMS writes the effects of transactions immediately back to permanent secondary storage

Question 31

record locking

Answer 31

record(s) being affected by a transaction are locked, effectively placing them in a read-only state
When the transaction is fully completed will the lock be removed
To stop multiple people trying to change the same thing (transactions being interrupted by other transactions)

Question 32

redundancy

Answer 32

repetition of the same data

Question 33

table

database

Answer 33

entity
file

Question 34

records

database

Answer 34

rows
tuples

Question 35

field

database

Answer 35

columns
attributes

Question 36

1:1

database relationship example

Answer 36

one student has one planner; or
one student planner belongs to one student.

Question 37

1:M

database relationship example

Answer 37

one tutor group contains many students; or
many students belong to one tutor group.

Question 38

M:M

database relationship example

Answer 38

one student can be taught by many teachers; or
one teacher can teach many students.

not considered a good database design

Question 39

SQL example - show everything from all records where the field ‘name’ is ‘algeria’ from a database called ‘world’

Answer 39

SELECT *
FROM world
WHERE name = “Algeria”

Question 40

SQL example - show the ‘continent’ from all records where the field ‘area’ is ‘mexico’ or ‘USA’ from a database called ‘globe’

Answer 40

SELECT continent
FROM globe
WHERE area = “mexico” OR area = “USA”

Question 41

SQL example - show everything from all records where the field ‘age’ is 2… and height over 1.5m from a database called ‘people’

Answer 41

SELECT *
FROM people
WHERE age LIKE “2%”
AND height > 1.5

Question 42

SQL example - make a new record in a table calle DBtable with value…s and field…s

Answer 42

INSERT INTO <DBtable> (<field1>,<field2>,<…>)
VALUES (<value1>,<value2>,<…>)</value2></value1></field2></field1></DBtable>

Question 43

SQL example - make a DELETE command that removes all records from ‘earth’ table with ‘population’ larger than 10,000,000

Answer 43

DELETE FROM earth
WHERE population > 10000000

Question 44

SQL example - change the ‘distance’ to 2000m where ‘school’ is edgbarrow in the tables ‘schools’

Answer 44

UPDATE schools
SET distance= 2000
WHERE school= “Edgbarrow”

Question 45

SQL example - join 2 tables: ‘group’ and ‘student’ with the common field of tutor with all students in 10V

Answer 45

JOIN <student>
ON <group>.<tutor> = <student>.<tutor>
WHERE student.tutor = “10V”</tutor></student></tutor></group></student>

Question 46

SQL example - delete the whole of a table called information

Answer 46

DROP TABLE information