Databases

Define Relational database

Stores different entities in separate tables. Linking tables depends on the relationship between entities

define database

an organised collection of data

List benefits of an electronic database

easier to add, modify and update data

data can be backed up and copied easier

multiple users from multiple locations can access the same database at the same time

define field

A single piece of data in a record

Define record

A single row of data about one entity

Define table

a collection of related records stored in rows and columns

Define primary key

a unique identifier for each record in a table

Properties of a primary key?

Unique and not null

What is an entity?

A real world object or concept that data is stored about

What is an attribute?

A property of an entity

What is a composite key?

A primary key made from more than one field

What is a foreign key?

A field that links to a the primary key of another table

What is a secondary key?

a field or fields that are indexed for faster searching

What is DBMS (Database management system)

software that allows users to create, manage + interact with a database

Why are keys important?

To uniquely identify records + create relationships

What is a relationship?

A link between tables using keys

What is referential integrity?

Ensures foreign keys refer to existing primary keys

What is a one to many relationship?

One record in a table refers to many in another

What is a many to many relationship?

Many records in one table relates to many in another

How is a many to many relationship resolved?

Using a linking (junction) table

What is a flat file database?

stores all data in a single table or file

ADV of flat file

Simple to set up and use

minimal software needed - low cost

suitable for very small databases

ADV of relational database

reduces data duplication

more efficient storage

faster searching + reporting on large databases

maintains data consistency (referential integrity)

easier to manage complex data

scales well to a large number of records

What is normalisation?

Organising data to:

• reduce data redundancy

• ensure data integrity

• avoid anomalies

What is 1NF?

No repeating groups + atomic fields

What is 2NF?

In 1NF and no partial dependency on a composite key

What is 3NF?

In 2NF and no non-key field depends on another non-key field.

What’s the purpose of normalisation?

Reduce duplication + improve data integrity

What is an index?

A structure that speeds up searching

DIS of an index

slows down update operations
However, speeds up searching operations as records can be located quickly

What is SQL

A language used to query + manipulate databases

What is access control?

Restricting who can view or edit data

What is validation?

Checking data is reasonable before storage

What is verification

checking data was entered correctly

ADV of databases over file systems

Reduced redundancy + improved consistency

ADV of relational model

Supports relationships + complex queries

DIS of flat file database

Data duplication

wastes storage space

hard to update consistently

searching + sorting becomes slow with large databases

poor scalability for a large number of records

DIS of relational database

more complex to design

requires specialist software (e.g: DBMS)

higher set up costs

needs more technical costs

What is data normalisation

the process of structuring tables to reduce data redundancy + prevent anomalies by organising data into relevant tables

What is data redundancy?

data is unnecessarily repeated in a database

can cause inconsistencies if one copy is updated and others aren’t