Databases Comprehensive Final Exam

What is the difference between generalization and specialization?

- Generalization: Combines entities into a higher-level entity. - Specialization: Divides an entity into sub-entities.

What are ACID properties?

Atomicity: All or nothing. Consistency: Database remains valid. Isolation: Transactions do not interfere. Durability: Committed transactions are saved.

What is k-nearest neighbors (k-NN)?

A classification algorithm that assigns a label based on the majority label of the k nearest neighbors.

What is a relationship in an ER diagram?

A connection between entities, represented by a diamond.

What are DataFrames in Spark?

A distributed collection of data organized into named columns. DataFrames are similar to SQL tables and provide a higher-level abstraction than RDDs.

What is Spark?

A distributed computing system for fast, in-memory processing of big data.

What is Hadoop?

A framework for distributed storage and processing of large datasets.

What violates BCNF?

A functional dependency where the determinant is not a superkey.

What is classification?

A method to assign discrete labels or categories to data (e.g., spam vs. not spam).

What is clustering?

A method to group data into clusters based on similarity (e.g., customer segmentation).

What is regression?

A method to predict continuous values based on input data (e.g., predicting house prices).

What is MapReduce?

A programming model for processing large datasets by splitting tasks into Mappers and Reducers.

What is a subquery?

A query nested inside another query. Can be in SELECT, WHERE, or FROM clauses.

What is an entity?

A real-world object or concept represented as a table in a database.

What is the requirement for 1NF?

A relation is in 1NF if all attribute values are atomic (indivisible).

What is the requirement for 2NF?

A relation is in 2NF if it is in 1NF and all non-prime attributes depend on the entire primary key.

What is the requirement for 3NF?

A relation is in 3NF if it is in 2NF and has no transitive dependencies.

What is the requirement for 4NF?

A relation is in 4NF if it is in BCNF and contains no non-trivial multivalued dependencies.

What is the requirement for 5NF?

A relation is in 5NF if it is in 4NF and resolves all join dependencies not implied by candidate keys.

What is the requirement for BCNF?

A relation is in BCNF if it is in 3NF and every determinant is a superkey.

What is k-means clustering?

An algorithm that partitions data into k clusters by minimizing intra-cluster variance.

What is XSS (Cross-Site Scripting)?

An attack where malicious scripts are injected into web pages to exploit users.

What is a weak entity?

An entity that depends on a strong entity for its primary key. Represented with a double rectangle.

What are multivalued attributes?

Attributes that can have multiple values, represented by a double oval.

What violates 5NF?

Cyclic or complex join dependencies causing redundancy.

How do you fix a 3NF violation?

Decompose into smaller relations to eliminate transitive dependencies.

How do you fix a 5NF violation?

Decompose the relation into multiple tables to resolve join dependencies.

How do you fix a 2NF violation?

Decompose the relation to remove partial dependencies.

How do you fix a 4NF violation?

Decompose the relation to separate multivalued attributes into distinct tables.

How do you fix a BCNF violation?

Decompose the table into smaller relations.

What is a JOIN predicate in the WHERE clause?

Defines the join condition in the WHERE clause instead of the ON clause. Example: SELECT * FROM table1, table2 WHERE table1.id = table2.id; Use this style in simple joins, but avoid it in complex queries for clarity

What are RDDs in Spark?

Resilient Distributed Datasets, the fundamental data structure in Spark for fault-tolerant parallel processing.

What is a SELECT statement?

Retrieves data from a table using specified conditions and columns.

What violates 3NF?

Transitive dependency: If A→BA \to B and B→CB \to C, then A→CA \to C causes redundancy.

What are set operations in SQL?

UNION: Combines results without duplicates. EXCEPT: Returns rows in the first query not in the second. INTERSECT: Returns rows common to both queries.

How do you prevent SQL injection?

Use parameterized queries or prepared statements.

How do you connect to a MariaDB database in Python?

import pymysql connection = pymysql.connect( host='localhost', user='root', password='password', database='mydb' ) Connects to MariaDB for querying.

How do you use map and reduce in Spark?

rdd = spark.sparkContext.parallelize([1, 2, 3, 4]) result = rdd.map(lambda x: x * 2).reduce(lambda x, y: x + y) print(result) map transforms each element; reduce aggregates elements.

What are the 3Vs of Big Data?

- Volume: Large amounts of data. - Velocity: Speed of data generation. - Variety: Different types of data (structured, unstructured).

What is a correlated subquery?

A subquery that depends on the outer query for its values.

What is cross-validation?

A technique to evaluate model performance by splitting data into training and testing sets.

What is a view in SQL?

A virtual table created from a query. Used for simplifying complex queries or securing data access.

What is SQL injection?

A vulnerability where attackers inject malicious SQL into queries.

What does the WHERE clause do?

Filters rows based on specified conditions.

How do you modify data in SQL?

INSERT: Adds new rows. UPDATE: Modifies existing rows. DELETE: Removes rows.

What is a FULL JOIN?

Includes all rows from both tables, with matching rows combined. Use when you want all data, matching or not, from both tables.

What is a LEFT JOIN?

Includes all rows from the left table and matches from the right table. Use when you want all rows from the left table, even if there are no matches in the right table.

What is a RIGHT JOIN?

Includes all rows from the right table and matches from the left table. Use when you want all rows from the right table, even if there are no matches in the left table.

What violates 4NF?

Independent multivalued attributes tied to the same key.

What is a NATURAL JOIN?

Matches rows based on columns with the same name and data type in both tables. Use for simplicity when the matching columns have identical names in both tables.

What is an INNER JOIN? (Need to find equivalent)

Matches rows in both tables based on a condition. Use when you only want matching records from both tables.

What violates 1NF?

Multivalued attributes, lists, or sets in a column.

What violates 2NF?

Partial dependency: A non-prime attribute depends on part of a composite key.

What is a JOIN using the USING keyword?

Specifies the column(s) to match when they share the same name in both tables. Example: SELECT * FROM table1 JOIN table2 USING (common_column);

What is a JOIN using the ON keyword?

Specifies the condition to match rows explicitly. Use when the columns to join have different names or require specific conditions. Example: SELECT * FROM table1 JOIN table2 ON table1.id = table2.foreign_id;

How do you fix a 1NF violation?

Split multivalued attributes into separate rows or tables.

What does cardinality indicate?

The number of instances of one entity that can be associated with another (e.g., 1:1, 1:N, N:M).

How do you use a simple Spark function?

```python data = [(1, 'Alice'), (2, 'Bob')] df = spark.createDataFrame(data, ['id', 'name']) df.show() ``` Creates a DataFrame and displays it.

How do you create a Spark session?

```python from pyspark.sql import SparkSession spark = SparkSession.builder.appName("Example").getOrCreate() ``` Spark sessions are entry points for running Spark jobs.

How do you use prepared statements in Python?

cursor = connection.cursor() cursor.execute("SELECT * FROM users WHERE username = %s", (username,)) results = cursor.fetchall() Prevents SQL injection by parameterizing queries.