Database Design, Implementation, and Testing: BTEC HND, 1st Semester

This document presents a complete database assignment for a BTEC HND Computing student, covering all aspects of relational database design and implementation. The assignment begins with the design of a relational database system using appropriate tools and techniques, including Entity-Relationship Diagrams (ERD), relational diagrams, and data dictionaries. It then moves on to the comprehensive design of a fully functional system, including interface and output design, data validation, and data normalization. The assignment progresses to the development of the database system, with evidence of user interface, output, data validations, and querying across multiple tables using joins. Further tasks include implementing system security and database maintenance, including password policies, stored procedures, triggers, functions, and views. The assignment also covers implementing a query language, assessing data extraction through query tools, and testing the system against user and system requirements, including detailed test logs. Finally, the project concludes with the production of technical and user documentation, including diagrams, flowcharts, and use case diagrams, assessing the effectiveness of the testing and the overall system design and functionality. The project uses SQL Server for the backend and Visual Studio with C# for the frontend and desktop application. This assignment provides a thorough example for database design, implementation, and documentation.

1
Database 2018
Database
Assignment
Submitted by: Sagar Timalsina,
BTEC HND Computing,
1st Semester.
Submitted to: Mr. Umesh Acharya
Tinkune, Kathmandu, Nepal
Contents
Task [1/P 1]............................................................................................................................................7
Sagar Timalsina (HND / First Semester)

2
Database 2018
Design a relational database system using an appropriate design tools and techniques, containing at
least four tables, with clear statements of user and system requirements. Introduction:.......................7
Choice of tools....................................................................................................................................7
Entity Relational Diagram (ERD)..........................................................................................................7
Relational Diagram................................................................................................................................9
Data Dictionary....................................................................................................................................10
User Requirement.................................................................................................................................12
Conclusion............................................................................................................................................13
Task [2/M 1].........................................................................................................................................13
Produce a comprehensive design for a fully functional system which includes interface and output
design, data validation and data normalization....................................................................................13
Introduction..........................................................................................................................................13
Choice of tools..................................................................................................................................13
Implement fully functional system.......................................................................................................13
Data Normalization..............................................................................................................................15
Types to Normalization....................................................................................................................15
Existing Method – (excel file) vs database normal form.................................................................15
Database Normal form.........................................................................................................................15
Data Validation....................................................................................................................................17
Client-side validation........................................................................................................................17
Server-side and database validation.................................................................................................18
Conclusion............................................................................................................................................18
Task [4/P 2]..........................................................................................................................................18
Develop the database system with evidence of user interface, output and data validations, and
querying across multiple tables............................................................................................................18
Introduction..........................................................................................................................................18
Implement Constraints.........................................................................................................................19
Evidence of user interface and data validation....................................................................................21
Sagar Timalsina (HND / First Semester)

3
Database 2018
Validate data querying multiple tables using joins..............................................................................24
Conclusion............................................................................................................................................26
Task [5/M 2].........................................................................................................................................26
Implement a fully functional database system which includes system security and database
maintenance..........................................................................................................................................26
Introduction..........................................................................................................................................26
System Security....................................................................................................................................26
Password Policy................................................................................................................................26
Store procedure....................................................................................................................................27
Trigger..................................................................................................................................................28
Functions..............................................................................................................................................29
Views....................................................................................................................................................30
Database Maintenance.........................................................................................................................30
Log file and data file management...................................................................................................31
Corruption Detection........................................................................................................................31
Index Fragmentation.........................................................................................................................31
Backup Plan......................................................................................................................................32
Types of Backup...............................................................................................................................32
Step9: Click on next.........................................................................................................................34
Step10: Click On finish....................................................................................................................34
Restore Plan.........................................................................................................................................35
Restore back up file in System.........................................................................................................35
Conclusion............................................................................................................................................36
Task [6/P 3]..........................................................................................................................................36
Implement a query language into the relational database system........................................................36
Introduction:.........................................................................................................................................36
Insert Statement....................................................................................................................................36
Sagar Timalsina (HND / First Semester)

4
Database 2018
Update Statement.................................................................................................................................42
Delete Statement..................................................................................................................................45
Select Statement...................................................................................................................................48
Conclusion............................................................................................................................................51
Task [7/M 3].........................................................................................................................................51
Assess whether meaningful data has been extracted through the use of query tools to produce
appropriate management information..................................................................................................51
Introduction..........................................................................................................................................51
Product wise stock reports...................................................................................................................51
Without Filter Parameter..................................................................................................................51
Using Filter Parameter......................................................................................................................52
Periodical Sales reports........................................................................................................................52
Without Filter Parameter..................................................................................................................52
Using Filter Parameter......................................................................................................................53
Product wise margin reports.................................................................................................................53
Without Filter Parameter..................................................................................................................53
Using Filter Parameter......................................................................................................................54
Supplier wise purchase details.............................................................................................................54
Without Filter Parameter..................................................................................................................54
Using Filter Parameter......................................................................................................................55
Conclusion............................................................................................................................................55
Task [8/P 4]..........................................................................................................................................56
Test the system against user and system requirements........................................................................56
Introduction..........................................................................................................................................56
Testing..................................................................................................................................................56
Types of testing................................................................................................................................56
Unit Test Log.......................................................................................................................................57
Sagar Timalsina (HND / First Semester)

5
Database 2018
Integration Test Log.............................................................................................................................58
Conclusion............................................................................................................................................62
Task [9/M 4].........................................................................................................................................63
Assess the effectiveness of the testing, including an explanation of the choice of test data used.......63
Introduction..........................................................................................................................................63
Testing..................................................................................................................................................63
Importance of testing............................................................................................................................63
Approach of testing..............................................................................................................................63
List of expected result and actual result...............................................................................................64
Login module....................................................................................................................................64
Product module.................................................................................................................................66
Purchase Module..............................................................................................................................68
Sales Module....................................................................................................................................71
Supplier Module...............................................................................................................................74
Customer Module.............................................................................................................................76
Conclusion............................................................................................................................................78
Task [11/P 5]........................................................................................................................................79
Produce technical and user documentation..........................................................................................79
Introduction..........................................................................................................................................79
Important Features of Application.......................................................................................................79
Significance of Application..................................................................................................................79
Limitation of Application.....................................................................................................................79
Choice of tools.....................................................................................................................................80
Technical manual.................................................................................................................................80
User validation module.....................................................................................................................80
Product module.................................................................................................................................81
User manual.........................................................................................................................................82
Sagar Timalsina (HND / First Semester)

6
Database 2018
Step guide to use application............................................................................................................82
Conclusion............................................................................................................................................87
Task [12/M 5].......................................................................................................................................88
Produce a technical and user documentation for a fully functional system, including diagrams
showing movement of data through system and flowcharts describing how the system work...........88
Introduction..........................................................................................................................................88
Important Features of Application.......................................................................................................88
Significance of Application..................................................................................................................88
Limitation of Application.....................................................................................................................89
Choice of tools.....................................................................................................................................89
Layout of Designed Application..........................................................................................................89
Data Dictionary use in Application......................................................................................................90
Flow chart of Application....................................................................................................................91
Use Case Diagram for Application......................................................................................................95
Context or 0-level Diagram for Application........................................................................................95
Project Plan (Gantt chart).....................................................................................................................96
Database Structure...............................................................................................................................96
Product Deployment.............................................................................................................................98
Maintenance technique.........................................................................................................................99
Conclusion............................................................................................................................................99
Bibliography.......................................................................................................................................100
Task [1/P 1]
Design a relational database system using an appropriate design tools and techniques,
containing at least four tables, with clear statements of user and system requirements.
Introduction:
Sagar Timalsina (HND / First Semester)

7
Database 2018
In RDBMS, the information store in database tables, fields. Each RDBMS table consists of database
table rows and each database table row consists of one or more than one database table fields.
RDBMS structured the information into collection of tables, which might be related each other.
RDBMS also provide relational operators to manipulate the data stored into the database tables. The
standard user and application program interface to a relationship database in the Structure Query
Language (SQL).
Choice of tools
IDE: VisualStudio-2017 community edition
Programming Language – C# using .Net Framework
Relational Database Management System – SQL server -express edition
Platform: Desktop Application
The most common RDBMS are MS SQL Server, Oracle and MySQL. Here I use MS SQL server to
design database program for back end and also use Visual studio-2017 for front end to create desktop
application using C# programming language. In this task, I would like to describe about relational
database management system, entity relationship diagram using appropriate design tools and
techniques containing eight tables. Each row contains a unique instance of data for the categories
defined by the columns and also maintain a defined relationship. Also design relationship diagram
and data dictionary.
Entity Relational Diagram (ERD)
Entity relational diagram is technique of data modeling which graphically representing the logical
relationship between entities while before creating database (Rouse, 2018). In ERD, database
structure is represent in form of diagram.
Sagar Timalsina (HND / First Semester)

8
Database 2018
Figure: Entity Relationship Diagram
Figure: Entity Relationship Diagram
Above figures are the ERD of ‘SMS’ database which have different eight tables name as Product,
Purchase, Sales, Suppliers, Customer, UserInfo, UserRole, and UserRoleAssignment. Product table
have nine different columns, they are ProductID, ProductName, Size, Brand, UOM, Size, SalesPrice,
CreatedOn, CreatedBy. Purchase table have eight different columns, they are PurchaseID,
ProductID, PurchaseOn, PurchaseRate, PurchaseQty, SupplierID, CreatedOn, and CreatedBy. Sales
table have different seven columns name as SalesID, ProductID, SalesDate, SalesQty, CustomerID,
CreatedOn, and CreatedBy. Supplliers table have six different columns name as SupplierID,
SupplierNmae, SupplierAddress, SupplierContact, CreatedOn, and CtreatedBy. Customer table have
six different columns name as CustomeID, CustomerName, CustomerAddress, CustomerConatct,
CreatedOn, and CreatedBy. These are the different tables have different column names in my
database system. In my database system, based on given scenario I implement or maintain one to
many relationship between Product and Purchase, Product and Sales, Sales and Customer, Purchase
and Supplliers tables. Product is parent table for Purchase and Sales tables where ProductID is
common in these tables. Supplliers table is also parent table for purchase where SupplierID is
common in these tables. Customer table is also parent table for Sales table where CustomerID is
common in these two tables. In other three tables name as UserInfo, UserRole and
UserRoleAssignment tables, there are different columns which shown in above ER diagram figure.
Also in these tables, I maintain one to many relation between UserInfo and UserRoleAssignment,
Sagar Timalsina (HND / First Semester)

9
Database 2018
UserRole and UserRoleAssignment. For UserRoleAssignment table both UserInfo and UserRole
tables are parent tables.
Relational Diagram
Relational diagram is diagram which show all various or different factor areas or processes.
Relational diagram define how your system is process, also define uses of different factor in your
database system.
Figure: Relation Diagram of SMS database system.
Above figure shows the relational diagram of my database system ‘SMS’. It shows this system is
new software based user or admin training which increase profitability of this system. It increase
customer services, increase consumption also increase operating cost.
Data Dictionary
Data dictionary is the collection of database table’s information like database data, its relationships,
data type. It define each columns of the table, its name datatype, relationships.
Here, I prepare some data dictionary in form of table to define its column name, data type, and its
relationship based on given scenario. I have different eight tables and different data dictionary which
are as follows:
Table1:Product
TableName CloumnName DAtaType AllowNull isPK isFk
Referenc
e Remarks
Sagar Timalsina (HND / First Semester)

10
Database 2018
Product ProductID Uniqueidentifier N Y N _ _
Product ProductName Varchar Y N N _ _
Product Size Varchar Y N N _ _
Product Brand Varchar Y N N _ _
Product UOM Varchar Y N N _ _
Product MiniStockLevel Varchar Y N N _ _
Product Salesprice Varchar Y N N _ _
Product CreatedOn Datetime Y N N _ _
Product CreatedBy Varchar Y N N _ _
Above table is product table data dictionary. In above table, I define each columns name and its data
type. I also maintain relationship using foreign key and primary key. In ProductID column, I
implement primary key to unique its records and not to accept null value. Its datatype is also
Uniqueidentifier. In other columns also have its own data datatype, it accept null values, there are
no primary and foreign key. This above table define each items or records, relationship of Product
table.
Table2:Purchas
e
TableName CloumnName DAtaType
AllowNul
l isPK isFk
Referenc
e
Remark
s
Purchase PurchaseID
Uniqueidentifie
r N Y N _ _
Purchase ProductID
Uniqueidentifie
r Y N Y Product _
Purchase PurchaseOn Varchar Y N N _ _
Purchase PurchaseDate Varchar Y N N _ _
Purchase PurchaseQty Varchar Y N N _ _
Purchase SupplierID
Uniqueidentifie
r Y N Y Supplliers _
Purchase CreatedOn Datetime Y N N _ _
Purchase CreatedBy Varchar Y N N _ _
This is Purchase table data dictionary. In above table, I define each columns name and its data type. I
also maintain relationship using foreign key and primary key. In PurchaseID column, I implement
primary key to unique its records and not to accept null value. Its datatype is also Uniqueidentifier. In
other columns as ProductID and SupplierID, there are foreign and theirs data types are
Uniqueidentifier but it accept null values. This above table define each items or records,
relationship of Purchase table.
Table3 :Sales
TableName CloumnName DAtaType
AllowNul
l isPK isFk
Referenc
e Remarks
Sagar Timalsina (HND / First Semester)

11
Database 2018
Sales SalesID Uniqueidentifier N Y N _ _
Sales ProductID Uniqueidentifier Y N Y Product _
Sales SalesOn Varchar Y N N _ _
Sales SalesQty Varchar Y N N _ _
Sales CustomerID Uniqueidentifier Y N Y Customer
Sales CreatedOn Datetime Y N N _ _
Sales CreatedBy Varchar Y N N _ _
This is Purchase table data dictionary. In above table, I define each columns name and its data type. I
also maintain relationship using foreign key and primary key. In SalesID column, I implement
primary key to unique its records and not to accept null value. Its datatype is also Uniqueidentifier. In
other columns as ProductID and CustomerID, there are foreign and theirs data types are
Uniqueidentifier but it accept null values. This above table define each items or records,
relationship of sales table.
Table4:Supplliers
TableName CloumnName DAtaType
AllowNul
l isPK isFk Reference
Remark
s
Supplliers SupplierID Uniqueidentifier N Y N _ _
Supplliers SupplierNAme Varchar Y N N _ _
Supplliers SupplierAddress Varchar Y N N _ _
Supplliers SupplierContact Varchar Y N N _ _
Supplliers CreatedOn Datetime Y N N _ _
Supplliers CreatedBy Varchar Y N N _ _
Table5:Customer
TableName CloumnName DAtaType AllowNull
isP
K isFk
Referenc
e Remarks
Customer CustomerID Uniqueidentifier N Y N _ _
Customer CustomerName Varchar Y N N _ _
Customer CustomerAddress Varchar Y N N _ _
Customer CustomerContact Varchar Y N N _ _
Customer CreatedOn Datetime Y N N _ _
Customer CreatedBy varchar Y N N _ _
Above two tables are Suppliers and customer table data dictionary. These above table define each
item or records, relationship of two Suppliers and customer tables.
Table6:UserInf
o
Table Name Cloumn Name Data Type
AllowNul
l isPK isFk
Referenc
e Remarks
UserInfo UserID Uniqueidentifier N Y N _ _
UerInfo FirstName varchar Y N N _ _
Sagar Timalsina (HND / First Semester)

12
Database 2018
UserInfo MiddleName varchar Y N N _ _
UserInfo LastName varchar Y N N _ _
UerInfo Email varchar Y N N _ _
UserInfo UserName varchar Y N N _ _
UserInfo UserPassword Varbinary Y N N _ _
Userinfo isActive Bit Y N N _ _
UerInfo CreatedOn Datetime Y N N _ _
UserInfo CreatedBy varchar Y N N _ _
Above table is userinfo table data dictionary. This above table define each items or records,
relationship of UserInfo table. UserID datatype is uniqueidentifier where I implement primary key.
In UserPassword column, its data type is varbinary which use to encrypt to maintain security.
Table7:UserRole
TableName CloumnName DAtaType
AllowNul
l isPK isFk
Referenc
e Remarks
UserRole RoleID Uniqueidentifier N Y N _ _
UserRole RoleName Varchar Y N N _ _
UserRole isActive Bit Y N N _ _
UserRole CreatedOn Datetime Y N N _ _
UserRole CreatedBy varchar Y N N _ _
Above table is UserRole table data dictionary. This above table define each items or records,
relationship of UserRole table.
Table8:
UserRoleAssignment
TableName
CloumnNam
e DAtaType
AllowNul
l isPK isFk Reference Remarks
UserRoleAssignment UserID Uniqueidentifier N N Y UserInfo _
UserRoleAssignment RoleID Uniqueidentifier N N Y UserRole _
UserRoleAssignment CreatedOn Datetime Y N N _ _
UserRoleAssignment CreatedBy Varchar Y N N _ _
Above table is UserRoleAssignment table data dictionary. This above table define each items or
records, relationship of UserRoleAssignment table.
User Requirement
Considering with given scenario, I designed database name as ‘Stationary Management System’ for
stationary shop. In database, I created different eight table for stationary which helps to unique each
its table as well as column records to overcome spreadsheet records. This database system is
designed on basis of user requirement. User have required database to store data uniquely in regular
basis. Database system automate and centralize their record to stock, customer where every data is
Sagar Timalsina (HND / First Semester)