Database development 7CS082 100% Solved Work

The assessment is carried out on an individual level, and it is crucial that every student diligently follows the guidelines specified in the following sections. The topic is divided into two separate parts, and the ultimate outcomes depend on the given Assessment Scenario. The assigned assignment entails the analysis, design, and development of the database. The final database must be implemented in any relational Database Management System (DBMs) of your choice. The assignment 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)

Assessment 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 student 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.

Key Guidelines (Part 1): Follow these STEPS carefully. You are required to:

1. Thoroughly review and analyze the provided “Assessment Scenario” in alignment with the fundamental principles of database design that you have acquired.
2. Produce a database design report to detail the implementation process including the Entity Relationship Model; normalisation process; detailing of data types, candidate keys, primary keys, constraints (foreign key and CHECK), etc.; Coding of tables and primary and foreign key creation; Query statements; Values insertion; Update and Delete data, etc; front-end interfaces.
3. The report should provide overall process and explanation of deriving entities, relationships, attributes and cardinalities. Normalisation, optimisation and performance of the database design should also be discussed in the report.
4. You have the option of selecting the programming language and platform for front-end development. Whether you choose MS Access forms or another programming language, provide pertinent information.
5. Ensure that your database is implemented with a distinct Database Management System (DBMS) if your front-end development platform of choice is Microsoft Access.
6. The word limit for Part 1 report is 1500 +/- 10% words.

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.
• Retrieve details of a specific film, including its unique code, title, year of first release, and crew members involved (with their respective roles).

Grant Information:

• Retrieve a list of grants received by production companies, including the grant title, funding organization, maximum monetary value, and proposal submission deadline.
• Obtain details of a specific grant application, including application date, desired amount, and the outcome (successful or unsuccessful).

• Data Manipulation Functional Requirements

The function categories outlined above will help you extract valuable information from your database to support various research and analysis tasks related to movie production companies in Europe. However, you should also provide the Add/Update/Delete functions for all those categories for adding new information, modifying existing information or removing the existing information from the database.

Key Guidelines (Part 2): Follow these STEPS carefully. You are required to:

1. Create a concise video recording lasting a maximum of 10 minutes to elucidate both the fundamental design principles and the operational system, showcasing the successful implementation of all functional requirements.
2. Organize your video into two distinct segments: In the first part, elucidate the critical aspects of your database design, encompassing the creation of the Entity-Relationship (ER) model through an analysis of the Assessment Scenario. Provide comprehensive insights into the normalization process and outline the potential Database Management System (DBMS) schema, highlighting key tables, data types, and other relevant information. It is advisable to dedicate the initial four-five minutes of your video to convey this vital information. You may choose to either reference your Part 1 report or create PowerPoint slides for this purpose. Please note that PowerPoint slides do not need to be submitted.
3. In the second part, during the system demonstration, ensure that you showcase all “Data Retrieval Functional Requirements.” Additionally, for the “Data Manipulation Functional Requirements,” specifically demonstrate the functions for “Add/Update/Delete.” To manage video duration, it is not necessary to exhibit these functions for every category, but it is imperative to demonstrate them for at least one of the outlined categories stipulated in the requirements. For example, include an “Adding” function to add a new employee, display the “Updating” function to modify existing film information, and illustrate the “Deleting” function for the removal of existing grant information. It is essential to emphasize that your Part 1 report must encompass these functions for all categories.

Furthermore, you should also submit a copy of the source code, which must contain the database script file and any other front-end-related code or files.