Chapter 1 Introduction

1. Chapter 1 Introduction

2. Basic Concepts of Database Systems • Database is • A collection of data organized in some way • grouped on what they refer to, or on technical types • relationships between pieces of data determined • Data = symbols for recording and communication (e.g., customer ID and name) • Example: Think of records (paper, electronic) describing employees. • The organization of data can be explained by metadata. • Metadata define how data are organized (e.g., Employee is a class of data, which has x number of attributes, and each attribute belongs to a data type). EmployeeID Number LastName Text FirstName Text Phone Number More 

3. Basic Concepts • Database Management System (DBMS) • Software for creating databases, storing & retrieving data, creating user interface (forms), creating reports, and administering a DB system (security, access, etc.). • DBMS Product (a specific DBMS software; same as “DBMS Brand”). • Database System (DBMS Application, Application)*: • An implementation of a DBMS product including one or more databases, logic (business rules), some code, and user interface. • Supporting various information needs. • An information system that results from “applying” a DBMS or a database. • Supporting specific information needs.

4. File (Processing) Systems vs. DB systems (DBS) • History but also frequent shortcut today (e.g., Excel as surrogate of DBS) • File Processing Systems: • Data files and program files (code) that work on data files, or • Data and code in the same file • Must have code (programs) for data input, modification, retrieval, & deletion. • Data files are closely coupled with programs that define metadata - tight coupling reducing design flexibility (changes). More 

5. File (Processing) Systems vs. DB systems DBS are: • Higher on retrieval capabilities • Lower redundancy, higher data integrity • Data independent from programs (code); looser coupling – changes in data and code less restricted • Better security and management of concurrent access to database • Significantly lower development and maintenance cost • But keep in mind: Excel’s enduring popularity in companies.

6. Add cell tel. number to employee table Open table template Add data element Existing reports, queries, code will not crash although need to be modified to output new data – looser coupling Modifying Data in DBS-- Data-Program independence Field Name Data Type Description EmployeeID Number Autonumber.. TaxpayerID Text Federal ID LastName Text FirstName Text . . . Phone Text Phone Number . . . CellPhone Text Cell Phone No.

7. D B M S DB System Components Database Database Engine: • CRUD* operations & Data Dictionary • Concurrency & Lock Manager • Recovery Manager • Disk Space Manager Data & Metadata Management Security Management Query Processor Retrieval Form Builder Report Writer Input/Output; User Interface Application Generator System development tools *CRUD= Create, Read, Update, Delete Note: The Post book mixes “database” With “data” and “application”.

8. Query Processor 2 Creating Outputs via Report Writer Database 4 3 Database Engine 5 Report Writer 1 6 Output: Report

9. Relational Database Standard • “Relation” = table, a logical view of the data structure for storing data. An example of Table (Sale) created in Oracle.

10. Examples of Relational DBMS Products • Oracle • Sybase • Informix (Unix) • DB2, SQL/DS (IBM) • Access, SQL Server (Microsoft) • Older (IMS, Focus) • Many limited to PC (MS Access, dBASE, Paradox, …) • Open source: MySQL (more)

11. Customers files Entry point Customer XYZ pointers Order 2 Order 1 Order files pointers Item C Item B Item A Item A To retrieve how many of item A are sold, start at the top from Customer. Then all nested data are retrieved top-down and left-right. Different data models needed for different retrieval tasks (e.g., Order at the root) => high data redundancy in DBS! Items Item# ItemName Quantity 998 Dog Food 12 764 Cat Food 11 Hierarchical Database • First commercial standard (IBM’s IMS) • Still used (legacy systems)

12. Entry point Customer XYZ Order 1 Order 2 Entry points Item C Item B Item A Network Database Relationships between records also supported by pointers; complex programming. One data model supports different retrieval paths (by customer, order, item).

13. Relational Database Foreign Key (FK) Primary Key (PK, Key) Customer(CustomerID, Name, …) Order(OrderID, CustomerID, OrderDate, …) ItemOrdered(OrderID, ItemID, Quantity, …) Item(ItemID, Description, Price, …) • Data organized as logical tables, consisted of rows (records) and columns (attributes), and connected via key attributes. • Possible to retrieve almost any combination of rows and columns, and a specific piece of data (field) within a row. • Pointers transparent to developers, just need to specify Key—Foreign Key relationships.

14. Order Customer OrderID CustomerID … CustomerID Name … Government Customer Commercial ContactName ContactPhone Discount, … NewOrder DeleteOrder … Add Customer Drop Customer Change Address ContactName ContactPhone … NewContact AddNewContact Data Procedures (behavior, methods) for processing data. Generalization/Specialization supported;inheritance Object-Oriented Databases

15. Object-Relational DB Systems • More frequent than pure object-oriented systems • Architecture: • Database is relational • Objects are created in main memory according to class diagram and business rules, and populated by data from the relational databases (data access layer in system sequence diagrams) • System operations, then, performed by objects