Songs: each song has a title, duration, and genre. Title and duration are required; genre is not.

Publish date has to be before today, and after the first day of the current year. Genre must be at

least 3 characters.

Albums: each album has a title, artist, release date, price, format, and genre. All fields except

genre are required. Price can't be negative. Format must be record, cd, or eight track. Release date

can be any date after 1/1/1931 if the format is record; any date after 1/1/1968 if the format is eight

track; and any date after 1/1/1979 if it's a cd.

*/

-- create and use database

CREATE DATABASE music

GO

USE music

-- create songs

CREATE TABLE songs

(

songTitle nvarchar(50) NOT NULL,

songDuration time NOT NULL,

genre varchar(10)

CHECK (Len(genre) >=3),

publishDate date

CHECK (publishDate

(publishdate > datefromparts(datepart(yyyy,getdate()),1,1) AND

publishdate

)

CREATE TABLE albums

(

albumTitle nvarchar(50) NOT NULL,

artist nvarchar(100) NOT NULL,

releaseDate date NOT NULL,

price smallmoney NOT NULL,

albumFormat varchar(10) NOT NULL

CHECK (albumFormat IN ('cd','record','eight track')),

genre varchar(10),

CHECK ((releaseDate > '1/1/1931' AND albumFormat = 'record') OR

(releaseDate > '1/1/1968' AND albumFormat = 'eight track') OR

releaseDate > '1/1/1979' AND albumFormat = 'cd'))

)

SQL ( EXERCISE): USE THE SOLUTION ABOVE AND MAKE THE FOLLOWING CHANGES:

* Each song belongs to a single album (one album has many songs). Modify the database to implement this relationship using identity columns for any primary key(s).

* Add the following entries in the two table below to the database:

Album # itle st Release Date Price Format enre 6/1/1987 95 8 track country anges in pimmy Latitude, Buffet hanges in titude /1/1995 8.95 cd Barometer immy Buffett pop oup Black Ice D/DC 11/1/2008 6.95 cd 1/1991 8.95 Pocket Fu Spin of Kryptonite cd Doctors