22_Database.ppt

Chapter 22 – Database: SQL, MySQL, DBI and ADO.NET Chapter 22 – Database: SQL, MySQL, DBI and ADO.NET Objectives In this lesson, you will learn: To understand the relational database model. To be able to write database queries using SQL (Structured Query Language). To understand the MySQL database server. To learn various database interfaces. To understand ADO.NET’s object model. 22.1??Introduction Database Integrated collection of data Database management system (DBMS) Store and organize data consistent with database’s format Relational database SQL (Structured Query Language) Queries Manipulate data 22.2??Relational Database Model Composed of tables Row Number column Primary key Reference data in the table A column or set of columns in table contains unique data 22.2??Relational Database Model 22.2??Relational Database Model 22.3??Relational Database Overview: Books.mdb Database Primary key uniquely identifies each row Rule of Entity Integrity Composite primary key Lines connecting tables Relationships One-to-many relationship Foreign key Join multiple tables Rule of Referential Integrity 22.3??Relational Database Overview: Books.mdb Database 22.3??Relational Database Overview: Books.mdb Database 22.3??Relational Database Overview: Books.mdb Database 22.3??Relational Database Overview: Books.mdb Database 22.3??Relational Database Overview: Books.mdb Database 22.3??Relational Database Overview: Books.mdb Database 22.3??Relational Database Overview: Books.mdb Database 22.4??SQL (Structured Query Language) 22.4.1?Basic SELECT Query SELECT * FROM tableName SELECT * FROM Authors SELECT authorID, lastName FROM Authors 22.4.1?Basic SELECT Query 22.4.2?WHERE Clause Specify selection criteria for query SELECT columnName1, columnName2, … FROM tableName WHERE criteria SELECT title, editionNumber, copyright FROM Titles WHERE copyright 1999 LIKE Pattern matching Asterisk ( * ) SELECT authorID, firstName, lastName FROM Authors WHERE lastName LIKE ‘D*’ Question mark ( ? )