• Purpose: Tracks various activities or events occurring within the database, such as data insertions, updates, or deletions.

• Fields:

  • id: Primary key, auto-incremented.

  • event_type: Type of event logged.

  • table_name: Name of the table where the event occurred.

  • key_attribute: Key attribute of the record affected by the event.

  • event_description: Detailed description of the event.

  • user: User who performed the event.

  • event_timestamp: Timestamp of the event.

Description

This table is a part of the movie_production_companies database and is responsible for storing information about countries. It plays a crucial role in providing country-specific details, which are used in conjunction with other tables requiring such information (e.g., for specifying the country of companies, shareholders, or registration bodies).

Fields Description

• code:

  • Data Type: CHAR(2)

  • Description: This field serves as a unique identifier for each country. The usage of CHAR(2) indicates that the country code is a fixed-length string, which is typically aligned with standard formats like ISO 3166-1 alpha-2 country codes.

  • Constraints: NOT NULL, ensuring that every record in the table must have a country code. This field is also the primary key of the table, ensuring the uniqueness and optimized searchability for each country.

• name:

  • Data Type: VARCHAR(200)

  • Description: Stores the full name of the country. The VARCHAR(200) data type is used to accommodate country names of varying lengths, up to 200 characters.

  • Constraints: NOT NULL, signifying that every country record must have a name. There is also a unique index on this field (name_UNIQUE), ensuring that each country name is unique within the table.

  • isd_code:

• Data Type: VARCHAR(10)

• Description: Contains the International Subscriber Dialing (ISD) code for the country. This code is used for making international phone calls to the country. The VARCHAR(10) data type allows for storing ISD codes that may include not only the digits but also any necessary prefixes or formatting characters.

• Constraints: NOT NULL, indicating that every country must have an ISD code. This information is critical for contact details related to companies, shareholders, and employees who may have international phone numbers.

This table is part of the movie_production_companies database and stores information about cities. It is used in conjunction with other tables that require city details, such as company addresses. This entity was essentially imported hence was structured to accommodate csv.

Fields Description

• id:

Data Type: INT

Description: A unique identifier for each city. 

Constraints: NOT NULL, indicating that every record must have a city ID.

Primary Key: This field is the primary key of the table, ensuring each city has a unique identifier and enhancing search performance.

• name:

Data Type: VARCHAR(200)

Description: This field stores the name of the city. The VARCHAR data type with a limit of 200 characters is used to accommodate city names of varying lengths.

Constraints: NOT NULL, meaning every city record must have a name.

• country_code:

Data Type: CHAR(2)

Description: Stores the country code associated with the city. The use of CHAR(2) suggests that the country code is a fixed-length identifier, likely adhering to a standard format like ISO 3166-1 alpha-2.

Constraints: NOT NULL, every city must be associated with a country code.

Description

This table is a central component of the movie_production_companies database and is designed to store comprehensive information about various movie production companies. It holds essential details about each company, ranging from basic identification and location details to financial data and registration information.

Fields Description

• id

  • Data Type: VARCHAR(144)

  • Description: Serves as the unique identifier for each production company. The VARCHAR(144) format allows for various ID formats, including potentially encoded or complex identifiers.

  • Constraints: NOT NULL. It is the primary key of the table, and there is a unique index (id_UNIQUE) to ensure uniqueness.

• name

  • Data Type: VARCHAR(144)

  • Description: Stores the name of the production company. The VARCHAR(144) format is chosen to cater to names of varying lengths.

  • Constraints: NOT NULL, with a unique index (company_name_UNIQUE) to ensure no two companies have the same name.

• address

  • Data Type: VARCHAR(115)

  • Description: The physical address of the company. VARCHAR(115) is used to accommodate a variety of address formats.

  • Constraints: NOT NULL.

• zip_code

  • Data Type: INT

  • Description: The zip code for the company's location.

  • Constraints: NOT NULL.

• city_id

  • Data Type: INT

  • Description: Links to the city table, representing the city where the company is located.

  • Constraints: NOT NULL. This field is indexed (company_fk_city_idx) for faster joins with the city table.

• country_code

  • Data Type: CHAR(2)

  • Description: Country code, likely linking to the country table.

  • Constraints: NOT NULL. Indexed (country_id_idx).

• kind_of_organization_id

  • Data Type: INT

  • Description: Implies a relationship with the  kind_of_organization table and defines organization types.

  • Constraints: NOT NULL. Indexed (kind_of_organization_fk_idx).

• total_asset

  • Data Type: DECIMAL(10,2)

  • Description: Financial data representing the total assets of the company. Decimal type suitable for financial data, allowing for two decimal places. 

  • Constraints: NOT NULL.

• total_liability

  • Data Type: DECIMAL(10,2)

  • Description: Financial data representing the total liabilities of the company.

  • Constraints: NOT NULL.

• registration_body_id

  • Data Type: INT

  • Description: Links to the registration_body table, detailing the organization responsible for company registration.

  • Constraints: NOT NULL. Indexed (regulatory_body_fk_idx).

• registration_date

  • Data Type: DATE

  • Description: The date when the company was registered and formally established.

  • Constraints: NOT NULL.

Indexes

• id_UNIQUE: Ensures the uniqueness of company IDs.

• company_name_UNIQUE: Ensures no duplication of company names.

• Various Foreign Key Indexes: Improve query performance on joined operations with related tables like city, country, and organization types.

This table is part of the movie_production_companies database and establishes a many-to-many relationship between companies and films. It allows for the association of multiple companies with multiple films, accommodating scenarios where a single film may be produced by more than one company or a company may produce multiple films. This relationship is crucial for tracking the collaborations between different companies in the production of films and for managing the diverse portfolio of films produced by a single company.

Fields Description

company_id:

• Data Type: VARCHAR(144)

• Description: Acts as a foreign key linking to the id field in the company table. It identifies the company involved in the production of the film.

• Constraints: NOT NULL. This field is part of the composite primary key for the table, alongside film_movie_code.

film_movie_code:

• Data Type: VARCHAR(115)

• Description: Acts as a foreign key linking to the movie_code field in the film table. It specifies the unique code of the film produced by the company.

• Constraints: NOT NULL. This field is the other part of the composite primary key for the table.

Indexes

• fk_company_has_film_film1_idx: This index on the film_movie_code field improves query performance for operations involving film lookups.

• fk_company_has_film_company1_idx: This index on the company_id field enhances the efficiency of queries that retrieve company information related to film production.

Constraints

• fk_company_has_film_company1: A foreign key constraint ensuring integrity by linking company_id to the company table. It specifies that actions such as delete or update on the company table do not automatically cascade to this table.

• fk_company_has_film_film1: A foreign key constraint that maintains referential integrity by associating film_movie_code with the film table. Similar to the previous constraint, it prevents automatic cascading of delete or update actions to this table.

This table is part of the movie_production_companies database and is designed to create a many-to-many relationship between companies and shareholders. It serves the purpose of linking multiple shareholders to multiple companies, allowing for a detailed representation of shareholding structures within the film industry. This table is essential for tracking the investments and ownership stakes that different shareholders hold across various production companies. 

Fields Description

company_id:

• Data Type: VARCHAR(144)

• Description: Serves as a foreign key linking to the id field in the company table. It identifies the company in which the shareholder has an investment.

• Constraints: NOT NULL. This field is part of the composite primary key for the table, alongside shareholder_id.

shareholder_id:

• Data Type: INT

• Description: Acts as a foreign key linking to the id field in the shareholder table. It specifies the unique identifier of the shareholder who holds shares in the company.

• Constraints: NOT NULL. This field is the other part of the composite primary key for the table.

Indexes

• fk_company_has_shareholder_shareholder1_idx: This index on the shareholder_id field facilitates efficient query performance for operations involving shareholder lookups.

• fk_company_has_shareholder_company1_idx: This index on the company_id field enhances the efficiency of queries that retrieve company information related to shareholder investments.

Constraints

• fk_company_has_shareholder_company1: A foreign key constraint that ensures integrity by linking company_id to the company table. It specifies that actions such as delete or update on the company table do not automatically cascade to this table.

• fk_company_has_shareholder_shareholder1: A foreign key constraint that maintains referential integrity by associating shareholder_id with the shareholder table. Similar to the previous constraint, it prevents automatic cascading of delete or update actions to this table.

This table is a crucial component of the movie_production_companies database, dedicated to storing comprehensive details about films produced by various companies within the database. It serves as a repository of information for each movie, including its unique identifier, title, release year, and the date it was first released.

Fields Description

movie_code:

• Data Type: VARCHAR(115)

• Description: A unique identifier for each film, serving as the primary key. This field ensures each movie is distinctly recognized within the database.

• Constraints: NOT NULL. This field is marked as the primary key to ensure uniqueness and facilitate efficient data retrieval.

title:

• Data Type: VARCHAR(115)

• Description: The name of the film. This field captures the essence of the movie in a concise text form.

• Constraints: NOT NULL. Additionally, there is a unique index on this field (title_UNIQUE), guaranteeing that no two films can have the same title within the database.

release_year:

• Data Type: YEAR

• Description: The year in which the film was officially released to the public. This field helps categorize films chronologically.

• Constraints: NOT NULL. Ensures that the release year of every film is recorded, providing temporal context for the movie's production and release.

first_released:

• Data Type: DATE

• Description: The specific date on which the film was first released. This information is more precise than the release year and is crucial for understanding the exact timing of a film's introduction to audiences.

• Constraints: NOT NULL. This ensures that the precise date of the film's initial release is captured in the database.

Indexes

• title_UNIQUE: A unique index on the title field that enforces the uniqueness of film titles, preventing duplicate entries and facilitating quick searches based on movie titles.

• movie_code_index: An invisible index on the movie_code field. While invisible to the optimizer by default, it can be used explicitly in queries or made visible as needed. This index supports efficient access and management of film records based on their unique identifiers.

Description

The kind_of_organization table is part of the movie_production_companies database. It is designed to classify the various types of organizations involved in movie production. This table is essential for categorizing companies based on their organizational structure or business type.

Fields Description

• id

  • Data Type: INT

  • Description: Acts as the primary key and a unique identifier for each type of organization. 

  • Constraints: NOT NULL, AUTO_INCREMENT. This field is the primary key, ensuring a unique and auto-incremented value for each entry.

• name

  • Data Type: VARCHAR(255)

  • Description: Represents the name or description of the organization type. VARCHAR(255) is used to accommodate a wide range of organization names, including potentially lengthy descriptions.

  • Constraints: NOT NULL. There is a unique index (name_UNIQUE) on this field, ensuring that each organization type is distinct.

Indexes

• name_UNIQUE: A unique index on the name column to ensure that all entries in this table are unique, preventing duplicate entries of organization types.

Description

The registration_body table is a component of the movie_production_companies database, created to store information about various registration bodies. These are typically organizations responsible for the official registration and regulation of film production companies, usually within specific countries.

Fields Description

• id

  • Data Type: INT

  • Description: Serves as the primary key and a unique identifier for each registration body. The use of INT and AUTO_INCREMENT implies that this identifier is numerical and automatically increases with each new entry.

  • Constraints: NOT NULL, AUTO_INCREMENT. This field is the primary key, ensuring uniqueness and systematic assignment of new identifiers.

• name

  • Data Type: VARCHAR(255)

  • Description: The name of the registration body. The VARCHAR(255) data type allows for accommodating various names, including potentially lengthy ones.

  • Constraints: NOT NULL. There is a unique index (name_UNIQUE) on this field to ensure that each registration body name is unique within the table.

• country_code

  • Data Type: CHAR(2)

  • Description: Represents the country code where the registration body operates, likely adhering to a standard format like ISO 3166-1 alpha-2.

  • Constraints: NOT NULL.

  • Notes: Indexed (reg_country_fk_idx) to optimize searches and joins based on the country code.

• price

  • Data Type: INT

  • Description: This field likely indicates the cost or fee associated with registering a company with this body.

  • Constraints: NOT NULL.

  • Notes: Stored as an integer, denoting the registration price in a standard currency unit.

Indexes

• name_UNIQUE: Ensures that each registration body is uniquely identified by its name.

• reg_country_fk_idx on country_code: Improves the efficiency of queries involving the country code, facilitating quicker searches and data retrieval based on specific countries.

Description

The shareholder table is an essential part of the movie_production_companies database. It is structured to record detailed information about individuals who hold shares in various film production companies. This table tracks shareholder identity, contact details, and their connection to specific companies and countries.

Fields Description

• id

Data Type: INT

Description: Acts as the primary key, a unique identifier for each shareholder. The AUTO_INCREMENT property ensures that this identifier is numerical and automatically increases with each new entry.

Constraints: NOT NULL, AUTO_INCREMENT.

• first_name

Data Type: VARCHAR(50)

Description: The first name of the shareholder.

Constraints: NOT NULL.

• last_name

Data Type: VARCHAR(50)

Description: The last name of the shareholder.

Constraints: NOT NULL.

• country_code

Data Type: CHAR(2)

Description: The country code associated with the shareholder, likely referencing their nationality.

Constraints: NOT NULL. Indexed (nation_fk_idx). This is a foreign key reference to the country table.

• place_of_birth

Data Type: VARCHAR(75)

Description: Indicates the birthplace of the shareholder.

Constraints: NOT NULL.

• mother's_maiden_name

Data Type: VARCHAR(45)

Description: The maiden name of the shareholder’s mother, often used as a security question or for additional identification purposes.

Constraints: NOT NULL.

• father's_first_name

Data Type: VARCHAR(45)

Description: The first name of the shareholder’s father.

Constraints: NOT NULL.

• personal_telephone

Data Type: VARCHAR(25)

Description: The personal telephone number of the shareholder.

Constraints: NOT NULL.

• national_insurance_number

Data Type: VARCHAR(30)

Description: The national insurance number of the shareholder, a unique identifier in some countries.

Constraints: NOT NULL.

• passport_number

Data Type: VARCHAR(25)

Description: The passport number of the shareholder.

Constraints: NOT NULL.

Indexes

shareholder_company_fk_idx on company_id: Facilitates faster query operations and data retrieval involving the company information of the shareholders.

nation_fk_idx on country_code: Enhances the efficiency of queries that involve national details of the shareholders.

Foreign Key Constraints

nation_fk_for_shareholders: Ensures referential integrity with the country table.

shareholder_company_fk: Ensures referential integrity with the company table.

Description

The company_grant table within the movie_production_companies database is designed to establish and manage the relationship between companies and the grants they apply for or receive. This table acts as a junction table in a many-to-many relationship, linking companies with various grants.

Fields Description

• company_id

Data Type: VARCHAR(144)

Description: A reference to the id field in the company table. It identifies the company involved in a grant application or award.

Constraints: NOT NULL. It is part of the composite primary key and is indexed (fk_companies_has_grant_companies1_idx) for efficient querying.

• grant_id

Data Type: VARCHAR(24)

Description: A reference to the id field in the grant_request table. This field identifies the specific grant associated with a company.

Constraints: NOT NULL. It is part of the composite primary key and is indexed (fk_companies_has_grant_grant1_idx) to enhance search performance.

Primary Key

Composite Key (company_id, grant_id): The combination of company_id and grant_id serves as the primary key for the table. This composite key ensures that each record uniquely identifies a link between a specific company and a specific grant, preventing duplicate entries for the same company-grant pairing.

Indexes

fk_companies_has_grant_grant1_idx on grant_id: Facilitates faster query operations and data retrieval involving grant information.

fk_companies_has_grant_companies1_idx on company_id: Enhances the efficiency of queries that involve company information.

Description

The employee table is a key component of the movie_production_companies database, designed to maintain detailed records of individuals employed by various movie production companies. It includes comprehensive personal and professional information about each employee.

Fields Description

• id

  • Data Type: VARCHAR(144)

  • Description: Serves as the unique identifier for each employee. The VARCHAR(144) format allows for a range of ID formats, potentially including alphanumeric and complex structures.

  • Constraints: NOT NULL. It is the primary key of the table, ensuring each employee has a unique identifier.

• first_name

  • Data Type: VARCHAR(45)

  • Description: The employee's first name.

  • Constraints: NOT NULL.

• middle_name

  • Data Type: VARCHAR(45)

  • Description: The employee's middle name.

  • Constraints: NOT NULL.

• last_name

  • Data Type: VARCHAR(45)

  • Description: The employee's last name.

  • Constraints: NOT NULL.

• gender

  • Data Type: ENUM('M', 'F')

  • Description: The employee's gender.

  • Constraints: NOT NULL.

• date_of_birth

  • Data Type: DATE

  • Description: The date of birth of the employee.

  • Constraints: NOT NULL.

• company_id

  • Data Type: VARCHAR(144)

  • Description: A reference to the id field in the company table, indicating which company the employee works for.

  • Constraints: NOT NULL. Indexed (company_fk_idx). This field is a foreign key that references the company table.

• employee_role

  • Data Type: VARCHAR(144)

  • Description: Describes the employee's role or job title within the company.

  • Constraints: NOT NULL. Indexed (employee_role_idx) to facilitate searches based on job roles.

• date_started

  • Data Type: DATE

  • Description: The date when the employee began working at the company.

  • Constraints: NOT NULL.

• email_address

  • Data Type: VARCHAR(115)

  • Description: The employee's email address, used for communication and identification purposes.

  • Constraints: NOT NULL. There is a unique index (email_address_UNIQUE) to ensure that each email address is unique across all employees.

Indexes

• email_address_UNIQUE on email_address: Ensures that each employee has a unique email address.

• company_fk_idx on company_id: Enhances the efficiency of queries that involve company information.

• employee_role_idx on employee_role: Facilitates quicker searches and sorting by employee roles.

Foreign Key Constraints

• employee_company_fk: Ensures referential integrity with the company table. It links each employee to their respective company.

Description

The phone_number table is part of the movie_production_companies database. It is designed to store phone numbers associated with employees, providing a way to record multiple contact numbers per employee along with their descriptions.

Fields Description

• id

  • Data Type: INT

  • Description: Acts as the primary key and a unique identifier for each phone number entry. The usage of INT with AUTO_INCREMENT suggests that this identifier is numerical and automatically incremented for each new entry.

  • Constraints: NOT NULL, AUTO_INCREMENT.

• employee_id

  • Data Type: VARCHAR(144)

  • Description: A reference to the id field in the employee table. This field indicates which employee the phone number belongs to.

  • Constraints: NOT NULL. Indexed (employee_fk_idx). This field is a foreign key that references the employee table.

• phone

  • Data Type: VARCHAR(35)

  • Description: The phone number of the employee. The VARCHAR(35) format is used to accommodate various international phone number formats.

  • Constraints: NOT NULL.

• description

  • Data Type: VARCHAR(45)

  • Description: A brief description of the phone number, such as 'Home', 'Work', or 'Mobile'. This helps in identifying the context or primary use of the phone number.

  • Constraints: NOT NULL. It has a default value of 'Home'.

Indexes

• employee_fk_idx on employee_id: Enhances the efficiency of queries and operations that link phone numbers to specific employees.

Foreign Key Constraints

• employee_fk: Ensures referential integrity with the employee table. It links each phone number to the corresponding employee record.

Description

The role table is part of the movie_production_companies database, specifically tailored to define various roles occupied by crew members in the film production industry. This table is crucial for cataloging distinct roles within production crews, such as directors, actors, editors, producers, and other specialized positions.

Fields Description

• id

Data Type: INT

Description: Acts as the primary key and unique identifier for each role. The INT data type with AUTO_INCREMENT property indicates that this identifier is numerical and systematically increases for each new entry.

Constraints: NOT NULL, AUTO_INCREMENT. This field is the primary key, ensuring each role is uniquely identified.

• name

Data Type: VARCHAR(55)

Description: Represents the name or title of the role. The VARCHAR(55) format is chosen to accommodate a variety of role names, which could range from generic to highly specific titles.

Constraints: NOT NULL. There is a unique index (name_UNIQUE) on this field to ensure that each role name is distinct within the table.

Indexes

role_name_index on (name, id): Enhances the efficiency of queries and operations involving role names, allowing for faster searches and sorting by role name and then by ID.

name_UNIQUE: Ensures that each role has a unique name, preventing duplication and maintaining clarity in role identification.

Description

The crew table is a crucial component of the movie_production_companies database. It functions to associate employees with specific roles within the production crew. This table is essential for tracking which crew members (identified by their employee IDs) are assigned to various roles (defined in the role table) in film production projects.

Fields Description

• s_n (Serial Number)

  • Data Type: INT

  • Description: Acts as the primary key and serves as a unique serial number for each entry in the table. The  s_n field's  AUTO_INCREMENT property ensures that each new record is automatically assigned a unique serial number, simplifying data entry and management.

  • Constraints: NOT NULL, AUTO_INCREMENT. It is the primary key of the table.

• employee_id

  • Data Type: VARCHAR(144)

  • Description: This field is a reference to the id field in the employee table. It identifies the specific employee who is part of the crew.

  • Constraints: NOT NULL. It is indexed (employee_fk_idx) to enhance query performance, particularly when joining with the employee table.

• role_id

  • Data Type: INT

  • Description: Links to the id field in the role table, identifying the specific role that the crew member fulfills. This connection facilitates the categorization of employees into various production roles.

  • Constraints: NOT NULL. Indexed (role_fk_idx) to improve efficiency in operations involving role-based queries.

Indexes

• employee_fk_idx on employee_id: This index optimizes searches, sorting, and join operations based on the employee's ID, thereby enhancing the table's functionality in relation to employee data.

• role_fk_idx on role_id: Facilitates efficient querying and sorting by the role ID, allowing for quick access to crew assignments based on specific roles.

Description:

This table acts as a central repository for crew-related data, emphasizing financial aspects such as hourly rates, daily bonuses, scene bonuses, and other incentives. It supports payroll management, budgeting for film production costs, and provides a structured way to analyze the distribution of financial resources among crew members based on their roles and contributions to a film.

• Description:

This table extends the crew management capabilities by storing comprehensive details about crew members' rates, bonuses, and specific roles in film projects. It serves as a nexus for tracking financial arrangements, performance incentives, and contractual agreements tied to individual crew members and their roles in film production.

Fields Description:

• crew_id:

Data Type: VARCHAR(144)

Description: References the employee_id from the crew table, uniquely identifying a crew member who is part of a specific film project.

Constraints: NOT NULL. This is part of the composite primary key for the table, ensuring unique identification in conjunction with movie_code and role_id.

• movie_code:

Data Type: VARCHAR(115)

Description: Links to the movie_code in the film table, identifying the specific film project the crew member is involved in.

Constraints: NOT NULL. Part of the composite primary key, it ensures each record uniquely identifies the involvement of crew members in specific films.

• role_id:

Data Type: INT

Description: Associates with the role_id from the role table, specifying the role the crew member fulfills in the film project.

Constraints: NOT NULL. Integral to the primary key, it helps in specifying and distinguishing roles within the same film project.

• hourly_rate, daily_bonus, scene_bonus, completion_bonus, contractual_incentive:

Data Type: DECIMAL(10,2)

Description: These fields record various forms of compensation that can be awarded to crew members. They include hourly rates, daily bonuses, specific bonuses for scenes, bonuses upon completion of the project, and additional contractual incentives that might be negotiated.

Constraints: These fields are NULLABLE to accommodate different types of compensation agreements, where some bonuses or rates may not apply to every crew member.

Indexes:

• movie_code_fk_idx on movie_code: Optimizes searches and joins based on the film project, enhancing performance when querying crew data by specific films.

• role_fk_idx on role_id: Facilitates faster querying and sorting by role, crucial for operations that manage or analyze crew roles across various projects.

Foreign Keys:

• crew_id_info_fk:

  • Reference: Binds crew_id to the crew table, ensuring data consistency and integrity between the employee's base record and their detailed role information.

• movie_code_fk:

  • Reference: Ensures the movie code matches with valid entries in the film table, maintaining data integrity across film-related tables.

• role_id_fk:

  • Reference: Ensures the role ID corresponds with valid roles defined in the role table, enforcing consistency in role assignments.

Operational Benefits:

The design of the crew_info table allows for detailed financial tracking and management of film production personnel. It supports a range of operational needs from budgeting and financial forecasting to compliance with contractual obligations. The structured approach to managing crew data ensures that film production companies can maintain accurate records and conduct detailed financial analyses to optimize production costs and crew utilization effectively.

Description

The department table within the movie_production_companies database is tailored to manage and categorize various administrative departments integral to the functioning of film production companies. This includes departments such as Human Resources, Legal Affairs, Marketing, Finance, and others that play key roles in the administrative and support functions of the company.

Fields Description

• id

  • Data Type: INT

  • Description: Acts as the primary key and a unique identifier for each department. The use of INT with the AUTO_INCREMENT attribute suggests that the identifier is numerical and systematically increases with each new department entry. This approach simplifies the management and reference of department records.

  • Constraints: NOT NULL, AUTO_INCREMENT. It serves as the primary key, ensuring a unique identifier for each department.

• name

  • Data Type: VARCHAR(45)

  • Description: Stores the name of the department, reflecting its administrative role within the film production company. The VARCHAR(45) format allows for flexibility in naming, accommodating a range of department names from concise to moderately descriptive titles.

  • Constraints: NOT NULL. A unique index (name_UNIQUE) is applied to this field to ensure each department name is distinct within the table, preventing any duplication.

Indexes

• name_UNIQUE on name: Guarantees the uniqueness of department names within the table. This unique index prevents the creation of duplicate department records, maintaining clarity and integrity in departmental categorization.

Description

The department_address table in the movie_production_companies database is designed to store and manage the physical address details of various departments within film production companies. This table links department IDs and company IDs to specific building and address details, providing a clear and organized way to track where each department is located within the company's infrastructure.

Fields Description

• department_id

  • Data Type: INT

  • Description: Refers to the id field in the department table. This field identifies the specific department within a company.

  • Constraints: NOT NULL. It is part of the composite primary key and is indexed (fk_department_has_companies_department1_idx) for efficient querying.

  • Foreign Key: The fk_department_has_companies_department1 constraint establishes a foreign key relationship with the department table.

• company_id

  • Data Type: VARCHAR(144)

  • Description: A reference to the id field in the company table, identifying the company to which the department belongs.

  • Constraints: NOT NULL. Part of the composite primary key and indexed (fk_department_has_companies_companies1_idx) for effective data retrieval.

  • Foreign Key: The company_address constraint links to the company table, ensuring referential integrity.

• building

  • Data Type: VARCHAR(144)

  • Description: Specifies the building name or identifier where the department is located. This detail helps in pinpointing the exact location of a department within a company's premises.

  • Constraints: NOT NULL.

• address

  • Data Type: VARCHAR(144)

  • Description: The full address where the department is situated. This field includes details that go beyond the building identifier, providing a comprehensive location description.

  • Constraints: NOT NULL.

Primary Key

• Composite Key (department_id, company_id): This combination of department and company IDs serves as the primary key for the table, ensuring that each record uniquely identifies a department's address within a specific company.

Indexes

• fk_department_has_companies_companies1_idx on company_id: Optimizes searches and join operations involving company information.

• fk_department_has_companies_department1_idx on department_id: Enhances the efficiency of database operations that involve departmental data.

Foreign Key Constraints

• company_address: Links each record to a specific company in the company table.

• department_address: Ensures that each department address is accurately associated with a department in the department table.

Description

The staff_salary table is specifically designed to manage the salary details of non-crew staff members. This table holds information about staff salaries, working hours, and job levels, providing a comprehensive view of employee compensation within the company.

Fields Description

• employee_id

  • Data Type: VARCHAR(144)

  • Description: A reference to the id field in the employee table. This field uniquely identifies each staff member whose salary details are being recorded.

  • Constraints: NOT NULL. It has a unique index (employee_id_UNIQUE) to ensure that salary details are unique to each employee.

• working_hours

  • Data Type: ENUM('Full-time', 'Part-time')

  • Description: Specifies the working hours of the staff member, categorizing them as either full-time or part-time. This classification is important for understanding employment terms and calculating appropriate salaries.

  • Constraints: NOT NULL.

• job_level

  • Data Type: ENUM('Entry', 'Mid', 'Senior', 'Executive')

  • Description: Indicates the job level of the staff member. This field categorizes staff into different levels such as Entry, Mid, Senior, or Executive, which can be indicative of their experience, responsibility, and salary scale.

  • Constraints: NOT NULL.

• salary

  • Data Type: DECIMAL(10,2)

  • Description: The salary of the staff member. The DECIMAL(10,2) format is used to accurately represent salary figures, which may include decimal values (e.g., cents in dollar amounts).

  • Constraints: NOT NULL.

Indexes

• employee_id_UNIQUE on employee_id: Ensures that each staff member has unique salary information in the table.

• staff_salary_fk_idx on employee_id: Enhances query performance, particularly for operations that involve looking up salary details based on the employee's ID.