IB Computer Science SL | Options A - Databases

Data

Raw fact

Information

Data with meaning

Information system

Collection of databases which provide storage of information

Database

Organized collection of data, subset of information system.

How are information systems helpful?

1) Present information in a visually organized way

2) Aids decision making

Transaction

Unit of logical action, independant of other transactions.

Acid

Ensures that either all changes in a database are performed as a single operation or none performed at all.

Consistency

Ensures that data is in a consistent state from beginning to end of transaction, only valid data is written in the database.

Isolation

Prevents modification of same data item by two different transactions. Ensures that intermediate state of a transaction is invisible to other transactions.

Durability

Ensures that transactions are saved permanently and committed, does not accidentally get deleted.

Example of Acid

When moving money from one account to another, ensures that credit is successfuly made from one account to corresponding account.

Example of Consistency

When moving money from one account to another, ensures total value of money in both accounts is same from beginning to end of each transaction

Example of Isolation

When moving money from one account to another, ensures that another transaction might see one account but cannot see both accounts.

Example of Durability

When moving money from one account to another, ensures that changes are permanent to each account.

Lost Update Problem

When two users update the same data item, but only one of those changes is recorded in the data.

Temporary Update Problem

When one transaction updates a database item and then the transaction fails for some reason. Meanwhile updated item is accessed by another transaction before it is changed back to its original value.

Incorrect Summary Problem

When calculating an aggregate of items on a database while other transactions are updating these items, the aggregate function may calculate some values before they are updates and therefore give an incorrect sum.

Rollback

Signals that a transaction has ended unsuccessfully

Needs of Data Sharing

1) Allows sharing amongst multiple users

2) Any changes are accessible and visible to all users

3) Allows reliable and consistent database management because of different sets of data shared with users having different levels of permission

4) Scaleability and reusability as operation grows

Verification

Make sure data entering the system matches data at the source

Double Entry Verification

Same data being entered twice

Manually Checking Verification

User manually checks if the data in the computer matches the data in the source document

Look up

Can only have two possible values

Range Check

Between two certain values

Length Check

Certain validation of different numbers or letters

DBMS

Collection of programs that enable you to organize data in a database

RDBMS

Maintain database in both tabular form as well as file form

Characteristics of RDBMS

1) Normalized so data is not repeated

2) All columns depens on a primary key to identify a column

3) Distinct separation between storage of data and application using them

Functions and Tools of DBMS

1) Define nature of tables and their relationships > DDL

2) Query and manipulate data from database > DML

3) Support concurrent access by multiple users > Data locking

4) Allows ACID Transactions

5) Maintains storage of database objects

Access Rights

Maintains integrity by providing users with different authorized levels of permission to modify data. Records in database are partitioned to ensure privacy

Validation

Does not allow any data to be entered to a databsae that does not match a specific set criteria, ensures accurate data input.

Data locking

Ensures multiple transactions are happening safely through a row locking mechanism which locks data, which means another database session cannot update that data until the lock is released which unlocks the data and other sessions are then allowed to update that data.

SCALP

Schema Conceptual and Logical Physical

Schema

Logical strucutre of data in a database

Conceptual Schema

List of entities and their relations to one another

Logical Schema

Validation rules and keys which help design the database which will be implemented in the DBMS system

Physical Schema

Represents how the model will be built in the database by showing all table structures.

Data Dictionary

Describes structure of tables in a database as well as audits information of who has updated records. Do not contain any actual data from database, only containing information for managing it.

Data Definition Language

Creating and modifying strutures of database objects.

Examples CREATE, ALTER, DROP

Data Modelling

Gives visual representation of the proposed system, allows database administrators to ensure policies from the government are in compliance with regulations. As well as all stake holders agree on prototype of database to confirm it matches their specific needs.

Consequneces of Incomplete Data Modelling

1) Redundant data might be created

2) Result in wrong data type being assigned to a field.

3) Inaccurate representation of database, information might be missing.

4) Not be detailed enough for the database developer to build the physical database.

Table

Objects that contain all the data in a database

Field

Data structure for a single piece of data

Tuple/Row/Record

Object that can contain one or more values

Primary Key

Uniquely specify a tuple in a relation.

Secondary Key

Used in addition to the primary key to locate specific data, but typically does not uniquely identify a record with complete accuracy.

Foreign Key

Column which creates a relationship between two tables, to maintain data integrity.

Candidate Key

Unqiuely identify tuples in a table, no repeated attributes.

Composite Primary Key

Combination of two or more columns that uniquely identify rows in a table.

Inner Join

Returns all rows from both participating tables where they key record of one table is equal to the key record of another table.

One-to-one Relationship

Primary key table contains only one record that relates to none or one record in a the related table.

One-to-many Relationships

Primary key table contains only one record that relates to none, one, or many records in a the related table.

Many-to-many Relationships

Each record in both primary values can relate to none or any number of records in the other table. (Require a third table called junction table because relational systems cannot directly accomodate the relationship.)

Data Redundancy

Condition created within a database where same piece of data is held in two separate places, when data is not normalized fully.

Issues with Redundant Data

1) Data inconsistency

2) Queries will produce inaccurate and inconsistent outputs

3) When a field value changes, multiple occurences must be changed.

4) Takes up unnecessary storage space.

Referential Integrity

Accuracy and consistency of data within a relationship, you cannot enter foreign keys that do not match corresponding primary keys in the related table; could cause mistached data.

Cascading update

If primary key for a record in the parent table changes, all corresponding records in the child table are modified as well .

Cascading delete

If the primary key in a parent table is deleted then all corresponding records in the child table are delted as well.

Normalization

Used to help reduce data redundancy when designing data strictures

1NF

1) Data is atomic

2) Each record has a primary key

3) No repeating groups of attributes

2NF

No partial dependencies

3NF

1) Has to be in 2NF form

2) No non-key dependencies

Insertion Anomalies

Attempt to store an attribute value in the database but unable to because the value of another attribute is not known

Deletion Anomalies

When data is lost unintentionally by deleting other data.

Update Anomalies

If data is held in more than one place, and an update is made to only one version of the data.

Cardinality

Number of times an instance in one entity can be associated with instances in the related entity

Dervied Field

Exists temporarily in the table so the rules of normalization do not apply.

View

Allows users to display only filtered information from the database.

Uses of View

1) Maintains security, given permission to access the database only through a small set of of views that contain the specific data the user is authorized to see.

2) Provides simplified and personalized view of database so users can easily find what they are looking for

Roles of DBA

1) Make changes are required to functions of database.

2) Make sure database is backed up

3) Make sure data security is maintained

4) Authorizing access to database

5) Capacity planning

6) Coordinating and monitoring use of database

System log

Recovery method recovers past copy of database > Reconstructs a more current state by redoing the operations of committed transactions, up to the time of failure

Deferred Update (Support)

To support abort scenarios, while transaction runs no changes made by that transactions are recorded in the database unless committed where it is flushed to the disk

Deferred Update (Abort)

On abort, do not do anything as database does not change after uncommitted transactions. on a system restart redo the log and data is not updated until after a transaction commits fully.

Data Integration System

Provides unified view across data sources and enables the analysis of combined data sets to unlock insights that were not previously available to obtain.

Data Privacy

Anonymizes individual's data such as masking some data while sharing with others. Users are aware for how long their personal data will be retained and with whom it will be shared with.

Data Mining

1) Use of patterns to identify trends

2) Extract unknown pieces of information from large databases

3) Analysing data in a number of way to detect relationships

Data Matching

1) Compares two sets of collected data.

2) Performs precise searches to find errors in data.