WSI Flashcards
Introduction to Information
1
Q
refers to databases that don’t follow the traditional relational model used by SQL databases. The term can be interpreted as “non-SQL” or “not only SQL.” NoSQL databases offer more flexibility and natural data storage compared to SQL databases. NoSQL is a broader database management approach, while SQL is specifically a query language.
A
NoSQL
2
Q
database
A
Key-value database
Document database
Wide-column database
Graph database
3
Q
is information that’s set up for easy access, management and updating.
A
database
4
Q
HISTORY: Early databases used flat files, which were unstructured and inefficient. Hierarchical and network models were developed to improve organization and relationships between data. The relational database model, introduced by Edgar F. Codd, revolutionized database management by using tables with rows and columns. His ideas led to the development of SQL (Structured Query Language).
A
flat files -> Hierarchical -> network -> relational (Edgar F. Codd)
5
Q
define what constitutes a true relational database management system. The rules cover various aspects of data storage, access, manipulation, and integrity.
While no commercial database fully implements all 12 rules, they
serve as an ideal standard for relational database systems.
A
Codd’s rules
6
Q
Pioneered relational concepts and SQL.
A
IBM System R
7
Q
Early commercial RDBMS inspired PostgreSQL
A
Ingres
8
Q
First commercial RDBMS, gaining market dominance.
A
Oracle
9
Q
Popularized relational databases for microcomputers
A
BASE II
10
Q
Robust, flexible, and advanced features.
A
PostgreSQL
11
Q
Fast, simple, and widely used for web applications.
A
MySQL
12
Q
Fork of MySQL with additional features and optimizations
A
MariaDB
13
Q
Microsoft’s RDBMS for enterprise environments.
A
MS SQL Server
14
Q
IBM’s RDBMS for high availability and scalability.
A
DB2
15
Q
The central component that stores, manages,
and processes data.
A
Server
16
Q
Applications that interact with the database
by sending requests to the server.
A
Client
17
Q
Clients send requests to the server,
and the server processes them and returns results.
A
Communication
18
Q
Centralized management, improved security,
better resource allocation, and scalability
A
Benefits
19
Q
- Developed by IBM researchers Donald D. Chamberlin and Raymond F. Boyce in the 1970s
- Based on Codd’s relational model.
- Designed for querying, manipulating, and managing data in relational databases.
- Standardized by ANSI and ISO.
- Widely used in RDBMS such as MySQL, PostgreSQL, Oracle, and Microsoft SQL
A
SQL - Structured Query Language
20
Q
- Unique Features: AUTO_INCREMENT, LIMIT, REPLACE INTO
- Use Case: Web development and applications
A
MySQL
21
Q
- Unique Features: JSON/JSONB, WINDOW FUNCTIONS, RETURNING clause
- Use Case: Complex queries, data integrity, high concurrency
A
PostgreSQL
22
Q
- Unique Features: TOP, TRY-CATCH, MERGE, CTEs
- Use Case: Enterprise-level applications, Microsoft ecosystem
A
Microsoft SQL Server (T-SQL)
23
Q
- Unique Features: CURSORS, TRIGGERS, SEQUENCES, procedural extensions
- Use Case: Large enterprises requiring complex business logic
A
Oracle SQL (PL/SQL)
24
Q
- Unique Features: Single-file database, limited concurrency, no user management
- Use Case: Embedded systems, mobile apps, small-scale software
A
SQLite
25
* Unique Features: Virtual columns, storage engines, compatibility with MySQL
* Use Case: Performance-enhanced alternative to MySQL
MariaDB
26
* Unique Features: PureXML, multi-dimensional clustering, RCAC
* Use Case: Large organizations requiring high throughput and complex queries
IBM DB2
27
For text fields like Title, Author, Category, ISBN, Publisher, Address, and Phone.
NVARCHAR(200):
28
The process of organizing data to minimize redundancy and ensure data integrity.
Normalization
29
A set of rules for achieving normalization.
Normal Forms
30
Data in a row-column intersection should be indivisible
Atomicity
31
* Unique Identifier: A primary key uniquely identifies each record.
* Avoid Changing: Primary keys should be stable to prevent errors.
* Artificial Primary Key: A generated integer or other unique value.
Primary Key
32
Separate Related Data: Move columns related to other entities into separate tables (e.g., publisher, author).
Redundancy
33
* Foreign Key Constraints: Automatically enforce referential integrity by ensuring foreign key values point to existing primary key values.
* Data Integrity: Prevent orphaned records and broken references.
Constraints: Ensuring Data Integrity
34
* SQL is declarative, allowing users to specify the desired result without detailing the process.
* This simplifies querying and makes it efficient for large datasets.
Declarative Language
35
* Different database systems have their own SQL dialects with variations in syntax and features.
* This provides flexibility and optimization for specific use cases.
SQL Dialects
36
* DQL (Data Query Language): Retrieves data.
* DDL (Data Definition Language): Defines database structure.
* DML (Data Manipulation Language): Modifies data.
* DCL (Data Control Language): Manages permissions and security.
* TCL (Transaction Control Language): Manages transactions.
SQL Sublanguages
37
* DQL (Data Query Language):
Retrieves data.
38
* DDL (Data Definition Language):
Defines database structure.
39
* DML (Data Manipulation Language):
Modifies data.
40
* DCL (Data Control Language):
Manages permissions and security.
41
* TCL (Transaction Control Language):
Manages transactions.
42
* CREATE TABLE: Creates a new table.
* ALTER TABLE: Modifies an existing table.
* DROP TABLE: Deletes a table.
* CREATE INDEX: Creates an index for faster querying.
* DROP INDEX: Deletes an index.
DDL Statements
43
Returns rows that have a match in both tables.
INNER JOIN
44
Returns all rows from the left table, along with matching rows from the right table.
LEFT JOIN
45
Returns all rows from the right table, along with matching rows from the left table.
RIGHT JOIN
46
Returns all rows from both tables, whether or not they have a match in the other table.
FULL OUTER JOIN
47
Types of Data Models
Hierarchical Data Model
Network Data Model
Relational Data Model
Entity-Relationship (ER) Model
Object-Oriented Data Model
NoSQL Data Models
48
* Tree-like structure with parent-child relationships.
* Limited flexibility due to rigid hierarchy.
Hierarchical Data Model
49
* Extends hierarchical model with multiple parents per child.
* Complex and challenging to manage.
Network Data Model
50
* Organizes data into tables with rows and columns.
* Most widely used model for structured data.
* Offers simplicity, flexibility, and powerful querying capabilities.
Relational Data Model
51
* Visual representation of entities and their relationships.
* Used for database design and understanding data structures.
Entity-Relationship (ER) Model
52
* Represents data as objects with properties and methods.
* Supports complex data structures and object-oriented programming paradigms
Object-Oriented Data Model
53
* Designed for large-scale, unstructured or semi-structured data.
* Includes various types:
o Key-value stores
o Document databases
o Columnar databases
o Graph databases
* Offers scalability, high performance, and flexibility for specific use cases.
NoSQL Data Models
54
o Composed of rows and columns forming cells.
o Cell addresses are unique (e.g., A1).
55
o Number Formats
General, Number, Currency, Accounting, Date, Time, Percentage, Fraction, Scientific, Text.
56
Adjusts based on cell position.
o Relative
57
Remains constant (uses "$").
o Absolute
58
Combines relative and absolute addressing.
o Mixed
59
Ideal for visualizing mathematical functions and trends.
* Scatter Charts
60
Effective for displaying proportions and percentages.
* Pie Charts
61
* Suitable for plotting functions with x and y values.
* Requires careful data entry and series selection.
* Can plot multiple functions on the same chart.
Scatter Charts:
62
* Represent data as proportions of a whole.
* Customize with data labels, legends, and 3D effects.
Pie Charts:
63
: Powerful tool for data analysis and summarization.
Pivot Tables
64
* Creation:
o Select data in the spreadsheet.
o Go to "Insert" > "PivotTable."
o Choose location and click "OK."
65
* Fields:
o Drag fields into "Row Fields," "Column Fields," "Values," and "Filters" areas.
o "Values" can be summarized (sum, average, count, etc.).
66
* Data Manipulation:
o Filtering: Select specific categories or values.
o Sorting: Arrange data in ascending or descending order.
o Calculations: Perform calculations (sum, average, etc.) within the pivot table.
67
* Data Updates:
o Refresh the pivot table to reflect changes in the source data.
68
* Data Preparation:
o Ensure data is clean and organized (no empty rows/columns, consistent headers).
o Convert text to numbers if necessary.
69
* Calculated Fields:
o Create new fields within the pivot table using formulas.
o Combine existing fields to derive new insights.
o Enhance data analysis by adding custom calculations.
70
* Grouping:
o Group data into ranges (e.g., sales ranges, date ranges).
o Customize group intervals (equal or unequal).
o Combine and ungroup groups as needed.
71
* Data Manipulation:
o Sort, filter, and rearrange data within the pivot table.
o Modify field names and formats.
o Hide and show details for better readability.
72
* Advanced Techniques:
o Use GetTableData function for more flexible formula creation.
o Combine grouping with other pivot table features.
73
is a graphical representation of a database that shows the entities (important objects), their attributes (descriptive details), and the relationships between them. It helps us understand the structure of a database before it is actually built.
An Entity-Relationship Diagram (ERD)
74
* Represent real-world objects or concepts (e.g., Human, Pet, Vet).
* Shown as rectangles in the ERD.
Entities
75
* Provide details about the entities (e.g., Name, Surname, Pesel for
Human).
* Shown inside the entity rectangles.
Attributes
76
* Show how entities are connected (e.g., a Human owns a Pet).
* Represented by lines connecting the entity rectangles.
Relationships
77
* A unique identifier for each entity record (e.g., Pesel for Human).
Primary Key
78
* A reference to a primary key in another entity (e.g., Owner_Pesel
in Pet points to Pesel in Human).
Foreign Key
79
* A process of organizing the database to minimize data redundancy and improve data integrity.
Normalization
80
* A way to create new entities that inherit attributes from existing entities (e.g., Owner and Vet inherit attributes from Person).
Inheritance
81
* An entity used to represent a many-to-many relationship between two entities (e.g., Owns table to connect Humans and Pets).
Associative Entity
82
* When the same data is stored in multiple places in a database, which can lead to inconsistencies.
Data Redundancy
83
Used to retrieve data from one or more columns.
* SELECT
84
select all columns
o SELECT * FROM table
85
select specific columns
o SELECT column1, column2 FROM table
86
Specifies the table from which to retrieve data
* FROM
87
Filters data based on conditions.
* WHERE
88
o WHERE column = value
o WHERE column > value
o WHERE column BETWEEN value1 AND value2
o WHERE column IN ('value1', 'value2')
o WHERE column IS NULL / IS NOT NULL
89
Sorts the results.
* ORDER BY
90
Removes duplicate values.
* DISTINCT
91
o SELECT DISTINCT column FROM table
92
Renames columns.
* AS
93
o SELECT column AS new_column_name FROM table;
94
Concatenates strings.
* ||
95
o SELECT column1 || ' ' || column2 FROM table;
96
Used for pattern matching.
* LIKE
97
contains pattern
o WHERE column LIKE '%pattern%'
98
starts with pattern
o WHERE column LIKE 'pattern'
99
second character is pattern
o WHERE column LIKE '_pattern'
100
Extracts the year from a date.
* EXTRACT(YEAR FROM column):
101
* Use NVL() function to handle NULL values.
102
* SQL commands are case-insensitive (e.g., SELECT and select are the same).
103
Combines data from two or more tables based on a related column.
* JOIN
104
o SELECT * FROM table1 JOIN table2 ON table1.column = table2.column;
105
Adds new rows to a table.
* INSERT
106
o INSERT INTO table (column1, column2) VALUES (value1, value2);
107
Modifies existing data in a table.
* UPDATE
108
o UPDATE table SET column1 = value1, column2 = value2 WHERE condition;
109
Removes rows from a table.
* DELETE
110
o DELETE FROM table WHERE condition;
111
(DDL):
Data Definition Language
112
Creates a new table with specified columns and data types
* CREATE TABLE
113
o CREATE TABLE table_name (column1 data_type, column2 data_type, ...);
114
Modifies the structure of an existing table.
* ALTER TABLE
115
o ALTER TABLE table_name ADD column data_type;
o ALTER TABLE table1 ADD CONSTRAINT fk_name FOREIGN KEY (column1) REFERENCES table2(column2);
116
Deletes an entire table.
* DROP TABLE
117
o DROP TABLE table_name;
118
* DML commands manipulate data within existing tables.
* DDL commands change the structure of the database itself.
* Use DML commands with caution, as they can permanently alter data.
* Always test DDL commands carefully before executing them on a production database.
119
Automatically generates a list of all the headings and subheadings within a document, along with their corresponding page numbers.
Table of Contents
120
Creates a list of all the figures (images, charts, diagrams) used in the document, along with their captions and page numbers.
Table of Figures
121
Creates a formatted list of all the sources cited within the document.
Bibliography
