Database Questions

1. Schemas

I create a table as follows:

[local] some_user@some_user=> select current_user;

current_user

--------------

some_user

(1 row)

[local] some_user@some_user=> DROP TABLE IF EXISTS some_user.product_lk;

DROP TABLE

[local] some_user@some_user=>

[local] some_user@some_user=> CREATE TABLE some_user.product_lk

[more] - > (

[more] ( > product_id INT NOT NULL,

[more] ( > product_code TEXT NOT NULL

[more] ( > );

CREATE TABLE

[local] some_user@some_user=>

[local] some_user@some_user=> INSERT INTO some_user.product_lk (product_id, product_code)

[more] - > SELECT a.id, 'some_user-' || abs(5 - a.id)::TEXT

[more] - > FROM generate_series(1, 5) a(id);

INSERT 0 5

After this runs successfully, I notice that when I try and do a select I am not seeing the results I expect. Instead I see the following:

[local] some_user@some_user=> select current_user;

current_user

--------------

some_user

(1 row)

[local] some_user@some_user=> select * from product_lk;

product_id | product_code

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

1 | public-4

2 | public-3

3 | public-2

4 | public-1

5 | public-0

(5 rows)

Can you tell me why I am seeing the results I am seeing and what I could do to have that SQL statement return the rows I expect?

2. Procedural Logic and pl/pgsql

Write a pl/pgsql function that:

1. Uses only numeric datatypes and functions—no VARCHAR, CHAR, TEXT, etc.

· In other words, character/string functions such as REVERSE, LEFT, RIGHT, SUBSTR, etc are not to be used in your solution.

2. Accepts a INTEGER parameter

3. If the provided value is less than zero the function should error out with a good error message

4. If the provided value is zero the function should return successfully but report back to the client a message indicating zero was passed

5. Returns a INTEGER value that has the digits from the input parameter in reverse order. If the input value ends in one or more zeroes those zeroes will not appear in the returned numeric value, since they would be leading zeroes

3. Data Model

Based solely on the description of library operations below (without making up any additional facts/requirements) design a data model that avoids update anomalies. You can use text to describe your model if you want. If so be clear as to what columns have primary and foreign keys, as well as what table a FK refers to. You can sketch it out on paper and scan it. You can use Visio. You can use some other tool if you can export it as a PDF.

If you have to make assumptions to complete the exercise, list the assumptions you are making.