128 lines
5.3 KiB
SQL
128 lines
5.3 KiB
SQL
-- Create user and grant privileges
|
|
DROP USER IF EXISTS 'app_user'@'localhost';
|
|
CREATE USER 'app_user'@'localhost' IDENTIFIED BY 'Secure@Password123';
|
|
GRANT ALL PRIVILEGES ON real_estate.* TO 'app_user'@'localhost';
|
|
FLUSH PRIVILEGES;
|
|
|
|
-- Create the database
|
|
CREATE DATABASE IF NOT EXISTS `real_estate`;
|
|
USE `real_estate`;
|
|
|
|
-- Table structure for table `owner`
|
|
CREATE TABLE IF NOT EXISTS `owner` (
|
|
`owner_id` INT AUTO_INCREMENT PRIMARY KEY,
|
|
`name` VARCHAR(255) NOT NULL,
|
|
`contact` VARCHAR(20) UNIQUE,
|
|
`email` VARCHAR(255) UNIQUE
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
|
|
|
|
-- Inserting 5 sample records into `owner`
|
|
INSERT INTO `owner` (`name`, `contact`, `email`) VALUES
|
|
('John Smith', '111-222-3333', 'john.smith@example.com'),
|
|
('Jane Doe', '444-555-6666', 'jane.doe@example.com'),
|
|
('Peter Jones', '777-888-9999', 'peter.jones@example.com'),
|
|
('Mary Williams', '123-456-7890', 'mary.williams@example.com'),
|
|
('David Brown', '098-765-4321', 'david.brown@example.com');
|
|
|
|
-- Table structure for table `agent`
|
|
CREATE TABLE IF NOT EXISTS `agent` (
|
|
`agent_id` INT AUTO_INCREMENT PRIMARY KEY,
|
|
`name` VARCHAR(255) NOT NULL,
|
|
`contact` VARCHAR(20) UNIQUE,
|
|
`email` VARCHAR(255) UNIQUE
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
|
|
|
|
-- Inserting 5 sample records into `agent`
|
|
INSERT INTO `agent` (`name`, `contact`, `email`) VALUES
|
|
('Agent Alice', '101-202-3030', 'alice@realestate.com'),
|
|
('Agent Bob', '404-505-6060', 'bob@realestate.com'),
|
|
('Agent Charlie', '707-808-9090', 'charlie@realestate.com'),
|
|
('Agent Diana', '112-223-3344', 'diana@realestate.com'),
|
|
('Agent Eve', '556-667-7788', 'eve@realestate.com');
|
|
|
|
-- Table structure for table `clients`
|
|
CREATE TABLE IF NOT EXISTS `clients` (
|
|
`client_id` INT AUTO_INCREMENT PRIMARY KEY,
|
|
`name` VARCHAR(255) NOT NULL,
|
|
`phone` VARCHAR(20) UNIQUE,
|
|
`email` VARCHAR(255) UNIQUE
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
|
|
|
|
-- Inserting 5 sample records into `clients`
|
|
INSERT INTO `clients` (`name`, `phone`, `email`) VALUES
|
|
('Client Chris', '121-232-3434', 'chris.c@client.com'),
|
|
('Client Dana', '454-565-6767', 'dana.d@client.com'),
|
|
('Client Frank', '787-898-9090', 'frank.f@client.com'),
|
|
('Client Grace', '212-323-4343', 'grace.g@client.com'),
|
|
('Client Heidi', '545-656-7676', 'heidi.h@client.com');
|
|
|
|
-- Table structure for table `properties`
|
|
CREATE TABLE IF NOT EXISTS `properties` (
|
|
`property_id` INT AUTO_INCREMENT PRIMARY KEY,
|
|
`title` VARCHAR(255) NOT NULL,
|
|
`type` VARCHAR(50) NOT NULL,
|
|
`address` VARCHAR(255) NOT NULL,
|
|
`city` VARCHAR(100) NOT NULL,
|
|
`price` DECIMAL(12, 2) NOT NULL,
|
|
`status` ENUM('Available', 'Sold', 'Rented') NOT NULL DEFAULT 'Available',
|
|
`owner_id` INT,
|
|
`agent_id` INT,
|
|
CONSTRAINT `fk_owner` FOREIGN KEY (`owner_id`) REFERENCES `owner` (`owner_id`) ON DELETE SET NULL,
|
|
CONSTRAINT `fk_agent` FOREIGN KEY (`agent_id`) REFERENCES `agent` (`agent_id`) ON DELETE SET NULL,
|
|
CHECK (`price` > 0)
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
|
|
|
|
-- Inserting 5 sample records into `properties`
|
|
INSERT INTO `properties` (`title`, `type`, `address`, `city`, `price`, `status`, `owner_id`, `agent_id`) VALUES
|
|
('Modern Downtown Loft', 'House', '123 Main St', 'Metropolis', 1200000.00, 'Available', 1, 1),
|
|
('Suburban Family Home', 'House', '456 Oak Ave', 'Smallville', 750000.00, 'Available', 2, 2),
|
|
('Luxury Beachfront Villa', 'House', '789 Ocean Dr', 'Coast City', 2500000.00, 'Sold', 3, 1),
|
|
('Cozy Countryside Cottage', 'House', '101 Pine Ln', 'Green Valley', 450000.00, 'Rented', 4, 3),
|
|
('High-Rise Apartment', 'Apartment', '212 Sky Blvd', 'Metropolis', 950000.00, 'Available', 5, 4);
|
|
|
|
-- Table structure for table `transactions`
|
|
CREATE TABLE IF NOT EXISTS `transactions` (
|
|
`transaction_id` INT AUTO_INCREMENT PRIMARY KEY,
|
|
`type` ENUM('Sale', 'Rent') NOT NULL,
|
|
`amount` DECIMAL(12, 2) NOT NULL,
|
|
`date` DATE NOT NULL,
|
|
`property_id` INT,
|
|
`agent_id` INT,
|
|
`client_id` INT,
|
|
UNIQUE (`property_id`), -- A property can only be in one final transaction
|
|
CONSTRAINT `fk_trans_property` FOREIGN KEY (`property_id`) REFERENCES `properties` (`property_id`) ON DELETE CASCADE,
|
|
CONSTRAINT `fk_trans_agent` FOREIGN KEY (`agent_id`) REFERENCES `agent` (`agent_id`) ON DELETE SET NULL,
|
|
CONSTRAINT `fk_trans_client` FOREIGN KEY (`client_id`) REFERENCES `clients` (`client_id`) ON DELETE SET NULL
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
|
|
|
|
-- Inserting 2 sample records into `transactions`
|
|
INSERT INTO `transactions` (`type`, `amount`, `date`, `property_id`, `agent_id`, `client_id`) VALUES
|
|
('Sale', 2450000.00, '2024-05-20', 3, 1, 3),
|
|
('Rent', 3000.00, '2024-06-01', 4, 3, 4);
|
|
|
|
-- SQL Views
|
|
-- View for Available Properties
|
|
CREATE OR REPLACE VIEW `AvailableProperties` AS
|
|
SELECT `property_id`, `title`, `type`, `address`, `city`, `price`
|
|
FROM `properties`
|
|
WHERE `status` = 'Available';
|
|
|
|
-- View for High Value Properties
|
|
CREATE OR REPLACE VIEW `HighValueProperties` AS
|
|
SELECT `property_id`, `title`, `type`, `address`, `city`, `price`
|
|
FROM `properties`
|
|
WHERE `price` > 1000000;
|
|
|
|
-- Trigger to update property status after a transaction
|
|
DELIMITER $$
|
|
CREATE TRIGGER `after_transaction_insert`
|
|
AFTER INSERT ON `transactions`
|
|
FOR EACH ROW
|
|
BEGIN
|
|
IF NEW.type = 'Sale' THEN
|
|
UPDATE `properties` SET `status` = 'Sold' WHERE `property_id` = NEW.property_id;
|
|
ELSEIF NEW.type = 'Rent' THEN
|
|
UPDATE `properties` SET `status` = 'Rented' WHERE `property_id` = NEW.property_id;
|
|
END IF;
|
|
END$$
|
|
DELIMITER ; |