A surrogate key in a database is a unique identifier for each record in a table,

typically generated automatically by the system. It does not have any

business meaning or real-world association; it is purely used for database
identification purposes. Surrogate keys are commonly used as primary keys
and are usually integers or GUIDs (Globally Unique Identifiers).

Advantages of surrogate keys:

They are guaranteed to be unique.

They are simple and efficient to use, especially when auto-incremented.

They are independent of the data, making it easier to change business logic
without affecting relationships.

This is in contrast to natural keys, which are derived from the actual data
(like Social Security Numbers or email addresses).

A composite key in a database is a type of key that consists of two or more

columns (attributes) combined to uniquely identify a record in a table. In
other words, a composite key is used when no single column is unique
enough to serve as the primary key, but a combination of columns can
ensure uniqueness.

Example:

Consider a table OrderDetails with the following columns:

OrderID
ProductID

Quantity

In this case, neither OrderID nor ProductID alone can uniquely identify each
record, but the combination of OrderID and ProductID can, making them a
composite key.

Key points:

A composite key enforces uniqueness across the combination of multiple

fields.

It is used when a single column cannot uniquely identify records.

A foreign key in a database is a column or a set of columns that establishes a

relationship between two tables. It acts as a reference to the primary key in
another table, ensuring referential integrity between the two.

Key Points:

A foreign key in one table refers to the primary key or a unique key in
another table.

It enforces relationships between tables, ensuring that the data in the foreign
key column(s) corresponds to valid entries in the referenced table.

The foreign key constraint prevents actions that would destroy links between
tables, such as deleting a record that another table depends on, unless
cascading actions are defined.
Example:

Consider two tables: Orders and Customers.

Customers table has a primary key: CustomerID

Orders table has a foreign key: CustomerID, referencing the CustomerID in

the Customers table.

This ensures that each order is linked to a valid customer.

A single-user database is a database system designed to be used by only

one user at a time. It is typically installed on a personal computer or a local
system, and its operations and access are limited to a single user session.
These databases are generally simpler in terms of design, functionality, and
performance compared to multi-user databases, as they do not need to
handle concurrency, complex transaction management, or access control for
multiple users.

Characteristics:

No concurrency management: Since only one user accesses the database at

a time, there is no need to manage simultaneous read or write operations.

Simple transaction handling: As there are no concurrent users, transaction

management is simpler and does not require conflict resolution.
Use cases: Single-user databases are often used for personal projects, small-
scale applications, or in environments where multi-user access isn’t needed,
such as desktop applications or local data storage.

Examples:

Microsoft Access (in a single-user mode)

SQLite

These databases are ideal for small or medium-sized applications that do not
require the complexity of a multi-user database system.

A multi-user database is a database system that allows multiple users to

access and interact with the database simultaneously. These systems are
designed to support concurrent access, meaning several users can perform
operations such as reading, writing, and updating data at the same time
without causing conflicts or data corruption.

Key Features of a Multi-User Database:

1. Concurrency Control: Mechanisms like locking, timestamp ordering, or

optimistic concurrency control are used to manage simultaneous
access and ensure data consistency.
 2. Transaction Management: Multi-user databases support complex
transaction handling, ensuring that transactions are executed
atomically (all or nothing) and maintain database integrity.

3. Data Integrity: By enforcing constraints and managing concurrency,

multi-user databases ensure that multiple users’ actions do not result
in inconsistent or corrupt data.

4. Security and Access Control: Permissions and roles are used to control
what each user can do in the database, ensuring that sensitive data is
protected and only authorized users can make certain changes.

5. Backup and Recovery: Multi-user systems usually have robust

mechanisms for data backup and recovery to protect against failures or
accidental data loss.

Use Cases:

Enterprise-level applications like ERP (Enterprise Resource Planning) or CRM

(Customer Relationship Management) systems.

Web applications that serve many users, like social media platforms or e-
commerce websites.
Shared databases in organizations where multiple employees need access to
shared data (e.g., hospital systems, financial institutions).

Examples:

MySQL, PostgreSQL, and Oracle databases, which are designed to handle

hundreds or even thousands of simultaneous users.

Microsoft SQL Server and IBM Db2 also fall into this category for managing
enterprise-level multi-user access.

Multi-user databases are essential for applications that need to support

large-scale, real-time interactions and ensure data accuracy despite
simultaneous access.

An e-commerce database is a specialized database designed to support the

operations of an online store or marketplace. It stores and manages all the
critical information needed for running an e-commerce platform, such as
product details, customer information, orders, transactions, inventory, and
more. The database is optimized for fast queries, efficient storage, and
scalability to handle large volumes of transactions, users, and products.

Key Components of an E-commerce Database:

1. Product Catalog: Contains details about the products being sold,

including:

Product name
Description

Price

Stock quantity

Category (e.g., electronics, fashion)

Product images or multimedia

2. Customer Information: Stores customer details such as:

User profile (name, email, password)

Shipping and billing addresses

Payment methods (credit card, PayPal, etc.)

Purchase history and preferences

3. Orders and Transactions: Records customer orders and payment

details:
Order ID

Product(s) ordered

Order status (pending, shipped, delivered)

Payment details (amount, method, status)

Shipping information (carrier, tracking number)

4. Inventory Management: Tracks the stock levels of products to prevent

overselling:

Stock availability

Restock levels

Supplier information

5. Shopping Cart: Stores items that users have added to their cart for
future purchase:
Cart ID

Product ID(s)

Quantity of items

Price at the time of addition

6. Reviews and Ratings: Manages user feedback and product reviews:

Customer reviews (text, ratings)

Product-specific ratings

Review timestamps

7. Session and Activity Data: Tracks user behavior on the site for analytics
and personalization:

Login sessions

Browsing history
Search queries

Wishlists

Database Design Considerations for E-commerce:

Scalability: The database should handle a growing number of users, products,

and transactions.

Performance: Queries should be optimized for fast data retrieval, especially

for product searches, filtering, and checkout.

Security: Sensitive information like customer data and payment details must
be secured using encryption and access controls.

Reliability: The database should be robust, ensuring data integrity, backup,

and recovery in case of failures.

Support for Transactions: E-commerce platforms require atomicity,

consistency, isolation, and durability (ACID) compliance to ensure that orders
and payments are processed correctly.

Popular Databases for E-commerce:

Relational databases like MySQL, PostgreSQL, and Microsoft SQL Server are
common for handling structured data and supporting complex queries.

NoSQL databases like MongoDB or Cassandra are used when there is a need
for high scalability, flexibility, and handling unstructured data such as
product reviews or user interactions.

In a large e-commerce platform, these components work together to provide

a seamless shopping experience for users while maintaining data accuracy
and performance at scale.

A query in a database is a request for data or information from one or more

tables. It allows users to retrieve, insert, update, or delete data in a
database. Queries are typically written in SQL (Structured Query Language)
for relational databases, but other languages may be used for NoSQL
databases (e.g., MongoDB uses a different syntax).

Types of Queries:

1. SELECT Query: Used to retrieve data from one or more tables.

Example: SELECT * FROM Customers WHERE Country = ‘USA’;

This retrieves all customers from the USA.

2. INSERT Query: Adds new data to a table.

Example: INSERT INTO Orders (OrderID, CustomerID, OrderDate) VALUES
(101, 1, ‘2023-10-12’);

3. UPDATE Query: Modifies existing data in a table.

Example: UPDATE Products SET Price = 19.99 WHERE ProductID = 10;

4. DELETE Query: Removes data from a table.

Example: DELETE FROM Orders WHERE OrderID = 101;

Query Characteristics:

Filtering: Queries can filter data using conditions (e.g., WHERE clause) to
retrieve specific records.

Joining Tables: Queries can combine data from multiple tables using JOIN
operations.

Example: SELECT [Link], [Link] FROM Orders

JOIN Customers ON [Link] = [Link];
Aggregation: Queries can group and summarize data using functions like
COUNT, SUM, AVG.

Example: SELECT COUNT(*) FROM Orders WHERE Status = ‘Shipped’;

Sorting: Results can be sorted using the ORDER BY clause.

Example: SELECT * FROM Products ORDER BY Price DESC;

Common Query Languages:

SQL: Used in relational databases like MySQL, PostgreSQL, Oracle, etc.

MongoDB Query Language: For NoSQL databases like MongoDB.

Queries allow users and applications to interact with a database to manage

and retrieve meaningful information.

A transaction in a database is a sequence of one or more operations (such as

reading, writing, or updating data) that are treated as a single, indivisible
unit of work. Transactions are essential for ensuring the integrity,
consistency, and reliability of the database, especially in multi-user
environments.
Key Properties of a Transaction: ACID

Transactions follow the ACID properties to maintain data integrity.

1. Atomicity: Ensures that all operations within the transaction are

completed successfully, or none of them are. If any part of the
transaction fails, the entire transaction is rolled back, and the database
is left unchanged.

Example: If you transfer money between two accounts, either both the debit
and credit occur, or neither does.

2. Consistency: Ensures that a transaction takes the database from one

valid state to another valid state, maintaining all defined rules (e.g.,
constraints, triggers). It prevents data corruption.

Example: If a transaction violates a foreign key constraint, it will fail,

ensuring data consistency.

3. Isolation: Ensures that the operations of one transaction are invisible to

other transactions until the transaction is completed. This prevents
“dirty reads” and other concurrency issues.

Example: While one transaction updates an account balance, another cannot

access the intermediate, incomplete state of that update.
 4. Durability: Ensures that once a transaction is committed, its changes
are permanent, even in the event of a system crash or failure. The
changes made by the transaction are written to permanent storage.

Example: After transferring money, even if the server crashes, the

transaction is saved and will not be undone.

Example of a Transaction:

BEGIN TRANSACTION;

UPDATE Accounts SET Balance = Balance – 100 WHERE AccountID = 1; --

Debit account

UPDATE Accounts SET Balance = Balance + 100 WHERE AccountID = 2; --

Credit account

COMMIT; -- Save the transaction

If both updates are successful, the transaction is committed. If there is an

error (e.g., insufficient funds), the transaction can be rolled back to ensure
that no changes are made.

Transaction States:
 1. Active: The transaction is being executed.

2. Partially Committed: All operations have been executed, but the

transaction is not yet committed.

3. Committed: The transaction has successfully completed and is saved

to the database.

4. Failed: An error occurred during the transaction, and it needs to be

rolled back.

5. Aborted (Rolled Back): The transaction failed and all changes were
undone.

Importance of Transactions:

Data Integrity: Ensures that database operations are consistent and valid.

Concurrency Control: Prevents conflicts when multiple users access the

database simultaneously.
Error Handling: Transactions allow for easy rollback in case of failure,
avoiding incomplete or corrupt data.

Transactions are fundamental for database operations, especially in systems

that require high reliability and data integrity, such as banking or inventory
management systems.

In a database, a schema is the structure that defines the organization of

data. It represents the blueprint or architecture of the database, outlining
how the data is stored and how the relationships between different tables or
entities are defined.

A schema typically includes:

1. Tables: The individual sets of data (like spreadsheets) that store

specific pieces of information.

2. Columns/Fields: The attributes or properties in each table that describe

the data (like headers in a spreadsheet).

3. Data Types: The type of data that each column holds (e.g., integer,
string, date).

4. Constraints: Rules for the data, such as ensuring values are unique, not
null, or within a specific range.
 5. Indexes: Structures to speed up data retrieval.

6. Relationships: How tables relate to each other, typically via primary

keys and foreign keys.

In essence, a schema defines the logical view of the entire database,

ensuring that the data is structured and organized consistently.

An index in a database is a data structure that improves the speed of data

retrieval operations on a database table. It works similarly to an index in a
book, which allows you to quickly locate information without having to read
the entire book.

### Key Points about Database Indexes:

1. **Purpose**: The primary purpose of an index is to enhance the

performance of queries by allowing the database management system
to find rows more efficiently.

2. **Structure**: An index typically consists of a sorted list of the values

in one or more columns of a table, along with pointers (or references)
to the corresponding rows in the table.

3. **Types of Indexes**:

- **Single-column Index**: An index on a single column.

- **Composite Index**: An index on multiple columns.

 - **Unique Index**: Ensures that all values in the indexed column(s) are
unique.

- **Full-Text Index**: Optimizes searches for full-text searches in large text

fields.

- **Clustered vs. Non-Clustered**: A clustered index determines the

physical order of data in a table, while a non-clustered index is a separate
structure that points to the data.

3. **Trade-offs**: While indexes can greatly speed up data retrieval, they

can also slow down data modification operations (like INSERT, UPDATE,
DELETE) because the index must be updated whenever the data
changes. Additionally, indexes take up extra space on disk.

4. **Creation**: Indexes are created using SQL commands, such as

`CREATE INDEX`, and can be tailored based on specific query patterns
and performance needs.

Indexes are a fundamental aspect of database optimization and are crucial

for maintaining efficient data access in large datasets.

Referential integrity constraints in a database are rules that ensure the

consistency and validity of data between related tables. They maintain the
relationships among tables by ensuring that foreign key values always point
to valid primary key values in another table.

### Key Points about Referential Integrity Constraints:

1. **Foreign Key**: A foreign key is a column (or a set of columns) in one

table that uniquely identifies a row of another table, establishing a link
between the two tables. The table with the foreign key is called the
 child table, and the table with the primary key is called the parent
table.

2. **Constraints**: Referential integrity constraints ensure that:

- A foreign key value must either be entirely null or match a primary key
value in the parent table.

- This prevents orphaned records in the child table (i.e., records that
reference non-existent records in the parent table).

3. **Cascading Actions**: Referential integrity can also be enforced with

cascading actions:

- **ON DELETE CASCADE**: If a record in the parent table is deleted, all

corresponding records in the child table are also deleted.

- **ON UPDATE CASCADE**: If a primary key value in the parent table is

updated, the corresponding foreign key values in the child table are
automatically updated.

5. **Importance**: Referential integrity is crucial for maintaining data

accuracy and consistency. It helps prevent data anomalies that can
occur from improper updates or deletions.

6. **Implementation**: Referential integrity is typically defined at the

time of table creation using SQL commands like `CREATE TABLE` or
modified later with commands like `ALTER TABLE`.

### Example:

In a database with two tables, `Customers` and `Orders`:

- The `Customers` table has a primary key `CustomerID`.

- The `Orders` table has a foreign key `CustomerID` that references the
`CustomerID` in the `Customers` table.

With a referential integrity constraint in place:

- Every order must refer to an existing customer. If a customer is deleted

from the `Customers` table, the system can automatically delete their
orders (if using ON DELETE CASCADE) or prevent the deletion until all
related orders are handled.

Overall, referential integrity constraints are essential for ensuring the

integrity of relationships between tables in a relational database.