creating tables using t-sql

Hi,

What did you have in mind for hte artist ID and Album ID?

My first thought would be to make these identity columns. You'll need to look up Identity in SQL Server Books On Line.

A couple of minor things
song.duration has no type.
artist.artist_id has no type.

I would suggest that songs are on albums, and each song can have multiple artists. Each song is one creative act. That is, the same tune can be recorded by more than one artist or group of artists on different albums, but maybe that is going a little far at this stage.

create table album
    (
    AlbumID Int Not Null identity ( 1, 1 )
    , album_name VarChar(50) Not Null
    )

create table song
    (
    SongID int not null identity ( 1, 1 )
    , AlbumID int not null
    , song_name VarChar(50) Not Null
    , duration
    , genre VarChar(50) Not Null
    )

create table artist
    (
    ArtistID int not null identity ( 1, 1 )
    , artist_name VarChar(50) Not Null
    )

create table dbo.SongArtist
    (
    SongArtistID int not null identity ( 1, 1 )
    , SongID int not null
    , ArtistID int not null
    )

HTH
  David

PS I noted that you've posted this against SQL 2008. There are some new date-time types. I don't recall if duration - the difference between two datetimes - is among them or not. Depending on your musical taste, this would be better than int, as it woudl be easier to format hours:minutes:seconds instead of storing duration as a large number of seconds.

And I've just noted that I haven't given duration a type above. It should be
create table song
    (
    SongID int not null identity ( 1, 1 )
    , AlbumID int not null
    , song_name VarChar(50) Not Null
    , duration int
    , genre VarChar(50) Not Null
    )

Thanks David for your quick reply.

Some clarifications as to what identity is so basically in the ablum id example it starts at 1 is that correct?

also i'm unclear about this:

create table dbo.SongArtist
    (
    SongArtistID int not null identity ( 1, 1 )
    , SongID int not null
    , ArtistID int not null
    )

and would i require to indicate any foreign keys or references?
thanks.
hope to keep this as simple as you can. thanks.

Also having trouble adding a filegroup to the table

Create table song
(      song_id Int Not Null identity(1,1),
      album_id int Not Null,
      song_name VarChar(50) Not Null,
      duration Int,
      genre VarChar(50) Not Null
}
On songs

i get an error on the "On Songs" statement
Incorrect syntax near the keyword 'On'.

SalesProd, two things on the error:
1. the last bracket turned into a 'curled bracked' (sorry for not konowing the correct English word for that)
2. 'on songs' would indicate that you would have created a special filegroup called 'songs'.

So I guess what you are looking for is:
(      song_id Int Not Null identity(1,1),
      album_id int Not Null,
      song_name VarChar(50) Not Null,
      duration Int,
      genre VarChar(50) Not Null
)

Next to that ... I've been breaking my head over a similar issue. I'm keeping track of a hitlist and was going to create a database for that myself. In the end I got a bit frustrated on things like:
1. artist changing their names over time (Prince, TAFKAP, The Symbol)
2. songs being copied/re-recorded by other artists ... is that the same song, or just the same title ?

It's off topic here I gues, so if you would like to brainstorm on this, we can do that on another way. Just let me know in this thread and we'll arange something.

Hope this helps ...

HI Yveau
 I just noticed the bracket too but i re-verified the code
and for some strange reason it is still giving me an error syntax error on "on songs"
songs is a filegroup actually

so basically what im trying to do is to create those tables and
All the tables above: album, song, artist should be stored in the Songs filegroup.
hope this clarifies.

Tested this script:
alter database Yveau
add filegroup songs

create table Y_test (col1 int, col2 varchar(50))
on songs

works fine ...

Is your filegroup marked read only perhaps ?
Check in SSMS, right click on the db, choose properties.
Go to the filegroup page and look for the readonly column for the songs filegroup. It shold be unchecked ...

Hope this helps ...

create table album
    (
    AlbumID Int Not Null identity ( 1, 1 )
    , album_name VarChar(50) Not Null
    )

create table song
    (
    SongID int not null identity ( 1, 1 )
    , AlbumID int not null
    , song_name VarChar(50) Not Null
    , duration
    , genre VarChar(50) Not Null
    )

create table artist
    (
    ArtistID int not null identity ( 1, 1 )
    , artist_name VarChar(50) Not Null
    )

create table dbo.SongArtist
    (
    SongArtistID int not null identity ( 1, 1 )
    , SongID int not null
    , ArtistID int not null
    )

i guess with these three databases i can assign it all into one filegroup?
also i'm kind of stuck with the constraints and checks on the columns.
 I also need 2 associative tables that would resolve the many-to-many relationship between the ALBUM-SONG and SONG-ARTIST tables.
 Would you be able to provide me with examples?
thks.

All these three tables can reside in the one database, no problem.

So for the associative tables:
create table SongArtist -- slightly different than yours !
(
    SongID int not null
,   ArtistID int not null
)

create table AlbumSong
(
    AlbumID int not null
,   SongID int not null
)

Note that you can remove the AlbumID column from the song table. Otherwise you are trying to create a one-to-many association, and that is not what you are looking for. So the script for the song table would be:
create table song
(
    SongID int not null identity ( 1, 1 )
,    song_name VarChar(50) Not Null
,    duration
,    genre VarChar(50) Not Null
)

then for the constraints and checks ...

alter table album add constraint pkAlbum primary key (AlbumID)
alter table song add constraint pkSong primary key (SongID)
alter table artist add constraint pkArtist primary key (ArtistID)
alter table songartist add constraint pkSongArtist primary key (SongID, ArtistID)
alter table albumsong add constraint pkAlbumSong primary key (AlbumID, SongID)

alter table songartist add constraint fkSongArtistSong foreign key (SongID) references Song (SongID)
alter table songartist add constraint fkSongArtistArtist foreign key (ArtistID) references Artist (ArtistID)
alter table albumsong add constraint fkAlbumSongAlbum foreign key (AlbumID) references Album (AlbumID)
alter table albumsong add constraint fkAlbumSongSong foreign key (SongID) references Song (SongID)

Hope this helps ...

wow that's really long
so just to clarify:
in the end I will end up with 5 tables
song, album, artist, albumsong, songartist? is that correct?

Also for the constraints and checks is it also possible to have them inside the tables?
im not sure if i explained that correctly?

and finally would there be any check constraints? other than adds?
thanks again

Yep, 5 tables ...
Yes, constraints can be defined in the 'create table' statement ... this is more clear I think. The result is all the same !

These are not 'add' checks. It's the way to go if you would like to add a constraint to a table ... these constraints will also make it impossible to remove a record from the song table if it is still mentioned in the AlbumSong table.

If you like other checks to the table, that can be done but you have to write some code for that. Writing that code you can define whether you like the constraint to check the current data as well, or if it will be new data only.

Hope this helps ...

oh ok that really clarifies but if you don't mind can you assist in showing me an example with the constraints within the table just for me to follow on how it is done. thanks.
also when u mentioned other constraints to check the current data? what do you mean by that? examples appreciated.

For example:

create table song
(
    SongID int not null identity ( 1, 1 )
,    song_name VarChar(50) Not Null
,    duration
,    genre VarChar(50) Not Null
,    constraint pkSong (SongID)
)

This check will check whether the song_name that is entered is not just spaces. (it already can't be null)

ALTER TABLE Song WITH NOCHECK
ADD CONSTRAINT ckSong CHECK (len(rtrim(song_name)) > 1)

... but the 'with nocheck' makes sure that the current data in the table is not checked. So only future data will be checked by this constraint ...

Hope this helps ...

in this example u gave:
create table song
(
    SongID int not null identity ( 1, 1 )
,    song_name VarChar(50) Not Null
,    duration
,    genre VarChar(50) Not Null
,    constraint pkSong (SongID)

shouldn't constraint pkSong primary key (SongID) instead?

and finally can u give me an example of a foreign key constraint in the table?
thanks!

Glad I could be of any help and thanks for the grade !