SQL da aula de SI dia 29 de outubro de 2019

CREATE DATABASE IF NOT EXISTS `network` DEFAULT CHARACTER SET latin1 COLLATE latin1_swedish_ci;
USE `network`;

-- --------------------------------------------------------

--
-- Estrutura da tabela `access`
--

CREATE TABLE IF NOT EXISTS `access` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `idUser` int(11) NOT NULL,
  `idAccessType` int(11) NOT NULL,
  `dataAcces` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `idUser` (`idUser`),
  KEY `idAccessType` (`idAccessType`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1 AUTO_INCREMENT=3 ;

--
-- Extraindo dados da tabela `access`
--

INSERT INTO `access` (`id`, `idUser`, `idAccessType`, `dataAcces`) VALUES
(1, 2, 1, '2019-09-20 11:50:33'),
(2, 2, 2, '2019-09-20 12:25:47');

-- --------------------------------------------------------

--
-- Estrutura da tabela `accesstypes`
--

CREATE TABLE IF NOT EXISTS `accesstypes` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `accessType` varchar(100) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1 AUTO_INCREMENT=3 ;

--
-- Extraindo dados da tabela `accesstypes`
--

INSERT INTO `accesstypes` (`id`, `accessType`) VALUES
(1, 'Login'),
(2, 'Logout');

-- --------------------------------------------------------

--
-- Estrutura da tabela `atributospessoais`
--

CREATE TABLE IF NOT EXISTS `atributospessoais` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `atributoPessoal` varchar(100) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1 AUTO_INCREMENT=2 ;

--
-- Extraindo dados da tabela `atributospessoais`
--

INSERT INTO `atributospessoais` (`id`, `atributoPessoal`) VALUES
(1, 'Data de Nascimento');

-- --------------------------------------------------------

--
-- Estrutura da tabela `contacts`
--

CREATE TABLE IF NOT EXISTS `contacts` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `idContactType` int(11) NOT NULL,
  `idPerson` int(11) NOT NULL,
  `contact` varchar(100) NOT NULL,
  PRIMARY KEY (`id`),
  KEY `idPerson` (`idPerson`),
  KEY `idContactType` (`idContactType`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1 AUTO_INCREMENT=4 ;

--
-- Extraindo dados da tabela `contacts`
--

INSERT INTO `contacts` (`id`, `idContactType`, `idPerson`, `contact`) VALUES
(1, 2, 4, 'mloureiro@'),
(2, 3, 4, '911222333'),
(3, 4, 4, 'ml-skype');

-- --------------------------------------------------------

--
-- Estrutura da tabela `contacttypes`
--

CREATE TABLE IF NOT EXISTS `contacttypes` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `contactType` varchar(100) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1 AUTO_INCREMENT=5 ;

--
-- Extraindo dados da tabela `contacttypes`
--

INSERT INTO `contacttypes` (`id`, `contactType`) VALUES
(1, 'Face'),
(2, 'Email'),
(3, 'Mobile phone'),
(4, 'Skype');

-- --------------------------------------------------------

--
-- Estrutura da tabela `persons`
--

CREATE TABLE IF NOT EXISTS `persons` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `idUser` int(11) NOT NULL,
  `firstName` varchar(100) NOT NULL,
  `lastName` varchar(100) NOT NULL,
  PRIMARY KEY (`id`),
  KEY `idUser` (`idUser`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1 AUTO_INCREMENT=5 ;

--
-- Extraindo dados da tabela `persons`
--

INSERT INTO `persons` (`id`, `idUser`, `firstName`, `lastName`) VALUES
(4, 2, 'Manuel', 'Loureiro');

-- --------------------------------------------------------

--
-- Estrutura da tabela `pessoas_atributospessoais`
--

CREATE TABLE IF NOT EXISTS `pessoas_atributospessoais` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `idPessoa` int(11) NOT NULL,
  `idAtributoPessoal` int(11) NOT NULL,
  `atributo` varchar(100) NOT NULL,
  PRIMARY KEY (`id`),
  KEY `idPessoa` (`idPessoa`),
  KEY `idAtributoPessoal` (`idAtributoPessoal`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1 AUTO_INCREMENT=2 ;

--
-- Extraindo dados da tabela `pessoas_atributospessoais`
--

INSERT INTO `pessoas_atributospessoais` (`id`, `idPessoa`, `idAtributoPessoal`, `atributo`) VALUES
(1, 4, 1, '1973');

-- --------------------------------------------------------

--
-- Estrutura da tabela `users`
--

CREATE TABLE IF NOT EXISTS `users` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `username` varchar(100) NOT NULL,
  `psw` varchar(100) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1 AUTO_INCREMENT=3 ;

--
-- Extraindo dados da tabela `users`
--

INSERT INTO `users` (`id`, `username`, `psw`) VALUES
(2, 'mloureiro', '123');

-- --------------------------------------------------------

--
-- Stand-in structure for view `v1_access`
--
CREATE TABLE IF NOT EXISTS `v1_access` (
`username` varchar(100)
,`firstName` varchar(100)
,`accessType` varchar(100)
);
-- --------------------------------------------------------

--
-- Stand-in structure for view `v1_login_email`
--
CREATE TABLE IF NOT EXISTS `v1_login_email` (
`contact` varchar(100)
,`firstName` varchar(100)
);
-- --------------------------------------------------------

--
-- Structure for view `v1_access`
--
DROP TABLE IF EXISTS `v1_access`;

CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v1_access` AS select `users`.`username` AS `username`,`persons`.`firstName` AS `firstName`,`accesstypes`.`accessType` AS `accessType` from (((`users` join `access`) join `persons`) join `accesstypes`) where ((`accesstypes`.`id` = `access`.`idAccessType`) and (`users`.`id` = `access`.`idUser`) and (`users`.`id` = `persons`.`idUser`));

-- --------------------------------------------------------

--
-- Structure for view `v1_login_email`
--
DROP TABLE IF EXISTS `v1_login_email`;

CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v1_login_email` AS select `contacts`.`contact` AS `contact`,`persons`.`firstName` AS `firstName` from ((`contacts` join `persons`) join `access`) where ((`contacts`.`idPerson` = `persons`.`id`) and (`contacts`.`idContactType` = 2) and (`access`.`idAccessType` = 1) and (`persons`.`idUser` = `access`.`idUser`));

--
-- Constraints for dumped tables
--

--
-- Limitadores para a tabela `access`
--
ALTER TABLE `access`
  ADD CONSTRAINT `access_ibfk_1` FOREIGN KEY (`idUser`) REFERENCES `users` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
  ADD CONSTRAINT `access_ibfk_2` FOREIGN KEY (`idAccessType`) REFERENCES `accesstypes` (`id`) ON DELETE CASCADE ON UPDATE CASCADE;

--
-- Limitadores para a tabela `contacts`
--
ALTER TABLE `contacts`
  ADD CONSTRAINT `contacts_ibfk_1` FOREIGN KEY (`idContactType`) REFERENCES `contacttypes` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
  ADD CONSTRAINT `contacts_ibfk_2` FOREIGN KEY (`idPerson`) REFERENCES `persons` (`id`) ON DELETE CASCADE ON UPDATE CASCADE;

--
-- Limitadores para a tabela `persons`
--
ALTER TABLE `persons`
  ADD CONSTRAINT `persons_ibfk_1` FOREIGN KEY (`idUser`) REFERENCES `users` (`id`) ON DELETE CASCADE ON UPDATE CASCADE;

--
-- Limitadores para a tabela `pessoas_atributospessoais`
--
ALTER TABLE `pessoas_atributospessoais`
  ADD CONSTRAINT `pessoas_atributospessoais_ibfk_2` FOREIGN KEY (`idAtributoPessoal`) REFERENCES `atributospessoais` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
  ADD CONSTRAINT `pessoas_atributospessoais_ibfk_1` FOREIGN KEY (`idPessoa`) REFERENCES `persons` (`id`) ON DELETE CASCADE ON UPDATE CASCADE;