Question

create table books (title text primary key,pubdate text,publisher text,publishercity text,scope text);
create table publishers (publisherName text,founded date,founderName text[],headquarters text);
insert into books values
        ('the gremlins', 1943, 'random house', 'new york', 'children'),
        ('sometime never: a fable for supermen', 1948, 'charles scribner''s sons', 'new york', 'adult'),
        ('james and the giant peach', 1961, 'alfred a. knopf', 'new york', 'children'),
        ('charlie and the chocolate factory', 1964, 'alfred a. knopf', 'new york', 'children')
      
 insert into publishers(publisherName, founded, founderName, headquarters) values
        ('random house', 'Jan 1, 1927', '{bennett cerf, donald klopfer}'::Text[], 'new york City'),
        ('charles scribner''s sons', 'Jan 1, 1846', '{charles scribner i, isaac d. baker}'::Text[], 'new york'),
        ('alfred a. knopf', 'Jan 1, 1915', '{alfred a. knopf sr.}'::Text[], 'new york'),
        ('jonathan cape', 'Jan 1, 1921', '{herbert jonathan cape, wren howard}'::text[], 'london'),
     
select ('[' || 
(select string_agg(
'{' ||
        '"title" : "' || title || '", ' ||
        '"pubyear" : ' || pubdate || ', ' ||
        '"publisher" : "' || publisher || '", ' ||
        '"publishercity" : "' || publishercity || '", ' ||
        '"scope" : "' || scope || '", ' ||
        '"publisherName" : "' || publisherName || '", ' ||
        '"founded" : "' || founded || '", ' ||
        '"headquarters" : "' || headquarters || '", ' ||
        '"founders" : ' || substring('' ||json_build_array(founderName), 2, char_length('' ||json_build_array(founderName)) - 2) 
|| '}'
, ',') from books left outer join publishers on publisher = publishername) || ']')::jsonb 
into jsonbooks;

Do not use the books table or the publishers table in any of the queries you write. Queries which use either of these two tables will not receive credit. You must use the jsonbooks table in your queries.

1. Explain how this data is denormalized (In other words: the table where each row is it’s own book is not in 3NF. Why?)

2. Write a query that returns the titles of all books written before 1980.

Thanks!

Answer 1

This data is not in 3NF because it is having transitive dependencies, that is if a non- key attribute then there is a change in other non- key attributes. In the data given if non -key attribute like headquarters text change then corresponding publisher details may also change and this contradicts the property of 3NF that there should not be any transition dependencies. This data is also not in 2NF where only single column primary key should be there. Title cannot be the primary key as there may be two different books with same titles.

QUERY:

select title

from jsonbooks

where year ( founded ) < 1980 ;