Database development

The Project is comprised of two distinct components:

Part 1: Database design, optimization, and implementation - report (Learning Outcome 1)

Part 2: Demonstration of key design and implementation – video demo + source code/scripts (Learning Outcome 2)

Project Scenario

The research team has requested the development of a database to support a project focused on movie production companies. The objective of this project is to employ machine learning, neural networks, and other methodologies to extract pertinent information regarding the status of movie production companies in Europe, as well as the overall condition of this industry within a specific group of countries, including the United Kingdom. The research institute, whose identity will not be disclosed, has commissioned the development of a data analytics application as part of this project. It is important to note that your responsibility lies in developing the central, server-side database that serves as the foundation for this application. Furthermore, it is worth mentioning that the application is intended to be open source, thereby ensuring its accessibility to a wide range of users.

This is a machine learning application designed to operate on a database, with the objective of discerning the association between various facets of the industry. These facets encompass funding prospects as well as the establishment of novel production businesses or studios.

The database comprehensively documents all production companies operating within the European region. It includes essential information such as the company's name, address, ZIP code, city, nation, kind of organisation (e.g., non-profit), number of employees, and net value, which is determined by subtracting total liabilities from total assets. Each production company is required to register its name with a single local government office, such as Companies House in the UK, on a designated date. It is possible for a company to have multiple shareholders. The regulatory body commonly necessitates the provision of certain details pertaining to shareholders, such as their place of birth, the maiden name of their mother, the first name of their father, a single personal telephone number, a national insurance number (each European country possesses an analogous distinct identifier), and a passport number. Moreover, it is important to note that the registration process incurs a financial obligation, such as a fee of £12 in the United Kingdom.

The database additionally stores the employees' data pertaining to each company, with the assumption that each employee is affiliated with a singular producing company. The categorization of people into crew and staff within movie production businesses is a result of the intricate organisational structure and the diverse range of talents and vocations required. The crew comprises three primary factions: the actors, the director(s), and the individuals responsible for various other tasks associated with the production, such as producers, editors, production designers, costume designers, and composers, among others. All remaining personnel are categorised within the staff group, encompassing those who hold responsibilities in areas such as human resources, advertising, and other related functions. Employees are characterised by an employee identification number, their given name, surname, and maybe a middle name. Additionally, their date of birth and commencement date may be included. In addition, the contact information of every employee is documented, encompassing either a singular telephone number or several numbers, accompanied by a corresponding description for each entry. Every employee possesses a solitary email address as well.

The compensation for crew members is determined on an hourly basis, and this information is meticulously documented in the database, along with an additional incentive that is contingent upon the terms of their contractual agreement. In the film industry, actors are typically compensated with a daily bonus for each day they work, as well as an additional bonus for each scene they successfully complete. Conversely, directors receive a bonus upon the conclusion of the shooting process. Similarly, crew members who contribute to the production in various capacities, such as producers or costume designers, are rewarded with a bonus at the end of the shooting period, while their specific role is duly documented.

The staff members are provided with a monthly wage, as well as a designated number of working hours per day, typically following a full-time schedule from 9 AM to 5 PM. In addition, it should be noted that every member of the staff is affiliated with a certain department, such as advertising, and this affiliation is associated with a designated building and address, both of which are meticulously documented in the database.

The database comprehensively stores information on all films produced by each respective production firm. In a comprehensive manner, pertinent details pertaining to each movie are meticulously documented, including a universally unique movie code, akin to the International Standard Book Number (ISBN) utilised for books, the movie's title, as well as the year and first release date (excluding subsequent release dates, which are deemed inconsequential and hence should not be included in the record). Additionally, the database maintains a record of every crew person involved in the film, along with their respective responsibilities. It is possible for a crew member to assume one or more roles within the same film, and each position is accompanied by a corresponding description. In the context of film production, it is common for a movie to feature either a solitary protagonist or multiple protagonists. Additionally, it is not uncommon for an actor to portray one or multiple characters inside a film, or even make a brief appearance known as a cameo.

One of the primary objectives of this research is to offer valuable insights into the effects of funding and grants within the film industry. In order to achieve this objective, it is imperative that the database possesses the capability to comprehensively document all financial support received by each production business. The essential components to be included in the proposal are the grant's official title, the funding organisation (e.g., a specific government or European Union funds like the ERDF), the maximum monetary value allocated for the award, and the specified deadline for proposal submission. Subsequently, the database should be designed to capture the application date, desired amount, and outcome (either successful or unsuccessful) for each company's grant application. A grant may be awarded to either a sole production company or distributed among multiple production companies.

Upon completion of the database, the project will proceed to execute a series of machine learning algorithms in order to conduct comprehensive data analysis. This analysis will focus on the various grants and their respective impact, with the objective of examining the effects of such financing against a predetermined set of criteria. At this point of the project, no supplementary information has been provided.

PART 1: Database design, optimization, and implementation (Weighting: 60%)

Individual must produce a comprehensive ER diagram of the database and produce an implementations of the design using any DBMS of your choice. To produce the ER diagram, you may use the ER modelling facilities of either MS Access or MySQL Workbench. The implementations must be done in a relational data base management systems (DBMS) of your choice, such as MySQL, PostgreSQL, Oracle Database, Microsoft SQL Server, MS Access etc. Moreover, upon completing the schema design, you will need to populate the databases with some test data (i.e., random fake data) to validate the design. The implementations should include sufficient test data to demonstrate necessary functionalities.

PART 2: Demonstration of key design and implementation (Weighting: 40%)

You must demonstrate your primary database design and implementation, as detailed in your Part 1 report, through a maximum ten-minute video recording. In this demonstration, please discuss your key design elements in detail and present your operational system, which must effectively meet the essential functional requirements outlined below.

Functional Requirements

1. Data Retrieval Functional Requirements

Based on the provided scenario, here are some data extraction functions your implementation should support for your database:

Retrieve Production Companies' Information:

• Get a list of all production companies in the European region.

• Retrieve specific details for a given production company, including its name, address, ZIP code, city, nation, organization type, number of employees, and net value.

Retrieve Shareholder Information:

• Obtain a list of shareholders for a particular production company.

• Retrieve details of a specific shareholder, encompassing essential information such as place of birth, name, personal telephone number, national insurance number, and passport number, among others.

Retrieve Employee Information:

• Get a list of all employees working for a specific production company.

• Retrieve details of a specific employee, including employee identification number, given name, surname, middle name (if available), date of birth, commencement date, contact information (telephone numbers and email address), and department affiliation.

Retrieve Compensation Information:

• Retrieve compensation details for crew members (actors, directors, and others), including hourly pay and bonuses.

• Obtain compensation details for staff members, including monthly wage, working hours, and department affiliation.

Retrieve Film Information:

• Get a list of all films produced by a particular production company.

Προγραμματισμός Βάσης Δεδομένων MySQL SQL Κατασκευή Βάσης Δεδομένων PHP

Ταυτότητα Εργασίας: #37423965

Σχετικά με την εργασία