Question

Consider the following SQL code to generate a table for storing data about a music library. CREATE TABLE playlists ( id INTEGER PRIMARY KEY AUTOINCREMENT, name TEXT ); CREATE TABLE songs ( id INTEGER PRIMARY KEY AUTOINCREMENT, title TEXT, artist TEXT, album TEXT, year NUMERIC, playlist_id INTEGER, FOREIGN KEY(playlist_id) REFERENCES playlists(id) ); Critique the design of this database, as by proposing and explaining at least two ways in which its design could be improved. Hint: Might songs end up with (lots of!) duplicate values in some columns?

Answer 1

Answer:

1. The size of the text fields not mentioned. How long is the song? 100 characters? 10000 characters?

2. Song table has the playlist id in it. It means playlist is connected to the song, not the other way

Lets say song "abc" exists in 2 play lists "alphabets" and "nursery rhymes"

The song table will look like this

01 - "abc" - "alphabets"

02 - "abc" - "nursery rhymes"

If you asked me how many songs you had in the table, I cannot answer that directly.

The best way is to have three tables

Songs table (contains information about the song)

playlist table (contains information about the playlist)

song_playlist table - that ties the song id and playlist id

Explanation:

Answer 2

Answer:

Explanation:

557629