• Many people collect things
– How about you?
• If you collect any thing, you probably are
familiar with some of the problems of
managing a collection
– e.g. stamps, photos, applications, bills,
information,
One way to keep track of a collection
is to create a database
• A database(DB) is a collection of information that is
related to particular subject or purpose.
• Databases are designed to offer an organized
mechanism for
storing,
managing and
retrieving information.
• They do so through the use of tables. If you’re familiar
with spreadsheets like Microsoft Excel, you’re probably
already accustomed to storing data in tabular form.
• Library systems
• Mailing list
• Video rent stall
• Order details
• Staff Attendance
• Salary Details
• Inventory Controls System
• Student details
• Contact information
• Flight reservation systems
• Hotel reservation systems, etc...
• Database Management System is a software that
facilitates the management of the database.
or
• A collection of programs that enables you to
store, modify, and extract information from a
database.
• There are many different types of DBMSs, ranging
from small systems that run on personal
computers to huge systems that run on
mainframes.
Database Applications
• Microsoft Office
– Ms Access
• Open office
– Base
• DB
• Oracle
• SQL Server
• Sybase
• FoxPro
• Clipper
• Paradox
Functions of a DBMS
• Data Security
• Data Integrity
• Backup and recovery
• Concurrent Data Access
• User-Oriented Data Manipulation
• Application Development
• Reduced data redundancy
• Reduced updating errors and increased
consistency
• Greater data integrity and independence from
applications programs
• Improved data access to users through use of
host and query languages
• Improved data security
• Reduced data entry, storage, and retrieval costs
• Facilitated development of new applications
program
• Database systems are complex, difficult, and
time-consuming to design
• Substantial hardware and software start-up costs
• Damage to database affects virtually all
applications programs
• Extensive conversion costs in moving form a file-
based system to a database system
• Initial training required for all programmers and
users
What is a database system?
• A Database System is an information system
that uses database. It consists of
a Database,
a Database Management System,
appropriate Hardware,
Software and
people.
Application S/W
User
Data
Result
Database
DBMS
User Interface
Database Systems: Then
Database Systems: Today
Database Development
• Practitioners in consultation with users identify data needs
and design database structures to accommodate these needs.
• The database structures are specified to the DBMS through
the data dictionary.
• Users enter data into the system by following specified
procedures.
• The entered data are maintained on hardware media such as
disks and tapes.
• Application programs that access the database are written by
practitioners and users to be run on computer.
Establishing
requirements
Maintenance Analysis
Testing Design
Implementation
Activity
• Identify the requirements of following
systems
– Library System
– Infortec International
– Hospital system
• Database File - the main file that
encompasses the entire database
• Table - a collection of data about a
specific topic
• Field - categories of data within a
table
• Data type - the properties of the
data (text, integer, time/date, etc)
• Value - the actual data
• File – Student database
• Table – Students
• Field – firstname
• Datatype – text
• Value - Robertson
Flat file dbs vs Relational dbs
• Flat file
– A flat file database is a database designed around
a single table
– The flat file design puts all database information in
one table, or list, with fields to represent all
parameters
• Relational
– A relational database, on the other hand,
incorporates multiple tables with methods for the
tables to work together.
Flat file
Relational
Activity
• Identify the tables and fields for following
situations.
– Library System
– Infortec International
– Hospital system
Guide with Access 2007
• Creating a database
• Creating tables
• Views
– Datasheet view
– Design view
• Database objects
• A Microsoft Access database is made up of several
components including
–tables,
–forms,
–queries, and
–reports.
These components are called database objects.
• MS Access includes seven types of database objects
that represent the major pieces of the database and
correspond with the buttons on the create tab.
• Tables – Stores all data within the database.
Queries – Running a query you can get a specific set
of data from one or more related table.(Retrieve
Data)
Forms – A form is designed for entering data
efficiently and accurately in a table.
Reports – Creates professional printout of
information that can include customized headers,
footers and calculation on group of records.
Pages – Creates web pages that are
automatically linked to the access database for data
enter and data reporting purposes.
Macros – Stores a collection of key strokes
or commands that can be used to automate
repetitive actions.
Modules – Store Visual Basic for
application code that can extends the features and
automated processes of an Access database.
Keys
• Primary key
a field in which very uniquely identifies its
records.
An attribute which act as a primary key
must have certain characteristics.
Can not have NULL values
Must be UNIQUE
value should not be REDUNDED
Keys
• Super key
– Any unique field(s) of a table
• Candidate key
– Every minimal super key of a table
• Primary key
– One of the candidate keys that we choose as the unique
identifier of a table
• Alternate key
– Candidate keys other than the primary key
• Composite key
– When the primary key contain more than one field it is
called a composite key
• Foreign key
– A key whose values matches with a primary key of
another table.
A primary key can be targeted by multiple
foreign keys from other tables. But a primary key
does not necessarily have to be the target of any
foreign keys.
Relationship types
• One to one
– there is exactly one record in the first table that
corresponds to exactly one record in the related table.
• One to many
– there is more than one records in the second table
that corresponds to exactly one record in the related
table.
• Many to many
– there are many records in the first table that
corresponds to more than one records in the related
table.
Guide with Access 2007
• How to create a primary key in a table
– Primary key
– Composite key
• Creating relationships between tables
Data Dictionary/Directory (Repository)
• A subsystem that keeps track of the definitions of
all data items in the database,
relationships that exists between various data
structures, Indexes that are used to access data
quickly, screen and report format definitions that
may be used by various application programs.
– Elementary – level data items (fields)
– Group and record - level data structures, and
– Files or relational tables.
Metadata
• Data that describe the properties or
characteristics of other data. Some of these
properties include data definitions, data
structures and rules or constraints.
• Item name, the data type, length, minimum
and maximum allowable values (where
appropriate)and a brief description of each
data item.
ANSI/SPARC Three-level Architecture
(Three Schema Architecture)
• We require a framework on which we can discuss
architectural issues for databases. The ANSI/SPARC Study
group defined three-level architecture for database systems.
• The ANSI-SPARC Architecture, where ANSI-SPARC stands for
American National Standards Institute, Standards Planning
And Requirements Committee, is an abstract design standard
for a Database Management System (DBMS), first proposed in
1975.
• Most modern commercial DBMS are based on this system.
The ANSI-SPARC model however never became a formal
standard.
• External (Sub) Schema
– defines the external view of data
as seen by a user or program
• Conceptual Schema
– defines the logical view of data
as seen by all users and programs
• Physical (Internal) Schema
– defines the physical view of data
as seen by a DBMS
41
External Schema
• Describes several views of the database based
on the database model.
• Several external schemas are possible for a
single database.
• Each view is based upon the user
requirements.
Example:
StdGrade(RegNo:Integer, Name:String,
Sem:Integer, Grade:Char)
– hide unauthorised data
e.g. salary, dob
– provide user view
e.g. view employee name, designation, department data
taken from employee and department files
– derive new attributes
e.g. age derived from dob or nid
– change unit of measurement
e.g. show age in years or months
– define security levels
e.g. update access to employee file
read-only to department file 43
Conceptual Schema
• Describes the stored data in terms of the data model of
the DBMS. This leads to conceptual database design.
• Example:
Student(RegNo:Integer, Name:String,
Sem:Integer, Branch:String)
Faculty(Fid:Integer, FName:String, Salary:Float)
Course(CourseNo:Integer, CName:String,
Credit:Integere, Dept:String)
Section(SecId:Integer, CourseNo:Integer,
Sem:Integer, Year:Integer, Instructor:String)
GradeReport(RegNo:Integer, SecId:Integer,
Grade:Char)
• This is a single logical description of all
data elements and their relationships.
• It is the result of the conceptual design
which involves analysis of all users
information needs and data definition of
data items needs to meet them.
• SQL CREATE TABLE statement is used to
define the data elements.
45
Physical Schema
• Describes the actual storage details of the
relations described in conceptual schema.
• Primary indexing, sequential, binary, secondary
indexing, etc.
• This leads to the physical database design.
• Physical view provides the disk drives,
physical addresses, indexes and pointers.
• Physical Database Design is the responsibility
of the Database Administrator (DBA). No user
is concerned with this view.
• Physical devices to contain the data
• Access methods to retrieve and update data
• maintain and improve database performance
47
Mapping
• In a DBMS base on the three-schema
architecture, each user group refers only to its
own external schema which is converted into a
request on the conceptual schema, then into a
request on the internal schema for processing on
the stored database.
• If the request is database retrieval, the data
extracted from the stored database must be
reformatted to match the user’s external view
before it is presented to the user.
Conceptual/Internal Mapping
• Defines the correspondence between the
conceptual view and the stored database.
• If the stored database is changed accordingly
so the conceptual schema can remain without
changing.
Conceptual/External Mapping
• It defines the correspondence between a
particular external view and the conceptual
view
• For example several conceptual fields can be
combined in to a single external field and so
on.
Database Users
Types of Database Users
• Database Administrator
• Database Designer
• End users
• System Analysts & Application Programmers
1.Database Administrator(DBA)
• A database administrator (DBA) is a person
responsible for
– authoring access to the database,
– Coordinating and maintatning db’s use
– Acquiring s/w & h/w resources as needed
Database administrator's duties
include:
• Development and design of database strategies.
• Monitoring and improving database performance
and capacity.
• Planning for future expansion requirements.
• Storage structure and access method definition.
• Schema and physical organization modification.
• Granting user authority to access the database.
• Specifying integrity constraints.
• Acting as liaison with users.
• Responding to changes in requirements.
2.Database Designer
• Responsible for identifying the data to be
stored in the db and for choosing the
appropriate structure to represent & store
those data
3.End Users
• These are the people who access the db for
– querying,
– updating &
– generating reports
• Primarily db exists for there usage
• There are 4 types of end users
– Casual end users
People who occasionally access to the db
Ex : Top level managers
– Parametric end users
People who constantly querying & updating the db
Ex: Reservation clerk of airlines / hotels
– Sophisticated end users
Users who access db for their complex requirements
Ex: Engineers, Scientists
– Stand – alone end users
Users who maintains personal dbs.
Ex: Shop Owner
The DA and DBA
• A data administrator is one who oversees the data
integrity of the database. They do not create objects and
they may not have privileges to do anything other than
"view" the data and report any data discrepancies to the
database administrator (one who administers the entire
database). The data administrator can check data in the
tables to make sure there are no errors, can check to see
if tables that need an index have one, are all programs
valid--stuff like that. The database administrator is
usually busy with fixing problems, creating table spaces,
tables, looking at error messages.
Object base logical models
• Entity - Relationship model
• Object – oriented model
• Semantic data model
• Functional model
Entity relationship data model
• Entity – An object which can be identified
distinctly.
Name of the entity
• Notation : -
• Ex : -
Employee Student
• Attribute – A property or characteristic of an
entity
• Notation : - Attribute
Emp_id Emp_name
• Ex : -
Employee
Types of attributes
• Simple (atomic) Attributes
– An attribute that cannot be broken down in to
parts
• Composite Attributes
– An attribute that can be subdivided in to parts.
First_name mid_name Last_name
Emp_name
• Single valued attributes
– An attribute that take only one value for a given
entity.
• Multi valued attributes
– An attribute that take more than one value for a
given entity.
Phone_no Skills
Employee
• Stored attributes
– An attribute whose values are recorded in the
database
• Derived attributes
– An attribute whose values can be calculated from
related attributes.
Birthday age
Employee
• Key attributes (identifiers)
An attribute or set of attributes which uniquely
identifies each entity.
– Simple identifiers
– Composite identifiers
RegNo Date
Emp_No
Key
Employee
Student_Payments
Relationships
• If an attribute of one entity type refers to
another entity type then there are some
relationship between them.
• Notation : -
Relationship
Degree of relationship
• Unary relationship
• Binary relationship
• Ternary relationship
• N - ary relationship
Unary relationship (Degree 1)
• Relationship among instances of one entity
type.
Employee
Manages
Binary relationship (Degree 2)
• Relationship among instances of two entity
types.
Customer Place Order
Ternary relationship (Degree 3)
• Simultaneous relationship among three entity
types.
Item
Supplier supplies Project
N-ary relationship(Degree above 3)
• Simultaneous relationship among entities
more than three.
Item
Supplier supplies Project
Shipment
Cardinality Ratios (Multiplicity)
• Cardinality ratio specifies the number of
relationship instances that an entity can
participate in.
1 to 1 Relationship (1 : 1)
President rules Country
1 1
1 to Many Relationship (1 : M)
Customer Place Order
1 M
Many to Many Relationship (M:N)
Student Follow Courses
M N
Participation
• Total (Mandatory)
• Partial (Optional)
Entity types
• Strong entities
• Weak entities
Introduction to Normalization
“The process of efficiently organizing data in the
database”
Tasks are to:
• Minimize redundancy
• Minimize insertion, deletion, update
anomalies
St_ Course_I Ins.
Name Campus Major Title Ins.loc Marks
Id d name
Comp.
125 Silva Colombo Prog. CA C B 104 74
Arch
PP Prolog A B 105 60
Comp. B 104
127 Perera Kandy Acct CA C 50
Arch
AA Accounts Z X 103 60
EC Econ E E 102 65
1NF
• Every attribute of the relation should be
atomic
• (i.e. Not multivalued)
2NF
• 1NF and
• Every non prime attribute should be fully
functional dependent on the primary key
Functional dependency
• Student(RegNo, Name, add)
RegNo Name
RegNo add
RegNo,Name add
Name add
Fully Functional dependency
• Student(RegNo, Name, add)
RegNo Name FFD
RegNo add FFD
RegNo,Name add PFD
Student(RegNo, Name, add,NIC)
PFD
RegNo,NIC
3NF
• 2NF and
• No non prime attribute is transitively
dependent on the primary key
Boyce – codd Normal form
• 3 NF
• For every dependency x y, x should
be a super key
Data Protection
• Database Security
Security involves ensuring that users are
allowed to do the things they are trying to do.
Methods of data security
• Passwords
• User access levels
• Encryption
• Authentication during data communication
• Multiple backups
• Antivirus software
• Physical measures
• Firewall
Transaction
• A transaction is a logical unit of work.
– Ex: Deposit transaction, Withdraw
transaction, RequestBalance
transaction
• In general, that transforms a consistent state
of the database into another consistent state.
The system components that provides
atomicity is known as the transaction manager
and COMMIT and ROLL BACK are the keys to
the way it works:
COMMIT
• Signals successful end of transaction.
• It tells the transaction manager that a logical
unit of work has been successfully completed,
the database is in a consistent state again, and
all of the updates made by the logical unit of
work can now be committed or made
permanent.
ROLL BACK
• signals unsuccessful end-of-transaction:
• It tells the transaction manager that
something has gone wrong, the database
might be in an inconsistent state, and all of
the update made by the logical unit of work so
far must be rolled back or undone.
The ACID Properties
• Atomicity – Transactions are atomic (either all
happens or non happen)
• Consistency – Transactions preserve database
consistency. Which means a transaction
transforms a consistent state of a database
into another consistent state, without
necessarily preserving consistency at all
immediate points.
• Isolation – Transactions are isolated from one
another. That is, even though in general there
will be many transactions running
concurrently, any given transaction’s updates
are concealed from all the rest, until that
transaction commits.
• Durability – Once transaction commits its
updates survive in the database, even if there
is a subsequent system crash.
System Recovery
• The system must be prepared to recover, not
only from purely local failures such as the
occurrence of an overflow condition within an
individual transaction, but also from "global"
failures such as power outage.
• A local failure, by definition, affects only the
transaction in which the failure has actually
occurred;
• Such failures fall into two broad categories
– System Failures
– Media Failures
• System failures (e.g. power outage), which
affect all transactions currently in progress but
do not physically damage the database. A
system failure is sometimes called a soft
crash.
• The key point regarding system failure is that
the contents of main memory are lost (in
particular, the database buffers are lost).
• Media failures (e.g., head crash on the disk),
which do cause damage to the database, or to
some portion of it. And affect at least those
transactions currently using that portion. A
media failure is sometimes called a hard
crash.
Concurrency
• Database systems typically provide multi-user
access to a shared database.
• As such concurrency control is critical in
ensuring that concurrent operations are
carried out correctly and efficiently.
Concurrency Problems
• The Lost update
• Uncommitted dependency
• Locking