[Php-avanzado] Parte de la estructura de la base de datos + Consulta

[NeiKeR]

Buenas..
Agregué la gestion de musicos y generos..

http://img195.imageshack.us/img195/4441/exportc.png

drop table IF EXISTS mforb;
drop table IF EXISTS instr;
drop table IF EXISTS musi;
drop table IF EXISTS gforb;
drop table IF EXISTS genres;
drop table IF EXISTS status;
drop table IF EXISTS bfore;
drop table IF EXISTS provs;
drop table IF EXISTS parts;
drop table IF EXISTS cities;
drop table IF EXISTS bford;
drop table IF EXISTS places;
drop table IF EXISTS events;
drop table IF EXISTS discs;
drop table IF EXISTS bands;


Create table bands (
	id Int UNSIGNED NOT NULL AUTO_INCREMENT,
	name Char(100) NOT NULL,
	ltheme Char(100),
	logo Char(20),
	syear Year(4),
	eyear Year(4),
	info Text,
	status Int UNSIGNED NOT NULL,
	label Char(100),
	city Mediumint UNSIGNED NOT NULL,
	display Mediumint UNSIGNED BINARY,
	UNIQUE (id),
	UNIQUE (logo),
 Primary Key (id)) ENGINE = MyISAM;

Create table discs (
	id Int UNSIGNED NOT NULL AUTO_INCREMENT,
	name Char(100),
	date Int UNSIGNED,
	type Mediumint UNSIGNED,
	tlist Text,
	front Char(20),
	info Text,
	UNIQUE (id),
	UNIQUE (front),
 Primary Key (id)) ENGINE = MyISAM;

Create table events (
	id Int UNSIGNED NOT NULL AUTO_INCREMENT,
	place Int UNSIGNED NOT NULL,
	day Int UNSIGNED,
	hr Int UNSIGNED,
	info Text,
	img Char(20),
	UNIQUE (id),
 Primary Key (id)) ENGINE = MyISAM;

Create table places (
	id Int UNSIGNED NOT NULL AUTO_INCREMENT,
	name Char(100) NOT NULL,
	city Mediumint UNSIGNED NOT NULL,
	info Text,
	gmaps Char(150),
	UNIQUE (id),
 Primary Key (id)) ENGINE = MyISAM;

Create table bford (
	id Int UNSIGNED NOT NULL AUTO_INCREMENT,
	disc_id Int UNSIGNED NOT NULL,
	band_id Int UNSIGNED NOT NULL,
	UNIQUE (id),
 Primary Key (id)) ENGINE = MyISAM;

Create table cities (
	id Mediumint UNSIGNED NOT NULL AUTO_INCREMENT,
	dep_id Mediumint UNSIGNED NOT NULL,
	name Char(100),
	UNIQUE (id),
 Primary Key (id)) ENGINE = MyISAM;

Create table parts (
	id Mediumint UNSIGNED NOT NULL AUTO_INCREMENT,
	name Char(20),
	prov_id Mediumint UNSIGNED NOT NULL,
	UNIQUE (id),
	UNIQUE (name),
 Primary Key (id)) ENGINE = MyISAM;

Create table provs (
	id Mediumint UNSIGNED NOT NULL AUTO_INCREMENT,
	name Char(20),
	UNIQUE (id),
	UNIQUE (name),
 Primary Key (id)) ENGINE = MyISAM;

Create table bfore (
	id Int UNSIGNED NOT NULL AUTO_INCREMENT,
	event Int UNSIGNED NOT NULL,
	band Int UNSIGNED NOT NULL,
	UNIQUE (id),
 Primary Key (id)) ENGINE = MyISAM;

Create table status (
	id Int UNSIGNED NOT NULL AUTO_INCREMENT,
	name Char(50) NOT NULL,
	UNIQUE (id),
 Primary Key (id)) ENGINE = MyISAM;

Create table genres (
	id Int UNSIGNED NOT NULL AUTO_INCREMENT,
	name Char(50) NOT NULL,
	UNIQUE (id),
	UNIQUE (name),
 Primary Key (id)) ENGINE = MyISAM;

Create table gforb (
	id Int UNSIGNED NOT NULL AUTO_INCREMENT,
	id_band Int UNSIGNED NOT NULL,
	id_genre Int UNSIGNED NOT NULL,
	UNIQUE (id),
 Primary Key (id)) ENGINE = MyISAM;

Create table musi (
	id Int UNSIGNED NOT NULL AUTO_INCREMENT,
	name Char(70),
	info Text,
	UNIQUE (id),
 Primary Key (id)) ENGINE = MyISAM;

Create table instr (
	id Int UNSIGNED NOT NULL AUTO_INCREMENT,
	name Char(50),
	UNIQUE (id),
	UNIQUE (name),
 Primary Key (id)) ENGINE = MyISAM;

Create table mforb (
	id Int UNSIGNED NOT NULL AUTO_INCREMENT,
	id_band Int UNSIGNED NOT NULL,
	id_mus Int UNSIGNED NOT NULL,
	id_ins Int UNSIGNED NOT NULL,
	UNIQUE (id),
 Primary Key (id)) ENGINE = MyISAM;


Alter table bford add Foreign Key (band_id) references bands (id) on
delete  restrict on update  restrict;
Alter table bfore add Foreign Key (band) references bands (id) on
delete  restrict on update  restrict;
Alter table gforb add Foreign Key (id_band) references bands (id) on
delete  restrict on update  restrict;
Alter table mforb add Foreign Key (id_band) references bands (id) on
delete  restrict on update  restrict;
Alter table bford add Foreign Key (disc_id) references discs (id) on
delete  restrict on update  restrict;
Alter table bfore add Foreign Key (event) references events (id) on
delete  restrict on update  restrict;
Alter table events add Foreign Key (place) references places (id) on
delete  restrict on update  restrict;
Alter table places add Foreign Key (city) references cities (id) on
delete  restrict on update  restrict;
Alter table bands add Foreign Key (city) references cities (id) on
delete  restrict on update  restrict;
Alter table cities add Foreign Key (dep_id) references parts (id) on
delete  restrict on update  restrict;
Alter table parts add Foreign Key (prov_id) references provs (id) on
delete  restrict on update  restrict;
Alter table bands add Foreign Key (status) references status (id) on
delete  restrict on update  restrict;
Alter table gforb add Foreign Key (id_genre) references genres (id) on
delete  restrict on update  restrict;
Alter table mforb add Foreign Key (id_mus) references musi (id) on
delete  restrict on update  restrict;
Alter table mforb add Foreign Key (id_ins) references instr (id) on
delete  restrict on update  restrict;