Relational databases (A3.1 Database fundamentals)

Database

Organized collection of structured data

• For storage and manipulation of data (retrieving, managing)

Relational database

Data organized as set of tables with columns/fields/attributes and rows/records/tuples

I. Relationships

Logical association between multiple tables within relational database

• Table contains 1/+ FKs that reference PKs in other tables = divide and store data in separate tables while connecting data items

One-to-one (I)

One record in table is associated with exactly one record in another table

• PK corresponds to one or no data

• Usually rare

• Ex: Each staff has one ID, each country has one capital, each person has one active passport

One-to-many (I)

One record in table is associated with 1/+ records in another table

• FK of one table references PK of another table

• More common

• Ex: one teacher teaches many subjects, one tourist visits many countries, one person owns many properties, one person has many bank accounts

Many-to-one (I)

1/+ records in table is associated with exactly one record in another table

• Similar to prev, differ in directionality: availability of entity, side of relationship

• Ex: many students enroll on single course, many people work for single company, many galaxies in universe

Many-to-many

Multiple records in table have relation with multiple records in another table

• Linking/associative entity/table - implement relation: create two one-to-many relationships (one between first and linking table, another between second and linking table)

  • e.g. many STUDENTS to many COURSES —> make ENROLLMENT as associative table: many STUDENTS to one ENROLLMENT, one ENROLLMENT to many COURSES

• Ex: many customers purchase many products, many actors act in many movies

II. Benefits/strengths of relational databases

Benefits/strengths of relational databases

Community support (II)

Most widely accepted model = lots of communities

Concurrency control (II)

Crucial DBMS component: manages simultaneous operations w/o conflicting with each other

• Purpose: maintain data integrity, consistency, isolation when multiple users/applications access database at once

• Eg avoiding deadlock

Data consistency (II)

Data remains in consistent state from start to finish so that all copies/instances of data are same across all systems and databases

• = ^data integrity

Data integrity (II)

Accuracy, completeness, consistency of data throughout lifecycle

• = ensure data hasn’t been altered unauthorized

Referential integrity (II)

Property: ensure all FK values either match existing PK in related table or are null = consistent relationships

• When one table has FK that points to PK in another table, means:

  • Can’t insert FK value that doesn’t exist in parent table

  • Can’t delete/change PK in parent table if there are related rows in child table (unless handled with cascade rules)

Efficient and flexible data retrieval (II)

• SQL allows for complex queries/statements to retrieve exactly data needed

• Users can create ad hoc queries to retrieve data w/o needing predefined reports/programs

Reduced data duplication (II)

Common fields link tables and match records w/o duplication

• = v storage needed

Reduced redundancy (II)

Stores less of same data in multiple locations at same time

• Normalize database - org data into several tables, create relationships, correctly enforce dependencies (see A3.2)

• Data redundancy = inconsistencies, partial updates, unnecessary duplications

Reliable transacting process (II)

Transaction - sequence of actions performed on data that is considered as single unit

• Unit of work/logical action independent of other transactions and performed on data by DBMS

• Either executed fully or not at all

• = ensure data integrity, reliability

Vertical scalability (II)

Ability of DB to handle increase amounts of data, users, types of requests w/o sacrificing performance, availability

• Relational DB is vertically scalable: support idea of adding more resources to existing systems (CPU, RAM, etc)

Security features (II)

Control access to shared data

• = assignment of unique user accounts with specific permissions

III. Limitations/weaknesses of relational DB

Limitations/weaknesses of relational DB

Horizontal scalability (III)

More difficult to scale horizontally (adding data) as size and complexity of data increase

Design complexity (III)

Requires lot of structure, planning to design

Hierarchical data handling (III)

Difficult to store hierarchical data due to mismatch between hierarchical structure and tabular nature

Predefined rigid schema (III)

• Schema - blueprint/structure of database

• Can be difficult to define schema: difficult to predict data structure beforehand, changing is complicated and time consuming

Object-relational impedance mismatch (III)

Difficulties when relational database used by program written in object-oriented programming (OOP) languages

• Due to data type differences: relational doesn’t allow use of by-reference attributes (pointers) which OOP embrace → no clear way to translate all OOP concepts (inheritance, etc) into relational and vice versa

Unstructured data handling (III)

Collection of data where one record differs from another record

• Being unable to identify common fields/attributes for records makes it impossible to design schema