Purpose

• The activity_filter procedure is designed to query the activity_log table and retrieve records based on specified event types and/or table names. Its flexibility allows for various levels of filtering, making it a versatile tool for examining database activities.

Parameters

• eventType (VARCHAR(50)): Specifies the type of event to filter (e.g., 'INSERT', 'DELETE').

• tableName (VARCHAR(50)): Specifies the name of the table for which to filter the activity logs.

Logic

• The procedure uses conditional statements to determine the appropriate query based on the provided parameters:

  • Filter by Event Type Only: If eventType is provided and tableName is NULL, it fetches records from activity_log where the event_type matches eventType.

  • Filter by Table Name Only: If tableName is provided and eventType is NULL, it selects records where the table_name matches tableName.

  • Filter by Both Event Type and Table Name: If both eventType and tableName are provided, it retrieves records where both the event_type and table_name match the given parameters.

  • No Filters Applied: If neither parameter is provided, it selects all records from the activity_log table.

Usage

• This procedure can be invoked to quickly filter and retrieve relevant activity logs, which is particularly useful for audit purposes, monitoring database usage, and troubleshooting.

• For example, to view all DELETE operations on the 'employee' table, you would call the procedure with activity_filter('DELETE', 'employee').

Importance

• activity_filter enhances the database's auditing and monitoring capabilities by providing a straightforward method to access and analyze logged activities based on specific criteria. This is crucial for maintaining transparency, ensuring compliance, and facilitating efficient database management.

Purpose

• The activity_log_auto procedure is devised to systematically record activity logs in the database. Its purpose is to capture essential details about database operations, including the type of operation, the entity affected, and a descriptive account of the event.

Parameters

• log_type (VARCHAR(50)): Indicates the type of activity being logged, such as 'INSERT', 'DELETE', or 'UPDATE'.

• entity (VARCHAR(50)): Specifies the database entity (like a table or a view) involved in the activity.

• key_column (VARCHAR(114)): Refers to a key attribute or column relevant to the logged activity, usually a primary key or a unique identifier.

• log_description (VARCHAR(200)): Provides a detailed description of the activity.

Logic

• The procedure executes an INSERT statement into the activity_log table.

• It uses the provided parameters to populate the columns in the activity_log table, which include:

  • event_type: The type of the event (derived from log_type).

  • table_name: The name of the entity involved (from entity).

  • key_attribute: A key identifying attribute (from key_column).

  • event_description: A textual description of the event (from log_description).

  • user: The user executing the action, captured using the CURRENT_USER() function.

Usage

• This procedure is typically invoked by triggers and other automated database mechanisms. For example, a trigger for logging deletions in the 'employee' table might call activity_log_auto with appropriate parameters to record each delete operation.

• It simplifies and standardizes the process of logging, ensuring consistency and reliability in the way data manipulation events are recorded.

Importance

• activity_log_auto plays a crucial role in maintaining an audit trail within the database. It provides a consistent method for logging various activities, aiding in monitoring, security, and compliance.

• The procedure also helps in understanding the history of data changes, user activities, and can be instrumental in debugging and analyzing database usage patterns.

Purpose

• The add_company procedure is designed to streamline the process of adding new entries to the company table. It encapsulates the logic necessary for inserting a company's comprehensive details, ensuring that data integrity and relational dependencies are maintained.

Parameters

• Company Name (VARCHAR(199)): The name of the company.

• Company Address (VARCHAR(199)): The address of the company.

• City (VARCHAR(75)): The city where the company is located.

• Zipcode (INT): The postal code for the company's address.

• Organization Kind (VARCHAR(199)): The type or category of the organization.

• Total Asset (DECIMAL(10, 2)): The total assets value of the company.

• Total Liability (DECIMAL(10, 2)): The total liabilities of the company.

• Country of Registration (VARCHAR(100)): The country in which the company is registered.

• Date of Registration (DATE): The date on which the company was registered.

Logic

• The procedure performs an INSERT operation into the company table.

• It uses the provided parameters to fill in the company's details. Notably, it includes several subqueries to resolve foreign key references:

  • The city_id is determined by looking up the city's ID based on the provided City name.

  • The kind_of_organization_id is obtained from the kind_of_organization table using the Organization Kind name.

  • The registration_body_id is derived by joining the registration_body and country tables, matching the provided Country of Registration.

• These subqueries ensure that the inserted data respects the referential integrity constraints of the database.

Usage

• This procedure is used to add new companies to the database. It simplifies the insertion process by handling the complex relationships and lookups internally, requiring only straightforward input parameters.

Importance

• add_company is crucial for maintaining an organized and consistent approach to data entry. By encapsulating the insertion logic within a stored procedure, it reduces the likelihood of errors, streamlines database interactions, and ensures that all necessary data relationships and constraints are adhered to.

Purpose

• The add_company_department_address procedure simplifies the process of adding address details for various departments within companies. It ensures that these addresses are accurately linked to the respective departments and companies.

Parameters

• dept_name (VARCHAR(75)): The name of the department.

• company_name (VARCHAR(75)): The name of the company associated with the department.

• building_name (VARCHAR(75)): The name of the building where the department is located.

• addy (VARCHAR(75)): The address of the department.

Logic

• The procedure executes an INSERT operation into the department_address table.

• It uses subqueries to find the department_id and company_id based on the provided department and company names:

  • The department_id is obtained by querying the department table with the given dept_name.

  • The company_id is derived by querying the company table with the specified company_name.

• The building_name and addy parameters are directly used to populate the building and address fields in the department_address table.

Usage

• This procedure is invoked to link department addresses with their corresponding companies and departments in the database. It encapsulates the logic for maintaining the relational integrity and consistency of department address data within the organizational structure.

Importance

• By automating the process of adding department addresses and handling the internal lookups and associations, the add_company_department_address procedure significantly reduces the complexity and potential for error in manual data entry.

• This procedure is crucial for ensuring data accuracy and streamlining the management of departmental information within companies, particularly in a database that handles intricate corporate structures and hierarchies.

Purpose

• The add_employee procedure is designed to facilitate the insertion of new employee records into the database. It abstracts the complexities involved in adding new employee data, ensuring that all necessary information is captured accurately and efficiently.

Parameters

• firstname (VARCHAR(45)): The first name of the employee.

• middlename (VARCHAR(45)): The middle name of the employee.

• lastname (VARCHAR(45)): The last name of the employee.

• DOB (DATE): The date of birth of the employee.

• company_name (VARCHAR(144)): The name of the company where the employee works.

• employeerole (VARCHAR(144)): The role or position of the employee within the company.

• commencement_date (DATE): The date when the employee commenced their role.

Logic

• The procedure performs an INSERT operation into the employee table.

• It directly uses the firstname, middlename, lastname, DOB, employeerole, and commencement_date parameters to populate the respective columns in the employee table.

• The company_id is determined by querying the company table to find the ID corresponding to the given company_name. This ensures that the employee is correctly associated with the existing company in the database.

Usage

• This procedure is used to add new employees to the database. By handling the company association internally, it simplifies the process of linking employees to their respective companies.

Importance

• add_employee enhances data entry efficiency and accuracy. It encapsulates the logic for adding new employees, thereby reducing the likelihood of errors that might arise from manual data entry.

• The procedure is crucial for maintaining an organized and consistent approach to managing employee data in the database, particularly in systems with a large number of employees and complex organizational structures.

Purpose

• The add_film procedure is crafted to facilitate the insertion of new film entries into the database. It simplifies the process of adding film details, ensuring that each film is accurately linked to its production company.

Parameters

• Title (VARCHAR(115)): The title of the film.

• Production Company (VARCHAR(144)): The name of the company producing the film.

• Release Year (YEAR): The year in which the film was or will be released.

• First Released (DATE): The date when the film was first released.

Logic

• The procedure performs an INSERT operation into the film table.

• It uses the provided Title, Release Year, and First Released parameters to populate the respective columns in the film table.

• The company_id is determined by querying the company table to find the ID that corresponds to the given Production Company. This step is crucial to maintain the relational integrity between the film and its production company.

Usage

• This procedure is invoked to add new films to the database. By managing the association with production companies internally, it streamlines the data entry process, ensuring that films are correctly and efficiently cataloged.

Importance

• add_film is key to maintaining an organized and consistent approach to managing film data within the database. It encapsulates the complexities of data entry, reducing the potential for errors and ensuring that each film is correctly associated with its producer.

• The procedure is particularly valuable in a database environment where film data is frequently updated or expanded, such as in the movie_production_companies database, which handles a diverse range of film-related information.

Purpose

• The add_grant_request procedure is designed to streamline the insertion of new grant request records into the database. It ensures that all necessary details for a grant request are captured accurately and efficiently.

Parameters

• grant_title (VARCHAR(199)): The title or name of the grant request.

• funding_agency (VARCHAR(135)): The organization or entity providing the funding.

• max_monetary_value (DECIMAL(10,2)): The maximum monetary value available for the grant.

• new_desired_amount (DECIMAL(10,2)): The amount of funding being requested.

• new_application_date (DATE): The date when the grant application was submitted.

• new_deadline (DATE): The deadline for the grant application.

• outcome (optional) (VARCHAR(45)): The outcome of the grant request, which is optional at the time of creation.

Logic

• The procedure performs an INSERT operation into the grant_request table.

• It directly uses the parameters provided to populate the relevant columns in the grant_request table, including title, funding_organization, maximum_monetary_value, desired_amount, application_date, deadline, and outcome.

• The optional nature of the outcome parameter allows for flexibility, accommodating scenarios where the outcome might not be known at the time of the grant request submission.

Usage

• This procedure is used to add new grant requests to the database. It encapsulates all the required logic to ensure that the grant requests are entered correctly, respecting the database's structure and data integrity.

Importance

• add_grant_request is crucial in maintaining an efficient and error-free process for recording new grant requests. It not only simplifies the data entry process but also ensures that all necessary details are consistently captured.

• The procedure is especially valuable in a database environment that handles a variety of grant-related data, ensuring that each grant request is documented accurately and thoroughly.

Purpose

• The add_shareholder procedure is developed to streamline the process of adding new shareholder entries into the database. It ensures that shareholder information is accurately linked to the corresponding companies and countries.

Parameters

• First Name (VARCHAR(50)): The first name of the shareholder.

• Surname (VARCHAR(50)): The surname or last name of the shareholder.

• Company (VARCHAR(24)): The name of the company in which the shareholder holds shares.

• Nationality (CHAR(50)): The nationality of the shareholder.

• Birth Place (VARCHAR(75)): The birthplace of the shareholder.

• mothers_maiden_name (VARCHAR(45)): The maiden name of the shareholder's mother.

• fathers_first_name (VARCHAR(45)): The first name of the shareholder's father.

• telephone (VARCHAR(25)): The personal telephone number of the shareholder.

• national_insurance_no (VARCHAR(30)): The national insurance number of the shareholder.

• passport_no (VARCHAR(25)): The passport number of the shareholder.

Logic

• The procedure performs an INSERT operation into the shareholder table.

• It uses the provided parameters to populate the respective columns in the shareholder table.

• The company_id and country_code are determined through subqueries:

  • company_id is obtained by querying the company table with the provided Company name.

  • country_code is derived from the country table based on the given Nationality.

Usage

• This procedure is invoked to add new shareholder records to the database. It handles complex associations, like linking shareholders to their respective companies and countries, internally within the procedure.

Importance

• add_shareholder is key to maintaining an efficient and accurate process for recording shareholder information. It encapsulates the complexities of shareholder data entry, thereby reducing potential errors and ensuring consistency in how shareholder records are added.

• This procedure is particularly valuable in a database that manages detailed shareholder information, as it ensures that each shareholder is correctly documented with all necessary personal and relational details.

Purpose

• The call_company procedure is intended to simplify the process of retrieving detailed information about a particular company from the company_info_full view. It's a convenient tool for users to get a complete profile of a company, including its address, organizational type, registration details, number of employees, and net value.

Parameters

• Company (VARCHAR(199)): The name of the company for which information is being requested.

Logic

• The procedure executes a SELECT operation on the company_info_full view.

• It uses the provided Company parameter to filter the view and retrieve data for the company that matches the given name.

• The query is designed to select all columns (*) from the company_info_full view for the company record that corresponds to the input Company.

Usage

• This procedure can be invoked to quickly obtain a comprehensive overview of a company, particularly useful in business analysis, company profiling, or data reporting.

• It offers an efficient and direct method for users or applications to access a complete set of company data without needing to navigate through multiple tables or write complex queries.

Importance

• call_company is crucial for efficient data retrieval in scenarios where comprehensive company information is needed.

• By providing a simple and effective means of querying company details, it enhances the user experience and contributes to the efficient management of company-related data within the database.

Purpose

• The call_employee procedure aims to simplify and streamline the process of fetching detailed information about an employee from the database. It consolidates data from the employee and phone_number tables, providing a comprehensive view of an employee's profile.

Parameters

• email (VARCHAR(75)): The email address of the employee whose information is being requested.

Logic

• The procedure performs a SELECT operation, joining the employee (aliased as e) and phone_number (aliased as p) tables.

• It uses the provided email parameter to filter the employee table and retrieve data for the employee associated with that email address.

• The query selects various fields from the employee table, including the employee's ID, names, date of birth, role, start date, and email address. Additionally, it fetches the phone number and its description from the phone_number table.

• The condition e.id = p.employee_id ensures that the phone numbers are correctly matched to the respective employee.

Usage

• This procedure can be invoked to obtain detailed information about an employee, particularly useful in HR systems, employee management applications, or internal directories.

• It offers a convenient way to access employee details using a common identifier like an email address.

Importance

• call_employee is vital for efficient data retrieval in scenarios where quick access to an employee's full profile is necessary.

• By combining data from multiple related tables into a single query, it ensures data consistency and provides a streamlined interface for accessing comprehensive employee information.

Purpose

• The call_film procedure is designed to enable an easy and direct way to query details about a specific film from the film table, using the film's title as the search criterion.

Parameters

• film_name (VARCHAR(122)): The title of the film for which information is being requested.

Logic

• The procedure executes a SELECT operation on the film table.

• It utilizes the provided film_name parameter to filter the film table and retrieve data for the film that matches the given title.

• The query is designed to select all columns (*) from the film table for the film record that corresponds to the input film_name.

Usage

• This procedure can be invoked to quickly obtain comprehensive information about a film, which is particularly useful in scenarios like content management systems, film cataloging applications, or media libraries.

• It offers a streamlined and straightforward method for users or applications to access film data without needing detailed knowledge of the database schema.

Importance

• call_film is crucial for efficient data retrieval in contexts where accessing detailed information about films is a frequent and necessary operation.

• By providing a simple and effective means of querying film details, it enhances user experience and contributes to the efficient management of film-related data within the database.

Purpose

• The call_shareholder procedure is established to facilitate the retrieval of comprehensive information about a specific shareholder from the shareholder table. It allows for querying based on the shareholder's first and last names, making data access straightforward and efficient.

Parameters

• shareholder_fn (VARCHAR(50)): The first name of the shareholder.

• shareholder_ln (VARCHAR(50)): The last name of the shareholder.

Logic

• The procedure executes a SELECT operation on the shareholder table (aliased as s).

• It utilizes the provided parameters shareholder_fn and shareholder_ln to filter the shareholder table and retrieve data for the shareholder matching these names.

• The query is designed to select all columns (*) from the shareholder table for the records where the first_name and last_name match the input parameters.

Usage

• This procedure can be invoked to quickly obtain detailed information about a shareholder, particularly useful in financial systems, shareholder management applications, or corporate governance tools.

• It offers an efficient way for users or systems to access shareholder data, especially in scenarios where querying based on names is the most practical approach.

Importance

• call_shareholder is essential for effective data retrieval, enabling easy access to shareholder profiles in situations where understanding shareholder composition and details is key.

• By simplifying the process of querying shareholder information, this procedure enhances the usability and accessibility of shareholder data within the database.

Purpose

• The delete_company_record procedure is crafted to handle the deletion of a company and its related records in a comprehensive and safe manner. This ensures that when a company is removed from the database, all associated data across different tables is also appropriately deleted.

Parameters

• company_name (VARCHAR(144)): The name of the company to be deleted.

Logic

• The procedure begins by declaring a variable company_id_to_delete to store the ID of the company to be removed.

• It then performs a SELECT query to find the ID of the company with the given name and stores it in the company_id_to_delete variable.

• The procedure checks if the company_id_to_delete is not null, indicating that the company exists in the database.

• If the company is found, it executes a series of DELETE statements across various tables where the company_id is referenced:

  • Deletes shareholder records associated with the company.

  • Removes employee records linked to the company.

  • Deletes film records produced by the company.

  • Removes entries from company_grant and department_address tables related to the company.

• Finally, it deletes the company record from the company table itself.

• If the company is deleted successfully, it returns a confirmation message. If the company is not found, it returns a different message indicating that the company does not exist.

Usage

• This procedure is invoked to remove a company and all its related data from the database. It is particularly useful in scenarios where a company no longer needs to be tracked or has ceased operations.

Importance

• delete_company_record is crucial for maintaining the integrity of the database. It ensures that when a company is removed, all dependent data is also cleared, preventing orphan records and maintaining data consistency.

• The procedure also encapsulates the complex logic of cascading deletions, making it safer and easier to manage the deletion of companies and their associated data.

Purpose

• The delete_employee_record procedure is intended to handle the deletion of an employee and all associated records within the database. It ensures that when an employee's record is removed, all linked information across different tables is also appropriately deleted.

Parameters

• email (VARCHAR(115)): The email address of the employee whose record is to be deleted.

Logic

• The procedure initiates by declaring a variable employee_id_to_delete to store the ID of the employee to be removed.

• It performs a SELECT query to find the ID of the employee associated with the given email address and stores this ID in the employee_id_to_delete variable.

• The procedure checks if the employee_id_to_delete is not null, indicating that the employee exists in the database.

• If the employee is found, it executes a series of DELETE statements across various tables where the employee_id is referenced:

  • Deletes records from the crew table linked to the employee.

  • Removes the employee's records from the staff table.

  • Deletes entries in the staff_salary table related to the employee.

  • Removes records from the crew_info and phone_number tables associated with the employee.

• Finally, it deletes the employee's record from the employee table itself.

• If the employee's record is deleted successfully, a confirmation message is returned. If the employee is not found, an alternate message indicating this is returned.

Usage

• This procedure is used when an employee leaves the company or their data needs to be removed from the system. It ensures that the deletion process is thorough, removing all related records and maintaining database integrity.

Importance

• delete_employee_record is crucial for data consistency and integrity. It ensures that when an employee is removed from the database, all dependent and related data is also cleared, avoiding orphan records.

• By handling the complex logic of cascading deletions within a stored procedure, the process is made safer and more manageable.

Purpose

• The delete_shareholder_by_name procedure is designed to enable the deletion of a shareholder's record from the database using their first and last names as identifiers. It offers a straightforward method to remove shareholder data when needed.

Parameters

• firstname (VARCHAR(50)): The first name of the shareholder to be deleted.

• lastname (VARCHAR(50)): The last name of the shareholder to be deleted.

Logic

• The procedure begins by declaring a variable shareholder_id_to_delete to store the ID of the shareholder to be removed.

• It performs a SELECT query to locate the ID of the shareholder with the given first and last names, storing this ID in shareholder_id_to_delete.

• The procedure checks if shareholder_id_to_delete is not null, confirming the existence of the shareholder in the database.

• If the shareholder is found, a DELETE statement is executed to remove the shareholder's record from the shareholder table.

• After the deletion, the procedure returns a confirmation message indicating the successful removal of the shareholder. If no matching shareholder is found, it returns a message stating that the shareholder was not found.

Usage

• This procedure is particularly useful for administrators or database managers who need to remove shareholder records due to changes in share ownership, shareholder departure, or data correction.

Importance

• delete_shareholder_by_name is crucial for maintaining the accuracy and relevance of shareholder data in the database. It ensures the removal of shareholder records is handled efficiently and correctly, based on reliable identifiers.

• This procedure helps in managing the shareholders' data with precision, avoiding any data inconsistency that might arise from manual deletion processes.

Purpose

• The get_employees_by_company procedure is developed to fetch information about employees who are associated with a specific company. It's aimed at providing a streamlined way to query employee data based on the company they work for.

Parameters

• company_name (VARCHAR(255)): The name of the company for which employee details are required.

Logic

• The procedure executes a SELECT query, joining the employee table (aliased as e) with the company table (aliased as c).

• It uses the provided company_name parameter to filter the company table and retrieve employees associated with that particular company.

• The query selects the first_name, middle_name, last_name, and division fields from the employee table for employees whose company_id matches the ID of the company with the name specified in the company_name parameter.

Usage

• This procedure can be invoked to obtain a list of employees for a particular company, which is particularly useful in HR systems, company directories, or internal management applications.

• It facilitates easy access to employee information for a given company, simplifying data retrieval for company-specific queries.

Importance

• get_employees_by_company is crucial for efficient data retrieval in contexts where understanding the composition of the workforce within a specific company is necessary.

• By streamlining the process of querying employee data based on company affiliation, this procedure enhances the usability and accessibility of employee data within the database.

Purpose

• The get_films_by_company procedure is designed to query and retrieve a list of films along with their release years, produced by a specified company. It provides a focused way to access film data related to a particular production company.

Parameters

• company_name (VARCHAR(200)): The name of the production company whose films are to be retrieved.

Logic

• The procedure executes a SELECT operation on the film table, joined with the company table.

• It utilizes the provided company_name parameter to filter the company table and identify the corresponding company ID.

• The query selects the title (aliased as movie) and release_year from the film table for films where the company_id matches the ID of the company specified in the company_name parameter.

Usage

• This procedure is useful in scenarios where a comprehensive list of films produced by a particular company is required, such as for filmography analysis, cataloging purposes, or content management systems.

• It allows for an efficient and direct method to access a subset of the film database based on the production company, avoiding the need for manual filtering or complex queries.

Importance

• get_films_by_company is key to streamlined data access within the movie_production_companies database. It facilitates focused queries, enabling users to efficiently extract relevant information about the films produced by a specific company.

• By simplifying access to film data, this procedure enhances the usability of the database for various user needs, from analytics to content management.

Purpose

• The get_company_grant_details procedure aims to facilitate the extraction of comprehensive grant information related to a particular company. It's crafted to provide a detailed view of the grants that a company has applied for or received.

Parameters

• Company (VARCHAR(200)): The name of the company for which grant details are requested.

Logic

• The procedure performs a SELECT operation, joining the company_grant table (aliased as cg), company table (aliased as c), and grant_request table (aliased as g).

• It uses the provided Company parameter to filter the company table and identify the corresponding company ID.

• The query selects all columns (*) from the grant_request table for grant records where the grant_id matches those associated with the company identified by Company.

• The use of DISTINCT ensures that the query returns unique grant records, eliminating any potential duplicates.

Usage

• This procedure can be called to get a list of all grant applications and awards for a specific company, which is particularly useful in financial analysis, grant management, or corporate auditing.

• It offers a streamlined and efficient method for users or systems to access comprehensive grant data linked to a specific company.

Importance

• get_company_grant_details is crucial for effective data retrieval in contexts where a detailed understanding of a company's grant-related activities is necessary.

• By consolidating data from multiple tables into a single query, it ensures data consistency and provides a user-friendly interface for accessing extensive grant information.

Purpose

• The get_payroll_by_company procedure is tailored to provide detailed payroll information for employees associated with a specific company. It taps into the payroll view, which consolidates employee payroll data from various underlying tables, to fetch relevant data.

Parameters

• Company (VARCHAR(144)): The name of the company for which payroll information is being requested.

Logic

• The procedure performs a SELECT query on the payroll view.

• It utilizes the Company parameter to filter the payroll view, thereby retrieving payroll data specific to employees of the given company.

• The query selects important payroll fields, including the employee's ID, names, department, job level, working hours, and salary.

• The WHERE clause, payroll.company = Company, ensures that the payroll information fetched is specific to employees of the company named in the Company parameter.

Usage

• This procedure can be utilized to gather comprehensive payroll information for a particular company, which is extremely valuable for HR departments, financial analysis, or internal auditing.

• It provides a streamlined and efficient way to access detailed payroll data pertinent to a company, especially in scenarios requiring analysis or reporting on employee compensation.

Importance

• get_payroll_by_company is critical for the efficient and accurate retrieval of payroll data. It plays a significant role in contexts where understanding the financial aspects of employee compensation within a specific company is essential.

• By enabling easy access to aggregated payroll data, this procedure enhances the functionality and user-friendliness of the database for financial and HR-related purposes.

Purpose

• The get_shareholders_by_company procedure is intended to provide a detailed view of shareholders for a given company. It consolidates shareholder information, including their names and nationalities, linked to a particular company.

Parameters

• company_name (VARCHAR(200)): The name of the company for which shareholder information is requested.

Logic

• The procedure performs a SELECT operation, joining the shareholder table (aliased as s), company, and country tables.

• It uses the provided company_name parameter to filter the company table and retrieve shareholders associated with that company.

• The query selects the first name, last name, nationality (from the country table), and place of birth from the shareholder table for records where the company_id matches the ID of the company specified in the company_name parameter.

• The join with the country table allows the query to translate the country code in the shareholder table into a more readable country name.

Usage

• This procedure can be used to generate lists of shareholders for a specific company, useful in scenarios such as corporate governance, investor relations, or financial reporting.

• It provides an efficient way to access detailed shareholder information, which is crucial for understanding the ownership structure of a company.

Importance

• get_shareholders_by_company is essential for efficient data retrieval in situations where detailed insights into a company's shareholder composition are necessary.

• By streamlining the process of querying shareholder data based on company affiliation, this procedure enhances the usability and accessibility of such information within the database.

Purpose

• The grant_status procedure is crafted to update the outcome of a grant request in the grant_request table. It allows for the modification of the grant's status, marking it as either 'Approved' or 'Denied' based on the input parameters.

Parameters

• grant_title (VARCHAR(199)): The title of the grant request whose status needs to be updated.

• status (ENUM('Approved', 'Pending', 'Denied')): The new status to be assigned to the grant request. This parameter can only take one of two values: 'Approved' or 'Denied'.

Logic

• The procedure performs an UPDATE operation on the grant_request table.

• It uses the grant_title parameter to locate the specific grant request that needs to be updated.

• The status parameter is then used to set the new outcome for the identified grant request.

• The WHERE clause ensures that only the grant request with a title matching the grant_title parameter is updated.

Usage

• This procedure can be invoked to change the status of a grant request, typically after a review process has been completed. It is useful in scenarios where grant applications are being tracked and their outcomes need to be recorded.

• It provides a straightforward and efficient method for administrators or grant managers to update the status of grant requests.

Importance

• grant_status is crucial for maintaining up-to-date records of grant applications and their outcomes. It ensures that the status of each grant request is accurately reflected in the database.

• By simplifying the process of updating grant request outcomes, this procedure enhances the management and tracking of grant applications within the database.