DAVID M. KROENKE and DAVID J. AUER DATABASE CONCEPTS, 7th Edition,

DAVID M. KROENKE and DAVID J. AUER DATABASE CONCEPTS, 7th Edition, Global Edition Chapter One Getting Started: An Overview of Databases

Chapter Objectives Identify the purpose and scope of this book Know the potential problems with lists Understand the reasons for using a database Understand how using related tables helps you avoid the problems of using lists Know the components of a database system Learn the elements of a database Learn the purpose of the database management system (DBMS) Understand the functions of a database application Introduce non-relational databases KROENKE and AUER - DATABASE CONCEPTS (7th Edition, Global Edition) Copyright 2015 Pearson Education, Ltd. 1-2

Purpose of a Database The purpose of a database is to keep track of things. Unlike a list or spreadsheet, a database may store information that is more complicated than a simple list. KROENKE and AUER - DATABASE CONCEPTS (7th Edition, Global Edition) Copyright 2015 Pearson Education, Ltd. 1-3

Problems with Lists: Redundancy In a list, each row is intended to stand on its own. As a result, the same information may be entered several times. – For Example: A list of Projects may include the Project Manager’s Name, ID, and Phone Extension. If a particular person is managing 10 projects, his/her information would have to be entered 10 times. KROENKE and AUER - DATABASE CONCEPTS (7th Edition, Global Edition) Copyright 2015 Pearson Education, Ltd. 1-4

Problems with Lists: Multiple Themes In a list, each row may contain information on more than one theme. As a result, needed information may appear in the lists only if information on other themes is also present. – For Example: A list of Projects may include Project Manager information (Name, ID, and Phone Extension) and Project information (Name, ID, StartDate, Budget) in the same row. KROENKE and AUER - DATABASE CONCEPTS (7th Edition, Global Edition) Copyright 2015 Pearson Education, Ltd. 1-5

List Modification Issues Redundancy and multiple themes create modification problems: – Deletion problems – Update problems – Insertion problems KROENKE and AUER - DATABASE CONCEPTS (7th Edition, Global Edition) Copyright 2015 Pearson Education, Ltd. 1-6

List Modification Issues (Cont’d) Figure 1-5: The Student with Adviser and Department List KROENKE and AUER - DATABASE CONCEPTS (7th Edition, Global Edition) Copyright 2015 Pearson Education, Ltd. 1-7

Addressing Information Complexities Relational databases are designed to address many of the information complexity issues. KROENKE and AUER - DATABASE CONCEPTS (7th Edition, Global Edition) Copyright 2015 Pearson Education, Ltd. 1-8

Relational Databases A relational database stores information in tables. Each informational topic is stored in its own table. In essence, a relational database will break-up a list into several parts—one part for each theme in the list. A Project List would be divided into a CUSTOMER Table, a PROJECT Table, and a PROJECT MANAGER Table. KROENKE and AUER - DATABASE CONCEPTS (7th Edition, Global Edition) Copyright 2015 Pearson Education, Ltd. 1-9

Putting the Pieces Back Together In our relational database, we broke our list into several tables. Somehow the tables must be joined back together. In a relational database, tables are joined together using the value of the data. If a PROJECT has a CUSTOMER, the Customer ID is stored as a column in the PROJECT table. The value stored in this column can be used to retrieve specific customer information from the CUSTOMER table. KROENKE and AUER - DATABASE CONCEPTS (7th Edition, Global Edition) Copyright 2015 Pearson Education, Ltd. 1-10

Sounds Like More Work, Not Less A relational database is more complicated than a list. However, a relational database minimizes data redundancy, preserves complex relationships among topics, and allows for partial data. Furthermore, a relational database provides a solid foundation for user forms and reports. KROENKE and AUER - DATABASE CONCEPTS (7th Edition, Global Edition) Copyright 2015 Pearson Education, Ltd. 1-11

Relational Database Example Figure 1-6: The Adviser and Student Tables KROENKE and AUER - DATABASE CONCEPTS (7th Edition, Global Edition) Copyright 2015 Pearson Education, Ltd. 1-12

A Relational Database Solves the Problems of Lists Figure 1-7: Modifying the Adviser and Student Tables KROENKE and AUER - DATABASE CONCEPTS (7th Edition, Global Edition) Copyright 2015 Pearson Education, Ltd. 1-13

The Department, Advisor and Student Tables Figure 1-8: The Department, Adviser, and Student Tables KROENKE and AUER - DATABASE CONCEPTS (7th Edition, Global Edition) Copyright 2015 Pearson Education, Ltd. 1-14

The Art Course Database Tables Figure 1-10: The Art Course Database Tables KROENKE and AUER - DATABASE CONCEPTS (7th Edition, Global Edition) Copyright 2015 Pearson Education, Ltd. 1-15

The Project Equipment Tables Figure 1-12: The Project Equipment Database Tables KROENKE and AUER - DATABASE CONCEPTS (7th Edition, Global Edition) Copyright 2015 Pearson Education, Ltd. 1-16

Structured Query Language (SQL) Structured Query Language (SQL) is an international standard for creating, processing and querying databases and their tables. Many database applications use SQL to retrieve, format, report, insert, delete, and/or modify data for users. KROENKE and AUER - DATABASE CONCEPTS (7th Edition, Global Edition) Copyright 2015 Pearson Education, Ltd. 1-17

SQL Example We can use SQL to combine the data in the three tables in the Art Course Database to recreate the original list structure of the data. We do this by using an SQL SELECT statement. KROENKE and AUER - DATABASE CONCEPTS (7th Edition, Global Edition) Copyright 2015 Pearson Education, Ltd. 1-18

SQL Example (Cont’d) SELECT CUSTOMER.CustomerLastName, CUSTOMER.CustomerFirstName, CUSTOMER.Phone, COURSE.CourseDate, ENROLLMENT.AmountPaid, COURSE.Course, COURSE.Fee FROM CUSTOMER, ENROLLMENT, COURSE WHERE CUSTOMER.CustomerNumber ENROLLMENT.CustomerNumber AND COURSE.CourseNumber ENROLLMENT.CourseNumber; KROENKE and AUER - DATABASE CONCEPTS (7th Edition, Global Edition) Copyright 2015 Pearson Education, Ltd. 1-19

SQL Example Results Figure 1-13: Results of the SQL Query to Recreate the Art Course List KROENKE and AUER - DATABASE CONCEPTS (7th Edition, Global Edition) Copyright 2015 Pearson Education, Ltd. 1-20

Database Systems The four components of a database system are: – Users – Database Application – Database Management System (DBMS) – Database KROENKE and AUER - DATABASE CONCEPTS (7th Edition, Global Edition) Copyright 2015 Pearson Education, Ltd. 1-21

Components of a Database System Figure 1-15: Components of a Database System KROENKE and AUER - DATABASE CONCEPTS (7th Edition, Global Edition) Copyright 2015 Pearson Education, Ltd. 1-22

Users A user of a database system will – use a database application to track things; – use forms to enter, read, delete and query data; and – produce reports. KROENKE and AUER - DATABASE CONCEPTS (7th Edition, Global Edition) Copyright 2015 Pearson Education, Ltd. 1-23

The Database A database is a self-describing collection of related records. – The database itself contains the definition of its structure. – Metadata is data describing the structure of the database data. Tables within a relational database are related to each other. KROENKE and AUER - DATABASE CONCEPTS (7th Edition, Global Edition) Copyright 2015 Pearson Education, Ltd. 1-24

Example Database Metadata: A Relationship Diagram Figure 1:16 Example Metadata: A Relationship Diagram for the Art Course Tables in Figure 1-10 KROENKE and AUER - DATABASE CONCEPTS (7th Edition, Global Edition) Copyright 2015 Pearson Education, Ltd. 1-25

Database Contents Figure 1-17 Database Contents KROENKE and AUER - DATABASE CONCEPTS (7th Edition, Global Edition) Copyright 2015 Pearson Education, Ltd. 1-26

Database Management System (DBMS) A database management system (DBMS) serves as an intermediary between database applications and the database. The DBMS manages and controls database activities. The DBMS creates, processes and administers the databases it controls. KROENKE and AUER - DATABASE CONCEPTS (7th Edition, Global Edition) Copyright 2015 Pearson Education, Ltd. 1-27

Functions of a DBMS Create databases Create tables Create supporting structures Read database data Modify database data (insert, update, and delete) Maintain database structures Enforce rules Control concurrency Provide security Perform backup and recovery KROENKE and AUER - DATABASE CONCEPTS (7th Edition, Global Edition) Copyright 2015 Pearson Education, Ltd. 1-28

Referential Integrity Constraints The DBMS will enforce many constraints . Referential integrity constraints ensure that the values of a column in one table are valid based on the values in another table. – If a 5 was entered as a CustomerID in the PROJECT table, a Customer having a CustomerID value of 5 must exist in the CUSTOMER table. KROENKE and AUER - DATABASE CONCEPTS (7th Edition, Global Edition) Copyright 2015 Pearson Education, Ltd. 1-29

Database Applications A database application is a set of one or more computer programs that serves as an intermediary between the user and the DBMS. KROENKE and AUER - DATABASE CONCEPTS (7th Edition, Global Edition) Copyright 2015 Pearson Education, Ltd. 1-30

Functions of Database Applications Create and process forms Process user queries Create and process reports Execute application logic Control database applications KROENKE and AUER - DATABASE CONCEPTS (7th Edition, Global Edition) Copyright 2015 Pearson Education, Ltd. 1-31

Database Applications: Example Data Entry Form Figure 1-20: Example Data Entry Form KROENKE and AUER - DATABASE CONCEPTS (7th Edition, Global Edition) Copyright 2015 Pearson Education, Ltd. 1-32

Database Applications: Example Query Figure 1-21: Example Query KROENKE and AUER - DATABASE CONCEPTS (7th Edition, Global Edition) Copyright 2015 Pearson Education, Ltd. 1-33

Database Applications: Example Report Figure 1-22: Example Report KROENKE and AUER - DATABASE CONCEPTS (7th Edition, Global Edition) Copyright 2015 Pearson Education, Ltd. 1-34

Personal Database Systems Personal database systems typically – have one application. – have only a few tables. – are simple in design. – involve only one computer. – support one user at a time. KROENKE and AUER - DATABASE CONCEPTS (7th Edition, Global Edition) Copyright 2015 Pearson Education, Ltd. 1-35

Personal Database Systems Figure 1-23: Personal Database System KROENKE and AUER - DATABASE CONCEPTS (7th Edition, Global Edition) Copyright 2015 Pearson Education, Ltd. 1-36

Personal Database Systems: An SQL Query in Microsoft Access Figure 1-24: SQL Generated by Microsoft Access Query KROENKE and AUER - DATABASE CONCEPTS (7th Edition, Global Edition) Copyright 2015 Pearson Education, Ltd. 1-37

Personal Database Systems: SQL Query Results in Microsoft Access Figure 1-25: Microsoft Access 2010 KROENKE and AUER - DATABASE CONCEPTS (7th Edition, Global Edition) Copyright 2015 Pearson Education, Ltd. 1-38

Enterprise-Class Database Systems Enterprise-Class database systems typically: – support several users simultaneously, – include more than one application, – involve multiple computers, – are complex in design, – have many tables, and – have many databases. KROENKE and AUER - DATABASE CONCEPTS (7th Edition, Global Edition) Copyright 2015 Pearson Education, Ltd. 1-39

Organizational Database Systems Figure 1-26 Enterprise-Class Database System KROENKE and AUER - DATABASE CONCEPTS (7th Edition, Global Edition) Copyright 2015 Pearson Education, Ltd. 1-40

Commercial DBMS Products Example of Desktop DBMS Products – Microsoft Access Examples of Organizational DBMS Products – Microsoft’s SQL Server – Oracle’s Oracle – Sun Microsystem’s MySQL – IBM’s DB2 KROENKE and AUER - DATABASE CONCEPTS (7th Edition, Global Edition) Copyright 2015 Pearson Education, Ltd. 1-41

Enterprise-Class Database Systems: Microsoft SQL Server 2014 Figure 1-27 Microsoft SQL Server 2014 KROENKE and AUER - DATABASE CONCEPTS (7th Edition, Global Edition) Copyright 2015 Pearson Education, Ltd. 1-42

Enterprise-Class Database Systems: Oracle Database Express Edition 11g Release 2 Figure 1-28 Oracle Database Express Edition 11g Release 2 KROENKE and AUER - DATABASE CONCEPTS (7th Edition, Global Edition) Copyright 2015 Pearson Education, Ltd. 1-43

Enterprise-Class Database Systems: MySQL 5.6 Figure 1-29: MySQL 5.6 KROENKE and AUER - DATABASE CONCEPTS (7th Edition, Global Edition) Copyright 2015 Pearson Education, Ltd. 1-44

What is a NoSQL Database? NoSQL database Non-relational database Web 2.0 Applications Facebook Twitter Pinterest Apache Software Foundation Cassandra Facebook Twitter KROENKE and AUER - DATABASE CONCEPTS (7th Edition, Global Edition) Copyright 2015 Pearson Education, Ltd. 1-45

DAVID M. KROENKE and DAVID J. AUER DATABASE CONCEPTS, 7th Edition, Global Edition End of Presentation on Chapter One Getting Started: An Overview of Databases