Postgres Db Developer Candidate Exercises V10 Please 435344

Postgres Db Developer Candidate Exercises V10please Provide Candidate

Provide candidates interested in our database developer position with exercises that may take up to 2 hours to complete. Candidates can submit their work to you, and you will forward it to us. Phone screens will largely focus on discussing their answers and the reasoning behind their decisions.

Paper For Above instruction

Question 1: Understanding Schema Behavior and Data Visibility in PostgreSQL

You create a table as follows:

select current_user;

DROP TABLE IF EXISTS some_user.product_lk;


CREATE TABLE some_user.product_lk (


product_id INT NOT NULL,


product_code TEXT NOT NULL


);


INSERT INTO some_user.product_lk (product_id, product_code)


SELECT a.id, 'some_user-' || abs(5 - a.id)::TEXT


FROM generate_series(1, 5) a(id);

After running this sequence successfully, attempting a SELECT yields results such as:

select * from product_lk;

product_id | product_code


------------+--------------


1           | public-4


2           | public-3


3           | public-2


4           | public-1


5           | public-0

However, the user observes that the data appears under a different schema or context, showing unexpected results. Why is this happening, and what steps can be taken to ensure that the SELECT query displays the expected rows?

Discussion:

This situation primarily relates to PostgreSQL’s schema search path behavior. In PostgreSQL, unqualified table names are resolved based on the current schema search path, which defaults to the 'public' schema unless otherwise configured. When creating the table as 'some_user.product_lk', if the current search path does not include 'some_user', then PostgreSQL will look for or display the table in the default schema ('public') or another schema, depending on configuration.

More specifically, in the above scenario, the CREATE TABLE statement specifies 'some_user.product_lk', which creates or replaces the table in the 'some_user' schema. However, the subsequent SELECT statement uses 'select * from product_lk' without specifying the schema. If 'some_user' is not included in the current search path, PostgreSQL defaults to looking for 'public.product_lk', which is either a different table or does not exist.

Additionally, the data inserted into 'some_user.product_lk' resides in the 'some_user' schema. When querying without schema qualification, PostgreSQL searches through schemas in the search path order. If the default or set search path does not include 'some_user', then the query might not access or show the data from the intended table, or it might be querying a different 'product_lk' table in another schema.

To fix this issue, candidates should ensure:

  • The schema search path is set correctly, including 'some_user'. This can be done with:
  • SET search_path TO some_user, public;
  • Or explicitly specify the schema in the SELECT statement: 'select * from some_user.product_lk'.
  • Additionally, confirm that the table exists in the intended schema before querying.

In practice, running SHOW search_path; reveals the current schema search path. Adjusting it ensures that unqualified table names resolve correctly to the desired schema. For example:

SET search_path TO some_user, public;

This will make PostgreSQL look into 'some_user' first when resolving unqualified objects, thus displaying expected results when querying 'product_lk'.

Question 2: Implementing a Numeric-Only PL/pgSQL Function

Write a PL/pgSQL function that:

  • Uses only numeric data types and functions—no VARCHAR, CHAR, TEXT, etc. (character/string functions such as REVERSE, LEFT, RIGHT, SUBSTR are not allowed).
  • Accepts an INTEGER parameter.
  • Errors out with a clear message if the value is less than zero.
  • Returns successfully with a message if the value is zero, indicating zero was passed.
  • Returns an INTEGER with the digits of the input in reverse order, ignoring trailing zeros in the input (so leading zeros in the reversed number are not included).

Solution Approach:

Since string functions are disallowed, reversing digits in a numeric context requires a mathematical approach. A common method is to repeatedly extract the last digit using modulus and construct the reversed number iteratively. Trailing zeros in the input number will be naturally removed if the number ends in zeros because dividing by 10 repeatedly will eliminate them without adding zeros at the front.

Here is the implementation:

CREATE OR REPLACE FUNCTION reverse_digits(n INTEGER)

RETURNS INTEGER AS $$


DECLARE


input_value INTEGER := n;


reversed INTEGER := 0;


last_digit INTEGER;


zero_passed BOOLEAN := FALSE;


BEGIN


-- Check for negative input


IF input_value 

RAISE EXCEPTION 'Input must be non-negative, but got %', input_value;


END IF;


-- Handle zero input specifically


IF input_value = 0 THEN


RAISE NOTICE 'Zero was passed to the function.';


RETURN 0;


END IF;


-- Loop to reverse digits


WHILE input_value > 0 LOOP


last_digit := input_value % 10;


reversed := reversed * 10 + last_digit;


input_value := input_value / 10; -- Integer division


END LOOP;


RETURN reversed;


END;


$$ LANGUAGE plpgsql;

Explanation:

  • The function first checks if the input is negative and raises an exception if so.
  • If the input is zero, it raises a notice and returns zero.
  • It then uses a while loop to extract the last digit using the modulus operator (% 10) and constructs the reversed number by multiplying the current reversed number by 10 and adding the last digit.
  • This process naturally removes trailing zeros from the input number, as zeros at the end are simply discarded when dividing and not added to the reversed number as leading zeros.

This approach adheres to the requirement of using only numeric operators and data types by avoiding string functions altogether. It also correctly handles edge cases such as zero and negative inputs, providing clear messages for each.

Question 3: Designing a Data Model for a Library System

Based solely on the description of the library operations, design a data model aimed at avoiding update anomalies. Describe the structure with primary and foreign keys, and clarify relationships between entities.

Assumptions:

  • Every book can have multiple authors.
  • Multiple copies of a single book can exist.
  • Patrons have active, non-expired library cards storing personal details.
  • Employees have an employee ID, salary, name, address, and a manager (who is also an employee).
  • Books are checked out by employees to patrons, with details about transaction date, checking employee, and the patron’s library card.
  • Fines for late returns depend on employee salary.
  • Employees can also be authors with library cards, allowing them unrestricted checkout privileges.

Proposed Data Model:

  • Entity: Book
  • BookID (PK)
  • ISBN
  • Name
  • Entity: Author
  • AuthorID (PK)
  • FirstName
  • LastName
  • Entity: Book_Author (to handle many-to-many relationship between Book and Author)
  • BookID (PK, FK to Book.BookID)
  • AuthorID (PK, FK to Author.AuthorID)
  • Entity: Copy
  • CopyID (PK)
  • BookID (FK to Book.BookID)
  • Status (Available, Checked Out, etc.)
  • Entity: Patron
  • LibraryCardID (PK)
  • FirstName
  • LastName
  • Address
  • Active (boolean or date to indicate non-expired)
  • Entity: Employee
  • EmployeeID (PK)
  • FirstName
  • LastName
  • Address
  • Salary
  • ManagerID (FK to Employee.EmployeeID)
  • Entity: Loan
  • LoanID (PK)
  • CopyID (FK to Copy.CopyID)
  • PatronID (FK to Patron.LibraryCardID)
  • EmployeeID (FK to Employee.EmployeeID)
  • CheckoutDate
  • DueDate
  • ReturnDate

This model eliminates update anomalies by normalizing data and ensuring each entity has a correct primary key, with relationships enforced via foreign keys. The Book and Author entities are linked through a junction table, allowing multiple authors per book and vice versa. Copies of books are stored in a separate table to track individual units, avoiding anomaly issues associated with multiple copies. Patrons and employees are represented separately, with employee-manager relationships maintained via a self-referential foreign key. The Loan entity captures all transaction details, maintaining referential integrity.

Question 4: SQL Query for Temperature Data Analysis

Create a query that retrieves days when temperatures were higher than the average for their ZIP code, based on a table ZIP_TEMPS with columns:

  • ZIP_CODE (PK)
  • MEASUREMENT_DATE (PK)
  • NOON_TEMP

Sample data:

ZIP_CODE | MEASUREMENT_DATE | NOON_TEMP

---------+------------------+----------


1        | 2023-01-01       | 30


1        | 2023-01-02       | 28


1        | 2023-01-03       | 35


2        | 2023-01-01       | 40


2        | 2023-01-02       | 42


2        | 2023-01-03       | 38


3        | 2023-01-01       | 25


3        | 2023-01-02       | 27


3        | 2023-01-03       | 26

The expected output should list each ZIP_CODE and MEASUREMENT_DATE where NOON_TEMP exceeds the average noon temperature for that ZIP code.

Solution:

SELECT z1.ZIP_CODE, z1.MEASUREMENT_DATE, z1.NOON_TEMP

FROM ZIP_TEMPS z1


JOIN (


SELECT ZIP_CODE, AVG(NOON_TEMP) AS avg_temp


FROM ZIP_TEMPS


GROUP BY ZIP_CODE


) z2


ON z1.ZIP_CODE = z2.ZIP_CODE


WHERE z1.NOON_TEMP > z2.avg_temp;

This query joins the table with a subquery that computes the average temperature per ZIP code. It then filters for days where the noon temperature exceeds this average, effectively highlighting hotter-than-average days per ZIP code.

References

  • PostgreSQL Documentation. (2024). SQL Language. https://www.postgresql.org/docs/current/sql.html
  • Fowler, M. (2018). Patterns of Enterprise Application Architecture. Addison-Wesley.

Related Assignments