Demo entry 6647199

assigment 4

   

Submitted by anonymous on Oct 19, 2017 at 14:43
Language: SQL. Code size: 11.3 kB.

-- phpMyAdmin SQL Dump
-- version 4.7.0
-- https://www.phpmyadmin.net/
--
-- Host: 127.0.0.1
-- Generation Time: Oct 19, 2017 at 12:43 PM
-- Server version: 10.1.25-MariaDB
-- PHP Version: 7.1.7

SET SQL_MODE = "NO_AUTO_VALUE_ON_ZERO";
SET AUTOCOMMIT = 0;
START TRANSACTION;
SET time_zone = "+00:00";


/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
/*!40101 SET NAMES utf8mb4 */;

--
-- Database: `assignment_4`
--

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

--
-- Table structure for table `assesser`
--

CREATE TABLE `assesser` (
  `ID` int(11) NOT NULL,
  `name` varchar(60) COLLATE utf8_danish_ci NOT NULL,
  `eMail` varchar(250) COLLATE utf8_danish_ci NOT NULL,
  `address` varchar(250) COLLATE utf8_danish_ci NOT NULL,
  `mobileNo` varchar(32) COLLATE utf8_danish_ci NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_danish_ci;

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

--
-- Table structure for table `assesses`
--

CREATE TABLE `assesses` (
  `assesserID` int(11) NOT NULL,
  `storeID` int(11) NOT NULL,
  `manName` varchar(16) COLLATE utf8_danish_ci NOT NULL,
  `date` date NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_danish_ci;

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

--
-- Table structure for table `cameratype`
--

CREATE TABLE `cameratype` (
  `manName` varchar(16) COLLATE utf8_danish_ci NOT NULL,
  `typeName` varchar(60) COLLATE utf8_danish_ci NOT NULL,
  `weight` int(11) NOT NULL,
  `perDayPrice` decimal(9,0) NOT NULL,
  `megapixel` decimal(4,0) NOT NULL,
  `lensManufacturerID` int(11) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_danish_ci;

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

--
-- Table structure for table `contains`
--

CREATE TABLE `contains` (
  `leaseNo` int(11) NOT NULL,
  `storeID` int(11) NOT NULL,
  `equipmentSerialNo` int(11) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_danish_ci;

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

--
-- Table structure for table `ctcmct`
--

CREATE TABLE `ctcmct` (
  `manName` varchar(16) COLLATE utf8_danish_ci NOT NULL,
  `typeName` varchar(60) COLLATE utf8_danish_ci NOT NULL,
  `MemoryCardTypeName` varchar(16) COLLATE utf8_danish_ci NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_danish_ci;

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

--
-- Table structure for table `customer`
--

CREATE TABLE `customer` (
  `eMail` varchar(250) COLLATE utf8_danish_ci NOT NULL,
  `name` varchar(60) COLLATE utf8_danish_ci NOT NULL,
  `address` varchar(250) COLLATE utf8_danish_ci NOT NULL,
  `mobileNo` varchar(32) COLLATE utf8_danish_ci NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_danish_ci;

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

--
-- Table structure for table `equipment`
--

CREATE TABLE `equipment` (
  `serialNo` int(11) NOT NULL,
  `startDate` date NOT NULL,
  `status` text COLLATE utf8_danish_ci NOT NULL,
  `conditionn` int(11) NOT NULL,
  `lensTypeName` varchar(60) COLLATE utf8_danish_ci NOT NULL,
  `lensManName` varchar(16) COLLATE utf8_danish_ci NOT NULL,
  `cameraTypeName` varchar(60) COLLATE utf8_danish_ci NOT NULL,
  `cameraManName` varchar(16) COLLATE utf8_danish_ci NOT NULL,
  `storeID` int(11) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_danish_ci;

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

--
-- Table structure for table `lease`
--

CREATE TABLE `lease` (
  `leaseNo` int(11) NOT NULL,
  `storeID` int(11) NOT NULL,
  `startDate` date NOT NULL,
  `endDate` date NOT NULL,
  `totalPrice` int(11) DEFAULT NULL,
  `extraFee` int(11) DEFAULT NULL,
  `explanation` text COLLATE utf8_danish_ci,
  `status` text COLLATE utf8_danish_ci NOT NULL,
  `customerEmail` varchar(250) COLLATE utf8_danish_ci NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_danish_ci;

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

--
-- Table structure for table `lensmount`
--

CREATE TABLE `lensmount` (
  `ID` int(11) NOT NULL,
  `name` varchar(16) COLLATE utf8_danish_ci NOT NULL,
  `manName` varchar(16) COLLATE utf8_danish_ci NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_danish_ci;

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

--
-- Table structure for table `lenstype`
--

CREATE TABLE `lenstype` (
  `manName` varchar(16) COLLATE utf8_danish_ci NOT NULL,
  `typeName` varchar(60) COLLATE utf8_danish_ci NOT NULL,
  `weight` int(11) NOT NULL,
  `perDayPrice` decimal(9,0) NOT NULL,
  `lenght` int(11) NOT NULL,
  `focalLenght` text COLLATE utf8_danish_ci NOT NULL,
  `maxAperture` decimal(4,0) NOT NULL,
  `filterSize` int(11) NOT NULL,
  `lensMountID` int(11) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_danish_ci;

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

--
-- Table structure for table `manufacturer`
--

CREATE TABLE `manufacturer` (
  `name` varchar(16) COLLATE utf8_danish_ci NOT NULL,
  `country` char(2) COLLATE utf8_danish_ci NOT NULL,
  `serviceURL` varchar(250) COLLATE utf8_danish_ci NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_danish_ci;

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

--
-- Table structure for table `memorycardtype`
--

CREATE TABLE `memorycardtype` (
  `name` varchar(16) COLLATE utf8_danish_ci NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_danish_ci;

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

--
-- Table structure for table `store`
--

CREATE TABLE `store` (
  `ID` int(11) NOT NULL,
  `name` varchar(60) COLLATE utf8_danish_ci NOT NULL,
  `eMail` varchar(250) COLLATE utf8_danish_ci NOT NULL,
  `address` varchar(250) COLLATE utf8_danish_ci NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_danish_ci;

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

--
-- Table structure for table `storephonenum`
--

CREATE TABLE `storephonenum` (
  `tellNo` varchar(32) COLLATE utf8_danish_ci NOT NULL,
  `storeID` int(11) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_danish_ci;

--
-- Indexes for dumped tables
--

--
-- Indexes for table `assesser`
--
ALTER TABLE `assesser`
  ADD PRIMARY KEY (`ID`);

--
-- Indexes for table `assesses`
--
ALTER TABLE `assesses`
  ADD PRIMARY KEY (`assesserID`,`storeID`,`manName`),
  ADD KEY `manName` (`manName`),
  ADD KEY `storeID` (`storeID`);

--
-- Indexes for table `cameratype`
--
ALTER TABLE `cameratype`
  ADD PRIMARY KEY (`manName`,`typeName`),
  ADD KEY `lensManufacturerID` (`lensManufacturerID`);

--
-- Indexes for table `contains`
--
ALTER TABLE `contains`
  ADD PRIMARY KEY (`leaseNo`,`storeID`,`equipmentSerialNo`),
  ADD KEY `equipmentSerialNo` (`equipmentSerialNo`);

--
-- Indexes for table `ctcmct`
--
ALTER TABLE `ctcmct`
  ADD PRIMARY KEY (`manName`,`typeName`,`MemoryCardTypeName`),
  ADD KEY `MemoryCardTypeName` (`MemoryCardTypeName`);

--
-- Indexes for table `customer`
--
ALTER TABLE `customer`
  ADD PRIMARY KEY (`eMail`);

--
-- Indexes for table `equipment`
--
ALTER TABLE `equipment`
  ADD PRIMARY KEY (`serialNo`),
  ADD KEY `cameraManName` (`cameraManName`,`cameraTypeName`),
  ADD KEY `storeID` (`storeID`),
  ADD KEY `lensManName` (`lensManName`,`cameraTypeName`);

--
-- Indexes for table `lease`
--
ALTER TABLE `lease`
  ADD PRIMARY KEY (`leaseNo`,`storeID`),
  ADD KEY `storeID` (`storeID`),
  ADD KEY `customerEmail` (`customerEmail`);

--
-- Indexes for table `lensmount`
--
ALTER TABLE `lensmount`
  ADD PRIMARY KEY (`ID`),
  ADD UNIQUE KEY `name` (`name`),
  ADD KEY `manName` (`manName`);

--
-- Indexes for table `lenstype`
--
ALTER TABLE `lenstype`
  ADD PRIMARY KEY (`manName`,`typeName`),
  ADD KEY `lensMountID` (`lensMountID`);

--
-- Indexes for table `manufacturer`
--
ALTER TABLE `manufacturer`
  ADD PRIMARY KEY (`name`);

--
-- Indexes for table `memorycardtype`
--
ALTER TABLE `memorycardtype`
  ADD PRIMARY KEY (`name`);

--
-- Indexes for table `store`
--
ALTER TABLE `store`
  ADD PRIMARY KEY (`ID`),
  ADD UNIQUE KEY `name` (`name`);

--
-- Indexes for table `storephonenum`
--
ALTER TABLE `storephonenum`
  ADD PRIMARY KEY (`storeID`,`tellNo`);

--
-- AUTO_INCREMENT for dumped tables
--

--
-- AUTO_INCREMENT for table `assesser`
--
ALTER TABLE `assesser`
  MODIFY `ID` int(11) NOT NULL AUTO_INCREMENT;
--
-- AUTO_INCREMENT for table `store`
--
ALTER TABLE `store`
  MODIFY `ID` int(11) NOT NULL AUTO_INCREMENT;
--
-- Constraints for dumped tables
--

--
-- Constraints for table `assesses`
--
ALTER TABLE `assesses`
  ADD CONSTRAINT `assesses_ibfk_1` FOREIGN KEY (`assesserID`) REFERENCES `assesser` (`ID`),
  ADD CONSTRAINT `assesses_ibfk_2` FOREIGN KEY (`manName`) REFERENCES `manufacturer` (`name`),
  ADD CONSTRAINT `assesses_ibfk_3` FOREIGN KEY (`storeID`) REFERENCES `store` (`ID`);

--
-- Constraints for table `cameratype`
--
ALTER TABLE `cameratype`
  ADD CONSTRAINT `cameratype_ibfk_1` FOREIGN KEY (`lensManufacturerID`) REFERENCES `lensmount` (`ID`),
  ADD CONSTRAINT `cameratype_ibfk_2` FOREIGN KEY (`manName`) REFERENCES `manufacturer` (`name`);

--
-- Constraints for table `contains`
--
ALTER TABLE `contains`
  ADD CONSTRAINT `contains_ibfk_1` FOREIGN KEY (`leaseNo`,`storeID`) REFERENCES `lease` (`leaseNo`, `storeID`),
  ADD CONSTRAINT `contains_ibfk_2` FOREIGN KEY (`equipmentSerialNo`) REFERENCES `equipment` (`serialNo`);

--
-- Constraints for table `ctcmct`
--
ALTER TABLE `ctcmct`
  ADD CONSTRAINT `ctcmct_ibfk_1` FOREIGN KEY (`manName`,`typeName`) REFERENCES `cameratype` (`manName`, `typeName`),
  ADD CONSTRAINT `ctcmct_ibfk_2` FOREIGN KEY (`MemoryCardTypeName`) REFERENCES `memorycardtype` (`name`);

--
-- Constraints for table `equipment`
--
ALTER TABLE `equipment`
  ADD CONSTRAINT `equipment_ibfk_1` FOREIGN KEY (`cameraManName`,`cameraTypeName`) REFERENCES `cameratype` (`manName`, `typeName`),
  ADD CONSTRAINT `equipment_ibfk_2` FOREIGN KEY (`storeID`) REFERENCES `store` (`ID`),
  ADD CONSTRAINT `equipment_ibfk_3` FOREIGN KEY (`lensManName`,`cameraTypeName`) REFERENCES `lenstype` (`manName`, `typeName`);

--
-- Constraints for table `lease`
--
ALTER TABLE `lease`
  ADD CONSTRAINT `lease_ibfk_1` FOREIGN KEY (`storeID`) REFERENCES `store` (`ID`),
  ADD CONSTRAINT `lease_ibfk_2` FOREIGN KEY (`customerEmail`) REFERENCES `customer` (`eMail`);

--
-- Constraints for table `lensmount`
--
ALTER TABLE `lensmount`
  ADD CONSTRAINT `lensmount_ibfk_1` FOREIGN KEY (`manName`) REFERENCES `manufacturer` (`name`);

--
-- Constraints for table `lenstype`
--
ALTER TABLE `lenstype`
  ADD CONSTRAINT `lenstype_ibfk_1` FOREIGN KEY (`manName`) REFERENCES `manufacturer` (`name`),
  ADD CONSTRAINT `lenstype_ibfk_2` FOREIGN KEY (`lensMountID`) REFERENCES `lensmount` (`ID`);

--
-- Constraints for table `storephonenum`
--
ALTER TABLE `storephonenum`
  ADD CONSTRAINT `storephonenum_ibfk_1` FOREIGN KEY (`storeID`) REFERENCES `store` (`ID`);
COMMIT;

/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
/*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;
/*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;

This snippet took 0.01 seconds to highlight.

Back to the Entry List or Home.

Delete this entry (admin only).