Chapter 5 Databases PDF

DatabasesNanda Ganesan, Ph.D.

ObjectiveQ1: What is the purpose of a database?Q2: What is a database?Q3: What is a database managementsystem (DBMS)?Q4: How do database applications make databases more useful?Q5: What is a NoSQL DBMS? Copyright © 2015 Pearson Education, Inc.

Q3: What Is a Database Management System (DBMS)?• Program used to create, process, and administer a database• Licensed from vendors such as IBM, Microsoft, Oracle, and others• DB2 from IBM, Access and SQL Server from Microsoft, Oracle Database from Oracle Corporation• MySQL - open source, license-free for most applications Copyright © 2015 Pearson Education, Inc.

Database Definition• Database Management System (DBMS) – Access, Oracle etc.• Database Application Systems – Student enrollment management system, enrollment management system

Data and Information• Data – Unprocessed• Information – Processed

Q1: What Is the Purpose of a Database?• Organize and keep track of things• Keep track of multiple themes• General rule:  Single theme - store in a spreadsheet  Multiple themes - use a database Copyright © 2015 Pearson Education, Inc.

A List of Student Grades Presented in a Spreadsheet – Single Theme Copyright © 2015 Pearson Education, Inc.

Student Data Shown in a Form, from a DatabaseCopyright © 2015 Pearson Education, Inc.

Data Processing• Data Integrity – Database is as valid as the correctness of the information stored and processed – GIGO

Good Information Characteristics• Accurate• Verifiable• Timely• Organized• Accessible• Useful• Cost-effective

Data Hierarchy File 1.1 Record 1 Field1 File 1.2 Record 2 (Primary) File 2.1 Field 2Database Database Server Application 1 Database Application 2

Q2: What Is a Database?Copyright © 2015 Pearson Education, Inc.

Hierarchy of Data ElementsCopyright © 2015 Pearson Education, Inc.

Components of a DatabaseCopyright © 2015 Pearson Education, Inc.

Example of Relationships Among Rows Copyright © 2015 Pearson Education, Inc.

Sample Metadata (in Access)

Database Components

File Characteristics

Data TypesText Numeric AutoNumb Currency erDate Memo Yes/No Hyperlink Object Attachment

Data Dictionary

Maintaining Data• Add• Modify• Delete• Validate

Data ValidationAlphabetic/Nume Range check Consistency check ric CheckCompleteness Check digit check

Evolution of Database Management Systems• File processing system• Hierarchical database systems• Relational database systems• Object oriented database systems

File Processing and Database System

Advantages of Database Systems• Reduced data redundancy• Improved data integrity• Efficiently shared data• Relatively simple development

Examples of DatabaseManagement Systems

Q4: How Do Database Applications Make Databases More Useful?• Database application – Collection of forms, reports, queries and application programs serves as intermediary between users and database data.• Application programs – Provide security, maintain data Ccoopynrigshti©s2t0e15nPceayrs,onaEdnucdatiohn,aInnc. dle special cases.

Specific Purposes of Four Elements of a Database Application Copyright © 2015 Pearson Education, Inc.

Traditional Forms, Queries, Reports, and ApplicationsCopyright © 2015 Pearson Education, Inc.

Browser Forms, Reports, Queries, and ApplicationsCopyright © 2015 Pearson Education, Inc.

Browser Report for SharePoint SiteCopyright © 2015 Pearson Education, Inc.

Access Database Components• Tables• Queries• Forms• Reports• Macros• Modules• Visual Programming (Visual Basic)

Relational Database• The entity-relationship model• Entities (objects) – Students, classes etc.• Relationships – Enrollment (Student to Classes)

Types of Relationships• One-to-one• One-to-Many• Many-to-Many• A design example – Creating tables to capture a database

Querying the Tables and the Database• Query by Example• Query Language – SQL is the most popular query language

Processing the Database• Structured Query Language - SQL (see-quell) – International standard – Used by most popular DBMSINSERT INTO Student([Student Number], [Student Name], HW1, HW2, MidTerm)VALUES (1000, ‘Franklin, Benjamin’, 90, 95, 100); Copyright © 2015 Pearson Education, Inc.

Sample SQL Statement

Administering the Database• Used to set up a security system involving user accounts, passwords, permissions, and limits for processing.• Permissions can be limited in very specific ways.• Backing up database data, adding structures to improve performance of database applications, removing unwanted data. Copyright © 2015 Pearson Education, Inc.

Other Database Models• Object oriented database model – Contains code and data• Multidimensional database – In comparison, relational database is a two-dimensional database• Web database – Back end database to a front-end web server

Database Personnel• Database analyst• Database administrators• Database users

Experiencing MIS InClass Exercise 5: How Much Is a Database Worth?• Data has resale value.• Data on everything customers do.• Use to target customer for offerings they care about, avoid those they don’t.• Costly and difficult to replace data collected over many years. Copyright © 2015 Pearson Education, Inc.

Other Database Terms• Data warehouse• Data mining

Big Data• The processing of large amount of data – FaceBook – Supermarkets

Q5: What Is a NoSQL DBMS?NOSQL DBMS (NotRelational DBMS • Supports very high transaction rates • Relatively simple data structures • Replicated on many servers in the cloud• Examples • Dynamo (Amazon) • Bigtable (Google) • Cassandra (Facebook) Copyright © 2015 Pearson Education, Inc.

Will NoSQL Replace Relational DBMS Products?•Conversion very expensive and disruptive.•Very technical, limited to those with a deep background in computer science.•Requires years of training to use.•Organization may choose NoSQL products for specialized applications. Copyright © 2015 Pearson Education, Inc.

Guide: No, Thanks, I’ll Use a Spreadsheet • Story of the failed database project • Database works but doesn’t meet the requirements of this salesperson. • Cause of database failures usually either incompetent database developers or underfunding. • Causes of systems development failure--poor communication between users and systems developers, lack of clear requirements, and inability to manage requirements. Copyright © 2015 Pearson Education, Inc.

Guide: No, Thanks, I’ll Use a Spreadsheet (cont'd)• Databases take time to build.• Complicated to operate.• Need IS people to create and keep them running.• Salesman doesn’t want to share data.• Spreadsheets sometimes better option, especially if data needs are simple. Copyright © 2015 Pearson Education, Inc.

Case Study 5: Fail Away with Dynamo, Bigtable, and Cassandra• Current relational DBMS products not designed for large, multi-server systems• NoSQL databases – Dynamo (elastic), Bigtable (elastic), Cassandra (used by Facebook, Twitter, Digg, Reddit)• Amazon: Dynamo• Google: Bigtable processes petabytes of data on hundreds of thousands of servers Copyright © 2015 Pearson Education, Inc.

Case Study 5: Fail Away with Dynamo, Bigtable, and Cassandra• Current relational DBMS products not designed for large, multi-server systems• NoSQL databases – Dynamo (elastic), Bigtable (elastic), Cassandra (used by Facebook, Twitter, Digg, Reddit)• Amazon: Dynamo• Google: Bigtable processes petabytes of data on hundreds of thousands of servers Copyright © 2015 Pearson Education, Inc.