Vehicle Parking System: Database & SQL

Normalization

A database design method called normalization avoids data duplication and gets rid of undesired traits like Insertion, Update, and Deletion Anomalies. Using relationships, normalization rules break up larger tables into smaller ones. SQL normalization serves the dual purposes of removing unnecessary (repetitive) data and ensuring logical data storage.

Normalization Steps

1NF (First Normal Form)

If a table's atomicity is 1, it is said to be in its first normal form.
Multi-valued attributes, composite attributes, and their mixtures are not allowed by the First Normal Form.
Our Tables are already in 2NF

2NF (Second Normal Form)

The table must first be in the First Normal Form in order for it to be in the Second Normal Form.
There shouldn't be any partial dependencies in the table.

3NF (Third Normal Form)

The table must first be in the Second Normal Form in order for it to be in the Third Normal Form.
The third Normal Form guarantees that data duplication is minimized. Data integrity is also achieved using it.

Relational database

SQL for table creation

SQL for create Admin Table

CREATE TABLE `admin` (`id` int(11) NOT NULL AUTO_INCREMENT, `name` varchar(45) DEFAULT NULL, `email` varchar(45) DEFAULT NULL, `username` varchar(45) DEFAULT NULL, `password` varchar(45) DEFAULT NULL, PRIMARY KEY (`id`) ) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8mb4;

SQL for create User Table

CREATE TABLE `users` (`id` int(11) NOT NULL AUTO_INCREMENT, `name` varchar(45) DEFAULT NULL, `address` varchar(45) DEFAULT NULL, `mobile` varchar(45) DEFAULT NULL, `username` varchar(45) DEFAULT NULL, `password` varchar(45) DEFAULT NULL, `status` varchar(45) DEFAULT NULL, `admin` int(11) NOT NULL, PRIMARY KEY (`id`), KEY `admin_index` (`admin`), CONSTRAINT `admin_fk` FOREIGN KEY (`admin`) REFERENCES `admin` (`id`) ON DELETE NO ACTION ON UPDATE NO ACTION) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8mb4;

SQL for create Vehicle Category Table

CREATE TABLE `vehiclecategories` (`id` int(11) NOT NULL AUTO_INCREMENT, `categoryName` varchar(45) DEFAULT NULL, `price` varchar(45) DEFAULT NULL, `user` int(11) NOT NULL, `status` varchar(45) DEFAULT NULL, PRIMARY KEY (`id`), KEY `user_category_fk_idx` (`user`), CONSTRAINT `user_category_fk` FOREIGN KEY (`user`) REFERENCES `users` (`id`) ON DELETE NO ACTION ON UPDATE NO ACTION) ENGINE=InnoDB AUTO_INCREMENT=7 DEFAULT CHARSET=utf8mb4;

SQL for create Vehicle Parking Details Table

CREATE TABLE `parkingdetails` (`id` int(11) NOT NULL AUTO_INCREMENT, `vehicleNumber` varchar(45) DEFAULT NULL, `ownerName` varchar(45) DEFAULT NULL, `mobileNumber` varchar(45) DEFAULT NULL, `parkingNumber` varchar(45) DEFAULT NULL, `parkingStatus` varchar(45) DEFAULT NULL, `vehicleCategory` int(11) NOT NULL, `user` int(11) NOT NULL, `inTime` datetime DEFAULT current_timestamp(), `outtime` datetime DEFAULT NULL ON UPDATE current_timestamp(), PRIMARY KEY (`id`), KEY `category_fk_index` (`vehicleCategory`), KEY `user_fk_index` (`user`), CONSTRAINT `category_fk` FOREIGN KEY (`vehicleCategory`) REFERENCES `vehiclecategories` (`id`) ON DELETE NO ACTION ON UPDATE NO ACTION, CONSTRAINT `user_fk` FOREIGN KEY (`user`) REFERENCES `users` (`id`) ON DELETE NO ACTION ON UPDATE NO ACTION) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8mb4;

SQL for add data

SQL for Insert an Admin

INSERT INTO `carparkdb`.`admin` (`name`,`email`,`username`,`password`) VALUES('bandara','bandara@gmail.com', 'bandara', '12345');

SQL for Insert a User

INSERT INTO `carparkdb`.`users` (`name`,`address`,`mobile`,`username`,`password`,`status`,`admin`) VALUES('user', 'colombo', '1234567890', 'user1', 'pass1', 'active', '2');

SQL for Insert a Vehicle Category

INSERT INTO `carparkdb`.`vehiclecategories` (`categoryName`,`price`,`user`,`status`) VALUES('Cars', '200.00', '2', 'active');

SQL for Insert a Parking Detail

INSERT INTO `carparkdb`.`parkingdetails` (`vehicleNumber`,`ownerName`,`parkingNumber`,`parkingStatus`,`vehicleCategory`,`user`, `mobileNumber`) VALUES('ABC-123', 'madhushan', 'P-002', 'In', '6', '2', '077777777');