Python for Data Science:

Data Science with Python

Author: Aamir Khan
Second Edition
Preface
Data science combines
programming, statistics and
domain knowledge to transform
raw data into useful insights. This
textbook introduces Python from
first principles through practical
examples.

In today’s digital world, data is

generated at an unprecedented
rate. Every online purchase,
financial transaction, healthcare
record, social media interaction,
scientific experiment, and business
operation produces valuable
information that can be transformed
into meaningful insights. The ability
to collect, analyse, and interpret this
information has become one of the
most sought-after skills across
industries. Python for Data Science:
A Practical Guide has been written
to equip learners with the
knowledge and practical skills
required to work confidently with
data using one of the world’s most
popular programming languages.
This book is designed for beginners,
students, educators, and
professionals who wish to build a
strong foundation in Python
programming and data science. No
prior programming experience is
assumed. Each chapter introduces
concepts in a logical sequence,
beginning with Python
fundamentals before progressing to
data manipulation, visualisation,
statistical analysis, machine
learning, and predictive modelling.
Every topic is supported by practical
examples, original diagrams, code
samples, review questions, and
hands-on exercises that encourage
active learning and problem-solving.

Rather than focusing solely on

theory, this book emphasises
practical application. Readers will
learn how to import, clean, analyse,
visualise, and interpret data using
widely adopted Python libraries
such as NumPy, pandas, Matplotlib,
Seaborn, and scikit-learn. Realistic
examples demonstrate how these
tools can be applied to solve
problems in business, finance,
healthcare, education, engineering,
and scientific research.

The field of data science continues

to evolve rapidly, creating exciting
opportunities for innovation and
informed decision-making. It is our
hope that this book will inspire
curiosity, strengthen analytical
thinking, and provide readers with
the confidence to explore
increasingly advanced topics in data
science. Whether your goal is
academic success, career
development, or personal growth,
the knowledge gained from this
book will serve as a valuable
foundation for your journey into the
world of Python and data science.
Chapter 1 Getting Started with
Python for Data Science
Learning Outcomes
•Define data science?
•Explain the workflow?
•Install Python. ?
•Write basic Python programs. ?
1.1 Understanding Data Science
Data science is the discipline of
collecting, preparing, analysing
and communicating data to
support evidence-based decisions.
It combines programming,
mathematics, statistics and
domain expertise.
Industry Application
Banking Fraud detection
Healthcare Disease
prediction
Retail Recommendatio
n systems
Education Student
performance
analys
1.2 The Data Science Workflow
[Link] the problem
[Link] data
[Link] data
4. Explore data
5. Build models
6. Communicate results
Example Code
print('Welcome
to Python for
Data Science!')
Chapter 2: Working with Data Using
NumPy and pandas

2.1 Introduction to NumPy

NumPy, short for Numerical

Python, is one of the most
important libraries in the Python
programming ecosystem and
serves as a cornerstone of
modern data science, scientific
computing, and machine
learning. It was developed to
provide an efficient way of
performing numerical
computations using
multidimensional arrays and
mathematical functions. While
Python itself is a versatile
programming language, its built-
in data structures, such as lists,
are not optimized for handling
large-scale numerical data.
NumPy addresses this limitation
by introducing a powerful array
object that allows numerical data
to be stored and processed
efficiently. As a result, NumPy
has become the foundation upon
which many other data science
libraries, including pandas, SciPy,
Matplotlib, TensorFlow, and
scikit-learn, are built.

One of the key reasons for

NumPy’s popularity is its ability
to perform operations on entire
datasets simultaneously through
a technique known as
**vectorization**. Instead of
processing one value at a time
using loops, NumPy applies
calculations to all elements in an
array at once. This significantly
improves computational speed
while reducing the amount of
code that programmers need to
write. For example, adding a
constant value to every element
in a NumPy array requires only a
single statement, whereas
performing the same operation
with a Python list typically
requires a loop. This
combination of simplicity and
efficiency makes NumPy
particularly valuable when
working with large datasets in
research, business, engineering,
and finance.
At the heart of NumPy is the
**ndarray** (n-dimensional
array), which is a flexible data
structure capable of storing
values in one or more
dimensions. A one-dimensional
array resembles a simple list of
values, while two-dimensional
arrays can represent tables or
matrices consisting of rows and
columns. NumPy also supports
arrays with three or more
dimensions, making it suitable
for handling complex datasets
such as images, videos, and
scientific measurements. Unlike
Python lists, NumPy arrays store
elements of the same data type
in contiguous memory locations.
This memory-efficient design
allows calculations to be
executed much faster and with
lower memory consumption,
which is essential when
processing millions of records.

NumPy provides an extensive

collection of mathematical
functions that simplify data
analysis. Users can calculate
descriptive statistics such as the
mean, median, standard
deviation, minimum, and
maximum values with only a few
lines of code. It also includes
functions for performing
algebraic operations, matrix
multiplication, trigonometric
calculations, logarithms,
exponential functions, and
random number generation.
These built-in capabilities
eliminate the need to implement
complex mathematical formulas
manually, allowing data
scientists to focus on analysing
and interpreting results rather
than developing low-level
computational routines.

Another important advantage of

NumPy is its compatibility with
the wider Python data science
ecosystem. Libraries such as
**pandas** rely on NumPy
arrays to store and manipulate
tabular data efficiently. Similarly,
**Matplotlib** uses NumPy
arrays to generate graphs and
visualizations, while **scikit-
learn** uses them as input for
machine learning algorithms.
Because of this close integration,
learning NumPy is considered an
essential first step for anyone
interested in data analysis,
artificial intelligence, machine
learning, or scientific research.
Mastering NumPy provides
learners with the skills required
to work confidently with these
advanced libraries.

NumPy also supports a wide

range of array operations,
including indexing, slicing,
reshaping, sorting, filtering, and
broadcasting. Indexing allows
individual values to be accessed
directly, while slicing enables
users to extract subsets of data
for further analysis. Reshaping
changes the dimensions of an
array without altering the
underlying data, making it easier
to organise information into
formats suitable for
computation. Broadcasting is
another powerful feature that
automatically performs
mathematical operations
between arrays of different but
compatible shapes, reducing the
need for repetitive programming
code. These capabilities allow
programmers to manipulate
datasets efficiently while
maintaining readable and
maintainable programs.

In practical applications, NumPy

is used across numerous
industries. Financial institutions
use it to analyse investment
portfolios and model market
behaviour. Healthcare
organisations apply NumPy to
process medical records and
analyse patient data for research
and diagnosis. Scientists use it to
perform simulations and analyse
experimental results, while
engineers rely on it for
numerical modelling and
optimisation. Businesses employ
NumPy to examine sales trends,
customer behaviour, and
operational performance,
enabling better decision-making
based on data-driven insights.
Because of its versatility and
performance, NumPy has
become an indispensable tool in
both academic research and
commercial applications.

In conclusion, NumPy is much

more than a simple Python
library; it is the computational
engine that powers much of
modern data science and
scientific computing. Its efficient
multidimensional arrays,
extensive mathematical
functionality, memory
optimisation, and seamless
integration with other Python
libraries make it an essential tool
for analysing numerical data.
Whether developing machine
learning models, performing
statistical analysis, creating
visualisations, or conducting
scientific research, NumPy
provides the speed, reliability,
and flexibility needed to
transform raw data into
meaningful information.
Learning NumPy establishes a
strong foundation for further
study in data science and equips
learners with practical skills that
are highly valued across many
professional fields.
2.2 Installing and Importing NumPy

Before the powerful features of

NumPy can be used, the library
must first be installed and imported
into the Python programming
environment. NumPy is not included
in every standard Python
installation, so it is typically installed
using Python’s package manager,
known as **pip**. Pip is a tool that
downloads, installs, updates, and
manages third-party Python
packages from the Python Package
Index (PyPI). Installing libraries
through pip ensures that users have
access to the latest stable versions,
bug fixes, and security updates
released by the development
community.

The installation process is

straightforward. Users simply open
the Command Prompt on Windows,
the Terminal on macOS or Linux, or
the terminal window within an
Integrated Development
Environment (IDE) such as Visual
Studio Code, PyCharm, or Jupyter
Notebook, and execute the
following command:

```bash
Pip install numpy
```

After pressing **Enter**, pip

connects to the online package
repository, downloads the
appropriate version of NumPy,
installs the necessary files, and
configures the library for use within
Python. Depending on the speed of
the internet connection and the
computer’s specifications, the
installation usually takes only a few
moments. Once completed
successfully, NumPy becomes
available for use in any Python
program running within the same
environment.

Following installation, the next step

is to import the library into a Python
script or Jupyter Notebook. This is
accomplished using the import
statement:

```python
Import numpy as np
```

In this statement, **numpy** refers

to the library itself, while **np** is
an alias assigned to it. An alias is a
shorter alternative name that
simplifies writing code. Instead of
repeatedly typing `[Link]()` or
`[Link]()`, programmers can
use the shorter forms `[Link]()`
and `[Link]()`. This convention is
recognised worldwide and is used
consistently in textbooks, university
courses, research publications, and
professional software development.
Adopting this standard notation
improves code readability and
makes it easier to understand
examples from other programmers.

After importing the library, it is good

practice to verify that NumPy has
been installed correctly. This can be
achieved by displaying the installed
version number using the following
code:

```python
Import numpy as np

Print(np.__version__)
Executing this program returns the
version of NumPy currently installed
on the computer, confirming that
the library is available for use.
Verifying the version is particularly
useful when following tutorials or
collaborating on projects, as some
functions and features may differ
slightly between releases. Keeping
NumPy up to date ensures
compatibility with other Python
libraries and provides access to
performance improvements, new
functionality, and important bug
fixes.

Successfully installing and importing

NumPy marks an important
milestone in learning Python for
data science. Once the library has
been configured, users can begin
creating arrays, performing
mathematical computations,
manipulating datasets, and building
efficient analytical solutions using
the extensive collection of tools that
NumPy provides.
2.3 Creating Arrays
Use [Link]() to create one- and
two-dimensional arrays. Arrays
provide efficient storage and
computation. Arrays are designed
to store collections of related
values in a structured format that
enables fast access, manipulation,
and mathematical processing.
Unlike ordinary Python lists,
NumPy arrays require that all
elements share the same data
type, such as integers, floating-
point numbers, or Boolean values.
This uniformity allows NumPy to
allocate memory more efficiently
and execute operations
significantly faster than standard
Python data structures. As
datasets increase in size, the
performance advantages of
NumPy arrays become
increasingly apparent, making
them the preferred choice for
scientific computing and data
analysis.

A one-dimensional array is the

simplest form of a NumPy array and
consists of a single sequence of
elements arranged in a straight line.
It is commonly used to represent a
list of numerical observations, such
as daily temperatures, monthly sales
figures, examination marks, or stock
prices. One-dimensional arrays are
easy to create using the `[Link]()`
function by passing a Python list as
the argument. Once created,
individual elements can be accessed
using their index positions, modified
when necessary, or processed using
built-in mathematical functions.
Two-dimensional arrays extend this
concept by organising data into rows
and columns, creating a matrix-like
structure. This format is particularly
useful for representing tabular data
such as spreadsheets, survey
responses, financial records, student
databases, and experimental results.
Each row typically represents an
observation, while each column
corresponds to a specific variable or
attribute. The ability to organise
information in this manner allows
data scientists to perform complex
analyses efficiently while
maintaining a logical structure for
the dataset.

NumPy also supports arrays with

three or more dimensions, often
referred to as multidimensional
arrays. These structures are widely
used in specialised applications
including image processing, medical
imaging, scientific simulations,
climate modelling, and artificial
intelligence. For example, a colour
image can be represented as a
three-dimensional array where the
first two dimensions correspond to
the image’s height and width, while
the third dimension stores the red,
green, and blue (RGB) colour
channels for each pixel. This
multidimensional capability enables
NumPy to process highly complex
datasets with remarkable efficiency.

Another important feature of

NumPy arrays is their ability to
perform vectorised operations.
Instead of applying calculations to
one element at a time using loops,
NumPy executes mathematical
operations simultaneously across all
elements within an array. This
approach reduces the amount of
code required while significantly
improving execution speed.
Operations such as addition,
subtraction, multiplication, division,
exponentiation, and statistical
calculations can therefore be
performed on entire datasets with a
single statement, making programs
shorter, more readable, and easier
to maintain.

Creating arrays using `[Link]()`

establishes the foundation for many
advanced data science techniques.
Once data has been organised into
arrays, it can be indexed, sliced,
reshaped, filtered, sorted, and
combined with other datasets.
These operations form the basis of
data preparation and analysis,
allowing users to transform raw
information into formats suitable for
statistical analysis, data
visualisation, predictive modelling,
and machine learning.
Consequently, understanding how
to create and work with NumPy
arrays is an essential skill for anyone
seeking to develop expertise in
Python programming and data
science.

2.4 Mathematical Operations

NumPy performs element-wise
arithmetic, aggregation (mean,
min, max), and vectorized
calculations without explicit loops.
Numerical operations are one of the
most powerful features provided by
NumPy. Unlike standard Python lists,
which often require loops to
perform calculations on multiple
values, NumPy allows mathematical
operations to be carried out on
entire arrays simultaneously. This
capability, known as **vectorized
computation**, significantly
improves both the speed and
efficiency of numerical processing.
By applying a single operation to
every element in an array at once,
NumPy reduces the amount of code
required while increasing program
performance, particularly when
working with large datasets.

NumPy supports a wide range of

arithmetic operations, including
addition, subtraction, multiplication,
division, exponentiation, and
modulus. These operations can be
performed between an array and a
scalar value or between two arrays
of compatible dimensions. For
example, adding a constant value to
an array increases every element by
the same amount, while multiplying
an array by a scalar scales each
value proportionally. Such
operations are commonly used in
data preprocessing, statistical
analysis, and scientific computing.
In addition to basic arithmetic,
NumPy includes numerous
mathematical functions for
analysing numerical data. Functions
such as `[Link]()`, `[Link]()`,
`[Link]()`, `[Link]()`,
`[Link]()`, and `[Link]()` enable
users to calculate descriptive
statistics quickly and accurately.
These functions eliminate the need
for manual calculations and provide
valuable insights into the
characteristics of a dataset. For
instance, the mean provides the
average value, while the standard
deviation measures the spread or
variability of the data.

NumPy also supports element-wise

mathematical functions, allowing
calculations to be performed
independently on every element
within an array. Functions such as
`[Link]()`, `[Link]()`, `[Link]()`,
`[Link]()`, and trigonometric
functions including `[Link]()`,
`[Link]()`, and `[Link]()` are widely
used in engineering, finance,
physics, and machine learning
applications. Because these
functions operate directly on arrays,
they execute much faster than
equivalent calculations
implemented using traditional
Python loops.

Another important aspect of

numerical operations is
broadcasting. Broadcasting allows
NumPy to perform arithmetic
operations between arrays of
different shapes, provided their
dimensions are compatible. This
feature simplifies complex
calculations by automatically
expanding smaller arrays to match
the dimensions of larger ones
without duplicating data in memory.
Broadcasting reduces programming
complexity while maintaining high
computational efficiency, making it
especially useful when analysing
multidimensional datasets.

Numerical operations in NumPy

form the foundation of many
advanced data science techniques.
They are used extensively in data
cleaning, feature engineering,
statistical modelling, optimisation,
machine learning, and scientific
research. By combining high
computational speed with an
extensive collection of mathematical
functions, NumPy enables
programmers and data scientists to
manipulate and analyse numerical
data accurately and efficiently.
Mastering these operations is
therefore an essential step towards
developing practical skills in Python-
based data science and numerical
computing.
Figure 2.1 (Diagram)
Python
├─ NumPy
│ ├─ pandas
│ ├─ Matplotlib
│ └─ scikit-learn
Figure 2.2 (Suggested Bar Chart
Data)
Subject Score
Python 95
SQL 88
Statistics 84
Visualization 91
Machine
Learning 86
Part 2
2.6 Array Indexing
Access elements using positive
and negative indexes.
2.7 Array Slicing
Extract subsets using
start:stop:step notation.
2.8 Array Reshaping and
Broadcasting
Use reshape() to reorganize arrays
and broadcasting for efficient
arithmetic.
Figure 2.3 (Diagram)
1-D Array -> reshape(3,4) -> 2-D
Matrix
Figure 2.4 (Suggested Line Chart
Data)
Month Sales
Jan 120
Feb 145
Mar 158
Apr 170
May 190
Jun 210

2.9 Boolean Indexing

Boolean indexing allows arrays to
be filtered using logical
expressions. This technique is
widely used to isolate records that
satisfy conditions, such as
selecting students with marks
above a threshold or identifying
transactions above a specified
value.
Example:
import numpy as np
marks=[Link]([55,
68,74,81,93])
print(marks[marks>
=70])
Figure 2.5 Boolean Filtering
Diagram
[55,68,74,81,93] -> condition >=70
-> [74,81,93]
2.10 Introduction to pandas
pandas builds on NumPy to
provide labelled data structures
for tabular data. The Series object
stores one-dimensional labelled
data, while the DataFrame stores
two-dimensional tables with rows
and columns.
2.11 Series and DataFrames
Series are suitable for a single
variable, whereas DataFrames
manage complete datasets.
Common operations include
selecting columns, filtering rows,
sorting, grouping and
summarising values.
Student Python Statistic
s
Alice 85 81
Brian 90 88
Carol 76 79
Figure 2.6 Suggested Scatter Plot
Dataset
Hours Studied vs Final Score
(2,60) (4,68) (6,78) (8,90) (10,96)
2.12 Reading CSV Files
Use pandas.read_csv() to import
comma-separated files for
analysis. Inspect data with head(),
info(), and describe().
3.4 Key Takeaways
Chapter Summary
This part introduced Boolean indexing, pandas Series, DataFrames, and importing datasets.
These concepts provide the foundation for data cleaning, exploration and visualization.

Review Questions
1. 1. Explain an important concept from this chapter. 2. 2.
Explain an important concept from this chapter. 3. 3.
Explain an important concept from this chapter. 4. 4.
Explain an important concept from this chapter.
5. 5. Explain an important concept from this chapter.
Understanding the Relational
Database Model
Unit 3

What is a Relational Database?

• Stores data in tables (like spreadsheets)
• Each table = entity (e.g., Invoice)
• Rows = records (e.g., each invoice) Columns
= attributes (e.g., Date, Amount)

•
 Table Characteristics
• Each row must be unique
• Columns must have distinct names and types
• Cells hold a single value (atomic) Order of
rows/columns doesn't matter

•
 Each table row represents a single
entity occurrence within the entity

Database Systems: Design,

Implementation, &
Management, International
Edition, Rob, Coronel &
Crockett
 set

Database Systems: Design,

Implementation, &
Management, International
Edition, Rob, Coronel &
Crockett
 distinct name

Database Systems: Design,

Implementation, &
Management, International
Edition, Rob, Coronel &
Crockett
 Each table column represent an
attribute, and each column has a

Database Systems: Design,

Implementation, &
Management, International
Edition, Rob, Coronel &
Crockett
 Each table column represent an
attribute, and each column has a

Database Systems: Design,

Implementation, &
Management, International
Edition, Rob, Coronel &
Crockett
 distinct name

Database Systems: Design,

Implementation, &
Management, International
Edition, Rob, Coronel &
Crockett
 Each intersection of a row and
value

Database Systems: Design,

Implementation, &
Management, International
Edition, Rob, Coronel &
Crockett
 column represents a single data

Database Systems: Design,

Implementation, &
Management, International
Edition, Rob, Coronel &
Crockett
 All values in a column must conform
to the same data format

Database Systems: Design,

Implementation, &
Management, International
Edition, Rob, Coronel &
Crockett
Database Systems: Design,
Implementation, &
Management, International
Edition, Rob, Coronel &
Crockett
 Each table must have an attribute or
combination of attributes that

Database Systems: Design,

Implementation, &
Management, International
Edition, Rob, Coronel &
Crockett
 uniquely identifies each row

Database Systems: Design,

Implementation, &
Management, International
Edition, Rob, Coronel &
Crockett
 The order of the rows and columns is
immaterial to the DBMS

Database Systems: Design,

Implementation, &
Management, International
Edition, Rob, Coronel &
Crockett
 Keys in Databases
• Primary Key: Uniquely identifies rows
• Foreign Key: Links to a primary key in another
table
• Composite Key: Made of more than one
column
B. Keys
• Keys consist of one or more attributes that determine other
attributes
• Candidate key is an attribute that determines all the other
attributes in the relation.
• A candidate key is a column, or set of columns, in a table that
can uniquely identify any database record without referring to
any other data. Each table may have one or more candidate
keys, but one candidate key is unique, and it is called the
primary key. This is usually the best among the candidate keys
to use for identification.

Keys
• Primary key (PK) is an attribute that uniquely
identifies any given row
• Composite key is a key composed of more than
one attribute eg in a Dependent table,
Employee number and Department number
might be a composite key
• Foreign key (FK) is an attribute whose values
match primary key values in the related table
Database Systems: Design,
Implementation, &
Management, International
Edition, Rob, Coronel &
 B. Keys - Example

Foreign key
232 and 235
Occur twice
In PRODUCT
table
Primary key in VENDOR table
Database Systems: Design, Implementation, & Management, International Edition, Rob, Coronel & Crockett
B. Keys - Composite
 Database Systems: Design, Implementation, & Management, International Edition, Rob, Coronel & Crockett Primary
key in VENDOR table

Integrity Rules
• Entity Integrity: No duplicate PKs, no NULL PK
• Referential Integrity: FK must match a PK or be
NULL
• Ensures reliable and valid accounting data
Relational schema
• A textual representation of database tables,
where each table is described by its name
followed by a list of its attributes in parenthesis
– R = {a1 , a2 , a3 …. an}
– Student (Stud_Num, FirstName, Surname, Age)
Student

Stud_Num FirstName Surname Age

 Relational Schema
• A relational schema is a blueprint for how a
relational database is structured.
• It outlines tables (entities), their columns
(attributes), and the relationships between
tables.
Components of a Relational Schema
• • Table (Entity): Represents a category of data
(e.g., Clients, Invoices).
• • Attributes (Columns): Specific data stored in
each table (e.g., Name, Amount).
• • Primary Key (PK): Uniquely identifies each row in
a table.
• • Foreign Key (FK): Connects a table to another
table.
• • Surrogate Key: System-generated ID used
instead of real-world identifiers.
Example Scenario – Accounting System
• Client(ClientID, Name, Email)
• Invoice(InvoiceID, ClientID, InvoiceDate,
Amount) – PK: InvoiceID, FK: ClientID
• Payment(PaymentID, InvoiceID, PaymentDate,
AmountPaid) – PK: PaymentID, FK: InvoiceID
 Entity: Client

Redundancy and Nulls

• Avoid repeating data unnecessarily
• Use FKs instead of duplicating details
• Avoid NULLs in primary key columns
 Relationships in Accounting
• 1:Many – One client has many invoices
• 1:1 – One bank account per accountant
• Many:Many – Resolved via a junction table
Relationships in Accounting
• 1:Many – One client has many invoices
• 1:1 – One bank account per accountant
• Many:Many– Resolved via a junction table
 E. Relationships within the Relational
E1. 1:* relationship Database
– Relational modeling ideal
– Should be the norm in any relational database design
E2. 1:1 relationship
– Should be rare in any relational database design
E3. *:* relationships
– Cannot be implemented as such in the relational model
– *:* relationships can be changed into two 1:* relationships
 E1. The 1:* Relationship
• Relational database norm
• Found in any database environment
E1. The 1:* Relationship
Database Systems: Design, Implementation, & Management, International Edition, Rob, Coronel & Crockett
 E2. The 1:1 Relationship

• One entity can be related to only one other

entity, and vice versa
• Sometimes means that entity components
were not defined properly
• Could indicate that two entities actually
belong in the same table
 • As rare as 1:1 relationships should be, certain
conditions absolutely require their use
Database Systems: Design,
Implementation, &
Management, International
Edition, Rob, Coronel &

E2. The 1:1 Relationship

 E3. The *:* Relationship
• Can be implemented by breaking it up to
produce a set of 1:* relationships
• Can avoid problems inherent to *:*
relationship by creating a composite entity or
bridge entity
E3. The *:* Relationship
 Indexes
• Arrangement used to logically access rows in a table like
finding a book in the library or a topic in a book

Database Systems: Design,

Implementation, &
Management, International
Edition, Rob, Coronel &

Index
• An index is composed of an index key and a set of pointers
– Index key is an index’s reference point. Eg the primary key
– Pointers are identifiers, eg numbers that point to the data location
identified by the key

• A unique index is one in which the index key

can have only one pointer value (row)
associated with it
Database Systems: Design,
Implementation, &
Management, International
Edition, Rob, Coronel &

Indexes in Databases
• Help speed up data searches
• Use on frequently accessed columns like
InvoiceDate
• Like a table of contents for a database
 More Key Concepts
• A surrogate key is a system-generated unique
ID used when no natural key exists.
• • Natural keys are real-world identifiers (e.g.,
ID number).
• • Surrogate keys are useful for simplifying
relationships.
 Functional Dependency in Relational
Models
• If you know the InvoiceID, you can determine
the Invoice Date, Amount, and ClientID.
• This is called functional dependency: A → B (A
determines B).
• Understanding dependencies helps with
normalization.
 Normalization
• Normalization removes data duplication and
improves data integrity.
– 1NF: No repeating groups, atomic values.
– 2NF: No partial dependencies (only applies to
composite keys).
– 3NF: No transitive dependencies (non-key
attributes depend only on PK).
 Quiz – Test Your Knowledge
• 1. What is a primary key?
• 2. Why use foreign keys?
• 3. What is referential integrity?
• 4. How does indexing help?
• 5. Why avoid NULLs in PKs?
 DATABASE SYSTEMS: Design Implementation and Management (Rob,
Coronel & Crockett 9781844807321)

Normalization

A. Database tables and normalization

1
 DATABASE SYSTEMS: Design Implementation and Management (Rob,
Coronel & Crockett 9781844807321)
B. The need for normalization
C. The normalization process
D. Improving the design
E. Surrogate key considerations

2
 DATABASE SYSTEMS: Design Implementation and Management (Rob,

A. Database Tables and

NormalizationCoronel & Crockett 9781844807321)
• Normalization
• Process for evaluating and correcting table structures to minimize data
redundancies
• Reduces data anomalies
• Works through a series of stages called normal forms:
• First normal form (1NF)
• Second normal form (2NF)
• Third normal form (3NF)

3
DATABASE SYSTEMS: Design Implementation and Management (Rob,
Coronel & Crockett 9781844807321)
• 2NF is better than 1NF; 3NF is better than 2NF
• For most business database design purposes, 3NF is as high as we need to go in
normalization process
• Highest level of normalization is not always most desirable
B. The Need for Normalization
• Example: Company that manages building projects
• Charges its clients by billing hours spent on each contract
• Hourly billing rate is dependent on employee’s position
• Periodically, report is generated that contains information displayed in Table 7.1

4
DATABASE SYSTEMS: Design Implementation and Management (Rob,

5
 DATABASE SYSTEMS: Design Implementation and Management (Rob,
Coronel & Crockett 9781844807321)
B. The Need for Normalization
Coronel & Crockett 9781844807321)

6
 DATABASE SYSTEMS: Design Implementation and Management (Rob,

Do You Still Remember the

Characteristics of a relational table?
• A table is perceived as a two dimensional structure composed of rows and
columns
• Each row (tuple) represents a single entity and must be distinct
• Each table column represents an attribute, and each column has a distinct
name
• Each cell should contain an atomic value – single cell value
Coronel & Crockett 9781844807321)

7
DATABASE SYSTEMS: Design Implementation and Management (Rob,
Coronel & Crockett 9781844807321)

Characteristics of a
relational table
• All values in a column must conform to the same data format
• Each column has a specific range of values known as the attribute
domain
• The order of the rows and columns is immaterial to the DBMS
• Each table must have an attribute that uniquely identifies each row

8
DATABASE SYSTEMS: Design Implementation and

B. The Need for

Management (Rob,

Normalization
Coronel & Crockett 9781844807321)

Easiest approach
• You might decide to set up a table with all the information in the
format you want in your report

9
DATABASE SYSTEMS: Design Implementation and Management (Rob,
Coronel & Crockett 9781844807321)
• This is often done in spreadsheets

1
0
1
1
 DATABASE SYSTEMS: Design Implementation and Management (Rob,
Coronel & Crockett 9781844807321)

B. The Need for

NormalizationDATABASE SYSTEMS: Design Implementation and
Management (Rob,
Coronel & Crockett 9781844807321)

1
2
 B. The Need for Normalization
• The table structure appears to work because the report can be generated
with ease
• Unfortunately, report may yield different results depending on what data
anomaly has occurred
• Unnecessary repetition of data (redundancy)
• Ease of making spelling mistakes in multiple occurrences of the same data

1
3
DATABASE SYSTEMS: Design Implementation and Management (Rob,
Coronel & Crockett 9781844807321)

B. The Need for Normalization

• Structure of data set in Figure 7.1 does not handle data very well
• Many opportunities for data anomalies due to repeatedly inputting the
same information
• Update anomalies – modifying one attribute may require changes in several
records
• Insertion anomalies – Can only complete a project record if you already have an
assigned employee
• Deletion anomalies – delete an employee may delete a project

1
4
DATABASE SYSTEMS: Design Implementation and Management (Rob,
Coronel & Crockett 9781844807321)

C. The Normalization Process

Objectives of normalization
1. Each table should represent a single entity
2. No data item is to be unnecessarily stored in more than one table
(redundancy)
3. All attributes in a table should be dependent on the primary key

1
5
 DATABASE SYSTEMS: Design Implementation and Management (Rob,
Coronel & Crockett 9781844807321)

Normal Forms
Normal Form Characteristics Section

First normal form (1NF) Table format: no repeating groups and PK identified 7.3.1

Second normal form (2NF) 1NF and no partial dependencies 7.3.2

Third normal form (3NF) 2NF and no transitive dependencies 7.3.3

Boyce-Codd normal form (BCNF) Every determinant is a candidate key (special case of 7.6.1
3NF)

Fourth normal form (4NF) 3NF and no independent multivalued dependencies 7.6.2

1
6
DATABASE SYSTEMS: Design Implementation and Management (Rob,
Coronel & Crockett 9781844807321)

Dependencies
• Partial dependency
• Attributes which are only dependent on part of the composite primary key
• Transitive dependency
• Attribute is dependent on any other attribute except the primary key

1
7
DATABASE SYSTEMS: Design Implementation and Management (Rob,
Coronel & Crockett 9781844807321)

C1. Conversion to First Normal Form

• A repeating group is an attribute, or group of attributes, within a table that
occurs with multiple values for a single occurrence of the nominated key
attribute(s) for that table

1
8
DATABASE SYSTEMS: Design Implementation and Management (Rob,
Coronel & Crockett 9781844807321)

•T
hi
s
is

data in an unnormalized form (UNF)

• A relational table must not contain repeating groups
• Normalizing table structure will reduce data redundancies
• Normalization is three-step procedure
C1. Conversion to First Normal Form
• Step 1: Eliminate the Repeating Groups

1
9
DATABASE SYSTEMS: Design Implementation and Management (Rob,
Coronel & Crockett 9781844807321)

• Present data in tabular format, where each cell has a single value and there are no
repeating groups
• Try to eliminate nulls by making sure that each repeating group attribute contains
an appropriate data value.
• If you can’t eliminate nulls at least reduce them as much as possible

2
0
DATABASE SYSTEMS: Design Implementation and Management (Rob,
Coronel & Crockett 9781844807321)

C1. Conversion to First Normal Form

2
1
DATABASE SYSTEMS: Design Implementation and Management (Rob,
Coronel & Crockett 9781844807321)

C1. Conversion to First Normal Form

2
2
DATABASE SYSTEMS: Design Implementation and Management (Rob,
Coronel & Crockett 9781844807321)

C1. Conversion to First Normal Form

• Step 3: Identify All Dependencies
• Dependencies can be depicted with the help of a diagram called a dependency
diagram
• Dependency diagram:
• Depicts all functional dependencies (FD) found within a given table structure

2
3
DATABASE SYSTEMS: Design Implementation and Management (Rob,

2
4
 DATABASE SYSTEMS: Design Implementation and Management (Rob,

C1. Conversion to First Normal

FormCoronel & Crockett 9781844807321)

2
5
 DATABASE SYSTEMS: Design Implementation and Management (Rob,

C1. Conversion to First Normal

FormCoronel & Crockett 9781844807321)
• First normal form describes the tabular format in which:
• There are no repeating groups in the table
• Each cell contains one value
• All key attributes are defined
• All relational tables must satisfy 1NF requirements
• Some tables contain partial dependencies
• Dependencies based on only part of the primary key
• Sometimes used for performance reasons, but should be used
with caution

2
6
 DATABASE SYSTEMS: Design Implementation and Management (Rob,
• Still subject to data redundancies
• Transitive dependency is a dependency of one non-primary key
attribute on another non-primary key attribute
Coronel & Crockett 9781844807321)
Normal Form Characteristics Section

First normal form (1NF) Table format: no repeating groups and PK identified 7.3.1

Second normal form (2NF) 1NF and no partial dependencies 7.3.2

Third normal form (3NF) 2NF and no transitive dependencies 7.3.3

Boyce-Codd normal form (BCNF) Every determinant is a candidate key (special case of 7.6.1
3NF)

Fourth normal form (4NF) 3NF and no independent multivalued dependencies 7.6.2

2
7
DATABASE SYSTEMS: Design Implementation and Management (Rob,

Normal Forms

2
8
 DATABASE SYSTEMS: Design Implementation and Management (Rob,
Coronel & Crockett 9781844807321)

C2. Conversion to Second Normal Form

• Relational database design can be improved by converting the database
into second normal form (2NF)
• Table is in second normal form (2NF) when:
• It is in 1NF and
• It includes no partial dependencies:
• No attribute is dependent on only a portion of primary key
• Two steps

2
9
 DATABASE SYSTEMS: Design Implementation and Management (Rob,

C2. Conversion to Second

Normal FormCoronel & Crockett 9781844807321)

• Step 1: Write each PK component on a separate line

• Write original (composite) key on last line
• Each component of PK will become PK in a new table
• Step 2: Assign corresponding dependent attributes
• Determine those attributes that are dependent on other attributes
• At this point, most anomalies have been eliminated

3
0
DATABASE SYSTEMS: Design Implementation and Management (Rob,

3
1
 DATABASE SYSTEMS: Design Implementation and Management (Rob,

C1. Conversion to First Normal

FormCoronel & Crockett 9781844807321)

3
2
DATABASE SYSTEMS: Design Implementation and Management (Rob,

3
3
 DATABASE SYSTEMS: Design Implementation and Management (Rob,

C2. Conversion to Second

Normal FormCoronel & Crockett 9781844807321)

3
4
 DATABASE SYSTEMS: Design Implementation and Management (Rob,

C2. Conversion to Second Normal

FormCoronel & Crockett 9781844807321)

• A partial dependency can only exist if the table’s key is a

composite key. So a table with a PK consisting of one attribute is
automatically in 2NF when it is in 1NF
• But it may still contain transitive dependencies

3
5
 DATABASE SYSTEMS: Design Implementation and Management (Rob,
Coronel & Crockett 9781844807321)

Normal Forms
Normal Form Characteristics Section

First normal form (1NF) Table format: no repeating groups and PK identified 7.3.1

Second normal form (2NF) 1NF and no partial dependencies 7.3.2

Third normal form (3NF) 2NF and no transitive dependencies 7.3.3

Boyce-Codd normal form (BCNF) Every determinant is a candidate key (special case of 7.6.1
3NF)

Fourth normal form (4NF) 3NF and no independent multivalued dependencies 7.6.2

3
6
 DATABASE SYSTEMS: Design Implementation and Management (Rob,

C3. Conversion to Third Normal

FormCoronel & Crockett 9781844807321)
• Data anomalies created are easily eliminated by completing
three steps
• Step 1: Identify each new determinant
• For every transitive dependency, write its determinant as the PK for a new
table
• A determinant is any attribute whose value determines other values within a tuple of the
relation

• Step 2: Identify the dependent attributes

3
7
 DATABASE SYSTEMS: Design Implementation and Management (Rob,
• Identify attributes dependent on each
determinant identified in step 1 and identify the dependency
• Name table to reflect its contents and function
C3. Conversion to Third Normal FormCoronel & Crockett
9781844807321)

• Step 3: Remove the dependent attributes from the transitive

dependencies
• Eliminate all dependent attributes in the transitive relationship(s) from each
of the tables that have such a transitive relationship eg JOB_CLASS →
CHG_HOUR
• Note the new table is included as a FK in original table eg EMP_NUM →
EMP_NAME, JOB_CLASS

3
8
DATABASE SYSTEMS: Design Implementation and Management (Rob,
• Draw new dependency diagram to show
all tables defined in steps
1–3
• Check new tables as well as tables modified in step 3 to make sure that each
table has a determinant and that no table contains inappropriate
dependencies

3
9
DATABASE SYSTEMS: Design Implementation and Management (Rob,

4
0
 DATABASE SYSTEMS: Design Implementation and Management (Rob,

C2. Conversion to Second Normal

FormCoronel & Crockett 9781844807321)

4
1
DATABASE SYSTEMS: Design Implementation and Management (Rob,

C3. Conversion to Third Normal

FK

4
2
 DATABASE SYSTEMS: Design Implementation and Management (Rob,

FormCoronel & Crockett 9781844807321)

4
3
DATABASE SYSTEMS: Design Implementation and

Management (Rob, Problem Question

1
Coronel & Crockett 9781844807321)
1. Using the INVOICE table structure shown in Table 1, write the relational schema, draw its
dependency diagram and identify all dependencies (including all partial and transitive
dependencies). You can assume that the table does not contain repeating groups and
that any invoice number may reference more than one product.
(Hint: This table uses a composite primary key.)

Table 1 Sample INVOICE Records

44
DATABASE SYSTEMS: Design Implementation and Management (Rob,
Coronel & Crockett 9781844807321)
Attribute Name Sample Value Sample Value Sample Value Sample Value Sample Value

INV_NUM 211347 211347 211347 211348 211349

PROD_NUM AA-E3422QW QD-300932X RU-995748G AA-E3422QW GH-778345P

SALE_DATE 1 Sep 2011 26 Aug 2011 30 Aug 2011 3 Sep 2011 25 Aug 2011
0.25-cm. drill
PROD_DESC Rotary sander Band saw Rotary sander Power drill
bit
VEND_CODE 211 211 309 211 157

VEND_NAME NeverFail, Inc. NeverFail, Inc. Saws, Inc. NeverFail, Inc. ToughGo, Inc.

NUM_SOLD 1 8 1 2 1

PROD_PRICE
€34.46 €2.73 €31.59 €34.46 €69.32

4
5
DATABASE SYSTEMS: Design Implementation and Management (Rob,
Coronel & Crockett 9781844807321)

Problem 1 Solution

Problem Question 2

4
6
 DATABASE SYSTEMS: Design Implementation and Management (Rob,
Coronel & Crockett 9781844807321)
2. Using the initial dependency diagram drawn in Problem 1,
remove all partial dependencies, draw the new dependency diagrams, and identify the normal

4
7
DATABASE SYSTEMS: Design Implementation and Management (Rob,
Coronel & Crockett 9781844807321)
forms for each table structure you created.

4
8
 DATABASE SYSTEMS: Design Implementation and Management (Rob,
Coronel & Crockett 9781844807321)

Problem Question 3
3. Using the table structures you created in Problem 2, remove all transitive dependencies, and
draw the new dependency diagrams. Also identify the normal forms for each table structure you
created.

4
9
DATABASE SYSTEMS: Design Implementation and Management (Rob,
Coronel & Crockett 9781844807321)

5
0
 DATABASE SYSTEMS: Design Implementation and Management (Rob,
Coronel & Crockett 9781844807321)

Problem Question 4
4. Using the results of Problem 3, draw the ERD using UML notation.

5
1
DATABASE SYSTEMS: Design Implementation and Management (Rob,
Coronel & Crockett 9781844807321)

5
2
 DATABASE SYSTEMS: Design Implementation and Management (Rob,
Coronel & Crockett 9781844807321)

D. Improving the Design

• Table structures are cleaned up to eliminate troublesome initial partial and
transitive dependencies
• Normalization cannot, by itself, be relied on to make good designs
• It is valuable because its use helps eliminate data redundancies

5
3
DATABASE SYSTEMS: Design Implementation and Management (Rob,
Coronel & Crockett 9781844807321)

D. Improving the Design

• Issues to address in order to produce a good normalized set of tables:
• Evaluate PK assignments
• eg add a JOB_CODE in the JOB table as a numeric attribute instead of a narrative and descriptive
attribute
• JOB_CODE is a surrogate key
• Evaluate naming conventions
• Eg JOB_CHG_HOUR in the JOB table
• Refine attribute atomicity
• Eg Split EMP_NAME into EMP_FNAME, EMP_LNAME, EMP_INTIALS

5
4
DATABASE SYSTEMS: Design Implementation and Management (Rob,

C3. Conversion to Third Normal

FK

5
5
 DATABASE SYSTEMS: Design Implementation and Management (Rob,

FormCoronel & Crockett 9781844807321)

5
6
 DATABASE SYSTEMS: Design Implementation and Management (Rob,

E. Surrogate Key
ConsiderationsCoronel & Crockett 9781844807321)
• When primary key is considered to be unsuitable, designers use
surrogate keys

Different
Job_Code
but same
details
5
7
DATABASE SYSTEMS: Design Implementation and Management (Rob,
• Data entries in Table 7.3 are
inappropriate because they duplicate existing records
• Yet there has been no violation of either entity integrity or referential integrity
• Enforce unique values for JOB_DESCRIPTION with a unique index

5
8
DATABASE SYSTEMS: Design Implementation and Management (Rob,

D. Improving the Design Coronel &

• Identify new attributes
• EgEMP_HIRE_DATE , EMP_SOCIAL_SECURITY_NUM
• Identify new relationships
• Egadd EMP_NUM of the project manager in the PROJECT table
• Refine primary keys as required for data granularity
• Granularity is the level of detail represented by the values stored in a table’s row. Lowest level
of granularity it atomic data
• ASSIGN_HOURS may be refined to a time egper
period,
day
• Maintain historical accuracy
• Record ASSIGN_CHG_HOUR in ASSIGMENT table as value of JOB_CHG_HOUR will change over
time
• Evaluate using derived attributes
• Store ASSIGN_CHARGE as ASSIGN_HOURS * ASSIGN_CHG_HOUR for ease of reporting and less
processing

5
9
 DATABASE SYSTEMS: Design Implementation and Management (Rob,
Crockett 9781844807321)
–2

6
0
DATABASE SYSTEMS: Design Implementation and Management (Rob,
Coronel & Crockett 9781844807321)

H. Denormalization
• Creation of normalized relations is important database design goal
• Processing requirements should also be a goal
• If tables decomposed to conform to normalization requirements:
• Number of database tables expands

6
1
DATABASE SYSTEMS: Design Implementation and Management (Rob,
Coronel & Crockett 9781844807321)

H. Denormalization
• Joining the larger number of tables takes additional input/output
(I/O) operations and processing logic, thereby reducing system speed
• Conflicts between design efficiency, information requirements, and
processing speed are often resolved through compromises that may
include denormalization

6
2
DATABASE SYSTEMS: Design Implementation and Management (Rob,
Coronel & Crockett 9781844807321)

H. Denormalization
• Unnormalized tables in production database tend to suffer from these
defects:
• Data updates are less efficient because programs that read and update tables
must deal with larger tables
• Indexing is more cumbersome
• Unnormalized tables yield no simple strategies for creating virtual tables known as
views
• Use denormalization cautiously
• Understand why—under some circumstances—unnormalized tables
are better choice

6
3
DATABASE SYSTEMS: Design Implementation and Management (Rob,
Coronel & Crockett 9781844807321)

Summary

• Normalization is a technique used to design tables in

which data redundancies are minimized
• First three normal forms (1NF, 2NF, and 3NF) are most
commonly encountered
• Table is in 1NF when:
• there are no repeating groups
• all key attributes are defined and
• when all remaining attributes are dependent on primary key
Summary
•Table is in 2NF

6
4
DATABASE SYSTEMS: Design Implementation and Management (Rob,
Coronel & Crockett 9781844807321)
• when it is in 1NF and
• contains no partial dependencies
•Table is in 3NF
• when it is in 2NF and
• contains no transitive dependencies
•Table that is not in 3NF may be split into new tables
until all of the tables meet 3NF requirements
•Normalization is important part—but only part—of
design process

6
5
DATABASE SYSTEMS: Design Implementation and Management (Rob,
Coronel & Crockett 9781844807321)

Summary

6
6
DATABASE SYSTEMS: Design Implementation and Management (Rob,
Coronel & Crockett 9781844807321)

Summary

6
7
DATABASE SYSTEMS: Design Implementation and Management (Rob,
Coronel & Crockett 9781844807321)

6
8
DATABASE SYSTEMS: Design Implementation and Management (Rob,
Coronel & Crockett 9781844807321)

Summary

6
9
DATABASE SYSTEMS: Design Implementation and Management (Rob,
Coronel & Crockett 9781844807321)

Summary

• Table in 3NF may contain multivalued dependencies that produce

either numerous null values or redundant data
• It may be necessary to convert 3NF table to fourth normal form (4NF)
by
• Splitting table to remove multivalued dependencies
• Tables are sometimes denormalized to yield less I/O which increases
processing speed

Examples of Test or Exam Questions

• Definitional questions on things like:

7
0
DATABASE SYSTEMS: Design Implementation and Management (Rob,
Coronel & Crockett 9781844807321)
• What is normalization?
• What is 1NF, 2NF, 3NF?
• What is partial dependency?
• What is transitive dependency?
• What is primary key dependency?
• Use of dependency diagrams
• Steps involved in transforming a table from 1NF to 2NF to 3NF

7
1
DATABASE SYSTEMS: Design Implementation and

Management (Rob, Problem Question

1
Coronel & Crockett 9781844807321)
1. Using the INVOICE table structure shown in Table 1, write the relational schema, draw
its dependency diagram and identify all dependencies (including all partial and transitive

Attribute Name Sample Value Sample Value Sample Value Sample Value Sample Value

INV_NUM 211347 211347 211347 211348 211349

PROD_NUM AA-E3422QW QD-300932X RU-995748G AA-E3422QW GH-778345P

SALE_DATE 1 Sep 2011 26 Aug 2011 30 Aug 2011 3 Sep 2011 25 Aug 2011
0.25-cm. drill
PROD_LABEL Rotary sander Band saw Rotary sander Power drill
bit
VEND_CODE 211 211 309 211 157

VEND_NAME NeverFail, Inc. NeverFail, Inc. Saws, Inc. NeverFail, Inc. ToughGo, Inc.

QUANT_SOLD 1 8 1 2 1

PROD_PRICE
€34.46 €2.73 €31.59 €34.46 €69.32

72
DATABASE SYSTEMS: Design Implementation and Management (Rob,
Coronel & Crockett 9781844807321)
dependencies). You can assume that the table does
not contain repeating groups and that any invoice number may reference more than one
product.
(Hint: This table uses a composite primary key.)

Table 1 Sample INVOICE Records

7
3
DATABASE SYSTEMS: Design Implementation and Management (Rob,
Coronel & Crockett 9781844807321)

Problem 1 Solution

Problem Question 2
2. Using the initial dependency diagram drawn in Problem 1, remove all partial dependencies,
draw the new dependency diagrams, and identify the normal forms for each table structure
you created.

7
4
DATABASE SYSTEMS: Design Implementation and Management (Rob,
Coronel & Crockett 9781844807321)

7
5
DATABASE SYSTEMS: Design Implementation and Management (Rob,
Coronel & Crockett 9781844807321)

Problem Question 3
3. Using the table structures you created in Problem 2, remove all transitive dependencies, and
draw the new dependency diagrams. Also identify the normal forms for each table structure
you created.

7
6
DATABASE SYSTEMS: Design Implementation and Management (Rob,
Coronel & Crockett 9781844807321)

7
7
DATABASE SYSTEMS: Design Implementation and Management (Rob,
Coronel & Crockett 9781844807321)

Problem Question 4
4. Using the results of Problem 3, draw the ERD using UML notation.

7
8
DATABASE SYSTEMS: Design Implementation and Management (Rob,
Coronel & Crockett 9781844807321)

7
9
 Data Modelling
In this chapter, you will learn:
A. Data model building blocks
B. Business rules
C. Data abstraction
Database Systems: Design,
Implementation, &
Management, International
Edition, Rob, Coronel &

Importance of Data Models in

Accounting
• A data model is a visual and logical representation of
financial data and how it's organized.
• Helps accountants and financial analysts understand
data relationships like client-invoice-payment.
• Enables consistency, reduces redundancy, and
improves compliance with financial regulations.
• Tools such as Visio or Lucidchart help design models
before implementation.
• Enables collaboration between accountants,
developers, and managers.
Data model basic building blocks:
Entities and Attributes
• Entities are real-world objects like Client,
Invoice, Payment, and Account.
• Each entity has attributes: for example, Client
(Name, ID, Email), Invoice (Amount, Due
Date).
• Used to capture all necessary data in an
organized manner.
• Helps ensure records are complete and
wellstructured.
Examples of Entities
Examples of Entities
Examples of Entities
 Data model basic building blocks:
Entities and Attributes
• Attributes are the details: Client name,
Invoice date, Payment amount.
• Example: An Invoice has an ID, a date, and a
total amount.
Data model basic building blocks:
Relationships in Data
• Defines how entities interact with each other:
– One-to-Many (1:*): One client can have many
invoices.
– Many-to-Many (*:*): Multiple clients can make
multiple payments toward shared services.
– One-to-One (1:1): One account is assigned to one
bookkeeper.
• Relationships support data integrity and
reduce duplication.
Relationship examples

Data model basic building blocks:

 Constraints in Databases
• Constraints enforce rules that ensure valid
accounting data:
– Invoice amount must be ≥ 0.
– Payment date must not be before the invoice
date.
– Each transaction must be linked to an existing
account.
– Helps prevent accounting errors and fraud.
 Why We Need Rules in Our Data
• Rules make sure the data is correct.
• Example: No payment can be more than the
invoice amount.
• Every invoice must belong to a client.
• These rules protect against accounting errors.
 Business Rules – 1
• Business rules are brief, precise, and
unambiguous descriptions of policies,
procedures, or principles within a specific
organization
• Apply to any organization that stores and uses
data to generate information
Database Systems: Design,
Implementation, &
Management, International
Edition, Rob, Coronel &
Crockett
 Business Rules – 2
• Must be rendered in writing
• Must be kept up to date
• Sometimes are external to the organization
• Must be easy to understand and widely
disseminated

Database Systems: Design,

Implementation, &
Management, International
Edition, Rob, Coronel &
Crockett
• Describe characteristics of the data as viewed
by the company
Discovering Business Rules
Sources of Business Rules:
• Company managers
• Policy makers
• Department managers
• Written documentation
– Procedures
Database Systems: Design,
Implementation, &
Management, International
Edition, Rob, Coronel &
Crockett
 – Standards
– Operations manuals
• Direct interviews with end users

Discovering Business Rules

• Generally, nouns translate into entities
– Customer, Invoice, Course, Classroom
• Verbs translate into relationships among
entities
Database Systems: Design,
Implementation, &
Management, International
Edition, Rob, Coronel &
Crockett
 – Purchase, pay, generate invoice, attend course,
Discovering Business Rules
• Relationships are bi-directional
– A customer may generate many invoices
– An invoice is generated by only one customer
• A relationship is an association among entities
– One to many (1:*)

Database Systems: Design,

Implementation, &
Management, International
Edition, Rob, Coronel &
Crockett
 – Many to many (*: *)
– One to One (1:1)

In-class exercise
Consider a university course offered to students and involving a lecturer. Give
an example of a business rule showing both directions of the relationship
(remember the 3 types of relationships).
• Fort Hare has many departments. (Hint: University + department)
• Fort Hare has different departments that teaches a variety of specific
courses. (Hint: Department + course)
• The lectures are taught in a classroom allocated specifically to that course
(Hint: Classroom + lecture)
Database Systems: Design,
Implementation, &
Management, International
Edition, Rob, Coronel &
Crockett
• The students attend classes taught by a lecturer. (Hint: class+ student)

In-class exercise
• Consider a university course offered to students
and involving a lecturer. Give an example of a
business rule showing both directions of the
relationship.
– Example 1: One lecturer teaches many students.
Students are taught by one lecturer.
– Example 2: One department offers many courses. A
course is offered by one department.
Database Systems: Design,
Implementation, &
Management, International
Edition, Rob, Coronel &
Crockett
 – Example 3: A classroom is used for many lectures.
A lecture takes place in one classroom.

Database Systems: Design,

Implementation, &
Management, International
Edition, Rob, Coronel &
Crockett
 Turning Rules Into a Database
• Words like Client, Invoice become 'entities'.
• Actions like 'pays' or 'issues' become
'relationships'.
• Helps build database diagrams different users
can understand.
D3. The Relational Model
• Table (relations)
 – Matrix consisting of a series of row/column
intersections
– Related to each other through sharing a common
entity characteristic

Database Systems: Design,

Implementation, &
Management, International
Edition, Rob, Coronel &
D3. The Relational Model
Database Systems: Design, Implementation, & Management, International Edition, Rob, Coronel & Crockett
 D3. The Relational Model
• A table is purely a logical structure
– How data are physically stored in the database is of
no concern to the user or the designer
– This property became the source of a real database
revolution
– If you're familiar with spreadsheets you're familiar
with tables of rows and columns.

D3. The Relational Model

• Relational diagram:
– Is a representation of the relational database’s entities,
– attributes within those entities, and
– relationships between those entities

Database Systems: Design,

Implementation, &
Management, International
Edition, Rob, Coronel &
 Relational Model in Accounting
• Uses tables to store data such as Clients,
Invoices, Payments.
• Each table has rows (records) and columns
(fields).
• Tables are connected through foreign keys.
• Ideal for structured financial data – reliable,
scalable, and SQL-compatible.
 D3. The Relational Model
• Rise to dominance of the relational model was due in part to its
powerful and flexible query language
• Structured Query Language (SQL) allows the user to specify
what must be done without specifying how it must be done
• SQL-based relational database application involves:
– User interface
– A set of tables stored in the database
– SQL engine
• Information in the model is represented using
entityrelationship models
Database Systems: Design,
Implementation, &
Management, International
Edition, Rob, Coronel &

E. Degrees of Data Abstraction – 1

• Data abstraction is the reduction of a certain portion of data for a
simple presentation of content. Abstraction, in general, the
process of removing features from something to reduce the set
of necessary features
• Way of classifying data models
• Many processes begin at high level of abstraction and proceed to
an ever-increasing level of detail
• Designing a usable database follows the same basic process
Database Systems: Design,
Implementation, &
Management, International
Edition, Rob, Coronel &
E. Degrees of Data Abstraction – 3
E1

E2

E3

E4
 Database Systems: Design, Implementation, & Management, International Edition, Rob, Coronel & Crockett

Understanding Data Abstraction

• External Level: User-focused views (reports,
dashboards).
• Conceptual Level: Logical structure of entire
database (ER diagrams).
• Internal Level: How DBMS interprets the data
structure.
• Physical Level: How data is stored on disks –
hardware specific.
• Helps design and manage systems effectively at
each level.
 E1. The External Model – 1
• End users’ view of the data environment
• Use business rules
• Highest level of abstraction

Database Systems: Design,

Implementation, &
Management, International
Edition, Rob, Coronel &
Crockett
End user

Database Systems: Design,

Implementation, &
Management, International
Edition, Rob, Coronel &
Crockett
 E1. The External Model – 2
• Advantages:
– Easy to identify specific data required to support
each business unit’s operations
– Facilitates designer’s job by providing feedback
about the model’s adequacy
– Creation of external models helps to ensure
security constraints in the database design

Database Systems: Design,

Implementation, &
Management, International
Edition, Rob, Coronel &
Crockett
 – Simplifies application program development

E2. The Conceptual Model – 1

• Represents global view of the entire
database
• Representation of data as viewed by the
entire organization

Database Systems: Design,

Implementation, &
Management, International
Edition, Rob, Coronel &
Crockett
• Basis for identification and high-level
description of main data objects, avoiding
details
• Most widely used conceptual model is the
entity relationship (ER) model
2. The Conceptual Model – 2
• Provides a relatively easily understood macro level view of
data environment
Database Systems: Design,
Implementation, &
Management, International
Edition, Rob, Coronel &
Crockett
• Independent of both software and hardware
– Does not depend on the DBMS software used to implement the
model
– Does not depend on the hardware used in the implementation of
the model
– Changes in either hardware or DBMS software have no effect on the
database design at the conceptual level

Database Systems: Design,

Implementation, &
Management, International
Edition, Rob, Coronel &
Crockett
Designer

Database Systems: Design,

Implementation, &
Management, International
Edition, Rob, Coronel &
Crockett
Database Systems: Design,
Implementation, &
Management, International
Edition, Rob, Coronel &
Crockett
 E3. The Internal Model
• Representation of the database as “seen” by
the DBMS
• Maps the conceptual model to the DBMS
• Internal schema depicts a specific
representation of an internal model

Database Systems: Design,

Implementation, &
Management, International
Edition, Rob, Coronel &
Crockett
 E4. The Physical Model – 1
• Operates at lowest level of abstraction,
describing the way data are saved on storage
media such as disks or tapes
• Software and hardware dependent
• Requires that database designers have a
detailed knowledge of the hardware and
software used to implement database design
Database Systems: Design,
Implementation, &
Management, International
Edition, Rob, Coronel &
Crockett
Factory floor

Database Systems: Design,

Implementation, &
Management, International
Edition, Rob, Coronel &
Crockett
Database Systems: Design,
Implementation, &
Management, International
Edition, Rob, Coronel &
Crockett
 Quick Review Quiz
• 1. What’s an entity? Give an example.
• 2. Explain a one-to-many relationship in
accounting.
• 3. Why are data rules important?
• 4. What does SQL help us do?
• 5. How do tables work together in financial
systems?