1 / 30

DBMS

DBMS. Database Management Systems. DBMS.

yoshio-zamora
Télécharger la présentation

DBMS

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. DBMS Database Management Systems

  2. DBMS • A collection of programs that enables you to store, modify, and extract information from a database. There are many different types of DBMSs, ranging from small systems that run on personal computers to huge systems that run on mainframes. The following are examples of database applications: • Computerized library systems • Automated teller machines • Flight reservation systems • Computerized parts inventory systems

  3. DBMS • One of the most important tools in business and Management Information Systems (MIS) • Changed the way computer applications are developed • Changing the way that companies are managed

  4. DBMS • The database approach begins with the premise that the most important aspect of the computer system is the data that it stores. • The purpose of a database management system is to provide shared access to data, answer questions, and create reports from the data.

  5. Relational Databases • The goal of a relational DBMS is to make it easy to store and retrieve needed data. All data is stored in tables, which consist of columns with rows of data. • Each table has a name and represents objects or relationships in the data. For instance, most businesses will have tables for customers, employees, orders, and inventory.

  6. Relational Databases • Tables • Rows • Columns • Primary keys • Data types • Text • Dates & times • Numbers • Objects Customer Table Phone Name Address City 312-555-1234 Jones 123 Main Chicago 502-555-8876 Smith 456 Oak Glasgow 602-555-9987 Juarez 887 Riviera Phoenix 612-555-4325 Olsen 465 Thor Minneapolis Orders Table Customer Date Salesperson Total_sale 502-555-8876 3/3/04 2223 157.92 602-555-9987 4/4/04 8876 295.53 612-555-4325 4/9/04 8876 132.94 502-555-8876 5/7/04 3345 183.67

  7. DBMS • Requests for information from a database are made in the form of a query, which is a stylized question. For example, the query SELECT ALL WHERE NAME = "SMITH" AND AGE > 35 Requests all records in which the NAME field is SMITH and the AGE field is greater than 35. • The set of rules for constructing queries is known as a query language. • Different DBMSs support different query languages, although there is a semi-standardized query language called SQL(structured query language). • Sophisticated languages for managing database systems are called fourth-generation languages, or 4GLs for short.

  8. QUERY There are three general methods for posing queries: • Choosing parameters from a menu: In this method, the database system presents a list of parameters from which you can choose. This is perhaps the easiest way to pose a query because the menus guide you, but it is also the least flexible. • Query by example (QBE): In this method, the system presents a blank record and lets you specify the fields and values that define the query. • Query language: Many database systems require you to make requests for information in the form of a stylized query that must be written in a special query language. This is the most complex method because it forces you to learn a specialized language, but it is also the most powerful.

  9. QUERY BY EXAMPLE In database management systems, query by example (QBE) refers to a method of forming queries in which the database program displays a blank record with a space for each field. You can then enter conditions for each field that you want to be included in the query. For example, if you wanted to find all records where the AGE field is greater than 65, you would enter >65 in the AGE field. *QBE systems are considered easier to learn than formal query languages.

  10. Single Table Query Introduction CID Name Phone City AccountBalance 28764 Adamz 602-999-2539 Phoenix 197.54 87535 James 305-777-2235 Miami 255.93 44453 Kolke 303-888-8876 Denver 863.39 29587 Smitz 206-676-7763 Seattle 353.76 Sample Data Query: Which customers have balances greater than $200? Access Query Screen (QBE)

  11. Computations • Sum • Avg • Min • Max • Count • StDev • Var SQL SELECT Count(C#), AVG(AccountBalance) FROM Customers ; QBE

  12. RECORD • In database management systems, a record is a complete set of information. • Records are composed of fields, each of which contains one item of information. • A set of records constitutes a file. For example, a personnel file might contain records that have three fields: a name field, an address field, and a phone number field.

  13. RECORD

  14. FIELD • A space allocated for a particular item of information. • A tax form, for example, contains a number of fields: one for your name, one for your Social Security number, one for your income, and so on. • Fields are the smallest units of information you can access. • In spreadsheets, fields are called cells. Most fields have certain attributes associated with them. • For example, some fields are numeric whereas others are textual, some are long, while others are short. In addition, every field has a name, called the field name.

  15. FIELDS • In database management systems, a field can be required,optional, or calculated. • A required field is one in which datamust be entered. • An optional field may be left blank. • A calculated field is one whose value is derived from some formula involving other fields. You do not enter data into a calculated field; the system automatically determines the correct value. • A collection of fields is called a record.

  16. Multiple Tables Orders Customers OrderID CID SID Odate Amount 117 12345 887 3/3/2004 $57.92 125 87535 663 4/4/2004 $123.54 157 12345 554 4/9/2004 $297.89 169 29587 255 5/5/2004 $89.93 178 44453 663 5/1/2004 $154.89 188 29587 554 5/8/2004 $325.46 201 12345 887 5/28/2004 $193.58 211 44453 255 6/9/2004 $201.39 213 44453 255 6/9/2004 $154.15 215 87535 887 6/9/2004 $563.27 280 28764 663 5/27/2004 $255.32 CID Name Phone City AccountBalance 12345 Jones 312-555-1234 Chicago $197.54 28764 Adams 602-999-2539 Phoenix $526.76 29587 Smitz 206-656-7763 Seattle $353.76 44453 Kolke 303-888-8876 Denver $863.39 87535 James 305-777-2235 Miami $255.98 Salespeople SID Name DateHired Phone Commission 225 West 5/23/75 213-333-2345 5 452 Zeke 8/15/94 213-343-5553 3 554 Jabbar 7/15/91 213-534-8876 4 663 Bird 9/12/93 213-225-3335 4 887 Johnson 2/2/92 213-887-6635 4 ItemsSold OID ItemID Quantity 117 1154 2 117 3342 1 117 7653 4 125 1154 4 125 8763 3 157 7653 2 169 3342 1 169 9987 5 178 2254 1 Items ItemID Description Price 1154 Corn Broom $1.00 2254 Blue Jeans $12.00 3342 Paper Towels--3 rolls $1.00 7653 Laundry Detergent $2.00 8763 Men's Boots $15.00 9987 Candy Popcorn $0.50

  17. Linking Tables The Orders to ItemsSold relationship enforces referential integrity. One Order can list many ItemsSold.

  18. CELLS • In spreadsheet applications, a cell is a box with a single piece of data. • The data is usually text, a numeric value, or a formula. The entire spreadsheet is composed of rows and columns of cells. • A spreadsheet cell is analogous to a field in database management systems. • Individual cells are usually identified by a column letter and a row number. For example, D12 specifies the cell in Column D and Row 12.

  19. FORMULAS • In spreadsheet applications, a formula is an expression that defines how one cell relates to other cells. • For example, you might define Cell C5 (Column C, Row 5) with the formula =A4*D7 which means multiply the value in Cell A4 by the value in Cell D7

  20. DBMS • The information from a database can be presented in a variety of formats. Most DBMSs include a report writerprogramthat enables the output of data in the form of a report. • Many DBMSs also include a graphics component that enables the output of information in the form of graphs and charts.

  21. Sample Report with Groups

  22. QUERY LANGUAGE • Query - a specialized language for requesting information from a database. • For example, the query SELECT ALL WHERE age > 30 AND name = "Smith" requests all records in which the Name field is "Smith" and the Age field has a value greater than 30. • The de facto standard for query languages is SQL

  23. E-Business Databases Order Form Descriptions Prices Web page Internet Customer Page request Web Server Web program script <HTML> Text <% Database connection %> Queries and data

  24. Database Servers • Specially designed computers that hold the actual databases and run only the DBMS and related software • Usually multiprocessor computers, with RAID disk arrays used for stable storage • Connected to one or more servers via a high-speed channel • Hardware database accelerators are also used in large volume transaction processing environments

  25. Database Servers • A database server is a computer or program running on a computer which provides database services to other programs or computers. They typically provide a complete database management system of some sort, even if limited to only basic database creation, repair and deletion. • An alternative approach is to build the support for the information storage directly into the programs which are using it. This is common on less powerful computer systems or for applications which don't have complex requirements. • In the Internet context, it's very common for web sites to have web servers delivering pages which have their core content stored in a database server.

  26. Database Programs Used at ECU • MVS (for registering students), SQL (Structured Query Language), DB2 (Database 2), Oracle, Access, and more • ITCS is in charge of programming databases and keeping them up and running. • Network support is in charge of keeping the databases connected to the network. • Student, faculty, and staff data are automatically updated on server at input.

  27. What is SQL? • SQL stands for Structured Query Language • Allows access to a database • Is an ANSI standard computer language • Can execute queries against a database • Can retrieve data from a database • Can insert new records in a database • Can delete records from a database • Can update records in a database • Is easy to learn

  28. SQL • SQL is an ANSI (American National Standards Institute) standard computer language for accessing and manipulating database systems. • SQL statements are used to retrieve and update data in a database. SQL works with database programs like MS Access, DB2, Informix, MS SQL Server, Oracle, Sybase, etc. • There are many different versions of the SQL language • To be in compliance with the ANSI standard, they must support the same major keywords in a similar manner (such as SELECT, UPDATE, DELETE, INSERT, WHERE, and others).

  29. ACCESS Access 2003 provides a powerful set of tools that are sophisticated enough for professional developers, yet easy to learn for new users. Create or use powerful database solutions that make organizing, accessing, and sharing information easier than ever. MICROSOFT ACCESS TOUR

  30. REFERENCES • http://www.bambooweb.com/articles/d/a/Database_management_system.html • http://www.webopedia.com/TERM/D/database_management_system_DBMS.html • Management Information Systems. Post/Anderson. 3rd Edition. • Rick Lupton, ECU ITCS, Computer Consultant, Brody School of Medicine • http://www.w3schools.com/sql/sql_intro.asp

More Related