Fundamentals of Relational Databases: Key Concepts and Common Questions

What are the common relational database management system products? Answer: Oracle, SQL Server, MySQL, Sybase, DB2, Access, etc.

What parts does the SQL language include and what are the key keywords for each part? SQL consists of Data Definition Language (DDL) – e.g., CREATE TABLE, ALTER TABLE, DROP TABLE, CREATE/DROP INDEX; Data Manipulation Language (DML) – SELECT, INSERT, UPDATE, DELETE; Data Control Language (DCL) – GRANT, REVOKE; and Data Query Language (DQL) – SELECT.

What are integrity constraints? Data integrity ensures accuracy and reliability. It includes entity integrity (unique rows), domain integrity (column type and range constraints), referential integrity (consistent primary‑key/foreign‑key relationships), and user‑defined integrity (application‑specific rules).

What is a transaction and what are its properties? A transaction is a logical unit of work consisting of one or more database operations. Its ACID properties are: Atomicity (all-or‑nothing), Consistency (transforms the database from one correct state to another), Isolation (intermediate results are invisible to other transactions), and Durability (committed results survive failures).

What is a lock? Locks control concurrent access to shared data, preventing inconsistent reads/writes. Basic lock types include row‑level locks and table‑level locks.

What are views and cursors? A view is a virtual table derived from one or more base tables; it simplifies data access and can provide logical independence and security. A cursor allows row‑by‑row processing of a result set, enabling fine‑grained manipulation when set‑based operations are insufficient.

What is a stored procedure and how is it invoked? A stored procedure is a pre‑compiled set of SQL statements that can be executed repeatedly, improving performance and modularity. It is called via a command object (e.g., EXECUTE or CALL).

What is the purpose of an index and its pros and cons? Indexes accelerate data retrieval similar to a book's table of contents. They can be unique and may involve single or multiple columns. Drawbacks include slower data insertion and increased storage size.

How can the three normal forms be explained simply? 1NF enforces atomic attribute values. 2NF requires each non‑key attribute to be fully functionally dependent on the whole primary key. 3NF eliminates transitive dependencies, ensuring no non‑key attribute is derived from another non‑key attribute.

What are basic tables and views? A basic table exists independently in the database. A view is derived from one or more basic tables and does not store data physically; it offers simplified operations, multiple perspectives, logical independence, and security benefits.

What does NULL mean? NULL represents an unknown value, not an empty string. Any comparison with NULL yields NULL; use IS NULL to test for it.

Differences among primary key, foreign key, and index? Primary key uniquely identifies a record, cannot be NULL, and ensures data integrity. Foreign key references a primary key in another table, may contain duplicates or NULLs, and establishes relationships. Indexes speed up query and sort operations; they can contain duplicate values but usually allow a single NULL.

How to enforce a column to accept only values within a specific range? Use a CHECK constraint defined on the column.

What are some SQL optimization methods? 1) Order WHERE conditions to filter most rows early; place HAVING last. 2) Replace IN with EXISTS and NOT IN with NOT EXISTS. 3) Avoid calculations on indexed columns. 4) Avoid IS NULL/IS NOT NULL on indexed columns. 5) Create indexes on columns used in WHERE and ORDER BY to prevent full table scans. 6) Avoid unnecessary NULL checks and expression operations on indexed columns.

Difference between correlated and non‑correlated subqueries? Non‑correlated subqueries execute once independently and pass results to the outer query. Correlated subqueries depend on each outer row, executing repeatedly, thus generally slower.

Difference between CHAR and VARCHAR? CHAR is fixed‑length, padding shorter values with spaces (which are trimmed on retrieval). VARCHAR is variable‑length, storing only the actual bytes plus one length byte.

Differences between MyISAM and InnoDB storage engines? MyISAM is non‑transactional, uses table‑level locking, suited for read‑heavy workloads with small data and low concurrency. InnoDB supports transactions, row‑level locking, foreign keys, and is better for write‑intensive, large‑scale, high‑concurrency scenarios.

What are the common MySQL table types? MyISAM, InnoDB, HEAP, BDB, ARCHIVE, CSV, etc.

How to optimize a MySQL database handling >50,000 daily inserts for a three‑year operation? 1) Design a good schema with possible denormalization. 2) Choose appropriate data types and storage engine; add indexes wisely. 3) Implement master‑slave replication for read/write separation. 4) Partition large tables. 5) Use caching (Redis, Memcached). 6) Generate static pages for rarely changing content. 7) Write efficient SQL (select specific columns instead of SELECT *).

How to handle page‑view statistics for high‑traffic websites? 1) Verify server capacity. 2) Optimize database access. 3) Prevent hotlinking. 4) Control file downloads. 5) Use load balancing across multiple hosts. 6) Deploy web analytics tools to monitor traffic and guide optimizations.