Archive for the 'Trabajos prácticos' Category

Trabajo práctico de bases de datos

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’)