Databases IB SL + HL

Question 1

Distinction between Data and Information

Answer 1

Data is just raw facts, numbers and figures
(e.g. 1975)

Information is data that has been given a context and meaning.
(e.g. The Vietnam war ended in 1975)

Question 2

Database

Answer 2

An organized collection of structured information.

Question 3

What is the software called that creates, defines, and manipulates a database?

Answer 3

Database Management System (DBMS)

Question 4

What are the things at the top of columns in a database that describe what’s inside that column?

Answer 4

Fields/Attributes

Question 5

What is a row in a database referred to?

Answer 5

A record

Question 6

3 Benefits of a database

Answer 6

• Enables for processing/querying of data
• Can easily generate reports
• Standardized (can be used by multiple systems)

Question 7

Data Verification (in context of databases)

Answer 7

This is the checking that input matches expected values in database

E.g. login details of a user matches those stored in the database

Question 8

Data Validation (in context of databases)

Answer 8

Checks that input follows rules for the type of input entered

For example: CVV code should be 3 numerical digits

Question 9

Entity

Answer 9

A real-world object or person represented by a record in a database

Question 10

Primary Key

Answer 10

A unique key used to identify a given record in a database

Question 11

Foreign Key

Answer 11

A field in one table that refers to the primary key field in another table. It is used to connect (relational) databases.

Question 12

6 different types of data types in a database

Answer 12

• Text:
  Combination of letters, numbers or symbols. Basically a string.
• Character:
  In individual letter, number or symbol
• Boolean:
  A binary value (True/False, Yes/No)
• Integer:
  A whole number
• Real:
  A decimal number
• Datetime:
  A date and/or time

Question 13

Write the SQL to get ProductName from the table Products where the price is more than 20 but less than 25

Answer 13

SELECT ProductName FROM Products WHERE Price > 20 AND Price < 25

Question 14

Secondary Key

Answer 14

Keys that are also capable of functioning as a primary key. Also known as the alternate key.

Question 15

2 differences between Primary and Secondary Key

Answer 15

Primary Key - Cannot be Null, only one primary key possible

Secondary Key - Can be Null, multiple secondary keys possible

Question 16

Candidate Key

Answer 16

Umbrella term for keys that can uniquely identify each record in a database

Both primary and secondary keys are candidate keys

Question 17

Composite Primary Key

Answer 17

When one field is not enough to uniquely identify records, two fields can function together as the primary key

Question 18

Database schema

Answer 18

A diagram of how data is organized in a relational database.

This means a diagram showing table names, fields, data types of fields, and relationships between tables

Question 19

Records can also be referred to as?

Answer 19

Tuples

Question 20

Referential Integrity

Answer 20

This is a feature of relational databases. What this means is every row has a primary key, and that every value in a foreign key column will be found in the primary key of the table from which it originated.

Question 21

Concurrency, and what does it use to prevent more than one user accessing the same row/record at the same time?

Answer 21

This is the process referred to managing simultaneous updates or transactions at the same time. It ensures updates/transactions occur sequentially to avoid collision between users’ actions.

Row locking

Question 22

Data Dictionary

Answer 22

A file or set of files that stores how the information and tables should be organized and stored in a database

Question 23

How does DBMS provide security to a database? (5 ways)

Answer 23

• Access Rights
• Audit trail (Record of any changes to the database)
• Data/Row Locking
• Encryption
• Backups - Continually updated copes that can be accessed in case the original database is lost or corrupted

Question 24

Transaction

Answer 24

A sequence of one or more SQL statements that are executed as a single unit of work. A transaction can either be committed (applied to the database) or rolled back (undoing something). Essentially, it allows us to make temporary changes, making data integrity easier.

Question 25

Different states in transactions?

Answer 25

Active State Partially Commited State Commited State Failed State Aborted State Terminated State

Question 26

What is the acronym for ACID?

Answer 26

Atomicity: All tasks in a transaction are performed if successful. Else no tasks are performed. Consistency: All data written must be valid according to existing rules. Isolation: No transactions will collide/interfere with another. Sequential execution of transactions. Durability: Once a transaction is complete, the change to the database is permanent, even if there is system failure.

Question 27

Why do we have atomicity?

Answer 27

They make sure there is never a case where there were incomplete changes.

Question 28

Log

Answer 28

Records every change making it easier to roll back

Question 29

Data Integrity

Answer 29

Accuracy: The correct data and relationships are retained and preserved Completeness: All data needed is available Validity: Data meets all predetermined rules

Question 30

Audit Trail

Answer 30

This records all changes, login attempts, and other security related events to help detect and investigate security breaches.

Question 31

Data Redundancy

Answer 31

This is what happens where the same piece of data is stored in multiple places. It can happen in different or the same tables.

Question 32

Data Redundancy 2 Pros & 2 Cons

Answer 32

Pros: - Faster data access speeds (more locations from which data can be accessed) - Better data protection: In case of loss of data, it can be replaced from another location Cons: - Data inconsistency: If data is updated at one location, other locations may not be updated as well - Takes more memory (more expensive)

Question 33

Normalization

Answer 33

This is the process in which larger tables in a database are divided into smaller tables to reduce data redundancy while ensuring data integrity.

Question 34

What are the different Normal forms, and at each step what do they seek to eliminate?

Answer 34

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 the requirements for 1NF - Eliminate partial dependency (Columns that are dependent on one attribute of a composite primary key) 3rd Normal Form (3NF) - Meet all the requirements for 2NF - Eliminates transitive dependency

Question 35

Why do we normalize databases?

Answer 35

- More data storage - Increased Data integrity - Makes queries simpler and more efficient

Question 36

3 types of anomalies that can occurs without normalization

Answer 36

Insertion anomaly: When you can't insert a new record without having to insert unrelated data too. Deletion anomaly: When deleting one record causes you to lose other important information (example: Deleting the last student in a course also deletes the course details) Update anomaly: Updating a value in one place, but it doesn't automatically update everywhere else that value appears leading a risk of data inconsistency.

Question 37

What are DBA, and 3 things they are responsible for?

Answer 37

Database Administrators They are responsible for: Training employees to understand and use the database Maintaining security - Assigning different levels of access to users Managing and creating backups

Question 38

What are DDL commands

Answer 38

Database Definition Language DDL commands are a part of SQL. They are a set of commands that allow us to define and modify the structure and metadata of a database. They are only available to DBAs. Examples such as: CREATE ALTER DROP TRUNCATE

Question 39

What is Data Modelling in databases

Answer 39

This is the process of creating a visual representation of all or some parts of a database. They help all stakeholders have a shared understanding of a system.

Question 40

What does Data Modelling set out to illustrate (at a minimum)

Answer 40

Type of data used Relationships between those types of data How data is grouped and organized Data attributes

Question 41

Conceptual Data Model

Answer 41

Conceptual: - High level view of what data exists and how it's related. - Does not really care about how the data will be stored - Often created with ERDs (shows entities, relationships and attributes)

Question 42

Logical Data Model

Answer 42

- Focus on structure, rules and keys - Defines tables, primary keys and foreign keys - Normalization often happens here - Specifies data types and constraints (e.g. NOT NULL)

Question 43

Physical Data Model

Answer 43

- Shows how the DB will be built on a specific DBMS - Includes indexes, storage and partitions - Adjusted for SQL dialects

Question 44

Advantages of data modelling reduced data ____ (3 different answers)

Answer 44

Reduces data: redundancy lack of integrity lack of consistency

Question 45

What is OOD?

Answer 45

Object-oriented database This is when data is stored as objects, and each object contains both data and methods that can be performed on this data. SQL is not required, data is manipulated by these self-contained methods. The format and methods used in the OOD are determined by templates called classes.

Question 46

3 advantages of OOD

Answer 46

- Can store a larger number of data types - Reusability (through inheritance of classes) - More accurately represents real-world entities

Question 47

3 disadvantages of OOD

Answer 47

- OODs are less secure (No levels of access) - Not universally agreed upon - Lower efficiency when data and relationships are simple

Question 48

Data warehouse

Answer 48

Essentially a relational database on a much larger scale. Often used by businesses for data mining/trend analysis

Question 49

3 Advantages of Data warehousing

Answer 49

- Better performance (Data warehouses are optimized for quick retrieval and analysis of data) - Standardization of Data due to ETL -Access to both Historical data and current data

Question 50

ETL

Answer 50

Extraction, Transformation, Loading 1) Extraction - Structured and unstructured data is imported from multiple sources into a single repository 2) Transformation- Data is transformed to ensure data quality and accessibility (removing duplicate data, standardizing) 3) Loading - Data is transmitted all at once or in intervals to the data warehouse

Question 51

3 differences between data warehouse and a database

Answer 51

Database: - Mainly stores current data - GBs of Data - Deals with thousands of users Data Warehouse: - Mainly stores historical data - TBs of data - Deals with relatively smaller number of users

Question 52

Data mining

Answer 52

This is the process of analyzing a large data set with the goal of finding new trends or relationships

Question 53

Data Mining 3 pros

Answer 53

- Helps to understand behaviors and trends - Helps detect risk and fraud - Helps to analyze large amounts of data very quickly

Question 54

Data Mining 3 Cons

Answer 54

- Expensive - May use sensitive personal information - Requires a large amount of data

Question 55

Cluster Analysis

Answer 55

A method of data mining. This segments data into groups that have similar characteristics (called "clusters") It uses unsupervised learning (no prior training of the algorithm required) For example, Netflix similar watching habits

Question 56

Classification

Answer 56

A method of data mining. This is when a model is developed using training data (supervised learning) to make predictions of outcomes for new data added to the model based on the training data.

Question 57

Association Analysis

Answer 57

A method of data mining Association analysis breaks up data sets by variables (such as gender, location, etc.) and aims to find interesting relationships in datasets where one data item is often dependent on other. For example: Doctors see what symptoms often lead to which diseases

Question 58

Link Analysis

Answer 58

A method of data mining. Often used in an anti-criminal situation. It establishes relationships between different entities in a data set. It does this by first seeing what could link two entities. It then checks which entities match this criteria, to what extent, and which do not.

Question 59

Deviation Detection

Answer 59

A method of data mining Deviation detection's purpose is to detect the most significant anomalies in a data set. It uses unsupervised learning.

Question 60

Spatial Databases, and what can it be used to do?

Answer 60

Databases that are used to store spatial data and data linked to location. For example: Points, polygons, coordinates, 3D shapes The data in a spatial database can be used to model the structure of geometric/3D objects and other geometric functions

Question 61

Data Segmentation

Answer 61

This is the process of taking your data and diving up and grouping similar data together based on chosen characteristics so it can be used more efficiently. Often used in marketing with demographics.