4.10 Fundamentals of databases

What are the 3 possible connections between enties?

one - to - one (1:1)

one - to - many (1:M)

many - to - many (M:N)

Which connection can’t databases handle?

many to many (M:N)

What must be made to resolve M:N relationships?

a LINK entity / table

Why can’t M:N relations be handled without a link table ?

There would be multiple values in a single cell violating 1NF, or data redundancy would occur as e.g. a students name is inserted 5 times into a single table

Produce an Entity description of a student which has a firstname, lastname, and date of birth

STUDENT(StudentID, FirstName, LastName, DOB)

How are composite primary keys or primary keys indicated in an entity description?

underline both parts

Define foreign key

An attribute in one table that is a primary key in another table, used to link between the two

What might be the ENTITIES in a library that stocks books and has members

BOOK, MEMBER

How are foreign keys indicated in an Entity Description?

Italics e.g. foreign_key

Define “Table” in regards to a database

A method of implementing an entity in a database

Define “entity” in terms of a database

An object about which data will be stored

What do the columns and rows represent in a table/entity in a database ?

Columns : Attributes → characteristics of this entity, e.g. house number

Rows : A specific instance of this entity e.g. a specific customer

What are the rules for : 1NF?

Each field contains atomic data (A single value that cannot be split into smaller values),

No repeating attributes

What are the rules for : 2NF?

Already 1NF,

All non-key attributes depend on the whole PK

What are the rules for : 3NF?

Already in 3NF,

All non-key attributes depend only on the key attributes

What is normalisation in terms of a relational database ?

A process that ensures data is structured efficiently

Why do we normalise databases?

Avoid data redundancy;

Improve data integrity

What is SQL

“Structured Query language”

A specialised language for manipulating databases

How do you create a table using SQL ?

CREATE TABLE Customer

(

CustomerId VarChar(5),

CustomerName VarChar(255),

PRIMARY KEY (CustomerID)

);

How do you update a value in a table using SQL ?

UPDATE Customer

SET CustomerName = ‘John’

WHERE CustomerID = ‘1’;

How do you delete a value from a table using SQL ?

DELETE FROM Customer

WHERE CustomerID = ‘1’;

What are the key points needed to create a table ?

Key words capitalised : CREATE, TABLE, PRIMARY KEY

Primary Key defined

Attributes defined as well as their datatypes

semicolon at the end

What are the key points when updating a table?

Key words capitalised : UPDATE,SET,WHERE

semicolon at the end

What are the key points when deleting a record from a database?

Keywords capitalised : DELETE, WHERE

The whole record is deleted

; at the end

How do you insert a value into a database?

INSERT INTO Customer

(CustomerId,CustomerName)

VALUES (‘V1’,’V2’);

What are the key points for inserting a value into a database table?

Keywords capitalised : INSERT,INTO,VALUES

List the attributes, that are being inserted, after the table name

List the instance of the attributes after the keyword VALUES

; to end

How do we query data (search/sort) with SQL?

SELECT CustomerName,CustomerAddress

FROM Customer

WHERE CustomerName = “John”

ORDER BY CustomerName DESC;

What is a Client Server Database ?

A way of implementing a database where the database is put into a server that various users can access from their own workstations

Where do queries run in a client-server database?

The queries run on the server

What allows queries run on a client-server database?

The DBMS

What is DBMS

Database Management System : software that enables the management of all aspects of a database including inserting/updating/querying data (how data is stored and accessed)

What two methods are used to prevent concurrency issues?

Record locks

Serialisation

What is meant by Record Locks ?

Once someone with write access accesses a record, nobody else can save to that location (sometimes they can but they must acknowledge that it may be being edited elsewhere)

What is meant by Serialisation ?

Only allowing actions to occur one at a time

What are the two methods of implementing Serialisation ?

Timestamping and Commitment Ordering

What is meant by Timestamping?

Every transaction has a read and a write timestamp that indicate the last time that process occurred on that record,

The DBMS uses a protocol to decide if an action can be committed depending on the timestamps,

e.g. if there was an update made at a time later than a transactions write timestamp, then this transaction will be rejected

What is meant by commitment ordering?

If two different transactions have a dependency or conflict with each other, they must commit in the exact same order that their conflicting actions occurred.

Why is commitment ordering better than record locks ?

Record locks can cause deadlocking (where T1 is waiting for T2 which is waiting for T1)

Commitment ordering doesn’t have this