Database Systems

Dr. Salah Amin Elewa

Code: CC414

Credit_hours: 3
Title: Database Systems

Prequisites: CC319 Advanced Programming

Credit hours: 3
Description/Outcomes: The course covers
topics related to relational Database analysis,
design, normalization, and implementation.

Objectives: To develop analysis skills in the design

and analysis of db systems with applications to the
market and some real S511
life problems
Session 2, IU-SLIS 2
 Marks Distribution
⚫ 7th week
⚫ Lecture: 20 points
⚫ 10 points (2 Quizes 1,2 )
⚫ 12th week
⚫ Lecture: 15 points
⚫ 5 points (2 Quizes 3,4 )

⚫ Term evaluation
⚫ project: 10 points
⚫ Final
⚫ 40 points
Database Overview

4
 Database: What
▪ Database
– is collection of related data and its metadata organized in a structured format
– for optimized information management

▪ Database System
– is an integrated system of hardware, software, Networks, people, procedures,
and data
that define and regulate the collection, storage, management, and use of
data within a database environment

▪ Database Management System (DBMS)

– is a software that enables easy creation, access, and modification of databases
– for efficient effective database management

5
 Database Management System
Manages interaction between end users and database

6
Database System Environment

▪ Hardware
▪ Software
- OS
- DBMS
- Applications
▪ People
▪ Procedures
▪ Data

Database Systems: Design, Implementation, & Management: Rob & Coronel

7
Job responsibilities for database administrators

➢ Conducting data backups

➢ Assessing database performance
➢ Modifying database structure if needed
➢ Restoring lost data
➢ Debugging programs or installing patches

Job responsibilities for system administrators

➢ Adding users to a network
➢ Implementing network security measures
➢ Training users in network and application usage
➢ Solving problems in an efficient and effective manner
➢ Conducting needs assessments
8
 Database: Why
Purpose of Database
– Optimizes data management
– Transforms data into information

Importance of Database Design

– Defines the database’s expected use
• different approach needed for different types of databases
– Avoid data redundancy & ensure data integrity
• data is accurate and verifiable
– Poorly designed database generates errors
• leads to bad decisions
• can lead to failure of organization

9
 Database: Why
Functions of DBMS/Database System
▪ Stores data and related data entry forms, report
definitions, etc.
▪ Hides the complexities of relational database
model from the user
• facilitates the construction/definition of data elements and their
relationships
• enables data transformation and presentation
▪ Enforces data integrity ( Overall accuracy, completeness, and consistency
of data.)

▪ Implements data security management

• access, privacy, backup & restoration
Database: How

11
 Database: How
➢Planning & Analysis
– Study
• Goal of the organization
• Database environment
– existing hardware, software, raw data, data processing procedures
– Identify
• Database needs
– what database can do to further the goal of the organization
• User needs and characteristics
– who the users are, what they want to do, how they envision doing it
• Database system requirements
– what the database system should do to satisfy the database and user
needs

12
 Database: How
➢Design
– From conceptual design to a detailed system
specification

➢Implementation
– Create the database

➢Maintenance
– Troubleshoot, update, modify the database

13
 Business Rules
What
– Brief, precise, and unambiguous
descriptions of operations in an organization
• based on policies, procedures, or principles within a
specific organization
• help to create and enforce actions within that
organization’s environment
• apply to any organization that stores and uses data
to generate information

14
 Business Rules
Why
– Enhance understanding & facilitate communication

• Standardize company’s view of data

• Constitute a communications tool between users and

designers

• Allow designer to understand business process as well as the

nature, role, and scope of data

– Promote creation of an accurate data model

15
 Business Rules
How (sources)
– Interviews
• Company managers
• Policy makers
• Department managers
• End users
– Written documentation
• Procedures, Standards, Operations
manuals
– Observation
• Business operations

16
 Database: Data Models
• Importance
– Abstraction of complex real-world data structures in relatively simple
(graphical) representations
– Facilitate interaction among the designer, programmer, and the end
user

• Basic Building Blocks

– Entity
• thing about which data are to be collected and stored
– Attribute
• a characteristic of an entity
– Relationship
• describes an association among entities
– Constraint
• restrictions placed on the data

17
Database: Historical Roots

18
 Manual File System
– to keep track of data
– used tagged file folders in a filing cabinet
– organized according to expected use
• e.g. file per customer
– easy to create, but hard to
• locate data
• aggregate/summarize data

19
 Database: Historical Roots
Computerized File System
To accommodate the data growth and information need
manual file system structures were duplicated in the computer
Data Processing (DP) specialists wrote customized programs to
write, delete, update data (i.e. management)
extract and present data in various formats (i.e., reports)

20
Disadvantages of File Processing
➢ Program-Data Dependence
All programs maintain metadata for each file they use
➢ Duplication of Data
Different systems/programs have separate copies of the same data
➢ Limited Data Sharing
No centralized control of data
➢ Lengthy Development Times
Programmers must design their own file formats
➢ Excessive Program Maintenance
80% of information systems budget
 Disadvantages of File Processing

Examples of
heterogeneous
data

22 22
Database System vs. File System

23
 Database Models
• A Database model defines the logical design
and structure of a database and defines how
data will be stored, accessed and updated in
a database management system.
• While the Relational Model is the most
widely used database model, there are other
models too:
– Hierarchical Model
– Network Model

24
 Hierarchical Model
▪ This database model organizes data into a tree-
like-structure, with a single root, to which all the
other data is linked. The hierarchy starts from the
Root data, and expands like a tree, adding child
nodes to the parent nodes.
▪ In this model, a child node will only have a single
parent node.
▪ In hierarchical model, data is organized into tree-
like structure with one one-to-many relationship
between two different types of data, for example,
one department can have many courses, many
professors and of-course many students.

25
Hierarchical Model

26
Network Model
▪ This is an extension of the Hierarchical model. In
this model data is organised more like a graph, and
are allowed to have more than one parent node.
▪ In this database model data is more related as
more relationships are established in this database
model. Also, as the data is more related, hence
accessing the data is also easier and fast. This
database model was used to map many-to-many
data relationships.
▪ This was the most widely used database model,
before Relational Model was introduced.
27
28
 Relational Model
• Problems with legacy database systems
– Required excessive effort to maintain
• Data manipulation (programs) too dependent on physical file
structure
– Hard to manipulate by end-users
• No capacity for ad-hoc query (must rely on DB
programmers).

• Evolution in Data Organization

– E. F. Codd’s Relational Model proposal
• Separated the notion of physical representation (machine-view)
from logical representation (human-view)
– Relational Database Model
• Dominant database model of today
• Tables
– flexible logical structure for data representation
– a series of row/column intersections
– related by sharing common entity characteristic(s)

29
 Relational Database
◼ Provides a logical “human-level” view of the data and
associations among groups of data (i.e., tables)

30
 Entity Relationship Model

• Entity Relationship (ER) Model

– Based on Entity, Attributes & Relationships

• Entity is a thing about which data are to be collected and
stored
– e.g. EMPLOYEE

• Attributes are characteristics of the entity

– e.g. SSN, last name, first name

• Relationships describe an associations between entities

– i.e. 1:M, M:N, 1:1

31
E-R Diagram: Chen Model
• Entity
– represented by a rectangle with
its name in capital letters.

• Relationships
– represented by an active or
passive verb inside the diamond
that connects the related
entities.

• Connectivities
– i.e., types of relationship
– written next to each entity box.

32
E-R Diagram: Crow’s Foot Model
• Entity
– represented by a rectangle with its
name in capital letters.

• Relationships
– represented by an active or passive
verb that connects the related
entities.

• Connectivities
– indicated by symbols next to
entities.
• 2 vertical lines for 1
• “crow’s foot” for M

33
Network Model vs Relational Model

Conclusion - Network Model for Speed, Relational for Usability

▪ While the relational data model is very popular

because of its ease of use, it requires key and index
tables which drastically slows down an application.
▪ The network database model provides faster
access to the data and is the optimal method for a
fast application.
34
Hirachical Network and Relational Software

• Hirachical
&XML and XAML use this model.

• Network
VAX-DBMS, DMS-1100 of UNIVAC and SUPRADBMS’s use this
model.

• Relational
It is mostly used in real world applications. Oracle, SQL

35
 Object-oriented Databases (OODB)
▪ Object-Oriented Databases (OODB) are databases that
represent data in the form of objects and classes.
▪ In object-oriented terminology, an object is a real-world
entity, and a class is a collection of objects.
▪ Object-oriented databases follow the fundamental
principles of object-oriented programming (OOP).
▪ The combination of relational model features
(concurrency, transaction, and recovery) with object-
oriented principles results in an object-oriented database
model.
▪ Popular NoSQL databases like MongoDB and AWS
DynamoDB are document-oriented databases

36
relational database and object-oriented database

37
 Web Database
A web database is essentially a database
that can be accessed from a local network or
the internet instead of one that has its data
stored on a desktop or its attached storage.
... Web database applications can be free or
require payment, usually through monthly
subscriptions.
38
 Amazon’s Database

Of Amazon's 212 cloud computing

services, fifteen are purpose-built
database engines. Amazon RDS, Amazon
Aurora, Amazon DynamoDB, Amazon
DocumentDB, Amazon ElastiCache,
Amazon Neptune, Amazon Timestream,
and Amazon Quantum Ledger Database
(QLDB).

S511 Session 2, IU-SLIS 39

40