Database Flashcards

Question

What are the Pros and Cons of Horizontal Scaling?

Answer 1

Pros: Increased fault tolerance: If one server fails, the others can continue to handle requests. Better resource utilization: Can distribute load across multiple servers, maximizing resource usage. Easily accommodates growing demand: Adding more servers can handle increased traffic or workload. Cost-effective for certain workloads: Can use commodity hardware and add more nodes as needed. Cons: Complexity in managing distributed systems: Requires coordination and synchronization among multiple servers. Potential for network bottlenecks: Increased communication overhead between nodes. Data consistency challenges: Synchronizing data across multiple nodes can be complex. Limited scalability in some cases: Certain applications may not be easily parallelizable.

Answer 2

Web Servers: Adding more web server instances to handle increased web traffic. For example, a website experiencing higher visitor numbers can deploy additional instances of its web server to distribute the load. Database Sharding: Splitting a database into smaller, more manageable parts and distributing these parts across multiple servers. Each shard handles a subset of the data, allowing for better performance and scalability. Content Delivery Networks (CDNs): Distributing content across multiple servers located in different geographical regions. This allows for faster delivery of content to users by serving data from the server closest to them.

Answer 3

Vertical scaling involves increasing the resources of individual machines to handle increased load. This typically involves upgrading hardware components such as CPU, memory, or storage to improve the performance and capacity of a single machine. | (Add More Power to current computers)

Answer 4

Pros: Simplicity: Adding resources to a single server is straightforward. Performance benefits: Can leverage more powerful hardware for increased performance. Easier to maintain: Fewer systems to manage and monitor. Lower networking overhead: Communication within a single server is faster. Cons: Limited scalability: Eventually, the capacity of a single server may become a bottleneck. Higher cost: Upgrading hardware can be expensive, especially for high-end servers. Increased risk of downtime: Upgrading hardware may require system downtime. Single point of failure: If the server fails, all services hosted on it are affected.

Answer 5

Enterprise Resource Planning (ERP) Systems: ERP systems often require significant processing power and memory to handle complex business operations such as inventory management, order processing, and financial transactions. Vertical scaling by upgrading the server hardware allows these systems to accommodate growing data volumes and user loads. Database Servers: In scenarios where a single, centralized database is critical for an organization's operations, vertical scaling can be used to increase the capacity and performance of the database server. This ensures that the database can handle growing data volumes and concurrent user requests without compromising performance. High-Performance Computing (HPC) Workloads: Applications such as scientific simulations, financial modeling, and weather forecasting require massive computational power to process large datasets and perform complex calculations. Vertical scaling by upgrading CPU, GPU, and memory resources allows these applications to execute computations faster and more efficiently.

Answer 6

Combines rows from two or more tables based on a related column. Example Foreign key

Answer 7

INNER JOIN LEFT JOIN RIGHT JOIN FULL OUTER JOIN

Answer 8

Returns only the rows that have matching values based on the specified join condition

Answer 9

SELECT columns FROM table1 INNER JOIN table2 ON table1.table1PK = table2.table1PK Example SELECT orders.order_id, customers.customer_name FROM orders INNER JOIN customers ON orders.customer_id customers.customer_id;] Result: Retrieves order IDs and customer names where there is a matching customer ID in both orders and customers tables.

Answer 10

Returns all rows from the left table (table1) and but only the matched rows from the right table (table2). If there is no match, NULL values are returned from the right table.

Answer 11

SELECT columns FROM table1 LEFT JOIN table2 ON table1.table1PK = table2.table1PK

Answer 12

Returns all rows when there is a match in either table. If there is no match, NULL values are returned for the missing side. (Retrieves all customer names and order IDs. If a customer has no orders or an order has no corresponding customer, NULL values are returned accordingly.) | (Puts everything and puits NULL if theres no value.)

Answer 13

SELECT columns FROM table1 FULL OUTER JOIN table2 ON table1.column = table2.colum (Retrieves all customer names and order IDs. If a customer has no orders or an order has no corresponding customer, NULL values are returned accordingly.)

Answer 14

GROUP BY ORDER BY WHERE LIKE

Answer 15

Groups rows that have the same values into summary rows.

Answer 16

SELECT column1, COUNT(column2) FROM table_name GROUP BY column1

Answer 17

Sorts the result set in ascending or descending order

Answer 18

SELECT column1, column2 FROM table_name ORDER BY column1 ASC/DESC

Answer 19

Filters records based on specified conditions | if statement

Answer 20

SELECT * FROM table_name WHERE condition | returns what meets the condition

Answer 21

Used in a WHERE clause to search for a specified pattern in a column

Answer 22

SELECT * FROM table_name WHERE column1 LIKE 'pattern'; | % and _ a% (must contain "a") f_re (looks for sum tht in the blank) fire

Answer 23

A Database Architecture represents the Database Management System’s (DBMS) design (schema). The DBMS architecture makes it easy to understand the components involved in the database system and their relations. The DBMS architecture may vary based on the system on which it runs. Some systems contain both client and server machines whereas some run only on a server machine. Based on the architecture, the system can be classified as centralized, decentralized, or hierarchical.

Answer 24

Presentation Layer Application Layer Data Layer

Answer 25

The presentation layer is often referred to as the client and is the topmost layer in an application. The end-user has access to the application layer. This layer passes all the information that is given by the end-user. In simple terms, it is the application that we use. For instance, the login page of Facebook. | Client side

Answer 26

AKA (Business layer) The application layer is also known as the business layer. It interacts with the end-user and sends the necessary details to the presentation layer. This layer acts as a mediator between the presentation and database layers. For instance, when the login button is clicked on the Facebook registration page, it passes the details to the presentation layer. | Server Side

Answer 27

The data layer is the server. It communicates with the database to share and retrieve data. This layer contains the functions that connect with the database and performs actions such as insert, update, delete, search, and so on. | Database/ Server

Answer 28

The database can be stored at one location (centralized) or multiple locations (decentralized).

Answer 29

One-Tier Architecture Two-Tier Architecture Three-Tier Architecture

Answer 30

AKA Local database system One -Tier (Single-Tier) Architecture is the simplest DBMS architecture. In this type of architecture, the client, server, and database are available on the same machine. No internet connection is required to perform any action on the database. All the changes are reflected in the database directly. Additionally, single-tier architecture is also referred to as a local database system. | Client, server, and database are available on the same machine

Answer 31

A Two-Tier Architecture operates as a two-way system. The database system is stored on the server in this client-server architecture, and the DBMS application is on the client-side. Both of these machines are connected through a network. However, the client and server machines cannot communicate directly. To access the database on the server, the client has to make a request using a query language, that is, SQL. When the server receives the query, it acts and returns the result to the client. The communication between the application program and the database server is achieved using APIs such as ODBC, JDBC. | Client-Server (Database)

Answer 32

A Three-Tier Architecture is the widest used architecture. In this type of Architecture, an additional layer, known as the Application Layer, sits between the client (presentation layer) and the server (database) machine. This application layer offers a Graphical User Interface (GUI), making it easy for the end-user to interact with the database. In brief, the application layer handles the communication between the client and server. | Client-Application-Server (Database)

Answer 33

CRUD stands for Create, Read, Update, and Delete. These are the basic operations performed on data in a database.

Answer 34

Create (INSERT): Used to add new records (rows) to a table.

Answer 35

INSERT INTO table_name (column1, column2, ...) VALUES (value1, value2, ...);

Answer 36

Read (SELECT): Used to retrieve data from a database.

Answer 37

SELECT column1, column2, FROM table_name WHERE condition | * For every column

Answer 38

Update (UPDATE): Used to modify existing records (rows) in a table.

Answer 39

UPDATE table_name SET column1 = value1, column2 = value2 WHERE condition;

Answer 40

Delete (DELETE): Used to remove records (rows) from a table.

Answer 41

DELETE FROM table_name WHERE condition;

Answer 42

Visual representations of the relationships between entities in a database.

Answer 43

**Entities (Rectangles): ** Represented by rectangles, such as "Customer" or "Product." **Attributes (Ovals / Ellipses): ** Properties of entities, shown within ovals. **Relationships: ** Connections between entities, indicated by lines. **Cardinality Notations:** Describes the numerical relationship between associated entities

Answer 44

Imagine you have a list of your favorite books, and you keep this list in two places: on your phone and on your computer. If you update the list on your phone but forget to update it on your computer, you now have two slightly different versions of the same information. This duplication is what we call data redundancy - having the same data stored in multiple places. It can lead to confusion and inconsistency if not managed properly.

Answer 45

No normalization Manual Data Entry: Human error during data entry can result in duplicate or redundant records. Data Replication: Replicating data across multiple systems for backup or performance reasons can inadvertently lead to redundancy if not synchronized properly. Multiple data entry points: When data can be entered or updated from various sources or interfaces, it increases the chances of redundancy. No updates: Inconsistent data updating or lack of data validation can result in duplicate entries. Old Data: Keeping outdated or obsolete data without proper archival can contribute to redundancy.

Answer 46

Increased storage requirements: Storing duplicate data consumes additional disk space. and cost. Data inconsistency: When redundant data is updated in one location but not in others, it can lead to inconsistencies and errors. Decreased data integrity: Redundant data increases the risk of data anomalies such as insertion, deletion, and update anomalies. Difficulty in data maintenance: Managing redundant data complicates data maintenance tasks such as updates and backups.

Database Flashcards

(70 cards)