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 '%%'

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 '%%' )

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:

create table vendor( vendor_id integer primary key ,vendor_no integer ,vendor_name varchar(50) ,vendor_tel varchar(15) ,leadtime varchar(20) ,price varchar(20) ,product_no integer 

);

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.