Database Design for a Bug Tracking System Project

Data Base Design Document BUG Tracking System Southern New Hampshire University Prof. Kimberly Queenan By D a t a B a s e D e s i g n D o c u m e n t P a g e 1 | 19 S.no Name 1 Sravani Vattikonda 2 Sannihitha Mekala 3 Sai Chandra Reddy Pallerla 4 Srinivas Reddy Gade 5 Juhitha Goli

Document Revision History Document Version Date Updated by Reviewed By Status Change Summary/Remarks v1.0 09-Nov-2022 Group Self-Review Completed Milestone-1 V2.0 15-Nov-2022 Group Self-Review Completed MileStone-2 D a t a B a s e D e s i g n D o c u m e n t P a g e 2 | 19

CONTENTS Document Revision History ................................................................................................................................. 2 Contents ........................................................................................................................................................... 3 1. Purpose ....................................................................................................................................................... 4 2. Data Base Initial Study ................................................................................................................................. 4 2.1 Group Members .................................................................................................................................... 4 2.2 Group’s Class Project ............................................................................................................................ 4 2.3 Analyze Business Reason ...................................................................................................................... 4 2.4 Define Problems and Constraints ............................................................................................................ 4 2.5 Define Objectives ................................................................................................................................. 5 2.6 Define Scope and Boundary ................................................................................................................... 5 3. Database Design .......................................................................................................................................... 6 3.1 Conceptual Database Design .................................................................................................................. 6 3.2 Logical Database Design ....................................................................................................................... 7 3.3 Physical Database Design ...................................................................................................................... 8 3.4 Tables and Values ................................................................................................................................. 9 3.5 Normalization .................................................................................................................................... 13 4. DBMS Software ........................................................................................................................................ 13 5. MS Access – Bug Tracking Database ........................................................................................................... 14 5.1 MS Access Relationship Diagram ......................................................................................................... 14 5.2 MS Access Components ...................................................................................................................... 14 5.3 MS Access Tables with loaded data ...................................................................................................... 17 6. Definitions / Abbreviations ......................................................................................................................... 20 7. References ................................................................................................................................................ 20 1. Purpose This document covers the data base design overview of the Bug Tracking system. The document will describe the steps that are necessary during the data base design of the specified system. This Design specification address the Data Base Initial Study and Data Base Design. D a t a B a s e D e s i g n D o c u m e n t P a g e 3 | 19

Your preview ends here

Eager to read complete document? Join bartleby learn and gain access to the full version

Access to all documents
Unlimited textbook solutions
24/7 expert homework help

2. Data Base Initial Study 2.1 Group Members Here are the group members,  Sravani Vattikonda  Sannihitha Mekala  Srinivas Reddy Gade  Sai Chandra Reddy Pallerla  Juhitha Goli 2.2 Group’s Class Project The Group’s project chosen is “ Bug Tracking System” . Bug Tacking System allows individual or groups of developers to keep track of outstanding bugs in their product effectively. 2.3 Analyze Business Reason “Bug-Tracking System” is an ideal solution to track the bugs of a product, solution or an application. Bug Tacking System allows individual or groups of developers to keep track of outstanding bugs in their product effectively. The Bug Tracking System can dramatically increase the productivity and accountability of individual employees by providing a documented workflow and positive feedback for good performance. 2.4 Define Problems and Constraints In any software development, bugs are inevitable. Let it be in any kind of product bugs arise at any phase of development. One must take a great care in the proper maintenance and resolution of the bugs. In the Existing system the bugs are not properly maintained, and they are simply relied on shared lists and email to monitor the bugs. In this type of system, it becomes difficult to track a bug if a bug is overlooked then it may cause tremendous errors in the next phase and can improve the cost of project whatever necessary effort spent on the bug maintenance may not be worthy. So, bug history has to be maintained properly. And there is no efficient search technique. 2.5 Define Objectives The main objective of Bug Tracking System is to manage the details of Bugs, Tickets, Tracking of bugs, reports. The Bug Tracking System can dramatically increase the productivity and accountability of individual employees by providing a documented workflow and effective database design. D a t a B a s e D e s i g n D o c u m e n t P a g e 4 | 19

As a part of Bug Tracking System, an effective and efficient data base must be designed by using normalization techniques, conceptual and physical design. The design should avoid data redundancies and insist data persistency. It must consider implementing entity integrity and referential integrity. 2.6 Define Scope and Boundary The scope is to design effective and efficient data base design for Bug Tracking System. Here are the key concepts of the scope,  Conceptual Database Design o Identify Entities o Define Relationships o ER Diagram  Logical Database Design o Detailed Entities o Detailed Relationships o Identify Attributes o Primary & Foreign Keys o Business Rules o Relationship Diagram  Physical Database Design o Tables Names o Column Names o Column Data Types o Data Types o Keys and Constraints D a t a B a s e D e s i g n D o c u m e n t P a g e 5 | 19

3. Database Design The database design includes conceptual, logical, and physical design of the system. 3.1 Conceptual Database Design Entities of Bug Tracking System are EMPLOYEE, DEPARTMENT, BUG_TYPE, BUG_REPORT, PROJECT, ASSIGNED_PROJECT.  EMPLOYEE and DEPARTMENT: (1:M) - An employee belongs to one department; however, each department have several employees.  EMPLOYEE and ASSIGNED_PROJECT: (1:M) - Each employee is assigned to one project; however, each project has several employees.  EMPLOYEE and BUG_REPORT: (1:M) – Each employee work on one bug report; however, many bug reports can be worked by different employees.  PROJECT and ASSIGNED_PROJECT: (1:M) - Each project has its own single project details; however, many projects executed parallelly.  PROJECT and BUG_REPORT : (1:M) - Each bug report belongs to one project; however, each project has many types of bug reports.  BUG_TYPE and BUG_REPORT : (1:M) - Each bug type has its own single bug description; however, many bug reports exist for different type of bugs. 3.2 Logical Database Design Relationship diagram and attributes for Bug Tracking System D a t a B a s e D e s i g n D o c u m e n t P a g e 6 | 19

Your preview ends here

Eager to read complete document? Join bartleby learn and gain access to the full version

Access to all documents
Unlimited textbook solutions
24/7 expert homework help

. Primary and Foreign keys for Bug Tracking System. 3.3 Physical Database Design Tables, Columns, Data types, Keys of the Bug Tracking System. Table Name Column Name Data Types Key Constraint DEPARTMENT dept_id number(10) Primary Key dept_name varchar(60) NA dept_location varchar(60) NA EMPLOYEE emp_id number(10) Primary Key lname varchar(60) NA fname varchar(60) NA gender varchar(60) NA D a t a B a s e D e s i g n D o c u m e n t P a g e 7 | 19 Entity Name Primary Key Foreign Key DEPARTMENT dept_id None EMPLOYEE emp_id dept_id ASSIGNED_PROJECT s_no emp_id, proj_id PROJECT prod_id None BUG_TYPE bug_type_id None BUG_REPORT bug_id bug_type_id, proj_id, emp_id

address varchar(60) NA doj date NA dept_id number(10) Foreign Key PROJECT proj_id number(10) Primary Key p_name varchar(60) NA p_description varchar(100) NA str_date date NA duration number(10) NA client_name varchar(60) NA client_addr varchar(100) NA client_phone number(10) NA ASSIGNED_PROJEC T s_no number(10) Primary Key proj_id number(10) Foreign Key emp_id number(10) Foreign Key role varchar(60) NA BUG_TYPE bug_type_id number(10) Primary Key bug_name number(10) NA bug_descriptio n varchar(100) NA BUG_REPORT bug_id number(10) Primary Key bug_type_id number(10) Foreign Key bug_level varchar(60) NA priority varchar(60) NA project_id number(10) Foreign Key bug_date date NA emp_id number(10) Foreign Key bug_status varchar(60) NA 3.4 Tables and Values DEPARTMEN T dept_id dept_name dept_location 101 Computers Hyderabad 102 Accounts Delhi 103 Sales Chennai 104 Marketing Kerala 105 Finance Karnataka 106 HR Maharastra D a t a B a s e D e s i g n D o c u m e n t P a g e 8 | 19

107 Science Bihar 108 Mathematics Telangana 109 Political Science Andrapradesh 110 Media Madhyaprades h EMPLOYE E emp_id lname fname gender addres s doj dept_id 1121 Vatiikonda Sravani Female HYD 21-Jan-19 101 1122 Mekala Sannihitha Female VZK 5-May-20 102 1123 Parella Sai Chandra Male AP 9-Apr-21 102 1124 Gade Srinivas Male TS 5-May-20 103 1125 Goli Juhitha Female MP 6-Jun-21 104 1126 Vemulapally Ramya Famela HYD 5-May-20 105 1127 Kodali Ravi Male UP 5-Feb-22 103 1128 Vattikonda RaviTeja Male VZK 5-Feb-22 104 1129 Sajja Sai Kiran Male AP 21-Jan-19 105 1130 Chalasani Pujitha Female TS 5-Feb-22 101 1131 Gade Nandhini Female TS 9-Apr-21 106 1132 Goli Shalini Female MP 5-May-20 110 1133 Marella Amar Male HYD 6-Jun-21 108 1134 Nannapanen i Raja Male UP 5-May-20 107 1135 Yarlagadda Amrutha Female VZK 5-Feb-22 109 PROJEC T proj_id p_name p_description str_date duratio n client_nam e client_add r client_phon e 201 Online Coaching This is an e-learning platform for the employees 2-Feb-19 5 GAP California 2345818654 202 Chargebac k Used to maintain fraudulent transactions by creating disputes 10-Mar-22 3 Lowes Florida 6218532491 203 Employee Portal To track employee details 5-Jul-21 4 Walmart Arkansas 8932613456 D a t a B a s e D e s i g n D o c u m e n t P a g e 9 | 19

Your preview ends here

Eager to read complete document? Join bartleby learn and gain access to the full version

Access to all documents
Unlimited textbook solutions
24/7 expert homework help

204 Online Payments Is the system to handle multiple products payments 10-Sep-21 2 Walmart Arkansas 9324516823 205 Deal and Offers This is offers coupons and special offers to the customers 2-May-19 6 Lowes Florida 7432561978 206 AI Shopping System Manages products for online shopping. 8-Aug-19 4 Walmart Arkansas 672345816 207 Chatbots Generates reports to the higher management 14-Oct-21 2 GAP California 7831982345 208 Weather forecasting Forecasts weather and publishes the results 21-Nov-20 4 CNN Illinois 8274536173 209 Tasks Monitoring Different types of activity monitoring system 12-May-20 3 Motorola Minnesota 5426251867 210 Shopping Cart Products hosting system for sale 8-Jan-19 5 Amazon New Hampshire 3458272983 ASSIGNED _ PROJECT s_no proj_id emp_id role 1 201 1121 Developer 2 202 1122 Developer 3 203 1123 Developer 4 202 1124 Tester 5 203 1125 Developer 6 201 1126 Developer 7 204 1127 Developer 8 205 1128 Tester D a t a B a s e D e s i g n D o c u m e n t P a g e 10 | 19

9 204 1129 Tester 10 205 1130 Developer 11 210 1135 Developer 12 206 1132 Developer 13 208 1133 Developer 14 209 1131 Developer 15 207 1134 Tester BUG_TYP E bug_type_i d bug_nam e bug_description 1001 404 error Page not found 1002 505 error HTTP version not supported 1003 501 error Not implemented 1004 604 error Column ambiguously defined 1005 705 error Online defragmentation 1006 405 error Method not allowed 1007 408 error Request time out 1008 412 error Preconditioned failed 1009 609 error Stack trace error 1010 701 error In sufficient memory BUG_REPOR T bug_id bug_type_i d bug_lev el priorit y project_i d bug_date emp_i d bug_status 10021 1001 High 2 205 10-Nov-22 1130 In Progress 10022 1002 Medium 3 204 9-Nov-22 1129 In Progress 10023 1003 Low 4 201 23-Sep-22 1126 In Progress 10024 1001 High 2 202 9-Nov-22 1124 In Progress 10025 1003 Low 3 202 11-Oct-22 1122 In Progress 10026 1002 Medium 3 201 29-Sep-22 1121 In Progress 10027 1004 Complex 1 203 11-Nov-22 1123 In Progress 10028 1005 Low 4 204 23-Aug-22 1127 Closed 10029 1004 Complex 1 205 9-Nov-22 1128 In Progress D a t a B a s e D e s i g n D o c u m e n t P a g e 11 | 19

10030 1005 Low 4 203 16-Aug-22 1125 In Progress 10031 1010 Medium 3 210 23-Sep-22 1135 In Progress 10032 1008 Complex 1 206 10-Nov-22 1132 Open 10033 1006 Low 4 208 21-Aug-22 1133 Closed 10034 1007 Complex 1 207 12-Nov-22 1134 In Progress 10035 1009 Low 4 209 23-Aug-22 1131 In Progress 3.5 Normalization 1NF: All the tables in the project follow all the 1NF rules,  There are only singles valued attributes to start with.  The attribute domain stays the same.  Every attribute and column have their own names.  It is not necessary to follow an order to store the data. 2NF: All the tables in the project follow all the 2NF rules,  Tables satisfy 1NF rules.  Tables of partial dependencies. 3NF: All the tables in the project follow 3NF rules,  Tables satisfy 2NF rules.  There are no transitive partial dependencies in the tables. 4. DBMS Software DBMS software used for Bug Tracking System. D a t a B a s e D e s i g n D o c u m e n t P a g e 12 | 19

Your preview ends here

Eager to read complete document? Join bartleby learn and gain access to the full version

Access to all documents
Unlimited textbook solutions
24/7 expert homework help

Technology Version Description MS Access Microsoft Access software is used to implement database, tables and MS Access Relationship Diagram. Draw.io Draw.io used to design Entity Relationship Diagrams and Models. 5. MS Access – Bug Tracking Database 5.1 MS Access Relationship Diagram 5.2 MS Access Components MS Access Components (List of tables pic without data) D a t a B a s e D e s i g n D o c u m e n t P a g e 13 | 19

D a t a B a s e D e s i g n D o c u m e n t P a g e 14 | 19

D a t a B a s e D e s i g n D o c u m e n t P a g e 15 | 19

Your preview ends here

Eager to read complete document? Join bartleby learn and gain access to the full version

Access to all documents
Unlimited textbook solutions
24/7 expert homework help

5.3 MS Access Tables with loaded data D a t a B a s e D e s i g n D o c u m e n t P a g e 16 | 19

D a t a B a s e D e s i g n D o c u m e n t P a g e 17 | 19

D a t a B a s e D e s i g n D o c u m e n t P a g e 18 | 19

Your preview ends here

Eager to read complete document? Join bartleby learn and gain access to the full version

Access to all documents
Unlimited textbook solutions
24/7 expert homework help

6. Definitions / Abbreviations  ERD – Entity Relationship Diagram  BTS – Bug Tracking System 7. References S. No. Item Description 1 Textbook Carlos Coronel, S. M. (n.d.). Database Systems: Design, Implementation, & Management. Cengage Learning. 2 Design Models https://www.vertabelo.com/blog/conceptual-logical-physical-data-model/ 3 MS Access You tube reference https://www.youtube.com/watch?v=FmFB42SCzWk 4 MSAccess YouTube Reference for Beginners https://www.youtube.com/watch?v=57jBdK3YVJo D a t a B a s e D e s i g n D o c u m e n t P a g e 19 | 19

Ex_Group2_MileStone2_BugTrackingSystem (2)

Related Documents