Accounting Essay 2 pages must answer all questions
noel001
romney_ais14_stppt_04.pptxAccounting Information Systems
Fourteenth Edition
Chapter 4
Relational Databases
Copyright © 2018 Pearson Education, Inc. All Rights Reserved
Copyright © 2018 Pearson Education, Inc.
Chapter 4: Relational Databases
Slide 1 - ‹#›
If this PowerPoint presentation contains mathematical equations, you may need to check that your computer has the following installed:
1) MathType Plugin
2) Math Player (free versions available)
3) NVDA Reader (free versions available)
1
Learning Objectives
Explain the importance and advantages of databases, as well as the difference between database and file-based legacy systems.
Explain database systems, including logical and physical views, schemas, the data dictionary, and DBMS languages.
Describe what a relational database is, how it organizes data, and how to create a set of well-structured relational database tables.
Copyright © 2018 Pearson Education, Inc.
Chapter 4: Relational Databases
Slide 1 - ‹#›
2
What Is a Database?
Efficiently and centrally coordinates information for a related group of files
A file is a related group of records
A record is a related group of fields
A field is a specific attribute of
interest for the entity (record)
Copyright © 2018 Pearson Education, Inc.
Chapter 4: Relational Databases
Slide 1 - ‹#›
3
Advantages of Databases
Data is integrated
Data sharing
Minimize data redundancy and inconsistencies
Data is independent of the programs that use the data
Data is easily accessed for reporting and cross-functional analysis
Copyright © 2018 Pearson Education, Inc.
Chapter 4: Relational Databases
Slide 1 - ‹#›
4
Database Users and Designers
Different users of the database information are at an external level of the database. These users have logical views of the data.
At an internal level of the database is the physical view of the data which is how the data is actually physically stored in the system.
Designers of a database need to understand user’s needs and the conceptual level of the entire database as well as the physical view.
Copyright © 2018 Pearson Education, Inc.
Chapter 4: Relational Databases
Slide 1 - ‹#›
5
Schemas
Conceptual-level—organization wide view
External-level—individual user’s view
Internal-level—low level view
Copyright © 2018 Pearson Education, Inc.
Chapter 4: Relational Databases
Slide 1 - ‹#›
6
Database Design
To design a database, you need to have a conceptual view of the entire database. The conceptual view illustrates the different files and relationships between the files.
The data dictionary is a “blueprint” of the structure of the database and includes data elements, field types, programs that use the data element, outputs, and so on.
Copyright © 2018 Pearson Education, Inc.
Chapter 4: Relational Databases
Slide 1 - ‹#›
7
DBMS Languages
Data Definition Language (DDL)
Builds the data dictionary
Creates the database
Describes logical views for each user
Specifies record or field security constraints
Data Manipulation Language (DML)
Changes the content in the database
Creates, updates, insertions, and deletions
Data Query Language (DQL)
Enables users to retrieve, sort, and display specific data from the database
Copyright © 2018 Pearson Education, Inc.
Chapter 4: Relational Databases
Slide 1 - ‹#›
Relational Database
Represents the conceptual and external schema as if that “data view” were truly stored in one table.
Although the conceptual view appears to the user that this information is in one big table, it really is a set of tables that relate to one another.
Copyright © 2018 Pearson Education, Inc.
Chapter 4: Relational Databases
Slide 1 - ‹#›
9
Conceptual View Example
| Customer Name | Sales Invoice # | Invoice Total |
| D. Ainge | 101 | $1,447 |
| G. Kite | 102 | $4,394 |
| D. Ainge | 103 | $ 898 |
| G. Kite | 104 | $ 789 |
| F. Roberts | 105 | $3,994 |
Copyright © 2018 Pearson Education, Inc.
Chapter 4: Relational Databases
Slide 1 - ‹#›
10
Relational Data Tables (1 of 2)
Copyright © 2018 Pearson Education, Inc.
Chapter 4: Relational Databases
Slide 1 - ‹#›
Copyright © 2018 Pearson Education, Inc.
Chapter 4: Relational Databases
Slide 1 - ‹#›
11
Relational Data Tables (2 of 2)
Primary Keys
Foreign Key (Customer # is a Foreign key in the Sales table because it is a Primary key that uniquely identifies Customers in the Customer table). Because of this, the Sales table can relate to the Customer table (see red arrow above).
Copyright © 2018 Pearson Education, Inc.
Chapter 4: Relational Databases
Slide 1 - ‹#›
Copyright © 2018 Pearson Education, Inc.
Chapter 4: Relational Databases
Slide 1 - ‹#›
12
Why Have a Set of Related Tables?
Data stored in one large table can be redundant and inefficient causing the following problems:
Update anomaly
Insert anomaly
Delete anomaly
Copyright © 2018 Pearson Education, Inc.
Chapter 4: Relational Databases
Slide 1 - ‹#›
13
Relational Database Design Rules
Every column in a row must be single valued
Primary key cannot be null (empty) also known as entity integrity
If a foreign key is not null, it must have a value that corresponds to the value of a primary key in another table (referential integrity)
All other attributes in the table must describe characteristics of the object identified by the primary key
Following these rules allows databases to be normalized and solves the update, insert, and delete anomalies.
Copyright © 2018 Pearson Education, Inc.
Chapter 4: Relational Databases
Slide 1 - ‹#›
Copyright © 2018 Pearson Education, Inc.
Chapter 4: Relational Databases
Slide 1 - ‹#›
14
Queries
Users may want specific information found in a relational database and not have to sort through all the files to get that information. So they query (ask a question) the data.
An example of a query might be: What are the invoices of customer D. Ainge and who was the salesperson for those invoices?
Copyright © 2018 Pearson Education, Inc.
Chapter 4: Relational Databases
Slide 1 - ‹#›
.
15
Creating the Query
Copyright © 2018 Pearson Education, Inc.
Chapter 4: Relational Databases
Slide 1 - ‹#›
Copyright © 2018 Pearson Education, Inc.
Chapter 4: Relational Databases
Slide 1 - ‹#›
16
Query Answer
Copyright © 2018 Pearson Education, Inc.
Chapter 4: Relational Databases
Slide 1 - ‹#›
Copyright © 2018 Pearson Education, Inc.
Chapter 4: Relational Databases
Slide 1 - ‹#›
Key Terms (1 of 2)
Database
Database management system (DBMS)
Database system
Database administrator (DBA)
Data warehouse
Business intelligence
Online analytical processing (OLAP)
Data mining
Record layout
Logical view
Physical view
Schema
Conceptual-level schema
External-level schema
Subschema
Internal-level schema
Data dictionary
Data definition language (DDL)
Data manipulation language (DML)
Data query language (DQL)
Report writer
Data model
Relational data model
Tuple
Primary key
Foreign key
Copyright © 2018 Pearson Education, Inc.
Chapter 4: Relational Databases
Slide 1 - ‹#›
18
Key Terms (2 of 2)
Update anomaly
Insert anomaly
Delete anomaly
Relational database
Entity integrity rule
Referential integrity rule
Normalization
Semantic data modeling
Copyright © 2018 Pearson Education, Inc.
Chapter 4: Relational Databases
Slide 1 - ‹#›
19
Copyright © 2018 Pearson Education, Inc.
Chapter 4: Relational Databases
Slide 1 - ‹#›
20
