1 / 71

Microsoft VB 2005: Reloaded, Advanced

Microsoft VB 2005: Reloaded, Advanced. Chapter 7 Relational Databases and SQL. Objectives. Discuss the structure of a client/server system Explain how data are stored in a relational database Create queries using the structured query language (SQL)

naida-koch
Download Presentation

Microsoft VB 2005: Reloaded, Advanced

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. Microsoft VB 2005: Reloaded, Advanced Chapter 7 Relational Databases and SQL

  2. Objectives • Discuss the structure of a client/server system • Explain how data are stored in a relational database • Create queries using the structured query language (SQL) • Execute queries on a database using Microsoft Access and Microsoft SQL Server • Create a database using Microsoft SQL Server Microsoft VB 2005: Reloaded, Advanced

  3. Overview of Data, Systems, and ADO.NET • Objective • Understand how data are usually organized and how information systems interact with data Microsoft VB 2005: Reloaded, Advanced

  4. Data • Data • Plural of the word “datum” • Single item of factual knowledge obtained from measurement or observation • An entire collection of factual knowledge • Data can be stored in a computer file for use by a computer-based application Microsoft VB 2005: Reloaded, Advanced

  5. Data (continued) Microsoft VB 2005: Reloaded, Advanced

  6. Data (continued) Microsoft VB 2005: Reloaded, Advanced

  7. Information • Information • Represents knowledge obtained by processing data Microsoft VB 2005: Reloaded, Advanced

  8. Information (continued) Microsoft VB 2005: Reloaded, Advanced

  9. Data Files • Record • A complete set of data • Field • A space reserved for a datum • Flat files • Contain only simple records and fields • That could be written on a flat (two-dimensional) piece of paper Microsoft VB 2005: Reloaded, Advanced

  10. Data Files (continued) Microsoft VB 2005: Reloaded, Advanced

  11. Databases and Database Management Systems • Data store • Any organized collection of data • Database • Data store that has a special structure for facilitating storage, retrieval, and manipulation of the data • Database management system (DBMS) • The software used to create and access a database • Relational database • Database that follows the relational model • Most popular type of database in use today Microsoft VB 2005: Reloaded, Advanced

  12. Databases and Database Management Systems (continued) • Relational model • A way of organizing data in related tables based on strict mathematical principles • Relational database management system • Creates and manages a relational database • Client/server system • System in which software is distributed across networked hardware components consisting of: • Clients that send requests for services • A server that provides responses to the requests Microsoft VB 2005: Reloaded, Advanced

  13. Databases and Database Management Systems (continued) Microsoft VB 2005: Reloaded, Advanced

  14. Databases and Database Management Systems (continued) • Client/server system (continued) • Clients store the application and the server stores the database • Database server • Server that stores the database and DBMS • Server software components • Network operating system • The DBMS • Client software components • Application software • Data access API Microsoft VB 2005: Reloaded, Advanced

  15. Databases and Database Management Systems (continued) Microsoft VB 2005: Reloaded, Advanced

  16. Databases and Database Management Systems (continued) • Client/server system (continued) • Data access application programming interface (API) • Software that provides an interface between the application and the database • ADO and ADO.NET • ADO.NET • A data access API • Part of the .NET Framework • Before ADO.NET existed, there was ADO Microsoft VB 2005: Reloaded, Advanced

  17. Databases and Database Management Systems (continued) • ADO and ADO.NET (continued) • ADO.NET advantages • Ability to work with data represented in XML • Integration with the rest of the Microsoft .NET Framework • Increased control and flexibility in working with all types of data sources • ActiveX Data Objects (ADO) • Microsoft technology for accessing data from a wide variety of data stores • In both Windows applications and Web applications Microsoft VB 2005: Reloaded, Advanced

  18. The Relational Database Model Microsoft VB 2005: Reloaded, Advanced

  19. The Relational Database Model (continued) Microsoft VB 2005: Reloaded, Advanced

  20. Tables • Data in a relational database are stored in tables • A table is a structure consisting of rows and columns • Each individual data item is placed in one cell • An intersection of a row and a column • Each row in a table represents a data record • Each column in a table represents a data field Microsoft VB 2005: Reloaded, Advanced

  21. Tables (continued) Microsoft VB 2005: Reloaded, Advanced

  22. Primary Keys • Each record in a table must be uniquely identified • Primary key • One or more fields in a database table that, taken in combination, form a unique value • Uniquely identify a record Microsoft VB 2005: Reloaded, Advanced

  23. Relationships • The power of a relational database lies in the relationships between tables • Each table in a relational database should represent only one kind of entity • Should contain only the fields that are absolutely essential for describing that entity • Entity-relationship diagram • Pictorial representation of the entities (tables), the entity attributes (fields), and the relationships between the entities in a relational database Microsoft VB 2005: Reloaded, Advanced

  24. Relationships (continued) Microsoft VB 2005: Reloaded, Advanced

  25. Relationships (continued) • Foreign key • A primary key of one table included as a field of another table • Cardinality of a relationship between Tables A and B • Number of rows in Table B that can be associated with each row in Table A • Symbol “1” represents one element • Symbol “∞” represents many elements Microsoft VB 2005: Reloaded, Advanced

  26. Relationships (continued) Microsoft VB 2005: Reloaded, Advanced

  27. Structured Query Language • Structured query language (SQL) • Standard language used to store, retrieve, and manipulate data within a relational database Microsoft VB 2005: Reloaded, Advanced

  28. The SELECT Statement • Query • Set of instructions for extracting data from a database Microsoft VB 2005: Reloaded, Advanced

  29. The SELECT Statement (continued) Microsoft VB 2005: Reloaded, Advanced

  30. The SELECT Statement (continued) Microsoft VB 2005: Reloaded, Advanced

  31. The WHERE Clause Microsoft VB 2005: Reloaded, Advanced

  32. The WHERE Clause (continued) Microsoft VB 2005: Reloaded, Advanced

  33. The ORDER BY Clause Microsoft VB 2005: Reloaded, Advanced

  34. The ORDER BY Clause (continued) Microsoft VB 2005: Reloaded, Advanced

  35. Merging Data with INNER JOIN • Inner join • Derives information from two or more tables Microsoft VB 2005: Reloaded, Advanced

  36. Merging Data with INNER JOIN (continued) Microsoft VB 2005: Reloaded, Advanced

  37. Merging Data with INNER JOIN (continued) Microsoft VB 2005: Reloaded, Advanced

  38. Merging Data with INNER JOIN (continued) Microsoft VB 2005: Reloaded, Advanced

  39. Merging Data with INNER JOIN (continued) Microsoft VB 2005: Reloaded, Advanced

  40. Merging Data with INNER JOIN (continued) Microsoft VB 2005: Reloaded, Advanced

  41. The INSERT Statement • Adds data into a database Microsoft VB 2005: Reloaded, Advanced

  42. The INSERT Statement (continued) Microsoft VB 2005: Reloaded, Advanced

  43. The INSERT Statement (continued) Microsoft VB 2005: Reloaded, Advanced

  44. The UPDATE Statement • Changes the contents of existing records Microsoft VB 2005: Reloaded, Advanced

  45. The UPDATE Statement (continued) Microsoft VB 2005: Reloaded, Advanced

  46. The DELETE Statement • Deletes rows from a table Microsoft VB 2005: Reloaded, Advanced

  47. The DELETE Statement (continued) Microsoft VB 2005: Reloaded, Advanced

  48. Executing SQL Statements in Microsoft Access Microsoft VB 2005: Reloaded, Advanced

  49. Using Microsoft SQL Server 2005 • SQL Server • Relational database management system (RDBMS) • Microsoft SQL Server 2005 Express Edition • Available for free at: http://msdn.microsoft.com/vstudio/express/sql/ Microsoft VB 2005: Reloaded, Advanced

  50. Using Microsoft SQL Server 2005 (continued) Microsoft VB 2005: Reloaded, Advanced

More Related