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