Database Assignment 2022
101 Pages18699 Words18 Views
Added on 2022-06-08
Database Assignment 2022
Added on 2022-06-08
ShareRelated Documents
1
Database 2018
Database
Assignment
Submitted by: Sagar Timalsina,
BTEC HND Computing,
1st Semester.
Submitted to: Mr. Umesh Acharya
Tinkune, Kathmandu, Nepal
Contents
Sagar Timalsina (HND / First Semester)
Database 2018
Database
Assignment
Submitted by: Sagar Timalsina,
BTEC HND Computing,
1st Semester.
Submitted to: Mr. Umesh Acharya
Tinkune, Kathmandu, Nepal
Contents
Sagar Timalsina (HND / First Semester)
2
Database 2018
Task [1/P 1]............................................................................................................................................7
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
Sagar Timalsina (HND / First Semester)
Database 2018
Task [1/P 1]............................................................................................................................................7
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
Sagar Timalsina (HND / First Semester)
3
Database 2018
Evidence of user interface and data validation....................................................................................21
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
Sagar Timalsina (HND / First Semester)
Database 2018
Evidence of user interface and data validation....................................................................................21
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
Sagar Timalsina (HND / First Semester)
4
Database 2018
Insert Statement....................................................................................................................................36
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
Sagar Timalsina (HND / First Semester)
Database 2018
Insert Statement....................................................................................................................................36
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
Sagar Timalsina (HND / First Semester)
5
Database 2018
Unit Test Log.......................................................................................................................................57
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
Sagar Timalsina (HND / First Semester)
Database 2018
Unit Test Log.......................................................................................................................................57
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
Sagar Timalsina (HND / First Semester)
6
Database 2018
User manual.........................................................................................................................................82
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)
Database 2018
User manual.........................................................................................................................................82
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)
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)
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)
End of preview
Want to access all the pages? Upload your documents or become a member.
Related Documents
Database Design and Development for Shoengalleric Art Gallerylg...
|66
|5608
|329
Manage Network and Data Integritylg...
|62
|2549
|302
After 30 years, Microsoft Excel remains ubiquitous in business.lg...
|106
|10270
|231
Linux Server Administrationlg...
|123
|727
|100
Linux Server Administration: DNS & SSH Server, FTP User, DNS Robust, Web Serviceslg...
|118
|1199
|380
Assignment on Task Manual Summary Split Start-Only Milestonelg...
|12
|2192
|14