A normalized relational schema. As documentation include all functional dependen

Question

A normalized relational schema. As documentation include all functional dependences, and identify (and justify) the normal form status of each of your relations. Also, include referential integrity actions. (If there are constraints in the E-R model that you cannot realize in the schema, note them!)

SQL DDL that creates the tables implementing your schema. If there are constraints in the schema that you cannot realize in the DDL, note them!

Record Labels +Name +Label ID +Address Pays Groups +Members +Group ID +Date Formed Musicians +Name +Stage name Hires +Artist ID +Date of Birth Creates Songs +Name Albums +Song ID +Length +Name +Year released +Album ID cludes +Writer +Artists +Singer +Year released +Genre +Rating +Rating +Number of Songs

Explanation / Answer

CREATE TABLE Record_Labels (
Lable_ID int NOT NULL,
Name varchar2(255),
Address varchar2(255),
PRIMARY KEY (Lable_ID),
  
);

CREATE TABLE Groups (
Group_ID int NOT NULL,
Members varchar2(255),
Lable_ID int NOT NULL,
DateFormed varchar2(255),
PRIMARY KEY (Group_ID),
FOREIGN KEY (Lable_ID) REFERENCES Record_Labels(Lable_ID)
);

Here i have used lable_id of groups table as foreign key to form relation to the table Record_Labels.

CREATE TABLE Musicians (
Name varchar2(255),
Stagename varchar2(255),
Artist_ID int NOT NULL,
DateOfBirth varchar2(255),
Lable_ID int NOT NULL,
PRIMARY KEY (Artist_ID),
FOREIGN KEY (Lable_ID) REFERENCES Record_Labels(Lable_ID)
);

Here i have used lable_id of Musicians table as foreign key to form relation to the table Record_Labels.

CREATE TABLE Albums (
Name varchar2(255),
Artist_ID int NOT NULL,
Years_Released number,
Album_ID int NOT NULL,
Artist varchar2(255),
  
PRIMARY KEY (Album_ID),
FOREIGN KEY (Artist_ID) REFERENCES Musicians(Artist_ID)
);

Here i have used Artist_ID of Albums table as foreign key to form relation to the table Musicians.

CREATE TABLE Songs (
Name varchar2(255),
Song_ID int NOT NULL,
Years_Released number,
writer varchar2(255),
singer varchar2(255),
Genre varchar2(255),
Rating number,
Length int,
Album_ID int NOT NULL,
Artist varchar2(255),
  
PRIMARY KEY (Song_ID),
FOREIGN KEY (Album_ID) REFERENCES Musicians(Album_ID)
);

The above relations are created as oper normalization applies and. And the above relational schema is in BCNF.

Related Questions

Navigate

• Browse (All)
• Browse A
• Subjects

---

---

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