Database

development
7CS082

MAQBOOL HUSSAIN

Sensitivity: Internal
Database development 7CS082
Contents
Module Leader......................................................................................................................................3
Key dates and details.............................................................................................................................3
Learning Outcomes covered in this Assignment....................................................................................4
Description of the assessment...............................................................................................................4
Assessment Scenario.............................................................................................................................4
PART 1: Database design, optimization, and implementation (Weighting: 60%)..................................6
PART 2: Demonstration of key design and implementation (Weighting: 40%)......................................7
Feedback...............................................................................................................................................9
Formal Submission................................................................................................................................9
Submission of your work.....................................................................................................................10
Marking Scheme..................................................................................................................................11

Sensitivity: Internal
Module Leader
Maqbool Hussain
Email: [email protected]
Phone:
Office Hours: 2 hours

Key dates and details

Assessment Type: Part-1: Database design, optimization, and

implementation - report
Part-2: Demonstration of key design and
implementation – video demo + source code/scripts
Assessment weighting: 100%
Word Count/Demo details: Part-1 1500+/- 10%, Part-2 Video max 10-minutes.
Learning Outcomes: 1 (Part-1), 2 (Part-2)
Submission Method: Turnitin Reports + Video Demo + Source Code with
supporting script and other documents
Date Set: 9:30am UK time, 5/09/2023
Submission Date: Part-1: 11:59 am UK time, 02/01/2024 09/01/2024
Part-2: 11:59 am UK time, 05/01/2024 12/01/2024
Provisional Feedback Release: 09:00 UK time, (15 working days After Submission)

Sensitivity: Internal
Learning Outcomes covered in this Assignment
1. To demonstrate a critical and in-depth awareness of the theory, methods, design issues,
development, deployment, and maintenance of modern database systems.
2. To demonstrate a mature and culturally sensitive ability to critically evaluate and select
database products, development methodologies and implementation strategies, and apply
these to design and implement database systems to a professional standard within a
globalised, industrial context.

Description of the assessment

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

Sensitivity: Internal
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

Sensitivity: Internal
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

Sensitivity: Internal
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.

Sensitivity: Internal
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).

2. 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:
a. 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.
b. In the second part, during the system demonstration, ensure that you showcase all "Data
Retrieval Functional Requirements." Additionally, for the "Data Manipulation Functional

Sensitivity: Internal
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.

3. 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.

Feedback
An in-progress check will take place during the practical sessions on week 10 and week 11. The in-
progress check is to provide you a formative feedback of the progress made so far and recommend
you possible areas for improvement prior to the formal submission. Summative feedback and marks
will be given in Turnitin within 15 working days after your submission.

Formal Submission
For both parts of the assignment, you will have two submission points for Part 1 and Part 2,
respectively:

1. Turnitin submission point for reports

Formatting Requirements: Please submit Part 1 report as a single Word or PDF document in the
Blackboard Assignment Submission link for the module. The file name must use the following
format:

For part 1: nnnnnnnnn-7CS082-report-part1.docx

where nnnnnnnn is your student number.

2. Submission point for the video, source code, database files of design, implementations,
and the front-end development. If the student is using a specific database development
tool outside of those mentioned above, it has to be provided as well.

Formatting Requirements: The video, data and source code should be included in a single zip file
and submit in the Blackboard Assignment Submission link for data. The file name must use the
following format:

For part2: nnnnnnnnn-7CS082-VideoAndSourCode-part2.zip

Sensitivity: Internal
where nnnnnnnn is your student number.

Please note that all assignments will be checked for plagiarism. The students will be provided with
a “Plagiarism Check-point”. Before submitting to original submission point, you can generate
plagiarism report. Any submission found to have a plagiarism rate exceeding 25% will be
considered a serious academic offense, necessitating the initiation of appropriate university
procedures and disciplinary actions. Plagiarism undermines the principles of academic integrity
and originality, and as such, it is imperative that all work submitted is a genuine reflection of a
student's own understanding and effort.

Failure to submit all these items by the specified deadline and without an authorized extension will
result to a FAIL grade.

Submission of your work

The University of Derby has now implemented a general policy of anonymous marking and all the
Turnitin Submission system will hide your name from your tutors, until after moderation. This
module has an exemption due to the face-to-face nature of the formative reviews; your tutor will
need the student number to link your source-code with your report for your course work. As a
result, you MUST ensure that you include your student number in the submission title of your work
for both your completed report and source code.

If you do not do this, it will be impossible for your tutor to provide any formative or summative
feedback during the face-to-face formative reviews and final marking, because they will not be able
to find your work.

10

Sensitivity: Internal
Marking Scheme

Assessment Criteria for assignment 1: Database design, optimization, and implementation (60%)

Criteria 5-39% 40 – 49% 50 – 59% 60 – 69% 70-79% 80-89% 90-100%

I: Introduction Unstructured Unstructured Must have Structured Must have Structured Must have Structured Must have Structured
Introduction Introduction with Introduction with Introduction with Introduction with Introduction with covering
(An introduction section
covering not covering all aspect of covering all aspects of covering all aspects of covering all aspects of all aspects of the scenario
which summarize the
all aspects of scenario. the scenario but not the scenario clearly. The the scenario clearly. clearly. The section also
key Assessment Scenario
scenario. clearly outlined. The section also highlights The section also highlights overall process
with key information Or
section also highlights overall process of highlights overall of database design with all
and requirements in Or
Structured overall process of database design with process of database relevant details.
structured format.
Structured Introduction covering database design with few details. design with important
Furthermore, it also
Introduction important aspects of few details. key details.
highlights the overall
covering few scenario.
process of database
aspects of
analysis and design)
scenario.
(15%)

Tentative Words: 300

II: Analysis and Design Inadequate Poor Analysis and Fair Analysis and Design: Good Analysis and Very Good Analysis Excellent Analysis and
Analysis and Design: Design: and Design: Design:
(The section include the The essential
Design:
outcomes of analysis - The key artifacts – components, such as All essential elements, All key elements, Exceptional attention to
entities, attributes, The key such as entity, entities, attributes, and such as entities, including entities, detail in identifying and
relationships, ER model, artifacts – attributes, relationships, are attributes, and attributes, and defining all elements, such
relational schema, and such as entity, relationship are accurately identified for relationships, are relationships, are as entities, attributes, and Meets all
refined design after attributes, correctly identified all concepts. However, accurately identified for meticulously identified relationships, for all criteria in
Normalisation) relationship for not all concepts. the resulting Entity- all relevant concepts. and accurately defined relevant concepts. 80-89%
are not Subsequent Relationship (ER) model for all relevant range,

Sensitivity: Internal
(50%) correctly outcomes ER, refined does not fully The resulting Entity- concepts. The resulting Entity- plus
identified. design schema is encompass all critical Relationship (ER) model Relationship (ER) model demonstra
Tentative Words: 600 The resulting Entity-
Subsequent partial. requirements, provides a generally exemplifies an impeccable, tes
Relationship (ER)
outcomes ER, particularly in terms of complete representation comprehensive, and exception
model demonstrates a
refined design correctly representing of key requirements, precise representation of al ability
comprehensive and
schema is not the multiplicities for with some minor all critical requirements, and
accurate
correct. relationships, resulting in inaccuracies or including an excellent insight,
representation of all
a partial refined design improvements possible. depiction of relationship indicating
critical requirements,
schema. This resulting the final multiplicities, setting a the
including precise
refined design schema high standard for design highest
depiction of
with major flaws. excellence. The final level of
relationship
refined design schema technical
multiplicities.
stands as a complete and competen
However, the final
flawless representation, ce.
refined design schema
devoid of any discernible
has flaws.
deficiencies.

III: Implementation Only the The database script The database script with The database script with The database script The database script with
database with data is provided data is provided and data is provided and with data is provided data is provided and
(This section provides
script is and incorporates incorporates front-end incorporates front-end and incorporates front- incorporates front-end
script of the database
provided that front-end interfaces interfaces that are either interfaces that are end interfaces that are interfaces that are
schema with key data
adheres to the that are either unsuitable or incomplete appropriate and appropriate and appropriate and complete
records. Furthermore, it
designated unsuitable or for specific 'Data complete for specific complete for specific for all 'Data Retrieval
must provide all front-
design, incomplete for Retrieval Functional 'Data Retrieval 'Data Retrieval Functional Requirements.',
end interfaces for all
accompanied specific 'Data Requirements.', and Functional Functional and 'Data Manipulation
functional requirements)
by the Retrieval Functional 'Data Manipulation Requirements.', and Requirements.', and Functional Requirements’.
(30%) requisite data. Requirements.' Functional either unsuitable or appropriate and in-
Requirements’. incomplete for 'Data complete for 'Data
Manipulation Functional Manipulation

12

Sensitivity: Internal
Tentative Words: 450 Requirements’. Functional
Requirements’.

IV: Conclusion The The conclusion is The conclusion The conclusion provides The conclusion The conclusion is
conclusion is narrow in scope and summarises the key a comprehensive summarises the particularly well-written,
(5%)
very brief, does not provide a themes and results of summary of the report's report's main themes, providing a thorough
Tentative Words: 150 offering no full assessment of the report. main points, results, and findings, and broader summary of the report's
useful the report's main observations. implications in a key arguments and
The key findings or
overview of themes. thorough and findings.
implications are It examines the findings'
the report's considered manner.
It mentions major mentioned, but there is significance and It presents a thorough
substance.
results in passing no in-depth or extensive prospective It indicates a examination of the
Fails to without delving into study. consequences, giving a knowledge of the implications,
connect the their relevance or clear sense of their significance and demonstrating an
It provides a sense of
report's main consequences. importance. relevance of the report extensive knowledge of
closure, however it does
themes or to the larger context. the subject.
The conclusion may not exceptionally tie The conclusion
provide any
appear to be together all components successfully concludes The conclusion The conclusion
meaningful
unrelated to the rest of the report. the report, providing the contributes to a strong successfully connects all
closure.
of the report. reader with a pleasant sense of closure and components of the report,
sense of completion. leaves the reader with providing the reader with
a lasting impression. a lasting and meaningful
impression.

% Mark Nothing of Merit: Nothing of value is contained in the submitted work. The work presents information that is irrelevant and unconnected to the task; no evident
awareness of appropriate principles, theories, evidence or techniques
1-4%

13

Sensitivity: Internal
NS Non-submission: No work has been submitted

Z Academic offence notation Applies to proven instances of academic offence

Word Count Note: Word counts are exclusive of spaces and content contained within tables and figures, including screenshots of the front-end interface. Please adhere to the word
count guidelines specified for each section. The inclusion of a References section is optional, and any references provided will not be counted towards the word
count.

Assessment Criteria for assignment 2: Demonstration of key design and implementation (40%)

In Part 2, the video demonstration's first segment, lasting 4-5 minutes, will be assessed in alignment with the database analysis and design explanation
submitted in Part 1 (Refer to Key Guidelines - Part 2). The evaluation will be based on the overall criteria outlined in Part 1's 'II: Analysis and Design' rubric to
verify the student's grasp of the core concepts of database analysis and design, accounting for 50% of the total evaluation.

For the second segment of the video presentation (5-6 minutes), the assessment will focus on the demonstration of the running system's adherence to the
key Functional Requirements as detailed in the guidelines (Refer to Key Guidelines - Part 2). This evaluation will consider both the overall criteria in Part 1's
'III: Implementation' rubric and the effectiveness of the student's implementation as elucidated in the video demonstration. This segment also contributes
50% to the total evaluation. The video should combine both segments into a single clip lasting no more than 10 minutes.

14

Sensitivity: Internal