Write a PostgreSQL script that contains the following elements. 1) A table ALBUM

Question

Write a PostgreSQL script that contains the following elements.

1) A table ALBUMS of your own, as well as a word index table that contains album id and individual words in a title. Be careful not to forget the necessary constraints.

2) A function that sanitizes a title, removing punctuation and turning everything to the same case (your choice: upper or lower). You’ll probably want to use the translate() PostgreSQL function in your solution, and the following list for punctuation: #@&"''(!)-_$*%£<>[{]}°,?;.:/=+

3) A function that takes two parameters, an album id and a title, and populates the index table containing album id and words (after sanitizing the title). Your function will ignore one-letter words.

Explanation / Answer

1)
create table ALBUMS
(
id int not null,
title varchar(64) not null,
author varchar(64) not null,
primary key(id)
);

create table Word_index
(
album_id int not null,
title varchar(64) not null,
genre varchar(24) not null,
primary key(id),
foreign key(album_id) reference ALBUMS(ID)
);

Related Questions

Navigate

• Browse (All)
• Browse W
• Subjects

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