Unit 1 - Relational Database Model Flashcards
1
Q
ACID
A
An acronym that stands for atomicity, consistency, isolation and durability of data.
2
Q
Analytical Databases
A
A specialized type of database designed for storing, retrieving, and analyzing large volumes of data.
3
Q
API
A
Application Programming Interface:
A set of rules, protocols, and tools that allow different software applications to communicate with each other.
4
Q
Associative Entity
A
An entity that exists only to associate data in one entity with data in another.
5
Q
Attribute
A
A single piece of information within a record, also known as a field. (column)
6
Q
Back End
A
The part of the database system responsible for managing and storing data, including the DBMS and the physical storage devices. The back end is not directly accessible to end users.
7
Q
Business Analytics
A
The practice of using data analysis and statistical techniques to drive informed business decision making.
8
Q
Business Rule
A
Specific constraints and requirements that govern how data should be stored, processed, and managed in a database.
9
Q
Cardinality
A
AKA Multiplicity
Defines relationships in terms of whether they are one-to-one, one-to-many, or many-to-many.
10
Q
Chen Notation
A
A visual representation technique for entity-relationship modeling that uses rectangles to represent entities, diamonds for relationships, and lines to represent cardinality and participation constraints.
11
Q
Class
A
A grouping of similar objects with shared attributes and behaviors.
12
Q
Columnar Data Storage
A
Storing data in columns rather than in rows.
13
Q
Columns
A
The individual attributes of a table, also called fields. They are usually of the same data type and can contain various data, such as numbers, text, and dates.
14
Q
Common Business-Oriented Language (COBOL)
A
A popular programming language for data processing; systems were designed to handle batch processing rather than real-time transactions.
15
Q
Common Table Expressions (CTEs)
A
A feature introduced in ANSI SQL that allows temporary result sets for complex queries.
16
Q
Conceptual Data Model
A
A high-level representation of a system’s data requirements and structure that establishes the entities, their attributes, and relationships between entities in a relational database.
17
Q
Concurrency
A
The ability for multiple users or processes to access and change a file simultaneously.
18
Q
Connection Pooling
A
A technique for managing and reusing database connections.
19
Q
Constraints
A
A rule that restricts the values that can be stored in a column or a table. Constraints are used to ensure the integrity of the data in a database.
20
Q
Crow’s Foot Notation
A
A graphical representation technique uses various symbols to represent cardinality, relationships, attributes, and relationships between entities, such as crow’s feet, lines, and diamonds.
21
Q
Data
A
Known as “raw facts,” the data we collect, like phone numbers or addresses.
22
Q
Data Definition Language (DDL)
A
Commands that create and remove schema components in a database.
23
Q
Data Manipulation Language (DML)
A
Commands that allow interaction with the data in a database.
24
Q
Data Masking
A
A method of anonymization that replaces sensitive data with consistent, nonsensitive values.
25
Data Migration
The process of transferring data from one database to another while ensuring the data's integrity, accuracy, and consistency.
26
Data Warehouse
A backend enterprise-level system used for storing data that is used for analysis and reporting.
27
Database
A structured and organized collection of data that is stored electronically.
28
Database Administrator (DBA)
A user of the database management system who ensures that the database is running correctly.
29
Database Designer
A database architect of the database who ensures that it fits the business needs and functions optimally.
30
Database Locking
A mechanism for controlling access to a database in a multiuser environment.
31
Decision Support System (DSS)
A computer-based tool that helps individuals and organizations make informed decisions by providing access to data, analysis, and interactive tools to support the decision-making process.
32
Delimiter
A sequence of one or more characters for specifying the boundary between separate, independent regions in plain text, mathematical expressions, or other data streams.
33
Disk Caching
A way to improve performance by reducing the need to access physical storage for frequently accessed data.
34
Document-Oriented Database
A non-relational database that pairs a key with a complex data structure.
35
Encapsulation
Groups data and operations that operate on it into a single entity.
36
End User
The user of the applications to run the day-to-day operations of the organization.
37
End-User Interface
Where ordinary database users interact with the data.
38
Entity Relationship (ER) Model
A visual representation that illustrates the structure and relationships within a database or data model.
39
Entity-Relationship Diagram (ERD)
Used to visualize the relationships among entities (objects or concepts) in a system or database, as well as their structure, attributes, and interactions.
40
Extended Relational Database Model (ERDM)
Extensions of traditional relational database models that add enhanced functionality. A relational model based on ERDM introduces object-oriented concepts, such as inheritance, encapsulation, and methods.
41
Flat File
A data file that is not related to or does not contain any linkages to another file.
42
Foreign Key
A column or a combination of columns that refers to the primary key of another table.
43
Fragmentation
A condition in which a file is not stored contiguously on the disk, so the read/write head has to move around, picking up the pieces of the file when it is requested.
44
Front End
A user interface or application that enables users to interact with a database without directly interacting with the underlying DBMS.
45
Graph Store
A non-relational database that links data through edges, nodes, and properties.
46
Graphical User Interface (GUI)
A type of user interface through which users interact with electronic devices via visual indicator representations.
47
Hardware
CPU, RAM, and disk drive speed all have an influence on how well a database runs on a computer system.
48
Hierarchical Model
A database model that consists of a series of one-to-many relationships, with each parent item having one or more child items.
49
Hierarchical Models
Databases that store data as records and organize them into a tree-like structure where one parent node has many child nodes connected to it through links.
50
In-Memory Databases
Databases that rely primarily on memory for data storage and retrieval.
51
Indexes
An index is a data structure that speeds up the retrieval of data from a table. Indexes are created on columns that are frequently used in queries.
52
Indexing Bottleneck
Occurs when the process of maintaining and updating indexes becomes a performance bottleneck.
53
Information
Data in a context that gives it meaning.
54
Inheritance
OODM uses inheritance to organize and structure data models. For example, objects inherit attributes and methods from classes.
55
Input/Output (I/O) Bottleneck
Occurs when there is a bandwidth limitation in reading from or writing to the storage device.
56
Isolation Level
The degree to which one transaction must be isolated from the effects of other concurrent transactions.
57
JavaScript Object Notation (JSON)
A lightweight, popular data interchange format commonly used in databases. Using it, one can represent data using key-value pairs or nested structures in a readable, easy-to-understand format.
58
Key-Value Store
A simple non-relational database that pairs each key with a single value.
59
Knowledge
Information or data that has been organized, stored, and made accessible in a structured manner.
60
Latency
A delay between when a request is issued and its fulfillment.
61
Logical Model
A high-level structure and technical map for a database that specifies primary and foreign keys and has been normalized.
62
Memory Bottleneck
Occurs when the database system either does not have enough physical memory allocated for its use or is unable to access or manage memory resources effectively.
63
Memory Leak
A situation where a program allocates memory but then fails to release it when the memory is no longer needed.
64
Metadata
Data about data, which describes characteristics or relationships of the data.
65
Method
An action that can be performed on an object to which it is assigned.
66
MongoDB
A popular example of a NoSQL flat file database that stores data in an object-oriented way.
67
Multifactor Authentication (MFA)
The practice of requiring two different forms of authentication for access to a system, such as a username/password and a PIN or access code.
68
Network Model
The database in the network model represents objects and their relationships in a flexible manner. The schema differs from other hierarchical or lattice models because it is viewed as a graph with nodes and arcs representing objects and relationships.
69
NoSQL
Not only SQL; a type of data system designed around unstructured data that needs to be processed using a high availability and scalable process.
70
NoSQL Database
A database that can work with semi-structured or unstructured data by enabling the storing and querying of data outside the traditional structures found in relational databases.
71
Non-Relational Databases
Databases that do not use the traditional tabular relational model that is used in relational database management systems.
72
Normalization
The process of applying design rules to a database to ensure that its table structures minimize duplication and ensure efficiency and integrity.
73
Object
An organizing unit in object-oriented databases that includes a fact, information about the fact, and the relationships between facts.
74
Object-Oriented Database Model (OODM)
An object-oriented database model that organizes and represents data as objects, including both data and behavior.
75
Object-Oriented Databases (OODBMS)
A database model that directly integrates object-oriented programming principles into database management, storing data as objects.
76
Object-Oriented Programming (OOP)
A way of organizing computer programs around objects, which are self-contained units that represent real-world entities or concepts.
77
Object-Relational Database (ORDBMS)
A type of database similar to a relational database but with an object-oriented database model; objects, classes and inheritance are directly supported in database schemas and in the query language.
78
Object/Relational (O/R) Database Model
A type of database model that bridges the gap between object-oriented and relational databases by enabling objects to be directly stored and retrieved from the database.
79
Online Analytical Processing (OLAP)
Software technology that is used to analyze business data from different points of view.
80
Online Transaction Processing (OLTP) Databases
Operational databases, which are used for handling real-time transactions daily.
81
Parallel Processing
The simultaneous execution of multiple operations across multiple CPU cores in a distributed computing environment to improve performance.
82
People
Database administrators, database designers and users are the people who manage, design, and use databases. Each type of person has specific needs and responsibilities when working with a database.
83
Physical Model
A technical plan for implementing the details outlined in the conceptual and logical models using a specific database management system (DBMS).
84
Polymorphism
Enhances OODM's code reusability, modularity, and flexibility.
85
Primary Key
A primary key is a column or a combination of columns uniquely identifying each row in a table. The primary key cannot contain duplicate values.
86
Principle of Least Privilege
A security strategy that gives users only the minimum privileges they need to do their work.
87
Procedures
Rules on how a database is used or designed. Think of this as similar to how you would design a business process, but here, how and what business data is stored, how it is used, who accesses it, and how it can be accessed.
88
Query Optimizer
A software that determines the most efficient way to execute a query.
89
RDBMS
Relational Database Management System.
90
Referential integrity
Rules ensuring that data relationships remain valid and consistent.
91
Relation
Also known as a table, a row-and-column structure for holding data in a relational database.
92
Relational Database
A database that has many tables, with shared common columns between tables.
93
Rows
The individual records in a table. They contain the values of all the columns in the table.
94
SQL Engine
The software that runs the database and is normally not accessible by end users.
95
Schema
The conceptual organization of an entire database.
96
Segment
A structured set of data elements.
97
Sharding
A database architecture strategy that breaks up a large database into smaller, more manageable parts called shards.
98
Software
The operating system and RDBMS (Relational Database Management System) that influence performance and management/administration of a database.
99
Spreadsheet
A row-and-column grid in which structured data tables can be created.
100
Structured Query Language
A programming language that allows data to be retrieved and manipulated in a relational database.
101
Structured Query Language (SQL)
A specialized programming language used for managing and manipulating relational databases.
102
Subschema
The part of a database that applications interact with.
103
System Administrator
A person who oversees the entire database/information system to ensure that everything is operating optimally.
104
Tables
The basic unit of data storage in a relational database. They are organized into rows and columns, each representing a single record. The columns in a table represent the attributes of the record, and the rows represent the values of the attributes.
105
Transactional Database
A database system designed for managing the day-to-day operations of an organization.
106
Tuple
Also known as a record, one row in a relation (table) containing all the data for one item.
107
UML Class Diagram Notation
A notation system that illustrates the structure and relationships among classes in an object-oriented system, showing the attributes, methods, and associations between them.
108
Universally Unique ID (UUID)
A 128-bit identifier that is guaranteed to be unique across both space and time. It is often used as a primary key or unique identifier for records in a database, particularly in distributed and decentralized systems, where ensuring uniqueness is crucial.
109
Validation Rule
A constraint that prevents invalid, inaccurate, or inconsistent data from being populated into database entities.
110
Variety
The diverse types and formats of data and big data. This includes structured data (e.g., traditional databases), semi-structured data (e.g., JSON, XML), and unstructured data (e.g., text, images, videos). The variety of data sources and formats necessitates flexible data storage and processing approaches.
111
Velocity
The speed of data generation and the need to process and analyze data in real time.
112
Vendor Lock-In
A situation in which a customer or organization becomes heavily dependent on a particular vendor's products, services, or technologies to the extent that it becomes difficult or costly to switch to an alternative vendor.
113
Volume
The enormous amount of data generated and collected. Big data storage and processing solutions are required to handle massive volumes of data efficiently.
114
Wide-Column Store
A non-relational database that allows the names and format of columns of its tables to vary from row to row.
115
XML Hybrid Database
A database type that bridges the structured formatting of XML and the relational model of modern databases.
116
SQLite
SQLite is a self-contained database that is file-based and completely open source
Pros
Known for portability, reliability and performance even with low memory. ACID compliant.
Serverless, so it accesses the database and reads and writes to the database disk file directly. If programs can access the disk that SQLite is on, they can access it directly.
Con is concurrency - multiple processes can read and query simultaneously, but only one process can make changes to the database at a time.
No way to set permissions for different users - as long as the operating system allows users to access the disk file, the user can access the database.
117
SQL Server
SQL Server is a robust Microsoft product with a variety of editions, from its smaller-scale Express to an
Enterprise edition that has high-end data center capabilities. SQL Server isn’t only a database engine; it has
many other business intelligence tools. SQL Server uses its version of SQL with Transact SQL. It is a tool that is
highly supported, but it also comes at a high price. For example, an Enterprise edition license can cost upwards
of $14,000 per core. Unlike Microsoft Access (Microsoft's consumer-level database application), it can run on
Linux as well as in the cloud environment.
118
Oracle
Oracle has established itself as one of the top database systems used in enterprise solutions. It is owned by
Oracle Corporation. Similar to SQL Server, many different variants of Oracle are available, so there is one to
meet any organization's needs.
Oracle has no open-source version of its DBMS product. However, the XE or Express edition, which is the basic
edition, is free if used for training purposes. Oracle charges for it only for enterprise use. Oracle is one of the
most expensive DBMSs, and determining the end cost can be quite complex because of all the many optional
tools and features available.
Oracle also offers PL/SQL, a procedural language that provides developers with powerful tools for grouping
procedures and functions together into packages. Even with Oracle's high cost, many organizations prefer it
because it is fast, highly reliable, and easy to support.
119
MySQL and MariaDB
MySQL (now owned by Oracle) and MariaDB are some of the most popular open-source databases. MySQL,
which is designed for speed and reliability, powers many of the largest databases, including Twitter, YouTube,
Airbnb, Facebook, and Netflix. Once MySQL was taken over by Oracle, the original MySQL developers split off
to create MariaDB as a fork of the MySQL code, as they worried that Oracle had planned to kill MySQL. MySQL
now exists both in an open-source version and as a commercial offering, known as MySQL Enterprise Edition.
While MariaDB remains open source, MariaDB Corporation, the company behind MariaDB, offers MariaDB
Enterprise as a commercial service. Its subscribers are not paying for MariaDB itself, but for services, support,
and extra features.
Like most other DBMSs, MySQL must be installed on a server. Many third-party tools are available for use with
MySQL/MariaDB, including phpMyAdmin.
120
PL/SQL
PL/SQL, a procedural language that provides developers with powerful tools for grouping
procedures and functions together into packages. Offered by Oracle
121
PostgreSQL
PostgreSQL is one of the most advanced open-source relational databases. It was created to be standardcompliant and highly extensible. Standard compliance is a significant element of PostgreSQL that differentiates
it from other databases. It is an object/relational database, meaning that although it is primarily a relational
database, it offers functionality like table inheritance implemented in object databases. PostgreSQL has no
corporate owner; it is maintained by a global network of volunteers. However, some companies sell support
services and tools for PostgreSQL as a commercial product.
PostgreSQL isn’t as widely used as MySQL due to a lack of additional third-party tools. However, PostgreSQL is
compatible with many programming languages and platforms, making it much easier to migrate the database
from one operating system to another or integrate it within a given tool. You will work with PostgreSQL later in
this course.
122
How to automatically increment? MySQL? SQL Server? ANSI SQL?
MySQL uses AUTO_INCREMENT
SQL Server uses IDENTITY
ANSI SQL uses GENERATED ALWAYS AS IDENTITY
123
ANSI SQL
ANSI SQL standard also defines a way to make SQL commands portable and consistent across different database management systems (DBMS) with standard syntax and semantics for querying, updating , and managing relational databases. However, many vendors also provide proprietary extensions and features that go beyond the standard
Ex: To limit rows returned in a query, SQL Server has TOP, while Oracle uses ROWNUM
124
SQLite Data Types
REAL: A decimal number with a relatively low level of precision.
BLOB: A binary file such as an image file. BLOB is an acronym for Binary Large Object.
NULL: The absence of a value or the unknown.
INTEGER: A whole number. (Booleans are considered integers of 0,1)
(If a date is stored as in integer, it is stored as the number of seconds since midnight on January 1, 1970. There are built-in date and time
features to change between those values.)
TEXT: Character strings.
(Dates are stored as text strings.)
Tables can be created without any data types at all, which can be confusing. For example, you could run a SQL
command like this:
CREATE TABLE myTable (a, b, c);
This would create a table named myTable with the column names a, b, and c that have no data types defined.
Anything of any size could be stored in those columns.
125
Composite Keys
The SQLite database also supports composite keys, which use multiple columns to create unique identifiers for
each row. A composite key is defined by listing the columns separated by commas within the PRIMARY KEY
constraint.
126
triggers
triggers perform actions in response to specific database events, such as
insertion, deletion, or updating of data.
127
INNER JOIN
INNER JOIN returns only matching rows from both tables
128
LEFT JOIN
LEFT JOIN (AKA LEFT OUTER JOIN) returns all rows from the left table, which includes matching rows from the right table
129
RIGHT JOIN
RIGHT JOIN (AKA RIGHT OUTER JOIN) returns all rows from the right table, which includes matching rows from the left table
130
FULL JOIN (AKA FULL OUTER JOIN)
FULL JOIN is performed, all rows from both tables are returned, including unmatched rows, with NULL values
for nonmatching rows
131
Some of the key differences between ANSI and MySQL/MariaDB include:
Data types: MySQL/MariaDB supports all the same data types as ANSI SQL, but there are a few minor
naming differences. For example, ANSI SQL uses INTEGER, and MySQL/MariaDB uses INT.
Storage engines: MySQL/MariaDB supports multiple storage engines, each with its own features. The ability
to choose a storage engine is a MySQL-specific feature.
MySQL Workbench: MySQL provides a graphical user interface called MySQL Workbench for database
design, development, and administration. It can also be used with MariaDB.
User-defined values: MySQL/MariaDB allows the use of user-defined variables, which are specific to
MySQL.
Full-text search: MySQL/MariaDB has robust full-text search capabilities that go beyond ANSI SQL.
LIMIT and OFFSET: MySQL/MariaDB allows the use of LIMIT and OFFSET clauses with the UPDATE and
DELETE statements; ANSI SQL does not.
SHOW and DESCRIBE: MySQL/MariaDB has specific extensions to the SHOW and DESCRIBE statements,
providing additional information about databases, tables, and indexes.
Non-standard date and time functions: MySQL/MariaDB includes some date and time functions that are not
part of the ANSI SQL standard.
132
Constraints
Constraints are rules that limit what data can be stored in a record
133
CLOB
character large object data type (might hold large character based objects like XML data, file data, or a temp table)
134
BLOB
Binary Large Object data type (binary file such as an image)
135
Bottleneck
A bottleneck is a degradation in performance that occurs when demand for services or resources exceeds what
is available.
136
Latency
a delay between when a request is issued and its fulfillment. For example, when an application requests a file
from a hard disk, the time it must wait for that file to be read and delivered is latency.
137
RAID
Redundant Array of Ifndependent Disks
It involves combining multiple physical disks into a single logical unit
138
Fragmentation
Fragmentation refers to a
condition in which a file is not stored contiguously on the disk, so the read/write head has to move around,
picking up all the pieces of the file when it is requested.
Not necessary on a solid-state drive (SSD).
139
Query pagination
retrieves and displays a large set of data in smaller, manageable chunks or
pages instead of fetching and displaying it all at once
140
Disk caching
storing a portion of the database in the computer's memory to speed up
read operations because accessing data from memory is significantly faster than reading it from the
disk.
141
in-memory databases
databases that rely primarily on memory for data storage and
retrieval can increase performance, as can increasing the size of a disk's cache.
142
indexing
involves making a list of all the values in a certain column to help speed up searches- used for frequently accessed columns
143
Inheritance
organize and structure data models by having objects inherit attributes and methods from classes
144
Encapsulation
group data and operations that operate on it into a single entity. It hides an object's inner workings and details, exposing only the interfaces and methods necessary to access and manipulate its data, which helps data integrity, preventing the object's internal state from being modified
145
Polymorphism
Objects of different types can be accessed
through a unified interface, simplifying the implementation of complex systems. "Programming to an interface" is
enabled by polymorphism, where objects can be treated generically based on shared behavior rather than
specific type. Maintainability and scalability are enhanced by loose coupling between objects.
146
