DSC 100: Database Management Practice Problems

Table of Contents

1. Relational Data Model and Design
2. SQL Fundamentals
3. Joins and Advanced Queries
4. Indexes and Query Optimization
5. Normalization and Dependencies
6. ER Diagrams

Relational Data Model

Problem 1 🟢 (Multiple Choice)

Which statement about relations is TRUE?

a) Relations must have at least one tuple

b) The order of tuples in a relation matters

c) All attributes must be of different types

d) A relation is a set of tuples with the same attributes

Problem 2 🟡 (Multiple Choice)

Given a relation R(A,B,C,D) with functional dependencies A→B and B→C, what is TRUE?

a) A is a superkey

b) {A,D} is not a superkey

c) B must be a primary key

d) A→C by transitivity

Problem 3 🟢

Given the following relation:

Student(id, name, major, advisor_id, department)

What is the difference between a candidate key and a superkey in this context? Note: id is considered a business key (auto incrementing id)

Problem 4 🟡

Given these functional dependencies:

A → B
BC → D
D → E

Find all the attributes that are transitively dependent on A.

Problem 5 🟢 (Multiple Choice)

Which of the following is NOT an atomic data type in SQL?

a) INTEGER

b) VARCHAR

c) ARRAY

d) DATE

Problem 6 🟡

Given this table structure:

CREATE TABLE Orders (
    order_id INTEGER PRIMARY KEY,
    customer_id INTEGER,
    order_date DATE,
    total_amount DECIMAL(10,2),
    status VARCHAR(20)
);

Write all the domain constraints that are implicitly enforced by this schema.

Problem 7 🟣

Consider the following query:

SELECT department, COUNT(*) as emp_count
FROM Employee
GROUP BY department
HAVING COUNT(*) > (
    SELECT AVG(dept_count)
    FROM (
        SELECT COUNT(*) as dept_count
        FROM Employee
        GROUP BY department
    ) as dept_sizes
);

Explain step by step how this query is evaluated by the database engine.

Problem 8 🟡 (Multiple Choice)

Which statement about NULL values in SQL is correct?

a) NULL = NULL evaluates to TRUE

b) NULL values are considered equal to each other

c) NULL values are ignored in COUNT(*)

d) NULL values are excluded from COUNT(column_name)

Problem 9 🟡

Write a query using window functions to rank employees by salary within each department. Display the following:

• Employee name
• Department
• Salary
• Rank within department

SELECT 
    name,
    department,
    salary,
    RANK() OVER (PARTITION BY department ORDER BY salary DESC) as dept_rank
FROM Employee;

Joins and Advanced Queries

Problem 10 🟣

Given tables:

Movies(movie_id, title, release_year)
Actors(actor_id, name)
Roles(movie_id, actor_id, character_name)

Write a query to find all pairs of actors who have appeared in at least 3 movies together.

WITH ActorPairs AS (
    SELECT 
        r1.actor_id as actor1_id,
        r2.actor_id as actor2_id,
        COUNT(DISTINCT r1.movie_id) as movies_together
    FROM Roles r1
    JOIN Roles r2 ON r1.movie_id = r2.movie_id
    WHERE r1.actor_id < r2.actor_id
    GROUP BY r1.actor_id, r2.actor_id
    HAVING COUNT(DISTINCT r1.movie_id) >= 3
)
SELECT 
    a1.name as actor1_name,
    a2.name as actor2_name,
    ap.movies_together
FROM ActorPairs ap
JOIN Actors a1 ON ap.actor1_id = a1.actor_id
JOIN Actors a2 ON ap.actor2_id = a2.actor_id;

Indexes and Optimization

Problem 11 🟡 (Multiple Choice)

When is a non-clustered index LEAST useful?

a) When querying a small percentage of rows

b) When the table is frequently updated

c) When performing range queries

d) When doing full table scans

Normalization

Problem 12 🟣

Given the relation:

StudentCourse(student_id, student_name, course_id, course_name, instructor_id, instructor_name, department)

With functional dependencies:

• student_id → student_name
• course_id → course_name, instructor_id
• instructor_id → instructor_name, department

Decompose into BCNF. Show work step by step.

     Student(student_id, student_name)
     Course(course_id, course_name, instructor_id)
     Instructor(instructor_id, instructor_name, department)
     Enrollment(student_id, course_id)
    

ER Diagrams

Problem 13 🟣

Design an ER diagram using Mermaid syntax for a Library Management System with these requirements:

• Library has multiple branches
• Each branch has books and employees
• Books can exist in multiple copies
• Members can borrow books
• Members can have fines
• Employees can be librarians or managers

erDiagram

    Library ||--|{ Branch : has
    Branch ||--|{ Book : contains
    Branch ||--|{ Employee : employs
    Book ||--|{ Copy : consists-of
    Member ||--|{ Borrow : borrows
    Borrow ||--|| Copy : of
    Member ||--|{ Fine : has

    Library {
        string library_id PK
        string name
    }

    Branch {
        string branch_id PK
        string address
        string library_id FK
    }

    Book {
        string book_id PK
        string title
        string author
    }

    Copy {
        string copy_id PK
        string book_id FK
        string branch_id FK
        string status
    }

    Member {
        string member_id PK
        string name
        string address
    }

    Borrow {
        string member_id FK
        string copy_id FK
        datetime borrow_date
        datetime return_date
    }

    Fine {
        string fine_id PK
        string member_id FK
        float amount
        datetime date
    }

    Employee {
        string employee_id PK
        string branch_id FK
        string name
        string role
        string title "takes either librarian or manager"
    }

Problem 14 🟡 (Multiple Choice)

In an ER diagram, what does a double-line (thick line) relationship indicate?

a) Many-to-many relationship

b) Total participation

c) Weak entity relationship

d) Inheritance relationship

Single line ──── : Partial participation
Double line ════ : Total participation
Single rectangle └─┐ : Strong entity
Double rectangle └═┐ : Weak entity