305 lines
10 KiB
SQL
305 lines
10 KiB
SQL
-- MariaDB dump 10.19 Distrib 10.9.4-MariaDB, for Linux (x86_64)
|
|
--
|
|
-- Host: mysql.cs.nott.ac.uk Database: psxrp11_dbcw
|
|
-- ------------------------------------------------------
|
|
-- Server version 5.5.60-MariaDB
|
|
|
|
/*!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 */;
|
|
/*!40103 SET @OLD_TIME_ZONE=@@TIME_ZONE */;
|
|
/*!40103 SET TIME_ZONE='+00:00' */;
|
|
/*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */;
|
|
/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;
|
|
/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */;
|
|
/*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */;
|
|
|
|
--
|
|
-- Table structure for table `Fines`
|
|
--
|
|
|
|
DROP TABLE IF EXISTS `Fines`;
|
|
/*!40101 SET @saved_cs_client = @@character_set_client */;
|
|
/*!40101 SET character_set_client = utf8 */;
|
|
CREATE TABLE `Fines` (
|
|
`Fine_ID` int(11) NOT NULL AUTO_INCREMENT,
|
|
`Fine_Amount` int(11) NOT NULL,
|
|
`Fine_Points` int(11) NOT NULL,
|
|
`Incident_ID` int(11) NOT NULL,
|
|
PRIMARY KEY (`Fine_ID`),
|
|
KEY `Incident_ID` (`Incident_ID`),
|
|
CONSTRAINT `fk_fines` FOREIGN KEY (`Incident_ID`) REFERENCES `Incident` (`Incident_ID`)
|
|
) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=latin1;
|
|
/*!40101 SET character_set_client = @saved_cs_client */;
|
|
|
|
--
|
|
-- Dumping data for table `Fines`
|
|
--
|
|
|
|
LOCK TABLES `Fines` WRITE;
|
|
/*!40000 ALTER TABLE `Fines` DISABLE KEYS */;
|
|
INSERT INTO `Fines` (`Fine_ID`, `Fine_Amount`, `Fine_Points`, `Incident_ID`) VALUES (1,2000,6,3),
|
|
(2,50,0,2),
|
|
(3,500,3,4),
|
|
(5,50,2,3);
|
|
/*!40000 ALTER TABLE `Fines` ENABLE KEYS */;
|
|
UNLOCK TABLES;
|
|
|
|
--
|
|
-- Table structure for table `Incident`
|
|
--
|
|
|
|
DROP TABLE IF EXISTS `Incident`;
|
|
/*!40101 SET @saved_cs_client = @@character_set_client */;
|
|
/*!40101 SET character_set_client = utf8 */;
|
|
CREATE TABLE `Incident` (
|
|
`Incident_ID` int(11) NOT NULL AUTO_INCREMENT,
|
|
`Vehicle_ID` int(11) DEFAULT NULL,
|
|
`People_ID` int(11) DEFAULT NULL,
|
|
`Incident_Date` date NOT NULL,
|
|
`Incident_Report` varchar(500) NOT NULL,
|
|
`Offence_ID` int(11) DEFAULT NULL,
|
|
PRIMARY KEY (`Incident_ID`),
|
|
KEY `fk_incident_vehicle` (`Vehicle_ID`),
|
|
KEY `fk_incident_people` (`People_ID`),
|
|
KEY `fk_incident_offence` (`Offence_ID`),
|
|
CONSTRAINT `fk_incident_offence` FOREIGN KEY (`Offence_ID`) REFERENCES `Offence` (`Offence_ID`),
|
|
CONSTRAINT `fk_incident_people` FOREIGN KEY (`People_ID`) REFERENCES `People` (`People_ID`),
|
|
CONSTRAINT `fk_incident_vehicle` FOREIGN KEY (`Vehicle_ID`) REFERENCES `Vehicle` (`Vehicle_ID`)
|
|
) ENGINE=InnoDB AUTO_INCREMENT=9 DEFAULT CHARSET=latin1;
|
|
/*!40101 SET character_set_client = @saved_cs_client */;
|
|
|
|
--
|
|
-- Dumping data for table `Incident`
|
|
--
|
|
|
|
LOCK TABLES `Incident` WRITE;
|
|
/*!40000 ALTER TABLE `Incident` DISABLE KEYS */;
|
|
INSERT INTO `Incident` (`Incident_ID`, `Vehicle_ID`, `People_ID`, `Incident_Date`, `Incident_Report`, `Offence_ID`) VALUES (1,12,4,'2017-12-01','40mph in a 30 limit',1),
|
|
(2,20,8,'2017-11-01','Double parked',4),
|
|
(3,13,4,'2017-09-17','110mph on motorway',1),
|
|
(4,14,2,'2017-08-22','Failure to stop at a red light - travelling 25mph',8),
|
|
(5,13,4,'2017-10-17','Not wearing a seatbelt on the M1',3);
|
|
/*!40000 ALTER TABLE `Incident` ENABLE KEYS */;
|
|
UNLOCK TABLES;
|
|
|
|
--
|
|
-- Table structure for table `Logs`
|
|
--
|
|
|
|
DROP TABLE IF EXISTS `Logs`;
|
|
/*!40101 SET @saved_cs_client = @@character_set_client */;
|
|
/*!40101 SET character_set_client = utf8 */;
|
|
CREATE TABLE `Logs` (
|
|
`Logs_ID` int(11) NOT NULL AUTO_INCREMENT,
|
|
`Logs_type` varchar(100) DEFAULT NULL,
|
|
`Users_username` varchar(100) DEFAULT NULL,
|
|
`Logs_date` datetime DEFAULT NULL,
|
|
PRIMARY KEY (`Logs_ID`),
|
|
KEY `fk_logs_users` (`Users_username`),
|
|
CONSTRAINT `fk_logs_users` FOREIGN KEY (`Users_username`) REFERENCES `Users` (`Users_username`)
|
|
) ENGINE=InnoDB AUTO_INCREMENT=12 DEFAULT CHARSET=latin1;
|
|
/*!40101 SET character_set_client = @saved_cs_client */;
|
|
|
|
--
|
|
-- Dumping data for table `Logs`
|
|
--
|
|
|
|
LOCK TABLES `Logs` WRITE;
|
|
/*!40000 ALTER TABLE `Logs` DISABLE KEYS */;
|
|
INSERT INTO `Logs` (`Logs_ID`, `Logs_type`, `Users_username`, `Logs_date`) VALUES (9,'Add Report','daniels','2022-12-16 13:14:34'),
|
|
(10,'Add Report','daniels','2022-12-16 13:15:33'),
|
|
(11,'Add Report','daniels','2022-12-16 13:19:58');
|
|
/*!40000 ALTER TABLE `Logs` ENABLE KEYS */;
|
|
UNLOCK TABLES;
|
|
|
|
--
|
|
-- Table structure for table `Offence`
|
|
--
|
|
|
|
DROP TABLE IF EXISTS `Offence`;
|
|
/*!40101 SET @saved_cs_client = @@character_set_client */;
|
|
/*!40101 SET character_set_client = utf8 */;
|
|
CREATE TABLE `Offence` (
|
|
`Offence_ID` int(11) NOT NULL AUTO_INCREMENT,
|
|
`Offence_description` varchar(50) NOT NULL,
|
|
`Offence_maxFine` int(11) NOT NULL,
|
|
`Offence_maxPoints` int(11) NOT NULL,
|
|
PRIMARY KEY (`Offence_ID`)
|
|
) ENGINE=InnoDB AUTO_INCREMENT=14 DEFAULT CHARSET=latin1;
|
|
/*!40101 SET character_set_client = @saved_cs_client */;
|
|
|
|
--
|
|
-- Dumping data for table `Offence`
|
|
--
|
|
|
|
LOCK TABLES `Offence` WRITE;
|
|
/*!40000 ALTER TABLE `Offence` DISABLE KEYS */;
|
|
INSERT INTO `Offence` (`Offence_ID`, `Offence_description`, `Offence_maxFine`, `Offence_maxPoints`) VALUES (1,'Speeding',1000,3),
|
|
(2,'Speeding on a motorway',2500,6),
|
|
(3,'Seat belt offence',500,0),
|
|
(4,'Illegal parking',500,0),
|
|
(5,'Drink driving',10000,11),
|
|
(6,'Driving without a licence',10000,0),
|
|
(7,'Driving without a licence',10000,0),
|
|
(8,'Traffic light offences',1000,3),
|
|
(9,'Cycling on pavement',500,0),
|
|
(10,'Failure to have control of vehicle',1000,3),
|
|
(11,'Dangerous driving',1000,11),
|
|
(12,'Careless driving',5000,6),
|
|
(13,'Dangerous cycling',2500,0);
|
|
/*!40000 ALTER TABLE `Offence` ENABLE KEYS */;
|
|
UNLOCK TABLES;
|
|
|
|
--
|
|
-- Table structure for table `Ownership`
|
|
--
|
|
|
|
DROP TABLE IF EXISTS `Ownership`;
|
|
/*!40101 SET @saved_cs_client = @@character_set_client */;
|
|
/*!40101 SET character_set_client = utf8 */;
|
|
CREATE TABLE `Ownership` (
|
|
`People_ID` int(11) NOT NULL,
|
|
`Vehicle_ID` int(11) NOT NULL,
|
|
KEY `fk_people` (`People_ID`),
|
|
KEY `fk_vehicle` (`Vehicle_ID`),
|
|
CONSTRAINT `fk_person` FOREIGN KEY (`People_ID`) REFERENCES `People` (`People_ID`),
|
|
CONSTRAINT `fk_vehicle` FOREIGN KEY (`Vehicle_ID`) REFERENCES `Vehicle` (`Vehicle_ID`)
|
|
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
|
|
/*!40101 SET character_set_client = @saved_cs_client */;
|
|
|
|
--
|
|
-- Dumping data for table `Ownership`
|
|
--
|
|
|
|
LOCK TABLES `Ownership` WRITE;
|
|
/*!40000 ALTER TABLE `Ownership` DISABLE KEYS */;
|
|
INSERT INTO `Ownership` (`People_ID`, `Vehicle_ID`) VALUES (3,12),
|
|
(8,20),
|
|
(4,15),
|
|
(4,13),
|
|
(1,16),
|
|
(2,14),
|
|
(5,17),
|
|
(6,18),
|
|
(7,21),
|
|
(1,27),
|
|
(1,32),
|
|
(1,12),
|
|
(1,12),
|
|
(1,12);
|
|
/*!40000 ALTER TABLE `Ownership` ENABLE KEYS */;
|
|
UNLOCK TABLES;
|
|
|
|
--
|
|
-- Table structure for table `People`
|
|
--
|
|
|
|
DROP TABLE IF EXISTS `People`;
|
|
/*!40101 SET @saved_cs_client = @@character_set_client */;
|
|
/*!40101 SET character_set_client = utf8 */;
|
|
CREATE TABLE `People` (
|
|
`People_ID` int(11) NOT NULL AUTO_INCREMENT,
|
|
`People_name` varchar(50) NOT NULL,
|
|
`People_address` varchar(50) DEFAULT NULL,
|
|
`People_licence` varchar(16) DEFAULT NULL,
|
|
PRIMARY KEY (`People_ID`),
|
|
UNIQUE KEY `People_pk` (`People_licence`)
|
|
) ENGINE=InnoDB AUTO_INCREMENT=18 DEFAULT CHARSET=latin1;
|
|
/*!40101 SET character_set_client = @saved_cs_client */;
|
|
|
|
--
|
|
-- Dumping data for table `People`
|
|
--
|
|
|
|
LOCK TABLES `People` WRITE;
|
|
/*!40000 ALTER TABLE `People` DISABLE KEYS */;
|
|
INSERT INTO `People` (`People_ID`, `People_name`, `People_address`, `People_licence`) VALUES (1,'James Smith','23 Barnsdale Road, Leicester','SMITH92LDOFJJ829'),
|
|
(2,'Jennifer Allen','46 Bramcote Drive, Nottingham','ALLEN88K23KLR9B3'),
|
|
(3,'John Myers','323 Derby Road, Nottingham','MYERS99JDW8REWL3'),
|
|
(4,'James Smith','26 Devonshire Avenue, Nottingham','SMITHR004JFS20TR'),
|
|
(5,'Terry Brown','7 Clarke Rd, Nottingham','BROWND3PJJ39DLFG'),
|
|
(6,'Mary Adams','38 Thurman St, Nottingham','ADAMSH9O3JRHH107'),
|
|
(7,'Neil Becker','6 Fairfax Close, Nottingham','BECKE88UPR840F9R'),
|
|
(8,'Angela Smith','30 Avenue Road, Grantham','SMITH222LE9FJ5DS'),
|
|
(9,'Xene Medora','22 House Drive, West Bridgford','MEDORH914ANBB223'),
|
|
(17,'Rohit Pai','123, midleton road, London','PAI99909048RA9YX');
|
|
/*!40000 ALTER TABLE `People` ENABLE KEYS */;
|
|
UNLOCK TABLES;
|
|
|
|
--
|
|
-- Table structure for table `Users`
|
|
--
|
|
|
|
DROP TABLE IF EXISTS `Users`;
|
|
/*!40101 SET @saved_cs_client = @@character_set_client */;
|
|
/*!40101 SET character_set_client = utf8 */;
|
|
CREATE TABLE `Users` (
|
|
`Users_username` varchar(100) NOT NULL,
|
|
`Users_password` varchar(100) DEFAULT NULL,
|
|
`Users_admin` tinyint(4) DEFAULT '0',
|
|
PRIMARY KEY (`Users_username`)
|
|
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
|
|
/*!40101 SET character_set_client = @saved_cs_client */;
|
|
|
|
--
|
|
-- Dumping data for table `Users`
|
|
--
|
|
|
|
LOCK TABLES `Users` WRITE;
|
|
/*!40000 ALTER TABLE `Users` DISABLE KEYS */;
|
|
INSERT INTO `Users` (`Users_username`, `Users_password`, `Users_admin`) VALUES ('daniels','copper99',1),
|
|
('mcnulty','plod123',0),
|
|
('moreland','fuzz42',0);
|
|
/*!40000 ALTER TABLE `Users` ENABLE KEYS */;
|
|
UNLOCK TABLES;
|
|
|
|
--
|
|
-- Table structure for table `Vehicle`
|
|
--
|
|
|
|
DROP TABLE IF EXISTS `Vehicle`;
|
|
/*!40101 SET @saved_cs_client = @@character_set_client */;
|
|
/*!40101 SET character_set_client = utf8 */;
|
|
CREATE TABLE `Vehicle` (
|
|
`Vehicle_ID` int(11) NOT NULL AUTO_INCREMENT,
|
|
`Vehicle_type` varchar(20) NOT NULL,
|
|
`Vehicle_colour` varchar(20) NOT NULL,
|
|
`Vehicle_licence` varchar(7) DEFAULT NULL,
|
|
PRIMARY KEY (`Vehicle_ID`)
|
|
) ENGINE=InnoDB AUTO_INCREMENT=33 DEFAULT CHARSET=latin1;
|
|
/*!40101 SET character_set_client = @saved_cs_client */;
|
|
|
|
--
|
|
-- Dumping data for table `Vehicle`
|
|
--
|
|
|
|
LOCK TABLES `Vehicle` WRITE;
|
|
/*!40000 ALTER TABLE `Vehicle` DISABLE KEYS */;
|
|
INSERT INTO `Vehicle` (`Vehicle_ID`, `Vehicle_type`, `Vehicle_colour`, `Vehicle_licence`) VALUES (12,'Ford Fiesta','Blue','LB15AJL'),
|
|
(13,'Ferrari 458','Red','MY64PRE'),
|
|
(14,'Vauxhall Astra','Silver','FD65WPQ'),
|
|
(15,'Honda Civic','Green','FJ17AUG'),
|
|
(16,'Toyota Prius','Silver','FP16KKE'),
|
|
(17,'Ford Mondeo','Black','FP66KLM'),
|
|
(18,'Ford Focus','White','DJ14SLE'),
|
|
(20,'Nissan Pulsar','Red','NY64KWD'),
|
|
(21,'Renault Scenic','Silver','BC16OEA'),
|
|
(22,'Hyundai i30','Grey','AD223NG'),
|
|
(27,'sdfsdf','sdff','3545fgg'),
|
|
(32,'Ford Focus','Red','EA22HNT');
|
|
/*!40000 ALTER TABLE `Vehicle` ENABLE KEYS */;
|
|
UNLOCK TABLES;
|
|
/*!40103 SET TIME_ZONE=@OLD_TIME_ZONE */;
|
|
|
|
/*!40101 SET SQL_MODE=@OLD_SQL_MODE */;
|
|
/*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */;
|
|
/*!40014 SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS */;
|
|
/*!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 */;
|
|
/*!40111 SET SQL_NOTES=@OLD_SQL_NOTES */;
|
|
|
|
-- Dump completed on 2022-12-16 13:25:56
|