Relationals

Question 1

Relational Database

Answer 1

type of database that stores and provides access to data points that are related to one another. It organizes data into tables which can be linked by defining relationships between the tables.

Question 2

Relational Model

Answer 2

framework for managing databases where data is stored in tables composed of rows and columns. It establishes how data can be related to each other and manipulated using operations that maintain these relationships

Question 3

What is CRUD?

Answer 3

refers to the four fundamental operations: Create, involves in adding new data entries to the database, Read, allows retrieving or accessing existing data, Update, modifies existing data entries, and Delete removes unwanted data from the database. These operations form the backbone of database manipulation and are crucial for effectively managing and maintaining data integrity.

Question 4

Data Model

Answer 4

A conceptual representation of how data is organized and structured within the database. It defines the relationship between different data elements, such as tables, attributes, and their constraints. A blueprint for designing and implementing a database system, outlining how data is stored, accessed, and manipulated.

Question 5

What is Table Indexing

Answer 5

Method to speed up data retrieval. It creates special lists sorted by certain column values, making it faster for the database to find specific data.

Question 6

What is a primary key and its purpose

Answer 6

primary key is a column or a set of columns that uniquely identifies each record in a table. It must have a unique value for each row and cannot contain NULL values. Ensures data integrity by preventing duplicate or null values in the key columns. Serves as a reference point for establishing relationships with other tables

Question 7

What is a Foreign key and its purpose

Answer 7

A foreign key is a column or set of columns in a table that establishes a link or relationship with a primary key in another table. It ensures referential integrity by enforcing a relationship between the data in two tables.

Question 8

What is Entity Integrity in database?

Answer 8

Entity integrity ensures that each row or record in a database table is unique and accurately represents a distinct entity or object. It prevents duplicates or inconsistent data within a table.

Question 9

What is referential integrity in database?

Answer 9

Referential integrity ensures that relationships between tables are maintained accurately. This means that foreign key values in one table must correspond to primary key values in another table. It guarantees the reliability and coherence of data relationships within a database.

Question 10

What is Database Recovery?

Answer 10

Database recovery refers to the process of restoring a database to a consistent state after a failure or a system crash. It involves techniques such as rollback to bring the database back to the original state it was in before the failure occurred.

Question 11

What is Rollback in database operation?

Answer 11

Rollback undoes or cancels the effects of an incomplete or aborted transaction. When a transaction encounters an error or is explicitly rolled back by the user, the db management reverts any changes made by the transaction to ensure data consistency and integrity.

Question 12

What is Database log and its purpose?

Answer 12

Database log is a record of all changes made to the database. It stores chronological sequence of transactions, including details such as the operations performed, the affected data, and the before and after values of the data.

Question 13

What is a transaction in database?

Answer 13

A transaction is a unit of work or a sequence of database operations that are executed as a single logical unit. Transactions ensure data integrity by either completing successfully and committing all changes, or failing and rolling back all changes if an error occurs. ACID properties characterize transactions.

Question 14

What is SQL (Structured Query Language)?

Answer 14

SQL is a standard programming language used to manage and manipulate a relational database. It provides a way to interact with the database by performing various operations such as querying data, inserting, updating, and deleting records, defining database structures, and controlling access to the database.

Question 15

Summary of SQL Queries

Answer 15

SELECT (attribute list)
FROM (table list)
WHERE (condition)
GROUP BY (grouping attribute(s)
HAVING (group condition)
ORDER BY (attribute list)

Question 16

What is Atomicity from ACID

Answer 16

Atomicity means that you guarantee that either all of the transaction succeeds or none of it does. You don’t get part of it succeeding or part of it not. If one part of the transaction fails, the whole transaction. Either all or nothing.

Question 17

What is Consistency from ACID

Answer 17

Consistency guarantees that all data will be consistent. All data will be valid according all define rules, including constraints, cascades, and triggers that have been applied to the database.

Question 18

What is Isolation from ACID

Answer 18

Isolation guarantees that all transactions will occur in isolation. No transaction will be affected by any other transaction. Even if multiple transactions are happening at the same time, they won’t interfere with each other’s operations. Ensuring consistency and preventing conflicts.

Question 19

What is Durability from ACID

Answer 19

Durability means that once a transaction is committed, its effects are permanent and will be be save, even in the event of a system failure such as power outage or crash. The transaction will persist and remain intact, ensuring data reliability and integrity over time.

Question 20

What is ACID and when is it needed?

Answer 20

ACID is a set or properties that ensure transactions in a database are reliable and consistent. It guarantees an integrity and reliability of transactions in a database system. Crucial in scenarios where data integrity, reliability, and consistency are paramount, such as in finance, e-commerce, healthcare database and other application where accuracy is essential

Question 21

What is the cost associated to be ACID compliant?

Answer 21

Performance overhead: can lead to performance overhead due to techniques like locking and isolation, reducing throughput and increasing latency.

Complexity: Introduces complexity in transaction management, logging and recovery mechanisms, increasing development and maintenance costs while raising the risk of bugs.

Scalability: ACID systems may struggle to scale efficiently, especially in distributed environments, requiring additional optimizations and resources to maintain consistency.

Resource utilization: ACID systems may consume more resources like memory, CPU, and disk impacting overall resource utilization and increasing operation costs.

Question 22

What is Database normalization and its usage?

Answer 22

A design technique that reduces data redundancy. It involves breaking down large tables into smaller, related tables and defining relationships between them.

The primary goal of normalization is to eliminate data anomalies such as insertion, update, and deletion anomalies, which can occur when data is duplicated across multiple tables. Eliminate redundancy on all tables.

Question 23

What is First Normal Form (1NF)

Answer 23

In 1NF, all attributes in a table must have atomic values, meaning it contains only a single value

Consider a table called StudentGrades:

StudentID Subject Grades
1 Math, Science A, B
2 English, Math A, C

This table violates 1NF because the Subject and Grades columns contain multiple values, violating the atomicity rule.

To make it 1NF compliant, we need to separate the multi-valued attributes into separate rows:
StudentID Subject Grades
1 Math A
1 Science B
2 English C
2 Math C

Question 24

What is Second Normal Form (2NF)

Answer 24

In 2NF, a table must meet the criteria of 1NF and all non-key attributes must be fully functionally dependent on the entire primary key, meaning no partial dependencies exist.

Example:
Consider a table called StudentCourses:
StudentID CourseID CourName Instructor
1 101 Math Mr.Smith
2 102 English Ms. Johnso
3 103 Math Mr.Smith

*This table violates 2NF because ‘Instructor’ is dependent only on ‘CourseID’, not on the entire composite key (‘StudentID’, ‘CourseID’)

To make it 2NF compliant, we need to separate the tables:
Student courses
StudentID CourseID
1 101
2 102

Courses:
CourseID CourseName Instructor
101 Math Mr.Smith
102 English Ms.Johnson

Question 25

What is Third Normal Form (3NF)

Answer 25

In 3NF, a table must meet the criteria of 2NF, and no transitive dependencies should exist
*Transitive dependencies - means no non-key attribute is dependent on another non-key attribute

Question 26

What is Functional Dependencies

Answer 26

Functional dependencies refer to the relationship between attributes or columns in tab;e where the value of one attribute uniquely determines the value of another attribute in the same. This helps ensure data integrity and reduce redundancy by organizing data efficiently and accurately.