Answered step by step
Verified Expert Solution
Link Copied!

Question

1 Approved Answer

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

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:

image text in transcribed

image text in transcribed

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

Step by Step Solution

There are 3 Steps involved in it

Step: 1

blur-text-image

Get Instant Access to Expert-Tailored Solutions

See step-by-step solutions with expert insights and AI powered tools for academic success

Step: 2

blur-text-image

Step: 3

blur-text-image

Ace Your Homework with AI

Get the answers you need in no time with our AI-driven, step-by-step assistance

Get Started

Recommended Textbook for

Machine Learning And Knowledge Discovery In Databases European Conference Ecml Pkdd 2015 Porto Portugal September 7 11 2015 Proceedings Part 2 Lnai 9285

Authors: Annalisa Appice ,Pedro Pereira Rodrigues ,Vitor Santos Costa ,Joao Gama ,Alipio Jorge ,Carlos Soares

1st Edition

3319235249, 978-3319235240

More Books

Students also viewed these Databases questions