Font size
WorksheetsPrelim Exam - CC105 - Information Management (Database 2)
Total questions: 51
Worksheet time: 31mins
Scenario: You need to add a "MiddleName" column to an existing "Students" table. Which command is correct?
UPDATE Students ADD COLUMN MiddleName VARCHAR(50);
ALTER TABLE Students ADD COLUMN MiddleName VARCHAR(50);
CREATE COLUMN MiddleName IN Students;
INSERT INTO Students (MiddleName) VALUES ('VARCHAR');
Evaluation: Which of the following best justifies the use of "Stored Procedures" in a PostgreSQL environment?
To make the database structure harder to read.
To encapsulate business logic and reduce network traffic between the app and the DB.
To replace the need for the SELECT command.
To store physical images, texts, numbers, and other important information inside the database.
Synthesis: If a database designer chooses to use "Composite Keys," what are they essentially doing?
Using a single auto-incrementing integer as a key.
Using two or more columns together to uniquely identify a row.
Avoiding the use of primary keys entirely.
Linking the database to an external API.
Evaluation: How does PostgreSQL handle "ACID" properties compared to basic NoSQL systems?
PostgreSQL prioritizes performance over ACID.
PostgreSQL guarantees strict ACID compliance for reliable transactions.
NoSQL systems are generally more ACID compliant than PostgreSQL.
ACID properties are only relevant to NoSQL.
Synthesis: You are tasked with migrating a local database to a cloud-based PostgreSQL instance. What is the most critical first step in terms of security?
Designing the tables.
Configuring roles, permissions, and firewall settings.
Writing SQL queries.
Installing a GUI like pgAdmin.
Evaluation: If a non-key attribute depends on another non-key attribute (Transitive Dependency), the table is failing which normal form?
1NF
b) 2NF
3NF
4NF
Scenario: You need to add a "MiddleName" column to an existing "Students" table. Which command is correct?
UPDATE Students ADD COLUMN MiddleName VARCHAR(50);
ALTER TABLE Students ADD COLUMN MiddleName VARCHAR(50);
CREATE COLUMN MiddleName IN Students;
INSERT INTO Students (MiddleName) VALUES ('VARCHAR');
Synthesis: A university database has a Students table and a Grades table. To see a student's name alongside their grade, what operation must be performed?
UNION
JOIN
AGGREGATE
INTERSECT
Evaluation: In a database design, "Redundancy" is generally avoided. However, in what scenario might it be acceptable?
When storage space is very expensive.
To improve read performance in specific high-traffic analytical systems.
When the developer is lazy.
It is never acceptable under any circumstances.
Synthesis: Design a query to find categories that have an average product price greater than $100.
SELECT CategoryID FROM Products WHERE AVG(Price) > 100;
SELECT CategoryID FROM Products GROUP BY CategoryID HAVING AVG(Price) > 100;
SELECT CategoryID FROM Products GROUP BY CategoryID WHERE Price > 100;
SELECT AVG(Price) FROM Products GROUP BY CategoryID;
Evaluation: Why is using SELECT * considered bad practice in production applications?
It is technically illegal in PostgreSQL.
It can return unnecessary data, wasting bandwidth and memory.
It prevents the database from using indexes.
It automatically deletes data after fetching.
Synthesis: You need to ensure that no "Order" can be created for a "ProductID" that doesn't exist. Which database concept enforces this?
Entity Integrity (Primary Key)
Referential Integrity (Foreign Keys)
Data Atomicity
Normalization
Evaluation: What is the primary risk of "Denormalization"?
It makes queries slower.
It increases data redundancy and risk of data inconsistency.
It makes the database schema too simple and elegant than normalization.
It prevents the use of SQL.
Synthesis: If you need to generate a report showing the total number of orders placed by each customer, which combination of SQL features is required?
SELECT, WHERE, and SUM
SELECT, COUNT, and GROUP BY
SELECT, MAX, and ORDER BY
INSERT and SELECT
Evaluation: A table is in 1NF but has "Partial Functional Dependencies" (attributes depending on only part of a composite primary key). Which Normal Form should it be converted to next?
2NF
3NF
BCNF
4NF
Synthesis: You are designing a database for a library. A "Book" can have multiple "Authors," and an "Author" can write multiple "Books." What is the best way to model this?
Put a list of authors in a text column in the Book table.
Put a list of books in a text column in the Author table.
Create a third "Join Table" with foreign keys to both.
Create a one-to-many relationship from Author to Book.
Evaluation: When is a NoSQL database a better choice than a Relational database?
When data is highly structured and requires strict consistency.
When handling massive amounts of unstructured data with a need for horizontal scaling.
When complex JOIN operations are the primary requirement.
Always; NoSQL is the modern replacement for RDBMS.
Analysis: What happens if you try to INSERT a NULL value into a column defined as NOT NULL?
PostgreSQL converts it to an empty string.
PostgreSQL throws an error and stops the insertion.
PostgreSQL ignores the constraint.
The value is inserted as 0.
Application: Which query correctly calculates the average age of users in a "Users" table?
SELECT AVG(age) FROM Users;
SELECT SUM(age)/COUNT(age) FROM Users;
Both a and b are logically correct.
SELECT MEAN(age) FROM Users;
Analysis: In a One-to-Many relationship between "Department" and "Employees," where does the Foreign Key usually reside?
In the Department table.
In the Employees table.
In both tables.
In a separate join table.
Application: Which operator is used to search for a specific pattern in a column (e.g., names starting with 'A')?
MATCH
CONTAINS
LIKE
=
Analysis: If a table has 10 rows and you run INSERT for 2 more rows but the transaction fails/rolls back, how many rows are in the table?
12
13
10
11
10. Scenario: A user is connected to PostgreSQL via terminal and wants to list all available tables. Which command is used?
\list
\dt
\d
SELECT * FROM tables;
Application: Write the query to find the cheapest item in the "Inventory" table.
SELECT MIN(price) FROM Inventory;
SELECT SMALL(price) FROM Inventory;
SELECT price FROM Inventory ORDER BY price DESC LIMIT 1;
SELECT COUNT(price) FROM Inventory;
Analysis: What is the main difference between DELETE and DROP?
DELETE removes the table, DROP removes data.
DELETE removes data, DROP removes the table structure.
There is no difference.
DELETE is DDL, DROP is DML.
Scenario: To ensure no two employees have the same EmployeeID, which constraint should be applied during CREATE TABLE?
NOT NULL
DEFAULT
PRIMARY KEY
CHECK
Application: You want to find the total revenue from a "Sales" table. Which function do you use?
COUNT(price)
MAX(price)
SUM(price)
AVG(price)
Analysis: Which data type is most appropriate for a column storing exact currency values for an e-commerce site?
REAL
VARCHAR
DECIMAL or NUMERIC
INTEGER
Scenario: You accidentally typed "Barger" instead of "Burger" in the product_name column. How do you fix this?
INSERT INTO Food_Products (product_name) VALUES ('Burger');
ALTER TABLE Food_Products SET product_name = 'Burger';
RENAME 'Barger' TO 'Burger';
UPDATE Food_Products SET product_name = 'Burger' WHERE product_name = 'Barger';
Analysis: What will be the result of SELECT COUNT(*) FROM Products; if the table is empty?
NULL
0
Error
1
Application: Which SQL statement removes all records for products with a price higher than 500?
NULLDROP FROM Products WHERE price > 500;
DELETE FROM Products WHERE price > 500;
REMOVE FROM Products WHERE price > 500;
ALTER TABLE Products DELETE price > 500;
The process of organizing data in a database to reduce redundancy and improve data integrity.
(a)
A database type that stores data in tables consisting of rows and columns, linked by defined relationships.
(a)
The subset of SQL commands used for managing data within schema objects (e.g., INSERT, UPDATE).
(a)
A function that performs a calculation on a set of values and returns a single value (e.g., SUM, AVG).
(a)
An object, person, or concept about which data is stored in a database.
(a)
The subset of SQL commands used to define or modify the database structure (e.g., CREATE, ALTER).
(a)
The interactive terminal-based command-line interface used to manage and query PostgreSQL databases.
(a)
In data modeling, this represents a characteristic or property of an entity.
(a)
A type of database that provides a mechanism for storage and retrieval of data modeled in means other than the tabular relations used in relational databases.
(a)
A unique identifier for a record in a relational database table.
(a)
PostgreSQL is an open-source object-relational database management system.
TRUE
FALSE
SELECT is the most commonly used DML command for retrieving data.
TRUE
FALSE
PostgreSQL does not support custom data types.
TRUE
FALSE
The ALTER command allows you to add or delete columns in an existing table.
TRUE
FALSE
A database is essentially an organized collection of structured information or data.
TRUE
FALSE
In a NoSQL database, data must always follow a rigid, predefined schema.
TRUE
FALSE
A "Relationship" in data modeling defines how two entities are connected or associated.
TRUE
FALSE
Aggregate functions like SUM() can be used on columns containing text data types.
TRUE
FALSE
Normalization typically involves dividing large tables into smaller, related tables.
TRUE
FALSE
The DROP command is used to remove specific rows from a table.
TRUE
FALSE
