Data-Management-Foundations Questions and Answers

In SQL,literalsrepresent explicit values such asnumbers, strings, or binary datadirectly written into queries. For example:

SELECT * FROM Employees WHERE Salary > 50000;

Here, 50000 is anumeric literal.

Option A (Correct):Literalsare explicit values used in SQL queries, such as 123, 'John Doe', and TRUE.

Option B (Incorrect):Commentsare non-executable text used for documentation within SQL code, typically denoted by -- or /* ... */.

Option C (Incorrect):Identifiersare names oftables, columns, or other database objects, such as EmployeeID.

Option D (Incorrect):Keywordsare reserved words in SQL (e.g., SELECT, FROM, WHERE) that define operations and syntax.

In SQL, aprimary key is explicitly defined using the PRIMARY KEY keywordwhen creating a table.

Example Usage:

sql

CREATE TABLE Products (

ProductID INT PRIMARY KEY,

Name VARCHAR(100),

Price DECIMAL(10,2)

);

Here,PRIMARY KEY is the SQL keyword that designates ProductID as the primary key.

Why Other Options Are Incorrect:

Option A (Formula in SQL) (Incorrect):SQLdoes not use formulas to define primary keys.

Option C (Bold typeface) (Incorrect):SQL syntax does not rely on text formatting.

Option D (Diamond symbol) (Incorrect):ER diagramsmight use symbols, but SQLdoes not use diamonds to indicate keys.

Thus, the correct answer isSQL keyword, as primary keys are explicitly defined using PRIMARY KEY.

TheUNIONkeyword in SQL is used tocombine the resultsof two or more SELECT queries into asingle result setwhile removing duplicate rows.

Example:

sql

SELECT Name FROM Employees

UNION

SELECT Name FROM Managers;

Option A (Correct):UNION combines results from multiple queries into one set,removing duplicates.

Option B (Incorrect):MERGE is not a valid SQL keyword for combining result sets (it is used insome database systems for data merging).

Option C (Incorrect):INTEGRATE is not a SQL keyword.

Option D (Incorrect):CONSOLIDATE is not an SQL keyword.

Indatabase design,attributesof an entity becomecolumnsin a relational table.

Example Usage:

For anEmployee entity, attributes might include:

CREATE TABLE Employees (

EmployeeID INT PRIMARY KEY,

Name VARCHAR(50),

Salary DECIMAL(10,2),

DepartmentID INT

);

Eachattribute(e.g., Name, Salary) becomes acolumnin the table.

Why Other Options Are Incorrect:

Option A (Modality) (Incorrect):Describesoptional vs. mandatoryrelationships, not table structure.

Option B (Uniqueness) (Incorrect):Ensuresdistinct valuesbutis not a column property.

Option D (Non-null values) (Incorrect):Ensures thatcolumns must contain databut doesnot define attributes.

Thus, the correct answer isAttribute, as attributes of entities becometable columns.

Atangible entityis a real-world object that can bemeasured and storedin a database.

Example Usage:

In an inventory system,tangible entitiesinclude:

Products, Orders, Customers

Why Other Options Are Incorrect:

Option A (Logical entity) (Incorrect):Exists logically butmay not have a physical presence(e.g.,views, categories).

Option C (Virtual entity) (Incorrect):Existsonly in queries or reports, not stored as real data.

Option D (Conceptual entity) (Incorrect):Abstract idea used indesign modeling, not astored entity.

Thus, the correct answer isTangible entity, as it representsmeasurable, real-world objects.

TheCASCADEconstraint ensures thatupdates or deletions in the primary key table automatically reflect in the foreign key table.

Example Usage:

sql

CREATE TABLE Departments (

DeptID INT PRIMARY KEY,

DeptName VARCHAR(50)

);

CREATE TABLE Employees (

EmpID INT PRIMARY KEY,

Name VARCHAR(50),

DeptID INT,

FOREIGN KEY (DeptID) REFERENCES Departments(DeptID) ON UPDATE CASCADE ON DELETE CASCADE

);

If DeptIDchangesin Departments, itautomatically updatesin Employees.

If a DeptID isdeleted, all employees in that departmentare also deleted.

Why Other Options Are Incorrect:

Option A (SET DEFAULT) (Incorrect):Sets foreign key values to adefaultvalue, rather than propagating changes.

Option B (SET NULL) (Incorrect):When the referenced key is deleted, dependent records areset to NULLinstead of being updated/deleted.

Option C (RESTRICT) (Incorrect):Prevents deletion of a referenced row if dependent foreign key rows exist.

Thus, the correct answer isCASCADE, as itpropagates primary key changes to dependent foreign keys.

Indatabase design, an entity is classified asstrong or weakbased on whether it has aprimary keythat uniquely identifies its records.

Differences Between Strong and Weak Entities:

CREATE TABLE Orders (

OrderID INT PRIMARY KEY,

CustomerID INT

);

CREATE TABLE OrderDetails (

OrderDetailID INT,

OrderID INT,

ProductID INT,

PRIMARY KEY (OrderDetailID, OrderID),

FOREIGN KEY (OrderID) REFERENCES Orders(OrderID)

);

Orders is astrong entity(has OrderID as its own primary key).

OrderDetails is aweak entity(depends on OrderID for uniqueness).

Why Other Options Are Incorrect:

Option A (Association) (Incorrect):Associations describerelationshipsbut do not define strong/weak entities.

Option C (Foreign key) (Incorrect):Weak entitiesdepend on foreign keys, butprimary keysdefine their status.

Option D (Cardinality) (Incorrect):Cardinality definesrelationship constraintsbut doesnot differentiate entity types.

Thus, the correct answer isPrimary key, as it is the defining characteristic betweenstrong and weak entities.

When a VIEW is created in SQL, users mayinsert or updatedata through that view. However, if a row is inserted or updated in such a way that itviolates the conditionof the VIEW’s WHERE clause, it can lead to inconsistencies.

Topreventsuch unwanted modifications,SQL provides the WITH CHECK OPTION clause.

How WITH CHECK OPTION Works:

Ensures thatany new data (INSERT/UPDATE) still fits within the defined constraints of the VIEW.

If a user tries to insert or update a row that would not appear in the VIEW,the operation isrejected.

Example:

sql

CREATE VIEW HighSalaryEmployees AS

SELECT * FROM Employees WHERE Salary > 50000

WITH CHECK OPTION;

Now, if someone attempts:

sql

INSERT INTO HighSalaryEmployees (ID, Name, Salary)

VALUES (101, 'Alice', 30000);

Thisfailsbecause 30000 does not satisfy Salary > 50000.

Why Other Options Are Incorrect:

Option B (Incorrect):JOIN VIEWS isnot a valid SQL clause.

Option C (Incorrect):MATERIALIZED VIEW refers tostored viewsin some databases, but it doesnotreject incorrect inserts/updates.

Option D (Incorrect):BASE TABLE refers to theoriginal table from which a VIEW is created.

Thus, the correct answer is WITH CHECK OPTION, whichensures that only valid data modifications occur.

Aprimary keyuniquely identifies each row in a table. When a primary key consists ofonly one field, it is called aSimple Primary Key.

Types of Primary Keys:

Simple Primary Key (Correct Answer):

Contains onlyone column.

Example:

sql

CREATE TABLE Students (

StudentID INT PRIMARY KEY,

Name VARCHAR(50)

);

Composite Primary Key:

Usesmultiple columnsto ensure uniqueness.

Example:

sql

CREATE TABLE Orders (

OrderID INT,

ProductID INT,

PRIMARY KEY (OrderID, ProductID)

);

Surrogate Primary Key:

A system-generatedunique identifier(e.g., UUID or AUTO_INCREMENT).

Why Other Options Are Incorrect:

Option B (Duplicate) (Incorrect):A primary keymust be unique, so itcannot be duplicate.

Option C (Numeric) (Incorrect):While primary keyscan be numeric, they can also be alphanumeric (VARCHAR).

Option D (Composite) (Incorrect):Acompositekey consists ofmultiple fields, whereas a simple key isa single field.

Thus, the correct answer isSimple, since aone-field primary keyis a simple primary key.

ATransaction ManagerensuresACID (Atomicity, Consistency, Isolation, Durability)properties in database transactions. It manages concurrent transactions, ensuring no conflicts occur and logs modifications to support recovery mechanisms.

Option A (Incorrect):Query optimization is managed by thequery processor, not the transaction manager.

Option B (Incorrect):The transaction manager is a component of the database architecture but is not composed of the entire system (query processor, storage manager, etc.).

Option C (Correct):The transaction manager logs transactions like INSERT, UPDATE, and DELETE, ensuring consistency and recoverability.

Option D (Incorrect):Thestorage manageris responsible for translating queries into filesystem commands.

In MySQL, theBIGINTdata type is a64-bit integerthat requires8 bytes (64 bits) of storage. It is used to store large numerical values beyond the range of INT (4 bytes).

Option A (Incorrect):1 byte corresponds toTINYINT, which can store values from -128 to 127.

Option B (Incorrect):3 bytes is not a standard integer storage size in MySQL.

Option C (Incorrect):4 bytes corresponds toINT, which has a range of -2,147,483,648 to 2,147,483,647.

Option D (Correct):BIGINT takes8 bytesand supports a massive range of numbers from -2^63 to 2^63 -1.

Thelogical design phasein database development focuses onstructuring data efficientlyto eliminate redundancy and ensure integrity. Thefinal step in logical designis toapply normalization (normal forms)to optimize the database schema.

Steps in Logical Database Design:

Discover entities→ Identify real-world objects (e.g., Customers, Orders).

Determine cardinality→ Define relationships between entities (one-to-one, one-to-many).

Analyze data requirements→ Determine the attributes each entity needs.

Apply normal forms→ Eliminate redundancy and improve data consistency.

Example Usage:

After identifying entities likeStudentsandCourses, applying3rd Normal Form (3NF)ensures that data isorganized without redundancy.

Why Other Options Are Incorrect:

Option A (Analyze data requirements) (Incorrect):Doneearlierto define attributes.

Option C (Determine cardinality) (Incorrect):Donebeforenormalization to establish relationships.

Option D (Discover entities) (Incorrect):Done at thebeginningof database design.

Thus, the correct answer isApply a normal form, as normalization is thelast step in logicaldesign.

Aunary relationship(also known as arecursive relationship) occurs when an entityrelates to itself.

Example Usage:

Employees and Managers:

sql

CREATE TABLE Employees (

EmpID INT PRIMARY KEY,

Name VARCHAR(50),

ManagerID INT,

FOREIGN KEY (ManagerID) REFERENCES Employees(EmpID)

);

Here, ManagerIDreferences another Employee→ aunary (self-referential) relationship.

Why Other Options Are Incorrect:

Option A (Modality) (Incorrect):Describesoptional vs. mandatoryrelationships, not self-referencing.

Option C (Cardinality) (Incorrect):Defines how many instances relate,not the type of relationship.

Option D (Binary) (Incorrect):Binary relationships involvetwo different entities, not self-referencing.

Thus, the correct answer isUnary, as it describesrelationships within the same entity type.