2 of 127

Data vs. Information

Data
Raw facts
Unprocessed
No context
Just text and numbers

Information
Data + context
Processed and structured
Interpreted by the user
Data is given meaning

Jason got a 95/100 on the Final Exam

Jason scored 12 points higher than the

class average

Jason got the 2nd highest score in the class

The British International School, Istanbul

3 of 127

What is a database?

A database is an organized collection of data connected to a specific topic
A database is made of tables, which are often related to each other
A database is created and managed by a piece of software called a Database Management System (DBMS). (SQLite, mySQL, PostgreSQL, MS Access, SQL Server are all examples)
We can use queries, or commands to store, retrieve, and delete, and edit data in a databases written in SQL (Structured Query Language)

The British International School, Istanbul

4 of 127

The British International School, Istanbul

5 of 127

Why use a database?

Enables processing/querying of data (search/sort/analyse)
Can generate reports on searched information in specific formats
Automatically supports the data validation/data verification when filling the database
Standardized - can be used by multiple systems
Provides the ability to store a greater number of records than a spreadsheet

The British International School, Istanbul

6 of 127

Data Validation vs. Data Verification

Data Verification	Data Validation
Checks that input matches expected values in database Example: Login details (email address and password) for Instagram user match those stored in database	Checks that input follows rules for the type of input entered Example: Credit card number entered by customer only contains numbers, 4 groups of 4 numbers, follows accepted format for credit card numbers

The British International School, Istanbul

7 of 127

What is a table?

The British International School, Istanbul

8 of 127

What is a table? (2)

A database consists of tables, in which we store a set of related data, called records.
All of the records in each table share the same fields or types of data.
Every record in the Movie table has a “MovieID” value, a “Title vale”, and a “Rating” value.

The British International School, Istanbul

9 of 127

What is an entity?

An entity is a

real-world object or person

Represented by a row in a database table
Referred to as a record or tuple in a database context

The British International School, Istanbul

10 of 127

What is a primary key?

A field that is distinctive for each record.
It is generally used to identify a given record in a database.

The British International School, Istanbul

11 of 127

What is a foreign key?

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

The British International School, Istanbul

12 of 127

The British International School, Istanbul

13 of 127

Data Types

Data Types	Description	Examples
Text	Combination of letters, numbers, symbols (treated as text) - even if number, no mathematical operation possible	“Amith” “X97BF” “$LLF” “34” “3”
Character	An individual letter, number, or symbol - even if number, no mathematical operation possible	“3” “A” “#” “b”
Boolean	True/False or Yes/No	True False
Integer	A whole number	5, 29, -31
Real	A decimal number	33.1, 22.39, 2.10
Datetime	A date and/or time	12:30 5/9/2022 7/11/2022 03:35

The British International School, Istanbul

14 of 127

Creating our first table

We connected to our companies.db database.
We created a query that creates a table with 4 fields NAME, AGE, ADDRESS, SALARY.
We executed the query.
We closed our connection to the database.

The British International School, Istanbul

15 of 127

Get All Values in a Field

SELECT ProductName FROM Products

SELECT ProductName, Price FROM Products

Table Name: Products

The British International School, Istanbul

16 of 127

SELECT Statements

SELECT - Used for SQL statements to get a set of data from table

FROM - Used to specify the table from which the data will come

SELECT ProductName, Price FROM Customers

Field(s)

Table

The British International School, Istanbul

17 of 127

Get All Values in a Field According to Criteria

SELECT ProductName FROM Products WHERE Price > 20

SELECT * FROM Products WHERE SupplierID <> 1

Table Name: Products

The British International School, Istanbul

18 of 127

Get All Values in a Field that Contain a String

SELECT * FROM Products WHERE ProductName LIKE ‘A’

SELECT ProductName FROM Products WHERE ProductName LIKE ‘Cha’

Table Name: Products

The British International School, Istanbul

19 of 127

Simple vs. Complex Queries

SELECT ProductName FROM Products WHERE Price > 20

SELECT ProductName FROM Products WHERE Price > 20 AND PRICE < 22

The British International School, Istanbul

20 of 127

SQL Cheat Sheet

Get Field Data

SELECT Field1, Field2, …* FROM TableName

Get Field Data According to Criteria

SELECT Field1, Field2, …* FROM TableName WHERE Some_Criteria

Get All Values in a Field That Contain A String

SELECT Field1, Field2, …* FROM TableName WHERE Some_Criteria LIKE Some_String

The British International School, Istanbul

21 of 127

What is a secondary key?

Capable of functioning as a primary key
Also called an alternate key

https://programmerbay.com/difference-between-pri mary-key-and-secondary-key/

The British International School, Istanbul

22 of 127

Secondary Key vs. Foreign Key

Primary Key	Secondary Key
Can uniquely identify records	Can uniquely identify records
Cannot be NULL	Can be NULL
Only one primary key field possible	Multiple secondary key fields possible

The British International School, Istanbul

23 of 127

What is a candidate key?

Can identify each unique record in a database independent of any other field
Both primary and secondary keys are candidate keys

https://programmerbay.com/difference-between-pri mary-key-and-secondary-key/

The British International School, Istanbul

24 of 127

What is a composite primary key?

- When one field is not enough to uniquely identify records, but two fields together can functions as a primary key

Name	Birthday	Grade
Amith	02/04/2003	98
John	27/03/2008	77
Ali	09/09/2009	95
Sofia	11/11/2011	100

The British International School, Istanbul

25 of 127

What is a database schema?

Defines how data is organized in a relational database
Includes table names, fields, data types of fields, and relationships between tables

The British International School, Istanbul

26 of 127

The British International School, Istanbul

27 of 127

What is a relational database?

Uses columns (for attributes) and rows (for records)
Records also referred to as tuples
Tables are related to each other (using foreign keys)
Each table has a primary key

The British International School, Istanbul

28 of 127

Referential Integrity

Feature of relational databases
Every row has a an identifier (primary key)
Requires that every value in a foreign key column will be found in the primary key of the table from which it originated
Ensures that relations between tables are consistent

The British International School, Istanbul

29 of 127

Database Management Systems (DBMS)

A database management system (DBMS) is a set of programs that allows to read, store, change /extract data in a database
Examples: SQLite, MySQL, PostgreSQL,

The British International School, Istanbul

30 of 127

DBMS Components

Data Dictionary - Manages metadata
Data Safety - Backup and recovery management, data integrity checks
Query Processor - Accepts and interprets queries (in SQL) and returns appropriate output
Storage Engine - handles create, read, update, and delete (CRUD) operations
Concurrency - Allows multiple users to access database concurrently; Makes sure multiple users can’t modify the same data simultaneously
Security - Enforces user access policies

The British International School, Istanbul

31 of 127

Data Dictionary

Also called a “metadata repository”
A file or set of files that stores information about the database and the tables inside
Includes (at a minimum):

Names & Descriptions of Tables
Names of Fields
Data Types and Length of Fields
Relationships between Tables

The British International School, Istanbul

32 of 127

Data Dictionary Example

The British International School, Istanbul

33 of 127

Concurrency

Process of managing simultaneous updates or transactions at the same time
Prevents access by more than one user to the same row/record (row locking)
Makes sure than one row isn’t

written by one user while at the same time it is being written by a different user

Forces updates/transactions to

take place sequentially

The British International School, Istanbul

34 of 127

Role of DBMS in Security

Access rights - Which users are able to do what (read, write, etc.)
Audit trails - Record of any changes to database
Data locking - Locking rows currently being accessed
Validation - Making sure new data follows the rules
Encryption - Encrypting data in a database
Backups - continually updated copies that can be accessed in case existing database is lost or corrupted

The British International School, Istanbul

35 of 127

Database Transactions

A transactions is a collection of low-level tasks
In a database, it is a set of SQL

statements that are executed sequentially, as if they were one command

All the operations in a transaction are performed and if there is no error, only then are the changes are made permanent, or “committed.”
If not, they are reverted or “rolled back”.

The British International School, Istanbul

36 of 127

The British International School, Istanbul

37 of 127

ACID (Atomicity, Consistency, Isolation, Durability)

DBMS follows ACID principles for transactions:

Atomicity - All tasks in a transaction are performed, or none
Consistency - All data written must be valid according to existing rules
Isolation - No transaction will interfere with another
Durability - Once a transaction is complete, the change to the database is permanent, even in case of system failure

The British International School, Istanbul

38 of 127

What is the purpose of transactions?

There is never a case where changes to a database are incomplete, during which time another change takes place
Concurrent transactions are conducted sequentially
Using logs created at commitment, changes can be easily reverted (“rolled back”).

The British International School, Istanbul

39 of 127

Data Integrity

Data integrity is a broad and frequently used term in the next few slides.
It refers to 3 things:

Accuracy - The correct data (and relationships) are retained and preserved
Completeness - All necessary data is available
Validity - Data meets all predetermined rules

The British International School, Istanbul

40 of 127

Data Redundancy

Situation where the same piece of data is stored in two or more different places
These could in different tables or even the same table

The British International School, Istanbul

41 of 127

Data Redundancy - Pros & Cons

Pros
Faster data access speeds - more locations from which data can be accessed
Better data protection - in case of loss, data can be replaced from other location(s)

Cons
Data inconsistency - if data is updated at one location, other locations may not be updated as well
More storage space required (expensive)

The British International School, Istanbul

42 of 127

Normalization

A process by which larger tables in a database are divided into smaller tables while ensuring data integrity and reducing data redundancy
Seeks to eliminate:

Attributes (fields) with multiple values
Attributes that are repeated or

contains the same data

Unrelated or non-descriptive attributes
Attributes with redundant

information

The British International School, Istanbul

43 of 127

Why use normalization?

Reduce data redundancy (reduces overall data storage space)
Reduce table complexity (to make insertion, updates, and deletions less error-prone)
Make sure data is stored logically, to make querying more efficient

The British International School, Istanbul

44 of 127

The Normal Forms

Each of the 3 forms of normalization represent a set of standards that determine whether as database has been normalized or not
1st Normal Form (1NF)

Eliminate duplicate columns, columns with multiple types of values
Create separate tables for each group of related data (with unique primary keys)

2nd Normal Form (2NF)

Meet all requirements for 1NF
Eliminate partial dependency (columns dependent on one attribute of a composite primary key)

3rd Normal Form (3NF)

Meet all requirements for 2NF
Eliminate transitive dependency

The British International School, Istanbul

45 of 127

The British International School, Istanbul

46 of 127

The British International School, Istanbul

47 of 127

The British International School, Istanbul

48 of 127

The British International School, Istanbul

49 of 127

Normalization Steps

Split into separate logical tables (by entity)
What is associated ONLY with each entity?
Get rid of any attributes with multiple values by creating new tables.
Make sure that no attributes are only dependent on ONE primary key - otherwise new table.
Make sure that no attributes are only dependent on another attribute that is not the primary key, otherwise new table.
Make sure that tables are all linked via relationships (Primary Key → Foreign Key).
Make sure that every table has a Primary Key.

The British International School, Istanbul

50 of 127

The British International School, Istanbul

51 of 127

The British International School, Istanbul

52 of 127

The British International School, Istanbul

53 of 127

The British International School, Istanbul

54 of 127

The British International School, Istanbul

55 of 127

The British International School, Istanbul

56 of 127

The British International School, Istanbul

57 of 127

The British International School, Istanbul

58 of 127

The British International School, Istanbul

59 of 127

The British International School, Istanbul

60 of 127

Normalization Advantages

Less data storage required due to lack of duplicate information (redundancy)
Data is more likely to be consistent - less outdated or duplicate information; no illogical links between tables
Increased data security - data is easier to locate and therefore protect
Updates and more complex operations can be conducted more quickly and efficiently due to table structure
The fact that each record can only have one value in each field (atomicity) makes for simpler queries.
Tables are more logically organized, so database is overall easier to understand

The British International School, Istanbul

61 of 127

Anomalies

Prevented by 2NF normalization
Insertion Anomaly - when a row cannot be inserted due to missing data (most commonly a missing foreign key)
Deletion Anomaly - when certain attributes are lost due to the deletion of other attributes
Update Anomaly - when data is only partially updated in a database

The British International School, Istanbul

62 of 127

Anomaly Examples

Insertion anomaly: it is not possible to add a new department to the database without also having to add a member of staff at the same time
Deletion anomaly: if we want to remove Betty Flood from the table, we would also need to remove all data that is stored about the Geography department
Update anomaly: If the phone number for the English department changed to 307 instead of 301 it would need to be changed in two different records

The British International School, Istanbul

63 of 127

Database Administrator (DBA)

Ensure data in database is performant, secure, and recoverable

Responsible for:

Updating the database - adding/updating new data, relations, fields and tables
Maintaining security - assigning access levels and passwords to users
Managing backup procedures
Establishing recovery plan for database in case of disaster or malfunction

The British International School, Istanbul

64 of 127

Database Definition Language (DDL)

Set of commands that allow us to define and modify the structure and metadata of a database
Used to generate data dictionary
DDL commands are a part of SQL
Only available to DBAs (Database Administrators)

The British International School, Istanbul

65 of 127

Data Modelling

Process of creating a visual representation of a whole or part of an information system
Helps all stakeholders have a shared understanding of a system
Illustrates (at a minimum

type of data used
relationships between those types of data
how data is grouped and organized
data attributes

Three types of models: Conceptual, Logical, Physical

The British International School, Istanbul

66 of 127

Conceptual, Logical, Physical

The data model will take the information from the conceptual schema and logical schema to informs the physical model for the development of the database:

The British International School, Istanbul

67 of 127

Advantages of Data Modelling

Helps avoid issues such as:

redundancy
lack of integrity
lack of consistency

Helps developers develop the actual database
A lack of modeling may lead to a structurally deficient database

The British International School, Istanbul

68 of 127

ERD (Entity Relationship Diagrams)

An ERD represents the relationship between different entities in a database.

The British International School, Istanbul

69 of 127

The British International School, Istanbul

70 of 127

The British International School, Istanbul

71 of 127

The British International School, Istanbul

72 of 127

What is an object-oriented database?

Data is stored as objects
No tables
Objects contain both data and functions (methods) that can performed on this data
Objects are self-contained units
No SQL, data manipulated using self-contained methods
Methods and format determined by templates called classes

The British International School, Istanbul

73 of 127

Advantages of Object-Oriented Databases

Can store a larger number of data types (text, numbers, pictures, audio, video, etc.)
Better performance when complex data structures and relationships
Reusability - each object is defined by a class and classes can take functionality from other classes using inheritance to easily store different types of data
More accurately represents real-world entities

The British International School, Istanbul

74 of 127

Disadvantages of Object-Oriented Databases

Lower efficiency when data is simple and relationships are simple;
Relational tables are simpler
More user tools exist for relational databases
Standards and support for relational databases are more stable so changes in database are less likely to be required;
OODBS are less secure (no access rights or advanced security capabilities)
No universally agreed upon data model

The British International School, Istanbul

75 of 127

Data Warehouse

Central storage/repository of data from multiple sources, collected over time
Relational
Usually contains large quantities of data
Use by large organizations/businesses for data mining/analysis to explore useful trends

The British International School, Istanbul

76 of 127

Data Warehousing Advantages

Better Performance - System optimized for quick retrieval of and efficient analysis of data
Timely Access to New Data - ETL quickly consolidates data from multiple sources and transforms it into a standardized format for easy, quick querying
Standardization of Data - Data of disparate formats from multiple sources is standardized, leading to easy access and querying across an organization
Access to Historical Data - Both current data and data from the past is stored

The British International School, Istanbul

77 of 127

ETL (Extraction, Transformation, Loading)

Process by which data from multiple sources is collected, processed and sent to the data warehouse

Extraction - Structured and unstructured data is imported from a variety of sources and consolidated into a single repository
Transformation - Data is transformed to ensure data quality and accessibility

Deduplication - Removing duplicate data
Cleansing - Resolving inconsistent or missing values
Standardizing - Making sure that all values follow the same rules

Loading - Data is transmitted all at once or in intervals to destination (data warehouse)

The British International School, Istanbul

78 of 127

Data Warehouse vs. Database

Data Warehouse	Database
Mainly stores historical data	Mainly stores current data
TB’s of Data	GB’s of Data
Data maintained over time	Data always up-to-date
Users mostly have read-only access	Users usually have read/write access
Used for everyday transactions	Used for decision support
Optimized for more complex queries across larger sets of data	Optimized for lightning-fast CRUD (Create, Read, Update, Delete) queries on individual rows
Deals with relatively small number of users	Can handle thousands of users

The British International School, Istanbul

79 of 127

What is data mining?

The analysis of large data sets with the purpose of detecting previously unknown patterns or relationships
Data Mining Methods:

Cluster Analysis
Association Analysis
Link Analysis
Classification
Deviation Detection

The British International School, Istanbul

80 of 127

Data Mining Pros & Cons

Pros	Cons
Helps to understand behaviors, trends, and hidden patterns	May utilize sensitive personal information
Helps detect risk and fraud	Aggregated personal data may compromise privacy or anonymity
Helps analyze large amounts of data very quickly	Expensive
	Requires large databases

The British International School, Istanbul

81 of 127

Supervised Vs Unsupervised Learning

Supervised and unsupervised learning are two main types of machine learning.

In supervised learning, the model is trained with labeled data where each input has a corresponding output.

On the other hand, unsupervised learning involves training the model with unlabeled data which helps to uncover patterns, structures or relationships within the data without predefined outputs.

The British International School, Istanbul

82 of 127

The British International School, Istanbul

83 of 127

The British International School, Istanbul

84 of 127

Supervised Vs Unsupervised Learning

The British International School, Istanbul

85 of 127

Supervised Vs Unsupervised Learning

The British International School, Istanbul

86 of 127

Cluster Analysis

Cluster analysis partitions the data into groups that exhibit similar characteristics (“clusters”)
Makes no assumptions about the groups that emerge
Uses unsupervised learning - no prior input or training of algorithm required
Usage Examples: Finding a demographic of customers with similar spending habits, finding viewers on Netflix with similar watching habits (genre, time watched, etc.)

The British International School, Istanbul

87 of 127

Classification

A classifier or model is developed using training sets of data (supervised learning)
New data (relationships) are then added to the model and compared against the predicted outcome, based on the training data
Based on this comparison, a prediction is made

The British International School, Istanbul

88 of 127

Cluster Analysis vs. Classification

Cluster Analysis	Classification
Unsupervised Learning	Supervised Learning
Unlabeled data samples as input	Labeled data samples as input
No prior knowledge of relationships in data	Prior knowledge of relationships in data
Outputs groups of data points	Outputs groups of data points

The British International School, Istanbul

89 of 127

Association Analysis

Association analysis breaks up data sets by variables / by variables, such as gender, location, age.
Aims to find “interesting”

relationships in datasets

Looks for dependency of one data item on another
Unsupervised learning
Examples: Doctors see what symptoms often lead to what diseases, retailers can see what factors lead to purchases

The British International School, Istanbul

90 of 127

Link Analysis

Establish relationships associations between different entities in the same data set
Begins by deciding what constitutes a link between entities
Then sees which entities meet the criteria for these links and which do not, and to what extent
Unsupervised Learning
Example: Using cell phone data to determining the hierarchy in a criminal network

The British International School, Istanbul

91 of 127

Deviation Detection

Data mining method whose purpose is to detect the most significant deviations in data from the norm
Looks for anomalous patterns
Unsupervised Learning
Examples: Unusual switch in pre-electoral voting opinions;

Sudden pro-candidate or

anti-candidate sentiment in a particular state

The British International School, Istanbul

92 of 127

Spatial Database

Used to store spatial data / locational data / geometric data types
Examples: Points, polygons, lines, 3D shapes, coordinates, etc
Data models the structure of geometric/3D objects
Supports geometric functions

The British International School, Istanbul

93 of 127

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
Examples: Demographic, psychographic, behavioral and geographic segmentation

The British International School, Istanbul

94 of 127

Supplement

Option A Database

IB Computer Science

The British International School, Istanbul

95 of 127

Intro

Writing SQL Queries
Derived Fields
Joins
Channel Updates

This content will be added to the study guide and slides.

Caffeine +

ChatGPT +

Love of CS = The CS Classroom

The British International School, Istanbul

96 of 127

The British International School, Istanbul

97 of 127

SELECT Statements

SELECT - Used for SQL statements to get a set of data from table

FROM - Used to specify the table from which the data will come

SELECT ProductName, Price FROM Customers

Field(s)

Table

The British International School, Istanbul

98 of 127

Get All Values in a Field

SELECT ProductName FROM Products

SELECT ProductName, Price FROM Products

Table Name: Products

The British International School, Istanbul

99 of 127

SELECT ProductName FROM Products

Product Name
Chaise
Chang
Aniseed Syrup
Chef Anton’s Cajun Seasoning
Chef Anton’s Gumbo Mix

The British International School, Istanbul

100 of 127

SELECT ProductName,Price FROM Products

Product Name	Price
Chaise	18
Chang	19
Aniseed Syrup	20
Chef Anton’s Cajun Seasoning	22
Chef Anton’s Gumbo Mix	21.35

The British International School, Istanbul

101 of 127

Get All Values in a Field According to Criteria

SELECT ProductName FROM Products WHERE Price > 20

SELECT * FROM Products WHERE SupplierID <> 1

SELECT ProductID, Price FROM Products WHERE Price >= 18 and Price < 22 and Unit LIKE “36 boxes”

Table Name: Products

The British International School, Istanbul

102 of 127

SELECT ProductName FROM Products WHERE Price > 20

Product Name
Chef Anton’s Cajun Seasoning
Chef Anton’s Gumbo Mix

The British International School, Istanbul

103 of 127

SELECT * FROM Products WHERE SupplierID <> 1

ProductID	Product Name	Price	SupplierID	CategoryID	Unit
4	Chef Anton’s Cajun Seasoning	22	2	2	48 - 6 oz jars
5	Chef Anton’s Gumbo Mix	21.35	2	2	36 boxes

The British International School, Istanbul

104 of 127

SELECT ProductID, Price FROM Products WHERE Price >= 18 and Price < 22 and Unit LIKE “%boxes%”

ProductID	Price
5	21.35

The British International School, Istanbul

105 of 127

Comparing Strings with LIKE and ‘==’

SELECT ProductName FROM Products WHERE Unit == “36 boxes”

SELECT ProductName FROM Products WHERE Unit LIKE “%boxes%”

The British International School, Istanbul

106 of 127

SELECT ProductName FROM Products WHERE Unit == “36 boxes”

Product Name

Chef Anton’s Gumbo Mix

The British International School, Istanbul

107 of 127

SELECT ProductName FROM Products WHERE Unit LIKE “%boxes%”

Product Name
Chais
Chef Anton’s Gumbo Mix

The British International School, Istanbul

108 of 127

Table Name: Products

Sum

SELECT SUM(Price) FROM Products WHERE CategoryId = 2

SELECT SUM(Price)FROM Products WHERE Price <= 22

The British International School, Istanbul

109 of 127

Count (Counts how many records meet criteria)

SELECT COUNT(Price) FROM Products WHERE CategoryId = 2

SELECT COUNT(Price)FROM Products WHERE Price <= 22

Table Name: Products

The British International School, Istanbul

110 of 127

SQL Cheat Sheet

Get Field Data

SELECT Field1, Field2, …* FROM TableName

Get Field Data According to Criteria

SELECT Field1, Field2, …* FROM TableName WHERE Some_Criteria

The British International School, Istanbul

111 of 127

SQL Cheat Sheet (Part 2)

Calculate Sum

SELECT SUM(Field) FROM Table WHERE Some_Criteria

Count Number of Records

SELECT COUNT(Field) FROM Table WHERE Some_Criteria

The British International School, Istanbul

112 of 127

The British International School, Istanbul

113 of 127

The British International School, Istanbul

114 of 127

The British International School, Istanbul

115 of 127

The British International School, Istanbul

116 of 127

The British International School, Istanbul

117 of 127

The British International School, Istanbul

118 of 127

Derived Fields

Temporary ﬁeld created through query
Not affected by normalization
Created with query using AS Keyword

SELECT Total/3 AS Average FROM Students

The British International School, Istanbul

119 of 127

The British International School, Istanbul

120 of 127

The British International School, Istanbul

121 of 127

The British International School, Istanbul

122 of 127

JOINS

Used to get matching information from two tables
Results in a third (derived) table
Different type of JOINS, but INNER JOIN is most common on IB Exam

The British International School, Istanbul

123 of 127

SELECT

Customers.customer_id, Customers.first_name, Orders.amount

FROM

Customers INNER JOIN Orders ON Customers.customer_id == Orders.customer

The British International School, Istanbul