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

12
DATABASE DEVELOPMENT AND MANAGEMENT
Data Dictionary
Sales Table
Attribute Data Type Size Null Validation Rule
SaleID Number - - -
QuotationID Number - - -
ProductID Number - - -
ContractorID Number - - -
Price Number - - > = 0
Contractor
Attribute Data Type Size Null Validation
Rule
ContractorID Number - - -
ContractorName Text 255 - -
ContracatorContact Number - - -
Quotation
Attribute Data Type Size Null Validation Rule

13
DATABASE DEVELOPMENT AND MANAGEMENT
QuotationID Number - - -
QuotationType Text 255 - -
EmployeeID Number - - -
ContractorID Number - - -
Quote Number - - >= 0
Manufacturer
Attribute Data Type Size Null Validation
Rule
ManufacturerID Number - - -
ManufacturingName Text 255 - -
Product
Attribute Data Type Size Null Validation Rule
ProductID Number - - -
ProductType Text 255 - “Door” and
“Window”
ManufactuerID Number - - -
ProductCost Number - - >= 0
Quotation
Attribute Data Type Size Null Validation Rule
EmployeeID Number - - -

14
Product form
Product ID
Product Type
Save Exit
DATABASE DEVELOPMENT AND MANAGEMENT
EmployeeName Text 255 - -
EmployeeType Text 255 - “Sales Agent”
Or “Staff”
Salary Number - - -
Interface Design
Product Interface
Contractor Interface
Manufacture ID
Manufacture ID

15
Contractor form
Contractor ID
ID
Contractor Name
Save Exit
Contractor
Contact
DATABASE DEVELOPMENT AND MANAGEMENT
Report Design
Profit Report
Profit
Auto_Dat
e
Auto_Tim
e
ContractorNa
me
EmployeeNa
me
ManufacturerNa
me
ProductCo
st
Pric
e
Profi
t
Text3
0
02
February
2019
12:42:31 David James David Benioff Golden Furnitures 19 25 6 Page
1 of 1
02
February
2019
12:42:31 Kevin Friend David Benioff Highend
Furnitures
19 30 11 Page
1 of 1
Task 2
Validation
Text Validation

16
DATABASE DEVELOPMENT AND MANAGEMENT

17
DATABASE DEVELOPMENT AND MANAGEMENT
Number Validation

18
DATABASE DEVELOPMENT AND MANAGEMENT
SQL queries
Select Queries
Query 1

19
DATABASE DEVELOPMENT AND MANAGEMENT
SELECT Contractor.*
FROM Contractor;
Query 2
SELECT Employee.*
FROM Employee;
Insert Query
Insert into Manufacturer Values(7,'TRX Producers');
Delete Query
DELETE FROM Manufacturer Where ManufacturerID = 7;

20
DATABASE DEVELOPMENT AND MANAGEMENT
Select Query with multiple tables
Query 1
SELECT Contractor.ContractorName, Employee.EmployeeName,
Manufacturer.ManufacturerName, Product.ProductCost, Sale.Price, [Sale.Price]-
[Product.ProductCost] AS Profit
FROM (Manufacturer INNER JOIN Product ON Manufacturer.ManufacturerID =
Product.ManufacturerID) INNER JOIN ((Employee INNER JOIN (Contractor INNER JOIN
Quotation ON Contractor.ContractorID = Quotation.ContractorID) ON
Employee.EmployeeID = Quotation.EmployeeID) INNER JOIN Sale ON
(Quotation.QuotationID = Sale.QuotationID) AND (Contractor.ContractorID =
Sale.ContractorID)) ON Product.ProductID = Sale.ProductID;
Query 2
SELECT Employee.EmployeeName, Quotation.Quote, Sale.Price, Sale.SaleID
FROM (Employee INNER JOIN Quotation ON Employee.EmployeeID =
Quotation.EmployeeID) INNER JOIN Sale ON Quotation.QuotationID = Sale.QuotationID;

21
DATABASE DEVELOPMENT AND MANAGEMENT
Task 3
Test Plan
Test plan helps to calculate the attempts that are to be made in order to approve the
application. Test plan can help the team members excluding the testers to follow the details of
the testing. Using the test plan guidance can be provided toward the work. The test plan
process is to be followed from the starting and till the end of the testing and development
process. Schema, columns, triggers, database server validation, keys and indexes validating
data duplication, database tables, stored procedures are include in the database test plans
(Nicolas and Katerina, 2015). Structural testing, functional testing and non-functional testing
are the three types of database test plan.
Validation testing in structural data is done basing on all the elements that are present
inside the data repository which is used for storing the data (Stonebraker, Deng and Brodie
2017). The storage cannot be modified by the user as the user is not authorized to make any
changes to the storage.
Test
Case
Testing
procedure
and
purpose of
test
Expected
result
Actual
Result
Required
Action
Test case
Author
Re-
Test
no
1 Insertion The data is The table Saving the Administrator 0

22
DATABASE DEVELOPMENT AND MANAGEMENT
testing by
inserting
data in the
table
entered in
the table.
in the
database is
updated
with new
data.
table
2 Insertion
testing by
inserting
data in the
table
The data is
entered in
the table.
Cannot
add record
to the
database
as the
primary
key
constraint
is violated.
Changing
the unique
field data.
Administrator 1
3 Insertion
testing by
inserting
data into the
table
The data is
entered in
the table.
The data
cannot be
inserted
into the
table as
there are
not data in
the
reference
table.
Changing
the unique
field data.
Administrator 1
4 Deletion The data is The table Saving the Administrator 0

23
DATABASE DEVELOPMENT AND MANAGEMENT
testing by
deleting
data
deleted
from the
table
is updated
with the
data being
deleted
from the
table
table
5 Deletion
testing by
deleting
data
The data is
deleted
from the
table
Cannot
delete data
from the
table due
to foreign
key
constraint
Deleting
the data
from the
reference
table at first
Administrator 1
Database Testing
Insertion testing
Deletion Testing

24
DATABASE DEVELOPMENT AND MANAGEMENT
Task 4
Plans for Improvement in future
This is important to manage the database properly in order to improve the system in
future the performance needs to be maintained properly. The performance needs to be
improved with time so that the database can be managed properly. The improvement plan for
the system as follows:
 Creating primary key in the form of clustered index for every table. This is important
for the user to set up the index properly by maintaining the foreign key.
 Once the SQL is references it is the responsibility of owner to qualify them.
 SET NOCOUNT ON top of every procedure and SET NOCOUNT OFF at the bottom
of every procedure will help to manage the programs.
 It is important for every developers and database administrator to work together in
order to create a relational diagram.
 Developers and database administrators should ensure the procedures as per the
migration date and should be ready to transfer all the data to new software.

25
DATABASE DEVELOPMENT AND MANAGEMENT
Flow Chart

26
DATABASE DEVELOPMENT AND MANAGEMENT
Bibliography
Coronel, C. and Morris, S., 2016. Database systems: design, implementation, & management.
Cengage Learning.
El-Halees, A.M. and Kehail, E.O., 2017. Integrate Database Design Techniques with Agile
Applications.
Fowler, M., 2018. Refactoring: improving the design of existing code. Addison-Wesley
Professional.
Hung, N.V., van Hung, P. and Anh, B.T., 2018. Database Design For E-Governance
Applications: A Framework For The Management Information Systems Of The Vietnam
Commitee For Ethnic Minority Affairs (CEMA). International Journal of Civil Service
Reform and Practice, 3(1).
Mallikarjun Sharada, S., Bligaard, T., Luntz, A.C., Kroes, G.J. and Nørskov, J.K., 2017.
SBH10: A Benchmark Database of Barrier Heights on Transition Metal Surfaces. The
Journal of Physical Chemistry C, 121(36), pp.19807-19815.
Nicolaos, P. and Katerina, T., 2015. Simple-talking database development: Let the end-user
design a relational schema by using simple words. Computers in Human Behavior, 48,
pp.273-289.
Stonebraker, M., Deng, D. and Brodie, M.L., 2017. Application-Database Co-Evolution: A
New Design and Development Paradigm. New England Database Day, pp.1-3.
Zoroufchian, M.P., Li, A., Wiggin, S.B., Tao, A., Maloney, A.G., Wood, P.A., Ward, S.C.
and Fairen-Jimenez, D., 2017. Development of a Cambridge Structural Database Subset: A
Collection of Metal-Organic Frameworks for Past, Present, and Future.

27
DATABASE DEVELOPMENT AND MANAGEMENT