Songs: each song has a title, duration, and genre. Title and duration are requir

Question

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 < getdate() AND

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

publishdate < getdate())

)

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

Explanation / Answer

1.
add one more Identity column i.e albumID and make albumID as primary key.

CREATE TABLE albums

(
albumID Int Identity(1,1) PRIMARY key,

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

)
or modify the table:
Alter Table albums Add albumID Int Identity(1,1)
ALTER TABLE albums ADD PRIMARY KEY (albumID);

Create one column in Songs table i.e albumID and make this as foreign key of albums table.

CREATE TABLE songs

(
albumID INT NOT NULL,

songTitle nvarchar(50) NOT NULL,

songDuration time NOT NULL,

genre varchar(10)

CHECK (Len(genre) >=3),

publishDate date

CHECK (publishDate < getdate() AND

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

publishdate < getdate()),

FOREIGN KEY(albumID) REFERENCES albums (albumID)

)
or modify the existing table:
Alter Table songs Add albumID Int NOT NULL;
ALTER TABLE songs ADD FOREIGN KEY (albumID) REFERENCES albums(albumID);

2.
insert into albums values(1,'Changes in Latitude,Changes in Attitude','Jimmy Buffet',6/1/1987,4.95,'eight track','country')

insert into albums values(2,'Barometer Soup','Jimmy Buffet',9/1/1995,8.95,'cd','pop')

insert into albums values(3,'Black Ice','AD/DC',11/1/2008,6.95,'cd','rock')

insert into albums values(4,'Pocket Full Of Kryptonite','Spin Doctors',3/1/1991,8.95,'cd','')

insert into songs values(4,'Jimmy Oslen''s Blues','4:50','rock','')

insert into songs values(4,'Little Miss Cant be Wrong','3:54','rock','')

insert into songs values(4,'Two Princes','4:17','pop/rock','')

insert into songs values(1,'Door Number 3','3:07','country','')

insert into songs values(1,'Trying to Reason with Hurricane Season','4:15','country','')

insert into songs values(1,'Life is Just a Tire Swing','3:04','country','')

insert into songs values(2,'Mexico','4:08','pop','')

insert into songs values(2,'Bank of Bad Habits','3:53','','')

insert into songs values(2,'Jimmy Dreams','3:40','','')

insert into songs values(3,'Rock''n Roll Train','4:21','Rock','')

insert into songs values(3,'Skies on Fire','3:34','','')

Related Questions

Navigate

• Browse (All)
• Browse S
• Subjects

---

---

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.