IB Comp Sci - Option A: Databases (SL)

Data

Data

• Meaningless without processing

• To be useful, must be interpreted to produce information

Information System

Any combination of information technology and people's activities using that technology to support operations, management, and decision-making.

Databases are a component within the system.

Data integrity

Refers to accuracy, completeness, and validity (it follows the rules) for data

Data redundancy

Situation where the same piece of data is stored in two or more different places

Database schema

Diagram that defines how data is organized in a relational database (includes table names, fields, data types of fields, and relationships between tables)

Normalization

Process by which larger tables in a database are divided into smaller tables while ensuring data integrity and reducing data redundancy

Concurrency

Multiple users to access database at the same time without interfering with each other

A database management systems (DBMS) concept that is used to address conflicts with simultaneous (same time) accessing or altering of data that can occur with a multi-user system.

Data modeling

Process of creating a visual representation of a whole or part of an information system

Data segmentation

The process of taking the data you hold and dividing it up and grouping similar data together based on the chosen parameters so that you can use it more efficiently within marketing and operations

Primary key

A field that is distinctive for each record

Foreign key

A field in one table that refers to the primary key field in another table

Primary composite key

Two fields that function as a primary key when one field is not enough to uniquely identify records

1st Normal Form

Normalization standard - eliminate duplicate columns, columns with multiple types of values (non-atomicity), create separate tables for each group of related data (with unique primary keys)

2nd Normal Form

Normalization standard - Atomic values, no duplicate columns, eliminate partial dependency (columns dependent on one attribute of a composite primary key)

3rd Normal Form

Normalization standard - Atomic values, no duplicate columns, all components must depend on the full primary key, eliminate transitive dependency

Partial dependency

When one field depends on only one field in a primary composite key, but not the other(s)

Transitive dependency

When one field depends only on another field that is not the primary

Database transactions

A ___ is a unit of work that you want to treat as "a whole." It has to either happen in full or not at all.

A logical unit of work must exhibit four properties, called the atomicity, consistency, isolation, and durability (ACID) properties, to qualify as a ___.

Remember if the ___ does not complete it will roll back.

ACID (atomicity, consistency, isolation, durability)

A set of properties of database transactions intended to guarantee data validity despite errors, power failures, and other mishaps.

Data dictionary

A file or set of files that stores information about the database and the tables inside

Data Definition Language (DDL)

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

Database Management System (DBMS)

Set of programs that allows a user to read, store, change / extract data in a database (CRUD)

Functions of a DBMS

• Data dictionary management

• Data storage management

• Data transformation and presentation

• Security management

• Multiuser access control

• Backup and recovery management

• Data integrity management

• Database access languages and application programming interfaces

• Database communication interfaces

• Transaction management

Database administrator

A person who ensures that data in a database is performant, secure, and recoverable

3 levels of schema

Determine at what complexity level the database is at:

Conceptual - Conceptual ERD is the simplest model

Logical - This is more complex than the conceptual and may have more details such as column types

Physical - Represents the actual design blueprint of a relational database

Conceptual ERD

• Entities and relationships modeled in such ERD are defined around the business's need.

• The need of satisfying the database design is not considered yet.

Logical ERD

• It is more complex in that column types are set.

• The setting of column types is optional and if you do that, you should be doing that to aid business analysis.

• It has nothing to do with database creation yet.

Physical ERD

• Represents the actual design blueprint of a relational database.

• It represents how data should be structured and related in a specific DBMS so it is important to consider the convention and restriction of the DBMS you use when you are designing it.

  • An accurate use of data type is needed for entity columns and the use of reserved words has to be avoided in naming entities and columns.

Simple Query

Might use all of the fields in a table and search using just one parameter

Complex Query

Might use just the necessary fields about which the information is required, but it will still use just one parameter (search criteria)