Database System

Introduction to Database
System

Aamir Malik

1
 Course Learning Outcomes (CLO)

2
 Course Contents

3
 Reference Books

4
 Reference Books

5
 Reference Books

6
 Assessment Methods

▪ 4 Assignments - 10% Lab

▪ 4 Quizzes - 15% ▪ 12+ Lab Tasks - 60%

▪ Mid Semester Exam – 25% ▪ Lab Mid – 10%

▪ End Semester Exam – 50% ▪ Final Project- 30%

7
 Data Storage System

▪ Manual File Storage Approach

▪ Classical or Traditional File Processing System

or File-based Approach

▪ Database Approach

8
 Manual File Storage

9
 Disadvantages of Manual Storage System

▪ Involve storing documents by hand in a filing cabinet.

▪ Manual systems need more equipment than electronic systems.

▪ Files are kept in drawers and are stored in filing pockets one behind the other

▪ Easily destroyed e.g., burned

10
 Traditional File Processing System

▪ File processing system is an old method

▪ Why traditional file processing systems (TFPS) were developed?

▪ But it is still widely used today, especially for database backup

11
 File Processing System - Example

12
 Traditional File Processing System

13
 File vs Database
Files:
A collection of records or documents dealing with one organization, person,
area, or subject. It could either be:
◦ Manual files (Paper)
◦ Computer Files
Database:
A shared collection of similar records with relationships between records.
◦ Statistical, Business Data

14
 File vs Database
File Processing System:
Collection of application programs that performs services for the end-users
(e.g. Reports)
Each program defines and maintains its own data
Database:
A shared collection of similar records with relationships between records.
◦ Statistical, Business Data

15
 Database vs File System

16
 Limitations of FPS
Separation and isolation of data
◦ Each program maintains its own set of data. Users of one program may be unaware of
potentially useful data held by other programs.

Duplication of data
◦ Same data is held by different programs/department. Wasted space and potentially different
values and/or different formats for the same item.

Data dependence
◦ File structure is defined in the program code with path.

17
 Limitations of FPS
Incompatible file formats
◦ Programs are written in different languages, and so cannot easily access each
others' files.

Integrity Problems
◦ Integrity constraints (e.g. account_balance > 0) became “buried” in program code
rather than being stated explicitly
◦ Hard to add new constraint or update existing one
◦ File in the folder cannot keep such conditions

18
 Limitations of FPS

Fixed Queries/Proliferation of application programs

◦ Programs are written to satisfy particular functions. Any new requirement
needs a new program.

Data Redundancy and Inconsistency

◦ Multiple file formats, and duplication of information in different files
◦ Same data in different forms
◦ If data is different but meaning is same, is called redundant data

Difficulty in Accessing Data

◦ Need to write a new program for accessing new data

19
 Database Approach

Overcomes problems associated with file-system based approach

Central repository of shared data
The database holds not only the data but also a description of the data.
◦ Data dictionary, or metadata
◦ A central location where data descriptions are stored.
◦ Data about data
◦ Program-data independence

20
 Advantages of Database Approach
Program-data independence
◦ The separation of data descriptions from the application programs that use
the data.
◦ Allows the data to change without changing the application programs.

No or Planned data redundancy

Improved data consistency
Improved data sharing
Enforcement of standards

Database is a Package

21
 Database Management System
DBMS is a software package that enables user to create and
maintain databases. It facilitates:
◦ Defining a database that involves specifying the data types,
structures, and constraints for the data to be stored in the
database.
◦ Constructing a database which is the process of storing the
data.
◦ Manipulating a database such as functions of querying and
sharing the database objects, updating the database, and
generating reports from the data.
Database and DBMS software together constitute a database
system.

22
 Examples of DBMS

◦ Oracle
◦ IBM DB2
◦ Ingress
◦ Teradata
◦ MS SQL Server
◦ MS Access
◦ MySQL etc.

23
 Data vs. Information vs. Knowledge
▪ Data: raw and unorganized facts that required to be processed to
make it meaningful

▪ Information: a set of data processed in a meaningful way

according to the requirements

▪ Knowledge: information used for making plans and decisions

24
 Database
▪ Data: known facts that can be recorded and that have some meaning

▪ Types of data - text, graphics, images, sound, videos

▪ Database: A shared collection of logical related data

- Represents some aspect of the real world.
- Organized collection of logically related data.
- It is modified for a specific purpose.

▪ Database Management system (DBMS)

- Software package for defining and managing a database
- It provides an efficient, reliable, convenient, and safe multi-user storage.

25
 Types of Data

▪ Types of data:
- Structured data is highly-organized and formatted in a way so it's easily
searchable.

- Unstructured data has no pre-defined format or organization, making it much

more difficult to collect, process, and analyze.

- Semi-structured data is partially organized; hence the level of organizing is

lesser than structured data but higher than that of unstructured data.

26
 Types of Data

27
 Types of Data

28
 Meta Data

- Database definition
- Data about data is called metadata (information about data).
- Data that describe the properties of end user data and the context of the data.
- Summarizes basic information about data
- Make it easier to find, use and reuse particular instances of data.

29
 Meta-Data vs Actual Data

30
 Typical DBMS Functionalities

▪ Define: specifying the type of database

▪ Construct: process of storing the database on some storage

▪ Manipulate: querying the database to

- Retrieve
- Update
- And generate reports

▪ Share: allow users to access the database

31
 Database Manipulation

▪ Database manipulation involves:

▪ Querying
▪ updating

▪ Examples of querying in the university DB are:

- Retrieve a transcript
- List the prerequisites of “COMP-234”

▪ Examples of updating are:

- Enter a grade of “A” for “Aslam” in the corresponding section of “COMP-234”

32
 DBMS Example

33
 DBMS Example

34
 Example, university database

▪ A university database that stores students and course information

- STUDENT file stores data on each student

- COURSE file stores data on each course
- SECTION file stores data on each section of each course
- GRADE_REPORT file stores the grades that students receive
- PREREQUISITE file stores the prerequisites

35
 Example, university database

36
 Use a DB when!

▪ centralized control of data

▪ control of redundancy
▪ multiple user support

▪ sharing of data

▪ control of access and security

▪ backup and recovery

37
 When not use DBMS!

▪ No multiple users

▪ Real-time requirements may not be met (use Main Memory DB)

▪ High investment in hardware, software, and training

▪ High overhead for security, concurrency control, recovery

38
 Key people in DBMS

▪ Database Designer: design and implement DBMS

▪ Database Application Developer: develops the tools that operate on DB

▪ Database Administrator (DBA):

- loads the data
- keeps DB system running smoothly and securely

▪ End users

39
 Database Design is important?
▪ Database design focuses on design of a database structure for end-user data

▪ Designer must identify database’s expected use

▪ Well-designed database:
- Facilitates data management
- Generates valuable information
- Results in realizing most of the DB advantages

▪ Poorly-designed database:
- Causes many problems!!
- Causes difficult-to-trace errors

40
 Key advantages of DBMS
▪ Improved data sharing

▪ Improved data security

▪ Minimized data inconsistency

▪ Improved data access

▪ Improved decision making

▪ Increased end-user productivity

▪ There could be many users, each may require a different view of the data.

41
 Advantages of DB

Program Data-Independence
▪ The separation of data description from the application program is called data independence.
▪ Data descriptions are store in a central location called repository.

Planned Data Redundancy

▪ By eliminating or controlling data redundancy, we greatly reduce the opportunities for
inconsistency.
▪ For example, if a customer’s address is stored only once, we cannot disagree about the
customer’s address.
▪ When the customer’s address changes, recording the new address is greatly simplified because
the address is stored in a single place. Finally, we avoid the wasted storage space that results
from redundant data storage.

42
 Advantages of DB

Improved Data Sharing

▪ Data is stored in central location, so it is accessible to all users of the organization.
▪ Authorized users are granted permission to use the database.
▪ User view is provided to one or more user to use data.
▪ Eliminating or controlling data redundancy improve data consistency.

Increased Productivity of Application Development

▪ Major advantage of the database approach is that it greatly reduces the cost and time for
developing new business applications.

43
 Advantages of DB

Improved Data Quality

▪ Database designer specify the constraints to improve the quality of data.
▪ Database approach provide number of tools and process to improve the quality of data.

Improved Data Accessibility and Responsiveness

▪ Accessing and retrieving of data is very easy because DBMS uses SQL through which data can be
accessed fastly.

44
 Advantages of DB

Reduced Program Maintenance

▪ Program maintenance includes: add new data item and types, change the formats of data etc.
▪ In database approach changes either in application program or data done without affecting
other factors.

45
 Cost and Risk of the Database Approach

▪ New, specialized personnel

▪ Installation and management cost and complexity
▪ Conversion costs
▪ Need for explicit backup and recovery
▪ Organizational conflict

46
 Advantages of DB

Improved Decision Support

▪ Data warehouse is used for decision making process.

Improved Data Consistency

▪ The data in database is stored only once and not duplicated.
▪ Single centralized database is used for all departments of the organization.

47