A) Using SQL, construct DDL statements that create the relational schema below.

Question

A) Using SQL, construct DDL statements that create the relational schema below. Include all the necessary domain key, and referential integrity constraints (25 points). artist(ar_id integer, name: string, gender string, genre: string) album(al_id integer, ar id integer, year. name: string, cost number) song (s id integer, al id integer, title: string, duration: number) M. Rathod .COP 3540 Intro to Database Structures. Sunmer 2018 Homemork 1 Using the data given below, write DML statements and populate the relational database created above (15 points). s idal id duration 4:08 3:34 4:53 3:57 3:48 3:37 God Feel All thi 2 Feeling Myself Despacito Havana al id ar id year 1 2017 2 2017 3 2017 4 2017 5 2018 Damn The Pinkprint to & My Greatest Hits 8.23 5.99 9.92 9.99 9.97 This Is Camila Artist ar id re name Kendrick Lamar Nicki M Luis Fonsi Sia Camila Cabello Dance B) Using single DML statements, complete the following: (10 points) The music store manager has decided to display the final price of the albums that includes a tax of 6.5%. Make this change in the affected instance Delete all the tuples from all the tables pertaining to the artist Sia (0) (ii)

Explanation / Answer

A) Ceate Statements.
create table artist (ar_id integer primary key,name varchar(15),gender char(2),genre varchar(8));

create table album (al_id integer primary key,ar_id integer,alyear year,name varchar(15),cost decimal(6,2),foreign key(ar_id) references artist(ar_id));

create table song(s_id integer primary key,al_id integer,title varchar(15),duration decimal(3,2),foreign key(al_id) references album(al_id));

Insertion Statements:- Below are the sample insertion statements that inserts 2 rows in each table. Just follow the same way by changing the data in the queries below.

Song:-
insert into song values (1,1,'God',4.08);
insert into song values (2,2,'Feel',3.34);

Album:-
insert into Album values (1,1,'2017','Damn',8.23);
insert into Album values (2,2,'2017','The Pinkprint',5.99)

Artist:-
insert into Artist values (1,'Kendrick Lamar','M','Rap');
insert into Artist values (2,'Nicki Minaj','F','Rap');

B. i)update Album set cost=(cost+(cost*6.5/100));
The above query will update the cost of the album by 6.5 %

ii) delete * from artist a join album al on a.ar_id=al.ar_id join song s on s.al_id=al.al_id and a.name='Sia';
Above query will delete the tuples from the tables related to the artist Sia.

Related Questions

Navigate

• Browse (All)
• Browse A
• Subjects

---

---

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