Task 6: databases (CS)

Database

Structured collection of data, stored electronically in a computer system

Controlled by a database management system (DBSM)

Flat file database

Contains individual, unrelated tables (collections of data)

e.g., excel

Relational database

Contains multiple tables that share related fields (ID, customer)

Use structured query language (SQL) to store, manipulate and retrieve data

e.g., SQLite

Data v

Raw, unorganised facts that need to be processed

Info

Data that is processed, organised, structured or presented in a given context to make it useful

SQL data types

INTEGER - whole

REAL - decimal

Boolean - true/false

TEXT - string

date

• TEXT as ISO8601 (“yyyy-mm-dd hh:mm:ss.sss”)

• INTEGER as Unix Time (number of seconds since 1970-01-01 00:00:00 UTC

Relationships

Associations or connections between tables in a relational database

Help organise and structure data, allowing for efficient data retrieval and maintaining data integrity

Related table

Tables that share a relationship

Established using foreign keys, which are columns in a table that refer to the primary key in another table

One-to-one

Each record in Table A is associated with one and only one record in Table B, and vice versa

e.g., Tables ‘users’ and ‘user_profiles’, where each user has a single corresponding profile

One-to-many

Each record in Table A can be associated with multiple records in Table B, but each record in Table B is associated with only one record in Table A

e.g., Tables ‘departments’ and ‘employees’, where each department can have multiple employees, but each employee belongs to one department

Many-to-many

Each record in Table A can be associated with multiple records in Table B, and vice versa

e.g., Tables ‘students’ and ‘courses’, where each student can enroll in multiple courses, and each course can have multiple students

Data anomalies

Inconsistency in data resulting from an operation like an update, insertion or deletion

Normalisation

To reduce the chance of creating data anomalies when modifying database

Minimise or eliminate redundancy

Redundancy

Several, unrelated copies of the same data in the database

Update anomaly

Results from data redundancy and partial update

Delete anomaly

Unintended loss of data due to deletion of other data

Insert anomaly

Inability to add data to the database due to the absence of other data

Composite keys

Creating a primary key by combining more than one attribute

Constraints

Used to limit the type of data that can go into a table

Ensures accuracy and reliability of data

Specified when table is created

e.g., primary key, autoincrement, not null, unique

Data integrity

Accuracy, completeness and quality of data as it’s maintained over time and across formats

e.g., currency, authenticity, relevance, accuracy

Currency

Accurate and up to date

Authenticity

Trustworthy info collected from real life events

Relevance

Degree to which data provides insight into the problem or purpose being addressed

Accuracy

Percentage of errors across the total number of records

Outliers

Data point that differs significantly from other observations that can distort statistical results

Accuracy vs validity

Both seek to describe the quality of usability of a data set

Accuracy - how well the data corresponds to the real world or true value

Validity - how well data values are consistent, based on defined rules