Database Design and Development

Higher Nationals

Internal verification of assessment decisions – BTEC (RQF)

INTERNAL VERIFICATION – ASSESSMENT DECISIONS

Programme title BTEC HND in Computing
Mr. Gihan Chanaka
Assessor Internal
Verifier
Unit 04: Database Design & Development
Unit(s)
Database Solution for Quiet Attic Films
Assignment title
Janani Chathurya / E194180
Student’s name
List which assessment Pass Merit Distinction
criteria the Assessor has
awarded.
INTERNAL VERIFIER CHECKLIST
Do the assessment criteria awarded
match those shown in the
assignment brief? Y/N
Is the Pass/Merit/Distinction grade
awarded justified by the assessor’s Y/N
comments on the student work?
Has the work been assessed accurately? Y/N

Is the feedback to the student:
Give details:
• Constructive?
• Linked to relevant assessment criteria? Y/N
• Identifying opportunities for improved Y/N
performance? Y/N
• Agreeing actions?
Y/N
Does the assessment decision need
amending? Y/N
Assessor signature Date
Internal Verifier signature Date
1 Janani Chathurya Database design & Development

Programme Leader signature (if
required) Date
Confirm action completed
Remedial action
taken
Give details:
Assessor signature Date

Internal
Verifier Date
signature
Programme Leader
signature (if Date
required)

Higher Nationals - Summative Assignment Feedback Form
Student Name/ID Janani Chathurya / E198140
Unit Title Unit 04: Database Design & Development
Assignment 1 Assessor
Number
18/10/2023 Date
Submission Date Received 1st
submission
Date Received 2nd
Re-submission Date submission
Assessor Feedback:
LO1 Use an appropriate design tool to design a relational database system for a
substantial problem
Pass, Merit & P1 M1 D1
Distinction Descripts
LO2 Develop a fully functional relational database system, based on an existing system
design
Pass, Merit & P2 P3 M2 M3 D2
LO3 Test the system against user and system requirements.

LO4 Produce technical and user documentation.

Grade: Assessor Signature: Date:

Resubmission Feedback:
Grade: Assessor Signature: Date:
Internal Verifier’s Comments:
Signature & Date:

* Please note that grade decisions are provisional. They are only confirmed once internal
and external moderation has taken place and grades decisions have been agreed at the
assessment board.
Assignment Feedback
Formative Feedback: Assessor to Student
Action Plan
Summative feedback

Feedback: Student to Assessor
Assessor Date
signature
[email protected] 18/10/2023
Student
signature
Pearson Higher Nationals in

Computing
Unit 04: Database Design & Development Assignment

01

General Guidelines
1. A Cover page or title page – You should always attach a title page to your
assignment. Use previous page as your cover sheet and make sure all the details are
accurately filled.
2. Attach this brief as the first section of your assignment.
3. All the assignments should be prepared using a word processing software.
4. All the assignments should be printed on A4 sized papers. Use single side printing.
5. Allow 1” for top, bottom , right margins and 1.25” for the left margin of each page.
Word Processing Rules
1. The font size should be 12 point, and should be in the style of Time New Roman.
2. Use 1.5 line spacing. Left justify all paragraphs.
3. Ensure that all the headings are consistent in terms of the font size and font style.
4. Use footer function in the word processor to insert Your Name, Subject,
Assignment No, and Page Number on each page. This is useful if individual sheets
become detached for any reason.
5. Use word processing application spell check and grammar check function to help
editing your assignment.
Important Points:
1. It is strictly prohibited to use textboxes to add texts in the assignments, except for
the compulsory information. eg: Figures, tables of comparison etc. Adding text boxes
in the body except for the before mentioned compulsory information will result in
rejection of your work.
2. Carefully check the hand in date and the instructions given in the assignment. Late
submissions will not be accepted.
3. Ensure that you give yourself enough time to complete the assignment by the due
date.
4. Excuses of any nature will not be accepted for failure to hand in the work on time.
5. You must take responsibility for managing your own time effectively.
6. If you are unable to hand in your assignment on time and have valid reasons such as
illness, you may apply (in writing) for an extension.
7. Failure to achieve at least PASS criteria will result in a REFERRAL grade .

8. Non-submission of work without valid reasons will lead to an automatic RE FERRAL.
You will then be asked to complete an alternative assignment.
9. If you use other people’s work or ideas in your assignment, reference them properly
using HARVARD referencing system to avoid plagiarism. You have to provide both
intext citation and a reference list.
10. If you are proven to be guilty of plagiarism or any academic misconduct, your grade
could be reduced to A REFERRAL or at worst you could be expelled from the course

Student Declaration
I hereby, declare that I know what plagiarism entails, namely to use another’s work and to
present it as my own without attributing the sources in the correct form. I further
understand what it means to copy another’s work.
1. I know that plagiarism is a punishable offence because it constitutes theft.

2. I understand the plagiarism and copying policy of Edexcel UK.
3. I know what the consequences will be if I plagiarise or copy another’s work in any of
the assignments for this program.
4. I declare therefore that all work presented by me for every aspect of my program,
will be my own, and where I have made use of another’s work, I will attribute the
source in the correct way.
5. I acknowledge that the attachment of this document signed or not, constitutes a

binding agreement between myself and Pearson, UK.
6. I understand that my assignment will not be considered as submitted if this

document is not attached to the assignment.
[email protected] 18/10/2023
Student’s Signature: Date:
(Provide E-mail ID) (Provide Submission Date) Higher
National Diploma in Computing
Assignment Brief
Student Name /ID Number Janani Chathurya / E194180
Unit Number and Title Unit 4: Database Design & Development
Academic Year 2022/23
Unit Tutor Mr. Gihan Chanaka
Assignment Title Data base system for Quiet Attic Films
Issue Date
Submission Date

IV Name & Date
Submission format
Part 1: The submission should be in the form of an individual written report written in a
concise, formal business style using single spacing and font size 12. You are required to make
use of headings, paragraphs, and subsections as appropriate, and all work must be supported
with research and referenced using Harvard referencing system. Please also provide in-text
citation and bibliography using Harvard referencing system. The recommended word limit is
3,000– 3,500 words, although you will not be penalised for exceeding the total word limit.
Part 2: The submission should be in the form of a fully functional relational database system
demonstrated to the Tutor; and an individual written report (please see details in Part 1
above). Part 3: The submission should be in the form of a witness statement of the testing
completed by the Tutor; technical documentation; and a written report (please see details in
Part 1 above).
Unit Learning Outcomes:
LO1 Use an appropriate design tool to design a relational database system for a substantial
problem.
LO2 Develop a fully functional relational database system, based on an existing system
design.
LO3 Test the system against user and system requirements.
LO4 Produce technical and user documentation.
Assignment Brief and Guidance:

Assignment brief
Quiet Attic Films is a film production company based in London, England who specialize in
making short information films and advertisements for television. They want you to design
and implement a database that meets the requirements for their data. These requirements
are specified in this scenario and the examples of paper documents kept by the company
shown below.
Quiet Attic Films organize their data around the concept of a ‘production’. A production is
specified as being for a particular client; but note that a client might have more than one
production at any time. A production will take place at one or more locations. A production
will also use a number of what are called, properties, which might be anything from an
actual property like a building, to costumes or small items of any sort. It is important to
keep a record of which properties are required at which location.
There should also be a record kept of the staff types that are assigned to productions

Activity 1
Identify the usersystem
and requirements to design a database for the above
and scenario
design a relational database systemconceptual
using design (ER Model)
by including
identifiers (primary Key) of entities and cardinalities, participations
Convert
of relationships.
the ER Model into logical database design using relational database model including primary
keys foreign keys and referential Integrities.
It should contain ast
at lefive interrelated .tables
Check whether the provided logical design
is normalised. If not, normalize the database by removing the anomalies.
(Note-It is allowed to have your own assumptions and related attributes within the scope
:
o the case study given
)
f
Design set of simple interfaces to input and output for the above scenario using Wireframe
or any interface
-designing tool.
Evaluate the effectiveness
theof
givendesign (ERD and
Logical design)
in terms of the
identified user and system
requirements.

Activity 2
Develop a relational database system according to the ER diagram you have created (Use
SQL DDL statements). Provide evidence of the use of a suitable IDE to create a simple
interface to insert, update and delete data in the database. Implement proper security
mechanisms in the developed database and evaluate the database solution developed in
terms of its effectiveness with relevance to the user and system requirements identified,
system security mechanisms (EX: -User groups, access permissions) and the maintenance
of the database. Suggest improvements for any identified problems.
Assess the usage of the below SQL statements with the examples from the developed
database to prove that the data extracted through them are meaningful and relevant to
the given scenario.
Select/ Where / Update / Between / In / Group by / Order by / Having
Activity 3
3.1 Provide a suitable test plan to test the system against user and system requirements.
provide relevant test cases for the database you have implemented. Assess how the
selected test data can be used to improve the effectiveness of testing.
Note:- Learner needs to give expected results in a tabular format and screenshots of the
actual results with the conclusion
3.2 Get independent feedback on your database solution from the non-technical users and
some developers (use surveys, questioners, interviews, or any other feedback collecting
method) and make a separate conclusion from the feedback.

Activity 4
Produce technical and user documentation for a fully functional system, including data
flow diagrams showing movement of data through the system, and flowcharts describing
how the system works. Evaluate the developed database by suggesting future
enhancements to ensure the effectiveness of the system.

Grading Criteria Achieved Feedback
LO1 Use an appropriate design tool to design a relational

database system for a substantial problem.
P1 Design a relational database system using appropriate

design tools and techniques, containing at least four
interrelated tables, with clear statements of user and
system requirements.
M1 Produce a comprehensive design for a fully functional

system, which includes interface and output designs, data
validations and data normalization.
D1 Evaluate the effectiveness of the design in relation to

user and system requirements.
16
Janani Chathurya Database design & Development
LO2 Develop a fully functional relational database system,
based on an existing system design
P2 Develop the database system with evidence of user

interface, output and data validations, and querying across
multiple tables.
P3 Implement a query language into the relational database

system
.
M2 Implement a fully functional database system, which
includes system security and database maintenance.
M3 Assess whether meaningful data has been extracted

through the use of query tools to produce appropriate
management information
LO3 Test the systems against user and system requirements
P4 Test the system against user and system requirements.
17
M4 Assess the effectiveness of the testing, including an
explanation of the choice of test data used.
D2 Evaluate the effectiveness of the database solution in

relation to user and system requirements and suggest
improvements.
LO4 Produce technical and user documentation
P5 Produce technical and user documentation.
M5 Produce technical and user documentation for a fully

functional system, including data flow diagrams and
flowcharts, describing how the system works.
D3 Evaluate the database in terms of improvements needed

to ensure the continued effectiveness of the system.
18
Acknowledgement
First of all, I would like to thank the lecturer Gihan sir who taught the subject well. I am
further thankful for teaching well so that I can understand the subject throughout the
teaching period. The subject matter helped a lot to complete the assignment. I am very
grateful for the educational guidance and experience provided by Esoft campus. I take
this opportunity to express my heartfelt thanks to my parents and friends including the
lecturer who helped me to complete the assignment.
Thanks to all who supported and guided me to complete the assignment on time.
Janani Chathurya
19
Table of Contents
Requirement.................................................................................................................. 22
Types of requirements...................................................................................................22
Entity Relationship (ER) Diagram.................................................................................34
Degree of relationship................................................................................................... 40
Concept of Keys............................................................................................................ 41
Database Mapping.........................................................................................................51
Normalization................................................................................................................53
Important Points Regarding Normal Forms in DBMS...................................................54
Anomaly........................................................................................................................ 55
System Interfaces...........................................................................................................68
Satisfying the System’s Design Interfaces User Requirements......................................74
Structure Query Language.............................................................................................76
Data Definition Language..............................................................................................77
Creating the database using DDL..................................................................................79
Database Query............................................................................................................. 80
Security mechanisms.....................................................................................................81
Develop database Fulfil the Requirements of User and System.....................................82
Improvement that can be made in the System................................................................84
Data Manipulation Language.........................................................................................86
Testing........................................................................................................................... 98
Feedback Form............................................................................................................104

Flow Chart...................................................................................................................122
Use Case Diagram....................................................................................................... 130
Data Flow Diagram (DFD)..........................................................................................131
Context Level DFD.................................................................................................... 133
Level 0 DFD........................................................................................................................133
Future improvement for Database System...........................................................................135
References...................................................................................................................137
Figure 1:Requirement ..............................................................................................................

22
Figure 2: Normalization Flow ..................................................................................................
53
Figure 3 : Login Form Interface Design ..................................................................................
68
Figure 4: Main Form Interface Design ....................................................................................
69
Figure 5: Client Registration Interface Design ........................................................................
70
Figure 6:Production Form Interface Design ............................................................................
71
Figure 7 : Report Form Interface Design .................................................................................
72
Figure 8: Assignment Form Interface Design ..........................................................................
73
Figure 9 : Payment Form Interface Design ..............................................................................
74
Figure 10 : Types Of SQL ........................................................................................................
76
Figure 11: Database View ........................................................................................................
79
Figure 12 : Create Database .....................................................................................................
79
Figure 13 : Create all Tables ....................................................................................................
79
Figure 14: Select Query 1 ........................................................................................................
87
Figure 15: Select Query 2 ........................................................................................................
87
Figure 16 : Select Query 3 .......................................................................................................
88
Figure 17 : Where Query 1 ......................................................................................................
88
Figure 18:Where Query 2 ........................................................................................................
89
Figure 19: Where Query 3 .......................................................................................................
89
Figure 20 : Update Query 1 ......................................................................................................
90
Figure 21: Update Query 2 .......................................................................................................
90
Figure 22: Update Query 3 .......................................................................................................
90
Figure 23: Between Query 1 ....................................................................................................
91
91
92
Figure 26 : In Query 1 ..............................................................................................................
92
Figure 27:In Query 2 ................................................................................................................
93
Figure 28:In Query 3 ................................................................................................................
93
Figure 29: Group Query 1 ........................................................................................................
94
Figure 30: Group Query 2 ........................................................................................................
94
Figure 31:Group Query 3 .........................................................................................................
94
Figure 32: Order by Query 1 ....................................................................................................
95
95
96 Figure 35: Having Query
1 ...................................................................................................... 96
Figure 36: Having Query 2 ......................................................................................................
97

Figure 37: Having Query 3 ......................................................................................................
97
Figure 38 : Feedback form1 ...................................................................................................
106
Figure 39: Feedback form 2 ...................................................................................................
106
Figure 40: Feedback form 3 ...................................................................................................
107
Figure 41 : Feedback form 4 ..................................................................................................
107
Figure 42; Feedback form 5 ...................................................................................................
108
Figure 43 : Usecase Diagram .................................................................................................
131
Figure 44: DFD Symbols .......................................................................................................
132

Requirement
A software project's requirements are the functions, features, and limitations that must
be satisfied by the final result. In other words, the requirements specify what the
program should perform, how it should appear, and what conditions must be satisfied in
order for it to be regarded successful.
It is critical to collect requirements in order to produce a product that fits the

expectations of the consumer or client. It is vital to remember that requirements might
change during the life of a project, thus a method for tracking and managing these
changes must be in place.
Types of requirements
Functional requirement
Requirement User Requirement

Non functional requirement
Hardware requirement
System Requirement
Software requirement

1. User requirements
User requirements are a blend of functional and non-functional criteria. These user
criteria must be constructed in such a way that people with no technical understanding
may readily grasp them. As a result, they must be written in simple tables, forms, and
diagrams in normal language. Also, ensure that the paper contains no information about
system architecture, software, or formal notations.
1.1 Functional Requirements
These are the specific requirements that the end user demands as basic features that the
system should provide. All of these functionalities must be included in the system as
part of the contract. These requirements are expressed as input to be given to the system,
the operation to be performed, and the expected output. They are the user's stated
requirements that can be directly observed in the final product, unlike non-functional
requirements.
1.2 Nonfunctional Requirement
These quality constraints are essential for the system to meet the requirements stated in
the project contract. The level of importance or implementation of these factors may
differ from one project to another. They are also referred to as non-functional
requirements. (www.geeksforgeeks.org,2023)
User requirements for designing and implementing a database for Quiet Attic Films.
• Users should be able to create and manage different movie productions and
reports. It is important to include details such as the ability to connect multiple
products to a single client.

• Users should be able to specify one or more locations for each product, including
the name, address, and other relevant details for each location.
• The properties utilized in each production must be tracked by users. These assets
can be a vast range of things, such as structures, costumes, equipment, or other
things. Users should be able to record information about certain characteristics,
including their name, description, and amount, and correlate those properties with
a production.
• Assignment of Properties to Locations: It's crucial to keep track of which

properties are needed where for each production. Properties ought to be able to be
connected to certain production sites by users.
• Users must keep a list of the different staff kinds that can be used for productions.
Roles like director, producer, cameraman, runner etc., are examples of staff types.
• Users should be able to designate staff members' responsibilities or staff

categories and allocate them to particular productions.
Functional Requirements for designing and implementing the database system for
Quiet Attic Films.
• Users must be able to view, edit, and delete server records. Clients can have
multiple products so the system must be able to connect multiple products to a
single client.
• Under Product Management, users should be able to create and manage product
records, including specifying the product title, start and end dates, and a
description.
Also, each product should be associated with a specific client.
• The system supports associating multiple locations with one product as production
can occur at one or more locations. The system allows users to view, edit and
delete location records.

• Users should be able to add, edit and delete property records. Must be able to
specify types of details such as buildings, clothing, equipment, and sizes. The
systems should allow finding the required attributes for each product and location.
• The system should provide reporting capabilities and allow users to generate such
reports, for example a list of products for a specific client, a list of required
attributes for a product and its locations, a list of staff assigned to a product by
role, etc.
• The system must implement user authentication to ensure that only authorized
users can access and modify the database.
• The system should provide users with a search feature to easily find specific
products, clients, locations, properties, and staff members.
The Non-functional requirements for designing and implementing the database

system for Quite Attic Films.
• Security - Encrypt access control and user authentication and data protection to
protect sensitive information in the system.
• Performance - It is imperative that the system is responsive and functional despite

the large amount of data.
• It is essential to have credibility in the system.
• In order to prevent data corruption, containment rules must maintain continuity

through referential integrity and error handling.
• The user interface should be user friendly and intuitive and should be easy for
users to use.

• Detailed documentation should be provided for system usage maintenance and
troubleshooting.
• The system should be easily accessible to users following accessibility standards
• While setting up the system, the font size, font colour, background image,
background colour used for the interface should be used appropriately.
2. System requirements
System requirements are a more detailed form of user needs. System requirements serve
as the starting point for every new system design. These specifications provide a
thorough explanation of the user requirements that the system must meet.
System Requirements for designing and implementing a database for Quiet Attic
Films.
Create tables for Productions, Clients, Locations, Properties, Staff Types, and Staff
Assignment in a database structure. Establish the proper connections between these
tables. Implement a database for precise information archiving and retrieval. By
specifying data types, restrictions, and validation guidelines, you may ensure data
integrity.
Create an intuitive user interface for managing and entering data. Records for
productions, locations, properties, customers, staff types, and staff assignments should be
able to be added, edited, and deleted by users. Establish a system to link properties to
particular production sites. This might involve a database direct relationship or a
connecting table. Implement search and reporting functionalities to allow users to find
specific productions, view property-location assignments, and track staff assignments.
To guarantee that only authorized users may access and edit the database, implement user
authentication and permission. User permissions and roles might be involved
here.Implement data validation procedures to stop inconsistent or inaccurate data entry.
To avoid data loss, establish regular data backup and recovery methods. Provide thorough
documentation for the database structure, user guides, and upkeep processes. Provide

instruction on how to use the database to the Quiet Attic Films personnel. Assist with any
continuing issues, changes, or improvements that the database may require.
2.1 Hardware Requirements
The physical computer resources, usually referred to as hardware, are the most typical
set of specifications defined by any operating system or software program. A hardware
compatibility list (HCL) is frequently included with a list of the necessary hardware,
especially when operating systems are involved. For a certain operating system or
application, an HCL describes hardware components that have been evaluated, are
compatible, and occasionally are not. The many facets of hardware requirements are
covered in the following subsections.
Every computer operating system is created for a certain computer architecture. The
majority of software programs have specific operating system and architectural
requirements. Although there are operating systems and programs that are independent of
architecture, the majority must be recompiled to work on a different architecture.
Processing power - The power of the central processing unit (CPU) is a fundamental
system requirement for any software. Most software running on x86 architecture define
processing power as the model and the clock speed of the CPU. Many other features of a
CPU that influence its speed and power, like bus speed, cache, and MIPS are often
ignored.
Memory - When software is launched, it is stored in a computer's random-access memory

(RAM). Memory needs are determined after considering the application's, operating
systems, supporting software and files, and other processes that are executing. When
determining this criteria, optimal performance of other unrelated applications operating
on a multi-tasking computer system is also considered.
Secondary storage - Hard-disk needs vary according to the size of the program
installation, the number of temporary files produced and kept when installing or operating
the software, and the possibility of using swap space (if RAM is inadequate).

Display adapter - High-end display adapters are frequently defined in system
requirements for software that requires a better-than-average computer graphics display,
such as graphics editors and high-end games.
Peripherals - Some software programs need significant and/or particular usage of

peripherals, necessitating increased performance or functionality from such peripherals.
CD-ROM drives, keyboards, pointing devices, network devices, and so on are examples
of peripherals.
(Anon, www.vskills.in ,2023 )
2.2 Software Requirements
Software requirements are concerned with establishing the software resource needs and
prerequisites that must be installed on a computer in order for a program to work
properly. These prerequisites or needs are typically not included in the program
installation package and must be installed separately before the product can be installed.
Platform - A platform in computing is a type of framework, either in hardware or

software, which allows software to execute. Platforms are often comprised of a
computer's architecture, operating system, or programming languages and their runtime
libraries.
When designing system requirements (software), one of the first criteria specified is the
operating system. Although some degree of backward compatibility is frequently
maintained, software may not be compatible with multiple versions of the same series of
operating systems. For example, most software designed for Microsoft Windows XP does
not run on Microsoft Windows 98, although the converse is not always true. Similarly,
software designed using newer features of Linux Kernel v2.6 generally does not run or
compile properly (or at all) on Linux distributions using Kernel v2.2 or v2.4.
Software that extensively uses specialized hardware, such as high-end display adapters,
requires specialized APIs or more recent device drivers. A notable illustration is DirectX,

a set of APIs for managing multimedia-related activities, particularly game development,
on Microsoft platforms.
Web browser - The system's built-in default browser is used by the majority of web
applications and software that extensively relies on Internet technology. Despite the flaws
in ActiveX controls, Microsoft Internet Explorer is a popular piece of software that runs
on the operating system known as Windows.
(Anon, www.vskills.in ,2023 )
We must address both hardware and software requirements while designing and
implementing a database for Quiet Attic Films.
User Hardware Requirements:
• Computer: In order to access the database and interact with it, users will require
a computer or laptop. These devices must adhere to the minimal hardware
requirements specified by the makers of the operating system and database
software.
• Operating system: The user's choices might influence the operating system that
is used, although the database software should be compatible with well-known
ones like Windows, macOS, or Linux.
• Memory (RAM): The size and complexity of the database will determine how
much RAM is needed. A modest to medium-sized database should work with
4GB to 8GB of RAM. But bigger databases could need more RAM.
• Storage: To store the database and related files, there must be enough storage
capacity. The required storage capacity will depend on the volume of data and
media files (e.g., videos, images) that the company handles. A minimum of
256GB to 512GB of storage is recommended.

• Network Connection: If the database is housed on a cloud server or calls for
online access for collaboration, users should have a dependable internet
connection.
User Software Requirements
Web browser: In order to access and interact with the database using a web-based
interface, clients will require a contemporary web browser (such as Google Chrome,
Mozilla Firefox, or Apple Safari). The database application's features and technologies
must be supported by the web browser.
Credentials for Authentication: To access the database, clients must have legitimate
login information (a username and password) issued by Quiet Attic Films. These login
credentials provide safe database access and safeguard private data.
The user must have the SQL software to install and open the database.
Communication Tools: Clients may need email or messaging software to communicate

with Quiet Attic Films or database administrators for queries, requests, or support.
Documentation: Quiet Attic Films should give customers easy-to-follow instructions or
manuals on how to utilize the database efficiently. It should be simple to view this
documentation online or using the database interface.
Support: To request help or report any problems they run into while using the database,
clients should have access to a support system, such as an email address or a helpdesk.
Data Input and Interaction: Clients may require basic data input tools like text editors or
form-filling functionality within the web browser if they need to input data or interact
with the database other than viewing information (such as submitting requests or
comments).

Security Awareness: Clients should be aware of fundamental security practices, such as
keeping their login credentials private and logging out of the database when not in use,
even when there is no special software or hardware need.
Since clients are largely end users that access the database through a web interface, their
software and hardware requirements are often simple.
The effective development and functioning of the database depend on the hardware and
software requirements being tailored to Quiet Attic Films' particular requirements. In
order to guarantee the best performance and security, it is also essential to routinely
update and maintain both the hardware and software components.
System developers who will work on establishing and maintaining the database will need
certain hardware and software requirements to design and construct a database for Quiet
Attic Films, including the needs for data organization and personnel assignment.
System Developer Hardware Requirements:
Computer Requirements:
• CPU: A multi-core CPU with at least 2.0 GHz or greater clock speed is suggested.
This allows developers to work more effectively, which is especially important
when working with enormous datasets.
• Memory (RAM): A minimum of 8GB of RAM is recommended for database

development. When working with really big databases or conducting complicated
queries, however, 16GB or more may be required.
• Storage: A fast and large SSD (Solid State Drive) is strongly recommended. SSDs
enable quicker read/write rates, which can enhance database development and
testing performance dramatically. A 256GB SSD or greater is recommend.
• Monitors: Having additional screen space on which to examine and change

database schemas, write code, and check database performance, a big display or

numerous monitors can increase productivity. The standard arrangement for
developers is two monitors.
• Backups, project files, and data sets should all be stored on secondary storage,
according to developers. This can be accomplished by using network-attached
storage (NAS) or external hard drives.
• High-Speed Internet Connection: For effective remote work (if applicable), team
collaboration, and access to online resources, a high-speed internet connection is
essential.
System Developer Software Requirements:
Database Management System (DBMS):

• The selection of a DBMS is based on the needs of the project and the preferences
of the company. Several more factors include:
• Make that the chosen DBMS licenses are in line with the organization's spending
limits and licensing requirements.
• Check the compatibility of the selected DBMS with the operating system and
development tools.
Tools for Database Design:

• Visual studio: the developer needs the visual studio software to build the system.
• A complete tool for creating and maintaining MySQL databases is MySQL
Workbench.
• Working with Microsoft SQL Server databases is made easy using Microsoft SQL
Server Management Studio.
• Oracle SQL Developer: Created specifically for the building of Oracle databases.
Programming Languages
• For constructing, querying, and managing the database, SQL proficiency is
necessary.
• Additional Languages: To create web apps or interfaces that communicate with
the database, developers may require knowledge of additional programming
languages like Java, C# depending on the application layer.

Development environment integrated (IDE):
• A flexible code editor with a wide range of features for many programming
languages is Visual Studio Code.
Software for version control:

• To keep track of changes, work with team members, and ensure code integrity,
developers should utilize platforms like GitHub and GitLab as well as version
control systems like Git.
Documentation Tools:
• Effective documentation is crucial. Developers can use Markdown or LaTeX to
create technical documentation, including data dictionaries, schema diagrams, and
development guidelines. Collaboration and communication technologies are
helpful for streamlining team coordination, communication, and task tracking.
Tools for Security and Testing:

• Vulnerability Scanners: Database security may be assessed using tools such as
OWASP ZAP or Nessus.
• Testing frameworks and technologies for automated database testing and quality
assurance should be implemented.
• Depending on the project's needs, developers may utilize virtualization tools like
VirtualBox or containerization platforms like Docker to create and maintain
development environments that match production conditions.
Backup and Recovery Tools:

• Implement backup and recovery solutions designed particularly for database
development environments to avoid data loss during the testing and development
phases.
Project Management Tools:

• Depending on the workflow of the team and the complexity of the project,
employing project management tools aids in the effective monitoring of tasks,
milestones, and project progress.
Giving system developers the appropriate hardware and software resources guarantees
that they can design, create, and manage the database efficiently. Furthermore,
prioritizing security standards and good documentation contributes to the database's
integrity and security throughout its lifespan.
Entity Relationship (ER) Diagram
The Entity Relationship Diagram (ERD), also known as the ER Diagram or the ER
model, is a sort of structural diagram used in database architecture. An ERD comprises
several symbols and connections that depict two crucial pieces of information: the
primary entities inside the system scope and their interrelationships.
There are also additional situations where ER diagrams may be useful, even though they
were primarily created for developing relational databases in terms of concept
visualization and actual database architecture. Some common use cases are listed below.
• Database design – Changing a database structure directly in a DBMS might be

dangerous, depending on the scope of the modification. Planning out the
modifications thoroughly is essential to prevent damaging the data in a production
database. ERD is a useful tool. You have the opportunity to spot errors and design
faults before implementing the changes in the database by creating ER diagrams
to represent database design concepts.
• Debugging database problems may be difficult, especially if the database has

several tables and you have to write sophisticated SQL statements to retrieve the
information you need. You can see the whole database schema when you visualize
a database schema using an ERD. It is simple to locate entities, evaluate their
characteristics, and determine the connections they have with other entities. All of
these make it easy to assess an existing database and identify database issues.

• Database creation and patching - Visual Paradigm, an ERD tool, supports a
database generation tool that can automate the database creation and patching
process by means of ER diagrams. So, with this ER Diagram tool, your ER design
is no longer just a static diagram but a mirror that reflects truly the physical
database structure.
• Aid in requirements gathering: Create a conceptual ERD that shows the high-level
business objects of the information system and use it to determine the needs of the
system. Such a basic model may also develop into a physical database model,
which helps with the construction of relational databases, the development of
process maps, and the establishment of data flow modes.
(Anon, www.visual-paradigm.com ,2023)
The components and features of an ER diagram
Entities, relationships, and attributes make up ER diagrams.
1. Entities
A defined thing that can have data saved about it, such as a person, object, idea, or
event. Consider entities to be nouns. A consumer, a pupil, a car, or a product are some
examples.
Typically shown as a rectangle.
For example, in a student study course, both the student and the course are entities.
Student Study Course
Entity
Entity is characterised into two types as follows

• Strong entity
• Weak entity

Strong Entity
A strong entity set is made up of entity types that have key characteristics or enough of
them to establish a main key attribute. A single rectangle is used to symbolize it.
Weak Entity
Each entity in the entity set is uniquely identified by an Entity type's key property.
However, there are some entity types for which key properties cannot be specified.
These are known as Weak Entity kinds.
In the example below, school is a strong entity because it has a primary key attribute -
school number. Unlike school, the classroom is a weak entity because it does not have
any primary key and the room number here acts only as a discriminator.
Room no School no
Relation
Classroom
School
A Double Rectangle is used to represent a weak entity type. Weak entity type
participation is always full. A double diamond is used to symbolize the identifying
connection, which exists between the weak entity type and its identifying strong entity
type.
2. Attributes
The characteristics of an entity type are defined by its attributes. For example, the
characteristics that constitute the entity type Student are Name, Student ID,DOB, Age,
Address, and Con No. The attribute is represented by an ellipse in the ER diagram.
Attribute

Key Attribute
The attribute which uniquely identifies each entity in the entity set is called the key
attribute. It underlines the text of a key attribute.
For example: For a student entity, the Student ID can uniquely identify a student from a
set of students.
Address Student ID
Student
Age Name
Composite Attribute
An attribute that is composed of several other attributes is known as a composite attribute.

An oval showcases the composite attribute, and the composite attribute oval is further
connected with other ovals.
Name
First name Last name

Middle name
Multivalued Attribute
Some attributes can possess over one value, those attributes are called multivalued
attributes. The double oval shape is used to represent a multivalued attribute

Name Age
Student
Qualification Address
Derived Attribute
An attribute that can be derived from other attributes of the entity is known as a derived
attribute. In the ER diagram, the dashed oval represents the derived attribute.
Name Address
Student
DOB Age
3. Relationships
The diamond shape showcases a relationship in the ER diagram. It depicts the

relationship between two entities.
In the example below, both the student and the course are entities, and study is the
relationship between them.
Student Study Course
Relationship
Types of Relationship

One to One relationship
When a single element of an entity is associated with a single element of another entity,
it is called a one-to-one relationship.
For example, a student has only one identification card and an identification card is
given to one person.
1 1
Student Has
Student ID
One to Many relationship
When a single element of an entity is associated with more than one element of another
entity, it is called a one-to-many relationship
For example, a customer can place many orders, but an order cannot be placed by many
customers.
1 M
Customer Has
Order
When more than one element of an entity is related to a single element of another entity,
then it is called a many-to-one relationship.
For example, students have to opt for a single course, but a course can have many
students.
Student M 1
Enroll Course
Many-to-Many Relationship

When more than one element of an entity is associated with more than one element of
another entity, this is called a many-to-many relationship.
For example, you can assign an employee to many projects and a project can have many
employees.
M M
Employee assign Project
(Ravikiran A S, www.simplilearn.com,2023)
Degree of relationship
Unary Relationship
Both associated entity types are the same in this kind of interaction. As a result, when
both entity types are the same, we may state that unary connections exist and that their
degree of relationship is 1. Or, to put it another way, when just one entity set participates
in a connection, the relationship is referred to as a unary relationship.
Example: In one class, there are numerous students as well as monitors. In this situation,
class monitors are also students. We can thus conclude that there are no adults present.
Therefore, such a connection has a degree of 1.
Binary Relationship
There are two kinds of entity associates in a Binary connection. So, when there are two
sorts of entities and their degree of relationship is 2, we may claim that a Binary
relationship exists. In other words, when two entity sets participate in a connection, it is
referred to be a binary relationship. This is the most common connection, and it is
simple to turn into a relational table.
We have the entity types "Student" and "ID," and each "Student" has a unique "ID." As
a result, we may conclude that this association between two entity types is binary. One
"father" can have several "daughters," but each "daughter" should only belong to one
"father." It is a one-to-many binary connection, as we might say.

Ternary Relationship
There are three different sorts of entity associates in the ternary relationship. Therefore,
when there are three different entity types and their degree of relationship is 3, we may
claim that a ternary relationship exists. This results in a rise in the number of entities,
making the process of converting E-R into a relational table exceedingly difficult. Let us
use examples to better understand now.
As an illustration, consider the three entity types "Teacher," "Course," and "Class." The
fact that the teacher instructs a certain course and teaches a specific class is what
constitutes the link between these two things. As a result, we may describe this
association of three entity kinds as a ternary connection.
N-ary Relationship
There are n different sorts of entities that associate in a N-ary connection. Therefore,
when there are n kinds of things, we may claim that a N-ary connection exists. One
drawback of the N-ary relationship is that it is highly challenging to convert into an
entity, rational table, due to the large number of entities. In contrast to binary, which is
quite prevalent, this is exceedingly unusual.
Using the five entities Teacher, Class, Location, Salary, and Course as an example. In
this case, five entity types are interacting, which is equivalent to five n-ary relationships.
These are the most frequent relationship degrees in ERDs, however if your data model
calls for higher degrees (quaternary, quinary, etc.), you can have them as well. The
complexity of the model and the queries that operate on it, however, can also rise as the
degree of association increases, therefore it's crucial to carefully analyze the layout of
your ERD based on your unique requirements.( Anon,www.geeksforgeeks.org, 2023)
Concept of Keys

A key is a data item that is used to identify a record or a value that is used to identify a
record in a database. It aids in distinguishing one entity from a group of entities. Key
identifies a set of properties that are adequate to differentiate entities from one another.
The different types of keys
• Primary key
• Super key
• Candidate key
• Alternate key
• Artificial key
• Foreign key
Primary Key
In DBMS, this sort of key refers to a column that uniquely identifies all of the records in
that table. A table contains a single primary key that cannot include repeated or
duplicated data across its rows. Each value in the main key must be distinct, with no
repeats permitted.
When the PK (PRIMARY KEY) constraint is applied to a column or collection of

columns, it assures that no null values or duplicates exist. Furthermore, any foreign key
that references the main key is unable to edit the primary key's values.
The Characteristics of a Primary Key

• The Primary Key field should never be left blank; the Primary Key column must
always have a value.
• No two rows in the table may have the same value in that column.
• If a foreign key in a DBMS relates to the primary key, no value in this primary
key column may be updated or modified.

The Importance of the Primary Key In DBMS,
The main key is critical in a database management system (DBMS) for the following
reasons:
• It ensures that each table entry may be individually identified.
• By assuring uniqueness, the main key protects data integrity by preventing
redundant or duplicate records.

The DBMS often indexes primary keys, which speeds up data retrieval processes.
• The main key is crucial in establishing links between tables.
Examples of Primary Keys
In this example, the student ID is the primary key.

St ID First Last Email
Name Name Age
1 Falan Andria [email protected] 22
2 Anne Jesika [email protected] 19

3 Jacky Janson [email protected] 25
Principles for Selecting a Primary Key
1. The most important factor in DBMS key selection is uniqueness. It signifies that the value
of this column does not appear in any other table row.
2. The definition and values must not be changed. While changing the value of a PK column
requires updating all referenced rows in the child (related) table, changing the columns of a PK
requires redefining all relevant foreign keys.
3. If a composite primary key is utilized, no one column or subset of columns should be able
to uniquely identify each individual.
4. Use as few columns as possible, and if possible, use columns with values that are easy to
read and memorize.
Candidate Keys
Candidate keys are critical in maintaining the integrity and consistency of a database. The aim
of this key in DBMS is to ensure the uniqueness and independent identification of each row
inside a table. Furthermore, candidate keys enforce table associations, assuring data integrity
and overall database consistency.
Characteristics of a candidate key

•
• Its values must be separate.

• A candidate key in SQL can have a number of characteristics, but it must not include null
values and must have the minimal minimum of fields to ensure uniqueness.
• Make a unique identifier for each table record.
Examples of candidate keys

Roll no Name Name Age
1 10 Falan Andria [email protected] 22
2 11 Anne Jesika [email protected] 19

3 12 Jacky Janson [email protected] 25
The candidate keys Roll No., Stud ID, and Email in the table allow us to individually identify each
student record.
Candidate keys' importance in DBMS These DBMS keys are crucial for maintaining the
consistency and integrity of a database. They are used to enforce relationships between tables and
ensure that every row in a database is unique and recognisable on its own.
Super Key
A super key is the aggregate of all keys that enables us to identify each row in the database. This
kind of DBMS key states that all table columns that might uniquely identify the columns serve as
the super keys.
A super key's properties

• A super key must guarantee the uniqueness of each entry in a table.

However, a super key should be the bare minimum set of traits that may ensure
uniqueness.
• A super key may have many subsets that are also recognized as super keys.
Super keys' importance in DBMS

• The sole purpose of a super key is to identify certain table rows. By choosing any random
column, you might not always be able to determine which table a row belongs to since a
duplicated column will not be able to identify a unique row. With this key in DBMS, data
retrieval is made simpler, and this ambiguity is removed.
Foreign Key
A foreign key is used to connect two tables that are both accessible. For the foreign key to work,
every value in a column or collection must match the main key in the referential table. With the
help of a foreign key, we can maintain referential and data integrity.
A foreign key's properties

• It is a key that acts as both a secondary and main key in two separate tables.
• It mixes two or more relations at any one moment.
• They act as table cross-references.
The importance of foreign keys in DBMS

• Foreign keys relate data in one table to data in another. A foreign key column in one table
links to a column in another table with unique values to create a method of cross-
referencing two columns.
Alternate Key
A key in a database can be chosen as the primary key of a table in a variety of ways. An alternate
key is any key that has the potential to replace the major key but is not presently the primary key.
It is a potential major key that has not yet been chosen.
Alternate key properties

•
• All keys that are not the primary keys are referred to as alternate keys.
• It is a spare key.
• It has two or more fields that enable it to distinguish between two or more records.
• These requirements are reinforced.

Roll no Name Name
1 10 Falan Andria [email protected]

2 11 Anne Jesika [email protected]
3 12 Jacky Janson [email protected]
St ID, Roll No., and Email serves as the main keys. However, because St ID is the main key, Roll
No. and Email is now the secondary key.
The importance of alternative keys in DBMS

• Because an alternate key and a candidate key are identical, their purpose and function are
the same. This means that the columns in a table that may be used to identify each record in
the database independently are identified with an extra key.
Compound Key
This key in DBMS comprises two or more attributes that solely recognize a specific record. It is
possible that none of the columns in the database are unique in and of themselves. When combined
with the extra column or columns, however, the composite key combination becomes unique.
Using the database's compound key, each entry in the table must be uniquely recognized.
Compound key properties

• A compound key must ensure that each record in a database is unique.
A compound key should be the bare minimum collection of attributes required to ensure
uniqueness.

• It is critical to have a compound key attribute order.
Examples of compound keys
Order No Product ID Product name Quantity

B005 JAP1024 Mouse 5
B005 JHD1345 USB 10
B005 KDL0973 LCD Monitor 20
B002 FRG1204 USB 6
B004 JUW1287 Laser printer 2
This key in DBMS is illustrated below:

• Because Order No and ProductID do not uniquely identify a record, they cannot be
used as the primary key. To uniquely identify each entry, a compound key combining
the Order ID and Product ID might be used.
The importance of compound keys in DBMS

• Compound keys are always built from the main keys of two or more other tables.
Both keys uniquely identify data in their separate tables, but only the compound key
is necessary to do so in the table.
In a table format, here is a comparison of several types of keys in a relational database

management system (DBMS):
Key Null
Definition Uniqueness Purpose Index Usage Alteration
Type Values
Uniquely Data Creates May be
Primary identifies Not integrity, Unique Within complex,
each record Required impact other
Key Allowed record Index same table
in a table. identification. tables.
Data May More
Foreign Establishes a Not Between
Allowed create flexible for
Key relationship Required tables
consistency, Index maintenance.
Key Null
Definition Uniqueness Purpose Index Usage Alteration
Type Values

•
between relationship
tables. maintenance.
Alternative Backup
May
Candidate unique keys Not primary key Within May become
that could be Required Allowed create
Key same table primary key.
primary keys. Index
options.
Combination
of attributes Conceptual, May N/A
Conceptual
Super Key Required Allowed create
use
that uniquely not enforced. Index
identify.
Combined Specialized May be
Creates Within
Composite attributes Not unique complex,
Required Composite
Key used as a Allowed impact
Index same table
single key. identification. performance.
Ensures Enforce
column(s) uniqueness, Creates May be used
Unique have unique Within
Required Allowed Unique as primary
Key values. same table
no primary Index key.
key.
Artificial Enhanced
Creates Generally
Surrogate keys assigned Not data privacy, Within
Required Allowed data Unique static,
Key for record same table
identification. warehousing. Index nonchanging.
Keys and functional dependencies are critical components in the development of a

database. These concepts may also be used to distinguish between good and bad database
design. Normalization is the final stage in removing redundancies and increasing database
efficiency.
(Anon , https://herovired.com/ ,2023 )

Quiet Attic Films is a film production company based in London, specializing in
creating short information films and television advertisements. They have a need for a
database system that will help them manage their production-related data effectively. A
relational database management system (RDBMS) may be built and deployed to
efficiently satisfy these objectives. The database would include tables for clients,
productions, locations, properties, and personnel kinds, as well as the required linkages
between them.The database system would allow Quiet Attic Films to manage their
projects more effectively, maintain client connections, allocate properties to films,
assign personnel kinds, and monitor locations, providing a holistic solution for their
production management needs.

Database Mapping
The process of matching fields from numerous datasets into a schema, or centralized
database, is known as data mapping. Data mapping is necessary for data transport, input,
processing, and management. The ultimate goal of data mapping is to merge numerous data
sets into a single one.
Data mapping is the process by which multiple data sets with varied means of designating
comparable points may be connected in a way that makes them accurate and usable at the
eventual destination.
In the corporate sector, data mapping is a regular practice. However, as the volume of data
and the complexity of the systems that use it have increased, so has the data mapping
process, necessitating the usage of automated and sophisticated solutions.
Table schema
Production (ProductionID, Title, Start date, End date, ClientID)
Client (ClientID, First Name, Last Name, Gender, Con No)
Property (PropertyID, Name, Description )
Location (LocationID, Name, Address)
Staff (StaffID, Name, Staff_ type ID )
Staff type (Staff_typeID, Type name )
Payment (PaymentID, Payment date, Amount, Description, ProductionID, ClientID)
56
57
52
Normalization
Normalization is a database design technique that reduces data redundancy and eliminates
undesirable characteristics like Insertion, Update and Deletion Anomalies. Normalization rules
divides larger tables into smaller tables and links them using relationships. The purpose of
Normalization in SQL is to eliminate redundant (repetitive) data and ensure data is stored logically.
Users User requirement Sources describing the

specification enterprise such as data
dictionary& data
model
Unnormalize
form(UNF)
Remove repeating groups
First Normal
Form(1NF)
Remove partial dependency
Second Normal
Form(2NF)
Remove transitive dependency

Third Normal
Form (3NF)
Make sure every determinant is a

Boyce-Codd Normal
form (BCNF)
Remove multi valued

dependencies
Fourth Normal
Form(4NF)
Remove join dependencies

Fifth Normal
Form(5NF)
Figure 2: Normalization Flow

candidate key
Important Points Regarding Normal Forms in DBMS
When it comes to database normalization, there are several levels to consider. The first level is 1NF,
which requires each table cell to have only one value and each column to have a unique name. This
eliminates duplicate data and simplifies queries. The next level is 2NF, which eliminates redundant
data by ensuring that each non-key attribute is dependent on the primary key. The third level is 3NF,
which builds on 2NF by requiring that all non-key attributes are independent of each other. Boyce-
Codd Normal Form, or BCNF, is a stricter form of 3NF that ensures each determinant in a table is a
candidate key. The fourth Normal Form, or 4NF, ensures that a table does not contain any multi-
valued dependencies. Finally, the highest level of normalization is 5NF, which involves breaking a
table into smaller ones to remove data redundancy and improve data integrity. While normalizing a
database can reduce redundancy, improve consistency, and enhance performance, it's important to
balance it with practicality and avoid making the database design and queries too complex.
Applications of Normal Forms in DBMS
When organizing data in a database, it's important to consider normal forms. Normal forms help
ensure that data is consistent and free from redundancies, reducing the risk of errors and
inconsistencies. By organizing data into tables with unique information, normal forms also reduce
the storage space required for the database and improve query performance by minimizing the
number of joins required to retrieve information. Additionally, normal forms make it easier to
maintain and modify the database, allowing for efficient and scalable design.
Advantages of Normal Form
By using normalization, duplicate data in tables can be eliminated, which reduces the amount of
storage space needed and enhances database efficiency. Additionally, data is stored in a consistent
and organized manner, which reduces the risk of data inconsistencies and errors. Guidelines for
organizing tables and data relationships are provided by normalization, this makes it easier to design
and maintain a database. Normalized tables are typically easier to search and retrieve data from,
resulting in faster query performance. Lastly, normalization reduces the complexity of a database by
breaking it down into smaller, more manageable tables, making it easier to add, modify, and delete
data.
Overall, using normal forms in DBMS helps to improve data quality, increase database efficiency,
and simplify database design and maintenance.

Anomaly
A database anomaly is a data discrepancy caused by an action such as an update, insertion, or
deletion. When a record is kept in numerous locations and not all of the copies are updated,
discrepancies might occur.
Anomalies emerge when the data in the database contains too much redundancy and the tables that
comprise the database are improperly structured.
These anomalies can be categorized into three types:
• Insertion Anomalies
These arise when data cannot be inserted into a database because needed fields are missing, or the
data is incomplete. For example, if a database requires that each record have a primary key, but no
value is supplied for a specific record, the record cannot be entered into the database.
• Deletion Anomalies
Deletion anomalies occur when removing a record from a database and might result in inadvertent
data loss. If a database contains information about customers and orders, for example, removing a
customer record may result in the deletion of all orders linked with that client.
• Update Anomalies
Update anomalies arise while altering data in a database and can cause inconsistencies or mistakes.
For example, if a database contains information about workers and their pay, changing an employee's
salary in one record but not other connected records may result in inaccurate computations and
reporting.
Database anomalies are errors in the database caused by inefficient storage of everything in a flat
database. It may be eliminated by the Normalization procedure, which typically separates the
database, resulting in fewer anomalies in the database.
Advantages:
• Data Integrity: Relational databases enforce data integrity via constraints such as primary
keys, foreign keys, and referential integrity rules, ensuring that data is correct and consistent.
• Scalability: Relational databases can manage vast volumes of data without losing
performance.
• Flexibility: The relational approach enables flexible data querying, making it easier to get
particular information and produce reports.
• Relational databases provide strong security mechanisms that safeguard data from unwanted
access.
Disadvantages:
• Redundancy: The relational architecture can result in data redundancy, which occurs when
the same data is kept in many locations, resulting in inefficiency and probable data
inconsistency.
• Complexity: Creating and maintaining a relational database may be timeconsuming and
difficult, necessitating specific knowledge and abilities.
• Performance might deteriorate as the database expands in size owing to the necessity to
connect several tables to access information.
• Inability to handle unstructured data: The relational paradigm is unsuitable for dealing with
unstructured or semi-structured data such as photos, videos, and text documents
(https://www.geeksforgeeks.org/,2023)
First Normal Form (1NF)
To ensure a relation conforms to the first normal form, it cannot contain composite or multivalued
attributes. Conversely, a relation is considered in first normal form when it contains only single-
valued attributes.
A table meets the criteria for being in first normal form if it fulfills the following requirements:
• All attributes are single-valued.

• The attribute domain remains consistent.
• Each attribute/column has a unique name.
• The order of data storage is irrelevant.
Second Normal Form (2NF)

The concept of full functional dependency forms the foundation of the Second Normal Form (2NF).
This rule applies to relations with composite keys, meaning those with a primary key composed of
two or more attributes. If a relation has a primary key consisting of a single attribute, it is
automatically in at least 2NF. However, if a relation is not in 2NF, it may suffer from update
anomalies. To be in the second normal form, a relation must be in first normal form and must not
contain any partial dependency. A relation is considered to be in 2NF if it does not have a partial
dependency. This means that no non-prime attribute (attributes that are not part of any candidate key)
can be dependent on any proper subset of any candidate key of the table. In other words, if every
non-primary-key attribute is fully functionally dependent on the primary key, then the relation is in
Second Normal Form (2NF) provided that it is also in First Normal Form.
Third Normal Form (3NF)
When a relation is in 2NF but has no transitive partial dependency, it is said to be in its third normal
form. When there is no transitive dependency for the non-prime characteristics, the relation is said to
be in 3NF.
In simpler terms, when none of the non-primary key characteristics transitively rely on their main
keys in a relation that is in 1NF or 2NF, we may say that the relation is in the third normal form of
3NF.
If there is no transitive dependency for non-prime characteristics and the relation is in second normal
form, it is in third normal form.
A relation is in 3NF if at least one of the following condition holds in every non-trivial function
dependency X –> Y:
• X is a super key.
• Y is a prime attribute (each element of Y is part of some candidate key).
A relation is in Third Normal Form (3NF) if it is in First and Second Normal Form and no non-
primary-key attribute is transitively dependent on the main key.DBMS Applications of Third Normal
Form

The 3NF is used to eliminate data duplication and achieve data integrity in a database. The third
normal form is appropriate for creating standard relational databases. Because the bulk of the 3NF
tables are devoid of deletion, update, and insertion abnormalities. Furthermore, a 3NF would always
assure loss lessness and the maintenance of functional dependencies.
(https://www.geeksforgeeks.org/,2023)
It seems that all the tables author have given are in at least the first normal form (1NF). According to
1NF, a table must have a primary key, and every column should contain indivisible values without
any recurring groups or arrays. Let's take a closer look at each table to show that they meet the 1NF
requirements.
Production:
• ProductionID (Primary Key)

• Title
• ClientID (Foreign Key)
• StartDate
• EndDate
The primary key "ProductionID" uniquely identifies each entry in the "Production" database. There
are no repeated groups or arrays in this table, and all attributes (Title, ClientID, StartDate, EndDate)
have atomic, indivisible values.
Client:
• ClientID (Primary Key)

• Name
• ContactInfo
Each client is individually identified by the primary key "ClientID" in the "Client" database. This
table's attributes (Name and ContactInfo) all have atomic, indivisible values, and there are no
repeated groups or arrays.
Location:
• LocationID (Primary Key)

• Name
• Address
• Description
The "Location" table has a primary key "LocationID" that uniquely identifies each location. All
attributes in this table (Name, Address, and Description) contain atomic, indivisible values, and there
are no repeating groups or arrays.
Property:
• PropertyID (Primary Key)

• Name
• Description
• LocationID (Foreign Key)
The primary key "PropertyID" in the "Property" database uniquely identifies each property. There
are no repeated groups or arrays in this table, and all attributes (Name, Description, and LocationID)
have atomic, indivisible values.
StaffType:
• StaffTypeID (Primary Key)

• TypeName
Each staff type is individually identified by the primary key "StaffTypeID" in the "StaffType"
database. This table's attributes (TypeName) all have atomic, indivisible values, and there are no
repeated groups or arrays.
Staff:
• StaffID (Primary Key)

• FirstName
• LastName
• StaffTypeID (Foreign Key)
Each staff member is individually identified by the primary key "StaffID" in the "Staff" database.
There are no repeated groups or arrays in this table, and all attributes (FirstName, LastName, and
StaffTypeID) have atomic, indivisible values.
ProductionLocation (Junction table for Production-Location many-to-many relationship):

• ProductionLocation (Primary Key)
• ProductionID (Foreign Key)
The main key "ProductionLocationID" in the "ProductionLocation" database uniquely identifies each
record. It is made up of two foreign keys, "ProductionID" and "LocationID," which relate to their
respective tables and constitute a junction table for a many-to-many relationship. The values in these
foreign key columns are all atomic.
Payment:
• PaymentID (Primary Key)

• Type
• Date
• Amount
• Description
Each payment is individually identified by the primary key "PaymentID" in the "Payment" database.
There are no repeated groups or arrays in this table, and all attributes (Type, Date, Amount,
Description, ProductionID, and ClientID) have atomic, indivisible values.
In summary, all of the tables have well-defined primary keys, and each column includes only atomic,
indivisible values, with no repeated groups, arrays, or complicated data structures.
There is a main key for each table, represented as "(Primary Key)". There are no recurring groups or
arrays, only atomic values in each table's columns. As a result, all of the tables meet the first normal
form (1NF) based on the information given.
We need to make sure that every non-prime attribute (an attribute not included in the primary key) is
completely functionally reliant on the whole primary key in order to transition from the first normal
form (1NF) to the second normal form (2NF). To put it another way, we need to get rid of table
partial dependencies. Let's examine each table's conformity with the second normal form:
Production Table (1NF):

• Title
• StartDate
• EndDate
All of the properties in this table are completely dependent on the main key (ProductionID) to work.
No partial dependencies exist. The second normal form (2NF) is thus already satisfied.
Client Table (1NF):

• Name
• ContactInfo
There are no partial dependencies in the "Client" table. The ClientID is the main key, and it governs
all characteristics. The second normal form (2NF) is satisfied.
Location Table (1NF):

• Name
• Address
• Description
There are no partial dependencies in the "Location" table either. The LocationID is the fundamental
key, and it underlies all characteristics. The second normal form (2NF) is satisfied.
Property Table (1NF):

• Name
• Description
The only factor affecting "Name" and "Description" in this database is the main key (PropertyID).
But because "LocationID" is a foreign key, it refers to a different table. This table may be divided
into two independent tables to reach second normal form:

• Name
• Description
PropertyLocation Table (2NF):
• PropertyID (Primary Key, Foreign Key)

You may make sure that each table has qualities that are completely reliant on its main key by doing
this.
StaffType Table (1NF):

• TypeName
There are no partial dependencies in the "StaffType" database. The second normal form (2NF) is
satisfied.
Staff Table (1NF):

• FirstName
• LastName
The only factor that influences "FirstName" and "LastName" in this database is the main key
(StaffID). "StaffTypeID" is a foreign key, however. You can make a separate table for staff
assignments in order to obtain second normal form:
Staff Table (2NF):

• FirstName
• LastName
StaffAssignment Table (2NF):
• StaffAssignmentID (Primary Key)

• StaffID (Foreign Key)
You may make sure that each table has qualities that are completely reliant on its main key by doing
this.
ProductionLocation Table (1NF):
• ProductionLocationID (Primary Key)

An example of a junction table for a many-to-many relationship is the
"ProductionLocation" table. It's in the second normal form (2NF) already.
Payment Table (1NF):

• Type
• Date
• Amount
• Description
Additionally, the "Payment" table is in second normal form (2NF). The main key (PaymentID) is a
prerequisite for all non-prime properties.
In conclusion, all the tables have been examined for conformity with the second normal form (2NF),
and modifications have been made where needed to get rid of any partial dependencies. This makes
sure that every non-prime characteristic is totally reliant on every single primary key in every single
table.
To attain third normal form (3NF), we must verify that every non-prime property is transitively
reliant on the main key and that no non-prime attributes are transitively dependent on each other.
Let's look at each table in terms of third normal form compliance:
Production Table (2NF):

• Title
• StartDate
• EndDate
Because there are no partial dependencies, the "Production" table is already in the second normal
form (2NF). To determine 3NF compliance, we must look for transitive dependencies. In this
situation, "ClientID" is a foreign key, however it is reliant on the main key "ProductionID" rather
than any other property. As a result, it fulfills the third normal form (3NF).
Client Table (2NF):

• Name
• ContactInfo
The "Client" table is already in the second normal form (2NF), and there are no attribute transitive
relationships. It conforms to the third normal form (3NF).
Location Table (2NF):

• Name
• Address
• Description
The "Location" table is in the second normal form (2NF), and there are no attribute transitive
relationships. It conforms to the third normal form (3NF).

• Name
• Description
The table "Property" is written in the second normal form (2NF). To accomplish 3NF, we must first
determine whether there are any transitive dependencies. The foreign key "LocationID" is directly
dependent on the primary key "PropertyID." There aren't any more transitive dependencies. The third
normal form (3NF) is satisfied by the "Property" table.

StaffType Table (2NF):

• TypeName
There are no transitive connections among attributes in the "StaffType" table, which is in the second
normal form (2NF). It conforms to the third normal form (3NF).
Staff Table (2NF):

• FirstName
• LastName
There are no transitive connections among attributes in the "Staff" table, which is already in the
second normal form (2NF). It conforms to the third normal form (3NF).
ProductionLocation Table (2NF):
• ProductionLocationID (Primary Key)

The "ProductionLocation" table is already in the second normal form (2NF), and there are no
attribute transitive relationships. It conforms to the third normal form (3NF).
Payment Table (2NF):

• Type
• Date
• Amount
• Description

The table "Payment" is written in the second normal form (2NF). To determine 3NF compliance, we
must look for transitive dependencies. In this scenario, the foreign keys "ProductionID" and
"ClientID" are directly reliant on the main key "PaymentID." There aren't any more transitive
dependencies. The third normal form (3NF) is satisfied by the "Payment" table.
In conclusion, all of the tables in database schema are in third normal form (3NF). There are no
transitive dependencies between non-prime attributes, and each non-prime attribute is totally
operationally reliant on its own table's main key.
System Interfaces
Figure 3 : Login Form Interface Design
Users must input their login information (Username , Password and UserType ) in the available text
boxes. The system verifies the entered credentials against a database of authorized users once the
user clicks the "Login" button.

The system allows access to the main dashboard if the credentials are legitimate (they match those
of an authorized user).
The system shows an error message, such as "Invalid username or password," and access to the
system is refused if the credentials are incorrect (that is, if they do not match the credentials of any
authorized user).
Figure 4: Main Form Interface Design
After logging in successfully, users are directed to the main dashboard, which provides a quick
view of important information and actions. The " Production" button allows users to initiate the
process of creating a new production. Product location, product attributes and staff information are
available through the product button. You can go to the client registration form through the client
button on the menu dashboard. You can get relevant information related to payment through
payment button. Search and report button and assignment button can be moved to related

information. Logout can exit the menu. Data grids or tables display lists of recent productions,
clients, and payments, while notifications inform users about upcoming events such as production
schedules and deadlines, and any pending tasks that require their attention. Users can easily
navigate to other parts of the system using links or buttons that lead to forms and features for
managing data.
Figure 5: Client Registration Interface Design
The client button on the main menu will take you to the client registration form, where the client's
personal information and relevant data will be entered. This form streamlines the process of
enrolling new clients. You can search for the information of all registered clients by clicking the
search button on the form. Also, the save and update button gives the ability to save information
and the ability to change information. Unnecessary information can be deleted by the delete button.
This process takes place in a client registration form.

Figure 6:Production Form Interface Design
To create and manage production records, users fill in necessary details such as the production title
and its start and end dates. They select the client associated with this production from a dropdown
list, which is typically populated with names from the database. Users can select multiple locations,
properties, and staff members associated with the production from multi-select list boxes. Once done,
they can save the production record to the database by clicking the "Save" button.

Figure 7 : Report Form Interface Design
Users can search for specific records such as productions, clients, or staff by entering the relevant
search criteria into text fields or dropdowns. Once they have entered the search criteria, clicking the
"Search" button initiates the search process, querying the database for records that match the
specified criteria. The search results are displayed in a data grid or table format, providing a list of
records that meet the search criteria. Users can interact with the search results by viewing details of
specific records or generating reports based on the displayed data.

Figure 8: Assignment Form Interface Design
To assign resources such as locations, properties, and staff to a specific production, users select the
production for which they want to make assignments from a dropdown list. For each type of
assignment, such as Production-Location, Production-Property, or ProductionStaff, users make
selections based on available resources. They can choose multiple locations, properties, or staff
members as needed. Once they have made the selections, users confirm the assignments by clicking
the "Assign" button, and the system updates the database with the assigned resources.

Figure 9 : Payment Form Interface Design
To record and manage payment-related data such as transactions associated with productions, users
fill in the payment type from a dropdown list, payment date, amount, and a description if needed.
They also select the relevant production and client from dropdowns. Once done, they can save the
payment record by clicking the "Save" button, which adds the payment data to the database.
Satisfying the System’s Design Interfaces User Requirements
User Requirements:
• The system should allow users to create, manage, and track movie productions effectively.
Users can provide production details, connect multiple productions to a single client, and
manage locations, properties, and staff.
• Users should also be able to specify one or more locations for each production, including
names, addresses, and relevant details.
• The system should track properties used in each production, allowing users to record property
details, including names, descriptions, and quantities, and associate properties with
productions.

• Users should be able to assign properties to specific production locations, meeting the
requirement to track which properties are needed and where for each production.
• Users can designate staff types and allocate staff members to specific productions, satisfying
the need to keep a list of different staff types and assign them to productions.
Functional Requirements:
• The system should allow users to view, edit and delete records, which is essential for effective
data management. Users should be able to connect multiple productions to a single client.
• Users should be able to create and manage product records, specifying titles, dates,
descriptions, and associating them with clients.
• The system should allow users to manage location records, including associating multiple
locations with a single product.
• Users should be able to add, edit, and delete property records, specifying types and details.
The system should support finding the required attributes for each product and location.
• The system should provide reporting capabilities, enabling users to generate various reports,
including product lists for specific clients, lists of required attributes for products and
locations, and staff assignments by role.
Non-functional Requirements:
• The system design should address security by encrypting access control, user authentication,
and data protection to safeguard sensitive information.
• The system should aim to maintain responsiveness and functionality even with large amounts
of data, meeting performance requirements.
• Continuity should be maintained through referential integrity and error handling to prevent
data corruption.
• The user interface should be designed to be user-friendly and intuitive, ensuring ease of use.
• Detailed documentation should be provided for system usage, maintenance, and
troubleshooting, fulfilling the need for comprehensive guidance.
• The system should be designed to follow accessibility standards to ensure it is easily
accessible to users with varying needs.
• The system's user interface should be carefully designed, considering font size, font color,
background image, and background color to provide a visually appealing and effective
interface.

The system design meets all the requirements effectively. It provides a comprehensive solution for
Quiet Attic Films, enabling efficient management of movie productions and associated data while
addressing security, performance, and usability concerns.
Structure Query Language
SQL (Structured Query Language) is a database programming language that contains Database
Creation, Database Deletion, Fetching Data Rows, Modifying & Deleting Data Rows, and other
features.
SQL stands for Structured Query Language, and it is a computer language used to store, manipulate,
and retrieve data from relational databases. SQL was created by IBM Computer Scientists in the
1970s and was adopted as a standard by the American National Standards Institute (ANSI) in 1986
and the International Organization for Standardization (ISO) in 1987.
Figure 10 : Types Of SQL
Data Definition Language
The SQL statements that may be used to specify the database structure make up DDL, or Data
Definition Language. It is used to construct and alter the structure of database objects in the
database and only works with descriptions of the database schema. Although data cannot be created,
modified, or deleted with DDL, database structures can. In most cases, a typical user shouldn't use
these commands; instead, they should use an application to access the database.
Commands in the Common Data Definition Language
DDL commands are not used by general application users, or people who are not permitted to deal
directly with a database. These ordinary users can and should only have indirect access to the
database via the application.

In DDL, the most common command types are CREATE, ALTER, and DROP. For the command to
run and modifications to take effect, all three kinds have a predetermined syntax that must be
followed.
List of DDL commands:
CREATE: This command is used to create the database or its objects (like table, index, function,
views, store procedure, and triggers).
This command is used to create a new table in SQL. The user has to give information like table
name, column names, and their datatypes. Syntax
• CREATE TABLE table_name
(
column_1 datatype, column_2 datatype,
column_3 datatype,
....
);
DROP: This command is used to delete objects from the database.

This command is used to remove an existing table along with its structure from the Database.
Syntax
• Syntax to drop an existing table.
DROP TABLE table_name;
ALTER: This is used to alter the structure of the database.

This command is used to add, delete or change columns in the existing table. The user needs to
know the existing table name and can do add, delete or modify tasks easily.
Syntax
• Syntax to add a column to an existing table.
ALTER TABLE table_name
ADD column_name datatype;
TRUNCATE: This is used to remove all records from a table, including all spaces allocated for the
records are removed.
Syntax
• Syntax to remove an existing table.
TRUNCATE TABLE table_name;
RENAME: This is used to rename an object existing in the database.

It is possible to change name of table with or without data in it using simple RENAME command.
• Syntax
RENAME TABLE <Table Name> To <New_Table_Name>;
Creating the database using DDL
Figure 11: Database View
Figure 12 : Create Database

Figure 13 : Create all Tables
Database Query
CREATE DATABASE Quit_Attic_Films
USE [Quit_Attic_Films];
CREATE TABLE Client (

ClientID INT PRIMARY KEY,
Name VARCHAR(50),
ContactInfo VARCHAR(10)
);
CREATE TABLE Location (

LocationID INT PRIMARY KEY,
Name VARCHAR(50),
Address VARCHAR(70),
Description TEXT
);
CREATE TABLE Property (

PropertyID INT PRIMARY KEY,
Name VARCHAR(50),
Description TEXT,
LocationID INT,
FOREIGN KEY (LocationID) REFERENCES Location(LocationID)
);
CREATE TABLE StaffType (

StaffTypeID INT PRIMARY KEY,
TypeName VARCHAR(50)
);
CREATE TABLE Staff (

StaffID INT PRIMARY KEY,
FirstName VARCHAR(25),
LastName VARCHAR(25),
StaffTypeID INT,
FOREIGN KEY (StaffTypeID) REFERENCES StaffType(StaffTypeID)
);

CREATE TABLE Production (
ProductionID INT PRIMARY KEY,
Title VARCHAR(25),
ClientID INT,
StartDate DATE,
EndDate DATE,
FOREIGN KEY (ClientID) REFERENCES Client(ClientID)
);
CREATE TABLE ProductionLocation (

ProductionLocationID INT PRIMARY KEY,
ProductionID INT,
LocationID INT,
FOREIGN KEY (ProductionID) REFERENCES Production(ProductionID),
FOREIGN KEY (LocationID) REFERENCES Location(LocationID) );
CREATE TABLE Payment (

PaymentID INT PRIMARY KEY,
Type VARCHAR(50),
Date DATE,
Amount CHAR(10),
Description TEXT,
ProductionID INT,
ClientID INT,
FOREIGN KEY (ProductionID) REFERENCES Production(ProductionID),
FOREIGN KEY (ClientID) REFERENCES Client(ClientID) );
Security mechanisms
Today's corporations place a high importance on database security. Database security refers to the
use of a wide variety of information security rules to safeguard databases from breaches of their
availability, confidentiality, and integrity. It involves a variety of controls, including technological,
procedural/administrative, and physical controls. Within the more general categories of computer
security and information security, database security is a specialized subject.
Access control, auditing, authentication, encryption, and integrity controls are a few examples of
database security controls.
Application security and backups. The selective limitation of access to a location or other resource
is known as access control (AC). Accessing can refer to utilizing, entering, or consuming.
Authorization is the process of obtaining access to a resource. Database auditing entails keeping an
eye on a database so that you are aware of database users' activity. Auditing is frequently set up by
database administrators and consultants for security reasons, such as to make sure that those who
shouldn't have access to information don't. The initial stage in access control is authentication, and
there are three typical elements utilized for authentication. It is the procedure or action of ensuring
that a user seeking to access a database has the proper authorization to do so and is only granted
access to the functions for which they have been granted.
Three common elements are utilized for authentication:
Something you are aware of, like a password Something that you own, like a smart card Something
that identifies you (like a fingerprint or another biometric technique)Data saved in databases can be
converted into "cipher text" (an unintelligible form) via the encryption process, which employs an
algorithm. Although encryption does not by itself stop interference, it does hinder a potential
interceptor from understanding the material. The intended data or message, known as plaintext, is
encrypted in an encryption scheme using an encryption method. An encryption technique often
employs a pseudo-random encryption key produced by an algorithm for technical reasons. The
entire completeness, correctness, and consistency of the data is referred to as data integrity. Data
integrity is often enforced by the implementation of standard practices and standards throughout the
database design process.
A relational database uses three integrity constraints. Integrity of entities, references, and domain
Backups are copies of your database's data that may be used to recreate the original data. Physical
backups and logical backups are two kinds of backups. For eventual re-importation into a database
using the related Oracle import tool, logical backups comprise logical data that has been exported
from a database using an Oracle export utility and stored in a binary file. The cornerstone of every
reliable backup and recovery strategy is a physical backup. In many situations, logical backups are a
helpful addition to physical backups, but they do not provide enough protection against data loss in
the absence of physical backups.
A customer installs a SQL server authentication. Database roles can be created by going to the
database security auction. The step is to choose the role name of the new database role and what the
role can do, for example, can access the database, put data, read data, back up, address, etc. Create
an account and choose the things you can do with them. When the OK button is pressed. It creates
a category. You can go to the created user category and give the username and login name and job
role created as default method.
According to each job roll, the user can separate the things to be done.
Develop database Fulfil the Requirements of User and System
User Credentials and Authorization:

• The "Users" table allows users to authenticate their identity by storing their usernames and
securely hashed passwords. The "Authorized" field enables the system to control access based
on user credentials.
Main Dashboard:
• The database includes tables for "Productions," "Clients," and "Payments," which match the
data displayed on the main dashboard. The relationships between these tables ensure data
integrity and the ability to link relevant information.
Production Information Form:

• The "Productions" table stores production records, including titles, start and end dates, and
links to clients. Junction tables for locations, properties, and staff allow multiple selections
associated with a production.
Assignment Forms:
• Separate tables for assignments, such as "Production-Location," allow for the management of
resources assigned to specific productions. These tables maintain the relationships between
productions and resources efficiently.
Search and Reporting Form:

• The flexible search system queries the database tables based on user criteria, enabling users to
search for productions, clients, staff, or other relevant data. The data grid or table format
facilitates easy interaction with the search results.
Payment Information Form:

• The "Payments" table manages payment records, including type, date, amount, and
descriptions. Links to productions and clients provide context for each payment.
Database setup aligns with the system requirements and user requirements effectively. It allows for
data storage, retrieval, and manipulation in a manner that supports the core functionality of the
system. However, successful implementation will also depend on the application layer (the software
that interacts with the database), security measures (to protect sensitive user data), and performance
considerations (to ensure efficient and responsive operations). Careful implementation and thorough
testing will be essential to ensure that the system functions as intended and meets user needs.

Improvement that can be made in the System
GPS Tracking for Location Management:

Incorporate GPS tracking functionality to enable real-time tracking of production locations and
provide geospatial data. For instance, the system could use GPS coordinates to track the exact
location of a production shoot, making it easier to plan logistics and ensure that the correct properties
and staff are available at the right time and place.
Production Schedule Management:

Develop a feature for managing production schedules, including the ability to set timelines,
deadlines, and milestones. For example, the system could allow production managers to create
detailed schedules for each production, track progress, and receive notifications when tasks are
behind schedule.
Cost Estimation and Budgeting:

Integrate a cost estimation and budgeting module to plan and track expenses for each production.
Such a feature would help in estimating the costs of properties, staff, equipment, and other resources
needed for a production and compare them against the actual expenses.
Inventory Management:
Implement an inventory management system to track the availability and usage of equipment, props,
costumes, and other production-related items. This feature would enable the database to keep track of
available items, check them out for productions, and log their return, reducing the risk of loss or
mismanagement.
Client Interaction and Communication History:

Enhance the system to keep a record of client interactions and communication history. For example,
store records of emails, phone calls, meetings, and notes related to client communications. This helps
in maintaining positive client relationships and providing better service.
Staff Scheduling and Availability:

Add a staff scheduling module that allows for managing staff availability and assigning them to
specific productions. The system could display staff availability, making it easier to schedule them
for productions and avoid conflicts.
Automated Reporting and Notifications:

Implement automated reporting and notification features to keep stakeholders informed of
production progress, issues, or upcoming tasks. For instance, automated notifications could be sent
to production managers, clients, and staff when significant milestones are reached, or when issues
arise during a production.
Document Management:
Create a document management system to organize and store contracts, scripts, release forms, and
other production-related documents. This feature would ensure that documents are stored in a
structured manner for easy retrieval and management. It would also enable documents to be
associated with specific productions or clients.
User Access Control and Auditing:

Enhance security and control by implementing user access control and auditing features. For
example, access to sensitive information could be restricted based on user roles, and an audit trail
of who accessed or modified data in the system could be kept.
Mobile App or Responsive Design:

Develop a mobile app or ensure that the system has a responsive design for mobile devices,
allowing staff to access and update data on the go. This feature would enable staff to use a mobile
app to check production details, update schedules, or add location information from the field.
These improvements and additional features can make the database system more robust, user-
friendly, and efficient in meeting the needs of Quiet Attic Films. Consider prioritizing and
implementing these enhancements based on the company's specific requirements and available
resources.
Data Manipulation Language
DML (Data Manipulation Language), which contains SQL instructions that deal with altering data in
a database, is the category under which the bulk of SQL statements fall. Who has access to the
database and data is managed by this part of the SQL statement. Statements that are both DML and

DCL are combined. The DML command won't be able to permanently preserve all database
modifications because it isn't auto committed. They might perhaps be rolled back.
SELECT: This command is used to retrieve rows from a table. The syntax is SELECT
[column name(s)] from [table name] where [conditions]. SELECT is the most widely used DML
command in SQL.
UPDATE: This command modifies data of one or more records. An update command syntax is
UPDATE [table name] SET [column name = value] where [condition].
INSERT: This command adds one or more records to a database table. The insert command syntax is
INSERT INTO [table name] [column(s)] VALUES [value(s)].
DELETE: This command removes one or more records from a table according to specified
conditions. Delete command syntax is DELETE FROM [table name] where [condition].
SELECT
SELECT Production.Title, Client.Name

FROM Production
JOIN Client ON Production.ClientID = Client.ClientID;
SELECT FirstName, LastName
FROM Staff;
SELECT Type, Amount

FROM Payment;
Figure 14: Select Query 1

Figure 15: Select Query 2
Figure 16 : Select Query 3
WHERE
SELECT *
FROM Production
WHERE StartDate > '2023-04-01';
SELECT *
FROM Payment
WHERE Amount > 10000;
SELECT FirstName, LastName

FROM Staff
WHERE StaffTypeID = StaffTypeID;

Figure 17 : Where Query 1
Figure 18:Where Query 2
Figure 19: Where Query 3
UPDATE
UPDATE Property
SET Name = 'furniture'

WHERE PropertyID = 55;
UPDATE Production
SET Title = 'Advertisment'
WHERE ProductionID = 4;
UPDATE Production
SET Title = 'training films'
WHERE ProductionID = 7;
Figure 20 : Update Query 1
Figure 21: Update Query 2
Figure 22: Update Query 3

BETWEEN
SELECT *
FROM Payment
WHERE Date BETWEEN '2023-02-01' AND '2023-05-31';
SELECT *
FROM Production
WHERE startdate BETWEEN '2023-01-30' AND '2023-04-02';
SELECT *
FROM Payment
WHERE Amount BETWEEN 15000 AND 25000;
Figure 23: Between Query 1

IN
SELECT *
FROM Production
WHERE ClientID IN (100, 101);
SELECT *
FROM Payment
WHERE ProductionID IN (2,7);
SELECT *
FROM Location
WHERE LocationID IN (15,12,11);
Figure 26 : In Query 1

Figure 27:In Query 2
Figure 28:In Query 3
GROUP BY
SELECT Type, SUM(Amount) AS TotalAmount

FROM Payment
GROUP BY Type;
SELECT Client.Name, COUNT(Production.ProductionID) AS ProductionCount FROM Client

LEFT JOIN Production ON Client.ClientID = Production.ClientID
GROUP BY Client.Name;
SELECT StaffType.TypeName, COUNT(Staff.StaffID) AS StaffCount

FROM StaffType
LEFT JOIN Staff ON StaffType.StaffTypeID = Staff.StaffTypeID
GROUP BY StaffType.TypeName;

Figure 29: Group Query 1
Figure 30: Group Query 2
Figure 31:Group Query 3

ORDER BY
SELECT *
FROM Client
ORDER BY Name ASC;
SELECT *
FROM Production
ORDER BY StartDate DESC;
SELECT *
FROM Payment
ORDER BY Amount ASC;
Figure 32: Order by Query 1

HAVING
SELECT Client.Name, COUNT(Production.ProductionID) AS ProductionCount
FROM Client
LEFT JOIN Production ON Client.ClientID = Production.ClientID
GROUP BY Client.Name
HAVING COUNT(Production.ProductionID) > 0;
SELECT StaffType.TypeName, COUNT(Staff.StaffID) AS StaffCount

FROM StaffType
LEFT JOIN Staff ON StaffType.StaffTypeID = Staff.StaffTypeID
GROUP BY StaffType.TypeName
HAVING COUNT(Staff.StaffID) >= 0;
SELECT Type, SUM(Amount) AS TotalAmount

FROM Payment
GROUP BY Type
HAVING SUM(Amount) > 100000;
Figure 35: Having Query 1

Testing
Database testing is the process of confirming the objects managing the data and numerous
capabilities surrounding it in order to validate the data that is being kept in a database. Database
testing often covers tasks like validating data, evaluating data integrity, monitoring performance,
and testing various procedures, triggers, and database functionalities.
Test case 01
Login form
Test case Test steps Expected Actual result Status

Successful Navigate to the The system User is Pass
Login login page. should grant directed to the
Enter a valid access to the main
username and main dashboard.
password. dashboard.
Click the
"Login" button.
Test case 02
Main Dashboard

Dashboard Log in with valid The main All expected Pass
Information credentials. dashboard elements are
Check if the should include present.
"Production" the "Production"
button is visible. button, data
Review data grids/tables, and
grids/tables for notifications.
recent
productions,
clients, and
payments.
Verify that
notifications are
displayed.

Test case 03
Production Information Form
Test case Test steps Expected Actual Status

result
Creating Access the Production The production The Pass

Production Information Form. record should be production
saved to the record is
Record Fill in the title, start database with the saved as
date, end date, and specified expected
information.
select a client.
Choose multiple
locations, properties,
and staff members.
Click the "Save"
button
Test case 04
Assignment Forms

Resource Access the Assignment The system The database Pass
Assignment Forms. should update is updated
the database with assigned
Select a production with the resources.
from the drop-down list. assigned
Make selections for resources for
production location, the selected
property, and staff production.
assignments. Click the
"Assign" button
Test case 05
Search and Reporting Form

Search Access the Search and The system The system Pass
Functionality Reporting Form. Enter should display a displays
specific search criteria list of records relevant
(e.g., production title, that match the records.
client name). Click the search criteria,
"Search" button. and users
should be able
to interact with
the results.
Test case 06
Payment Information Form

Recording Access the The payment The payment Pass
Payment Payment data should be data is
Information Form. saved to the successfully
Data Select a payment database as saved.
type, enter payment specified
date, amount, and
select a production
and client. Click
the "Save" button.
Login Form:
User Requirements:
• Users should be able to log in using their unique credentials.
• The login process should be simple and user-friendly.
• Users should receive clear error messages if they enter incorrect credentials.
System Requirements:
• The system must validate the entered username and password against the authorized user
database.
• If the entered credentials are valid, the system should grant access to the main dashboard.
• If the entered credentials are invalid, the system should display appropriate error messages
and deny access.
Evaluation:
The design should be evaluated based on how well it meets user and system requirements, including
the user-friendliness of the login process and the clarity of error messages.
Main Dashboard:
User Requirements:
• The main dashboard should provide an organized and visually appealing overview of key
information.
• It should include a "Production" button for initiating new productions.
• Users should be able to easily navigate to other parts of the system from the dashboard.
• Notifications should effectively inform users about upcoming events and pending tasks.

• The system should populate the dashboard with data from recent productions, client records,
and payment information.
• It should offer links or buttons that lead to forms and features for managing data.
Evaluation:
The design's effectiveness should be assessed based on how well it meets user and system
requirements, including the visual appeal, functionality, and ease of navigation.
Production Information Form:
User Requirements:
• Users should be able to easily create and manage production records.
• The form should include fields for the title, start and end dates, and client selection.
• Users should be able to select multiple locations, properties, and staff members.
• The system should store the entered production data in the database.
Evaluation:
The design should be evaluated based on how well it allows users to create and manage production
records, the user-friendliness of the form, and its data storage capabilities.
Assignment Forms:
User Requirements:
• Users should be able to easily assign resources (locations, properties, and staff) to specific
productions.
• The process should be user-friendly and allow for the selection of multiple resources.
• The system should update the database with the assigned resources.
Evaluation:

The design's effectiveness in enabling resource assignments, its user-friendliness, and its impact on
the database should be evaluated.
Search and Reporting Form:
User Requirements:
• Users should be able to search for records based on specific criteria (e.g., title, client name).
• They should be able to interact with the search results, view details, and generate reports.
• The system should be able to perform database queries based on user-specified criteria and
provide search results.
• It should support interaction with the displayed data.
Evaluation:
The design should be evaluated based on how well it allows users to search for records, interact with
search results, and generate reports.
Payment Information Form:

User Requirements:
• Users should be able to record and manage payment-related data (if applicable).
• The form should include fields for payment type, date, amount, and relevant production and
client selections.
System Requirements (if applicable):

• The system should store payment data in the database.
Evaluation:
The design's effectiveness in recording and managing payment-related data should be evaluated,
focusing on user-friendliness and data storage capabilities.
In addition to these requirements, the system should be evaluated for non-functional requirements
such as security, scalability, and performance. The system must be secure, handle a growing amount
of data, and perform efficiently.

To complete the evaluation, comprehensive testing should be conducted, user feedback should be
gathered, and the system's performance should be compared with established requirements and
expectations. Any discrepancies should be addressed through design improvements or system
updates.
Feedback Form
A feedback form is a useful tool that serves several crucial purposes in different contexts. The
following are some reasons why feedback forms are necessary:
• Quality Improvement: Feedback forms allow individuals or organizations to collect input

from customers, users, or participants about their products, services, or experiences. This
feedback can be used to identify areas that need improvement and to enhance the overall
quality of what is being offered.
• User Satisfaction: Feedback forms help to gauge user satisfaction. By collecting feedback,
you can understand what aspects of a product or service make users happy and which aspects
may be causing dissatisfaction. This information can guide efforts to improve user
experience.
• Issue Resolution: Feedback forms provide a channel for users to report issues, problems, or
concerns they encounter. This enables organizations to identify and address these issues
promptly, which can lead to increased customer loyalty and trust.
• Feature Prioritization: For software products and services, feedback forms can help prioritize
the development of new features or improvements. By gathering feedback on what users want
or need, organizations can make informed decisions about where to focus their development
efforts.
• Data Collection: Feedback forms can be used to collect data on a wide range of topics. For
instance, in research, feedback forms may be used to collect survey data, interview responses,
or opinions on various subjects.
• Customer Engagement: By actively soliciting feedback, organizations can engage with their
customers or users in a meaningful way. This engagement can foster a sense of partnership
and show that the organization values the opinions of its stakeholders. Product or Service

Iteration: Feedback forms support iterative development. As products and services evolve,
feedback can be used to inform the next round of changes and updates.
• Legal Compliance: In some cases, feedback forms may be necessary for legal or regulatory
reasons. For instance, certain industries are required to collect and document customer
feedback to ensure compliance with specific standards or regulations.
• Performance Evaluation: In educational or professional settings, feedback forms are used for
performance evaluation. They help instructors, employers, or organizations assess the
performance of students, employees, or participants in various programs or activities.
• Continuous Improvement: Feedback forms support a culture of continuous improvement.

They encourage ongoing assessment and adaptation to meet changing needs and expectations.
• Conflict Resolution: In situations where conflicts or disputes arise, feedback forms can be a
means of documenting and resolving issues. They provide a structured way for parties to
express their concerns.
Figure 38 : Feedback form1

Figure 39: Feedback form 2
Figure 40: Feedback form 3

Figure 41 : Feedback form 4
Figure 42; Feedback form 5

The feedback given for the system set up according to the above google form is positive.
It can be concluded that the built system has good performance.

User Manual
QUIET ATTIC FILMS

Prepared By: JANANI CHATHURYA
Date: 22/09/2023

Table of Contents
• Logging In
• Main Dashboard
• Production Information Form
• Assignment Forms
• Search and Reporting Form
• Payment Information Form (if applicable)
• Logging Out
• Troubleshooting
• Additional Help and Support

1. Logging In
• To access the system, visit the login page.

• Enter your username and password in the provided fields.
• Click the "Login" button to authenticate and access the system.
• If you encounter login issues, contact your system administrator or IT support.

2. Main Dashboard
• Upon successful login, you'll land on the Main Dashboard.
Key features include:

• " Production" button for creating a new production record.
• Lists of recent productions, clients, and payments.
• You can go to the client registration form through the client button on the menu dashboard.
• You can get relevant information related to payment through payment button.
• Search and report button and assignment button can be moved to related information.
• Notifications for upcoming events and pending tasks.
• Navigation links to other system sections.

3. Production Information Form
• Use this form to create and manage production records.
• Fields include:
• Title: Enter the production title.
• Start Date: Set the start date of the production.
• End Date: Specify the end date of the production.
• Client (Dropdown/Selection): Choose the associated client from the dropdown list.
• Locations, Properties, and Staff (Multiple Selection): Select multiple resources related to the
production.
• Click "Save" to save the production record.

4. Assignment Forms (Combined)
• These forms are for assigning resources to productions.
• Sub-forms include:
• Production-Location Assignment: Assign locations to productions.
• Production-Property Assignment: Assign properties to productions with quantities.
• Production-Staff Assignment: Assign staff to productions. • Use dropdowns and multiselect
lists to make assignments.
• Click "Assign" to confirm the assignments.

5. Search and Reporting Form
• Access this form to search for and retrieve information about various aspects of your
productions.
• Specify search criteria in text fields and dropdowns.
• Click "Search" to find records that match the criteria.
• Results are displayed in data grids or tables.
• Generate reports based on displayed data.

6. Payment Information Form (if applicable)
Use this form to record and manage payment-related data for your productions.
Fields include:
• Payment Type: Select the payment type from the dropdown.
• Date: Set the payment date.
• Amount: Enter the payment amount.
• Description: Provide a payment description (if needed).
• Production (Dropdown/Selection): Choose the associated production from the dropdown.
• Client (Dropdown/Selection): Select the associated client from the dropdown.
• Click "Save" to record the payment information.
7. Logging Out
• Click the Log Out button on the main menu

• Clicking the logout button will take you back to the login form
Clicking the setting button in the main menu enables the following issues to be resolved
8. Troubleshooting
If you encounter any issues, consider the following:

• Check your internet connection.
• Verify that you're using the correct login credentials.
• Clear your browser cache if the system is slow.
• For persistent problems, contact your system administrator or IT support.
9. Additional Help and Support
• For further assistance, refer to the system's Help or Support section.

• Consult the system's documentation for in-depth information.
• Reach out to your system administrator for technical issues.

Thank you for using the Quiet Attic Films Database
System. We hope this user manual helps you navigate
the system effectively and efficiently.
Flow Chart
A diagram that depicts a workflow or process is called a flowchart. Another
definition of a flowchart is a diagrammatic description of an algorithm or a
step-by-step process for addressing a problem.
There are a wide variety of flowchart types. Here are just a few of the more commonly used ones.
• Swimlane flowcharts
• Data flow diagrams
• Influence diagrams
• Workflow diagrams
• Process flow diagrams
• Yes/no flowcharts
• Decision flows
Flowchart Symbols
• One step in the process. The step is written inside the box. Usually, only one arrow
goes out of the box.
• Direction of flow from one step or decision to another.

• Decision based on a question. The question is written in the diamond. More than
one arrow goes out of the diamond, each one showing the direction the process takes for a
given answer to the question. (Often the answers are "yes" and "no.")
• Delay or wait
• Link to another page or another flowchart. The same symbol on the other page
indicates that the flow continues there.
• Input or output
• Alternate symbols for start and end points
A process flowchart is a diagram that outlines the decision-making stages that must be taken in
order for the process to proceed. Every step in the chart or visual representation is denoted by a
shape. To depict the flow and direction of the process, arrows and lines link these forms.
Process flowcharts are standardized so that anyone who is familiar with them may glance at one and
comprehend what is going on. They adhere to the logical progression of information so that
company stakeholders have a map for effectively carrying out procedures.
The Benefits of a Flowchart
• It is the most effective method of explaining the system's rationale.
• During the program design, it serves as a guide for a blueprint.
• It is also useful for debugging.
• We can simply analyze the programs using flowcharts.
• Flowcharts are useful in documentation.
Flowchart's Drawbacks
• Flowcharts are difficult to create for large and sophisticated programs.
• It lacks the necessary quantity of detail.
• Flowcharts are extremely tough to duplicate.
• Flowcharts are quite tough to change. (https://www.geeksforgeeks.org/,2023)
Process of Creating and Managing Production

IF True
Save data to database
YES
and show success
message
False
Show error STOP

message & return
• The "Start" sign marks the start of the procedure.

• Users add production details such the title, start date, and finish date. They also choose a
customer from a dropdown menu and various properties, staff members, and locations from
multiselect lists.
• The user hits "Save" once all the information has been entered.
• All input data is subjected to validation tests by the system to make sure it is accurate (e.g., date
formats, necessary fields).
• The system continues to store the data to the database and displays a success message as output
before concluding the procedure if all of the input data is correct (Yes).
• If any of the entered data is incorrect (No), the system displays an error notice and sends the
user back to the form to make the necessary changes.
• The "End" sign marks the finish of the procedure.
This flowchart shows the process of entering and storing production data, together with the necessary
validation tests and user feedback. Data entry and database storage accuracy can be helped by this.

Process of Client Registration

START
INPUT F_name, L_name,

Con No
Register Button Clicked, Validation

Check
True
IF
YES Create client record
and show success
message
False
Show error
message & return STOP
to registration form
The registration process starts at the "Start" symbol. Clients decide to create an account within the
system by providing their details and preferences. They enter their first name, last name, contact
information, and preferences, and then click the "Register" button to submit their registration details.
The system performs validation checks to ensure that the entered information is accurate and
complete. For example, it checks if the fields are not empty and if the contact information is in the
correct format. If the entered information passes validation checks, the system creates a client record
in the database, saving the client's information, and shows a success message to inform the client that
their registration was successful. If the entered information is not valid, the system shows an error
message, informing the client that their registration details contain errors, and the client is returned to
the registration form to make corrections. Clients can cancel the registration process at any point by

clicking the "Cancel" button. In summary, this flowchart outlines the process of client self-
registration, including data entry, validation checks, and feedback to the client. If the client's
registration details pass validation, they are successfully registered, and if not, they are informed of
any errors and given the opportunity to correct them.

Process of Payment
START
INPUT Payment type,

Payment date, Amount,
Description, Production,
Client ID
Save Button Clicked, Validation

Check True
True
IF
YES Save payment data to
database and show
success message
False
Show error
STOP
message & return
to payment form
• The process begins with the "Start" symbol.

• Users enter payment information, including selecting the payment type from a dropdown,
entering the payment date, specifying the amount, and providing an optional description. They
also select the relevant production and client from dropdowns.
• After entering all the information, the user clicks the "Save" button.
• The system performs validation checks for the entered data, ensuring it is valid (e.g., payment
type selection, date format, amount).

• If all input data is valid (Yes), the system proceeds to save the payment data to the database and
shows a success message as output before ending the process.
• If any input data is invalid (No), the system shows an error message as output and returns to the
payment form for the user to make corrections.
• The process ends at the "End" symbol.
This flowchart represents the workflow of entering and saving payment-related data, with validation
checks and feedback to the user. It helps ensure that payment records are accurately recorded in the
system.
Use Case Diagram
A use case diagram is used to describe a system's dynamic behavior. It contains the functionality of
the system by including use cases, actors, and their interactions. It simulates the jobs, services, and

functionalities required by an application's system/subsystem. It represents a system's high-level
functionality as well as how the user interacts with the system.
A use case diagram's primary goal is to depict the dynamic nature of a system. It collects the
system's needs, which comprise both internal and external inputs. It invokes people, use cases, and a
variety of other things that hold actors and elements responsible for the implementation of use case
diagrams. It illustrates how an entity from the outside world might interact with a component of the
system.( https://www.javatpoint.com/,2023)

Figure 43 : Usecase Diagram
Data Flow Diagram (DFD)
Data flow diagrams are used to depict the movement of data in a corporate information system
graphically. DFD outlines the procedures that occur in a system to move data from the input to file
storage and to generate reports.

Data flow diagrams are classified as logical or physical. The logical data flow diagram depicts the
movement of data through a system to fulfil certain business functions. The physical data flow
graphic depicts how the logical data flow is implemented.
The Components of a DFD

Different individuals use various notations to express DFD processes, data storage, data flow, and
external entities. Yourdon and Coad's and Gane and Sarson's notations are the most often utilized.
DFD represents the flow of the graphic using four fundamental symbols.
• Process
• Data Store
• External Entity
• Data Flow (Arrow)
Figure 44: DFD Symbols

Context Level DFD
A context diagram is the highest level in a data flow diagram that provides an overview of the entire
system. It consists of only one process that represents the entire system. The context diagram should
be divided into major processes that provide more details and each major process can be further
divided to give even more detail.
The context diagram displays all external entities, as well as the major data flow to and from them.
No data storage is included in the context diagram.
The single process in the context-level diagram, which represents the entire system, can be exploded
to include the major processes of the system in the next level diagram, which is called diagram 0.
Level 0 DFD
Client
00
0 )000
Film production system
Staff

Future improvement for Database System

As a designer, it is important to plan for future improvements and enhancements to make the system
more robust, efficient, and user-friendly. Here are some suggestions for future improvements:
• Data Validation and Integrity: Implement data validation rules to ensure the accuracy and
consistency of data entered into the system. This could include constraints on data types,
lengths, and ranges.
• Scalability: As your system and user base grow, consider strategies for database scalability.
You might explore options like sharding, clustering, or migrating to a more scalable database
system.
• Data Archiving and Purging: Develop a data archiving and purging strategy to manage
historical data efficiently. This can help improve system performance and reduce storage costs.
• User Management: Enhance the user management system to allow for roles and permissions.
Define different levels of access for users, ensuring that they can only perform actions relevant
to their role.
• Audit Logging: Implement audit logging to track all user interactions with the system. This can
be valuable for security, compliance, and troubleshooting.
• Security Enhancements: Continuously monitor and improve security measures. This includes
encryption for sensitive data, regular security audits, and staying up to date with security best
practices.
• Reporting and Analytics: Add reporting and analytics capabilities to provide users with insights
into the data. This can include generating various reports and charts based on user preferences.
• APIs and Integrations: Develop APIs to allow for seamless integration with other systems or
services. This can enhance the system's capabilities and provide more options for data
exchange.
• Performance Optimization: Continuously monitor and optimize the database's performance.

This might involve query optimization, indexing, and caching strategies.

• Data Backups and Disaster Recovery: Establish robust data backup and disaster recovery plans
to ensure data safety and quick recovery in case of system failures.
• User Interface Enhancements: Gather user feedback and make improvements to the user
interface to enhance user experience and make it more intuitive and userfriendly.
• Mobile Access: Consider developing a mobile application or making the system more mobile-
responsive to meet the needs of users who prefer to access the system on the go.
• Notifications and Alerts: Implement a notification system to keep users informed about
important events, deadlines, or updates in real-time.
• Machine Learning and AI Integration: Explore the integration of machine learning and AI for
data analysis, prediction, and automation of routine tasks.
• Feedback Mechanism: Create a feedback mechanism for users to provide input and suggestions
for system improvements. Regularly gather and assess user feedback to prioritize future
enhancements.
References
Anon, 2023. https://herovired.com/. [Online] [Accessed 27 september 2023].
Anon, 2023. https://www.geeksforgeeks.org. [Online] [Accessed 23 september 2023].
Anon, 2023. https://www.javatpoint.com/,. [Online] [Accessed 10 october].
Anon, 2023. www.geeksforgeeks.org. [Online] [Accessed 23 september 2023].
Anon, 2023. www.visual-paradigm.com. [Online] [Accessed 26 september 2023]. Anon, 2023.
www.vskills.in. [Online] [Accessed 25 september 2023]. S, R. A., 2023. www.simplilearn.com.
[Online] [Accessed 30 september 2023].


Database Design and Development

Uploaded by

Copyright:

Available Formats

Database Design and Development

Uploaded by

Document Information

Original Title

Copyright

Available Formats

Share this document

Share or Embed Document

Sharing Options

Did you find this document useful?

Is this content inappropriate?

Copyright:

Available Formats

Database Design and Development

Uploaded by

Copyright:

Available Formats

Higher Nationals

Internal verification of assessment decisions – BTEC (RQF)

INTERNAL VERIFICATION – ASSESSMENT DECISIONS

Has the work been assessed accurately? Y/N

Assessor signature Date

Internal Verifier signature Date

1 Janani Chathurya Database design & Development

Assessor signature Date

2 Janani Chathurya Database design & Development

LO3 Test the system against user and system requirements.

LO4 Produce technical and user documentation.

Grade: Assessor Signature: Date:

Grade: Assessor Signature: Date:

Internal Verifier’s Comments:

Signature & Date:

3 Janani Chathurya Database design & Development

4 Janani Chathurya Database design & Development

Pearson Higher Nationals in

Unit 04: Database Design & Development Assignment

5 Janani Chathurya Database design & Development

Word Processing Rules

6 Janani Chathurya Database design & Development

7 Janani Chathurya Database design & Development

1. I know that plagiarism is a punishable offence because it constitutes theft.

5. I acknowledge that the attachment of this document signed or not, constitutes a

6. I understand that my assignment will not be considered as submitted if this

Student Name /ID Number Janani Chathurya / E194180

Unit Number and Title Unit 4: Database Design & Development

Academic Year 2022/23

Unit Tutor Mr. Gihan Chanaka

Assignment Title Data base system for Quiet Attic Films

8 Janani Chathurya Database design & Development

9 Janani Chathurya Database design & Development

10 Janani Chathurya Database design & Development

11 Janani Chathurya Database design & Development

13 Janani Chathurya Database design & Development

15 Janani Chathurya Database design & Development

LO1 Use an appropriate design tool to design a relational

P1 Design a relational database system using appropriate

M1 Produce a comprehensive design for a fully functional

D1 Evaluate the effectiveness of the design in relation to

P2 Develop the database system with evidence of user

P3 Implement a query language into the relational database

M3 Assess whether meaningful data has been extracted

LO3 Test the systems against user and system requirements

P4 Test the system against user and system requirements.

D2 Evaluate the effectiveness of the database solution in

LO4 Produce technical and user documentation

P5 Produce technical and user documentation.

M5 Produce technical and user documentation for a fully

D3 Evaluate the database in terms of improvements needed

Entity Relationship (ER) Diagram.................................................................................34

Important Points Regarding Normal Forms in DBMS...................................................54

Satisfying the System’s Design Interfaces User Requirements......................................74

Structure Query Language.............................................................................................76

Data Definition Language..............................................................................................77

Creating the database using DDL..................................................................................79

Develop database Fulfil the Requirements of User and System.....................................82

Improvement that can be made in the System................................................................84