DB schema for the exercise ChordChord ID Name

DB schema for the exercise Chord(Chord. ID, Name, S 1, S 2, S 3, S 4, S 5, S 6) Song(Song. ID, Name) Chords(ID, Time, Song. ID, Chord. ID) Attributes S 1 -S 6 in table Chord represent strings (e. g. , S 1 = 0 means the first string is free) Table Chords models position of fixed chords in a song (transposition is not considered) RA/NRK means query in relational algebra/calculus 1

Chord(Chord. ID, Name, S 1, S 2, S 3, S 4, S 5, S 6) Song(Song. ID, Name) Chords(ID, Time, Song. ID, Chord. ID) Q example: Chord names with the first string free (S 1 = 0)? RA: Chord(S 1 = 0)[Name] TRC: {r[Name]|Chord(r) & r. S 1 = 0} 2

Chord(Chord. ID, Name, S 1, S 2, S 3, S 4, S 5, S 6) Song(Song. ID, Name) Chords(ID, Time, Song. ID, Chord. ID) Q example: Chord names that appear in time 1 s? RA: (Chord * Chords(Time = 1))[Name] without natural join (Chord[Chord. ID=Chords. Chord. ID]Chords) (Time = 1)[Name] TRC: {r[Name]|Chord(r) & ∃s(Chords(s) & s. Chord. ID = r. Chord. ID & s. Time = 1)} 3

Chord(Chord. ID, Name, S 1, S 2, S 3, S 4, S 5, S 6) Song(Song. ID, Name) Chords(ID, Time, Song. ID, Chord. ID) Q example: Chords with the same name as some song? RA: (Chord * Song)[Name] TRC: {r[Name]|Chord(r) & ∃s(Song(s) & s. Name = r. Name)} 4

Chord(Chord. ID, Name, S 1, S 2, S 3, S 4, S 5, S 6) Song(Song. ID, Name) Chords(ID, Time, Song. ID, Chord. ID) Q example: Chords with a different name from any song? RA: Chord[Name] - Song[Name] TRC: {r[Name]|Chord(r) & ∄s(Song(s) & s. Name = r. Name)} 5

Chord(Chord. ID, Name, S 1, S 2, S 3, S 4, S 5, S 6) Song(Song. ID, Name) Chords(ID, Time, Song. ID, Chord. ID) Q example: Names of songs with chord C dur? RA: CS = (Chord(Name = ‘C dur’)[Chord. ID] * Chords * Song)[Name] TRC: {s[Name]|Song(s) & ∃r, c(Chord(r) & Chords(c) & s. Song. ID = c. Song. ID & c. Chord. ID = r. Chord. ID & r. Name = ‘C dur’)} 6

Chord(Chord. ID, Name, S 1, S 2, S 3, S 4, S 5, S 6) Song(Song. ID, Name) Chords(ID, Time, Song. ID, Chord. ID) Q example: Names of songs without chord C dur? RA: Song[Name] - (Chord(Name = ‘C dur’)[Chord. ID] * Chords * Song)[Name] Extra question: is this solution correct - and why? TRC: {s[Name]|Song(s) & ∄r, c(Chord(r) & Chords(c) & s. Song. ID = c. Song. ID & c. Chord. ID = r. Chord. ID & r. Name = ‘C dur’)} 7

Chord(Chord. ID, Name, S 1, S 2, S 3, S 4, S 5, S 6) Song(Song. ID, Name) Chords(ID, Time, Song. ID, Chord. ID) Q 5: Names of songs with ONLY chord C dur? Hint: remove from CS songs using other chords RA: TRC: 8

Chord(Chord. ID, Name, S 1, S 2, S 3, S 4, S 5, S 6) Song(Song. ID, Name) Chords(ID, Time, Song. ID, Chord. ID) Q 6: Different songs with the same name? Hint: rename attribute(s) in Song & self join RA: TRC: 9

Chord(Chord. ID, Name, S 1, S 2, S 3, S 4, S 5, S 6) Song(Song. ID, Name) Chords(ID, Time, Song. ID, Chord. ID) Q 8: Chords that appear in all songs? Hint: study the division operator… RA: TRC: 10

Chord(Chord. ID, Name, S 1, S 2, S 3, S 4, S 5, S 6) Song(Song. ID, Name) Chords(ID, Time, Song. ID, Chord. ID) Q 9: Songs with just one chord? Hint: remove songs with more (different) chords RA: TRC: 11

Chord(Chord. ID, Name, S 1, S 2, S 3, S 4, S 5, S 6) Song(Song. ID, Name) Chords(ID, Time, Song. ID, Chord. ID) Q 10: Songs without chords where all Si > 0? RA: TRC: 12