TOPIC IS Design Methodology/ DATABASE systems Homework 5 – DDL Using the descrip
ID: 3805544 • Letter: T
Question
TOPIC IS Design Methodology/ DATABASE systems
Homework 5 – DDL
Using the description below write the DDL to create the definitions necessary.
Modify the above DDL to be able to show all of the albums that have a specific song on it.
ALBUMS
Cat#
Title
Artist #
Music Category
Record Label
EK-38308
Greatest Hits
25
Rock
Full Moon
LPM-2256
GI Blues
10
Rock
Epic
LSP-246
Blue Hawaii
10
Rock
Epic
M16014
Surfin Safari
20
Rock
RCA
7499-2256
Between the Buttons
15
Rock
Capitol
945024-2
Unplugged
30
Rock
Reprise
ARTISTS
Artist#
Artist Name
Yr. Born
Yr. Died
10
Elvis Presley
1935
1977
15
Rolling Stones
1943
20
Beach Boys
1942
25
Dan Fogelberg
1951
30
Eric Clapton
1945
TRACKS
Cat#
Track#
TrackTitle
TrackLength
TrackSample
EK-38308
1
Part of the Plan
3:30
partplan.mp3
EK-38308
2
Heart Hotel
6:00
hearthotel.mp3
EK-38308
3
Hard to Say
5:33
hardtosay.mp3
LPM-2256
1
Blue Suede Shoes
1:04
bluesuede.mp3
LPM-2256
2
Franfort Special
4:33
franfspec.mp3
LPM-2256
3
Wooden Heart
3:45
woodheart.mp3
7499-2256
1
RubyTuesday
4:33
rubytues.mp3
7499-2256
2
Power of Gold
3:44
powergold.mp3
945024-2
1
hey hey
5:33
heyhey.mp3
945024-2
2
layla
6:30
layla.mp3
945024-2
5
alberta
3:33
alberta.mp3
PLEASE DO NOT ANSWER THE QUESTION LIKE THIS:
For retrieving all the albums which have a particular track in them, i am assuming below:
1. Table names are ALBUMS and TRACKS
2. Tracks table Cat# is related with Albums table through column Cat# in Albums table.
To achieve the above, we will first try to find all the categories which contains this track.
Query for that would be: select Cat from TRACKS where TrackTitle like '%<Give track title>%'
Now based on these Categories, we will search on Albums table, and whoever album has the same category, that will be the part of the result.
Hence overall Query:
select * from ALBUMS where Cat in (
select Cat from TRACKS where TrackTitle like '%<Give track title>%'
)
THE ABOVE ANSWER IS NOT WHAT I AM LOOKING FOR. I NEED THE ANSWER TO LOOK LIKE THIS BUT INCLUDE THE WORK FROM THE BEGINING TO THE FINAL RESULT. I NEED THE STEPS OF HOW YOU GOT THE ANSWER. ONE EXAMPLE OF WHAT I AM LOOKING FOR IS THIS BELOW:
Topic is Design Methodology / DataBase systems
Homework 5 – Extra Credit
The following is to add to the PineValley Database to satisfy the following:
Add vendors for products.
Vendors should contain vendor#, vendorname, tel#, leadtime, price.
Products may have many vendors. Vendors have many products
THE ANSWER IS:
);
PLEASE DO NOT ANSWER MY QUESTION AND WASTE MY QUESTION OR MY TIME IF YOU CAN NOT DO IT THE WAY THE INSTURTIONS ASK THEN DONT DO IT!!!
THESE ARE THE INSTRUCTIONS AGAIN:
Using the description below write the DDL to create the definitions necessary.
Modify the above DDL to be able to show all of the albums that have a specific song on it.
Cat#
Title
Artist #
Music Category
Record Label
EK-38308
Greatest Hits
25
Rock
Full Moon
LPM-2256
GI Blues
10
Rock
Epic
LSP-246
Blue Hawaii
10
Rock
Epic
M16014
Surfin Safari
20
Rock
RCA
7499-2256
Between the Buttons
15
Rock
Capitol
945024-2
Unplugged
30
Rock
Reprise
Explanation / Answer
The main objective is to display all the albums with the entire information where the album has a speific song.
So, we would need the ALBUMS and the TRACKS table.
ALBUMS table contains CAT# (PRIMARY KEY), Title, Artist#, Music_Category, Record_Label
TRACKS table contains CAT# (FOREIGN KEY to CAT# in ALBUMS table), Track#, TrackTitle, TrackLength, TrackSample.
ow, an Album may have many Tracks so we need to intersect the ALBUMS table with the TRACKS table.
SO, that when we are finding for a particular Track we get the entire Album details.
We would be using sub-queries to get to the final result which you shared.
The column CAT# is a primary key in ALBUMS table and CAT# is a foreign key in TRACKS table so we would be using the column CAT# to intersect the 2 tables.
In the first part we would be selecting the Category (CAT# column) from TRACKS and filter it by the TrackTitle which you want to see.
1st Query - select CAT# from TRACKS where TrackTilte like 'your_chosen_song';
By this query we would be getting the Unique Categories of the tracks whose album you want to see.
In the Final we would print all the album details for the particular category of tracks which you have chosen.
Final Query :- select * from ALBUMS where CAT# in (select CAT# from TRACKS where TrackTilte like 'your_chosen_song';) ;
Related Questions
Navigate
Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.