Software Database and Testing

Software Database and Testing

• 1.
  Workshop Software Databases & Testing Lviv, 2013
• 2.
  Agenda items • Introductionto databases ▫ Database models ▫ Types of Keys in Database ▫ Types of Table Relationships ▫ Database Diagram • Structured Query Language ▫ SQL Command Types ▫ SQL Select Command  Joining Tables • Database & Testing ▫ Database Testing ▫ Database migration testing ▫ SQL Tuning or SQL Optimization
• 3.
  Database Models • HierarchicalModel • Network Model • Relational Model • Object/Relational Model • Object-Oriented Model • Semi structured Model • Associative Model • Entity-Attribute-Value (EAV) data model • Context Model
• 4.
  Types of Keysin Database • Primary key • Foreign key • Compound key
• 5.
  Types of TableRelationships A relationship works by matching data in key columns — usually columns with the same name in both tables. In most cases, the relationship matches the primary key from one table, which provides a unique identifier for each row, with an entry in the foreign key in the other table. • One-to-Many Relationships • Many-to-Many Relationships • One-to-One Relationships
• 6.
  Structured Query Language SQL(Structured Query Language) A special purpose programming language designed for managing data in relational database management systems (RDBMS).
• 7.
  SQL Command Types •Data Definition Language (DDL) • Data Manipulation Language (DML) • Data Control Language (DCL) • Transaction Control Language (TCL)
• 8.
  Data Definition Language DataDefinition Language (DDL) statements are used to define the database structure or schema. Some examples: – CREATE - to create objects in the database – ALTER - alters the structure of the database – DROP - delete objects from the database – TRUNCATE - remove all records from a table, including all spaces allocated for the records are removed – COMMENT - add comments to the data dictionary – RENAME - rename an object
• 9.
  Data Manipulation Language DataManipulation Language (DML) statements are used for managing data within schema objects. Some examples:  SELECT - retrieve data from the a database  INSERT - insert data into a table  UPDATE - updates existing data within a table  DELETE - deletes all records from a table, the space for the records remain  MERGE - UPSERT operation (insert or update)  CALL - call a PL/SQL or Java subprogram  EXPLAIN PLAN - explain access path to data  LOCK TABLE - control concurrency
• 10.
  Data Control Language DataControl Language (DCL) statements. Some examples: – GRANT - gives user's access privileges to database – REVOKE - withdraw access privileges given with the GRANT command
• 11.
  Transaction Control Language TransactionControl Language (TCL) statements are used to manage the changes made by DML statements. It allows statements to be grouped together into logical transactions. – COMMIT - save work done – SAVEPOINT - identify a point in a transaction to which you can later roll back – ROLLBACK - restore database to original since the last COMMIT – SET TRANSACTION - Change transaction options like isolation level and what rollback segment to use
• 12.
  SQL Select SELECT [ALL| DISTINCT] columnname1 [,columnname2] FROM tablename1 [,tablename2] [WHERE condition] [ and|or condition...] [GROUP BY column-list] [HAVING conditions] [ORDER BY column-list [ASC | DESC] ]
• 13.
  Database & Testing •Database Testing • Database migration testing • SQL Tuning or SQL Optimization
• 14.
  Thank You allfor attention!  http://qagroup.com.ua/