Embedded Files
In the intricate and data-intensive environment of the movie_production_companies database, the use of stored procedures stands as a testament to the principles of efficiency, security, and modularity in database management. As I delve into the Stored Procedure Data Definition Language (DDL) section, I aim to provide a comprehensive overview of these powerful tools that form the backbone of many database operations.
Stored procedures in this database are more than just reusable SQL scripts; they are carefully crafted pieces of logic that encapsulate complex operations, ensure data consistency, and optimize performance. Through this section, I will guide you through the intricacies of the DDL for each stored procedure that I have meticulously designed and implemented.
activity_filter
activity_filter
CREATE DEFINER=`root`@`localhost` PROCEDURE `activity_filter`(
IN eventType VARCHAR(50),
IN tableName VARCHAR(50)
)
BEGIN
IF (eventType IS NOT NULL AND eventType <> '') AND (tableName IS NULL OR tableName = '') THEN
SELECT id, table_name, key_attribute, event_description, user, event_timestamp
FROM activity_log
WHERE event_type = eventType;
ELSEIF (tableName IS NOT NULL AND tableName <> '') AND (eventType IS NULL OR eventType = '') THEN
SELECT id, event_type, key_attribute, event_description, user, event_timestamp
FROM activity_log
WHERE table_name = tableName;
ELSEIF (tableName IS NOT NULL AND tableName <> '') AND (eventType IS NOT NULL AND eventType <> '') THEN
SELECT id, key_attribute, event_description, user, event_timestamp
FROM activity_log
WHERE event_type = eventType AND table_name = tableName;
ELSE
SELECT * FROM activity_log;
END IF;
END
activity_log_auto
activity_log_auto
CREATE DEFINER=`root`@`localhost` PROCEDURE `activity_log_auto`(
IN log_type VARCHAR(50),
IN entity VARCHAR(50),
IN key_column VARCHAR(114),
IN log_description VARCHAR(200)
)
BEGIN
INSERT INTO activity_log (event_type, table_name, key_attribute, event_description, user)
VALUES (log_type, entity, key_column, log_description, CURRENT_USER());
END
add_company
add_company
CREATE DEFINER=`root`@`localhost` PROCEDURE `add_company`(
IN `Company Name` VARCHAR(199),
IN `Company Address` VARCHAR(199),
IN `City` VARCHAR(75), IN Zipcode INT,
IN `Organization Kind` VARCHAR(199),
IN `Total Asset` DECIMAL(10, 2),
IN `Total Liability` DECIMAL(10, 2),
IN `Country of Registration` VARCHAR(100),
IN `Date of Registration` DATE)
BEGIN
INSERT INTO company(name, address, city_id, zip_code, kind_of_organization_id,
total_asset, total_liability,
registration_body_id, registration_date)
VALUES(
`Company Name`, `Company Address`,
(SELECT id FROM city WHERE city.name = `City`), Zipcode,
(SELECT id FROM kind_of_organization WHERE name = `Organization Kind`),
`Total Asset`, `Total Liability`,
(SELECT r.id FROM registration_body r, country c WHERE r.country_code = c.code AND c.name = `Country of Registration`),
`Date of Registration`);
END
add_company_department_address
add_company_department_address
CREATE DEFINER=`root`@`localhost` PROCEDURE `add_company_department_address`(
IN dept_name VARCHAR(75),
IN company_name VARCHAR(75),
IN building_name VARCHAR(75),
IN addy VARCHAR(75))
BEGIN
INSERT INTO department_address(department_id, company_id, building, address)
VALUES (
(SELECT id FROM department WHERE name = dept_name),
(SELECT id FROM company WHERE name = company_name),
building_name, addy);
END
add_employee
add_employee
CREATE DEFINER=`root`@`localhost` PROCEDURE `add_employee`(
IN firstname VARCHAR(45),
IN middlename VARCHAR(45),
IN lastname VARCHAR(45),
IN DOB DATE,
IN company_name VARCHAR(144),
IN employeerole VARCHAR(144),
IN commencement_date DATE
)
BEGIN
INSERT INTO employee (
first_name,
middle_name,
last_name,
date_of_birth,
company_id,
employee_role,
date_started
)
VALUES (
firstname,
middlename,
lastname,
DOB,
(SELECT id FROM company WHERE name = company_name),
employeerole,
commencement_date
);
END
add_film
add_film
CREATE DEFINER=`root`@`localhost` PROCEDURE `add_film`(
IN `Title` VARCHAR(115),
IN `Release Year` YEAR,
IN `First Released` DATE
)
BEGIN
INSERT INTO film (
title,
release_year,
first_released
)
VALUES (
`Title`,
`Release Year`,
`First Released`
);
END
add_grant_request
add_grant_request
CREATE DEFINER=`root`@`localhost` PROCEDURE `add_grant_request`(
IN `Grant Title` VARCHAR(199),
IN `Funding Agency` VARCHAR(135),
IN `Maximum Monetary Value for Grant` DECIMAL(10,2),
IN `Desired Amount for Grant` DECIMAL(10,2),
IN `Grant Application Date` DATE,
IN `Grant Deadline` DATE,
IN `Grant Status (optional)` VARCHAR(45)
)
BEGIN
INSERT INTO grant_request (
title,
funding_organization,
maximum_monetary_value,
desired_amount,
application_date,
deadline,
outcome
)
VALUES (
`Grant Title`,
`Funding Agency`,
`Maximum Monetary Value for Grant`,
`Desired Amount for Grant`,
`Grant Application Date`,
`Grant Deadline`,
`Grant Status (optional)`
);
END
add_shareholder
add_shareholder
CREATE DEFINER=`root`@`localhost` PROCEDURE `add_shareholder`(
IN `First Name` VARCHAR(50),
IN `Last Name` VARCHAR(50),
IN `Nationality` CHAR(2),
IN `Place of Birth` VARCHAR(75),
IN `Mothers Maiden Name` VARCHAR(45),
IN `Fathers First Name` VARCHAR(45),
IN `Personal Telephone` VARCHAR(25),
IN `National Insurance Number` VARCHAR(30),
IN `Passport Number` VARCHAR(25)
)
BEGIN
INSERT INTO shareholder (
first_name,
last_name,
company_id,
country_code,
place_of_birth,
`mothers_maiden_name`,
`fathers_first_name`,
personal_telephone,
national_insurance_number,
passport_number
)
VALUES (
`First Name`,
`Last Name`,
(SELECT code FROM country WHERE name = `Nationality`),
`Place of Birth`,
`Mothers Maiden Name`,
`Fathers First Name`,
`Personal Telephone`,
`National Insurance Number`,
`Passport Number`
);
END
call_company
call_company
CREATE DEFINER=`root`@`localhost` PROCEDURE `call_company`(IN `Company` VARCHAR(199))
BEGIN
SELECT * FROM company_info_full WHERE company_name = `Company`;
END
call_employee
call_employee
CREATE DEFINER=`root`@`localhost` PROCEDURE `call_employee`(IN email VARCHAR(75))
BEGIN
SELECT e.id, e.first_name, e.middle_name,
e.last_name, e.date_of_birth, e.employee_role,
e.date_started, e.email_address, p.phone, p.description
FROM employee e, phone_number p
WHERE e.id = p.employee_id AND e.email_address = email;
END
call_film
call_film
CREATE DEFINER=`root`@`localhost` PROCEDURE `call_film`(IN film_name VARCHAR(122))
BEGIN
SELECT * FROM company_film_full WHERE title = film_name;
END
call_grant
call_grant
CREATE DEFINER=`root`@`localhost` PROCEDURE `call_grant`(heading VARCHAR(200))
BEGIN
SELECT *
FROM grant_info_full
WHERE grant_title = heading;
END
call_shareholder
call_shareholder
CREATE DEFINER=`root`@`localhost` PROCEDURE `call_shareholder`(IN full_name VARCHAR(101))
BEGIN
-- Declare local variables to hold the first name and last name
DECLARE first_name VARCHAR(50);
DECLARE last_name VARCHAR(50);
-- Extract the first name and last name from the full_name input
SET first_name = SUBSTRING_INDEX(full_name, ' ', 1); -- Extracts the substring before the first space
SET last_name = SUBSTRING_INDEX(SUBSTRING_INDEX(full_name, ' ', -1), ' ', 1); -- Extracts the substring after the last space
-- Perform the query using the extracted first name and last name
SELECT * FROM shareholder_info_full s WHERE s.first_name = first_name AND s.last_name = last_name;
END;
delete_company_record
delete_company_record
CREATE DEFINER=`root`@`localhost` PROCEDURE `delete_company_record`(
IN company_name VARCHAR(144)
)
BEGIN
DECLARE company_id_to_delete VARCHAR(144);
-- Get the company_id based on the company name
SELECT id INTO company_id_to_delete
FROM `movie_production_companies`.`company`
WHERE `name` = company_name;
IF company_id_to_delete IS NOT NULL THEN
-- Delete from the related tables with cascading delete
DELETE FROM `movie_production_companies`.`shareholder`
WHERE `company_id` = company_id_to_delete;
DELETE FROM `movie_production_companies`.`employee`
WHERE `company_id` = company_id_to_delete;
DELETE FROM `movie_production_companies`.`film`
WHERE `company_id` = company_id_to_delete;
DELETE FROM `movie_production_companies`.`company_grant`
WHERE `company_id` = company_id_to_delete;
DELETE FROM `movie_production_companies`.`department_address`
WHERE `company_id` = company_id_to_delete;
-- Delete the company itself
DELETE FROM `movie_production_companies`.`company`
WHERE `id` = company_id_to_delete;
SELECT 'Company and related records deleted successfully.' AS result;
ELSE
SELECT 'Company not found.' AS result;
END IF;
END
delete_employee_by_email
delete_employee_by_email
CREATE DEFINER=`root`@`localhost` PROCEDURE `delete_employee_by_email`(
IN email VARCHAR(115)
)
BEGIN
DECLARE employee_id_to_delete VARCHAR(144);
-- Get the employee_id based on the email address
SELECT id INTO employee_id_to_delete FROM `movie_production_companies`.`employee`
WHERE `email_address` = email;
IF employee_id_to_delete IS NOT NULL THEN
DELETE FROM `movie_production_companies`.`crew`
WHERE `crew_id` = employee_id_to_delete;
DELETE FROM `movie_production_companies`.`staff`
WHERE `staff_id` = employee_id_to_delete;
DELETE FROM `movie_production_companies`.`staff_salary`
WHERE `staff_id` = employee_id_to_delete;
DELETE FROM `movie_production_companies`.`crew_info`
WHERE `crew_id` = employee_id_to_delete;
DELETE FROM `movie_production_companies`.`phone_number`
WHERE `employee_id` = employee_id_to_delete;
-- Delete the employee itself
DELETE FROM `movie_production_companies`.`employee`
WHERE `id` = employee_id_to_delete;
SELECT 'Employee and related records deleted successfully.' AS result;
ELSE
SELECT 'Employee not found.' AS result;
END IF;
END
delete_shareholder_by_name
delete_shareholder_by_name
CREATE DEFINER=`root`@`localhost` PROCEDURE `delete_shareholder_by_name`(
IN firstname VARCHAR(50),
IN lastname VARCHAR(50)
)
BEGIN
DECLARE shareholder_id_to_delete INT;
-- Get the shareholder_id based on the first and last names
SELECT id
INTO shareholder_id_to_delete
FROM `movie_production_companies`.`shareholder`
WHERE
`first_name` = firstname
AND `last_name` = lastname;
IF shareholder_id_to_delete IS NOT NULL THEN
-- Delete the shareholder
DELETE FROM `movie_production_companies`.`shareholder`
WHERE `id` = shareholder_id_to_delete;
SELECT 'Shareholder deleted successfully.' AS result;
ELSE
SELECT 'Shareholder not found.' AS result;
END IF;
END
get_employees_by_company
get_employees_by_company
CREATE DEFINER=`root`@`localhost` PROCEDURE `get_employees_by_company`(IN company_name VARCHAR(255))
BEGIN
SELECT
e.first_name,
e.middle_name,
e.last_name,
e.employee_role
FROM
employee e
JOIN
company c ON e.company_id = c.id
WHERE
c.name = company_name;
END
get_shareholders_by_company
get_shareholders_by_company
CREATE DEFINER=`root`@`localhost` PROCEDURE `get_shareholders_by_company`(company_name VARCHAR(200))
BEGIN
SELECT
CONCAT(shareholder.first_name, ' ', shareholder.last_name) AS full_name,
place_of_birth,
country.name AS nationality,
shareholder.personal_telephone
FROM company_shareholder
JOIN company ON company_shareholder.company_id = company.id
JOIN shareholder ON company_shareholder.shareholder_id = shareholder.id
JOIN country ON country.code = shareholder.country_code
WHERE company.name = company_name;
END
get_films_by_company
get_films_by_company
CREATE DEFINER=`root`@`localhost` PROCEDURE `get_films_by_company`(company_name VARCHAR(200))
BEGIN
SELECT film.title, film.release_year
FROM company_film
JOIN company ON company.id = company_film.company_id
JOIN film ON film.movie_code = company_film.film_movie_code
WHERE company.name = company_name
ORDER BY film.release_year;
END
grant_info_by_company
grant_info_by_company
CREATE DEFINER=`root`@`localhost` PROCEDURE `grant_info_by_company`(`Company` VARCHAR(200))
BEGIN
SELECT g.*
FROM company_grant cg
JOIN company c ON cg.company_id = c.id
JOIN grant_request g ON cg.grant_id = g.id
WHERE c.name = `Company`
ORDER BY g.application_date;
END
get_shareholders_by_company
get_shareholders_by_company
CREATE DEFINER=`root`@`localhost` PROCEDURE `get_shareholders_by_company`(company_name VARCHAR(200))
BEGIN
SELECT
CONCAT(shareholder.first_name, ' ', shareholder.last_name) AS full_name,
place_of_birth,
country.name AS nationality,
shareholder.personal_telephone
FROM company_shareholder
JOIN company ON company_shareholder.company_id = company.id
JOIN shareholder ON company_shareholder.shareholder_id = shareholder.id
JOIN country ON country.code = shareholder.country_code
WHERE company.name = company_name;
END
grant_status
grant_status
CREATE DEFINER=`root`@`localhost` PROCEDURE `grant_status`(IN grant_title VARCHAR(199), IN outcome ENUM('Approved', 'Denied', 'Pending'))
BEGIN
UPDATE grant_request
SET status = outcome
WHERE grant_request.title = grant_title;
END
get_payroll_by_company
get_payroll_by_company
CREATE DEFINER=`root`@`localhost` PROCEDURE `get_payroll_by_company`(company_name VARCHAR(200))
BEGIN
SELECT
`staff_id` AS `staff_id`,
`first_name` AS `first_name`,
`middle_name` AS `middle_name`,
`last_name` AS `last_name`,
`department`,
`job_level` AS `job_level`,
`working_hours` AS `working_hours`,
`salary` AS `salary`
FROM
`payroll` `p`
WHERE p.company = company_name
ORDER BY `p`.`first_name` , `p`.`last_name`;
END
get_crew_by_film
CREATE DEFINER=`root`@`localhost` PROCEDURE `get_crew_by_film`(IN movie_name VARCHAR(211))
BEGIN
SELECT
CONCAT(e.first_name, ' ',
e.last_name) AS crew_member,
company.name AS company,
r.name AS role
FROM film f
JOIN crew_info ci ON f.movie_code = ci.movie_code
JOIN crew c ON ci.crew_id = c.crew_id
JOIN employee e ON c.crew_id = e.id
JOIN company_film cf ON f.movie_code = cf.film_movie_code
JOIN company ON company.id = e.company_id AND company.id = cf.company_id
JOIN role r ON ci.role_id = r.id
WHERE f.title = movie_name
ORDER BY r.id;
END
Page updated
Google Sites
Report abuse