Option A: Database (CS SL)

Raw fact that is meaningless on it’s own / unprocessed values

meaningful data that has been interpreted / added context or meaning

Collection of hardware, software and human resources that work together to transform raw data into useful information

Organized of data stored electronically and is a component of the information system, it is stored in tables as records (rows, data about one particular person / company / item in a database) and fields (column, part of a record and contains a single piece of data for the subject of the record)

Created and stored by a software called DMBS (SQLite, mySQL, SQLserver)

store, retrieve, delete and edit data

• processing / querying data (search / sort / analyze)

• generate reports

• automatically support data validation / data verification when filling the database

• standardized (used in multiple systems)

• provides ability to sore a greater number of records compared to a spreadsheet

Checks that input follows rules for the type of input entered

Checks that input matches expected values in database

• a real world object / person

• represented by a row

• referred to as a record / tuple in a database

• stores a set of related data called records

• every record has value

• records in each table share the same fields / types of data

Collection of items that may or may not be related to one another

Collection of items that may or may not be related to one another

• identify a given record

• distinctive for each record

• primary key field in another table

• connect record in one table to another

text, char, boolean, int, real, datetime

• values that can be used as primary key (PK cannot be NULL, but SK can)

• can have more than 1 SK

• Identify each unique record in a database

• both PK and SK are candidate keys

when one field is not enough to uniquely identify records, but 2 fields together can work as a PK

example: name & birthday

• defines how data is organized

• overview of the database

• organizational chart

• tables are related to one another

• each table has a PK

• columns → attributes

• rows → records, if none then tuples

• row → identifier / PK

• relations between table are consistent and logical

set of programs that allow to read, store, change / extract data in a database

example: SQLite, mySQL, PostgreSQL

• data dictionary

• data safety

• query processor

• storage engine

• concurrency

• security

• file or set of files that store information about the database and the tables inside

• manage metadata / metadata repository

backup & recovery, data integrity

accept queries and return appropriate output

handles, create, read, update and delete operations

allows multiple users to access database; make sure multiple user can’t modify the same data simultaneously

example: row - locking

enforce user policies, include:

• access rights

• audit trails: changes made to the data

• data locking: lock rows that are accessed

• validation: make sure new data follows rules

• encryption

• back ups: update copies

• collection of low - level tasks

• set of SQL statements executed sequentially

• all operations are performed and if there is an error, changes either made permanent or save changes commited if not partially or reverted or rolled back

• Atomicity: all task performed or none

• Consistency: all data written must be valid

• Isolation: no transaction will interfere

• Durability: once transaction is complete, changes are permanent even with system failure

make sure changes don’t run into each other & is permanent

• data should be what the user means it to be

• accuracy, completeness, validity

situation where the same piece of data is stored in 2 or more different place

pros:

• faster data access

• better protection

cons:

• data inconsistency (updated at one place)

• more storage used

process where larger tables are divided into smaller tables while ensuring data integrity and reduce data redundancy

• reduce data redundancy (storage)

• reduce table complexity (insertion, updates and deletion less error prone

• make sure data is stored logically (to make querying more efficient)

• eliminate duplicate columns, multiple types of value

• create separate tables for each group

• meet requirements for 1NF

• eliminate partial dependency, 2PK, columns dependent on one attribute of a composite PK

• meet requirements for 2NF

• eliminate transitive dependency (column depending on another column)

• less data storage required → lack of duplicates

• data is more likely to be consistent

• increased data security → easy to locate

• operations are conducted more quickly and efficiently due to table structure

• simpler queries

• easier to understand as it is logically organized

• prevented by 2NF

• three types of anomaly:

  • insertion anomaly: or cannot be inserted due to missing data

  • deletion anomaly: when certain attributes are lost due to deletion of other attributes

  • update anomaly: data only partially updates

ensure that data is performant, secure, and recoverable by: updating database, maintaining security, managing back up procedures, and establishing recovery plan

• commands that allow us to define and modify the structure and metadata of a database

• generate data dictionary

• only available to DBAs

• DDL commands: alter, create, drop, truncate, rename

• visual representation of a whole / part of an information system

• helps stakeholders have a shared understanding of a system

• Three types:

  • conceptual: rough sketch, entities

  • logical: 4 entities, attributes, FK

  • physical: data, data type, attributes, relationship

• avoid issues:

  • redundancy

  • lack of integrity

  • lack of consistency

• helps developers develop actual database

• lack → deficient modelling