Trabajo práctico de bases de datos
Comments(0) Introducción
Este trabajo tiene como fin mostrar la estructura y diseño de la base de datos de SNFlog, un sitio Web que hace unos años está en funcionamiento.
SNFlog, en resumidas palabras, permite a los cibernautas crear fotologs, configurarlo (colores, fuente) y subir fotos al mismo; que serán vistas, comentadas y votadas por el resto de los visitantes del sitio.
Para comenzar se verá la estructura de la base de datos, para exponer todo el código SQL que se utilizó para crearla.
Se presentarán también el código para la creación de: vistas, procedimientos almacenados y desencadenadores.
Estructura dela Basede Datos
Para comenzar vamos a analizar la estructura de la base de datos que utiliza el sistema para su funcionamiento. A continuación, el canónico de la base de datos:
En el diagrama anterior podemos ver por completo la estructura completa de la base de datos.
Código SQL de la base de datos
A continuación se adjuntará todo el código SQL necesario para la creación de la base de datos anteriormente esquematizada.
Tablas
CREATE TABLE ‘comentarios’ (
‘codCom’ int(11) NOT NULL auto_increment,
‘codPos’ int(11) NOT NULL default ‘0’,
‘codUsu’ int(10) unsigned NOT NULL,
‘Nombre’ varchar(50) default NULL,
‘eMail’ varchar(50) default NULL,
‘Comentario’ mediumtext NOT NULL,
‘Fecha’ datetime NOT NULL default ‘0000-00-00 00:00:00’,
PRIMARY KEY (‘codCom’),
KEY ‘codPos’ (‘codPos’),
KEY ‘codUsu’ (‘codUsu’)
)
CREATE TABLE ‘estilos’ (
‘codest’ int(10) unsigned NOT NULL auto_increment,
‘codusu’ int(10) unsigned NOT NULL default ‘0’,
‘fondo’ varchar(8) NOT NULL default ‘‘,
‘color’ varchar(8) NOT NULL default ‘‘,
‘acolor’ varchar(8) NOT NULL default ‘‘,
‘titulocolor’ varchar(8) NOT NULL default ‘‘,
‘fuente’ varchar(20) NOT NULL default ‘‘,
PRIMARY KEY (‘codest’),
KEY ‘codusu’ (‘codusu’)
);
CREATE TABLE ‘favoritos’ (
‘codFav’ int(11) NOT NULL auto_increment,
‘codUsu’ int(10) unsigned NOT NULL default ‘0’,
‘codUsuFav’ int(10) unsigned NOT NULL default ‘0’,
PRIMARY KEY (‘codFav’),
KEY ‘codusu’ (‘codUsu’),
KEY ‘codusu2’ (‘codUsuFav’)
);
CREATE TABLE ‘links’ (
‘codLin’ int(11) NOT NULL auto_increment,
‘codUsu’ int(10) unsigned NOT NULL default ‘0’,
‘Titulo’ varchar(50) NOT NULL default ‘‘,
‘Link’ varchar(80) NOT NULL default ‘‘,
PRIMARY KEY (‘codLin’),
KEY ‘ind_codUsu’ (‘codUsu’)
);
CREATE TABLE ‘pagamigas’ (
‘codpag’ int(10) NOT NULL auto_increment,
‘pagina’ varchar(50) NOT NULL default ‘‘,
‘url’ varchar(50) NOT NULL default ‘‘,
PRIMARY KEY (‘codpag’)
);
CREATE TABLE ‘posteos’ (
‘codPos’ int(10) NOT NULL auto_increment,
‘codUsu’ int(10) unsigned NOT NULL default ‘0’,
‘Titulo’ varchar(50) NOT NULL default ‘‘,
‘Descripcion’ longtext,
‘Fecha’ datetime NOT NULL default ‘0000-00-00 00:00:00’,
‘Visitas’ int(10) unsigned NOT NULL default ‘0’,
‘Comentarios’ int(11) NOT NULL default ‘0’,
PRIMARY KEY (‘codPos’),
KEY ‘codusu’ (‘codUsu’)
);
CREATE TABLE ‘useronline’ (
‘id’ int(10) NOT NULL auto_increment,
‘ip’ varchar(15) NOT NULL default ‘‘,
‘timestamp’ varchar(15) NOT NULL default ‘‘,
‘registrado’ int(10) NOT NULL default ‘0’,
‘codpos’ mediumtext character set utf8 collate utf8_bin NOT NULL,
PRIMARY KEY (‘id’),
UNIQUE KEY ‘id’ (‘id’)
);
CREATE TABLE ‘usuarios’ (
‘codUsu’ int(10) unsigned NOT NULL auto_increment,
‘Alias’ varchar(30) NOT NULL default ‘‘,
‘Contrasena’ varchar(50) NOT NULL default ‘‘,
‘Nombre’ varchar(20) NOT NULL default ‘‘,
‘Apellido’ varchar(15) NOT NULL default ‘‘,
‘eMail’ varchar(70) NOT NULL default ‘‘,
‘Titulo’ varchar(100) NOT NULL default ‘‘,
‘fechaNac’ date NOT NULL default ‘0000-00-00’,
‘Sexo’ enum(‘‘,’Masculino’,’Femenino’) NOT NULL default ‘‘,
‘Estilo’ varchar(10) NOT NULL default ‘‘,
‘Celular’ bigint(10) unsigned default NULL,
‘FechaAlta’ datetime NOT NULL default ‘0000-00-00 00:00:00’,
‘FechaActividad’ datetime NOT NULL default ‘0000-00-00 00:00:00’,
‘privilegios’ int(11) NOT NULL default ‘0’,
‘mailcomentario’ tinyint(1) NOT NULL default ‘1’,
‘maillimite’ tinyint(1) NOT NULL default ‘1’,
‘comenta’ int(1) NOT NULL default ‘0’,
PRIMARY KEY (‘codUsu’),
KEY ‘codusu’ (‘codUsu’),
KEY ‘alias_codusu’ (‘Alias’,’codUsu’)
);
CREATE TABLE ‘votaciones’ (
‘codvot’ int(11) NOT NULL auto_increment,
‘codpos’ int(11) NOT NULL default ‘0’,
‘votacion’ int(5) NOT NULL default ‘0’,
PRIMARY KEY (‘codvot’),
KEY ‘codpos’ (‘codpos’)
);
Restricciones
ALTER TABLE ‘comentarios’
ADD CONSTRAINT ‘comentarios_ibfk_1’ FOREIGN KEY (‘codPos’) REFERENCES ‘posteos’ (‘codPos’) ON DELETE CASCADE ON UPDATE CASCADE;
ALTER TABLE ‘estilos’
ADD CONSTRAINT ‘estilos_ibfk_1’ FOREIGN KEY (‘codusu’) REFERENCES ‘usuarios’ (‘codUsu’) ON DELETE CASCADE ON UPDATE CASCADE;
ALTER TABLE ‘favoritos’
ADD CONSTRAINT ‘favoritos_ibfk_1’ FOREIGN KEY (‘codUsu’) REFERENCES ‘usuarios’ (‘codUsu’) ON DELETE CASCADE ON UPDATE CASCADE,
ADD CONSTRAINT ‘favoritos_ibfk_2’ FOREIGN KEY (‘codUsuFav’) REFERENCES ‘usuarios’ (‘codUsu’) ON DELETE CASCADE ON UPDATE CASCADE;
ALTER TABLE ‘links’
ADD CONSTRAINT ‘links_ibfk_1’ FOREIGN KEY (‘codUsu’) REFERENCES ‘usuarios’ (‘codUsu’) ON DELETE CASCADE ON UPDATE CASCADE;
ALTER TABLE ‘posteos’
ADD CONSTRAINT ‘posteos_ibfk_1’ FOREIGN KEY (‘codUsu’) REFERENCES ‘usuarios’ (‘codUsu’) ON DELETE CASCADE ON UPDATE CASCADE;
ALTER TABLE ‘votaciones’
ADD CONSTRAINT ‘votaciones_ibfk_1’ FOREIGN KEY (‘codPos’) REFERENCES ‘posteos’ (‘codPos’) ON DELETE CASCADE ON UPDATE CASCADE;
ALTER TABLE ‘useronline’
ADD CONSTRAINT ‘useronline_ibfk_1’ FOREIGN KEY (‘codUsu’) REFERENCES ‘usuarios’ (‘codUsu’) ON DELETE CASCADE ON UPDATE CASCADE;
Vistas
CREATE VIEW inicio AS
SELECT MAX( P.codpos ) M, U.alias A, MAX( UNIX_TIMESTAMP( P.fecha ) )
FROM posteos P INNER JOIN usuarios U USING ( codusu ) GROUP BY A
ORDER BY M DESC LIMIT 16
Procedimientos almacenados
CREATE PROCEDURE votar (IN icodpos INT,IN ivoto INT)
begin
INSERT INTO votaciones (codpos,votacion) VALUES (icodpos,ivoto);
end //
Desencadenadores
CREATE TRIGGER estilo AFTER INSERT ON usuarios
FOR EACH ROW
INSERT INTO estilos(codusu, fondo, color, acolor, titulocolor, fuente ) VALUES (NEW.codusu, ‘#F0F0D0’, ‘#406080’, ‘#1030C0’, ‘#909090’, ‘Arial’)