Copyright © 2015 Pearson Education, Inc.
Relational Databases
Chapter 4
Copyright © 2015 Pearson Education, Inc.
Learning Objectives
• Explain the importance and advantages of databases, as well as the difference between database and file-based legacy systems.
• Explain the difference between logical and physical views of a database.
• Explain fundamental concepts of database systems such as DBMS, schemas, the data dictionary, and DBMS languages.
• Describe what a relational database is and how it organizes data.
• Create a set of well-structured tables to properly store data in a relational database.
• Perform simple queries using the Microsoft Access database.
4-2
Copyright © 2015 Pearson Education, Inc.
Introduction
Relational databases underlie most modern
integrated AISs.
• They are the most popular type of database used
for transaction processing.
• In this chapter, we’ll define the concept of a
database with the emphasis on understanding the relational database structure
Copyright © 2015 Pearson Education, Inc.
What Is a Database?
A set of interrelated, centrally coordinated
data files that are stored with as little data
redundancy as possible.
This Figure explain how data are stored in computer systems: 4-3 ًادج هليلق تانايبلا راركت هيناكمإ
Copyright © 2015 Pearson Education, Inc.
What Is a Database?
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 © 2015 Pearson Education, Inc.
File-Oriented System VS. Database
system
Databases were
developed to address the reproduction of master files. For many years, companies created new files and programs each
time a need for
information arose.
Copyright © 2015 Pearson Education, Inc.
File-Oriented System VS. Database
system
• In the database approach, data is used by and managed for the entire organization, not just department which produce the data.
• A database management system (DBMS) is the program that manages and controls the data and the interfaces between the data and the application programs that use the data stored in the database.
• The database, the DBMS, and the application programs that access the database through the DBMS are referred to as the database system.
Copyright © 2015 Pearson Education, Inc.
Data Warehouses
Since strategic decision making requires access to large amounts of historical data, organizations are building separate databases called data warehouses.
Data warehouse is a very large databases containing detailed and summarized data for a number of years that are used for analysis rather than transaction processing.
Data warehouses do not replace transaction processing databases; they complement them by providing support for strategic decision making.
Data warehouses are purposely redundant to maximize query efficiency. ملاعتسلاا ةءافك ةدايزل دمع نع ةدئاز
Copyright © 2015 Pearson Education, Inc.
Business Intelligence.
• Analyzing large amounts of data for strategic
decision making is often referred to as
business intelligence.
• Data warehouses are used for business
intelligence.
• There are two main techniques used in business
intelligence: online analytical processing
Copyright © 2015 Pearson Education, Inc.
Advantages of Databases
• Data integration.
▫ Achieved by combining master files into larger pools of data accessible by many programs.
• Data sharing.
• Minimize data redundancy and data inconsistencies.
• Data is independent.
▫ Data and the programs that use the data are independent of each other.
• Data is easily accessed for reporting and cross-functional analysis.
Copyright © 2015 Pearson Education, Inc.
Database System: Views
• The database approach provides two separate views of the data: the physical view and the logical view.
• Logical views is how the user or programmer
conceptually organizes and understands the data. (an external level of the database)
• Physical view is how and where the data are physically
arranged and stored in the computer system. (an internal level of the database)
• 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 © 2015 Pearson Education, Inc.
Benefit of Separating Physical &
Logical views of data
• Allows the user to access, query, and update data without reference to how or where it is physically stored.
• Users can change their conceptualizations of the data relationships without making changes in the physical storage.
• The database administrator can also change the physical storage of the data without affecting users or application programs.
Copyright © 2015 Pearson Education, Inc.
Database System:
Schemas
• A schema is a description of the data elements
in a database, the relationships among them, and the logical model used to organize and describe the data.
• There are three levels of schemas: the
Copyright © 2015 Pearson Education, Inc.
Database System:
Schemas
• The conceptual-level schema is the organization-wide
view of the entire database
▫ lists all data elements and the relationships among
them.
• The external-level schema is an individual user’s view
of portions of a database,
▫ each of which is referred to as a subschema.
• The internal-level schema, a low-level view of the
database,
Copyright © 2015 Pearson Education, Inc.
Database System: The Data dictionary
• 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. ( refer to table 4-1)
Copyright © 2015 Pearson Education, Inc.
Database System: DBMS Languages
• Data Definition Language (DDL)
▫ Builds the data dictionary and 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
• A report writer simplifies report creation.
Copyright © 2015 Pearson Education, Inc.
Relational Database
• Relational data model 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 data is in one big table, it actually stored as described in the internal-level schema.
Copyright © 2015 Pearson Education, Inc.
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 4-9
Copyright © 2015 Pearson Education, Inc.
Relational Data Tables
Copyright © 2015 Pearson Education, Inc.
Relational Data Tables
4-11
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).
A primary key is the database attribute, or combination of
attributes, that uniquely identifies a specific row in a table.
Copyright © 2015 Pearson Education, Inc.
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 © 2015 Pearson Education, Inc.
Relational Database Design Rules
• Every column in a row must be single valued
• Primary key cannot be null (empty) also known
as entity integrity rule
• Foreign key must either be null or have a value
that corresponds to the value of a primary key in another table (referential integrity rule)
• All other (non-key) attributes in the table must
describe characteristics of the object identified by the primary key
Copyright © 2015 Pearson Education, Inc.
Relational Database Design Rules
Following the previous four rules allows databases to be normalized and solve the update, insert, and delete anomalies.
For example: Deletion of a class for a student would cause the elimination of one record in the student x class table.
• The student still exists in the student table. • The class still exists in the class table.
• Avoid the delete anomaly.
Copyright © 2015 Pearson Education, Inc.
Two Approaches To Database Design
There are two approaches to design well- structured relational databases. One is called normalization, and the other is semantic data modeling.
Normalization
Copyright © 2015 Pearson Education, Inc.
Normalization Approach
• Starts with the assumption that everything is
initially stored in one large table.
• A set of rules is followed to decompose that
initial table into a set of normalized tables.
• Objective is to produce a set of tables in
third-normal form (3NF) because such tables are free of update, insert, and delete anomalies.
Copyright © 2015 Pearson Education, Inc.
Semantic data modeling Approach
• Database designer uses knowledge about how
business processes typically work and the information needs associated with transaction processing to draw a graphical picture of what should be included in the database.
• The resulting graphic is used to create a set of
relational tables that are in 3NF.
Copyright © 2015 Pearson Education, Inc.
Creating Relational Database
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 © 2015 Pearson Education, Inc.
Creating the Query
4-16
Copyright © 2015 Pearson Education, Inc.
