estudio.sql

create table gravadora(
	grav_cod integer,
	grav_nome varchar(50) not null,
	grav_end varchar(100),
	constraint pk_grav primary key (grav_cod)
);

create table cd(
	cd_cod integer,
	cd_grav_cod integer,
	cd_nome varchar(100) not null,
	cd_valor numeric(10,2) not null,
	cd_data_lancamento date,
	constraint pk_cd primary key (cd_cod),
	constraint fk_gravadora foreign key (cd_grav_cod) references gravadora(grav_cod)
);

create table musica(
	mus_cod integer,
	mus_nome varchar(50) not null,
	mus_duracao time,
	mus_faixa integer,
	constraint pk_musica primary key (mus_cod)
);
create table autor(
	aut_cod integer,
	auto_nome varchar(50) not null,
	constraint pk_autor primary key (aut_cod)
);

create table item_cd(
	ite_mus_cod integer,
	ite_cd_cod integer,
	constraint pk_item_cd primary key(ite_mus_cod, ite_cd_cod),
	constraint fk_musica foreign key (ite_cd_cod) references musica(mus_cod),
	constraint fk_cd foreign key (ite_cd_cod) references cd(cd_cod)
);

create table musica_autor(
	mus_cod integer,
	aut_cod integer,
	constraint pk_musica_autor primary key(mus_cod, aut_cod),
	constraint fk_mus_cod foreign key (mus_cod) references musica(mus_cod),
	constraint fk_aut_cod foreign key (aut_cod) references autor(aut_cod)
);


insert into gravadora values(1, 'Sony', 'Km 12'), 
				(2, 'Som Disco', 'Rua Y'), 
				(3, 'MK Music', 'Travessa P'),
				(4, 'An', 'Viela N'),
				(5, 'Gi', 'Av. G');

insert into cd values(1, 1, 'CD 1', 15.00, '1990-05-21'),
			(2, 2, 'CD 2', 25.00, '1999-08-01'),
			(3, 3, 'CD 3', 35.00, '2010-12-21'),
			(4, 4, 'CD 4', 45.00, '2017-02-09'),
			(5, 5, 'CD 5', 50.00, '2016-06-28');

insert into musica values(1, 'Musica 1', '00:02:20', 1),
			(2, 'Musica 2', '00:03:50', 2),
			(3, 'Musica 3', '00:01:00', 3),
			(4, 'Musica 4', '00:10:00', 4),
			(5, 'Musica 5', '00:01:30', 5);

insert into autor values (1, 'Dan Reynold'), (2, 'Ana'), (3, 'Gino'), (4, 'Vitor'), (5, 'Viviane');
insert into item_cd values (1,1),(2,4),(3,2),(3,3),(5,2);
insert into musica_autor values(1,5),(2,3),(4,1),(3,1),(5,4);