Hello. I have a quick question. I am making a database as a personal project (I am using Microsoft SQL Server therefore I am using its SQL syntax). All it does is that it keeps track of singing groups and bands, the members in these groups, albums they made, songs they did and awards they won (for example, I can insert The Beatles into the Database and record all their members, when they debuted, when they disbanded, their songs and more).
For now I am only going to create a table that can store the group's/band's name and another table to store the band's/group's members. When this works then I will add more tables. Here is the code for this:
USE BandD_Base GO CREATE TABLE BAND( BandName Char(100) NOT NULL, BandLocation Char(100) NOT NULL, DebuteDate Numeric(4) NOT NULL, DisbandDate Numeric(4) NULL, Genre Char(100) NOT NULL, CONSTRAINT Band_PK PRIMARY KEY(BandName), CONSTRAINT CheckDebute CHECK (DebuteDate LIKE '[1-2][0-9][0-9][0-9]'), CONSTRAINT CheckDisband CHECK (DisbandDate LIKE '[1-2][0-9][0-9][0-9]'), CREATE TABLE MEMBERS( MemberName Char(100) NOT NULL, Nationality Char(100) NOT NULL, Position Char(100) NOT NULL, LeftGroup Char(3) NOT NULL, BandName Char(100) NOT NULL, CONSTRAINT Member_PK PRIMARY KEY(MemberName), CONSTRAINT CheckLeftGroup CHECK (LeftGroup IN ('Yes', 'No')), CONSTRAINT Member_FK FOREIGN KEY (BandName) REFERENCES BAND(BandName));
Here is some sample data about an American girl group called Destiny's Child. It might not be completely accurate but I am just using this information for fun. Here is the SQL code for it:
INSERT INTO BAND(BandName, BandLocation, DebuteDate, DisbandDate, Genre) VALUES ('Destinys Child', 'America', '1990', '2006', 'R&B'); INSERT INTO MEMBERS(MemberName, Nationality, Position, LeftGroup, BandName) VALUES ('Beyoncé Knowles', 'American', 'Vocals', 'No', 'Destinys Child'), ('Kelly Rowland', 'American', 'Vocals', 'No', 'Destinys Child'), ('Michelle Williams', 'American', 'Vocals', 'No', 'Destinys Child'), ('LaTavia Roberson', 'American', 'Vocals', 'Yes', 'Destinys Child'), ('LeToya Luckett', 'American', 'Vocals', 'Yes', 'Destinys Child'), ('Farrah Franklin', 'American', 'Vocals', 'Yes', 'Destinys Child'),
I get an error that says that I cannot insert duplicates. I know that the error is coming from the fact that when I inserted all the members of Destiny's Child, I had to input "Destiny's Child" for the band name or else they will not be associated with the same group. I just don't know how to fix this.
Is it because I used the band name as the primary key? Should I make a BandID that will be a surrogate key and make that the primary key (I would think that I will get the same error)? I know there are no multi-valued dependencies because one band can have many members so this is a one-to-many relationship (therefore I don't need to normalize anything).
I'm sure the answer is probably really simple but any help is appreciated. Thanks.