Chapter 1 Database Concepts Introduction n n Good

Chapter 1 Database Concepts

Introduction n n Good decisions require good information derived from raw facts Data is managed most efficiently when stored in a database Databases evolved from computer file systems Understanding file system characteristics is important

Why Databases? n Databases solve many of the problems encountered in data management n – Used in almost all modern settings involving data management: • Business • Research • Administration • Important to understand how databases work and interact with other applications

Why Study Databases? n Databases have incredible value to businesses. n n n Very 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.

Data vs. Information n n Data are raw facts Information is the result of processing raw data to reveal meaning Information requires context to reveal meaning Raw data must be formatted for storage, processing, and presentation Data are the foundation of information, which is the bedrock of knowledge

Data vs. Information n n Data: building blocks of information Information produced by processing data Information used to reveal meaning in data Accurate, relevant, timely information is the key to good decision making • Good decision making is the key to organizational survival

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 Large 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 Small

Fields n 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 A bunch of fields form a record First Name Last Name Sex Eric Breimer Yes A record is an entity Weight 263

Tables n n A bunch of records forms a table First Name Last Name Sex Age Eric Breimer M 30 Jeff Albert M 22 Jackie Pizzo F 21 A table is a group of related entities

Databases n A bunch of tables form a database Order Table Customer Table Product Table n A database can represent a single business or an entire market

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

Chapter 1 Relationships & Queries in Databases

Types of Relationships Thing. A One to One n Examples? n Analysis Technique n n Thing. B Relationship Man Woman Married Consider Thing. A and Thing. B Can Thing. A be related to more than one Thing. B? Can Thing. B be related to more than one Thing. A? If the two answers are NO, then it is a one to one relationship.

Types of Relationships Student Faculty One to Many n Examples? n Analysis Technique n n Student Advises Get Advisement Consider Thing. A and Thing. B Can Thing. A be related to more than one Thing. B? Can Thing. B be related to more than one Thing. A? If only one answer is yes, then you have a one to many relationship

Types of Relationships student takes course Student Many to Many n Examples? n Analysis Technique n n Course Student course has a student Consider Thing. A and Thing. B Can Thing. A be related to more than one Thing. B? Can Thing. B be related to more than one Thing. A? If the answers are yes and yes, then the relationship is many to many.

How to Model Relationships Orders OID CID PID Quantity 001 508 199 500, 000 002 508 201 2 003 510 201 1 Customers 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

Find the name and hire date of the manager working on the sales manual project

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

Problems of File Processing n Data Redundancy – n n Lack of Integration – n n duplicate data requires update to many files data stored in separate files hard to combine data Dependence – n changing the file format requires changing the program…

Database Management Approach n Consolidates data records into one CENTRAL database that can be accessed by many different 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

Schemas Schema - A description of the database Subschema – describes a subset of the database and which users have access to this subset

Data Definition Language n n n Language Used to describe Schemas and Subschemas Describes relationships between different data Provides a Logical view of the data

Data Dictionary Entry n n n A more detailed description of the data in a database Specifies data types and ranges Assists programmers in understanding the data

Physical vs. Logical n DBMS concentrate on Physical access to the underlying tables n n n Concurrency control Query’s Creating/deleting tables

Physical vs. Logical n MIS systems are (Logically) interface with a DBMS n n n monthly reports charts automated inquiries

Application Development n Today, even non-technical staff can use tools to build little programs that use a database. n n Database Management Systems have all kinds of tools to develop custom application programs and interfaces. Example: The College’s MIS (Banner) is actually an application built on top of a Oracle Database.