View Objects

The company_info_full view serves as a comprehensive data repository, designed to provide an all-encompassing snapshot of companies encompassing operational, financial, and structural details. By aggregating data from multiple sources into a single, queryable view, it enables users to access a complete profile of a company with ease, making it invaluable for reporting, strategic decision-making, and business intelligence analysis.

Structure and Content

The view is constructed using SQL JOIN operations and subqueries to pull together and calculate data from several tables, including company details, employee statistics, shareholder information, production activities, and grant applications. Here is a breakdown of the fields included in this view:

• Basic Company Information:

  • id, company_name, address, zip_code: Directly sourced from the company table.

  • city: Obtained through a join with the city table.

  • country: Derived from a join with the country table.

• Organizational Details:

  • organization_type: Linked via the kind_of_organization table.

  • registration_body: Sourced from the registration_body table.

  • registration_date: Indicates when the company was registered, from the company table.

• Financial Metrics:

  • net_value: Calculated as the difference between total assets and total liabilities.

• Operational Metrics:

  • number_of_employees: Total number of employees through a subquery counting entries in the employee table.

  • crew: Count of crew members linked through the employee table.

  • staff: Count of staff members linked similarly.

• Engagement and Contributions:

  • number_of_shareholders: Total number of shareholders per company.

  • total_films_produced: Total films produced, showcasing the company's contribution to the film industry.

  • total_grants_applied, approved_grants, pending_grants, denied_grants: Detailed breakdown of grant applications and their statuses, providing insight into the company's funding dynamics.

Usage

This view can be utilized in multiple ways:

• Comprehensive Reporting: Allows for the creation of detailed reports on company demographics, operations, and financial status.

• Data Analysis and Business Intelligence: Facilitates deep dives into data for trends, forecasting, and strategic planning. Furthermore, this view was the only object involved in the dashboard design with Power BI which was eventually deployed to the Power BI Service.

• Operational Insights: Offers a look into the workforce and production capabilities of companies, aiding in operational adjustments and improvements.

Purpose

• The employee_info_full view is designed to offer a detailed and holistic overview of employee information in the database. It amalgamates various aspects of employee data, including personal details, company affiliation, role, division, contact information, and more.

Structure and Logic

• This view is created by joining the employee (aliased as e), phone_number (aliased as p), and company (aliased as c) tables.

• Fields included in the view:

  • id: The unique identifier of the employee.

  • first_name, middle_name, last_name: The name components of the employee.

  • date_of_birth: The birth date of the employee.

  • company: The name of the company where the employee works.

  • employee_role: The role of the employee within the company.

  • division: A derived field that categorizes the employee as 'Crew' or 'Staff' based on their role. This is determined using a CASE statement that checks the employee's role against entries in the role and department tables.

  • date_started: The date when the employee started working at the company.

  • email_address: The employee's email address.

  • phone, description: Contact number and its description from the phone_number table.

Usage

• This view can be queried to obtain a comprehensive profile of an employee, which is useful for HR management, internal directories, or employee-related analysis.

• It provides a convenient way for users to access detailed employee information, encompassing various data points, without the need for complex queries or multiple joins.

Importance

• employee_info_full is crucial for efficient data retrieval in contexts where a complete understanding of employee details is necessary.

• By aggregating relevant data into a single view, it enhances the database's usability and facilitates more effective data access and analysis.

Purpose

• The payroll view is created to provide an integrated and detailed picture of payroll information for employees across different departments and companies. This view serves as an essential tool for managing and analyzing payroll data, ensuring easy access to critical information such as job levels, working hours, and salaries.

Structure and Logic

• The view is established through a series of JOIN operations linking the staff_salary (aliased as ss), staff (aliased as s), employee (aliased as e), department (aliased as d), and company (aliased as c) tables.

• Fields included in the view:

  • employee_id: The unique identifier of the employee, sourced from the staff_salary table.

  • first_name, middle_name, last_name: The name components of the employee, obtained from the employee table.

  • company: The name of the company where the employee works, derived from the company table.

  • department: The department to which the employee is assigned, from the department table.

  • job_level, working_hours, salary: Payroll details including the employee's job level, working hours, and salary, all extracted from the staff_salary table.

Usage

• This view can be queried to obtain a consolidated view of payroll details, which is extremely beneficial for financial analysis, payroll processing, HR management, and internal auditing.

• It simplifies complex data retrieval by providing a single, comprehensive source for payroll data, eliminating the need for multiple, complicated queries.

Importance

• payroll is essential for efficient and effective payroll data management. It ensures that comprehensive payroll information is readily accessible in a user-friendly format.

• By aggregating key payroll data into a single view, it streamlines data retrieval processes, enhancing the overall efficiency and usability of the database for payroll-related inquiries and analysis.

Purpose

This view primarily serves the purpose of consolidating grant request data with associated company information, enabling application developers to easily fetch and display data about grants, including the company applying for them, the amount requested, and the current status of the grant.

Structure

The view pulls together information from three key tables:

• company: Contains details about companies.

• grant_request: Contains details about grant applications.

• company_grant: A junction table linking companies with their grant requests.

Fields Included

• company_name: Name of the company applying for the grant. This helps users identify which company the grant data pertains to.

• grant_title: Title of the grant application. This provides a brief description or name of the grant.

• desired_amount: The financial amount requested in the grant application.

• funding_organization: The funding agency that received respective grants from companies.

• status: Current status of the grant application (e.g., "Pending", "Approved", "Denied").

In Power Apps, this view was be used to track a summary of respective grants by status and desired amount, helping managers track progress and focus resources where needed. 

Purpose

The view is intended to provide an easily accessible, ordered list of films along with their associated production companies and release years. This can help stakeholders in the film industry, or related fields quickly understand production trends, company outputs, and historical film data.

Structure

The view is constructed by joining two main tables:

• company_film: This table appears to link companies with films, probably containing mappings between a company's ID and a film's unique code.

• company: Contains details about companies, in this case, primarily used for retrieving the company name.

• film: Holds data about films, including titles, movie codes, and release years.

Fields Included

• movie_title: The title of the movie, sourced from the film table.

• production_company: The name of the company that produced the movie, sourced from the company table.

• release_year: The year the movie was released, also from the film table.

This view is useful for managing a digital catalog of films, particularly in Power Apps where it was used to display detailed lists of films produced by specific companies, their release years, which generally shows how production profiles have evolved.