1 / 36

Databases Management Systems

Databases Management Systems. CIS105. Brain Teaser…. Draw a pyramid using 10 dots (see next slide) After moving to the next slide, don’t click until you are ready to have the answer revealed. Invert the pyramid so that the peak is at the bottom by moving as few dots as possible. Topics.

Télécharger la présentation

Databases Management Systems

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. Databases Management Systems CIS105

  2. Brain Teaser… • Draw a pyramid using 10 dots (see next slide) • After moving to the next slide, don’t click until you are ready to have the answer revealed. • Invert the pyramid so that the peak is at the bottom by moving as few dots as possible.

  3. Topics • Explain the differences between File Management Systems and Database Management Systems • Describe the concept of data integrity • Describe the functions of a database management system • Describe some key items to consider when designing and building a database • Compare information systems such as TPS, MIS, DSS, and EIS

  4. File Management System (FMS) • Using the operating system’s features to manage files and data (ie: Windows Explorer).

  5. File Management System - limitations • User manually creates folders and files. • User needs to remember what data is stored in each file. • The operating system does NOT know which files are related to each other. • Will require programming to create relationships and random access. • Ad-hoc queries are almost impossible – need to write a program. • Not too efficient.

  6. Data Organization and Access Methods for FMS • Sequential File Organization • Records are stored in the order they were entered. • If a particular record is desired, all prior records must be read first. • Tape storage uses sequential organization • Random File Organization • Go directly to desired record by using a key. • Organization may be... • Direct File Organization • Indexed File Organization • (see next slides).

  7. Direct File Organization • Must be created using a programming language and must be supported by the operating system. • Location on storage device is usually determine by hashing (randomizing algorithm).

  8. Indexed File Organization • Combines elements of sequential and direct methods. • Records stored sequentially, but file contains an index for direct access. • Index is sorted and contains record key. • Data is directly accessed using the key. • May have more than one index (Customer Id, Last Name). • More efficient to use a database management system that incorporates all of these features...

  9. Database Management Systems (DBMS) • Software that allows us to • Create a database • Maintain data (add, change, delete) • Query data (retrieve and sort) • Create forms and reports • Access is the software used for this class • Relational products are known as Relational Database Management Systems (RDBMS). • Now most DBMS products are relational, so they may be referred to as DBMS or RBDMS.

  10. Relational Model • 1969, Dr. Edgar Codd developed the Relational Model of Data. • Application of math theory. • Relational databases became popular because they: • can be “easily” modified and expanded. • relationships between tables can “easily” be defined. • data can “easily” be queried

  11. Microsoft Access is a RDBMS • Database - a collection of related tables and objects • Table – a collection of records for a single subject • Query – selects data based on selection criteria • Form – simplifies data entry by displaying one record at a time • Report – formatted information suitable for distribution (read-only)

  12. Hierarchy of Data (DBMS) • Data is organized in layers, and each higher level of data consists of one or more items from the lower level. • File – contains a collection of related tables • Tables – a collection of records (rows) for a single subject • Record (rows) – a group of related fields that describe a person, place, or thing • Fields (columns) – store characters for specific item (acct #,name, address) • Characters – are defined using bits.

  13. Database Design • Databases must be designed and developed before data can be entered. • During design consider the needs: • What data needs to be collected? • What reports will be needed? • What queries you may want to make? • We usually begin with the end in mind (Covey). • If we know the required output, we can identify the required input and processing (computations). • Much time should be spent in the design phase, before anything is developed. It will save time and money. • During the design phase, and ERD is developed…

  14. Entity Relationship Diagram (ERD) See next slide…

  15. Entity Relationship Diagram (ERD) • Databases designs are depicted in an ERD. • ERD shows: • Tables • Fields (columns) • Relationships • Primary keys – uniquely identifies record (Id) • Foreign keys – used to link from one table back to the primary key of another table. • Index – a field that sorted to facilitate fast searches (Name) • The data types of the fields will need to be determined before building (next slide).

  16. Data Types • The data typeof each field determines what kind of data can be entered into the field.

  17. Eliminate Data Redundancy during Design

  18. Data Management • Data Management – the procedures used to acquire, access, and maintain data. • Data will be used to provide information that is used to make decisions. • Data integrity – the degree to which data is accurate and reliable. • Defining validation rules increases data integrity (see next slide).

  19. Data entered into the felds can be checked by the DBMS by specifying Validation Rules Alphabetic or numeric check Completeness check Range and consistency check Required entry Data Validation

  20. Data Maintenance • Three basic operations • Entering new data • Updating existing data • Some systems record data before and after change • Deleting data • May need to archive data before removing • Some systems set record to inactive instead of removing it • These operations can be perform via • Datasheet View • Forms • Custom Programming Datasheet Form

  21. Structured Query Language (SQL) • Pronounced by saying the 3 letters S-Q-L, or as see-quell. • SQL is a language included in a RDBMS, and it is used to maintain (insert, update, delete) and query (select) a database. • There is a basic set of SQL commands that are common in all RDBMS, but many vendors then extend the language. • Because of SQL, 3rd party companies can develop products that will work with many different database such as Crystal Reports (reporting tool) by Business Objects. • Such reporting tools use the data dictionary to get to the data.

  22. Database Query • A query is a request for specific data from the database. • The query would list the columns you want to display. • The records display may be limited by entering selection criteria (select all Freshmen). • A Wildcard is a special character (* or ?) inserted in the selection criteria and it used to search for a pattern of characters. For example: lastName Like “Sm*” would select all last names that begin with Sm: Smitts, Smith, etc. • Common queries can be saved for future use or to use it to create a report or mailing labels. • The data selected by the query is not saved with the query – many confuse a query with a table. • A query is dynamic – each time a query is executed, it is processed against the data currently stored in the database, so the results are always up-to-date. • Query-by-Example (QBE) uses a graphical interface or a form to generate the SQL for users.

  23. Mail Merge • A Mail merge is when a form letter and a list of names and addresses are used to create personalized letters. Form Letter in Word or an Access Report Personalized letterfor each individualin the database. Names andAddresses

  24. DBMS Key Features • Reduces data redundancy • Query capabilities • Security – can be set at the table and field levels. • Read-only privileges • Update privileges • Backup & recovery tools • Concurrent access to data (multi-user) • Data dictionary (next slide)

  25. Data Dictionary • Data Dictionary contains data or documentation about the tables and fields included in the database. • The data dictionary is automatically created as the tables and columns are defined and documented. • Contains tables names, field names, field sizes, validation rules, input masks, default values, table relationships, etc. • Used by DBMS to automatically generate forms, reports, queries, and SQL based tools.

  26. Object-Oriented Databases • Databases now need to be able to store: • Images • Video • Audio • Other complex formats • For example • X-rays, MRI • Audio and video segments • Geographical Information Systems (GIS) maps

  27. Corporate Oracle MySql Informix IBM DB2 Microsoft SqlServer Personal MS Access MS FoxPro Filemaker Pro (Mac & Win) Paradox Approach Examples of DBMS Products Free versions of Oracle, MySql, and MS SqlServer are available via download.

  28. Database Administrator (DBA) • Coordinate the creation, maintenance and use of the database. • Database Design and redesign • Security administration • Performance monitoring • Backup and recovery • Other duties as assigned.

  29. Databases for Information Systems • Information Systems are built with a database at the core. • Information Systems may include: • Operational or Transaction Processing Systems (TPS) • Management Information Systems (MIS) • Decision Support Systems (DDS) • Executive Information Systems (EIS) • See next few slides for details…

  30. Operational or Transaction Processing Systems (TPS) • Operational systems are designed to support the day-to-day transactions of a business – sales, orders, purchasing, accounts payable, payroll, etc. • Details of all transactions are captured and saved in a database. • The detail is then manipulated and summarized for other systems (see next few slides).

  31. Management Information Systems (MIS) • MIS is designed to provide accurate, timely, and useful information from the operational systems. • Managers received reports as scheduled (daily, weekly, monthly, etc.) • A MIS manager runs the MIS department • Also called Chief Information Officer (CIO) • Must be comfortable with computer technology and the organization’s business • MIS still exist, but has expanded to… • Decision Support Systems (DSS) – support managers in non-routine decision-making tasks • Executive Information Systems (EIS) – support executives in decisions that affect the entire organization • EIS includes information from outside the organization such as from competitors, market, and government

  32. Differences Between MIS and DSS (or EIS) • MIS emphasizes planned reports, so reports are standard, structured, scheduled, and routine. • Generating ad-hoc reports is constrained by the design of the operational system. • DSS is designed to support ad-hoc queries, so it is a separate stand-alone system, but most of the data comes from the operational system and other sources. • DSS focuses on supporting decision making

  33. Informatics • Informatics – the study of how information is gathered, stored, manipulated, accessed, transferred, secured, given meaning, and presented. • Biomedical Informatics (BMI) – is an emerging discipline that combines the health science knowledge including medicine, dentistry, pharmacy, nursing, radiology and biological sciences with computer science, mathematics, statistics, engineering, information technologies and management. • Good field for those interested in medicine and technology. • ASU – School of Computing and Informatics has a program: bmi.asu.edu/index.php

  34. MCC’s Database Courses • CIS117DM – Access Level I • CIS217AM – Access Level II • CIS119DO – Oracle • CIS276DA – MySql • CIS276DB – MS SqlServer

  35. Access Introduction/Overview Access (application)File: databaseName.accdbA file contains database Objects and the actual data. TableDefinitions Forms ActualData Reports DatabaseDictionary Queries

More Related