Concept-Finals-Mock

Tags: #◇CCINFOM #◇Data Modeling #◇Information Structures #◇Computer Science #◇Databases #◇SQL

◇Overview

Subject: Information and Database Management

◇Details / Theory

Subject: Information and Database Management
Coverage: Module 1, Relational Model, Keys, Normalization, ACID
Time Limit: 45 Minutes

◇Part 1: Multiple Choice

Select the best answer for each question.

Which of the following best describes a DBMS?
A) A collection of related data.
B) Software that manages and controls access to the database.
C) The physical hardware where data is stored.
D) A diagram showing entity relationships.
In the Relational Model, a "Tuple" corresponds to which of the following?
A) A Column
B) A Table
C) A Row
D) A Key
Which level of data independence allows you to change the physical storage structures (like indexes) without affecting the logical schema?
A) Logical Data Independence
B) Physical Data Independence
C) Conceptual Data Independence
D) View Data Independence
Which anomaly occurs when you cannot add a new record because you are missing a required parent record (e.g., cannot add a Course without a Student)?
A) Update Anomaly
B) Deletion Anomaly
C) Insertion Anomaly
D) Modification Anomaly
A table is in 2NF if it is in 1NF and:
A) It has no transitive dependencies.
B) It has no repeating groups.
C) It has no partial dependencies.
D) Every determinant is a candidate key.
Which ACID property ensures that a transaction is "all or nothing"?
A) Atomicity
B) Consistency
C) Isolation
D) Durability
If a table has a Composite Primary Key (StudentID, CourseID), and the attribute StudentName depends ONLY on StudentID, this violates which normal form?
A) 1NF
B) 2NF
C) 3NF
D) BCNF
Which constraint ensures that a Foreign Key value must match an existing Primary Key value in the parent table?
A) Entity Integrity
B) Domain Integrity
C) Referential Integrity
D) User-Defined Integrity
What is a "Surrogate Key"?
A) A key derived from real-world data (like SSN).
B) A key made of multiple columns.
C) An artificial key with no business meaning (like an auto-increment ID).
D) A key that allows NULL values.
In an ER Diagram, what does a "Crow's Foot" symbol represent?
A) The "One" side of a relationship.
B) The "Many" side of a relationship.
C) An optional relationship.
D) A weak entity.

◇Part 2: True or False

Write TRUE if the statement is correct, and FALSE if it is incorrect.

A Primary Key can contain NULL values.
A Foreign Key can contain NULL values.
A table can have multiple Candidate Keys but only one Primary Key.
Normalization is primarily used to increase data redundancy for faster reads.
Logical Data Independence means you can add a new column to a table without breaking existing applications that don't use that column.
In 3NF, non-key attributes are allowed to depend on other non-key attributes.
A "Relation" in database terminology is simply a Table.
The "Consistency" property in ACID guarantees that the database remains in a valid state before and after a transaction.
A Weak Entity can exist without its owner/parent entity.
SQL is considered a procedural language (you tell the computer how to get data step-by-step).

◇Part 3: Identification / Fill in the Blanks

Identify the term being described.

The process of organizing data to reduce redundancy and improve data integrity.
A key consisting of two or more attributes that uniquely identify a row.
The specific type of dependency where A -> B and B -> C, therefore A -> C.
The standard language used for defining and manipulating relational databases.
A representation of the database that is independent of both hardware and software (usually an ERD).

◇Part 4: Advanced Concepts (New Topics)

Select the best answer.

Which Relational Algebra operation combines every row of Table A with every row of Table B?
A) Union ( $\cup$ )
B) Intersection ( $\cap$ )
C) Cartesian Product ( $\times$ )
D) Join ( $\bowtie$ )
In an EER model, if a Supertype (e.g., Employee) MUST belong to a Subtype (e.g., Pilot or Mechanic), this is called:
A) Partial Participation
B) Total Participation (Completeness Constraint)
C) Disjoint Rule
D) Overlapping Rule
Which Isolation Level prevents "Dirty Reads" but still allows "Non-Repeatable Reads"?
A) Read Uncommitted
B) Read Committed
C) Repeatable Read
D) Serializable
Which type of index sorts the actual data rows on the disk?
A) Non-Clustered Index
B) Clustered Index
C) Composite Index
D) Hash Index
What is the main risk of allowing User Input directly into a SQL query string without sanitization?
A) Deadlock
B) SQL Injection
C) Phantom Read
D) Data Redundancy

◇Part 5: Scenario Analysis

Briefly analyze the situation.

Scenario: You have a table Orders with 1 million rows. A query SELECT * FROM Orders WHERE OrderDate = '2023-01-01' takes 5 seconds to run.
Question: What is the most likely reason for the slowness, and what single change could fix it?
(Think about Indexing)
Scenario: Two users try to book the last seat on a flight at the exact same millisecond. Both see the seat as "Available", click "Book", and the system confirms both. Now the flight is overbooked.
Question: Which ACID property was violated, and what mechanism failed?
(Think about Isolation and Concurrency)

◇Part 6: Normalization Analysis

Determine the highest Normal Form (1NF, 2NF, or 3NF) of the table described.

Table: Orders(OrderID, ProductID, Qty, ProductName)
PK: (OrderID, ProductID)
Dependencies: ProductID -> ProductName
Status: **___**
Table: Student(StudentID, Name, PhoneNumbers)
Data: PhoneNumbers contains "555-0101, 555-0102" in a single cell.
Status: **___**
Table: Employee(EmpID, Name, DeptID, DeptName)
PK: EmpID
Dependencies: DeptID -> DeptName
Status: **___**

◇Common Pitfalls

Rushing through scenario questions without identifying the specific concept being tested (e.g., missing index vs. isolation failure).
Confusing relational algebra operators — selection ( $\sigma$ ) filters rows, projection ( $\pi$ ) filters columns.
Not reading normalization questions carefully — identify all functional dependencies before determining the normal form.

◇Summary

TL;DR — 35-item mock exam covering DBMS concepts, relational algebra, EER, normalization, concurrency, and scenario analysis. Practice under timed conditions.

◇Links

Related:
Source / Reference:

Made by 2ru17

Please contact me through discord or my the university email for any problems regarding the reviewer. Thanks!

Related Notes: ◇Information Management

CONCEPT-FINALS-MOCK