Why is storing artist information (name, country, bio) directly in the songs table considered bad practice?

Data duplication leads to update anomalies — changing an artist's name requires updating hundreds of song rows. Relationships solve this by storing the artist once and referencing it.

What is the primary purpose of a foreign key in database design?

Foreign keys establish relationships between tables and, combined with constraints, ensure referential integrity by preventing orphaned rows.

In the context of normalization, what is the main benefit of splitting a flat table into multiple related tables?

Normalization removes data duplication, making the database smaller and easier to maintain. Updates only need to happen in one place.

In a one-to-many relationship, which table holds the foreign key?

The many-side holds the foreign key. Each song row references one artist via artist_id, establishing the one-to-many link.

Which of the following is a valid one-to-many relationship?

One-to-many means the parent (customer) can have multiple children (orders), but each child belongs to only one parent.

What would be the correct SQL statement to create a one-to-many relationship between users and orders?

The many-side (orders) has a foreign key (user_id) referencing the one-side (users). The FK should not be UNIQUE so multiple orders can share a user_id.

In a one-to-many relationship, can a child row exist without a parent row?

If the foreign key column is defined as nullable (no NOT NULL constraint), a child can have NULL in its FK, orphaning it. To require a parent, use NOT NULL.

Why can't you represent a many-to-many relationship using only two tables with a foreign key in one of them?

You cannot store multiple FK values (like '1,2,3') in a single column — that violates relational database design. A junction table solves this.

Which of the following is a many-to-many relationship?

Many-to-many means both sides have cardinality of many. A song is in multiple playlists, and a playlist has multiple songs.

What is the minimum number of columns required in a junction table?

A junction table needs exactly two foreign key columns to represent the relationship. These can form a composite primary key (no additional ID column required).

In a junction table for playlist_tracks, what does a 'position' column represent?

Payload columns like 'position' store additional data about the many-to-many relationship itself, not just the connection between two tables.

How should the primary key of a junction table typically be defined?

A composite primary key (playlist_id, track_id) ensures no duplicate relationships. Each combination can appear only once.

Which SQL statement correctly creates a junction table for student-course enrollment?

Option A uses a composite primary key and explicitly declares two foreign keys. This prevents duplicates and enforces referential integrity.

What happens when you try to insert a row with a foreign key value that doesn't exist in the parent table?

Referential integrity constraints prevent inserting orphaned rows. The database rejects the insert if the referenced parent does not exist.

What is referential integrity?

Referential integrity ensures relationships remain valid — every FK points to an existing PK, preventing orphaned data.

Can a foreign key column accept NULL values?

A nullable foreign key allows orphaned rows (no parent). If you want to enforce a parent, use NOT NULL on the FK column.

What does ON DELETE CASCADE do?

CASCADE automatically deletes all child rows when a parent is deleted. Use this for dependent data like cart items.

You have a users table and a comments table. Comments should be deleted when users are deleted. Which ON DELETE option should you use?

CASCADE is appropriate here — comments are meaningless without their user. Deleting the user should cascade and delete all their comments.

What does ON DELETE RESTRICT do?

RESTRICT blocks the deletion with an error. Use this for critical parent data like products or artists where deletion should be prevented.

You have an albums table and a tracks table. Tracks reference albums. If you delete an album, you want the tracks to remain but lose their album reference. Which option?

SET NULL keeps the tracks but sets their album_id to NULL, orphaning them. This is appropriate when children can exist without a parent.

What is a potential risk of using ON DELETE CASCADE on critical parent tables?

CASCADE can trigger a chain reaction deleting deep hierarchies of data. A single delete statement could remove thousands of rows unintentionally.

If you declare ON DELETE SET NULL on a foreign key, what must you ensure about that column?

SET NULL only works if the FK column is nullable. If it's NOT NULL, the database will reject the delete operation.

In the music streaming schema, why is the relationship between playlists and tracks many-to-many?

A song can appear in multiple playlists (e.g., 'Blinding Lights' in both 'Chill Vibes' and 'Workout Mix'), and each playlist has multiple songs.

In the music streaming design, which ON DELETE option is used for playlists when a user is deleted?

CASCADE is used — when a user is deleted, all their playlists should be deleted too, as playlists are user-owned resources.

Why is the album_id foreign key in the tracks table nullable?

Not all tracks belong to albums. Single releases and compilations might exist without album context, so album_id can be NULL.

In the music streaming schema, what does ON DELETE CASCADE on the playlist_id foreign key in playlist_tracks accomplish?

When a playlist is deleted, all rows in playlist_tracks that reference it are deleted, cleaning up the many-to-many association.

Why is ON DELETE RESTRICT used for the artist_id foreign key in the albums table?

RESTRICT protects critical data. Preventing artist deletion if they have albums ensures you don't lose discography information.

How would you query all tracks in a specific user's playlist?

You must join tracks with the junction table (playlist_tracks) to retrieve the actual track data for a playlist.

In the user_liked_tracks junction table, what does the composite primary key (user_id, track_id) prevent?

The composite PK ensures uniqueness — a user cannot like the same track twice. Trying to insert a duplicate pair would violate the constraint.

If you want to cascade deletes for all relationships in the music streaming schema, which relationships would be dangerous to cascade?

Cascading deletes on artist would wipe entire discographies. It's safer to RESTRICT artist deletion or use soft deletes to preserve historical data.

Database Relationships and Foreign Keys — System Design Concept

A relationship describes how rows in one table connect to rows in another table. Instead of duplicating data everywhere, we split information into separate tables and then link them together using special columns called foreign keys.

Why not just put everything in one table?

Imagine storing every song along with the full artist name, artist country, and artist bio repeated for every single song. If the artist changes their name, you'd need to update thousands of rows. Relationships solve this by storing the artist once and referencing them by ID.

BAD (flat table — data duplication): ┌─────────────────────────────────────────────────────────────┐ │ song_id │ song_title │ artist_name │ artist_country │ bio │ │ 1 │ Blinding Lights│ The Weeknd │ Canada │ ... │ │ 2 │ Starboy │ The Weeknd │ Canada │ ... │ │ 3 │ Shape of You │ Ed Sheeran │ UK │ ... │ └─────────────────────────────────────────────────────────────┘

GOOD (normalized — linked tables): artists table: songs table: ┌────┬────────────┐ ┌────┬─────────────────┬───────────┐ │ id │ name │ │ id │ title │ artist_id │ │ 1 │ The Weeknd │ │ 1 │ Blinding Lights │ 1 │ │ 2 │ Ed Sheeran │ │ 2 │ Starboy │ 1 │ └────┴────────────┘ │ 3 │ Shape of You │ 2 │ └────┴─────────────────┴───────────┘