Добрый вечер,
Скажу сразу что я далеко не спец. по БД, пробую реализовать БД по UML диаграмме, которая была сделана не мной.
Возможно мною кардинально не правильно были поняты некоторые моменты в этой UML диаграмме, буду очень признателен. Между таблицами если я верно понял требуется реализовать связи многие ко многим, пробую сделать через промежуточную таблицу, но при попытке создать ее в PhpMyAdmin вижу ошибку:
#1005 - Can't create table 'annonces.group_right' (errno: 150) (Детали...)
Supports transactions, row-level locking, and foreign keys
транзакции нижнего уровня выключены.
Вот то что мне удалось сделать на данный момент:
| Код |
CREATE TABLE `Right` (
`Id_right` int(12) NOT NULL AUTO_INCREMENT,
`Descr_Right` varchar(32) NOT NULL,
PRIMARY KEY (`id_right`)
);
CREATE TABLE `Group` (
`Id_group` int(12) NOT NULL AUTO_INCREMENT,
`Decsr_Group` varchar(32) NOT NULL,
PRIMARY KEY (`id_group`)
);
CREATE TABLE `Group_Right` (
`Id_right` int(12) NOT NULL,
`Id_group` int(12) NOT NULL,
PRIMARY KEY (`Id_right`, `Id_group`),
FOREIGN KEY (`Id_right`) REFERENCES `Right`(`Id_right`),
FOREIGN KEY (`Id_group`) REFERENCES `Group`(`Id_group`)
);
CREATE TABLE `Group_User` (
`Id_group` int(12) NOT NULL,
`Id_user` int(12) NOT NULL,
PRIMARY KEY (`Id_group`, `Id_user`),
FOREIGN KEY (`Id_group`) REFERENCES `Group`(`Id_group`),
FOREIGN KEY (`Id_user`) REFERENCES `User`(`Id_user`)
);
CREATE TABLE `User_Job` (
`Id_user` int(12) NOT NULL,
`Id_job` int(12) NOT NULL,
PRIMARY KEY (`Id_user`, `Id_job`),
FOREIGN KEY (`Id_user`) REFERENCES `User`(`Id_user`),
FOREIGN KEY (`Id_job`) REFERENCES `Job`(`Id_user`)
);
CREATE TABLE `User_Log_Host` (
`Id_user` int(12) NOT NULL,
`Id_job` int(12) NOT NULL,
`Id_host` int(12) NOT NULL,
PRIMARY KEY (`Id_user`, `Id_job`, `Id_host`),
FOREIGN KEY (`Id_user`) REFERENCES `User`(`Id_user`),
FOREIGN KEY (`Id_job`) REFERENCES `Job`(`Id_job`) ,
FOREIGN KEY (`Id_host`) REFERENCES `Host`(`Id_host`)
);
CREATE TABLE `Host_Port` (
`Id_host` int(12) NOT NULL,
`Id_port` int(12) NOT NULL,
PRIMARY KEY (`Id_host`, `Id_port`),
FOREIGN KEY (`Id_host`) REFERENCES `Host`(`Id_host`) ,
FOREIGN KEY (`Id_port`) REFERENCES `Port`(`Id_port`)
);
CREATE TABLE `Cve_Port` (
`Id_cve` int(12) NOT NULL,
`Id_port` int(12) NOT NULL,
PRIMARY KEY (`Id_cve`, `Id_port`),
FOREIGN KEY (`Id_cve`) REFERENCES `Cve`(`Id_cve`),
FOREIGN KEY (`Id_port`) REFERENCES `Port`(`Id_port`)
);
CREATE TABLE `User` (
`Id_user` int(12) NOT NULL AUTO_INCREMENT,
`User` varchar(32) NOT NULL,
`Pass` varchar(32) NOT NULL,
PRIMARY KEY (`id_user`)
);
CREATE TABLE `Log` (
`Id_log` int(12) NOT NULL AUTO_INCREMENT,
`Nom_log` varchar(32) NOT NULL,
PRIMARY KEY (`id_log`)
);
CREATE TABLE `Job` (
`Id_job` int(12) NOT NULL AUTO_INCREMENT,
`Job_name` varchar(32) NOT NULL,
PRIMARY KEY (`id_job`)
);
CREATE TABLE `Host` (
`Id_host` int(12) NOT NULL AUTO_INCREMENT,
`Name` varchar(32) NOT NULL,
`Os_info` varchar(32) NOT NULL,
PRIMARY KEY (`id_host`)
);
CREATE TABLE `Port` (
`Id_port` int(12) NOT NULL AUTO_INCREMENT,
`Service_p` varchar(32) NOT NULL,
`Service_v` varchar(32) NOT NULL,
PRIMARY KEY (`id_port`)
);
CREATE TABLE `Cve` (
`Id_cve` int(12) NOT NULL AUTO_INCREMENT,
`Service_c` varchar(32) NOT NULL,
`Descr_c` varchar(32) NOT NULL,
PRIMARY KEY (`Id_cve`)
);
|
|