2.5 Databases and distributed systems

What are databases?

Organised collections of data, which allow for easy storage, management, and retrieval of data

What is it important for databases to have?

Data redudancy - The same piece of information is stored multiple times in different rows (This wastes storage, reduces integrity, and increases chance of inconsistencies as data is changed)

Data consistency - Data is accurate in all stored areas (Data doesnt conflict as it gets updated which would lead to unreliability and poor integrity of data)

Data integrity - Accuracy and reliability of data (no errors/duplicates/missing data) (Unreliabilitys can spread reducing integrity and can lead to communication errors)

What is the structure of a  flatfile database

• Data is stored in rows and columns

• Each row represents one records

• Each column is a field

• All data is in one table

What is referal integrity?

A validation method for databases that is used when linking tables a record is required to exist in both. (For example primary and foreign keys and these prevent records being added that are not part of the relationship)

It ensures connecting data exists and you cannot delete data that is connected to other data

What are relational databses

They were introduced to solve the problem with flatfile

They have multiple tables linked together with primary keys

Characterisitcs

-Entities (each table in the database)

-Primary key (unique identifier for each record)

-Foreign key (these keys link tables)

-Composite key (Primary key of 2 or more fields)

No two records can have the same primary key

What are benefits of electronic databases?

• Easier to add/delete/modify data

• Data is easily backed up/copied

• Acessibile by multiple people in diff locations at the same time

• Less mistakes (spelling and copying error etc)

• Storage is smaller physcially

What are types of database relationships?

One-to-One (Like person to student ID as one field represents one other field)

One-to-Many (Like School to student

Many-to-Many (Like student to classes but will create reduancies so will need to add a new table)

Whats an ERD

Shows how data is organised with entities, atribute and relationships (Like a shop can have many sales)

What is indexing

Used to speed up data retrieval by indexing a column by associating an index with certain characters (Like names starting with T)

Pros and cons of indexing?

+Faster retrieval

+Efficient sorting/filtering

+Improve performance

-Overheads

-More storage

-Needs updating with changes

What is normalistation?

Organising data in a relational database to reduce redudancy and improve data integrity.

There are 3 Levels (1NF, 2NF, 3NF)

What is 1NF and its Rules

1s Normalised Form.

It ensures data in a table is organised clearly and efficiently so each value is atomic (cannot be split)

Rules:
-Each column contains 1 value (no lists)

-Each record is unique (each row has primary key)

-Each field is dependant on the primary key

What is 2NF and its rules

2nd normalised form.

Rules:

-It is already in 1NF

-All-non key attributes depend on the whole primary key (Not on a composite)(For example spliting an order table with each product having a product ID and its details so you dont have to repeat them)

What is 3NF Rules

-Must be in 2NF

-Every non-key attribute depends on the primary key and not a non-key attribute

This removes repeated data by creating new tables

What is a data dictionary?

A document or table that describes the structure of a database (contains information about how data should be)

Its a reference that says:

-What data exists

-Where it exists

-How its used

What will a data dictionary store for each field?

-Field name

-Data type

-Size

-Constraints (Like if its a key and if it should contain letter ‘s’)

Why are data dictionaries important?

It outlines rules to allow for accuracy and consistency of data.

How is validation used in a database?

It is designed into a data dictionary when creating the table. Restrctions can be set during declaration with constraints

How would i create a new column in a table?

You would:
-Set the data type (like an int would reject a string)

-Reject null values (NOT NULL or instead just keep them)

-Set length checks (truncate data)

How would I format check in a database?

The database does not do that so it needs to be separately implemented

What is data redudancy?

When there are duplicate data in different tables (this compromises consistency as data may not be updated everywhere and also wastes storage)

What is data consistency?

The accuracy of data (all occurances of the same field data are the same value)

What are the benefits of a relational database?

• Avoids redundancy

• Easily edited

• Supports complex queries

• Consistnet records

• Data is easily added and deleted

What is SQL

Structures Query Language. Is a general purpose language used to manipulate data in databases and tables

Create table query?

make sure to define NOT NULL, primary keys, foreign keys ,etc

With structure:

<name><data type (char(50) or int)><Primary key/Foreign key><NOT NULL (optional)>

What is numeric data type for create table

numeric(x,y)

x is total digits

y is digits to right of decimal (use 2 for money)

Insert Into [Table] Values[]

select query

where… IN

e.g. WHERE name IN (‘Pete’,’steve’)

instead of where name == pete

AND

where name = ‘peter’ and age = 12

OR

Similar to and but can allow one or both conditions

ORDER BY

ASC

DESC

BETWEEN

BETWEEN ID = 1 AND ID = 2

UPDATE query

DELETE query

Join query

can use where instead of join on

GROUP BY [Field];

You would use this to group data that is associated (such as associating all the cash payments associated with one person and grouping it by cash payments to add all up)

Make sure to end with semi colon