Database Management Systems

By: Assistant Professor Malik M Ali

 Course Assessment Policy
 Theory Part
 Assignments (3) ……………………………. 20
 Quiz (4) ………………………………………….. 10
For assignments:
Timely submission is absolutely necessary
In case of late submission submit your
assignment at kcllad08@gmail.com
 Midterm and Final Exam ….…………….20, 50
 Course Assessment Policy
 Lab Part
 Lab Performance ……………………..52
 Mid Exam ……………………………….…12
 Final Exam ..……………………………...06
 Lab Project.………………………………..30
 Course Website Information
1. LMS
2. YouTube Channel Name: Malik Ali Academy
3. Channel Link
https://www.youtube.com/channel/UC-
Rh64DwoESImOXBEUKQcrg/playlists
 Plagiarism
• Submitting copied assignment / project is a
serious crime and will be dealt seriously. You
will be awarded negative marks for such an
attempt
• You are supposed to provide acceptable
references for the ideas discussed in
assignments
Importance of Time
• You have to come in the class with in the first
10 minutes in order to get marked as present
• 75% presence is mandatory
• All assignments, quizzes, presentations and
project submission should be on time.
• Counseling hours: to be discussed
Books
1. Fundamentals of Database Systems 7th Ed by
Ramez Elmasri
2. Database Systems – A practical approach to
Design, Implementation and Management By
Thomas M Connolly
3. Beginning SQL Queries From Novice to
Professional by APress Publishers
Course Outline
• Basic Terms and Concepts • Views and Authorizations
• File system versus DBMS • OLAP
• Architecture of a DBMS • Relational Algebra
• Data Models • SQL using SQL Server 19
– Data query
• ER Models
– Data manipulation
• Types of Keys – Data definition
• Functional dependencies – Data access control
• Multivalued Dependencies – Transactions
• Normalization – T-SQL
• Types of Joins
• Indexing and Constraints
• Transactions (Theory)

Malik M Ali
 Data versus Information
Data Information
• Raw facts • Produced by processing raw data to
– Have not yet been processed to reveal reveal its meaning
their meaning to the end user • Requires context
– e.g. numbers, characters, graphics, – e.g. Total sale for month June, Total
images, audio, video students at Bahria Karachi campus
• Building blocks of information • Bedrock of knowledge
• Data management • Should be accurate, relevant, and timely
– Generation, storage, and retrieval of to enable good decision making
data • Information is what you retrieve from a
• Data is what you store in database. database.
Data Information – Pie Chart, Bar Graph Table

ID Name Address City State

th
1 Andrew SW 16 Ave Orlando FL
rd
2 Robert 23 Terrace Boston MA

3 Bob 52nd Avenue Bronx NY

4 Lee Hung 52nd Avenue Albany NY

5 Richard 999 Palm Bay Detroit MI

6 Tony 15th Avenue Austin TX 11

Types of Data
• Unstructured data: Data having no structure. For
Example Websites, Videos
• Semi-Structured data: Data having some structure.
For Example emails
• Structured data: Data having a structure. It can be
shown in tabular format.
• For Example: Tables in a relational database

12
Definitions
 Database:
 Database is a shared collection of logically

related data that is stored to meet the

requirements of different users of an organization.
Stored data is of two types.
 End user data: Raw facts
 Meta data: Data about data
 Database management system:
 is a software designed to manage database

structure and controls access to the data stored

in the database. For example: Oracle, SQL
Server, DB2, MySQL, MS Access etc.
 The Database System Environment
 The database system composed of 5 major
parts.
 Hardware: includes all of the system’s physical
devices, including computers, storage, printers,
network devices and other devices like automated
teller machines, ID readers and so on.
 Software: three types of software are required
 Operating system (Windows, Linux, Mac etc)
 DBMS (Oracle, SQL Server, MySQL, DB2 etc)
 Application programs: programs written by application
developers to access and manipulate data. These
programs can generate reports for decision making.
The Database System Environment
 People: it includes all users of the database system.
 System Administrator: oversee the database
system’s general operations
 Database Administrator: Manage the DBMS and
ensure that the database is functioning properly. The
DBA is responsible for
 authorizing access to the database

 Coordinating and monitoring its use

 Acquiring software and hardware resources as

needed.
 Breach of security or poor system response time.
The Database System Environment
 Database Designer: Design the database
structure. Designers’ responsibilities include
 Identification of the data to be stored in the
database
 Choosing appropriate structures to represent
and store this data.
 Analyst Programmers: Design and implement
application programs to provide different functions
of the database to the End-Users
 End Users: People who use the application
programs to run the organization’s daily
operations.
The Database System Environment
 Procedures: Rules that govern the design and
use of the database system.
 Data: collection of Facts stored in the database.
Determining what data to enter into the database
and how to organize those data is a vital part of
the database designer’s job.
Interaction of Database System Components
 Different typical components of a database environment
are shown in the figures below; they describe graphically
the role of different types of users
 Database is used to store data and DBMS uses
mechanisms to get data from the database
Interaction of Database System Components
 Application programs talk to the DBMS and ask for
the data required
Interaction of Database System Components

 Database designers design and implement the

database on the selected DBMS
Interaction of Database System Components
 Once Database has been implemented and is
functioning properly in a production environment of
an organization the Database Administrator takes
over the charge and performs specific DBA related
activities including:
 Database maintenance
 Database Backup
 Grant and Revoke rights to database users
 Monitoring of Running Jobs
 Managing Print jobs
 Ensuring quality of Service to all users
Interaction of Database System Components
Interaction of Database System Components
 DBA’s interaction with other users
Interaction of Database System Components
 Database administrator can interact with
the database designer during database design
phase so that he has a clear idea of the database
structure for easy reference in future.
 This helps DBA perform different tasks related to
the database structure.
 DBA also interacts with the application
programmers during the application development
process and provides his services for
better design of applications.
 End users also interact with the system
using application programs and other tools as
specified in the description above
 Responsibilities of a DBMS
The DBMS provides all the basic services required
to organize and maintain the database in correct
format. It includes management of
 Data storage: DBMS creates and manages the
complex structures required for data storage. It
is a job of DBMS to Move data to and from the
physical data files as needed in an effective and
efficient manner.
 Responsibilities of a DBMS
 Data security: DBMS creates a security system
that enforces user security and data privacy.
Security rules determine which users can access
the database, which data items each user can
access and which data operations (read, insert,
update, delete) the user can perform.
 Concurrent access: Managing concurrent data
access by multiple users, including provisions to
prevent conflicting simultaneous updates
 Responsibilities of a DBMS
 Backup and recovery: DBMS provide special
utilities that allow the DBA to perform backup and
restore procedures. Recovery features deals with
recovering a database after a failure such as bad
sector in the disk or a power failure
 Data integrity: DBMS minimize data redundancy
and maximize data consistency through integrity
rules. Data relationships stored in the data
dictionary and rules for Transaction management
are used to enforce data integrity and
consistency.
 Responsibilities of a DBMS
 Database access languages and APIs: DBMS
provides data access through a query language
known as SQL (structured query language). It
also provides application programming
interfaces to high level languages like C, C++,
Java, C#, VB.Net, Python etc.
 Database communication interfaces: DBMS
accepts end-user requests via multiple sources.
For example, the DBMS might provide access to
the database via the internet through the use of
web browser