Database Design Documentation and Implementation Report - ICBT HND

This report details the design and implementation of a relational database for New Life Academy Pvt Ltd, an educational institute. The assignment covers the analysis of data models, comparing different approaches to database design, and creating an Entity Relationship Diagram (ERD). It includes the development of relational schemas and the creation of a database using SQL Server, with at least 15 records per table. The report also presents SQL queries to retrieve specific data, such as student lists, batch details, and program information. Furthermore, it provides a test plan, test strategy, and test cases for the database, along with an explanation of verification, validation, and access rights for different user types. The report adheres to Harvard referencing and follows specific formatting guidelines, including word count, margins, and font specifications.

Assignment Cover Sheet
Qualification Module Number and Title
HND in Computing / Network Engineering SEC4205/Database Design Development
Student Name & No. Assessor
Nishanthi . K
CL/HND/22/17 Chatura.N.Warnasuriya
Hand out date Submission Date
Assessment type Duration/Length of Weighting of Assessment
Report 3000 words Assessment Type 40%
TBC
Learner declaration
I certify that the work submitted for this assignment is my own and research sources are fully
acknowledged.
1

Marks Awarded
First assessor
IV marks
Agreed grade
Signature of the assessor Date
2

FEEDBACK
FORM
INTERNATIONAL COLLEGE OF BUSINESS &
TECHNOLOGY
Module: Database Design Development
Student:
Assessor: Chatura.N.Warnasuriya
Assignment: Database Design Documentation and Implementation
Strong features of your work:
Areas for improvement:
3

Learning outcomes covered
 LO1 Understand the database concepts and components
 LO 2 Develop a logical database design.
 LO 3 Implement a physical database for a given requirement
 LO 4 Test and Manage databases
Scenario and the Task
New Life Academy pvt LTD is a Sri Lankan based education institute which conducts different
education programs such as Higher National Diplomas, Bachelors and Masters Programs. They
have set of student councilors who register students for the programs. Once a student registers
following details would be considered. Registration No, Student Name, Contact Details (Home,
Mobile and Email), Address, NIC, Education and Professional Qualifications. Same student can
register for multiple programs and a new registration no would be issued based on registering
batch and program. Once students are registering the respective student counselors’’ employee
no should be recorded for further reference. Usually, programs are conducted in both full time
and part time and should be finished with in a given time period and payment amount. Each
Program have modules and modules would include Module no, Name, Credit Value, No of
Sessions and module leader Emp No. Module leader is a lecturer who is working at the institute
as a full time employee and allocated for the batches as Batch advisors. The batches are created
based on student registrations. Each Student is allocated to a batch. Batch would have a Batch
No, Relevant Program, Starting Date, End Date and No of Students. In the institute, the
employee data should be recorded appropriately. And Each employee is assigned to different
departments such as Academics, Marketing, Accounts …etc.
New Life Academy pvt LTD is willing to enhance their information system and to have a
properly designed Database Management System. Assume that you have been assigned as the
Database Designer of the Development Company to prepare a fully descriptive Database Design
documentation to the implementation of the Information System.
4

Students can add any functionality which will enhance the system and make the proposed
solution more comprehensive.
Use Harvard referencing to properly acknowledge all the external sources you use.
Tasks:
Design the database with the constraint that the available technology is relational.
1. Explain what is data model, critically compare different data models and explain why
older data models are being replaced by new data models.(LO1) (15 Marks)
2. Analyze and briefly explain the different approaches to database design. (LO1) (10 Marks)
3. Draw an entity relationship diagram for given scenario with proper standards. Identify
important keys and represent different types of attributes and relationships. (LO2) (10
Marks)
4. Draw Relational Schemas. Effectively map conceptual data models with relational database
schema according to the mapping algorithm. All the steps should be clearly mentioned.
(LO2) (10 Marks)
5. Create the database using SQL server. (LO3) Practical submission and Demonstration (15
Marks)
NOTE: Make sure to enter at least 15 records for each table.
6. Write sql queries for below requirements. ( LO3)
Practical submission and Demonstration (10 Marks)
5

List of Student names of a particular Batch
List of Batch details and relevant student councilor information
List of Program details with relevant modules that they relate to
7. Provide the Test plan, test strategy and proper test cases. (LO4) (10 Marks)
8. Explain how verification and validation has addressed in your data base and the access rights
to various types of users. (LO4) (10 Marks)
6

Assessment Criteria
This submission will be
assessed as follows Total marks Marks obtained by
Allocated the student for the
answer provided
TASK 1 15
TASK 2 10
TASK 3 10
TASK 4 10
TASK 5 15
TASK 6 10
TASK 7 10
TASK 8 10
TASK 9 10
TOTAL 100
Guidelines for the report format
o Paper A4
o Margins 1.5” left, 1” right, top and bottom
o Page numbers – bottom, right
o Line spacing 1.5
o Word Count 3000
7

o Font
o Headings 14pt, Bold
o Normal 12pt
o Font face- Times New Roman
 Referencing and in-text citation should be done strictly using Harvard Referencing
System.
 All citation should be given as per "Harvard Referencing Style" for Text, Tables
and Figures
 Late submission will be penalized
 Please refer to the ICBT HND student handbook for further details
 Improper report structure will lead to maximum of 15% reduction in the
total marks.
Note: Any kind of copying and plagiarized text will lead to automatic Re-submission
8

Marking Scheme
Task-1 contains 15 marks
Criteria
Marks
Out of 15
Fail
Not explained the data model. Not compared enough data models
0-4
.Not given proper reasons for the replacement.
Pass
Explained the data model. Compared minimum 3 data models. Not
5-8
provided enough/Proper reasons to replace the older data models
Good
Explained the data model. Compared minimum 5 data models. 9-12
Provided proper reasons to replace the older data models
Excellent
Well explained about the data model. Critically compared different
13-15
5 data models. Well explained why the older data models are being
replaced by new data models
9

Task-2 contains 10 marks
Criteria
Marks
Out of 10
Fail
0-2
Not analyse properly the different approaches. Not explain the
different approaches
3-4
Pass
Analyzed the different approaches. Not provided the proper
explanation.
Average / Good
5-7Analyzed the different approaches using enough features. Provided
the proper explanation with examples
Excellent
Design Well Analyzed and briefly
explained the different approaches to
database design with proper examples and
figures also.
8-10
10

Task-3 contains 10 marks
Diagram should be evaluated according to the following criteria.
Criteria
Marks
Out of 10
Fail
Poor logical database design.
 Include incorrect Entities
 Include incorrect Attributes and Relationships 0-2
 Not used proper /standard symbols
Pass
Develop a logical database design.
Entity Relationship Diagram
3-4
 Identification of correct Entities
 Identification of correct Attributes and Relationships
Average Design/ Good
 Clear identification of Entities, Attributes and
5-7
Relationships
 Correct use of ERD notations with minor mistakes
11

Excellent Design
 Highly detailed diagram
 Backed by relevant assumptions
 ERD with cardinality and attributes with proper
standard symbols
8-10
 Proper theories and techniques need to apply in
ERD
 Use Chen & Martin notations for ERD
12

Task-4 contains 10 marks
Criteria
Marks
Out of 10
Fail
In correct Relational Schema
 Not identified the correct Entities Attributes and 0-2
Relationships
Pass
Relational Schema
 Identification of correct Entities 3-5
 Identification of correct Attributes and Relationships
Average Design/Good
 Clear identification of Entities, Attributes and
Relationships 6-7
 Correct use of Relational Schema notations with
minor mistakes
Excellent Design Relational Schema with proper standard symbols Proper theories and techniques
need to apply in Relational schema
 Relational Schema for ERD state the primary key
8-10
13

foreign key concept correctly
Task- 5 contains 15 marks
Criteria Marks
Out of 15
Excellent 15-13
 Appropriate use of more sophisticated database features (e.g.
use of stored procedures / functions / triggers to implement
business rules)
 Database design being proposed to facilitate requirements
 Provided the relevant assumptions
Good 12-9
 Make a good attempt to follow the Relational Database
Model including proper database objects
 Provide More accurate database design
Pass 8-5
 Provide a Basic Database Design with minor mistakes
including enough database objects (simple design)
Fail 4-0
 Provide a poor Database Design with mistakes
 Not work with enough database objects
Task 6 -contains 10 marks
14

Written SQL Queries (1 mark for each query 2×3 -> 6) Provided the correct SQL queries
Appropriate Data with proper records (4 marks Derive test data for the Database
Task-7 contains 10 marks
Criteria Marks
Out of 10
Excellent
 Detailed Test Plan Provided the Test Strategy and Proper test cases and test data 10 - 8
Good 7-5
 Provided Correct Test Plan and Test Cases with
less test data
Pass Level 4-2
 Provided Only test cases with minor
mistakes with less test data
Fail
 Not provided proper test cases and Not provided proper test data 2-0
15

Task-8 contains 10 marks
Criteria Marks
Out of 10
Excellent 10-8
Provided and well explained the applied verification
and validation process with the relevant examples,
Detailed Explanation of Access rights with Suitable
Examples for Object permission, Statement permission
and Implied permission Statements
Good 7-5
Provided the explanation of verification and validation
process with the relevant examples
Provided the Explanation of Access rights with
Suitable Examples
Pass 4-3
Provided the process of Verification and Validation
Process only.
Provided the Access Rights only for the Database
without the description and examples
Fail 2-0
Not provide the process of Verification and Validation
properly and not provide examples to show the Access
Rights only for the Database
16

Task- 9 contains 10 marks
Criteria Marks
Out of 10
Excellent
10-8
Professional standard of documentation
with screen shots & good explanation
Harvard Referencing System is used
for Referencing and in-text citation.
Good 7-5
High standard of documentation with
screen shots & average explanations
Pass 4-3
Acceptable standard of documentation
with poor explanations
Fail 2-0
Not followed a proper academic style ,
Not provided the referencing /provided in
correct citation.
17

Required Reading/ learning Materials
• 2020. [online] Available at: <https://www.quora.com/What-are-some-examples-of-
adatabase> [Accessed 14 June 2020].
• ExamPlanning. 2020. What Is Database, Its Types And Examples? - Examplanning %.
[online] Available at: <https://examplanning.com/what-is-database-its-types-
andexamples/> [Accessed 14 June 2020].
• Codeproject.com. 2020. Black Box Testing, Its Advantages And Disadvantages. [online]
Available at: <https://www.codeproject.com/Articles/5579/Black-Box-Testing-
ItsAdvantages-and-Disadvantages> [Accessed 14 June 2020].
• Tutorialspoint.com. 2020. Software Testing - Methods - Tutorialspoint. [online]
Available at:
<https://www.tutorialspoint.com/software_testing/software_testing_methods.htm>
[Accessed 14 June 2020].
• Bugraptors, 2020. Why Back-End Is The Most Important Part Of The System. [online]
Slideshare.net. Available at: <https://www.slideshare.net/Bugraptors/why-backend-is-
themost-important-part-of-thesystem#:~:text=Backend%20Testing%3A%20The
%20inevitable%20part,system%20dea dlock%2C%20and%20bad%20performance.>
[Accessed 14 June 2020].
• Tutorialspoint.com. 2020. Software Testing - Methods - Tutorialspoint. [online]
Available at:
<https://www.tutorialspoint.com/software_testing/software_testing_methods.htm>
[Accessed 14 June 2020].
 Rainer, R., 2019. Introduction To Information Systems, 7Th Australia And New Zealand
Edition. New York: Wiley.
Recommended readings
Fundamental of Database Systems , 7th Edition (ELMASRI NAVATHE)
18

19

Acknowledgment
I would like to express my special thanks of gratitude to my lecture Mr. Chatura.N.Warnasuriya
who gave me the good knowledge to professional practice and to do this wonderful assignment
which also helped me in doing a lot of research and I came to know about so many new things I
am really thankful to sir.
Secondly, I would also like to thank my parents and friends who helped me a lot in finalizing
this assignment within the limited time frame.
Thank you
Nishanthi.K
HND in computer and network technology (Batch 22)
20

Introduction
This assignment is about Database Design & Development within the work place. This unit
provide Foundation for good practice variety of situation. This assignment consists four learning
Outcomes, which are LO1, LO2, LO3 and LO4.
LO1 Understand the database concepts and components 1st Learning outcome is done as
classroom activity.
LO2 Develop a logical database design. 2nd Learning outcome applied Data manipulation using
appropriate query tools, including complex queries to query across multiple tables, and using
functions
LO3 Implementation a physical database for a given requirement. 3rd Learning outcome
discusses the elements of the system that need to be tested. This task is done in Report method.
LO4 Test and manage databases .4th Learning Outcome Produce technical and user
documentation for above design and implementation. This task is done in Report method.
21

Table Of Contents
What is Data?................................................................................................................................................................25
What is Database?........................................................................................................................................................26
Elements of database................................................................................................................................................26
Advantage of Database.............................................................................................................................................26
Disadvantage of Database.........................................................................................................................................27
Datamodels...................................................................................................................................................................27
Catagories of data models.........................................................................................................................................28
Conceptual data models.......................................................................................................................................28
Representational Data models.............................................................................................................................29
Physical Data model..............................................................................................................................................30
Advantages and Disadvantages of Data Model:...........................................................................................................30
Conclusion.....................................................................................................................................................................30
Concept of Object-Oriented Database.................................................................................................................33
List of Object-Oriented Database Standards........................................................................................................33
Characteristics of Object-oriented database........................................................................................................33
Merits of Object-oriented database.....................................................................................................................34
Top 5 Free Database Diagram (ERD) Design Tools...............................................................................................34
Top Most Popular Database Designing Tools.......................................................................................................34
Advantages of Object-oriented Database............................................................................................................35
Disadvantages of Object-oriented Database........................................................................................................35
Top Down Design Model...............................................................................................................................................36
Advantages................................................................................................................................................................36
Bottom up design..........................................................................................................................................................37
A relational Database....................................................................................................................................................37
Benefits of Relational Databases..............................................................................................................................38
Keys...............................................................................................................................................................................38
ER Diagram ( Entity–relationship).................................................................................................................................40
Entity Type ER Notion.............................................................................................................................................40
Here the above the Scenario.....................................................................................................................................40
Above the Scenario will be placed in SQL Query..........................................................................................................43
List of Students Names of a particular batch................................................................................................................49
List of batch details and relevant student councellor information..............................................................................50
22

List the program details with relevant modules...........................................................................................................51
In database we use some design tools for create and design the database in an organization standard..................52
Back-end systems..........................................................................................................................................................52
Why Back-end is the most important part of the system?..........................................................................................52
White-box testing......................................................................................................................................................53
Advantages / Pros of White Box Testing..................................................................................................................53
Disadvantages / Cons of White Box Testing............................................................................................................53
Functional testing......................................................................................................................................................54
Advantages of functional testing..............................................................................................................................54
Robustness testing.....................................................................................................................................................55
Assess the effectiveness of the testing, including an explanation of the choice of test data used.............................55
Why it is Important...................................................................................................................................................56
Challenges in Database Testing................................................................................................................................56
Benefits of Database Testing....................................................................................................................................57
Database Testing Tools.............................................................................................................................................57
23

Tables of Figures
Figure 1 Object oriented database................................................................................................................................32
Figure 2 top-down and bottom-up design....................................................................................................................36
Figure 3 ER Diagram....................................................................................................................................................42
Figure 4 creating the databases.....................................................................................................................................43
Figure 5 create the registration table............................................................................................................................43
Figure 6 INsert the Datas..............................................................................................................................................43
Figure 7 datas of the registration table..........................................................................................................................44
Figure 8 Program tables................................................................................................................................................44
Figure 9 Batch tables....................................................................................................................................................45
Figure 10 Employee tables...........................................................................................................................................46
Figure 11 Advisor Tables.............................................................................................................................................47
Figure 12 Lectuer tables...............................................................................................................................................48
Figure 13 alter the table code........................................................................................................................................48
Figure 14 after alteration...............................................................................................................................................49
Figure 15 SQL Code for find the batchwise name.......................................................................................................49
Figure 16 sql code for find the batch information........................................................................................................49
Figure 17 Sql code........................................................................................................................................................50
24

What is Data?
Data mean know fact that can be recorded & that have implicit meaning. It can be used in a
variety of forms like text, numbers, media, bytes, etc. it can be stored in pieces of paper or
electronic memory.
In computing, data is information that can be translated into a form for efficient movement and
processing. Data is interchangeable.
What is Database?
A database is a collection of related data. Here related means a logically related. A database has
the following implicit properties
 Mini world
 A database is a logically related collection of data
 Database is design, build and populated with data for a specific purpose
Most databases contain multiple tables, which may each include several different fields. For
example, a company database may include tables for customer details, products, employees, and
financial records. Each of these tables would have different fields that are relevant to the
information stored in the table.
Elements of database
• Schema
• Indexes
• Tables
• Fields / Colum
• Records / rows
• Keys
25

Advantage of Database
 Controls redundancy:
Redundancy means storing same data multiple times. This leads to several problems
1. Duplication of data
2. Storage space is wasted
Controlling redundancy means stop or control storing the same data multiple times.
Storing data in only one place in the database design technique is called data
normalization.
 Restrict Unauthorized access:
When multiple users share a large database most users will not be authorized to access
all information in the database. A DBMS should provide a security and authorization
subsystem, which the DBA (Database Administrator) use to create accounts & to Specify
account restriction. Then the DBMS should enforce these restrictions automatically.
 Providing Backup and recovery:
A DBMS must provide facilities for recovering from hardware or software failures, the
backup & recovery subsystem of the DBMS is responsible for recovery.
 Providing Multiple user interface:
Because many types of users (Customer, bank manager, cashier) with varying levels of
technical knowledge use a database, a DBMS should provide a variety of user interface
Disadvantage of Database
Size: It occupies a large disk space and large memory to run efficiently.
Cost: DBMS requires a high-speed data processor and larger memory to run DBMS software,
so it is costly.
Complexity: DBMS creates additional complexity and requirements.
26

Datamodels
Collection of concepts that can be used to describe the structure of database. By structure of the
database we mean the data types Relationships & constrains that should hold for the data.
The primary goal of using data model are:
 Ensures that all data objects required by the database are accurately represented.
Omission of data will lead to creation of faulty reports and produce incorrect results.
 A data model helps design the database at the conceptual, physical and logical levels.
 Data Model structure helps to define the relational tables, primary and foreign keys and
stored procedures.
 It provides a clear picture of the base data and can be used by database developers to
create a physical database.
 It is also helpful to identify missing and redundant data.
 Though the initial creation of data model is labor and time consuming, in the long run, it
makes your IT infrastructure upgrade and maintenance cheaper and faster.
Catagories of data models
Data models have been proposed according to the types of concept they used to describe the
database structure
1. High level or conceptual data models
2. Low level or physical data models
3. Representational or implementation data models is equal to three schema architecture
Conceptual data models
This data model used concepts such as entities , attributes and relationship
 Here the entities means
27

 An entity represents a particular type of object in the real world
 An entity is “Distinguishable”- that is each entity occurrence is unique &
distinct
 Attributes means it is a characteristics of an entity for example CUSTOMER entity would
be described by attributes such as customer_last_name, customer_first_name,
customer_phone_number,customer_address and Customer_credit_limit
Attributes are the equivalent of fields in the systems
 Relationship means the two relational database tables are connected with the foreign key
of another table of the primary key. Relation types can be divided in three types
o One to one (1:1)
 This relationship is when the one instance of a entity A is associated with
one instance of entity B
 Example is you taken as simple example is there is a two groups one is the
kind of the birds such as parrot, crow, eagle another group is colors such
as green, black , brown so here birds group members are interconnected
with the color group in one member each member connected one member
of another group this called one to one relationship
o One to many (1:N)
 This relationship is when the one instance of a entity A is associated with
more than one instance of entity B
 Example is you taken as simple example is there is a two groups one is the
kind of the numbers such as 4,5,7. another group is calculations such as
2+2, 6-2, 5+2, 4+3, 4+1 , 8-3 so here numbers group members are
interconnected with the calculations group in more than one member each
member inter connected more than one member of another group this
called one to one relationship
o Many to many (N:N)
 This relationship is when the more than one instance of a entity A is
associated with more than one instance of entity B
 Example is you taken as simple maths example is there is a two groups
one is the kind of the symbols such as X,Y,Z another group is alphabets
such as P,Q,E,R,S,T,V so here symbols group more than one members are
28

interconnected with the alphabets group in more than one member each
member connected more than one member of another group this called
one to one relationship
Representational Data models
 These data models are the models used most frequently in commercial dbms (database
management system).
 These include the widely used relational data model, as well as network & hieratical
model that have been widely used in the past.
 Object data model group as a new family of implementation data model.
Physical Data model
These data model describe how data is stored as files in the computer by representing
information such as record formats, record orderings & access path
Advantages and Disadvantages of Data Model:
Advantages of Data model:
 The main goal of a designing data model is to make certain that data objects offered by
the functional team are represented accurately.
 It used to build the physical database
 It helps the important of the relationship between the tables
 It also helps the business can communicate within the across organizationons.
 Data model helps to documents data mappings in ETL process
 Help to recognize correct sources of data to populate the model
Disadvantages of Data model:
 To develop Data model, one should know physical data stored characteristics.
29

 This is a navigational system produces complex application development, management.
Thus, it requires a knowledge of the biographical truth.
 Even smaller change made in structure require modification in the entire application.
 There is no set data manipulation language in DBMS.
Conclusion
There are main three types of the data models are used here so every organization should
changed in every year in their business so they have to required changing the technology so there
fore we have to change the new adopting the technology. When using the older model that may
be insufficient of the data grow and companies need to understand how to improve the back end
service to align with demand. Older data model not satisfy the following the perspectives
Techniques :
If you are looking at techniques in data modelling, there are quite a few that are
exploding. Deep learning, spectral methods, kernel methods, probabilistic graphical
models, social networking analytics are all the latest and fastest growing areas.
Business verticals:
We are also seeing a lot of interest in data science applications in the entire circle of
health care industries like pharmaceutical industries, hospitals and insurance companies.
Previously only banks and retail organisations used to be analytics savvy. So, if I
interpret your questions as what are the areas where data science is becoming a new
approach to problem solving, I advise you to watch out the healthcare sector.
Horizontal problems:
we often hear a lot from clients from a variety of verticals about their need to solve
questions related to unstructured data analysis in the context of social media content.
Data visualization is also a capability that is generating a lot of interest in the corporate
world.
30

What is Object Oriented Database?
An object-oriented database is a collection of object-oriented programming and relational
database. There are various items which are created using object-oriented programming
languages like C++, Java which can be stored in relational databases, but object-oriented
databases are well-suited for those items. An object-oriented database is organized around
objects rather than actions, and data rather than logic.
Object database management systems are based on objects in object-oriented programing. In
object-oriented programing, an entity is represented as an object and objects are stored in
memory. Objects have members such as fields, properties, and methods. Objects also have a life
cycle that includes the creation of an object, use of an object, and deletion of an object. OOP has
key characteristics, encapsulation, inheritance, and polymorphism.
Today, there are many popular object-oriented programing languages such as C++, Java, C#,
Ruby, Python, JavaScript, and Perl. The idea of object databases was originated in 1985 and
today has become common for various common OOP languages, such as C++, Java, C#,
Smalltalk, and LISP. Common examples are Smalltalk is used in Gemstone, LISP is used in
Gbase, and COP is used in Vbase.
Object databases are commonly used in applications that require high performance, calculations,
and faster results. Some of the common applications that use object databases are real-time
systems, architectural & engineering for 3D modeling, telecommunications, and scientific
products, molecular science, and astronomy.
Object-Oriented Programming + Relational Database Features = Object-Oriented
Database Model
31

Figure 1 Object oriented database
Concept of Object-Oriented Database
An object-orientation is a set of design and development principles based on conceptually
autonomous computer structures known as objects. Each object represents a real-world entity
with the ability to interact with itself and with other objects. We live in a world of objects.
These objects exist in nature, in human made entities, in business and in the products that we
use. They can be categorized, described, organized, combined, manipulated and created.
Therefore, an object-oriented view enables us to model the world in ways that help us better
understand and navigate it.
List of Object-Oriented Database Standards
Some of the Object-oriented Database management system standards are: -
• Object data management group
• Object database standard ODM 6.2.0
• Object query language
• Object query language support of SQL 92
32

Characteristics of Object-oriented database
The characteristics of object-oriented database are listed below.
• It keeps up a direct relation between real world and database objects as if objects do not
lose their integrity and identity.
• OODBs provide system generated object identifier for each object so that an object can
easily be identified and operated upon.
• OODBs are extensible, which identifies new data types and the operations to be
performed on them.
• Provides encapsulation, feature which, the data representation and the methods
implementation are hidden from external entities.
• Also provides inheritance properties in which an object inherits the properties of other
objects.
Merits of Object-oriented database
• Object oriented database allow for the storage of complex data structures that cannot be
easily stored using conventional database terminology.
• Object oriented database support all the persistence required for object-oriented
applications.
• Object oriented database contain active object servers which support both distribution of
data and distribution of work.
Top 5 Free Database Diagram (ERD) Design Tools
• dbdiagram.io
• draw.io
• Lucid chart
• SQLDBM
• Quick DBD
Top Most Popular Database Designing Tools
• Visual Paradigm ERD Tools
• Vertabelo
33

• Lucid chart
• SQL Server Database Modeler
• DeZign for Databases
• Erwin Data Modeler
• Aqua Data Studio ER Modeler
• DB Wrench
• IBM Info Sphere Data Architect
• DbDesigner.net
Advantages of Object-oriented Database
Object oriented database advantages over Relational database:
• Objects do not require assembly and dis-assembly saving coding time and execution
time to assemble or disassemble objects.
• Reduced paging
• Easier navigation
• Better concurrency control
• Data model is based on the real world
• Works well for distributed architectures
• Less code required when applications are object oriented.
Disadvantages of Object-oriented Database
Object oriented database is not as successful as Relational database Management System due to
its many disadvantages.
• There is no standard query language to OODB as SQL to RDMB. Several attempts have
been failed. SQL is an approved standard.
• There is no commonly recognized single data model for OODB. Relational data model
design is based on descriptive events and much simpler. However, object database has to
34

create different objects, resulting low efficiency when the data and relationships are
simple.
• OODB is towards more to the professional programmers instead of naive end users.
Database should not be language specific; it should be accessible to everyone to have
market.
• OODB is much more complex than RDMB.
• OODB lacks support of security. The user cannot grant access rights on individual
objects and classes.
• OODB uses “lock” protocol for concurrency control while RDMB uses timestamp
protocol. If locking is applied at the object level, performance will be impacted.
Top Down Design Model
o It an overview of the system is formulated, without going into detail for any part
of it
o Each part of the system is then refined in more details
o Each new part may then be refined again, defining it in yet more details until the
entire specification is detailed enough to validate the model
o If we look at a problem as a whole, it may seem impossible to solve because it is
so complex.
o Examples :
 Writing a university system program
 Writing a spreadsheet
o Complex problems can be solve using top down design also known as stepwise
refinement where,
o We break the problem into parts
o Then break the parts into parts
o Soon, each of the parts will be easy to do
Advantages
o Breaking the problem into parts helps us to clarify what needs to be done
o At each step of refinement, the new parts become less complicated and therefore
easier to figure out.
35

o Parts of the solution may turn out to be reusable
o Breaking the problem into parts allows more than one person to work on the
solution.
Bottom up design
o In bottom up design individual parts of the system are specified in details
o The parts are then linked together to form larger components, which are in turn
linked until a complete system is formed
o Object oriented languages such as C++ or JAVA use bottom up approach where
each object is identified first
Figure 2 top-down and bottom-up design
A relational Database
A relational database refers to a database that stores data in a structured format, using rows and
columns. This makes it easy to locate and access specific values within the database. It is
relational because the values within each table are related to each other. Tables may also be
related to other tables. The relational structure makes it possible to run queries across multiple
tables at once.
While a relational database describes the type of database an RDMBS manages, the RDBMS
refers to the database program itself. It is the software that executes queries on the data,
including adding, updating, and searching for values. An RDBMS may also provide a visual
36

representation of the data. For example, it may display data in a table like a spreadsheet,
allowing you to view and even edit individual values in the table. Some RDMBS programs
allow you to create forms that can streamline entering, editing, and deleting data.
Most well-known DBMS applications fall into the RDBMS category. Examples include Oracle
Database, MySQL, Microsoft SQL Server, and IBM DB2. Some of these programs support
nonrelational databases, but they are primarily used for relational database management.
Benefits of Relational Databases
If you want to design a data storage system that makes it easy to manage lots of information, and
is scalable and flexible, the relational database is a good bet.
Manageability: for starters, an RDB is easy to manipulate. Each table of data can be updated
without disrupting the others.
You can also share certain sets of data with one group, but limit their access to others – such as
confidential information about employees.
Flexibility: if you need to update your data, you only have to do it once – so no more having to
change multiple files one at a time.
Flexibility: if you need to update your data, you only have to do it once – so no more having to
change multiple files one at a time.
Avoid Errors: there’s no room for mistakes in a relational database because it’s easy to check
for mistakes against the data in other parts of the records. And since each piece of information is
stored at a single point, you don’t have the problem of old versions of data clouding the picture.
Keys
Set of attributes whose values uniquely identify each tuple of relation called keys
Basic 4 keys
 Primary key
o Primary key values are used to identify each tuples with in a table. The value of
primary key attribute must each be unique within the domain of that attributes
o For a example student is the table
37

o So here Sname,S_Number,class,Major are attributes
o So S_number is a primary key the primary key should be underline in the
database schema like below
Table 1 Example of primary key
 Foreign key
o These are the attributes used to across references tuple using the tuples primary
key values. Primary key for one table into which it is embedded for the purpose of
identifying relationship occurring
 Composite key
o More than one attribute column is need to establish identification is need to
establish unique identification for tuples within table the resulting primary key is
referred to as a concatenated primary key
Ex- customer_id & order_id
 Candidate key
o Sometimes a relation schema may have more than one key. In this case each of
the keys is called a candidate key
 Secondary key
o If a relation schema has more than one key each is called a candidate key. One of
the candidate key is selected to be the primary key and the others are called
secondary keys
Another is super key
An attribute or set of attributes that uniquely identifies a tuple with a relation
38
Sname S_Number Class Major

ER Diagram ( Entity–relationship)
An Entity–relationship model (ER model) describes the structure of a database with the help of a
diagram, which is known as ER Diagram. An ER model is a design or blueprint of a database
that can later be implemented as a database. The main components of E-R model are: entity set
and relationship set.
Entity Type ER Notion
 Entity types represented as a rectangular box enclosing entity name
 Attributes name are enclosing with oval
 Composite attributes are attached to their component attributes by straight lines
 Multivalued attributes are displayed in double ovals
 Relationship types defines a set of associates among the entities and shown as a diamond
shape
Here the above the Scenario
The entity as a New life Academy pvt ltd. It has the many tables (relations) such as
students_details, Employee_details, modules, programs so the each tables have their attributes
so below the images shown as their tables as per their attributes
Table 2 student table
Registration_Details
Registration_no Varchar (20)
Student_Name Varchar (300)
Contact_Number (email,home,mobile) Varchar (200)
Address Varchar (600)
NIC Varchar (10)
Education and professional qualification Varchar (50)
EMpno Int
Here is the Registration_no is the primary key
39
Student
Student_Name
Student
_Name
color

Table 3 program tables
Programs tables
Modules no Int
Modules name Varchar (200)
Credit values Varchar (3)
No of sessions Varchar (10)
Module leader emp no Varchar (20)
Here is the Modules_no is the primary key
Table 4 batch tables
Batch tables
Batch no Varchar (30)
Relevant program (modules name) Varchar (200)
Starting date Varchar (200)
End Date Varchar (200)
No of student int
Here is the Batch_no is the primary key
Table 5 Employee tables
Employee tables
Emp no Varchar (20)
Emp name Varchar (300)
Department name Varchar (100)
Addresss Varchar (400)
Here is the Emp_no is the primary key
Table 6 lecturer table
Lecturer table
Emp no Varchar (20)
Emp name Varchar (100)
Address Varchar (300)
Modules Varchar (300)
Here is the Emp_no is the primary key
40

Table 7 Advisor Table
Advisor table
Emp no Varchar (20)
Emp name Varchar (300)
Address Varchar (300)
Here is the Emp_no is the primary key
Figure 3 ER Diagram
`
The Registration table have the relation with advisor because advisor directly handle with the
student registration so here the relationship in one to many and same as a handling the
programming so there are same relationships. But lecturer and advisor work as an employee so
they associate with the employee tables. Lecturer teaches batch so there is one to many
relationships. Lecturer and programs many to many relationship and registrated students
allocated to the batch so many to many relationship also taken this place
41

Above the Scenario will be placed in SQL Query
Figure 4 creating the databases
Figure 5 create the registration table
Figure 6 INsert the Datas
42

Figure 7 datas of the registration table
Figure 8 Program tables
43

Figure 9 Batch tables
44

Figure 10 Employee tables
45

Figure 11 Advisor Tables
46

Figure 12 Lectuer tables
List of Students Names of a particular batch
Early we don’t have the batchno column in registration so I do the alter of the code and do the query
Figure 13 alter the table code
47

Figure 14 after alteration
Figure 15 SQL Code for find the batchwise name
List of batch details and relevant student councellor information
48
Figure 16 sql code for find the batch information

List the program details with relevant modules
Figure 17 Sql code
In database we use some design tools for create and design the database in an organization
standard.
Example: -
• MySQL.
• PostgreSQL.
• Microsoft Access.
• SQL Server.
• FileMaker.
• Oracle. • RDBMS.
Back-end systems
A back-end system is any system that supports back-office applications. Back end system is
worked with some corporate management. These systems are used as part of corporate
49

management and they work by obtaining user input and gathering input from other systems to
provide responsive output.
Separation of front-end and back-end computer systems simplifies the computing process when
dealing with multilayered development and maintenance. Back-end systems deal with databases
and data processing components, so the purpose of the back-end system is to launch the
operating system's programs in response to front-end system requests and operations. In other
words, the back-end system implements responses to what the front end has initiated.
Why Back-end is the most important part of the system?
Backend Testing: The inevitable part of Software testing. A backend or database is the most
important part of any client/server system. The data entered in the front end will be stored in the
back-end if the backend fails, it may cause data corruption, data loss, system deadlock, and bad
performance or finally system will be dismissed.
White-box testing
White box testing is testing of a software solution's internal structure, design, and coding. In this
kind of testing, the code is visible to the tester. It focuses primarily on verifying the flow of
inputs and outputs through the application, improving design and usability, strengthening
protection. White box testing is also known as clear box testing, Open box testing, Structural
testing, transparent box testing, Code-based testing, and Glass box testing. it is also achieved
through developers.
How to do White Box Testing?
1. Identify the feature, component, program to be tested
2. Plot all possible paths in a flow graph
3. Identify all possible paths from the flow graph
4. Write Test Cases to cover every single path on the flow graph
5. Execute, rinse, repeat
50

Advantages / Pros of White Box Testing
• Code optimization by revealing hidden errors
• Transparency of the internal coding structure which is helpful in deriving the type of
input data needed to test an application effectively
• Covers all possible paths of a code thereby, empowering a software engineering team to
conduct thorough application testing
Disadvantages / Cons of White Box Testing
• A complex and expensive procedure which requires the adroitness of a seasoned
professional, expertise in programming and understanding of internal structure of a code
• Updated test script required when the implementation is changing too often
• Exhaustive testing becomes even more complex using the white box testing method if
the application is of large size
Functional testing
Functional testing is a type of software testing that validates the software system against the
purposeful requirements/specifications. The purpose of functional tests is to test every function
of the software application, by way of providing suitable input, verifying the output towards the
useful requirements.
Functional testing involves the following steps:
• Identify functions that the software is expected to perform.
• Create input data based on the function’s specifications.
• Determine the output based on the function’s specifications.
• Execute the test case.
• Compare the actual and expected outputs.
51

Advantages of functional testing
Functional testing has so many benefits to provide the end users an efficient and reliable
application software the following are the various advantages of functional testing to the
business organizations
• Functional testing focus on the requirements of the guidelines for end users to
test the various models of the applications
• It accepts realistic facts and figures to make testing reports
• This type of testing is conducted when the project is very close to customers like
operating system browsers database Excel
Disadvantages of functional testing
As we know everything has good things along with a few bad things, this is the reason why
functional testing also has some disadvantages which are stated as below
• functional testing is a process in which various logical mistakes in the software
are not detected in the testing process
• where is also a possibility of redundant testing which me in hands the testing cost
and efforts of the
• It doesn’t care how the developer implements the actual source code because it
only focuses on the results of the source code.
Robustness testing
The term 'robust' is synonymous with strength. So, robustness testing is the way to assess the
quality of a software product. It the procedure of verifying whether a software system performs
well under stress situations or not. The technique of carrying out robustness testing follows a set
of conventions. A set of invalid inputs or odd/stressful environment is set up. Sometimes it so
52

happens that on offering sure inputs the program may crash. It becomes significant to capture
those errors and rectify it according with the requirement specifications
Assess the effectiveness of the testing, including an explanation of the choice of test data
used.
Database testing involves validation of values or data retrieved by an application from the
database to ensure the data shown on the user interface matches the data present in the database
or vice versa. This testing is at the data access layer. The layer through which the application
communicates with the database. It involves testing application database integrity testing and its
data stores, such as stored procedures, views, and tables.
Why it is Important
• When a tester goes through the database design documents, the tables & joins, the
dataflow, the data types impart more knowledge to the testers there by more test
scenarios and by which better testing of the application is done.
• The bugs which are difficult to find in front-end testing can only be found by database
testing.
• Some conditions which you cannot test by front end application can be tested easily
using database.
Challenges in Database Testing
• Under Testing
DB testing requires an organized, well-planned approach. When the testing team doesn’t have
enough understanding, testing becomes lengthy and incomplete in terms of test coverage and the
test data utilized.
• The Incomplete Scope of Testing
Under-qualified testing teams perform just back-box testing. They test the applications that
depend on databases by acting like real users; they test only through the user interface (UI).
White-box testing provides a gives idea as to test coverage, but requires testers with excellent
53

SQL skills. Those testers cost more, yet the cost is maintained, as SQL queries also work for
testing database triggers, procedures, and other critical database properties.
Inexperienced testing teams neglect to prioritize DB features in terms of significance, which is
essential to saving effort and time. They try to test everything, then lose time by testing minor
pieces of the database, and leaving the significant parts under-tested or not tested at all.
• Incomplete Test Data
Most common testing approach is to populate a database with a limited amount of false data.
These data have nothing to work with the real data, so the testing team can’t recognize some
flaws.
When finally uncover these issues, need to invest additional time and effort debugging and
retesting. It’s possible to utilize realistic test data without exposing actual customer data. While
can’t use the names of real people and companies, you can use postal codes, Phone numbers
specific to a target country. And that may help to address any front-end inconsistencies that
users might experience adequately.
• Pricey automation
The problem is that testing teams are unaware of the cost of tools. They tend to go for open
source tools, hoping to get a performing tool for nothing. These tools may provide rich
functionality free of charge, but open source tools may also lack the stability and convenience of
commercial tools.
For better service, you’ll need to pay, so if decide to go with a commercial tool, it’s critical to
calculate costs correctly. To do that need a good understanding of the project specifics and tool
functionality required.
Benefits of Database Testing
It speeds an overall cycle to create a room to test large data sets.
It also helps in processing complex transaction behaviors.
54

Database Testing Tools
Some of the tools mentioned below that help to execute these types of testing on a database.
• Database Benchmark
• Database Rider
• Db stress
• DB Test Driven
Database testing assures security and reliability of an application. We have understood that
keeping the privacy of user’s data is of prime importance. Therefore, it is essential to carry out
database testing to ensure data integrity, consistency, etc. are maintained. While performing
database testing one must remember the ‘why’ and ‘how’ criteria.
55