Chapter 5 Database Concepts Why Study Databases n

Chapter 5 Database Concepts

Why Study Databases? n Databases have incredible value to business. n n n Probably the most important technology for supporting operations. Vastly superior to file processing systems Businesses cannot survive without quality data about their n n internal operations and external environment.

Topics n n n Fundamental database structures. Logical data elements. Databases vs. Files Processing Major types of databases. Data warehouses and data mining.

Data Resource Management Definition: n Managerial activity that applies information technology to the task of managing an organization’s data resources to meet the information needs of the stakeholders

Foundation Data Concepts Abstract Concepts n Entity – person, place, object or event – stored as a record or a table row n Attribute – characteristic of an entity – stored as field or table column

Foundation Data Concepts Database Concepts n Database – a collection of related tables n Tables – a collection of related records – collection of related entities n Record – collection of fields (table row) –represents an entity n Field – collection of characters (table column) – represents an attribute n Character – single alphabetic, numeric or other symbol

Fields n n n Small to large Characters “B R E I M E R” Form a field Last Name Breimer n A field is an attribute of an entity

Records n n n Small to large A bunch of fields form a record First Name Last Name Sex Eric Breimer Yes A record is an entity Weight 253

Tables n n n Small to large A bunch of records forms a table First Name Last Name Sex Age Eric Breimer M 30 Bob Anuszewski M 22 Mary Ridzi F 21 A table is a group of related entities

Databases n n Small to large A bunch of tables form a database Order Table Customer Table Product Table n A database represents an organization or business

Databases n But, databases are not just a bunch of tables Orders OID CID PID Quantity 001 508 199 500, 000 002 508 201 2 003 510 201 1 Customers n Products CID FName LName Address PID Description Cost 508 Eric Breimer . . . 199 Viagra $45. 99 509 Andrew Zych . . . 200 Tooth Paste $2. 58 510 Greg Smith . . . 201 Hair Gel $5. 99 A database also includes relationships between the different tables

Traditional File Processing Definition: n Data is organized, stored, and processed in independent files of data records

Problems of File Processing n Data Redundancy – duplicate data requires an update to be made to all files storing that data n Lack of Data Integration – data stored in separate files require special programs for output making ad hoc reporting difficult n Data Dependence – programs must include information about how the data is stored so a change in storage format requires a change in programs

Database Management Approach Definition: n Consolidates data records into one database that can be accessed by many different application programs. n Software interface between users and databases n Data definition is stored once, separately from application programs

Database Management Software (DBMS) Definition: n Software that controls the creation, maintenance, and use of databases

DBMS Software Components n Database Definition n n Language and graphical tools to define entities, relationships, integrity constraints, and authorization rights Application Development n Graphical tools to develop menus, data entry forms, and reports

DBMS Software Components n Transaction Processing n n n Controls to prevent interference from simultaneous users and Controls to recover lost data after a failure Database Tuning n Tools to monitor and improve database performance

Database Interrogation Definition: n Capability of a DBMS to report information from the database in response to end users’ requests n Query Language – allows easy, immediate access to ad hoc data requests n Report Generator - allows quick, easy specification of a report format for information users have requested

Natural Language vs. SQL Queries

Database Maintenance n Updating a database continually to reflect new business transactions and other events n Updating a database to correct data and ensure accuracy of the data

Application Development n End users, systems analysts, and other application developers can use the internal programming language and built-in software development tools provided by many DBMS packages to develop custom application programs.