4.10 Fundamentals of Databases

Question 1

Primary Keys (definition)

Answer 1

The unique identifier for each row

Question 2

Simple Primary Keys (definition)

Answer 2

Primary keys that only contain one field to make a unique value

Question 3

Composite Primary Keys (definition)

Answer 3

Primary key that contains more than one field to make a unique value

Question 4

Foreign Keys (definition)

Answer 4

A field in one table that is linked to the primary key in another table
A primary key that has come from another table

Question 5

Entity

Answer 5

Table

Question 6

Record

Answer 6

Row

Question 7

Attribute

Answer 7

Field

Question 8

Entity Definition

Answer 8

TableName (Primary Key, field1, field2)

• underline primary key

Question 9

Relationships: One to One

Answer 9

x -|——|- y

Question 10

Relationships: One to Many

Answer 10

x -|——-<- y

Question 11

Relationships: Many to Many

Answer 11

x -|——<- xy ->——| y

middle box contains foreign keys of other boxes to make primary key

Question 12

Relationship Diagrams are called …

Answer 12

Entity-Relationship Diagrams (ERDs)

Question 13

Normalisation Rules help …
(3)

Answer 13

• organise data efficiently
• eliminate redundant data
• ensure only related data stored in table

Question 14

First Normal Form (4)

Answer 14

• no columns with repeated or similar data
• each data item cannot be broken down any further (atomic)
• each row is unique (it has a primary key)
• each field has a unique name

Question 15

Second Normal Form (2)

Answer 15

• must already be in 1NF
• non-key attributes must depend on every part of the primary key

Question 16

Third Normal Form (2)

Answer 16

• must already be in 2NF
• there are no non-key attributes that depend on another non-key attribute

Question 17

Benefits of Normalisation (4x2)

Answer 17

• Easier to query data
• • no repetition of data
• • no redundant data
• Less chance of errors
• • only one instance of each piece of data (no data duplication)
• • changes are cascaded throughout the database (only need to change the data once)
• Queries run faster
• • less data to search through
• • database is optimised
• Less money on storage
• • less data = smaller database size

Question 18

Problems of Normalisation (5)

Answer 18

• Possibility of larger databases and more tables
• • requires more storage
• • requires more relationships to be assigned between tables
• • queries could run slower
• • queries are more complex
• Need to be careful making data atomic

Question 19

Concurrent Access (3)

Answer 19

• when more than one user has access to a record at the same time
• could lead to overwriting entries/changes
• could lead to losing data

Question 20

Record Locks (2)

Answer 20

• when a user starts to edit a record, a lock is set on the record
• other users can’t edit/access the record until the lock is released

Question 21

Timestamp Ordering (3)

Answer 21

• timestamps generated for each transaction
• timestamps indicate the order that transactions occurred in
• timestamps can be generated for edits, queries and reading & writing of data

Question 22

Serialisation (2)

Answer 22

• rather than locking, requests from other users are placed in a queue
• once the first user has finished using the field, the next command in the queue is executed

Question 23

Commitment Ordering (3)

Answer 23

• an algorithm is used to work out an optimum order in which to execute commands for the same field
• algorithm takes into account the impact of command on other parts of the database
• algorithm attempts to minimise issues from occurring

Question 24

Insert: every field

Answer 24

INSERT INTO table VALUES (value1, value2);

Question 25

Insert: selected fields

Answer 25

INSERT INTO table (field1, field2) VALUES (value1);

Question 26

Update

Answer 26

UPDATE table SET fieldtochange = newfieldvalue WHERE condition;

Question 27

Delete

Answer 27

DELETE FROM table WHERE condition;

Question 28

Select

Answer 28

SELECT *
FROM table
WHERE condition;

Question 29

Select - Order By

Answer 29

ORDER BY Asc/Dsc

Question 30

Select - 2 Tables

Answer 30

SELECT *
FROM table1, table2
WHERE table1.PrimaryKey = table2.ForeignKey AND condition;

Question 31

DDL - Database Definition Language

Answer 31

CREATE TABLE tableName (
Id integer NOT NULL PRIMARY KEY,
field1 varchar(50),
field2 date/time);

Question 32

Different Data Types (6)

Answer 32

• VARCHAR (x)
• INT/INTEGER
• FLOAT
• REAL
• BOOL
• DATE/TIME

Question 33

Normalisation (definition)

Answer 33

Process of storing data while removing redundant data and elimination data duplication

Question 34

Client Server Databases (3)

Answer 34

• Provides simultaneous access to the database for multiple clients
• Idea: client requests data from server by a connection made over WAN/LAN
• Once request fulfilled, connection terminated

Question 35

Advantages of Client Server Databases (5)

Answer 35

• data stored in single location
• data accessed any time/where (with permission)
• database can grow without client needing to improve
• centralised back ups
• increased security as what client can perform/access is restricted

Question 36

Disadvantages of Client Server Databases (5)

Answer 36

• database can become overloaded with data leading to slow response
• data corruption
• concurrent access
• servers & hardware not cheap to implement/maintain
• specialists required to maintain

Question 37

CRUD Acronym

Answer 37

Create –> Insert
Retrieve –> Select
Update –> Update
Delete –> Delete

Question 38

REST
- acronym
- function
- mapping

Answer 38

• Representational State Transfer
• Mapping Javascript (database) –> HTML
• GET –> SELECT
• POST –> INSERT
• DELETE –> DELETE
• PUT –> UPDATE`

Question 39

JSON vs XML (4)

Answer 39

• easier for humans to read/understand
• more compact representation
• easier to create and code
• easier for computers to parse therefore quicker to parse