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

NeiKeR neiker en gmail.com
Mie Sep 9 15:14:42 ART 2009 

El 8 de septiembre de 2009 02:55, Leonardo Tadei - Pegasus Tech Supply <
leonardot en pegasusnet.com.ar> escribió:

> Hola Javier!
>
> El mar, 08-09-2009 a las 02:04 -0300, NeiKeR escribió:
> > >> 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;
> > >
> > >        Te pregunto para que me des contexto: y si un músico toca en una
> banda
> > > varios instrumentos, tendrá un registro en esta tabla para cada
> > > instrumento que toca?
> >
> >
> >
> > Si, por cada instrumento que un músico toque en una banda va un
> > registro en esta tabla.. ¿Esta bien asi?
>
>         Está correctísimo!
>        Como te decía, estaba más que nada ratificando cosas, porque estas
> relaciones no se desprenden de tu SRS.
>

Entonces agrego eso a la SRS?




> > >        Por otra parte, tiene sentido que tenga UNIQUE
> (id_band,id_mus,id_ins).
> >
> >
> >
> > Si, eso pense pero no encontre como hacerlo en el CaseStudio... Pero
> > lo agrego a mano y listo. ¿Pongo el UNIQUE (id_band,id_mus,id_ins)
> > debajo de UNIQUE (id)?
>
>         Más bien sacá el UNIQUE (id) por lo que te decía de que al ser
> Primary
> Key ya es única, y agregá UNIQUE (id_band,id_mus,id_ins)
>

Ok, pero los id de todas las tablas los tengo como UNIQUE.. ¿se lo saco?
Agregué un index formado por esos 3 campos.
Ademas agregué una tabla mas para los temas (Con el nombre del tema, la
duración y la letra)
Quedo asi:
ERD:
http://img169.imageshack.us/img169/6752/export.png

SQL:

Drop index musician on mforb;


drop table IF EXISTS songs;
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,
 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,
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(100),
 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;

Create table songs (
id Int UNSIGNED NOT NULL AUTO_INCREMENT,
id_disc Int UNSIGNED NOT NULL,
 name Char(100),
lyric Text,
len Int UNSIGNED,
 UNIQUE (id),
 Primary Key (id)) ENGINE = MyISAM;


Create UNIQUE Index musician ON mforb (id_band,id_mus,id_ins);


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 songs add Foreign Key (id_disc) 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;




La ultima clase fue el sabado pasado o es este sabado?


-- 
Javier Alejandro Alvarez
------------ próxima parte ------------
Se ha borrado un adjunto en formato HTML...
URL: http://www3.fi.mdp.edu.ar/cgi-bin/mailman/private/php-avanzado/attachments/20090909/bc78f191/attachment.htm

Más información sobre la lista de distribución Php-avanzado