Specific Subject: Database System Write a (PostgreSQL) SQL script creating the f

Question

Specific Subject: Database System

Write a (PostgreSQL) SQL script creating the following elements:

1) A table ALBUMS as well as an index table theat contains album id and individual words in a title. Don't forget constraints.

The table can be similar to this:

2) A user-written function that sanitizes a title, removing punctuation and replacing accented letters with their unaccented equivalent, and finally turning everything to the same case (either upper or lower case). Translate() function would be a possible solution.

3) A function that will take 2 parameters, an album id and a title, and will populate the table containing album id and words. This function will ignore one-letter words.

4) A trigger on ALBUMS table that uses the preceding function to automatically index titles when they are inserted or updated (updates will delete and replace words associated with the previous version of the title).

Explanation / Answer

(1).
CREATE TABLE ALBUMS(
album_id int,
title varchar(100),
released varchar(20),
PRIMARY KEY( album_id, title)
);

CREATE INDEX ALBUMS_INDEX ON ALBUMS (album_id, title);

(2).
CREATE FUNCTION Translate(text varchar)
RETURNS text IMMUTABLE STRICT LANGUAGE SQL AS $$
BEGIN
SELECT translate(
text,
'âãäåÁÂÃÄÅèééêëìíîïìÌÍÎÏÌóôõöÒÓÔÕÖùúûüÙÚÛÜ',
'aaaaaaaaaaaaaaaeeeeeeeeeeeeeeeiiiiiiiiiiiiiiiiooooooooooooooouuuuuuuuuuuuuuuu'
);

SELECT regexp_replace(text, @"[^ws]", "");

END; LANGUAGE 'plpgsql';

(3).
CREATE FUNCTION insertAlbums(album_id integer, title varchar)
RETURNS void AS
BEGIN
INSERT into ALBUMS(album_id, 'title') values(album_id, title);
END; LANGUAGE 'plpgsql';

(4).
CREATE TRIGGER REFRESH_INDEX AFTER INSERT ON DBNAME.ALBUMS
BEGIN
   REINDEX INDEX ALBUMS_INDEX;
END;

Related Questions

Navigate

• Browse (All)
• Browse S
• Subjects

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.