Accounting Information Systems (2)

Lecture (2)
Fourth Year
All Groups
Second Semester 2020-2021
Chapter (4) Relational Databases
Part Dr. Ahmed Mokhtar

Copyright © 2018 Pearson Education, Ltd. Chapter 4: Relational Databases Slide 1 - 1

The difference between Logical & Physical View

• logical view - How people conceptually organize, view, and

understand the relationships among data items.

• physical view - The way data are physically arranged and

stored in the computer system.

Copyright © 2018 Pearson Education, Ltd. Chapter 4: Relational Databases Slide 1 - 2

Schemas
Define Schema and its three different levels

schema - A description of the data elements in a database,

the relationships among them, and the logical model used
to organize and describe the data.
• 1-Conceptual-level—organization wide view
• conceptual-level schema – The organization-wide view of
the entire database that lists all data elements and the
relationships between them. This is the DBA’s view of the
entire database. The database administrator (DBA) is
responsible for coordinating, controlling, and managing
the database.

Copyright © 2018 Pearson Education, Ltd. Chapter 4: Relational Databases Slide 1 - 3

• 2-External-level—individual user’s view
• external-level schema - An individual user’s view of
portions of a database; also called a subschema.
• subschema - A subset of the schema; the way the user
defines the data and the data relationships.

• 3-Internal-level—low level view

• internal-level schema – A low-level view of the entire
database describing how the data are actually stored and
accessed.

Copyright © 2018 Pearson Education, Ltd. Chapter 4: Relational Databases Slide 1 - 4

Copyright © 2018 Pearson Education, Ltd. Chapter 4: Relational Databases Slide 1 - 5
• Example: At S&S, the conceptual schema for the revenue
cycle database contains data about customers, sales, cash
receipts, sales personnel, cash, and inventory. External
subschemas are derived from this schema, each tailored
to the needs of different users or programs. Each
subschema grants specific types of access rights to
those portions of the database the user needs to
perform their duties.
• access rights – Permissions granted to create, read,
update, and delete data, database records, or data files.

Copyright © 2018 Pearson Education, Ltd. Chapter 4: Relational Databases Slide 1 - 6

Data stored in one large table (e.g.Table 4-3.)can be
1-redundant
2-inefficient causing the following problems:
– Update anomaly
– Insert anomaly
– Delete anomaly
• An alternative to Table 4-3 is Table 4-4 is to record sales
invoice and customer data once and add additional
columns to record each item sold.
• Disadvantages:
1- How many columns to put in the table to store each
additional item. Large number of columns.
2-there will be a great deal of wasted space

Copyright © 2018 Pearson Education, Ltd. Chapter 4: Relational Databases Slide 1 - 7

The Solution: A Set of Related Tables. The data
redundancies and storage problems in Tables 4-3 and 4-4
are solved using a relational database. The set of tables
in Table 4-5 represent a well-structured relational
database.

relational database - A database built using the relational

data model.

Copyright © 2018 Pearson Education, Ltd. Chapter 4: Relational Databases Slide 1 - 8

Relational Database
• Represents the conceptual and external schema as if that
“data view” were truly stored in one table.
• data model - An abstract representation
of database contents.
• The relational data model represents conceptual- and
external-level schemas as if data are stored in two-
dimensional tables.
• relational data model – A two-dimensional table
representation of data; each row represents a unique entity
(record) and each column is a field where record attributes
are stored.

Copyright © 2018 Pearson Education, Ltd. Chapter 4: Relational Databases Slide 1 - 9

• 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.
• A relational database is a collection of two-dimensional
tables with each table representing an object about which
we wish to collect and store information. Each row in a
table, called a tuple, contains data about a specific
occurrence of an entity. Each column contains data
about an attribute of that entity.

Copyright © 2018 Pearson Education, Ltd. Chapter 4: Relational Databases Slide 1 - 10

• Basic requirements of a relational database
1. Every column in a row must be single valued. In a
relational database, there can only be one value per
cell.

2.Primary keys cannot be null. A primary key cannot

uniquely identify a row in a table if it is null (blank). A
nonnull primary key ensures that every row in a table
represents something and that it can be identified. This is
referred to as the entity integrity rule.

Copyright © 2018 Pearson Education, Ltd. Chapter 4: Relational Databases Slide 1 - 11

3. Foreign keys, if not null, must have values that
correspond to the value of a primary key in another
table. Foreign keys link rows in one table to rows in
another table.
• referential integrity rule – Foreign keys which link rows in
one table to rows in another table must have values that
correspond to the value of a primary key in another table.
4. All nonkey attributes in a table must describe a
characteristic of the object identified by the primary
key. Most tables contain other attributes in addition to the
primary and foreign keys.

Copyright © 2018 Pearson Education, Ltd. Chapter 4: Relational Databases Slide 1 - 12

• These previous four constraints produce a well-structured
(normalized) database in which data are consistent and
data redundancy is minimized and controlled.
• Normalization will be discussed in the next lecture

Copyright © 2018 Pearson Education, Ltd. Chapter 4: Relational Databases Slide 1 - 13

Key Terms (1 of 2)
• Database • External-level schema
• Database management system • Subschema
(DBMS) • Internal-level schema
• Database system • Data dictionary
• Database administrator (DBA) • Data definition language (DDL)
• Data warehouse • Data manipulation language (DML)
• Business intelligence • Data query language (DQL)
• Online analytical processing (OLAP) • Report writer
• Data mining • Data model
• Record layout • Relational data model
• Logical view • Tuple
• Physical view • Primary key
• Schema • Foreign key
• Conceptual-level schema

Copyright © 2018 Pearson Education, Ltd. Chapter 4: Relational Databases Slide 1 - 14

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, Ltd. Chapter 4: Relational Databases Slide 1 - 15