Theory: Databases

Question 1

Describe the reasons for database normalisation (6)

Answer 1

-efficient without compromising data integrity
-no redundant or repeated data
-faster searching and sorting
-easier to maintain
-duplication of data minimised
-improved data consistency

Question 2

What is needed to create a data model?

Answer 2

Data requirements

Question 3

Describe a data model

Answer 3

An abstract model of which entities to record and which attributes of each entity

Question 4

What does each entity in a table have?

Answer 4

A row/record

Question 5

Describe the concept of an entity identifier

Answer 5

-sometimes called primary key, or composite key.
-a unique value used to identify a single record
-primary keys are single attributes/fields that use only one field to determine a unique value
-composite keys (or composite primary keys) use multiple attributes/fields to form a unique value for a record.

Question 6

Describe an entity description:

Answer 6

-eg. Customer(customerID, customeremail)
-describes how information about an entity should be stored in a table.
-the name of the table as well as field names within the brackets

Question 7

How is the entity identifier/primary key identified in an entity description?

Answer 7

A value can be underlined

Question 8

Describe the concept of a relational database

Answer 8

-a database containing multiple tables that are linked by common attributes.

Question 9

What relationship type is not supported by relational databases?

Answer 9

Many to many

Question 10

Describe the concept of a foreign key

Answer 10

-an attribute/field of a table that is the primary key of another table

Question 11

Describe first normal form

Answer 11

-no repeating attributes
-data is atomic, all fields contain one value and only one. Two values can not be stored in one field.

Question 12

Describe second normal form

Answer 12

-partial key dependancies are removed
-For a database with a composite primary key, attributes that don’t depend on all values that make the composite key, are removed to form another table

Question 13

Describe the concept of third normal form

Answer 13

-no non-key dependancies
-all non key attributes depend on the key, the whole key and nothing but the key.

Question 14

Properties of SQL

Answer 14

-declarative, readable

Question 15

List as many SQL commands as you can

Answer 15

-SELECT
-UPDATE
-INSERT INTO…VALUES()
-DELETE
-WHERE
-FROM
-UPDATE
-SET
-ORDER BY
-CREATE (TABLE)
-VARCHAR(size)CHAR(size)
-INT(size)FLOAT(size)

Question 16

what is the wildcard you’ve learned?

Answer 16

-*

Question 17

Describe the complexities of a client server database

Answer 17

-simultaneous access supported for multiple clients
-issues arise when clients attempt to access the same field-concurrent access
-can result in updates being lost

Question 18

List some methods used to preserve data integrity in a client server database

Answer 18

-record locks
-serialisation
-timestamp ordering
-commitment ordering

Question 19

Describe record locks

Answer 19

-when a record is accessed by one client/user, it is immediately locked.
-other clients/users are blocked from accessing or modifying the record until it is unlocked

Question 20

Describe serialisation

Answer 20

-placing other user requests in a queue to be serially executed after the current user is finished

Question 21

Describe timestamp ordering

Answer 21

-commands performed on a field are assigned a timestamp and are carried out in the order of the timestamps

Question 22

Describe commitment ordering

Answer 22

-an algorithm determines the order by which to execute commands on the same field based on reducing the impacts of commands on other parts of the database.

Question 23

How are many to many relationships handled with databases?

Answer 23

A link table is created that utilises the unique identifier of values to map entities to each other.

Question 24

Why is database normalisation needed?

Answer 24

So that databases can be efficient without compromising the data integrity. It ensures that entities contain no redundant or repeated data.

Question 25

How can the data of a database be defined in first normal form?

Answer 25

atomic- no single column contains more than one value.

Question 26

What does the term atomic mean, when referring to data in a database?

Answer 26

No single column contains more than one value.

Question 27

What is concurrent access?

Answer 27

When two people access a database at the same time

Question 28

What is the problem with concurrent access?

Answer 28

Can result in updates being lost if two users edit a record at the same time

Question 29

What are some processes used to prevent concurrent access?

Answer 29

-record locks
-serialisation
-timestamp ordering
-commitment ordering

Question 30

What are some processes used to prevent concurrent access?

Answer 30

-record locks
-serialisation
-timestamp ordering
-commitment ordering