Database Theory and Design Flashcards

Question

How would you improve data integrity in a database?

Answer 1

Organizational data is stored in one place, it's easy to enforce consistency rules that shouldn't be violated

Answer 2

Database administrator can easily put in place security and access measures using the DBMS

Answer 3

Since all data is in the same database, its easy to enforce data standards such as: - Data formats - Naming conventions - Documentation standards - Update procedures - Access rules

Answer 4

Combining an entire organization's data into one database and creating a set of applications that work on this one source results in saving costs

Answer 5

Since the DBA has an organizational-wide view of all operations, they can optimize the design and operational use of the database to provide the best use of resources as a whole as opposed to satisfying one department at the expense of another

Answer 6

Common database provides easy data access to all authorized people within orgranization Also, the DBMS provides SQL to enable end-users to easily construct ad hoc queries without detailed programming knowledge

Answer 7

Can be improved by using a DBMS, which handles low-level file operations and provides SQL for user-friendly query development, reducing the need for extensive programming.

Answer 8

A DBMS separates data management from application programs - so if changes are made to underyling data structure, the DBMS doesnt have to change application programs. The DBMS will provide the necessary mapping between application programa and data stored on the database

Answer 9

Many DBMSs allow users to access the database simultanoeusly without any conflict

Answer 10

DBMS have facilities to enable data recovery following a failure They can also automatically backup the data stored in the database

Answer 11

- Complex: require those using them to fully understand them - Size: Large piece of software that requires alot of memory to run - Cost of DBMS: Cost varies depending on if its single user or a big organization. Maintenance and operations costs also need to be factored. - Cost of conversion: Data conversion costs as well as training and recruiting costs - Performance: Written for general use, so some applications aren't as fast - Greater impact of failure: Centralization increases system vulnerability. Failure may result in everyone being affected in an organization

Answer 12

External Level - The way users percieve data Conceptual Level - Provide both the mapping and desired independence Internal Level - The way DBMS and the OS perceive the data

Answer 13

- Data Abstraction - Data Independence

Answer 14

Hide storage details and present the users with a conceptual view of the database

Answer 15

ability to change the database's internal structure (the lower levels) without impacting the higher levels, such as the applications or user interfaces that interact with the data.

Answer 16

The ability to change the structure of a database's logical schema (the way data is organized and represented to users) without affecting the applications or queries that use that data.

Answer 17

Changing the way data is stored in a database without affecting how the data is stored or viewed by users or applications.

Answer 18

The description of the structure of the database

Answer 19

The content of a DB at a moment in time

Answer 20

How data is stored, related, and accessed A set of concepts to describe - Data and relationships among data - Data constraints

Answer 21

Conceptual data model Logical data model Physical data model

Answer 22

Identifies the high-level data structure (Independent of DBMS, application programs, physical considerations)

Answer 23

Describes the data in terms of data structures Independent of a particular DBMS product and storage technology

Answer 24

Hierarchical Network Relational

Answer 25

Data is organized in a tree-like structure Each node has 1 parent

Answer 26

Data is organized as a graph One node can have more than one parent node

Answer 27

Data is stored in tables. Each table, called a relation, consists of rows and columns, much like a spreadsheet

Answer 28

Describe how data is stored in the computer, representing record structures, record openings, and access paths Highly dependent on target DBMS

Answer 29

One of the most difficult aspect of database design is that designers, programmers and end-users tend to view data in different ways Need a model for communication that is non-technical and free of ambiguities

Answer 30

1. Chen Notation 2. Crow's feet Notation 3. UML Notation

Answer 31

- Entity - Relationship - Attribute - Constraint

Answer 32

A group of objects with the same properties

Answer 33

Meaningful associations among two or more entities

Answer 34

The number of participating entity types in a relationship

Answer 35

- Degree one: recursive - Degree two: binary - Degree three: ternary - Degree four: quaternary

Answer 36

Two entities are associated through more than one relationship

Answer 37

A property of an entity or a relationship

Answer 38

The minimal number of attribute(s) whose value(s) uniquely identify each entity occurrence - A candidate key cannot contain a null

Answer 39

A unique identifier for each record in a database table

Answer 40

- Attribute length - Minimal number of attributes required - Future certainty of uniqueness

Answer 41

Existence-dependent on some other entity - Each entity occurrence cannot be uniquely identified using only the attributes assoicated with that entity type

Answer 42

The number of possible occurrences of an entity type that may relate to a single occurrence of an associated entity type through a particular relationship

Answer 43

1. One-to-one (1:1) 2. One-to-many(1 : *) 3. Many-to-many (* : *)

Answer 44

Each record in one table is linked to exactly one record in another table.

Answer 45

A record in one table can be linked to multiple records in another table.

Answer 46

Multiple records in one table can be linked to multiple records in another table.

Answer 47

It's a way of showing the "how many" in a relationship between entities

Answer 48

The maximum values for the multiplicity ranges on either side of the relationship

Answer 49

The minimum values for the multiplicity ranges on either side of the relationship

Answer 50

Optional (if 0) Mandatory (if 1 or more)

Answer 51

When two one-to-many relationships connect in a way that causes incorrect data interpretation.

Answer 52

The process of maximizing the differences between members of an entity by identifying their distinguishing characteristics

Answer 53

The process of minimizing the differences between entities by identifying their common characteristics

Answer 54

- Optional - Mandatory

Answer 55

- And (nondisjoint) - Or (disjoint)

Answer 56

An abstraction through which relationships are treated as higher-level entities

Answer 57

A two-dimensional table that has specific characteristics

Answer 58

Attribute - A column that defines a specific piece of information about each record (row). Each attribute represents a data field Tuple - A row that represents a single record or entry. Each tuple contains specific data for all the attributes (columns) in the table. Degree - number of attributes (columns) it has Cardinality - number of tuples (rows) it contains

Answer 59

The minimal set of attributes whose values uniquely identify each tuple

Answer 60

An attribute, or set of attributes, within one relation that matches the primary key of another relation

Answer 61

A relation has a name that is distinct from all other relation names in the database Each cell of relation contains exactly one atomic (single) value Each attribute has a distinct name The values of an attribute are all from the same domain Each tuple is distinct - there are no duplicate tuples Order of attributes and tuples has no significance

Answer 62

A named relation defined by the relation name (table name) and attributes

Answer 63

A set of tuples from a relation schema

Answer 64

Uniqueness : No two tuples can have identical values for candidate keys Not-Null: No attribute of a candidate key can be a NULL

Answer 65

If a foreign key exists in a relation - Either the foreign key value matches a primary key value in its home relation - Or the foreign key is NULL

Answer 66

Set of allowable values for one or more attributes

Answer 67

Enables RDBMS to carry out checks on data errors and to determine the range of operations that can be carried out on the domain

Answer 68

A subset of base relations

Answer 69

A base relation is an actual table in the database, not a view or derived table Represents a real-world entity or concept

Answer 70

Virtual/derived relation. It does not necessarily exist in the database, but can be produced upon request. Dynamic. The changes made to the base relation(s) that affect the view are immediately reflected in the view. Generated by applying appropriate relational operations (e.g., 𝜎, 𝜋, x, ⋈, ….)

Answer 71

A schema that represents a strong entity in a database. A strong entity is an entity that can be uniquely identified by its own attributes, without needing any reference to another entity. - For composite attributes, include only the constituent simple attributes

Answer 72

One record in a table is related to many records in another table - The many side (child entity) receive the foreign key

Answer 73

Combine the two entities to form one relation The primary key of any one of the two relations is chosen to be the priamry key, whilst the other becomes the alternate key

Answer 74

The entity with mandatory participation receives the foreign key

Answer 75

Foreign key in either side is fine

Answer 76

Create a new relation as an associative entity Primary key: both of these primary keys of its associate entities, may add other attributes

Answer 77

Same rules as binary relationships - (1:1), (1:*) recursive relationships are implemented by foreign keys - (*:*) recursive relationships are implemented by creating a new relation

Answer 78

involves more than two entities participating in a relationship

Answer 79

1. Only keep the subclasses 2. Only keep the superclass 3. Keep both

Answer 80

In a weak entity relationship, a 1:1 relationship means that each instance of the weak entity is associated with exactly one instance of the strong entity and vice versa. Key Points: Weak Entity: Cannot exist without a strong entity; it relies on it for its identification. Strong Entity: Has its own primary key.

Answer 81

In a weak entity relationship, a 1:* relationship means that each instance of the weak entity is associated with one instance of the strong entity, but one instance of the strong entity can be associated with multiple instances of the weak entity. Key Points: Weak Entity: Cannot exist independently; relies on the strong entity for identification. Strong Entity: Has its own primary key.

Answer 82

An attribute that can have more than one value. For example, a person can have multiple phone numbers.

Answer 83

A technique for producing a set of relations with desired properties - Remove redundancy - Remove potential for insertion, modification, deletion anomalies

Answer 84

Insertion of data without necessary conditions having been met. Example: Storing a persons credit card in a transaction log without the transaction having ever occured

Answer 85

Occurs in databases when data is not properly organized, leading to issues with updating, inserting, or deleting records

Answer 86

Deleting data that should be kept

Answer 87

If A and B are attributes of relation R, if each value of A is assoicated with exactly one value of B, B is said to be functionally dependent (FD) on A. - Denoted: A → B - A is the determinant (Note: A or B is not limited to a single attribute, could be a set of attributes)

Answer 88

If A → B and B → C is transitive dependent on A via B: A → C

Answer 89

Given A → B, if removing some attribute(s) from A, the dependency still holds

Answer 90

If A → B and B is not functionally dependent on any subset of A

Answer 91

Remove repeating groups

Answer 92

Remove partial dependencies

Answer 93

Remove transitive dependencies

Answer 94

Every determinant is a candidate key (Boyce-Codd Normal Form (BNCF) is also known has 3.5 Normal Form)

Answer 95

In its most general form, algebra is the study of mathematical symbols and the rules of manipulating these symbols

Answer 96

An algebra whose operands are relations Relational algebra provides a mean to query the data in a database and to modify the data - One of the query language - SQL incorporates relational algebra

Answer 97

Unary Operations - Projection (𝜋) - Selection (𝜎) - Rename (𝜌) Set Operations - Union (∪) - Intersection (∩) - Set difference (−) - Cartesian Product (X) Binary Operations - Join (⋈) - Outer Join (⋉, ⋊) - Division (÷) Aggregate and Grouping operations (ℑ)

Answer 98

- 𝝅<𝒂𝒕𝒕𝒓𝒊𝒃𝒖𝒕𝒆 𝒍𝒊𝒔𝒕>(𝑹): Produce a new relation that has only some of 𝑅‘s columns - Eliminate duplicate tuples, if any.

Answer 99

- 𝝈<𝒔𝒆𝒍𝒆𝒄𝒕 𝒄𝒐𝒏𝒅𝒊𝒕𝒊𝒐𝒏>(𝑹): Produce a new relation that contains only those tuples of R that satisfy the condition. - The relation has the same schema as the original schema.

Answer 100

- 𝝆𝑺 𝑬 : Rename the expression E to S - 𝝆𝑺(𝒂𝟏,𝒂𝟐,…,𝒂𝒏) 𝑬 : Rename the relation E to S, and rename the attributes as 𝑎1, 𝑎2, … , 𝑎𝑛.

Answer 101

A relation that contains tuples from both sets with no duplicates

Answer 102

A relation that contains tuples that are only from both sets.

Answer 103

A relation that contains tuples from one set but not the other

Answer 104

A relation that is the concatenation of every tuple of Relation X with every tuple of relation Y

Answer 105

Performs selection over the Cartesian product of two relations

Answer 106

1. Theta join (Θ-join) 2. Equijion 3. Natural join 4. Outer join

Answer 107

- 𝑹 ⋈<𝒋𝒐𝒊𝒏 𝒄𝒐𝒏𝒅𝒊𝒕𝒊𝒐𝒏> 𝑺: Defines a relation that contains all combinations of tuples from 𝑅 and 𝑆 that satisfy the join condition - 𝑅 ⋈<𝑗𝑜𝑖𝑛 𝑐𝑜𝑛𝑑𝑖𝑡𝑖𝑜𝑛> 𝑆 = 𝜎<𝑗𝑜𝑖𝑛 𝑐𝑜𝑛𝑑𝑖𝑡𝑖𝑜𝑛>(𝑅 × 𝑆)

Answer 108

Produces all the combinations of tuples from that satisfy a join condition with only equality comparisons.

Answer 109

A way to combine two tables in a database based on common columns 𝑹 ⋈ 𝑺: an equijoin of the two relations R and S over all common attributes.

Answer 110

A Declarative Language: Focuses on the what, not how Also a free-format, case insensitive language

Answer 111

Permissions and access control

Answer 112

Managing Transactions (commit, rollback)

Answer 113

1. Comparison: =, <> (!=), <, <=, >=, >, OR, AND, NOT 2. Range: BETWEEN ... AND, NOT BETWEEN ... AND 3. Set membership: IN, NOT IN 4. Pattern match: LIKE, NOT LIKE 5. IS NULL, IS NOT NULL

Answer 114

Perfom calculations on multiple rows 1. COUNT - counts rows 2. MIN - finds min 3. MAX - finds max 4. SUM - adds up values 5. AVG - finds average

Answer 115

1. DISTINCT: Eliminates duplicate rows. 2. WHERE: Filters rows based on conditions. 3. GROUP BY: Groups rows sharing a common value. 4. HAVING: Filters grouped rows (like WHERE but for aggregated data). 5. ORDER BY: Specifies sorting of results.

Answer 116

Groups customers by a condition For example: Groups customers by country and counts the number in each SELECT COUNT(CustomerID), Country FROM Customers GROUP BY Country;

Answer 117

Used to filter data Filters groups where count > 5: SELECT COUNT(CustomerID), Country FROM Customers GROUP BY Country HAVING COUNT(CustomerID) > 5;

Answer 118

A query inside another query For example: SELECT CustomerName FROM Customers WHERE Country IN ( SELECT Country FROM Orders WHERE TotalSales > 5000 );

Answer 119

- INNER JOIN: Retrieves matching rows between two tables. - LEFT JOIN: Retrieves all rows from the left table and matching rows from the right table. - RIGHT JOIN: Retrieves all rows from the right table and matching rows from the left table. - SELF JOIN: Joins a table to itself.

Answer 120

Adds new rows to a table Example: INSERT INTO Customers (CustomerName, Country) VALUES ('John Doe', 'USA');

Answer 121

Modifies existing data in a table Example: UPDATE Customers SET City = 'Frankfurt' WHERE CustomerID = 1;

Answer 122

Removes rows from a table Example: DELETE FROM Orders WHERE OrderDate < CURRENT_DATE - 30;

Answer 123

Temporary names for columns or tables Example: SELECT o.OrderID AS "Order ID", c.CustomerName AS "Customer" FROM Orders o JOIN Customers c ON o.CustomerID = c.CustomerID;

Answer 124

Numeric Types: - INT(n), TINYINT(n), DECIMAL(p,s), FLOAT, DOUBLE, etc. String Types: - CHAR(n), VARCHAR(n), TEXT, etc. Date/Time Types: - DATE: YYYY-MM-DD format. - TIME: hh:mm:ss format. - DATETIME, TIMESTAMP: YYYY-MM-DD hh:mm:ss format.

Answer 125

CREATE TABLE Students ( ... );

Answer 126

A column that uniquely identifies rows in a table

Answer 127

A key that links to the PRIMARY KEY of another table

Answer 128

To change the contents of a table such as adding, modifying, dropping etc. Example: ALTER TABLE table_name ADD COLUMN new_column data_type [constraint];

Answer 129

Deletes a table and all its data permanently Example: DROP TABLE table_name;

Answer 130

Used to check data is not a duplicate Example: CREATE TABLE Suppliers ( supplier_id INT AUTO_INCREMENT PRIMARY KEY, name VARCHAR(255) NOT NULL, UNIQUE(name) );

Answer 131

Used to check if a condition is true Example: CREATE TABLE Parts ( part_no INT PRIMARY KEY, description VARCHAR(40), price DECIMAL(12,2) NOT NULL CHECK(price > 0), cost DECIMAL(12,2) NOT NULL CHECK(price >= cost) );

Answer 132

Automatically generates a unique number for each new row. Commonly used for primary keys Example: CREATE TABLE Persons ( PersonID INT NOT NULL AUTO_INCREMENT PRIMARY KEY, FirstName VARCHAR(255) NOT NULL, LastName VARCHAR(255) );

Answer 133

A data structure that speeds up data retrieval but can slightly slow down updates.

Answer 134

1. Speeds up SELECT queries. 2. Improves performance for frequently searched columns.

Answer 135

A virtual table created from the result of an SQL query.

Answer 136

1. Simplifies complex queries. 2. Makes data updates reflect in all linked views (updatability).

Answer 137

Saves all changes permanently

Answer 138

Cancels all uncommitted changes

Answer 139

1. SELECT: Retrieves data. 2. INSERT INTO: Adds new rows. 3. UPDATE: Modifies existing rows. 4. DELETE: Removes rows.

Answer 140

1. CREATE TABLE: Defines a new table. 2. ALTER TABLE: Modifies table structure. 3. DROP TABLE/VIEW/INDEX: Deletes tables, views, or indexes. 4. Constraints: NOT NULL, UNIQUE, PRIMARY KEY, FOREIGN KEY.

Answer 141

- Focuses on implementing a database on storage (e.g., hard drive). - Defines how data is stored, indexed, and organized for performance.

Answer 142

- Create database schemas (using SQL DDL commands). - Populate data with SQL DML (e.g., INSERT). - Use indexes, constraints, and file organization for optimization.

Answer 143

1. Translate Logical Data Model for Target DBMS - Design Base Relations: - Define tables, columns, primary keys, and surrogate keys - Derived Data: - Decide whether to store derived data - General Constraints: - Use CHECK, stored procedures, and triggers for validation.

Answer 144

2. Design File Organizations and Indexes - File Organizations: - Choose how data is stored: heap (unordered) or sequential (ordered). - Indexes: - Use clustered indexes (physical order) or non-clustered indexes (logical order). - Disk Space Estimation: - Calculate storage needs based on table size and structure.

Answer 145

3. Design User Views and Security - User Views: - Define views for specific user roles and queries. - Security Mechanisms: - Use access control, encryption, and permissions.

Answer 146

An artificial key added to the table With Surrogate Key: - OrderDetails(OrderDetailID, OrderID, ProductID, Quantity) Without Surrogate Key - Uses natural data to uniquely identify rows: - OrderDetails(OrderID, ProductID, Quantity)

Answer 147

- When natural keys are too complex or unstable. - To avoid duplication across tables.

Answer 148

Data that is calculated based on other data in the database.

Answer 149

A rule that enforces data integrity in a table

Answer 150

Heap Files: - Data is stored unordered (faster for insertion). Sequential Files: - Data is stored in order (faster for specific queries). MySQL Storage Engines: - InnoDB: Default engine for transactions, foreign keys, and performance.

Answer 151

Clustered Index: Physical data stored in index order, one per table Non-Clustered Index: Logical ordering, multiple per table

Answer 152

Denormalization: - Improves read performance at the cost of write performance. - Used when updates are rare, but reads are frequent.

Answer 153

A transaction is a group of database operations (insert, update, delete, etc.) that are treated as a single logical unit

Answer 154

Atomicity - All or nothing: The transaction is fully completed, or no changes are made. Consistency - Transactions transform the database from one consistent state to another. Isolation - The operations of one transaction are invisible to others until it is completed. Durability - Once a transaction is committed, changes are permanently saved, even in the event of a system failure.

Answer 155

If any operation in a transaction fails, the entire transaction is rolled back to ensure data integrity.

Answer 156

A schedule is the order of operations for concurrent transactions

Answer 157

1. Serial Schedule: - Transactions are executed one after the other. - Ensures data consistency but is less efficient. 2. Non-Serial Schedule: - Operations of multiple transactions are interleaved. - Requires careful testing for conflicts.

Answer 158

- Serializability: The schedule should be equivalent to a serial execution. - Recoverability: The schedule should ensure no data is lost due to failed transactions.

Answer 159

Occurs if two transactions: - Access the same data - At least one is a WRITE operation

Answer 160

Build a precedence graph: - Each node represents a transaction. - Draw edges for conflicting operations. Check for cycles: - If cycles exist, the schedule is not serializable.

Answer 161

A schedule is recoverable if, for every transaction T2 that reads data written by T1, T1 commits before T2

Answer 162

Managing simultaneous operations on a database to prevent conflicts and maintain consistency

Answer 163

1. Schedule Transactions: Avoid interference between them 2. Guarantee Serializability: Ensure the results are as if transactions were executed one after the other

Answer 164

- Pessimistic: Assume conflicts are likely; prevent them proactively (e.g., locking, timestamping) - Optimistic: Assume conflicts are rare; resolve only if conflicts occur at commit time

Answer 165

A mechanism to prevent multiple transactions from accessing the same data simultaneously in a conflicting way

Answer 166

Entire database, file, page, record, or even a single field value

Answer 167

- Read Lock (Shared): Multiple transactions can read, but no writes allowed. - Write Lock (Exclusive): Only one transaction can write.

Answer 168

1. A transaction must issue read_lock or write_lock before reading. 2. A transaction must issue write_lock before writing. 3. Locks must be released after operations (unlock).

Answer 169

A locking protocol ensuring serializability by dividing a transaction into two phases: 1. Growing Phase: Acquire locks, no releases. 2. Shrinking Phase: Release locks, no new acquisitions. Benefits: Guarantees serializability

Answer 170

A situation where two or more transactions are stuck waiting for each other to release locks. Example: Transaction 1 locks X, waits for Y. Transaction 2 locks Y, waits for X.

Answer 171

1. Timeouts: Abort a transaction if it waits too long. 2. Detection and Recovery: - Build a Wait-For Graph (WFG): - Nodes: Transactions. - Edges: Dependencies (T1 → T2 means T1 waits for T2). - A cycle indicates a deadlock. - Abort transactions involved in the deadlock. 3. Prevention: - Lock all resources before execution (Conservative 2PL). - Use timestamps to prioritize transactions (e.g., older transactions wait for younger ones).

Answer 172

1. Wait-Die Algorithm: - Older transactions can wait for younger ones. - Younger transactions roll back if they must wait for older ones. 2. Wound-Wait Algorithm: - Older transactions force younger ones to roll back. - Younger transactions can wait for older ones.

Answer 173

- Assigns a unique timestamp to each transaction to order operations. - Older transactions are given priority during conflicts. Key Concept: - Ensures serializability by using timestamps to determine the execution order of transactions.

Answer 174

- Assumes conflicts are rare. - Performs checks at the end of the transaction to detect conflicts.

Answer 175

1. Execute transaction without locks. 2. Before committing, check if conflicts occurred. 3. If yes, roll back and restart the transaction. When to Use: Suitable for systems with low contention for resources.

Answer 176

Primary Storage: - Volatile (e.g., main memory, cache memory). - Data operations occur here but is lost on power failure. Secondary Storage: - Non-volatile (e.g., magnetic disk, optical disk, flash drives). - Used for storing the database permanently. Stable Storage: - Data is replicated across multiple non-volatile media (e.g., RAID). - Ensures reliability and protects against failures.

Answer 177

A periodic copy of the database stored securely to restore data in case of failure.

Answer 178

1. Full Backup: Entire database. 2. Differential Backup: Changes since the last full backup. 3. Incremental Backup: Changes since the last incremental backup.

Answer 179

Restoring the database to a consistent state after a failure

Answer 180

1. Log-Based Recovery: - Uses transaction logs to record database changes: - Before-Image (value before update). - After-Image (value after update). - Components: , , . 2. Checkpointing: - Marks a "safe point" to reduce recovery time. - Recovery process: - Redo: Transactions committed after the checkpoint. - Undo: Transactions active during failure. 3. Shadow Paging: - Maintains two page tables: - Shadow table (unchanged). - Current table (updated during transactions). - Advantages: No undo logs needed. - Disadvantages: High overhead for large updates.

Answer 181

1. Confidentiality: Prevent unauthorized access. 2. Integrity: Ensure data is accurate and unaltered. 3. Availability: Ensure data is accessible when needed.

Answer 182

- Unauthorized users. - Malicious/accidental errors by authorized users. - Errors by database administrators (DBAs).

Answer 183

1. Discretionary Access Control (DAC): - Users specify access rights. 2. Mandatory Access Control (MAC): - Bell-LaPadula Model: - "No Read Up": Cannot read data above clearance level - "No Write Down": Cannot write data below clearance level

Answer 184

- Combines multiple disks for: - Improved reliability (e.g., mirroring). - Increased performance (e.g., striping). - Enhanced availability with error correction.

Answer 185

1. Access Control 2. Views 3. RAID

Answer 186

1. Manage account creation and user access. 2. Assign and revoke privileges. 3. Implement security policies. 4. Ensure data integrity and performance optimization.

Answer 187

Database Administrator

Answer 188

A collection of multiple interconnected databases: - Physically spread across various locations. - Communicate via a network.

Answer 189

Distribution/Location Transparency: - Users see the database as one logical entity, no matter its physical location. Local Autonomy: - Local databases manage their own data independently, even if remote systems are down.

Answer 190

Homogeneous: - All sites use the same DBMS. Heterogeneous: - Different DBMSs at each site, with gateways translating between them.

Answer 191

Dividing a table into smaller subsets (fragments) stored across different sites

Answer 192

1. Horizontal: Subsets of rows. 2. Vertical: Subsets of columns (attributes). 3. Hybrid: A mix of horizontal and vertical fragmentation.

Answer 193

Assigning fragments to specific sites (optimal allocation is complex)

Answer 194

1. Full Replication: Entire database copied at all sites (better reliability). 2. Non-Redundant Allocation: Each fragment stored at one site (saves storage).

Answer 195

Advantages: 1. Mirrors organizational structure. 2. Simplifies system expansion. 3. Improves reliability and availability. 4. Enhances response time and performance. Disadvantages: 1. Complex design and implementation. 2. Ensuring security and integrity is harder. 3. Lack of standardization.

Answer 196

A database designed for analysis and decision-making: - Separate from operational databases. - Stores historical, consolidated data (not updated frequently).

Answer 197

1. Subject-Oriented: Focused on specific areas (e.g., sales, transactions). 2. Integrated: Combines data from multiple sources. 3. Time-Variant: Captures data changes over time. 4. Non-Volatile: Previous data isn’t altered when new data is added.

Answer 198

Answer 199

ETL Process: 1. Extract: Retrieve data from source systems. 2. Transform: Convert it into a usable format. 3. Load: Store it into the warehouse.

Answer 200

Uses multi-dimensional data views to provide quick access to strategic insights Key Features: OLAP cubes analyze data across dimensions (time, region, product etc.)

Answer 201

Process of discovering patterns, trends, and correlations in data using: 1. Statistical methods. 2. Mathematical models. 3. Artificial intelligence techniques.