Databases Flashcards

1
Q

what is a database

A

An organised collection of data enabling efficient:
- Adding
-Modification
-Deleting
-Searching of data

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

Primary key

A

Field that holds unique value for each record in a database.

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

Foreign key

A

A field that links to tables together.

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

Secondary key

A

A unique identifier which is indexed to allow fast searching

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

Composite primary key

A

2 or more attributes uniquely identifying a record.

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

Flat file database

A

Contains one table. Easy to set up and maintain, but can be inefficient and have repeated data

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

Referential integrity

A

Ensures all foreign keys represent a valid and existing primary key in parent table

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

SQL to extract field

A

SELECT …
FROM …
WHERE…
ORDER BY….
ASC/DESC

WILDCARD (*) = ALL FIELDS

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

SQL to combine rows from different tables

A

JOIN table2
ON table1.attribute = table2.attribute

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

SQL to create a new database

A

CREATE TABLE table1
(
Attribute 1 [Datatype,
Null/not null, Primary key]

)

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

Data types for creating tables

A

CHAR(n)
VARCHAR(n)
BOOLEAN
INTEGER
FLOAT
DATE
TIME
CURRENCY

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

Adding a column

A

ALTER TABLE table1
ADD attribute and data types

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

Deleting a column

A

ALTER TABLE table1
DROP Attribute

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

Modifying a column

A

ALTER TABLE table1
MODIFY COLUMN Attribute NEW DATATYPE

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

Insert a new record

A

INSERT INTO COLUMN 1, COLUMN 2,…
VALUES(Value 1, Value2…)

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

Updating a record

A

UPDATE table1
SET column1 = value1,….
WHERE columnX = value

17
Q

Deleting a record

A

DELETE FROM table1
WHERE columnX = value

18
Q

Normalisation

A

Coming up with the best possible layout for a relational database.

19
Q

Aims of Normalisation

A

No redundancy
Consistent data
Records added/removed without issue
Complex queries can be carried out

20
Q

1st Normal Form

A

No attributes that contain more than 1 value. Fixed by making more records but causes redundancy

21
Q

2nd Normal Form

A

Must be in 1st Normal Form
Must be no partial dependencies (no attributes must depend on part of a composite key.)
Fix partial dependency by creating another table with field which has the partial dependency and field it is dependent on.

22
Q

3rd Normal Form

A

Must be in 2nd Normal Form
Contains no non key dependencies (All attributes must depend on only the primary key)
Fixed by splitting table until each attribute in a table is only dependent on primary key

23
Q

Transaction

A

A single logical operation performed on data (Create, Read, Update, Delete)

24
Q

ACID

A

A set of rules that ensure database validation rules are met.
Atomicity, Durability, Isolation, Consistency

25
Atomicity
A transaction must be completed entirely or not at all.
26
Consistency
A transaction must maintain referential integrity rules
27
Isolation
Simultaneous execution of transactions should produce same result as if done one after another
28
Durability
Once a transaction has been executed it will remain so regardless of circumstances like power cuts.
29
Record Locking and issue
Is a method to counter loss of updates due to simultaneous access to records. A record is locked when a user is editing it until the changes are saved. Issue: If two users need to update the same two records simultaneously, they will end up in a situation where they will wait for each other to stop updating a record, which means records dont become available and users wait endlessly. DEADLOCK.
30
Serialisation
Ensures transactions do not overlap in time so they cannot interfere with each other. One transaction cannot start unless the previous is complete
31
Timestamp ordering
Transactions are given timestamps whenever it starts. If multiple transactions affect the same object the transaction with earliest timestamp is applied first. Each object has a read/write timestamp to ensure transactions are not lost.
32
Commitment ordering
Transactions ordered in terms of their dependencies on each other as well as time of initiation.
33
Types of data capture
Magnetic Ink Character Recognition [used in banks scanning cheques] Optical Mark Recognition [ used in MCQ's for a test] Optical character Recognition [used in forms]