Intro to Databases (using Microsoft Access)

1. Intro to Databases (using Microsoft Access) Yitzchak Rosenthal

2. Database (DB) • A Database is an organized collection of data that can be accessed and manipulated by computer programs.

3. People who use databases • End Users • Most business people do not usually use a database directly. Rather, business people use programs geared to their specific business needs. “Behind the scenes” those programs store or retrieve data from a database in response to the user’s actions. • Technical IT Professionals (e.g. programmers, database administrators) • IT Professional such as programmers and database administrators need to understand the in-depth issues about databases. These professionals create and maintain the programs that are used by the end users. • Business Analysts (BA) • When a new program needs to be created, the business analysts work with both the “end users” and the “technical IT professionals” to design the programs. The BAs don’t actually create the programs, they just work on the design. BAs must also understand databases concepts. • (See architecture diagram later in this presentation …)

4. Database (DB) vs. DataBase Management System (DBMS) • Database (DB) • A Database contains the actual data. • Database Management System (DBMS) • A Database Management System (DBMS) is the software that is used to add, remove and manage the data in a database. • Difference between DB and DBMS • You can think of a database and a DBMS similar to the way you think about a word processing program (e.g. Microsoft Word) VS a word processing file. The DBMS is similar to the word processing program, while the database is similar to a word processing file.

5. One DBMS can control many different databases • A single “Database Management System” can control many different “databases”. • Individual databases contain information about a particular topic. • EXAMPLE: A telephone company may have the following different databases (as well as many others). These different databases can all be controlled through one DBMS program: • Human Resources Database: contains information about employee name, titles, hire dates, salaries, pension and benefit information, 401K data, etc • Customer and billing database: information about customer names and addresses, billing information, calling patterns, etc • Assets database: information about real estate that the company owns and leases, cell phone towers and leased lines, etc. • Marketing database: information about marketing campaigns, advertising rates, locations of billboards that are being used, times/stations and rates for radio and TV ad spots, data from focus groups, etc. • etc.

6. Categories of Databases (relational, object oriented, hierarchical, network)

7. Relational / Hierarchical / Network / Object • Databases are categorized by how they organize information. • Types of databases • Hierarchical Database model • Network Database model • Object Oriented Database model • Relational Database model

8. Hierarchical Databases • Hierarchical Databases: • Data is organized into a hierarchy, similar to the folder/file hierarchy of the file system. • Hierarchical databases can be faster than an equivalent relational database. However, with modern faster hardware this is not as much of an issue as it originally was. • Hierarchical databases are not used much anymore for storing business data. • IBM’s IMS database is an example of a hierarchical database that is still used, mostly on large mainframe computers.

9. Network Databases • Network databases: • The word “network” in “network database” refers to how the information is organized in the database. (The word “network” is NOT related to the common concept of a computer network.) • Network databases were proposed as an enhancement of the hierarchical database. • Discussion of network databases is primarily academic since they are not really used anymore.

10. Object Oriented Databases • Object Oriented Databases: • Data is organized into information about “objects”. • Object Oriented Databases are designed to be easily integrated with Object Oriented programming languages. • Object oriented programming languages are very popular today. However, object oriented programming languages can ALSO be used with other types of databases. In practice most object oriented programs today use Relational Databases. • Object Oriented databases are the newest type of database. They will probably NOT replace relational databases but their use will probably grow. • An Object-Relational database is an object oriented database that can be implemented on top of a relational database.

11. Relational Databases • Relational Databases: • Data is organized into many “tables” of information. • Vast majority of databases in use today are “Relational” databases. • This is the type of database that we will be studying in depth.

12. Relational DBMS Products

13. Current Relational DBMS Products • Most popular • MySql: “open source” DBMS. Can be downloaded and used for free. Powerful. Companies can purchase a technical support license from MySql, inc. Used a lot by smaller companies. Not as quick to catch on in larger fortune 500 companies. • Microsoft SQL Server – Microsoft’s “high-end” DBMS. More powerful than Access (i.e. faster, can handle more data, more users, better security features, etc). • Microsoft Access – Microsoft’s “low-end” DBMS. Used for smaller applications. Cheaper than SQL Server to buy/license. • Oracle – first commercially successful Relational DBMS. Very powerful. Has majority market share for large DB applications in fortune 500 companies. • Others • Sybase • IBM UDB • IBM Informix • many others …

14. Clients and Servers

15. Client Programs and Server Programs • Client programs and server programs are programs that are designed to communicate with each other. • A client program makes requests of a server program to send some information or perform some action. • The server program performs the requests of the client program and sends the client a response. • The overall architecture of such a system is known as a “client-server” architecture.

16. Example: databases • Example: • In the database architecture described above, the “application program” is acting as a “client program” and the DBMS is acting as a “server program”. • The “application program” requests the DBMS to lookup some information in the database and the DBMS performs the lookup and returns the results to the application program.

17. Other Types of Client And Server Programs

18. Other types of client and server programs • The client server model is used to implement many different types of computer applications. • Examples • Web browsing • Email • Instant Messaging (IM) • Many other examples …

19. Web Browsing • TODO – show architecture diagrams for • Web • Email • IM • File sharing

20. What is a network? servers clients • A network is a group of computers that can communicate with each other directly.

21. What is Internetworking? • any network can communicate with any other network • some communications are direct (e.g. A-B, A-C, C-D) • some communications go through other networks (e.g. B-C, B-D, A-D) Network A Network B servers direct communication line clients Network C Network D

22. The Internet • The Internet "cloud" in the diagram represents many many networks connected to each other. • The internet permits communication (usually indirect) between any two computers attached to the internet. Network A Network B servers The Internet clients Network C Network D

23. Clients and Servers • There are two general classifications of computers (AKA machines) on a network (or the Internet) • clients • servers

24. What Is a Server? • Server Machine • a computer on a network that runs programs ("Server Programs") which provide other computers on the network (i.e. "Client Machines") with services. • Server Program (AKA Server Software) • these are the programs that are run on "server machines" to provide other computers on the network with services • Server • The term "server" is sometimes used to refer to a "server machine" and is sometimes used to refer to a "server program" • In general it is the thing (machine or program) that provides the services. You will be able to tell from context the word refers to the machine or the program.

25. What is a Client? • Client Machine • a computer on a network that communicates with a Server to get some services. • Client Program (AKA Client Software) • a program that runs on the "client machine" which communicates with the server to get some services. • Client • The term "client" is sometimes used to refer to the client machine and sometimes used to refer to the client program • In general it is the thing (machine or program) that uses the services. You will be able to tell from context the word refers to the machine or the program.

26. One server, many clients • In general, one server handles the requests of many clients. • If there are too many clients for a single server then you can usually add a second (third, fourth, etc) server to handle the extra load. server clients

27. Server Types • There are many many types of servers. The following are just a few: • Web Server: Sends web pages to clients that request them. The client program that communicates with a web server is a web browser. • Email server: Delivers email from one client to another client • IM server: Coordinates IM connections • File server: Allows client machines to store files on the server machine's disk drives. (e.g. Z: drive, F: drive, etc) • Print server: Allows different machines to use a single printer. • Network server: This is a "traffic cop" that controls the communications that allow computers on the network to "talk" with each other. • Database Server: Coordinates access to the information in a database. Allows different client programs to read/write/modify information in the database. • Custom Server: For running a custom application. The custom application would also consist of custom client software. For example, a bank may develop an application so that its branches can exchange their financial information in "real time".

28. One computer can run both client programs and server programs at the same time • A single computer can simultaneously run several server programs and client programs. • Example • a person sitting at the console of a database server can run a web browser that gets a web page from a different web server. • In this case the "database server machine" is running a "web client program" (i.e. the browser). • When referring to the database, this machine can be thought of as the server machine and when referring to the web, this machine can be thought of as the client machine. • Another example • The "database server program" might store a file on another machine. • In this case the database server machine is acting as a client to the "file server machine" • Many server programs on one machine • You may have one machine that runs a database server program and a web server program

29. Database Client Server Architecture

30. Database Architecture Diagram • The diagram on the next slide shows the organization of a typical database system. Don’t be worried if you don’t understand the diagram now. The diagram will be referenced and explained throughout the coming slides. • Two computers in the diagram: • The computer on the left is running an “application program” (see later slides for an explanation) • The computer on the right is running DBMS software (see later slides for an explination). The computer running the DBMS software is shown without a screen. This is because the user doesn’t use this computer, rather the application program does. A computer that runs software that is used by other programs is known as a “server computer”. The program that it runs is known as a “server program”. • Symbols in the diagram • A cylinder is generally used to represent a hard drive or a database. • See next slide …

31. Architecture Diagram Application Program running on user’s computer DBMS software running on server computer (this is expressed using SQL, not English) Step 2: What’s Joe Smith’s tel#? Step 1: DB Management System (DBMS) Server Program CLICK Step 4: 938-4763 Tel# Lookup Program Name: Click here to look up # Joe Smith Step 3: DBMS Software finds answer in database on server computer’s hard drive. Click to Lookup # EXPLINATION Step 1: User runs application program on his computer.Step 2: application program sends query to server: “What’s Joe Smith’s tel Number?” (query is actaully expressed using Sequential Query Language (SQL), NOT in English as in picture)Step 3: DBMS Software looks up answer in the database. Step 4: DBMS Software sends answer back to appl. program Step 5 (not shown): application program displays answer to the user. Server Computer’s Hard Drive

32. About the Diagram: DB and DBMS • The diagram above shows two different computers, one the left and one on the right. (The reason why there are 2 computers will be explained a little later.) The computer on the left is known as the “client” computer and the one on the right is the “server” computer. (The terms “client” and “server” will also be explained later.) • The DBMS is shown as a program that is running on the server computer (the computer on the right). • The database appears on the diagram below the server computer (the computer on the right). The cylinder that surrounds the data on the diagram represents the server computer’s hard drive. (Even though the hard drive is usually “inside” the server computer, it is often shown on such diagrams separately).

33. Application Programs

34. Application Programs • People do not usually use a DBMS program directly, rather people use an “application program”. • The application program communicates with a DBMS program to store or retrieve data from a database in response to the user’s actions. • Very often, the application program and the DBMS are running on different computers and communicate with each other using the computer network.[Note that it is possible to have the application program and the DBMS on the same computer. However for large database applications used by businesses, the DBMS is usually on a different computer than the application program is running on. ]

35. About the Diagram: Application Program • The diagram above shows an application program that is running on the computer on the left. • In “Step 1” on the diagram, the application program in the diagram presents a screen for the user to enter a name and click a button to lookup the person’s telephone number. • The arrows for “Step 2” and “Step 4” show the application program and the DBMS communicating over the computer network to get the desired telephone number. The user does not need to know that this part is happening. • In “Step 5” (not shown on the diagram) the “application program” displays the telephone number to the user. It appears to the user that the application program “knows” the telephone number. However, really, the application program just asked for this information from the database management system.

36. Other Types of Database Clients • The following are all systems that use a database • ATM machine – data is stored on the bank’s database. The data is accessible from any ATM machine. • Point of Sale – when you buy items in a supermarket and the cashier “scans” the item – the scanner and the checkout system acts as a client to the database server. The database sends product and pricing information back to the checkout system to produce the bill. The database also updates its inventory records to reflect the fact that the items have been purchased. • “EZ-Pass” – when a car passes through an “ez-pass” toll booth, the information about the car is recorded in a database which is then used to generate billing information. • eCommerce websites – use a database to keep track of product descriptions, customer orders and shipping information • etc.

37. Structured Query Language (SQL)

38. Structured Query Language (SQL) • The application program uses a special computer language, called Structured Query Language (SQL), to communicate with the DBMS program. • SQL is often pronounced as “sequel” • Users of the application program do NOT need to understand SQL. It is the computer programmers who create application programs who must understand how to write SQL.

39. SQL : Inserts, Updates, Deletes, Queries, … • The most common types of SQL requests are: • Inserts: Requests from the application program to add information to the database (DB). • Deletes: Requests from the application program to remove information from the DB. • Updates: Requests from the application program to modify some information that already exists in the DB. • Queries: Requests from the application program to retrieve specific information from the DB. The diagram above shows the application querying the DMBS for Joe Smiths telephone number. This is a simple query that returns a single piece of information. Queries can be much more complex and return a lot of information. An example of a more complex query might be – find the list of names and telephone numbers for those people whose last name begins with “S” and whose telephone number is in the 917 area code.

40. Complete Explanation of Architecture Diagram • Complete Explanation of Architecture Diagram • Step 1: The user runs an application program that allows him to look up people’s telephone numbers. The user types in a name to lookup and then clicks the “Lookup” button. • Step 2: The application needs to get the information so the application program sends a query to the DBMS over the computer network to lookup the information in the database. This query is expressed using the SQL programming language. • Step 3: The DBMS looks up the telephone number in the database. • Step 4: The DBMS sends the information back to the client application program. • Step 5: The application program displays the answer to the user (not shown in the diagram).

41. One Database – Many Application Programs

42. Many Clients at the Same Time • The diagram above only shows a single client computer. • In reality it is more common for many different client computers running application programs to communicate with the DBMS at the same time. • EXAMPLE: Given a database that stores information about flights for an airline. An application program might be created for the ticketing agents to issue tickets to passengers. It is possible for many different ticketing agents to use the application program on their different computers at the same time. Server running DBMS Clients running application programs

43. Different types of application programs. • The information contained in a database can be used by different types of application programs simultaneously. • Example: A database that tracks information about planes and flights for an airline might be accessed by any of the following types of application programs • A program used by ticketing agents to sell tickets for the flights • A different program used by the airplane maintenance staff to determine which planes need to be maintained based on how many miles they have flown so far • A third program used by the payroll department to calculate paychecks based on the number of hours and miles flown by the crew. • Etc.

44. Features of Databases

45. Database vs. Excel • Many students wonder why a database is useful more than an Excel file would be for storing data. • The following slide lists some features of modern databases that cannot be easily accomplished with a program such as Excel

46. Features of Modern Databases • Automated storage, retrieval and manipulation of the data - programs can access the data, not just people • Network accessible. Data can be accessed over a network by several users/programs at the same time • Able to process HUGE amounts of data – generally, the amount of data that data database can process is limited only by the amount of disk space on the computer. • Efficient (i.e. fast) storage, retrieval and manipulation of the data • Flexible querying capability – You can quickly summarize and reorganize the data in the database in many different ways.

47. Features of Modern Databases (continued) • Validation features. Has features for preventing “bad data” from being entered into the database (e.g. typos or illogical information such as a person’s age being recorded as 237, or as a word such as “blue”, etc). Databases can be setup with rules to describe what is considered valid data. Invalid data will not be accepted into the database. • Durable data. Modern databases have features to ensure that data will remain valid and uncorrupted even in the face of catastrophic errors such as a power outage.

48. Features of Modern Databases (continued) • Security. “Authentication” means that before accessing the data, a user must “login” or otherwise identify who they are. If a user has already logged into the operating system (e.g. windows) then many databases can identify the user based on how he logged into the operating system. Authentication is necessary to allow the DBMS to enforce security of the data. The database can be setup with rules to indicate which users can access which data. For example a database might store information about employees in a company. The names and telephone numbers might be available to every employee in the company. However, salary data might only be available to employees in the human resources department.

49. Overview of Databases using Access 2007

50. What is “Microsoft Access”? • What is “Microsoft Access”? • Access is a Database Management System (DBMS) program (see above for an explanation of what a DBMS is). • Access contains features to allow designers of database applications to create and manage the database. This includes the ability to create • “Tables” to store the data • “Queries” that extract and display information from the tables • “Reports” that display the information from a query in a “fancy” formatted way • It is important to remember that even though Access provides the ability to run queries directly, often the queries are started by an application program, and not directly by a user.