SQL

Avui hem començat passant el model relacional a SQL.

create table usuaris(
 idusuari int NOT NULL PRIMARY KEY AUTO_increment,
 compte varchar(25),
 clau char(25),
 tema varchar(50)
);
create table moduls(
 idmodul int not null primary key auto_increment,
 tipus enum('0','1') not null,
 nom varchar(50)
);
create table rols(
 idrol int not null primary key auto_increment,
 nom varchar(50) not null
);
create table usuaris_rols(
 idusuarif int not null,
 idrolf int not null,
 primary key (idusuarif,idrolf),
 foreign key (idusuarif) references usuaris(idusuari),
 foreign key (idrolf) references rols(idrol)
);
create table rols_moduls(
 idrolf  int not null,
 idmodulf int not null,
 nivellacces enum ('0','1','2','3','4','5') not null default 0,
 primary key (idrolf,idmodulf),
 foreign key (idrolf) references rols(idrol),
 foreign key (idmodulf) references moduls(idmodul)
);
create table menus(
 idmenu int not null primary key auto_increment,
 idmodulf int not null,
 nom varchar(20) not null,
 accio char(1),
 pare int,
 foreign key (pare) references menus(idmenu),
 foreign key (idmodulf) references moduls(idmodul)
);
create table dadespersonals(
 iddadespersonals  int not null primary key,
 nom varchar(25),
 cognoms varchar(50),
 telefon varchar(14),
 email varchar(100),
 dni varchar(9),
 foreign key (iddadespersonals)references usuaris(idusuari)
);
create table configuracio(
 nom varchar(50) primary key,
 nomempresa varchar(100),
 temadefault varchar(100),
 canvitema enum('0','1') not null default 1
);
 
ALTER TABLE `dadespersonals` DROP FOREIGN KEY `dadespersonals_ibfk_1` ;

ALTER TABLE `dadespersonals` ADD FOREIGN KEY ( `iddadespersonals` ) REFERENCES `modinet`.`usuaris` (
`idusuari`
) ON DELETE CASCADE ;
ALTER TABLE `menus` DROP FOREIGN KEY `menus_ibfk_2` ;

ALTER TABLE `menus` ADD FOREIGN KEY ( `idmodulf` ) REFERENCES `modinet`.`moduls` (
`idmodul`
) ON DELETE CASCADE ;

ALTER TABLE `menus` DROP FOREIGN KEY `menus_ibfk_1` ;

ALTER TABLE `menus` ADD FOREIGN KEY ( `pare` ) REFERENCES `modinet`.`menus` (
`idmenu`
) ON DELETE CASCADE ;