For an instructor lead, in-depth look at learning SQL click below.
This blog post offers an overview of how to create a Parking Permit Management System utilizing Structured Query Language (SQL). SQL, renowned for being a powerful language used for managing and manipulating relational databases, can be used for managing parking permit databases efficiently.
Database Design
Main entities for our parking management system could be Permits, Persons, Vehicles and Parking Lots. The main relationships would be a Person has a Permit, a Permit is for a Vehicle, and a Permit gives permission to park in a Parking Lot.
Creating Tables in SQL
First step in the SQL process is to create tables that we need for the database:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 |
CREATE TABLE Persons ( ID INT PRIMARY KEY, Name VARCHAR(30), Address VARCHAR(100) ); CREATE TABLE Vehicles ( ID INT PRIMARY KEY, LicensePlate VARCHAR(7), OwnerID INT, FOREIGN KEY (OwnerID) REFERENCES Persons(ID) ); CREATE TABLE ParkingLots ( ID INT PRIMARY KEY, Location VARCHAR(100), Spaces INT ); CREATE TABLE Permits ( ID INT PRIMARY KEY, VehicleID INT, LotID INT, ExpiryDate DATE, FOREIGN KEY (VehicleID) REFERENCES Vehicles(ID), FOREIGN KEY (LotID) REFERENCES ParkingLots(ID) ); |
Inserting Information into the Table
At this point, each freshly created table is unfortunately empty. Now, let us populate these tables with some data:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 |
INSERT INTO Persons (ID, Name, Address) VALUES (1, 'John Doe', '123 Main St'); INSERT INTO Vehicles (ID, LicensePlate, OwnerID) VALUES (1, 'ABC1234', 1); INSERT INTO ParkingLots (ID, Location, Spaces) VALUES (1, 'Lot A', 100); INSERT INTO Permits (ID, VehicleID, LotID, ExpiryDate) VALUES (1, 1, 1, '2023-12-31'); |
Retrieving Data
Now that we’ve inserted some data, we can retrieve it through SQL’s SELECT operation. Below is the SQL code to select all permits:
|
1 2 3 |
SELECT * FROM Permits; |
Updating Data
Life is full of changes and when data changes, we can change it in our database too with SQL’s UPDATE operation. Let’s extend the expiry date of the permit:
|
1 2 3 4 5 |
UPDATE Permits SET ExpiryDate = '2024-12-31' WHERE ID = 1; |
Deleting Data
Regrettably, not every vehicle stays with us forever. When it’s time for a vehicle to go, we can remove it from our database:
|
1 2 3 |
DELETE FROM Vehicles WHERE ID = 1; |
Conclusion
In conclusion, SQL provides a powerful tool to manage databases effectively. Employing SQL for managing a parking permit system offers you the flexibility of data manipulation hence making database management seamless and efficient. Do remember, these examples are a tiny part of what can be achieved with SQL. Possibilities are endless!