COLLEGE OF SCIENCE AND TECHNOLOGY
SCHOOL OF ICT
DEPARTMENT OF INFORMATION SYSTEMS
YEAR II
DATABASE DESIGN AND MANAGEMENT (ISY2161)
Prepared by: Mr. Frodouard MINANI
Telephone: 0782149182/0739505574 Email: frodominani@gmail.com
1
Course Information
• Prerequisites : None
• Credits: 10
• Assessments: 100%
– Quizzes: 10%
– Assignments : 10%
– Continuous Assessment Tests(2): 30%
– Final Exam: 50%
• Office Hours: Working days (8:00am-5:00pm)
2
Indicative Contents
Unit One: Database Management System and Design Concepts
Chapter 1: Database overview
• Information
• Database
• Why Databases?
Chapter 2:File-Based systems
• Features
• File-based processing
• Limitations of File based systems approach
Chapter 3:Database Management System (DBMS)
• Components of DBMS
• Functions, Advantages and disadvantages of a DBMS
• Database approach
• Database views
• Roles in Database environment
Chapter 4: Database Architecture
• Why three-Level architecture (Objectives)
• Three levels of ANSI-SPARC architecture.
• Data dependency
• Database languages
3
Chapter 5: Data Modeling Concepts
• Categories of data modeling
• Conceptual modeling
• Relational model
• Data modeling concepts
• Relational keys
• Relational Integrity
• Entity relationship symbols, types and relational cardinality
Chapter 6: Normalization
• First Normal Form (1st NF)
• Second Normal Form (2nd NF)
• Third Normal Form (3rd NF)
• Boyce-Codd Normal Form (BCNF)
Chapter 7: Data and Database Security
• Computer and Non-Computer-based measures and controls
• Security in various DBMSs
• DBMS and web Security
4
Unit Two: Implementation and Administration of Databases
Chapter 8: Database Modeling
• Design of Data Flows Diagram (DFD)
• Design of Entity Relationship Diagram(ERD)
• Design of Logical Data Model (LDM)
Chapter 9:Database Implementation
• Implement the LDM in the database to have the Physical Data Model
• Creation of tables and relationships following referential constraints
• Creation of views, cursors, sequences, triggers and stored procedures
Chapter 10: Administering the Database
• Users creation and management
• Granting and revoking privileges
• Database security
• Recovery management
5
Unit One:
Database Management System
and Design Concepts
7
Chapter 1:
Overview of Database
8
A Database is a collection of related data organized
in a way that data can be easily accessed, managed
and updated.
Any piece of information can be a data, for example
name of your District.
Database is actually a place where related piece of
information is stored and various operations can be
performed on it.
9
Properties of Database (DB)
It is a representation of some aspect of the real world or a
collection of data elements (facts) representing real world
information.
A database is logical, coherent and internally consistent.
A database is designed, built and populated with data for a
specific purpose.
Each data item is stored in a field.
A combination of fields makes up a table.
10
Example of a Database
11
Database System Environment
Oversee the
DA: Ensure database
that the system’s general
database is operations
functioning
properly.
D.D: Design the
database
structure. They
are, in effect , Hardware
the database identifies all
architects. the system’s
SA & physical
Programmers: devises.
Design and
implement the Software refers
application to the collection
programs. of programs used
End-users: Are the
by the computers
people who use the within the
application programs database system.
to run the OS
organization’s daily DBMS Software
operations. App.programs &
Utilities
Procedures: Are the
instructions and rules
that govern the design
and use of the database Figure 1:Database System Environment
system. 12
Why Database?
Redundancy can be reduced
Inconsistency(instability) can be avoided
The data can be shared
Standards can be enforced
Security restrictions can be applied
Integrity can be maintained
Provision of data independence
– Integrity: Is the assurance that the information is trustworthy and
accurate (protecting information from being modified by unauthorized
parties)
13
The Essence (Quality of) of a Database
Organization of data
Efficient retrieval of data
Reliable storage of data
Maintaining the consistency of the data
Sharing data
Structuring the data
14
Data vs. Information
15
Figure 2: Data vs. Information
Figure 3: Example 2 of Data & Information
16
Chapter 2:
File-Based Systems
17
Outline
Introduction about File based system
• What is file based system?
• How it works?
Advantages & disadvantages in file based
system
Solution for those problems
What is file based system?
• A file based system is a collection of
application programs that perform services
for the user.
• Each program within a file based system
defines and manages its own data.
How it works?
Accounts HRM Production
Department Department Department
Accounts HR Production
Data Data Data
Each department maintains their own set of data.
There is no link between those data pools.
Advantages of file based system
• No need of external storage
• No need of highly technical person to handle
the database.
• Processing speed is high as compare to DBMS
• Low cost (ex: Equipments)
Disadvantages of File based system
• Provide less security.
• High complexity in updating of database
• Data separation and isolation
• Data dependence
• Data duplication
• Incompatible data (different file formats)
• Lack of flexibility in organizing and querying the data
• Increased number of different application programs
23
Data Redundancy
Redundancy means having multiple copies of the same data.
In computer file-based processing system, each application
program has its own data files.
The same data may be duplicated in more than one file. The
duplication of data may create many problems such as:
• To update a specific data/record, the same data must be
updated in all files, otherwise different file may have different
information about a specific item.
• A valuable storage space is wasted.
24
Data Inconsistency
Data inconsistency means that different files
may contain different information of a
particular object or person.
Actually redundancy leads to inconsistency.
When the same data is stored in multiple
locations, the inconsistency may occur.
25
Data Isolation and Separation
In computer file-based system, data is isolated
in separate files.
It is difficult to update and to access particular
information from data files.
26
Data Dependence
In computer file-based processing systems,
the data stored in file depends upon the
application program through which the file
was created.
It means that the structure of data files is
coupled with application program.
28
Data Dependence Cont’
The physical structure of data files and records
are defined in the application program code.
It is difficult to change the structure of data files
or records.
If you want to change the structure of data file
(or format of file), then you have to modify the
application program.
29
Program Maintenance
In computer file-based processing system, the
structure of data file is coupled with the individual
application programs.
Therefore, any modification to a data file such as size of
a data field, its type etc. requires the modification of
the application program also.
This process of modifying the program is referred to as
program maintenance.
30
Data Sharing
In computer file-based processing systems, each
application program uses its own private data
files.
The computer file-based processing systems do
not provide the facility to share data of a data file
among multiple users on the network.
31
Data Security
The computer file-based processing system do
not provide the proper security system against
illegal access of data.
Anyone can easily change or delete valuable data
stored in the data file.
It is the most complicated problem of file-
processing system.
32
Incompatible File Format
In computer file-based processing systems, the
structure of data file is coupled with the
application program and the structure of data file
is dependent on the programming languages in
which the application program was developed.
33
Solutions
• Using Database Management System (DBMS)
• Because
- Sharing of data
- Consistency of data
- Integrity of data
- Security of data
- Potentially increased productivity
- Improved data backups and recovery Data
independence
How DBMS works
Accounts HRM Production
Department Department Department
Data
Database vs. File Systems
36
Figure 6: Database vs. File System
