-- Database: `18SGupta_softwareDB`
--
CREATE DATABASE IF NOT EXISTS `18SGupta_softwareDB` DEFAULT CHARACTER SET utf8mb4
COLLATE utf8mb4_general_ci;
USE `18SGupta_softwareDB`;
-- --------------------------------------------------------
--
-- Table structure for table `softwareinstallation`
--
CREATE TABLE `softwareinstallation` (
`SoftwareID` char(10) NOT NULL,
`ComputerID` char(6) NOT NULL,
`DateInstalled` date DEFAULT NULL,
`StaffID` char(3) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
--
-- Dumping data for table `softwareinstallation`
--
INSERT INTO `softwareinstallation` (`SoftwareID`, `ComputerID`, `DateInstalled`,
`StaffID`) VALUES
('OS1', 'B1031', '2018-04-27', 'AGS'),
('OS1', 'B1032', '2018-04-27', 'AGS'),
('OS1', 'B1033', '2018-04-27', 'AGS'),
('OS1', 'B1039', '2018-04-27', 'PRM'),
('OS1', 'B103SB', '2018-04-27', 'AGS'),
('OS2', 'B103SB', '2018-04-27', 'GSN'),
('PR1', 'B1033', '2018-04-27', 'GSN'),
('PR2', 'B103SB', '2018-04-27', 'GSN'),
('PR3', 'B103SB', '2018-04-27', 'GSN'),
('SS1', 'B103SB', '2018-04-27', 'GSN'),
('SS2', 'B1032', '2018-04-27', 'GSN'),
('SS3', 'B103SB', '2018-04-27', 'GSN'),
('WP1', 'B103SB', '2018-04-27', 'GSN'),
('WP4', 'B1031', '2018-04-27', 'GSN');
-- --------------------------------------------------------
--
-- Table structure for table `softwarelicence`
--
CREATE TABLE `softwarelicence` (
`SoftwareID` char(10) NOT NULL,
`SoftwareNAME` varchar(30) DEFAULT NULL,
`Version` varchar(10) DEFAULT NULL,
`Supplier` varchar(20) DEFAULT NULL,
`DatePurchased` date DEFAULT NULL,
`ExpiryDate` date DEFAULT NULL,
`NoOfLicences` int(11) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
--
-- Dumping data for table `softwarelicence`
--
INSERT INTO `softwarelicence` (`SoftwareID`, `SoftwareNAME`, `Version`, `Supplier`,
`DatePurchased`, `ExpiryDate`, `NoOfLicences`) VALUES
('OS1', 'Windows', '7', 'Microsoft', '2018-04-27', '2021-04-27', 20),
('OS2', 'Windows', '10', 'Microsoft', '2018-04-27', '2021-04-27', 30),
('PR1', 'Powerpoint', '2018', 'Microsoft', '2018-04-27', '2021-04-27', 100),
('PR2', 'Powerpoint', '2016', 'Microsoft', '2018-04-27', '2021-04-27', 120),
('PR3', 'Powerpoint', '2016', 'Microsoft', '2018-04-27', '2021-04-27', 3),
('SS1', 'Excel', '2018', 'Microsoft', '2018-04-27', '2021-04-27', 12),
('SS2', 'Excel', '2016', 'Microsoft', '2018-04-27', '2021-04-27', 20),
('SS3', 'Excel', '2010', 'Microsoft', '2018-04-27', '2021-04-27', 0),
('WP1', 'Word', '2010', 'Microsoft', '2018-04-27', '2021-04-27', 0),
('WP2', 'Word', '2013', 'Microsoft', '2018-04-27', '2021-04-27', 5),
('WP3', 'Word', '2016', 'Microsoft', '2018-04-27', '2021-04-27', 10),
('WP4', 'Word', '2018', 'Microsoft', '2018-04-27', '2021-04-27', 20);
--
-- Indexes for dumped tables
--
--
-- Indexes for table `softwareinstallation`
--
ALTER TABLE `softwareinstallation`
ADD PRIMARY KEY (`SoftwareID`,`ComputerID`);
--
-- Indexes for table `softwarelicence`
--
ALTER TABLE `softwarelicence`
ADD PRIMARY KEY (`SoftwareID`);
--
-- Constraints for dumped tables
--
--
-- Constraints for table `softwareinstallation`
--
ALTER TABLE `softwareinstallation`
ADD CONSTRAINT `softwareinstallation_ibfk_1` FOREIGN KEY (`SoftwareID`)
REFERENCES `softwarelicence` (`SoftwareID`);
--
