BTEC HNC Computing Unit 4: Database Design and Development Report

This report presents a comprehensive analysis of database development and management, focusing on the requirements and design of a relational database system for the Double Pane organization. The report begins with interview transcripts to gather user needs, followed by detailed user and system requirements specifications. It then covers the design phase, including context and level 1 data flow diagrams (DFDs), an entity-relationship model (ERM), and normalization of database tables to ensure data integrity. The report includes a data dictionary with validations and interface designs for user interaction, as well as report designs for data presentation. The implementation phase demonstrates SQL queries for data manipulation, including insert, delete, and select statements. Furthermore, it presents a test plan and database testing procedures to validate the system against user and system requirements. Finally, the report outlines plans for future improvements and concludes with a bibliography of cited sources.

Running head: DATABASE DEVELOPMENT AND MANAGEMENT
Database Development and Management
Name of the Student:
Name of the University:
Author Note

1
DATABASE DEVELOPMENT AND MANAGEMENT
Table of Contents
Task 1.........................................................................................................................................2
Interview Transcripts.............................................................................................................2
User requirements..................................................................................................................3
System Requirements.............................................................................................................3
Context Level DFD................................................................................................................5
Level 1 DFD...........................................................................................................................5
ERM.......................................................................................................................................6
Normalization.........................................................................................................................6
ER Diagram............................................................................................................................8
Data Dictionary......................................................................................................................8
Interface Design...................................................................................................................10
Report Design.......................................................................................................................11
Task 2.......................................................................................................................................12
Validation.............................................................................................................................12
SQL queries..........................................................................................................................15
Insert Query......................................................................................................................15
Delete Query....................................................................................................................15
Select Query.....................................................................................................................16
Task 3.......................................................................................................................................16
Test Plan...............................................................................................................................16
Database Testing..................................................................................................................18

2
DATABASE DEVELOPMENT AND MANAGEMENT
Task 4.......................................................................................................................................19
Plans for Improvement in future..........................................................................................19
Flow Chart............................................................................................................................20
Bibliography.............................................................................................................................21

3
DATABASE DEVELOPMENT AND MANAGEMENT
Task 1
Interview Transcripts
In order to gather the information regarding the system it becomes important to
interview the CEO of the company. The Double Pane organization needs to gather proper
information for developing the system properly. As a database developed I was approached
by the company for developing their database. The way transcript interview was conducted
for the CEO is provided below:
 How is the business in the organization managed currently?
Double Pane needs to manage their business based on the date with the help of computerised
system that was developed by a college student.
 What are the faults in the current system?
The current system is too slow and there is no proper system documentation. Thus it
becomes difficult to mad changes within the system. The business requirement related to the
organization has changed with the time and it becomes important to invest on new
technology. This will help in developing a new system that will help to grow with the
management of this organization.
 Explain the current business process in the organization.
Double Pane is running company office with the use of marketing materials. The sales
are generated mainly by the agents those are visited by building contractors. With the every
visit taking place within the system actual sale can be identified properly. The organization
aims at delivering huge range of windows and doors. The organization aims at buying the
products in a bulk amount from large manufactures and stores the product in warehouse.

4
DATABASE DEVELOPMENT AND MANAGEMENT
User requirements
Database requirements are needed to be cleared before developing the database. Thus
the requirements can be addressed only after the organization has addressed all the
requirements clearly. It becomes important to specify the requirements properly that will help
to understand the need of the business and what the users wants from system. User
requirements specification will help in understanding the need of the system (Coronel and
Morris 2016). The requirements related to the system are validated at early stages while
creating the system. The requirements associate with the systems are represented by owners
and users after getting proper input regarding the quality assurance. The requirements
associated with the systems are tested once it is included in URS that comes under user
acceptance training. Proper analysis of the user requirement helps in planning the document
properly and are created with the aim of acquiring a system with proper needs. With the aim
of having a proper complex system it becomes important to combine the functional
requirements with the user requirements (El-Halees and Kehail 2017). The working of the
information system and the details stored within this.
 The database created should have the ability to store details related to the contractors
that are identified by unique ID.
 The database should be able to store the details of the Products in the system that
would be purchased.
 The database holds the details regarding sales agents.
 The database should be able to store the details of the Quotation.

5
DATABASE DEVELOPMENT AND MANAGEMENT
System Requirements
Network Requirements
The organization should focus on both functional and non-functional reports equally.
This will help in addressing the requirements related to the system clearly. Double Pane
organisation needs to validate all the requirements related to the system in order to develop a
proper database. This is a situation where the customer can get access over the given
resources. That is if the database is available than all the data started within the database can
be accessed by the customers at any time.
Software Requirements
The main functional requirements associated with the system that are needed to be
developed properly for facilitating the proper working of Double Pane organization are as
follows:
 Data Management: This is important for the system to provide proper staffs that will
be able to manage the data efficiently so that the organization can work efficiently. In
addition to this, they should have the ability to monitor the data and perform proper
data management functions that will help in updating sales related to the data and
deleting the customer related to information (Hung and Hung 2018).
 Reports: The system should have the capability of handling the data and analysing it
on correct time. The system should be able to provide dataset such as profit reports for
a particular product.
Hardware Requirements
 Scalability: This is the ability of a system to handle the work pressure at the time of
increased demand and increase in work amount. The potential will increase with time
and with the demand of market. With proper scalability towards the database

6
DATABASE DEVELOPMENT AND MANAGEMENT
scalability will help in scaling up the database and allows increase in amounts of data
that is stored within the system without compromising the performance of the system.
 User interface: user interface allows the database system to input according to the
need of the system. Thus, it is also known as user interface. This will ensure that the
interface facilitates proper list that can be used further for managing the database
throughout the system (Mallikarjun et al. 2017).
Security Requirements
The important property that is needed to be maintained properly throughout the
database includes proper security within the details of database. This will help the database to
protect the information properly while working with the system. Proper software use will
help in protecting the database from hackers and viruses.
Context Level DFD

7
DATABASE DEVELOPMENT AND MANAGEMENT
Level 1 DFD
ERM
Normalization
In order to perform normalization it becomes important to choose proper tables. The
six tables chosen for performing normalization are as follows Production, Sales,
Manufacturer, Contractor, Employee and Quotation. Justification along with normalization
are described below:

8
DATABASE DEVELOPMENT AND MANAGEMENT
Sale (SaleID (PK), QuotationID, ProductIDD, ContractorID, Price)
 The Sale table is generated for the purpose storing the details regarding product. In
this table SaleID is a primary key that helps in identifying the table uniquely. From
the table it can be stated that there is no proper unique attribute thus it can be stated
that the relation is in 1NF.
 In the given table the SaleID is referred to as the primary key and provides unique
features that will help in distinguishing the table. In this table this is the single
attribute that aims at providing functional dependencies towards the table. Thus it can
be stated that the relation is in the 2NF.
 Apart from this there are no more tables that are providing functional dependencies
and thus it can be said that there is no transitive dependencies present. Thus from the
above representation it can be stated that the relation is in 3NF.
Quotation (QuotationID (PK), QuotationType, EmployeeID, ContractorID, Quote)
 Quotation table contains a primary key that is QuotationID and this are used for the
purpose of defining the related attributes in table. This is a unique value and cannot be
duplicated. Thus this is relation holds the property of 1NF.
 QuotationID is the primary key that is the only one candidate key in the table with
unique features. The only candidate key in the table with unique features that provides
functional dependencies towards the table, and thus said to be in 2NF form.
 There are no field with functional dependencies thus the relation is said to be in 3NF.
Employee (EmployeeID (PK), EmployeeName, EmployeeType, Salary)
 EmployeeID is used with the purpose of defining attributes that are stored within the
table and this is the only primary key. However this does not allows the table to
implement any duplicity. Hence this is 1NF.

9
DATABASE DEVELOPMENT AND MANAGEMENT
 The primary key is the only candidate key and this provides functional dependencies
in table. The relation is in 2NF.
 There are no other table that provides functional dependencies and hence it can be
said to be third normal form.
Manufacturer (ManufacturerID (PK), ManufacturerName)
 In this table, ManufacturerID is the only primary key that possess the ability to
uniquely identify the tables. Hence it is said that the table is in 1NF.
 The primary key in the table is the only candidate key and provides unique features
towards the table. This is the single attribute providing the functional dependencies
towards the table. Hence it is in 2NF.
 No other field in table offers functional dependencies and hence it is said to be in
3NF.
Product (ProductID (PK), ProductType, ManufacturerID, ProductCost)
 ProductID is the primary key that uniquely identifies in the table and does not allows
duplicate values in the table and hence it is said to be in 1NF.
 Primary key ProductID is the candidate key that helps in providing unique feature that
will help in providing functional dependencies. Hence it is in 2NF.
 There are no other fields in the table and hence the relation is in 3NF.
Contractor (ContractorID (PK), ContractorName, ContractorContact)
 ContractorID is the primary key in the table contractor that is used to uniquely
identify the tables and does not allows the user to duplicate the values within the table
and hence it is said to be in 1NF.

10
DATABASE DEVELOPMENT AND MANAGEMENT
 Primary key ContractorID is the only candidate key that will provide unique features
towards as single attribute that helps in providing proper functional dependencies.
Hence it is in 2NF.
 However there are no fields in the table and hence the relation is in 3NF.
ER Diagram
Database Tables:
Sale Table
Contractor

11
DATABASE DEVELOPMENT AND MANAGEMENT
Quotation
Employee
Manufacturer
Product