Skip to main content

Database Knowledge for a Tester - Part I

1. Database concepts

1.1 Codd’s Rule for RDBMS – 12 Rule

Rule 1: The Information RuleAll data should be presented in a table form
Rule 2: Guaranteed Access RuleAll data should be accessible without ambiguity.
Rule 3: Systematic Treatment of Null ValuesSupport for Null values which is distinct from an empty string or a number with a value of zero.
Rule 4: Dynamic Online Catalog based on the Relational modelA relational database must provide access to its structure through the same tools that are used to access the data.
Rule 5: Comprehensive Data Sublanguage RuleThe DB must support at least one clearly defined language that includes functionality for Data Definition, Data Manipulation, Data Integrity, and Database Transaction Control.
Rule 6: View Updating RuleEach view in the database should support the same full range of data manipulation that has direct access to a table available. In reality, providing update and delete access to logical views is difficult and is not fully supported by any current database.
Rule 7: High-level Insert, Update, and DeleteInsert, Update and Delete operations should be supported for any retrievable set rather than just for a single row in a single table.
Rule 8: Physical Data IndependenceThe user is isolated from the physical method of storing and retrieving information from the database.
Rule 9: Logical Data IndependenceHow data is viewed should not be changed when the logical structure of the database changes. This rule is particularly difficult to satisfy.
Rule 10: Integrity IndependenceThe database language should support constraints on user input that maintain database integrity.
Rule 11: Distribution IndependenceA user should be totally unaware of whether or not the database is distributed.
Rule 12: Non Subversion RuleThere should be no way to modify the db structure other than through the multiple row database language.

1.2 DBMS Vs RDBMS

DBMS – Database Management System.The relationship between 2 tables or files is maintained programmatically.

  • Does not support Client/Server Architecture
  • Does not support distributed databases
  • There is no security of data
RDBMS – Relational Database Management SystemThe relationship between 2 tables or files can be specified at the time of table creation
  • Support Client/Server Architecture
  • Support distributed databases
  • There are multiple levels of security
  • Logging in at O/S level
  • Command level
  • Object Level

1.3 Normalization

It is a process that helps in designing the table structure for an application.
Normalization is technique that:
  • Decomposes data into two dimensional tables.
  • Eliminates any relationship in which table data does fully depend upon the primary key of a record.
  • Eliminates any relationship that contains transitive dependencies.

1.3.1 First Normal Form

A table is in 1st Normal Form if:
  • There are no repeating group
  • All the key attributes are defined.
  • All attributes are dependent on a primary key
1.3.2 Second Normal Form
A Table is in 2nd Normal form if:
  • Its in 1st Normal Form
  • It includes no partial dependencies i.e. an attribute is dependent on only a part of a primary key.

1.3.3 3rd Normal Form

A table is in 3rd Normal form if:
  • Its in 2nd Normal Form
  • It contains no transitive dependencies.
Note: A general case of transitive dependencies is as follows:
X, Y, Z are 3 columns in table.
If Z is related to Y
If Y is related to X
Then Z is indirectly related to X
This is when Transitive dependencies exist.
Note: Normalization slow down the database query for fetching records. So sometime to make the query faster denormalization is deliberately done on the table.

1.4 Illustration of Normalization






2 Components of SQL

2.1 DDL, DML & DCL Commands

DDL – Data Definition Language – No Rollback is possible – Commit is issued implicitly.Examples are:
· CREATE
· ALTER
· DROP
· TRUNCATE – Remove all the records from a table, including all spaces allocated for the records are removed
· GRANT
· REVOKE
DML – Data Manipulation LanguageExamples are:
· INSERT
· UPDATE
· DELETE – Deletes all records from a table, the space for the records remain.
· SELECT – more specifically a DQL – data query language.
DCL – Data Control Language
Examples are:
· COMMIT
· ROLLBACK

References

SQL, PL/SQL Third Edition by Ivan Bayross.

Download

Click here to download a PDF copy of this article.
Labels:

Comments

Post a Comment

Popular posts from this blog

Why to do Software Testing?

Regardless of the limitations, testing is an integral part in software development. It is broadly deployed in every phase in the software development cycle. Typically, more than 50% percent of the development time is spent in testing. Testing is usually performed for the following purposes: To improve quality. As computers and software are used in critical applications, the outcome of a bug can be severe. Bugs can cause huge losses. Bugs in critical systems have caused airplane crashes, allowed space shuttle missions to go awry, halted trading on the stock market, and worse. Bugs can kill. Bugs can cause disasters. The so-called year 2000 (Y2K) bug has given birth to a cottage industry of consultants and programming tools dedicated to making sure the modern world doesn't come to a screeching halt on the first day of the next century. [Bugs] In a computerized embedded world, the quality and reliability of software is a matter of life and death. Quality means the conformance to the ...
1 comment
Read more

HP Quality Center - Best Practices

1.Introduction Quality Center is a test management tool which provides very good features for managing both your manual and automated test cases. This paper highlights the best practices for managing your test cases. When you open Quality center, depending on your rights it display the below mentioned option in the sidebar: 1. Requirements 2. Test Plan 3. Test Lab 4. Defects 5. Dashboard 2.Requirements When you have assigned with the responsibility of developing your test cases in the quality center then you must be wondering where to start with. I am going to share my experience to overcome such a situation. You need to find the solution of some question before you start writing your test cases. 1. Is your requirement developed and available? 2. Is your requirement organized in a logical sequence? If answer to both of the above question is Yes, then you can start with Requirement option in the side bar. In case your requirement is under development, then you keep your...
3 comments
Read more

ABC of Unit Testing

What is Unit Testing? After writing any code, every programmer will do some kind of testing to make sure the code works as expected. This testing is called 'unit testing'. Unit testing is done in different ways. Some programmers write simple test applications to test their own code. Some others simply debug the code and change the values during debugging to make sure the code works fine for different cases. Some facts about unit testing: Unit testing is done by developers. Quality department does different tests, they are not unit tests. Every programmer must do unit testing after finishing development or during development itself. Unit testing can be done by writing separate test applications to call your classes and methods to make sure the classes work as expected. This is called manual unit testing. Unit testing can be automated by writing unit test scripts. This is called Automated Unit Testing. In automated Unit Testing, after writing each class/method, you will write sev...
1 comment
Read more