Merit Databse

Database Design & Development | Bikash Dhakal
Contents
Task 2/ M1.................................................................................................................................................3
Produce a comprehensive design for a fully functional system which includes interface and output
designs, data validations and data............................................................................................................3
Introduction...........................................................................................................................................3
Interface and output designs:...............................................................................................................3
Data validation:.....................................................................................................................................6
Normalization........................................................................................................................................9
Type of normalization.........................................................................................................................9
2.2NF (Second Normal Form)...........................................................................................................11
3.3NF (Third Normal Form)..............................................................................................................12
4.BCNF (Boyce Codd Normal Form)................................................................................................14
Justification..........................................................................................................................................14
Conclusion............................................................................................................................................15
References............................................................................................................................................15
Task 5/M2................................................................................................................................................16
Implement a fully functional database system which includes system security and database
maintenance.............................................................................................................................................16
Introduction:........................................................................................................................................16
Conclusion:..........................................................................................................................................25
Task 7 / M3 Assess whether meaningful data has been extracted through the use of query tools to
produce appropriate management information....................................................................................26
Introduction.........................................................................................................................................26
SELECT............................................................................................................................................26
Select Query for different tables........................................................................................................27
Select Query for Join...........................................................................................................................38
Conclusion............................................................................................................................................42
Task 9/ M4 Assess the effectiveness of the testing, including an explanation of the choice of test data
used...........................................................................................................................................................43
Introduction.........................................................................................................................................43
Reason for Testing...............................................................................................................................43
Effectiveness of testing in Task 8........................................................................................................43
Advantages of Testing.........................................................................................................................45
P a g e 1 | 53
Disadvantages of Testing.....................................................................................................................46
Justification..........................................................................................................................................46
Conclusion............................................................................................................................................47
Task 12/ M5 Produce a technical previous and user documentation for a fully functional system,
including diagrams showing movement of data through system and flowcharts describing how the
system work.............................................................................................................................................48
Introduction.........................................................................................................................................48
Relational Diagram.............................................................................................................................48
Gannt Chat of time taken for Assignment.........................................................................................49
Flow chart of Login Table...................................................................................................................50
Flow chart Of Product table...............................................................................................................51
ER-Diagram.........................................................................................................................................51
Conclusion............................................................................................................................................53
P a g e 2 | 53
Task 2/ M1
Produce a comprehensive design for a fully functional system which includes interface and
output designs, data validations and data.
Introduction
In order to meet the requirement of the question to achieve M1 criteria, I have to produce
comprehensive design for a fully functional system which includes interface and output designs,
data validations and data normalization. So, I am going to design fully functional systems and
put the screen shots for interface and output designs as well as data validation with proper
explanation and for normalization I am going to explain about different types of normalization
and change the unnormalized table.
Interface and output designs:
P a g e 3 | 53
P a g e 4 | 53
P a g e 5 | 53
In the above screenshots, I have managed to include the interface and output design using the C#
programming. In above figure it is clearly seen that the fields in which the values are inserted is
known as the input and the values which are displaying in the grid view is known as the output. I
have given the input and output for each table based on the scenario.
Data validation:
In this task I am going to perform data validation and putting the screenshots of outputs result.
P a g e 6 | 53
P a g e 7 | 53
P a g e 8 | 53
As shown in the above screen shots in each and every form there is at least one empty field and
due to that empty field while performing any operation the validation appears as shown in above
screen shot with different popup message. In order to remove that data validation, it is necessary
to fill all the empty field. So, if we are able to fill the empty fields then the validation will get
remove automatically. Hence, the designed system is fully functional.
Normalization
Normalization also common word for database normalization is the process of organizing the
attributes and relations of relational database to reduce data redundancy and improve data
integrity.
Normalization is a systematic approach of decomposing tables to eliminate data redundancy and

undesirable characteristics like Insertion, Update and Deletion Anamolies. It is a multi-step
process that puts data into tabular form by removing duplicated data from the relation tables.
(Studytonight.com, 2018)
P a g e 9 | 53
Type of normalization
Following are the types of normalization.
1) 1NF
Table to be in first normal form i.e. 1NF
• There should be no repeating groups.
• All data value should be atomic.
• Each field should have unique name.
• Should have primary key
Table without normalization:
Custome Custome Custome Customer Customer Customer Customer Customer

r Id r name r Number1 Address2 Number2 Address3 Number3
Address
1
1 Basanta Bhojpur 984183727 kathmand 9843827689 Bharatpur 980728273
u 8 8
2 Onisha Hetauda 984523242 Bharatpur 984568347 kathmand 986582838
4 u 8
3 keshav jhapa 985364343 papla 9846828388 Kathmand 980762764
3 u
The above table is not in the normal form as the attributes are repeated with their
sub heading and we created the following table as
Customer Id Customer Name Customer Address Customer Number
1 Basanta Bhojpur 984183727
2 Onisha Hetauda 9845232424
P a g e 10 | 53
3 Keshav Jhapa 9853643433
1 Basanta Kathmandu 98438276898
2 Onisha Bharatpur 984568347
3 Keshav Papla 9846828388
1 Basanta Bharatpur 9807282738
2 Onisha Kathmandu 9865828388
3 Keshav Kathmandu 980762764
2.2NF (Second Normal Form)

It is the second step in normalizing database in which it should meet all the rules of
First Normal Form and every non-key column should fully dependent on the entire
key.
Table to be in second normal form i.e. 2NF
 It should be in 1NF normalization.
 All non-key attributes should depend on all part of the primary key.
 Each field should have a unique name.
 It should have a primary key.
Example
1 Basanta Kathmandu 98438276898

2 Onisha Bharatpur 984568347
3 Keshav Palpa 9846828388
1 Basanta Bharatpur 9807282738
2 Onisha Kathmandu 9865828388
3 Keshav Kathmandu 980762764
P a g e 11 | 53
Solution
Customer Id and Customer Name
1 Basanta 2 Onisha 3 Keshav

Customer Address and Customer Number
Kathmandu 98438276898
Bharatpur 984568347
Palpa 9846828388
Bharatpur 9807282738
Kathmandu 980762764
3.3NF (Third Normal Form)

The table which is derive from Second Normal Form in which the data does not
have transitive dependency is called Third Normal Form.
The table to be in third normal form i.e. 3NF
 It should be in second normal form.
 All non-key attributes should not be depended on any other non-key
attributes.
P a g e 12 | 53
 Each field should have a unique name

 It should have primary key
Examples

Customer Address and Customer Number
Bharatpur 984568347
Papla 9846828388
Bharatpur 9807282738
Kathmandu 980762764
Solution
1 Basanta
2 Onisha
3 Keshav
P a g e 13 | 53
4.BCNF (Boyce Codd Normal Form)

Boyce and Codd Normal Form is a higher version of the Third Normal form. This form deals
with certain type of anamoly that is not handled by 3NF. A 3NF table which does not have
multiple overlapping candidate keys is said to be in BCNF.
 Should be in 3NF normalization

 for each functional dependency (X -> Y), X should be a super Key
Example and Solution
1 Basanta
2 Onisha
3 Keshav
In above I have explained the different types of normalization with necessary examples and
tables.
Justification
As per given task I have design fully functional database system which includes interface and
output designs, data validations and data normalization with explanations. In the interface and
output design I have inserted some screen shots as per the demand of the questions. For the
interface I have created the login system consisting the user id and password as well as in the
output design I have design the form using C# programming which refers to the contain of my
database table. In the output design my main target is to show the same contain in the form
which I have design while creating the table and to show the output whenever I input the value in
the field as shown in above screen shots. Also, in the data validations I have manage to show the
users that what problem may arises while operating the database, for that I have designed the
form which refers to the contain of my table. By using the C# program, I have shown that
whenever we left the field without entering any values and try to operate with that value than
P a g e 14 | 53
there arises some problem that is shown in a certain message box format which is known as the
data validation. In the normalization I have defined the meaning of normalization with its type
also I have shown how can we change the unnormalized table into the normalized. For that I
have taken the unnormalized table with different column name the unnormalized table contain
the duplicate values in the fields. Initially I have managed the single value in each of the table
cell after that I have managed to keep the entries in column as unique. After the completion of
the first normal form, in the second normal form I have divided the single table of first normal
form into two tables. In the third normal form I have divided the table and created the new table
which is shown above. At last I also mentioned about BCNF. In this way I completed the given
task.
Conclusion
In this task we have shown Interfaces of input as well as Output of the table we have. I have also
shown series of data validation as per the requirement of my task of the table and at last I have
shown data normalization of the tables. Hence, successfully completed the task.
References
Studytonight.com. (2018). 1NF, 2NF, 3NF and BCNF in Database Normalization | Studytonight.
[online] Available at: https://www.studytonight.com/dbms/database-normalization.php
[Accessed 22 Jun. 2018].
Task 5/M2
Implement a fully functional database system which includes system security and
database maintenance.
P a g e 15 | 53
Introduction:
In this task we have to implement a fully functional database system which includes Trigger,
Functions, Views, etc. We also have to document how we applied security to our database
System from SQL injection and other Vulnerability. Also, we have to show evidence of how we
achieve the given following Operations like Login file Management, Backup and restore, Data
fragmentation, etc.
1. Stored Procedures
Group of SQL and programming commands that are stored in a database System for the
propose of sharing which is assigned by a name. Stored procedures mainly perform specific
functions. Stored Procedures is supported by relational database systems like Oracle, SQL
server, etc.
Syntax
Create Procedure Customer Table
Customer Id int
Customer Name varchar (30)
As
Begin
Select * from Customer Table
End
Requirements / Needs of Stored Procedures
1. To provide security to our database System.
P a g e 16 | 53
2. To makes us easier to maintain and manage our documentation separating data

functionality.
3. Since Stored Procedures only sent few calls to our database which helps us to improve
the performance of our database.
Advantages of Stored Procedure are:
1. It helps in the faster execution of the Program.

2. We can reuse Code which is flexibility in Stored Procedure.
3. There is more Secure of data
4. There is less network traffic during the use of Stored Procedure.
2. Trigger
The set of SQL procedure which perform an action automatically when a certain Operation is
done to our database is called Trigger. Insert, Delete and Update are the operation that are
found in trigger. When we change or modify certain data in our table of database then
Database Management System automatically fires trigger to that specific table.
Syntax
create trigger trg_I_Table_1
ON Table_1
FOR INSERT
as
insert into Table_2 (Col_1, Col_2, Col_3) select Col_1, Col_2, Col_3
from inserted
insert into Table_3 (Col_1, Col_2, Col_3) select Col_1, Col_2, Col_3
from inserted
go
Requirements / Needs of Trigger are:
1. Trigger helps us to increase the efficiency of our database as well as our application.
P a g e 17 | 53
2. Extra code is not needed in trigger as it automatically performs action within our
database.
3. It helps in maintain replicate database table.
4. It helps to make complex Security to our database.
Advantages of Trigger are:
1. It helps us to run our task alternatively.

2. It helps us to audit the changes in the tables.
3. It helps us to check our data alternatively.
3. Functions
It is a unit code of a program in a programming which performs a specific task by their code
structure. The function can also be stated as a command which can be created by ourselves so
as to perform a specific task. For example: we can use Insert command to input data on the
desired database.
Requirements / Needs of Functions are:

1. For Selection of hardware and Software.
2. For Installation and Upgrading DBMS.
3. To change the performance of Database.
Advantages of Functions are:
1. It helps us to manipulate data values of the table.

2. It helps us to increase efficiency of our database.
3. We can reuse code in our database.
4. Views
P a g e 18 | 53
It is a way by which we search a searchable object in our database by generating a query. If

we have more item in our database then there will be more number of views and if less
number of objects then there will be less views. Example
Requirements / Needs of Views are:

1. To implement Security mechanism in SQL server.
2. To fetch data from underlying tables.
Advantages of Views are:
1. It helps us to simplify complex queries in our database.

2. We can reuse and simplify our data using Views.
3. It can also be used as a Security maintenance by hiding useful information from our
database.
4. It uses less storage capacity.
P a g e 19 | 53
Security
It is the process of protecting your computer from unauthorized User, destruction of hardware
and Software data of computer system. Security is very important for our computer system as it
helps to run Computer smoothly and private data is preserved. For eg:
Here, we can see by entering proper Server Name in SQL we are able to access all the data of the
SQL table
P a g e 20 | 53
Where as in the above Screen shot we can see due to the wrong entry of Server Name I am not
allow to get access to the database and there appear an error box.
There are mainly two types of Security which are present in computer Security and they are:
1. Physical Security
It is the process of protecting mainly physical devices of computing devices like Guards,
Vaults, etc.
2. Logical Security
It is the process of protecting Computer devices from Password, Firewalls, etc.
P a g e 21 | 53
SQL Injection
The type of injection attack in Computer where the malicious SQL commands are injected in the
poorly designed or less Security database System so that the execution of pre-defined statement
is affected or the execution of unexpected commands. It is one of the common Threat in Web
application where there is lack of proper Security. SQL injection is mostly use now for stealing
Credit Card Numbers, money as well as valuable details of the People which is totally illegible.
To do SQL injection attackers make fake web pages with different offers to lure the Victims.
Example of SQL Injection
Data and log file management
Data and Log file management is the file which helps to register, process and store recent data
which was transact between two database system.
P a g e 22 | 53
Index Fragmentation
Index is fragmented when there is a modification of database. Due to the fragmentation of Index
on the database become less efficient and there is reduce in performance. When new key pointer
is to be added on the index then there occurs index fragmentation.
P a g e 23 | 53
Back Up and Restore
Back up is the copy of data which can be restore even when there is a failure in data. Back Up is
very important as it helps us to recover our valuable data even after data are delete.
P a g e 24 | 53
Conclusion:
In this task I have implemented a fully functional database system which includes Trigger,
Functions, Views, etc. I also have documented how I applied security to our database System
from SQL injection and other Vulnerability. Also, I have shown evidence of how I achieved the
given following Operations like Login file Management, Backup and restore, Data
fragmentation, etc.
P a g e 25 | 53
Task 7 / M3 Assess whether meaningful data has been extracted through the use of query
tools to produce appropriate management information.
Introduction
In order to achieve M3 we have to explain different syntax of select command in all tables of our
data base with their advantage, disadvantage and evidences and also need to use select command
for different types of joins like inner joins, left joins, right joins inserting their evidences. So I am
going to do this task as per requirement.
SELECT
SELECT is the keyword used to select data from table. It is type of retrieve query. It queries a
set of tables and return the data in that table.
Syntax:
Select * from table name
Select * from table name where clause
Select query is one of the DML query which is very important in the database. By using this
query, we can check whether we have inserted data on table or not.it helps to overcome our
dilemma on our database system. Also it works as filter in our database we can get desired data
from too many data in database it makes us faster and easier to get data from our database. We
can also place the Order details according to our desire as we can see in following Screenshot
that I have taken data according to ascending order where gundruk is at the first row of table and
green tea at the end of the table.
P a g e 26 | 53
Select Query for different tables

1.
Advantages
In above screenshot we can see all information of the product table. By using select query, we
can get all data that we have inserted on our table. In product table we can see product ID,
product Name, product price, product manufacture date and product expiry date of all product
that I have inserted.
Disadvantages
In product table if we insert lots value of product then it does not filter and show all the data
which is boring and time consuming. It shows all data that we have inserted and does not
skipping the any one data form table and show data with all mentioned attribute.
2.
P a g e 27 | 53
Advantages
In above I have executed the another command of select query. On that Query we can select
desired data from the product table where here is taken from Pro_Id which is greater than 4. We
can see only data that are greater than Pro_Id 4 is shown in the table and less than Pro_Id is not
shown.
Disadvantages
Only the data in which the Pro_Id is greater than 4 is shown rest of the other are not. As seen in
above screenshot rest of the data are not shown whose product Id is greater than 4 as we can see
Poduct Id having 2 is not shown whose Product Name is rice.
3.
Advantage
Here, we can select the data either by ascending or descending order according to our will. As
we can see in the screenshot that it is started from ascending order as Product price having 40 is
at the first place and the product price 2000 is at the end of the table.
Disadvantage
The disadvantage of using this command is that it is only focused on Product price whereas those
Product whose Product id comes first are also placed behind than whose Product id is larger.
P a g e 28 | 53
Here, we can see that Product Id of green tea is greater than that of applecider with their
respective Product Id as 5 and 3.
4.
Advantage
By using this query, we can get all information of Customer table as shown in the above
Screenshot. All the data I have inserted in this table is shown without skipping any and if we
have inserted thousands of data on it then it will show all of the data according to their respective
entity.
Disadvantage
If we insert lots of value in Customer table, then it does not filter data and show all the data that
we have inserted which is time consuming and space. If there are lots of data in Customer table,
then it will be difficult for us to look each and every data as well as it will take more space.
5.
P a g e 29 | 53
Advantage
So, in the above Screen Shot it is shown that by using that Query we can select desired data from
the Customer table where here is taken from customer Id which is greater than 1 but less than 3.
As we can see only one data is shown having name onisha pokchi it shows that it will be easy for
us to choose any data from whole table even when there are thousands of data there.
Disadvantage
Only the data in which the customer Id which is greater than 1 and less than 3 is shown rest of
the other are not. As we can see only one data is present in the screenshot whose name is onisha
pokchi and Customer Id as 2 and rest of the data are not shown.
Advantage
In above screen shot I have performed the order by command of select query.
we can see in the above screenshot that it started from ascending order whose Customer Name
Amira at the first place and Customer Name onisha pokchi at the end of the table. Despite of
their Customer Id and other identity.
Disadvantage
P a g e 30 | 53
In above screenshot we can see data which are order by their name.The disadvantage of using
this command is that it is only focused on Customer Name whereas those Customer whose
Customer Id comes first are also placed behind whose Customer Id is larger. Here, we can see
that Customer Id of keshav noks is greater than that of onisha pokchi with their respective
Customer Id as 3 and 2.
Advantage
In above screen shot we can see all data of Sale table. All the data I have inserted in this table is
shown without skipping any and if we have inserted thousands of data on it then it will show all
of the data according to their respective entity.
Disadvantage
If we insert lots of value in Sale table then it does not filter data and show all the data that we
have inserted which is time consuming and space. If there are lots of data in Sale table then it
will be difficult for us to look each and every data as well as it will take more space. So we need
to execute other command of select query as follows.
8.
P a g e 31 | 53
Advantage
By using this query, we can select the data that are only needed for us as we can see on the above
Screenshot that I have only taken Quantity from Sale table as per my need. This query helps us
to choose any column from the table.
Disadvantage
The disadvantage of using this command is that only one column is shown and rest of the
Column are not shown. In the above, Screen shot we can see only one column of Sale Table
which is of Quantity having three values as 3, 4, 5, 6, 7 and 11 respectively.
Advantage
P a g e 32 | 53
In the above Screen Shot it is shown that by using that Query we can select desired data from the
Sale table where it is taken from Sale_Id which is less than 9. We can see only three sale Id in
our Screenshot which is by using this query which makes us easier for us to take only the data
according to our need as I have only taken Sale Id 3, 4 and 5.
Disadvantage
Only the data in which the Sale_Id which is greater than 2 is shown and rest of the other data are
not. As we can see only three data that are present in the screenshot having Sale Id 3, 4 and 5
respectively and rest of the data that were present on the table before using this command is not
shown.
10.
Advantage
By using this query, we can get all data of Category table as shown in above screenshot. All the
data I have inserted in this table is shown without skipping any and if we have inserted thousands
of data on it then it will show all of the data according to their respective entity.
P a g e 33 | 53
Disadvantage
The disadvantage of this query is if we insert lots of value in Category table, then it does not
filter data and show all the data that we have inserted which is time consuming. If there are lots
of data in Category table, then it will be difficult for us to look each and every data as well as it
will take more space.
11.
Advantage
In the above Screen Shot it is shown that by using that Query we can select desired data from
the Category table where it is taken from Cat_Id which is equal to 3. So, this query helps us to
choose the data that are only needed for us like I have taken apple from Category name whose
category Id is 3 as per my query.
Disadvantage
Only the data in which the Cat_Id is equal to 3 is shown and rest of the other data are not. As we
can see only one data that is present in the screenshot having Category Id whose Category Name
is apple and rest of the data that were present on the table before using this command is not
shown.
12.
P a g e 34 | 53
Advantage
In have screen shot I have executed another command of select query. By using this query, we
can select the data that are only needed for us as we can see on the above Screenshot that I have
only taken Category type from Category table as per my need. This query helps us to choose any
column from the table.
Disadvantage
The disadvantage of using this command is that only one column is shown and rest of the entity
are not shown. In the above, Screen shot we can see only one column of Category Table which is
that of Category type having o values as drinks, fruits, rice, tea and vegetable.
13.
P a g e 35 | 53
Advantages
In above screenshot we can see the all values of invoice table which are output of select
command. By using this query, we can get all data from Invoice table as shown in the above
screenshot. All the data I have inserted in this table is shown without skipping any and if we
have inserted thousands of data on it then it will show all of the data according to their respective
entity.
Disadvantages
The disadvantages of this command is if we insert lots of data in Invoice table then it does not
filter data and show all the data that we have inserted which is time consuming. If there are lots
of data in Category table then it will be difficult for us to look each and every data as well as it
will take more space.
14.
P a g e 36 | 53
Advantages
we can see in the above screenshot that it is started from ascending order whose Invoice date is
less than that of the another with their respective Invoice date as ‘2018-05-01’ which is ahead of
Invoice date ‘2018-05-05’.
Disadvantages
The disadvantage in using this command is that it is only focus on Invoice date whereas those
Invoice Id which comes at first are also placed behind those Invoice Id having larger number. In
above screenshot coincidently there is both product id and invoice date are in ascending order.
15.
P a g e 37 | 53
Advantages
So, in the above Screen Shot it is shown that by using that Query we can select desired data
from the Invoice table where it is taken from Invoice_Id which is smaller than 4. So, this query
helps us to choose the data that are only needed for us like I have taken ‘2018-05-01’ as an
Invoice date whose Invoice Id is 1 as per my query.
Disadvantages
In above screenshot we can see data which invoice id are less than 4 and rest of the other are not.
So, in this table we can see three data which Invoice Id are 1, 2 and 3.is less than 4.
Select Query for Join

1.
P a g e 38 | 53
Advantage
In above screen shot we can see the execution of select query for inner join. By using this
command, we can return all the data that are common between left table and right table as shown
in the above Screen Shot. Here, all the common data from the Customer Table and the Product
Table is shown especially of that Customer Name and Customer Address and rest data is
Escaped.
Disadvantage
The disadvantage in using this command is that only the common data between the two table is
shown which is of Customer table and Product Table and rest of the data present in both the table
are not shown. We can see that only Customer Name and Customer Address is present while
other entity of the table is not.
2.
P a g e 39 | 53
Advantage
In above screen shot we can see the execution of select query for right join. By using this
command, we can return all the data that are matched from right table to the left table as shown
in the above Screen Shot. Here in this table it returns all the data of the catagory table whereas it
only returns the corresponding data of sale Id and agent Name and other data of the sale Id and
agent Name is Escaped.
Disadvantage
The main disadvantage of using this command is that if there is larger number of value in one
table than the other one then it will show all the value with respect with the larger value table and
takes the value as a null in the smaller value table.
3.
Advantage
P a g e 40 | 53
In above screen shot we can see the execution of select query for left join. By using this
command, we can return all the data that are matched from left table to the right table as shown
in the above Screen Shot. Here, I have select Product Id and Product Name from Product table
and also Category Id from category table.
Disadvantage
4.
Advantage
In above screen shot we can see the execution of select query for inner join. By using this
command, we can return all the data from left table as well as from right table as shown in the
above Screen Shot. Here we can see all the data from the invoice date and invoice name as well
as from the product Id from two different table invoice and the product.
P a g e 41 | 53
Disadvantage
Conclusion
DML command is very important and useful command for database create and management.
SELECT query is one of most usable in database system. It makes us very easy to monetarizing
the database system. So, in this task I have explained about some select query. The conclusion of
this task is that we have to use different types of syntax of select command and for different
types of joins using two tables by which we get a required result for our task.
P a g e 42 | 53
Task 9/ M4 Assess the effectiveness of the testing, including an explanation of the choice of
test data used.
Introduction
In order to achieve the M4 criteria on this the task, I have to explain effectiveness of testing,
including the choice of test data used. Here, I have to relate this task with task 8 where we have
done different types of testing like unit test, stress test and integration test. The tables that I have
create like invoice, sale, Category, Customer, Product, etc. and tested them so that I could find
their limitations as well as functions which will help me to make effective database for JAGUAR
DEPARTEMENTAL STORE. So, it is very necessary for each and every person to test a
database project for better and effective software developer. On basis of my testing results here, I
am going explain more about database as follows:
Reason for Testing

Testing is very important in database system. After creating or developing database system it is
necessary to do testing of every functional unit of our system to conform whether our system is
fully functional or not. In Database there is an association between each and every data from
different tables of database. So, if there is a loss of any data or default in table of database then it
will affect each and every table associated with it. If we do testing of each and every table, then
it will be easy for us to solve the problems as well as to know the limitation as well as function
of them. We can find the problem easily and can solve them quickly so that it will help us to
manage our time properly. Testing also helps us to sort each and every data in its own column
rather than other which will be effective in making tables. So, testing is necessary in database
system.
Effectiveness of testing in Task 8
Database testing is the testing in which user exams or test the existing facts along with tables,
strategies, query, data duplication etc. Testing is executed with the aid user it can be achieved in
both web software or computing device in database software program like SQL or oracle. Many
P a g e 43 | 53
initiatives call for database nowadays which include banking, schools and colleges, business and
so on. Within the testing technique many stuffs are checked including double data, if there's
duplicate data or not, if there may be key errors or now not.
Testing is very important for each and every especially to those who are developing databases
for a Software Company. So, I also have created a database for a Jaguar departmental store and
tested the tables of database which was quite helpful for me. Some of the tested table are as
follows:
1. category table.
In above screenshot I have shown the testing of category table. In this screen shot I have tested
stress testing for stress test I put the value ‘mustangi’ in the field of product id and I have got
error message for this duet to unmatched data type. In product id I should be integer data type
but I have written the value ‘mustangi’ which is in varchar data type. So, I got error message and
I can conclude that test is successful.
P a g e 44 | 53
For the category table I have also tested the unit testing which is shown in below:
In above screenshot I have performed the unit test for category table. For Unit test I did not put
the value of Pro_Id or left it blank to test whether it will be correct or not so it showed me an
error messaging me that it was not valid from this I knew that we should not left the column
blank in the table we should put the value whether it is integer or varchar according to the
situation of the Column which shows that it is very effective for every person to check their
database they have created about its limitation and functions. Hence from these testing I can
conclude that my database system is fully functional.
Advantages of Testing
Data which is one of the most important element of a Company should be tested show that error
can be detected. Behind error detection there are many advantages or significances of Testing
and they are as follows:
 It is able to check capability which can be controlled by way of values in database.

 Beneath optimized database design leads to fundamental capability failure.
 It checks data validation and duplication.
P a g e 45 | 53
 Incorrect or inefficient or badly written SQL Queries can also lead functionality failure.
 It guarantees that no data is lost inside the manner.
 It helps to reduce the size of a code which makes us easier.
 Extra lines during the process of coding can also be removed so that the number of error
created by the extra lines can be detected.
 Even after change in user interface different version of application can also be reused.
Disadvantages of Testing
Having a number of Advantage, testing has also some of the disadvantages and they are as
follows:
 Some of the testing should be tested by highly qualified person which increases the cost
of testing.
 Code analyzers and debugging tools which are special is needed during the testing which
is difficult for normal people.
 It is difficult to test each and every code and lots of codes remain untested.
Justification
Database testing is the testing in which user exams or test the existing facts along with tables,
strategies, query, data duplication etc. we have three types of testing unit test, stress test and
integration test. In unit test, testing is done in individual basis. Testing of an individual software
component or module is termed as Unit Testing. Stress testing is done to identify the system
breakpoint. It stresses the application or software under test with many loads. This type of testing
gives a lot load to the system and in integration test one-of-a-kind operation on data is examined
to ensure that the data and information are inserted properly with accurate constraints in order
that it gives accurate results. This type of testing out is particularly relevant to client/server and
distributed systems.
In this task, I have done theses type of testing Firstly, in Table and Query Testing I have provide
a test date, what are the thing that I have Tested in each and every table of my database. For
Example: In the Customer Table of my database I had tested Customer Id, Customer Name,
Customer Address Customer Number and customer email as they have certain limitations as
P a g e 46 | 53
Customer Id, Customer Name, Customer Address Customer Number and customer email
should be according to the command that we have inserted during the process of creating
database and if the value in that table are not inserted according to the command then it will
show an error which I have tested them and the result of each and every table is per my
expectation as I have insert the data on these table according to the command. In the second table
of this Task I have done three types of testing in each and every table with their entity which are
Unit and Stress testing. In this testing table I have given a topic on what types of testing I am
going to do. For Example: For the Unit testing I have done test of Product Price in which I have
not Provided any Input Value and checked whether it shows error or not and as per my
expectation there shows and error showing that I have not input any data there in the box. Now, I
have to input value in that entity which is a successful test as it shows that we have to provide
value in all entity of the table. The second testing that I have done in the Product Table is
integration testing which test the data type of the input data. For the integration test I have done
test of Product Manufacture date which is of rice Product where I have Input data ‘jghhfhj’ to
check which is in string(varchar) data type. As per the expected, it showed and error in the box
that the data in row 2 is not committed which shows that the test for the integration was
successful. We have to do testing for other tables to like Customer Table, Invoice table, Category
Table and Sale table in which we also have to do unit and stress testing with one entity in those
table and what are the what you want to test in that table with their error to know that the testing
has been successful in each table. In this way I have done all three test for all table, by this I can
conclude that the data base system I have developed was fully functional.
Conclusion
In this task I have explained all about database testing with their introduction, significance,
disadvantages and reasons to test. I also have given well explain justification with example
referring from task 8. Hence, in short conclusion I have successfully completed this task.
P a g e 47 | 53
Task 12/ M5 Produce a technical previous and user documentation for a fully functional
system, including diagrams showing movement of data through system and flowcharts
describing how the system work.
Introduction
This is a continuation of task 11 where we have to draw flow chart of the one or two tables,
Relational Diagram as well as ER-Diagram of the tables. Gantt Chart is also required to show
how you manage your time in doing Assignment.
Relational Diagram
In the above Relationship Diagram, I have created Six Table with their attributes each as per the
requirement of the task. Here, in the Customer table I have given a primary key to the Customer
Id with the data type integer and Customer Name, Customer Address, Customer Number with
data type Varchar (20), Varchar (20), Integer respectively. Similarly, in the product Table
Product Id is a primary key and Product Name, Product Price and Product manufacture are the
entity of that table. Product Id which is a Primary key provides value to the foreign key which is
in Category as well as in the sale table. In the same way Invoice table has a entity of Invoice Id,
P a g e 48 | 53
Invoice date, Customer Id and Invoice Number in which Invoice id is a primary key and
Customer Id is a foreign key as it retrieve data from Customer Id of Customer table. Category
table which has a entity of Category Id, Category Name, Product Id and Description in which
Category Id is a Primary key and Product Id is a foreign key as it retrieve data from Product Id of
Product table. At last, we have a Sell table in which Sale Id is a Primary key and other entities
are Agent Id, Product Id which is a foreign key which retrieve data from Product of product table
and Quantity.
Gannt Chat of time taken for Assignment
This is a Gannt Chart where I have described about how I finished my Assignment with in a
time. The Assignment was nearly for 1 month so I planned to finish my Assignment within a
time. First of all I made a list and here we go so first task that I have to do was to analysis each
and every task so it will be very easy for me to do which took me about 7 days. After that It took
me about 8 days to know what are the sources that will help me to do my task. After that it took
me about 7 days to do my task which is the most important. After implementing I checked all the
topics and list out the missing one which took me 8 days as I did with less care and lastly, I
Deployed it to my teacher.
P a g e 49 | 53
Flow chart of Login Table
In this flow chart I have shown how it works. So first of all we should start then I have entered
Login Table and if the data match according to my command then this will operate database and
gives us result which is a ending of the program and if the data do not match then it will end not
performing any task.
P a g e 50 | 53
Flow chart Of Product table
In this flow chart of the product table first of all we have to use command start then required
database name and if name exist in that part then it will store the data and ultimately end the
program but if the name don’t exist then it will not store a data saying not a user then this process
continues until it will find a user and after finding the User it will end the program performing
Operation.
ER-Diagram
P a g e 51 | 53
In the above Entity Relationship Diagram there are four tables with their attributes each.
Customer Id, Invoice Id, Sale Id and Product Id are the Primary key. Customer and Product table
is connected by Sale. Customer and Product has one to many relations as One Customer can buy
many products. Similarly, Invoice Product which connects Invoice and Product as Invoice can
sell more than one Products. Sale and Product Table is connected by Buys as many products can
be sell by an Invoice. At last, we have Invoice and sale Table which is connected by writes as
one Invoice can write about many Sale.
P a g e 52 | 53
Conclusion
The conclusion of this task is that I have described as well as Paste Screen shot of ER-Diagram,
Flow Chart, Gannt Chart and Relational Diagram as per the requirements of the task.
P a g e 53 | 53

Merit Databse

Uploaded by

Document Informationclick to expand document information

Copyright:

Available Formats

Merit Databse

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

Merit Databse

Uploaded by

Copyright:

Available Formats

Database Design & Development | Bikash Dhakal

Interface and output designs:

Normalization is a systematic approach of decomposing tables to eliminate data redundancy and

Table to be in first normal form i.e. 1NF

• There should be no repeating groups.

• All data value should be atomic.

• Each field should have unique name.

• Should have primary key

Table without normalization:

Custome Custome Custome Customer Customer Customer Customer Customer

Customer Id Customer Name Customer Address Customer Number

1 Basanta Bhojpur 984183727

2 Onisha Hetauda 9845232424

3 Keshav Jhapa 9853643433

1 Basanta Kathmandu 98438276898

2 Onisha Bharatpur 984568347

3 Keshav Papla 9846828388

1 Basanta Bharatpur 9807282738

2 Onisha Kathmandu 9865828388

3 Keshav Kathmandu 980762764

2.2NF (Second Normal Form)

1 Basanta Kathmandu 98438276898

Customer Id and Customer Name

1 Basanta 2 Onisha 3 Keshav

Customer Address and Customer Number

3.3NF (Third Normal Form)

 Each field should have a unique name

Customer Id and Customer Name

1 Basanta 2 Onisha 3 Keshav

Customer Address and Customer Number

Customer Id and Customer Name

4.BCNF (Boyce Codd Normal Form)

 Should be in 3NF normalization

Example and Solution

Customer Id and Customer Name

Create Procedure Customer Table

Customer Name varchar (30)

Select * from Customer Table

Requirements / Needs of Stored Procedures

1. To provide security to our database System.

2. To makes us easier to maintain and manage our documentation separating data

Advantages of Stored Procedure are:

1. It helps in the faster execution of the Program.

Requirements / Needs of Trigger are:

Advantages of Trigger are:

1. It helps us to run our task alternatively.

Requirements / Needs of Functions are:

Advantages of Functions are:

1. It helps us to manipulate data values of the table.

It is a way by which we search a searchable object in our database by generating a query. If

Requirements / Needs of Views are:

Advantages of Views are:

1. It helps us to simplify complex queries in our database.

Example of SQL Injection

Data and log file management

Back Up and Restore

Select Query for different tables

Select Query for Join