Date and Database Management Systems (DBMS)

1. Date and Database Management Systems (DBMS) Supplement C

2. Physics 101 Assignments Last Name, First name ID # Email Major Year #1 #2 #3 #4 #5 Einstein, Albert 123456 Bigal Phys 1 9 2 Copernicus 234567 Coper Math 3 5 5 Galileo 345678 Gali Art 2 3 7 Hawking, Stephen 456789 HSteve CS 4 8 4 Calvin 567891 Hobbes Phys 5 10 10 Sample Flat File - Student

3. Supplement C: Objectives • Describe the difference between file-oriented and database environments. • Describe what a DBMS is. • Identify various types of DBMS software. • Describe common features of a DBMS. • Describe how DBMSs are used.

4. File Versus Database Environments • Computer processing involves two components: data and instructions (programs). • Conceptually, there are two methods for designing the interface between program instructions and data. • File-oriented processing A specific data file was created for each application. • Data-oriented processing. Create a single data repository to support numerous applications. • Disadvantages of file-oriented processing include redundant data and programs and varying formats for storing the redundant data. • The format for similar fields may vary because the programmer used inconsistent field formats.

5. Mailing list: Customer Number, Customer Name, Customer Address . . . Order file: Order Number, Date, Customer Number, Customer Name, Customer Address, Inventory Items Ordered, Quantities Ordered . . Shipment file: Date, Shipment Number, Order Number, Customer Number, Customer Name, Customer Address, Inventory Items Ordered, Items Shipped . . . Customer items: Customer Number, Customer Name, and Customer Address. Order items: Order Date, Order Number, Inventory Ordered, Quantities Ordered, and Customer Number. Shipment items: Shipment Date, Shipment Number, Order Number, Customer Number, Items Shipped, and Balance Due for Shipment. Comparison of File Structures File Oriented Data Oriented Redundant Items in Yellow

6. What is a Database Management System (DBMS) • A DBMS is the resource available to define and manage data structures in an information system. • DBMS software was originally developed to simplify and enhance the value of an organization’s data by reducing data and programming redundancy, inconsistent data formats, and data inconsistencies. • A DBMS environment allows the development and maintenance of a data repository that is independent from application programs. • Such flexibility enables database users to create and maintain IT applications that capture, maintain, and report data while shielding application developers from the physical structure and maintenance of the data repository.

7. Tree or hierarchical Network Relational Object oriented Types of DBMS Software Application A DBMS Data Application B

8. Hierarchical Logical Structures • Hierarchical databases (also called tree databases) organize and search data using the structure of a family tree. • Each record of the hierarchical structure may have multiple “child” records that are subordinate to it. See next Slide • Choosing which records are superior and which records are subordinate depends in part on storage efficiency as well as on the quantity and types of queries you anticipate. • Because the hierarchical structure can model only 1:1 and 1:* maximum cardinalities, this restricts the usefulness of the hierarchical database structure. • Hierarchical structures are commonly used to organize accounting data when a chart of account structure is used.

9. COURSE RECORD Course Section Teacher Room Time Acc 1 Calvin 340 MW 1pm Course Record STUDENT RECORD Student # Last Name First Name Major Grade 12345 Smith Bob History B Student Record 23456 Sanders Shelly Math A 34567 Samson Jennifer CS C COURSE RECORD CS 1 Hobbes 205 TTh 2pm 12345 Smith Bob History B 45678 Fisher Mary CS A- 56789 Lincoln Perry Math A Sample Tree Structure

10. Accounting Data in a Tree Structure FINANCIAL STATEMENTS Assets Liabilities Equity Current Liabilities Current Assets Revenue Property, Plant, and Equipement Long-termLiabilities Expenses

11. Network Logical Structure • The network structure allows any number of superior records to be related to any number of subordinates (and vice versa). • The distinction between parent and child records is eliminated. • Using a network structure, Student records could be subordinate to Course, and Course records could be subordinate to Student records at the same time. • Network DBMS software is fairly complex because you must know the physical structure of the data to be able to search the database.

12. Relational Logical Structure • Relational databases organize and store data in two‑dimensional tables consisting of rows and columns. • The table is arranged in columns, which are called attributes, or domains, and rows, which are called records or tuples. • Each row of information in a table is called an instance. • One of the table fields is a unique identifier field (the key attribute or primary key). Normally, each table contains: • Data items about a particular group of people, resources, locations, or business activity type. • Putting only data items about a particular entity of object into a data table is known as normalization. A table is in normal form if it only contains items that describe the main theme or focus of the table (e.g., customers, sales orders, or inventory) plus any fields needed to link the table with other tables. • Data items (usually key attributes) that are used to combine or link tables.

13. The relational approach to designing databases allows more flexibility. By introducing some limited duplication into the data pool, users are able to create relationships and logical structures ad hoc, rather than having to predefine relationships as the database is created. A change in the database does not require rewriting all the application program codes. Like the network model, relational databases can incorporate all of the types of maximum relationship cardinalities. Rather than physically linking data, relational models make use of logical tables to model structures. Relationships among tables are represented by common data values in the tables. The power of relational DBMS software is the ability to maintain several tables of related information that can be accessed by several different information customers in many different ways One of the great advantages of the relational structure is its ability to simplify the organization of complex sets of data. Advantages of the Relational Approach

14. Objects contain both data as well as methods (i.e., instructions for processing the data). The major advantage of object oriented (OO) structures is their increased flexibility to represent very complex data structures that capture the essence of complex objects existing in reality. Using object technology, programmers can model and implement complex data types such as voice, video, audio, etc. Although OO has several conceptual advantages over the other structures, it is much more computationally demanding than even the pure relational structure. Today, there are a limited number of pure object oriented database applications in use. However, as technology improves, the frequency of object oriented applications will increase. Object Oriented Structure

15. Data Definition Commands which are used to set up the data structures and define user data views of the data—these are known as logical views or schemas; Data Manipulation Commands which are used to add, delete, and update data in the structures; Forms Generators which are used to design the format and look of desired screens and forms Report Generators that are used to define the format and look of desired output. DBMS environments provide management and security features. Data Query Commands which help users ask questions about the data; Popular data query tools include QBE (query by example) and SQL (structured query language). Using QBE, a grid or replica of an empty record is displayed and the user types search criteria in the applicable columns. SQL, on the other hand, is an actual program language that you can use to directly interact with data, or you can embed SQL commands in programs that access and process data.; and Other Features of a DBMS

16. DBMS (and File) Documentation • The database generally consists of many files and each file contains many records. • Each event represents one record. • Within each record there are many fields of data called data items, with each data item representing one attribute about the entity. • A record layout details the record structure, including record field names, the width of each field, and the type of data stored in each field (e.g., numeric, alphanumeric, or date). • Since database data items are shared by numerous applications, database documentation, called data dictionaries, document data item, rather than record level detail.

17. File Database Record #1 Record #2 Record #3 Fields Record Layout Register # Customer # Sale # Employee # File Structure Relationships

18. DBMS Analysis and Design • Much of the work to develop a database and the programs associated with it is completed during the analysis, planning, and design stages of a system. • A central objective in analyzing and designing the database is to identify the set of data needed to document business activities (e.g., financing, selling, purchasing, or marketing) and provide information about parties (e.g., customers, vendors, or employees) and resources (e.g., cash, services, or inventory) involved in the business activities.

19. Architecture plans include an overview of such DBMS components as: the specific data items and structures needed, the attributes of each data item, the primary identifier for each data structure, a method for linking related data items and structures (which requires an understanding of data/object relationships), needed business and information controls, needed help screens and help functions, input/output screens, report formats, queries, and documents, and computer instructions needed to perform recording, maintenance, and reporting tasks. DBMS Analysis and Design

20. Data warehouses contain data from operational databases, as well as data from external sources. The objective is to capture and make available the knowledge and information critical for analysis and decision making The data are combined and duplicate, invalid, and unneeded data is removed. The data are aggregated (i.e., summarized) and organized by dimension and/or subject area. Data warehouses provide summary data as of a certain point in time. Data warehouse users can mine the data and “slice and dice” it to generate desired outputs. Users are sending a signal that they need enterprise-wide information views of an organization to support all levels of analysis and decision making. Data Warehouses

21. Features of DBMS Software Data Definition Commands used to set up the data structures and define user data views -- known as logical views or schemes Report Generators used to define the format and look of desired output. Data Manipulation Commands used to add, delete, and update data in the structures Security features Forms Generators used to design the format and look of desired screens and forms Data Query Commands help users ask questions about the data