1.3.2 Databases Flashcards

You may prefer our related Brainscape-certified flashcards:
1
Q

What is an entity?

A

A category of object, person, event or thing about which data is to be recorded.

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

What is a flat file database?

A

A single file where all data about a single entity is held.

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

What is a database?

A

A structured, persistent store of data, organised so that it can be easily accessed, managed and updated.

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

What does it mean that a database is structured?

A

The data is organised in a logical way to make processing simple.

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

What does it mean that a database is persistent?

A

The data remains for as long as is required.

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

Give some benefits of using databases.

A

Easy and fast to:

  • Access data
  • Update data
  • Search for data
  • Present data in a usable way
How well did you know this?
1
Not at all
2
3
4
5
Perfectly
7
Q

What are flat file databases suitable for?

A

Small amounts of data

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

Why are flat file databases unsuitable for large amounts of data?

A

Because they are cumbersome to update and prone to data redundancy.

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

What is data redundancy?

A

The unnecessary duplication of data in a database.

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

Why is data redundancy bad?

A

When updates occur, all instances of a data item must be changed.

If not all instances are updated properly, this can lead to errors when processing the data.

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

What is the template of an entity description?

A

Entity(Attribute, Attribute, Attribute…

e.g. Teacher(Title, FirstName, Surname, Subject)

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

How do we represent a primary key in an entity description?

A

Underline the attribute.

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

What is a primary key?

A

A unique identifier for each record of a particular entity.

Indexed.

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

What sort of databases do we use to reduce data redundancy?

A

Relational databases.

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

What is a foreign key?

A

The primary key of one table, present in another table in order to link the two tables.

A common attribute of both tables.

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

What is a secondary key?

A

Another indexed field in a database which can be searched quickly, but is not the primary key.

e.g. Customers may not know their customerID but would know their email address so this could be a secondary key

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

What is a one-to-one relationship?

A

A single entity links to only one other entity and this entity links back to the original entity.

e.g. Student and studentID

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

What is a one-to-many relationship?

A

A single entity links to multiple different entities however each of these entities link back to the same original entity.

e.g. Mother with multiple children

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

What is a many-to-many relationship?

A

Multiple different entities link to multiple other entities and these entities link back to multiple other entities.

e.g. A student takes multiple courses and each course is taken by multiple students.

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

What is each entity represented by in a relational database?

A

A new table

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

Can we have many-to-many relationships in relational databases?

A

No

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

What do we do if we need to use a many to many relationship in a relational database?

A

Create an extra table to link the two tables.

E.g. Students m2m Classes

Students 12m Enrollment m21 Classes

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

What is a composite / compound primary key?

A

A primary key consisting of more than one attribute.

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

Why would a composite primary key be used?

A

When the record cannot be uniquely identified by a single field.

E.g. Student, Class

or in a pack of cards, suit and number

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

What is referential integrity?

A

Ensuring that if a record cannot be deleted from one table if it is required in another table.

Can prevent deletion or delete in both tables.

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

What is a relational database?

A

A collection of tables in which relationships are modeled by shared attributes.

27
Q

What does a row in a table represent?

A

One record

28
Q

What does a column in a table represent?

A

One attribute

29
Q

What is indexing?

A

When an index of the primary key (and any secondary keys) is stored so that the record can be quickly accessed.

30
Q

What is normalisation?

A

A process used to come up with the best possible design for a database.

31
Q

What four main points should be followed to ensure that a database is normalised?

A
  • No unnecessarily duplicated data
  • Data consistent throughout database
  • Flexible structure to enter as many or as few items as required
  • Structure enables user to make complex queries relating different tables
32
Q

What are the three basic stages of normalisation called?

A

First, second and third normal form

33
Q

What does it mean if a table is in 1st normal form?

A

If it contains no repeating attribute or groups of attributes.

34
Q

How do we make something in 1st normal form?

A

Use a link table to remove groups of attributes and repeating attributes.

35
Q

What does it mean if a table is in 2nd normal form?

A

There are no partial dependencies.

36
Q

What is a partial dependency?

A

When a primary key is made up of multiple fields but an attribute depends on only one of these fields.

e.g. PK = (EventID, Year)

Winner is fully dependent on both fields whereas event name (100m) is dependent only on EventID

37
Q

What does it mean if a table is in 3rd normal form?

A

All attributes are dependent on the key, the whole key, and nothing but the key.

38
Q

How do we make something in 3rd normal form?

A

Create a new, linked table for any attributes that are not dependent on the whole key.

39
Q

What does the A in ACID stand for?

A

Atomicity: A change is performed or not performed. Half-finished changes must not be saved.

40
Q

What does the C in ACID stand for?

A

Consistency: Any change must retain the overall state of the database

e.g. money out of one account must be balanced by into another

41
Q

What does the I in ACID stand for?

A

Isolation: A transaction must not be able to be interrupted by a different transaction. A transaction is isolated until the change is comitted.

42
Q

What does the D in ACID stand for?

A

Durability: Changes must be written to storage in order to preserve them.

43
Q

What are the ACID rules for?

A

Protecting the integrity of the database.

44
Q

What is a transaction?

A

A change in a database.

45
Q

Why is it important to normalise a database?

A

Removes redundant data
Makes it easier to maintain and change
Faster sorting and searching
No errors when deleting records

46
Q

What is the “physical view” of a database?

A

The data that is written to storage

47
Q

What is the “logical view” of a database?

A

The organisation of the tables, queries and reports

48
Q

What is the “user view” of a database?

A

The interface that allows the user to interact with the database

49
Q

What is referential integrity?

A

The state of a database being consistent. For example, you cannot delete a record if it is linked to a record in another table.

50
Q

What is a DBMS (Database management system)?

A

Software that handles the data that is stored in secondary storage.

51
Q

What does the DBMS act as an intermediary between.

A

Applications and the data

52
Q

What does the DBMS provide for a user?

A
  • Security
  • Backup functionality
  • Index updating
  • Enforcement of referential integrity
  • Facilities to update and interrogate the database
53
Q

What are potential problems with multi-user databases?

A

If two users access a record at the same time, the last one to update the record will be saved and so the changes of one user may be lost.

54
Q

What is record locking?

A

When a user accesses a record, the record is locked for others to access until the transaction has been completed.

55
Q

What is a potential issue with record locking?

A

If two users are attempting to access the same two records, they can both be waiting for the records that are locked.

This is called DEADLOCK. (see p108 of main textbook)

56
Q

How do we get around deadlocking?

A

The DBMS must recognise when the situation has arisen and take action.

Serialisation, timestamp ordering, commitment ordering

57
Q

What is serialisation?

A

A techniques which ensures that transactions do not overlap in time.

A transaction cannot start until the previous one has finished.

58
Q

What is timestamp ordering?

A

When a transaction starts, it is given a timestamp. Therefore, it knows that the transaction with the earlier timestamp should be applied first.

59
Q

What is commitment ordering?

A

When transactions are ordered in terms of their dependencies on each other as well as the time they were initiated.

60
Q

What does serialisation prevent?

A

Deadlocking

61
Q

What is hardware redundancy?

A

When multiple identical systems are setup in different geographical locations, so that each transaction is written to multiple storage locations.

62
Q

What does hardware redundancy prevent?

A

Loss of transaction data.

63
Q

What is hashing?

A

A method of transforming a string of characters into a shortened form that can be used as a disk address.