Please help me with ER Diagram that I did for CD collection database. Most imort
ID: 3918101 • Letter: P
Question
Please help me with ER Diagram that I did for CD collection database. Most imortant to write correct relationship, there must be at least 2 one-to-many/one-to-one relationships;
and there must be at least 1 many-to-many relationship. Am I correct with them?
Artist (artist_id, ARTIST_BAND, artist_fname, artist_lname)
Record Label (RECORD_ID, RECORD_NAME , CITY , STATE , COUNTRY, WEBSITE)
Song (song_id, song_name, song_length, artist_id , album_id)
Album (ALBUM_ID, ALBUM_TITLE, Number_of_tracks, ALBUM_YEAR, ARTIST_ID, RECORD_ID)
And also what my professor meant : c. discuss which Normal Form it is in? (FULL QUESTION:
you must submit the conceptual and logical design of your database as specified in section Design and Implementation, which include the E-R diagram and relational database schema of your database. For each table:
a, describe all the attributes (including attribute names, data types etc.);
b, specify the primary keys and foreign keys (if exist);
c, discuss which Normal Form it is in;
d, provide the SQL DDL statement you used to create the table, or the screen snapshots if your used GUI;
ALBUM ALBUM ID ALBUM Title Number of Tracks ALBUM_Year Artist ID RecordID ALBUMSONG Record ALBUM ecord Labe Record ID Record Name Cit State Country website SONG SONG ID SONG_NAME SONG LENGTH ALBUM_ID ARTIST_ID ARTIST SONG ARTIST ARTIST ID ARTIST BAND ARTIST Name ecord ArtisExplanation / Answer
If you have any doubts, please give me comment...
Artist (artist_id, ARTIST_BAND, artist_fname, artist_lname)
Record Label (RECORD_ID, RECORD_NAME , CITY , STATE , COUNTRY, WEBSITE)
Song (song_id, song_name, song_length, artist_id , album_id)
Album (ALBUM_ID, ALBUM_TITLE, Number_of_tracks, ALBUM_YEAR, ARTIST_ID, RECORD_ID)
Artist_Record_Labels(artist_id, record_id);
DDL Schema:
CREATE TABLE Artist(
artist_id INT NOT NULL PRIMARY KEY,
ARTIST_BAND VARCHAR(30),
artist_fname VARCHAR(50),
artist_lname VARCHAR(50)
);
CREATE TABLE Record_Label(
RECORD_ID INT NOT NULL PRIMARY KEY,
RECORD_NAME VARCHAR(100),
CITY VARCHAR(50),
STATE VARCHAR(20),
COUNTRY VARCHAR(5),
WEBSITE VARCHAR(50)
);
CREATE TABLE Album(
ALBUM_ID INT NOT NULL PRIMARY KEY,
ALBUM_TITLE VARCHAR(100),
Number_of_tracks INT,
ALBUM_YEAR INT,
ARTIST_ID INT,
RECORD_ID INT,
FOREIGN KEY(ARTIST_ID) REFERENCES Artist(artist_id),
FOREIGN KEY(RECORD_ID) REFERENCES Record_Label(RECORD_ID)
);
CREATE TABLE Song(
song_id INT NOT NULL PRIMARY KEY,
song_name VARCHAR(100),
song_length REAL(5,2),
artist_id INT,
album_id INT,
FOREIGN KEY(ARTIST_ID) REFERENCES Artist(artist_id),
FOREIGN KEY(album_ID) REFERENCES Album(ALBUM_ID),
);
CREATE TABLE Artist_Record_Labels(
artist_id INT,
record_id INT,
PRIMARY KEY(artist_id, record_id),
FOREIGN KEY(ARTIST_ID) REFERENCES Artist(artist_id),
FOREIGN KEY(RECORD_ID) REFERENCES Record_Label(RECORD_ID)
);