-- http://www.phpmyadmin.net
--




--
-- Database: `frampsSQLByExample`
--

CREATE DATABASE frampsSQLByExample;

USE frampsSQLByExample;

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

--
-- Table structure for table `Address`
--

CREATE TABLE IF NOT EXISTS `Address` (
  `id` int(11) NOT NULL,
  `City_Id` int(11) NOT NULL,
  `Street` varchar(45) COLLATE utf8_unicode_ci NOT NULL,
  `No` varchar(45) COLLATE utf8_unicode_ci DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

--
-- Dumping data for table `Address`
--

INSERT INTO `Address` (`id`, `City_Id`, `Street`, `No`) VALUES
(1, 1, 'Bondstreet', '13'),
(2, 2, 'PlaceDeLetoile', '45'),
(3, 3, 'BarneysStreet', '5'),
(4, 4, 'ShiningStreet', '10');

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

--
-- Table structure for table `City`
--

CREATE TABLE IF NOT EXISTS `City` (
  `id` int(11) NOT NULL,
  `Name` varchar(45) COLLATE utf8_unicode_ci NOT NULL,
  `ZIP` int(11) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

--
-- Dumping data for table `City`
--

INSERT INTO `City` (`id`, `Name`, `ZIP`) VALUES
(1, 'Rochester', 87656),
(2, 'Paris', 1234),
(3, 'Wien', 52637),
(4, 'Berlin', 12987),
(5, 'New York', 65432);

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

--
-- Stand-in structure for view `fatherOnly`
--
CREATE TABLE IF NOT EXISTS `fatherOnly` (
`LastName` varchar(45)
,`FirstName` varchar(45)
);
-- --------------------------------------------------------

--
-- Table structure for table `Friend`
--

CREATE TABLE IF NOT EXISTS `Friend` (
  `id` int(11) NOT NULL,
  `from_Id` int(11) NOT NULL,
  `to_Id` int(11) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

--
-- Dumping data for table `Friend`
--

INSERT INTO `Friend` (`id`, `from_Id`, `to_Id`) VALUES
(1, 3, 9),
(2, 9, 3),
(3, 4, 10),
(4, 10, 4),
(5, 3, 9),
(6, 9, 3);

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

--
-- Table structure for table `Message`
--

CREATE TABLE IF NOT EXISTS `Message` (
  `id` int(11) NOT NULL,
  `from_Id` int(11) NOT NULL,
  `to_ID` int(11) NOT NULL,
  `message` varchar(45) COLLATE utf8_unicode_ci DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

--
-- Dumping data for table `Message`
--

INSERT INTO `Message` (`id`, `from_Id`, `to_ID`, `message`) VALUES
(1, 1, 2, 'Nice to meet you'),
(2, 2, 1, 'I also enjoy to meet you'),
(3, 3, 1, 'My system crashed. Can you help me?'),
(4, 3, 2, 'I''m hungry'),
(5, 2, 3, 'Just visit MC''Donalds'),
(6, 1, 3, 'Restart your system'),
(7, 3, 1, 'Could you help me with GMAT?'),
(8, 3, 1, 'Don''t forget me and buy a big mac for me');

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

--
-- Stand-in structure for view `motherOnly`
--
CREATE TABLE IF NOT EXISTS `motherOnly` (
`LastName` varchar(45)
,`FirstName` varchar(45)
);
-- --------------------------------------------------------

--
-- Table structure for table `Person`
--

CREATE TABLE IF NOT EXISTS `Person` (
  `id` int(11) NOT NULL,
  `LastName` varchar(45) COLLATE utf8_unicode_ci NOT NULL,
  `FirstName` varchar(45) COLLATE utf8_unicode_ci NOT NULL,
  `Father_Id` int(11) DEFAULT NULL,
  `Mother_Id` int(11) DEFAULT NULL,
  `Address_Id` int(11) DEFAULT NULL,
  `Gender` char(1) COLLATE utf8_unicode_ci DEFAULT NULL,
  `Height` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

--
-- Dumping data for table `Person`
--

INSERT INTO `Person` (`id`, `LastName`, `FirstName`, `Father_Id`, `Mother_Id`, `Address_Id`, `Gender`, `Height`) VALUES
(1, 'Turner', 'Peter', 6, 5, 1, 'M', 180),
(2, 'Turner', 'Angie', NULL, 8, 1, 'F', 160),
(3, 'Turner', 'Marvin', 1, 2, 2, 'M', 185),
(4, 'Turner', 'Roxanne', 1, 2, 1, 'F', 155),
(5, 'Turner', 'Wendy', 8, NULL, 3, 'F', 167),
(6, 'Turner', 'Richard', NULL, NULL, 3, 'M', 162),
(8, 'Wilson', 'Hilary', NULL, NULL, 4, 'F', 169),
(7, 'Wilson', 'Woodrow', NULL, NULL, 4, 'M', 174),
(10, 'Chen', 'Walter', NULL, NULL, NULL, 'M', 156),
(9, 'Robinson', 'Rick', NULL, NULL, NULL, 'M', 189);

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

--
-- Table structure for table `Sibling`
--

CREATE TABLE IF NOT EXISTS `Sibling` (
  `id` int(11) NOT NULL,
  `from_Id` int(11) NOT NULL,
  `to_Id` int(11) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

--
-- Dumping data for table `Sibling`
--

INSERT INTO `Sibling` (`id`, `from_Id`, `to_Id`) VALUES
(1, 3, 4),
(2, 4, 3);

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

--
-- Structure for view `fatherOnly`
--

CREATE ALGORITHM=UNDEFINED DEFINER=`peter`@`localhost` SQL SECURITY DEFINER VIEW `fatherOnly` AS select `Person`.`LastName` AS `LastName`,`Person`.`FirstName` AS `FirstName` from `Person` where (isnull(`Person`.`Father_Id`) and (`Person`.`Mother_Id` is not null));

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

--
-- Structure for view `motherOnly`
--

CREATE ALGORITHM=UNDEFINED DEFINER=`peter`@`localhost` SQL SECURITY DEFINER VIEW `motherOnly` AS select `Person`.`LastName` AS `LastName`,`Person`.`FirstName` AS `FirstName` from `Person` where ((`Person`.`Father_Id` is not null) and isnull(`Person`.`Mother_Id`));

