Database, SQL, & ADO.Net

1. Database, SQL, & ADO.Net Chapter 19 VBAN09

2. Quote for Today Get your facts first, and then you can distort them as much as you please. Mark Twain It is a capital mistake to theorize before one has data. Arthur Conan Doyle VBAN09

3. Database Systems • An integrated collection of related data • Provide file-processing capabilities • Organize data to enhance retrieval of data in meaningful ways • Query • a question • a request for information based on specific criteria • SQL - Structured Query Language (pronounced sequel) • used universally to query databases VBAN09

4. Hierarchy of a DBMS DBMS (DataBase Management System)      made up of one or more databases Databases Tables/Queries/Forms/Reports (also known as files in other databases) Records Fields Characters VBAN09

5. Relational Database Systems • Microsoft SQL Server • Oracle ™ • Sybase ™ • DB2 ™ • Informix ™ • MySQL™ • Microsoft Access VBAN09

6. Database System Advantages • Redundancy can be reduced • Inconsistencies can be avoided • Data can be shared • Standards can be enforced • Security restrictions can be applied • Integrity can be maintained • Conflicting requirements can be balanced VBAN09

7. Database Management System • (DBMS) - where the actual manipulation of the database occurs. • This separation from the user interface simplifies the design process. Conceptual layers of a database End User Application Software Database management system Actual database Data seen in terms of the application Data seen in terms of a database model Data seen in its actual organization “User Interface” VBAN09

8. Data Independence • Applications are separate from how the data is physically stored or accessed. • Makes it easy to have different views of the same data by different applications. • Data Dependence • is an application where the storage structure and accessing strategy cannot be changed without affecting the application significantly. VBAN09

9. Database Languages • Users interact with data in databases through the use of database languages. • Applications can use higher-level languages to interface with databases. • VB, C, C++, Java, COBOL, PL/I or Pascal • Queries of the database are made through the use of a query language • Structured Query Language (SQL)

10. Host Languages • Query languages that make it easy to express requests in the context of a particular application. • Each host language ordinarily includes a Database Sublanguage (DSL) VBAN09

11. Database Sublanguage (DSL) • Concerned with the specifics of database objects and operations • combination of 2 languages • Data Definition Language (DDL) - facilities for defining database objects • Data Manipulation Language (DML) - provides features for specifying the processing to be performed on database objects. • SQL includes both DDL and DML VBAN09

12. Distributed Database • A database that is spread throughout the computer systems of a network. • Provides the control and economics of local processing with the advantages of information accessibility over a geographically dispersed organization. • Each data item is stored at the location in which it is most frequently used • The item remains accessible to other network users. VBAN09

13. DD - Disadvantages • Costly to Implement • Costly to Operate • Vulnerable to security violations VBAN09

14. Distributed Databases - Advantage 1 • Without a DBMS, the application software would have to know where all of the pieces of the database were stored. • With a DBMS, the application software can be written as if the database were all on one machine. Concept of one database Pensacola Ft. Walton Milton database database database Machine 1 Machine 2 Machine 3 VBAN09

15. Payroll User Application Software Inventory User User Interfaces DD - Advantage 2 • Separating the application software from the DBMS helps control access to the database. • By providing different interfaces for different users, access is controlled. Ex. Payroll and Inventory personnel require different parts of the database. By providing each group a different user interface, access is restricted to other areas of the database. VBAN09

16. Old vs. New File-oriented Information System Old Customer Records Payroll Records Employee Records Inventory Records Customer Service Dept. Payroll Dept. Personnel Dept. Purchasing Dept. NEW Database-oriented Information System Consolidated Database Customer Service Dept. Purchasing Dept. Payroll Dept. Personnel Dept. VBAN09

17. DD - Advantage 3 • The organization of the database can be changed without changing the application software. • Supports the concept of data independence. • Only the schema used by the central system and the subschemas to those users involved in the change are impacted. VBAN09

18. DD - Advantage 4 • Software at the application level is written from a simplified, conceptual view of the database rather than the actual, complex, detailed view of the database. • VB frequently provides the user interfaces to the database. • The database can be written in several different database languages. VBAN09

19. Relational Database Model • Logical representation of data that allows relationships among data to be considered without concern for the physical structure of the data. • Popular Database Models • Hierarchical Database • Network Database • Relational Database - most popular VBAN09

20. Relational Database • A logical representation of the data that allows the relationships between the data to be considered without concerning oneself with the physical implementation of the data structures.Codd, 1988 • Composed of tables VBAN09

21. Tables • Related data divided up into • fields (columns) and • records (rows) • Should contain a Primary Key • An identifier that is unique to each record • Examples include SSN, Vehicle ID #’s, Employee ID #’s, Course #’s, Reference #’s. • Usually are associated with an ordering • Ascending or Descending VBAN09

22. Example Fields CourseNo RefNo Instructor Campus CGS1100 15689 McManus DL CGS1100 15690 McManus FWB CGS1100 15692 McManus FWB CGS1100 15693 McManus FWB CGS1570 15706 McManus FWB CGS1570 15707 McManus DL CIS1000 15735 McManus DL COP2011 17055 McManus Niceville COP2010 17056 McManus Niceville Records Primary Key VBAN09

23. Projections & Joins • Projection • Selecting a subset of fields from a set of records • Filters and Queries • Join • Combining selected fields from multiple tables. • Queries • Both are accomplished through SQL. VBAN09

24. Advantages over Hierarchical & Network Schemes 1. Tabular representation is easier for users to understand and easier to implement in the physical database system. 2. Easy to convert other database structures into a relational scheme. 3. Projection and join operations are easy to implement and make the creation of new tables easy. VBAN09

25. Advantages cont. 4. Searches are generally faster than when using schemes that use pointers. 5. Relational structures are easier to modify.Advantage when flexibility is an issue. 6. Database clarity and visibility.Tabular format is easier for the user to search than when using more complex structures. VBAN09

26. Primary Keys • Uniquely identifies each record in the table. • Rule of Entity Integrity • Every record must have a value in the primary key field • The value must be unique • Duplicates -- not allowed VBAN09

27. Foreign Keys • A field in a table for which every entry has a unique value in another table and where the field in the other table is the primary key for that table. • Rule of Referential Integrity • every value in a foreign key field must appear in another table’s primary key field • Provide the means of joining multiple tables VBAN09

28. SQL Query Keywords VBAN09

29. SQL Commands • SELECT • Chooses information from one or more tables in a database. SELECT * FROM TableName • The * indicates that all fields from TableName should be selected SELECT Au_ID, LastName FROM Authors VBAN09

30. SQL Commands • WHERE Clause • Provides the selection criteria • Records must match the criteria in order to be selected SELECT * FROM Authors WHERE [Year Born] >= 1950 SELECT * FROM Authors WHERE Author Like ‘d[a-e]*’ VBAN09

31. SQL Commands • ORDER BY Clause • Query results are sorted in either ascending or descending order SELECT * FROM Authors ORDER BY Author ASC SELECT authorID, firstName, lastName FROM Authors ORDER BY lastName, firstName ASC VBAN09

32. SQL Commands • INNER JOIN • Merges Data from Multiple Tables SELECT * FROM Authors INNER JOIN [Title Author] ON Authors.Au_ID = [Title Author].Au_ID The dot notation is necessary when the same field exists in two separate tables. VBAN09

33. SQL Commands • INSERT • Inserts a new record in a table INSERT INTO tableName (field1, field2, …, fieldNameN ) VALUES (value1, value2, …, valueN ) INSERT INTO Authors ( firstName, lastName ) VALUES (‘Sue’, ‘Smith’ ) VBAN09

34. SQL Commands • UPDATE • Modifies data in a table UPDATE tableName SET field1 = value1, field2 = value2, …, fieldN = valueN WHERE criteria UPDATE Authors SET lastName = ‘Jones’ WHERE lastName = ‘Smith’ AND firstName = ‘Sue’ VBAN09

35. SQL Commands • DELETE • Removes data from a table DELETE FROM tableName WHERE criteria DELETE FROM Authors WHERE lastName = ‘Jones’ AND firstName = ‘Sue’ VBAN09

36. Biblio.mdb Predefined Query SELECT Titles.Title, Titles.ISBN, Authors.Author, Titles.[Year Published], Publishers.[Company Name] FROM Publishers INNER JOIN (Authors INNER JOIN ([Title Author] INNER JOIN Titles ON [Title Author].ISBN = Titles.ISBN) ON Authors.Au_ID = [Title Author].Au_ID) ON Publishers.PubID = Titles.PubID ORDER BY Titles.Title VBAN09

37. ADO.Net VBAN09

38. DataSet DataAdapter SelectCommand Data Tables DataTable DataTable InsertCommand Data Rows Data Rows Connection Data Columns Data Columns DeleteCommand UpdateCommand Data Relations Your Code Database ADO.Net Data Objects VBAN09

39. Next? XML & ASP VBAN09