Database Systems

1
Database Systems
2
Key Terms cont.

• Database System
• A database, a database management system and
  appropriate hardware and personnel.

3
Database System Hardware

• Set of physical devices on which a database
  resides. It consists of one or more computers,
  disk drives, CRT terminals, printers, tape
  drives, connecting cables and other auxiliary and
  connecting hardware.

4
Database System Hardware cont.
5
Database System Software

• A database software includes two types of
  software
• General-purpose database management software,
  usually called the database management system
  (DBMS)
• Application software that uses DBMS facilities to
  manipulate the database to achieve a specific
  business function, such as providing reports or
  documents, which can be used by users.

6
Database System Software cont.

• Application software is generally written
  standard programming language such as C, or it
  may be written in a language (commonly called a
  fourth-generation language) supplied with the
  DBMS.
• These programs utilise the command language of
  the DBMS and make use of the information
  contained in the data dictionary.

7
Database System Software cont.

• User Interface
• Language, menus and other facilities by which
  users interact with various system components,
  such as application programs, the DBMS
• Computer-aided software engineering (CASE) tools
• Automated tools used to design databases and
  application programs.

8
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.

9
Data Dictionary

• Definitions of data items in the database
  includes
• Elementary-level data items (fields),
• group and record-level data structures, and
• files or relational tables.

10
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.
• Metadata allow database designers and users to
  understand what data exist, what the data mean.
• Data without clear meaning can be confusing,
  misinterpreted or erroneous.

11
Metadata
E.g. Data Item Value Name Type Length Min
Max Description Name Character 30 Employee
Name ID Number 9 Employee No. Dept Character 10
Dept. No. Age Integer 2 18 60 Employee
Age Name Character 15 Dept. Name Manager Number
9 Mgr. Emp. No. Employee No. (ID)
unique Manager is an employee of the organisation
12
Database System People

• Two different types of people (users and
  practitioners) are concerned with the database.
• Users
• who need information from the database to carry
  out their primary business responsibility
• e.g. Executives, managers, staff, clerical
  personnel

13
Database System People cont.

• Practitioners
• people responsible for the database system and
  its associated application software.
• e.g. Database administrators, analysts,
  programmers, database and system designers,
  information systems managers.

14
Components of a Database System

• Four components People, H/W, S/W, Data
• Practitioners (analysts and database designers)
  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.

15
Components of a DBS cont.

• Users enter data into the system by following
  specified procedures.
• The entered data are maintained on hardware media
  such as disks and tapes.
• Application programmes that access the database
  are written by practitioners and users to be run
  on computers.

16
Components of a DBS cont.
17
Key Terms cont.

• Database Management System
• Systems software that facilitates the management
  of a database.
• E.g. Oracle, Access, SQL Server

18
Database Management System (DBMS)

• DBMS
• An application software that organises data into
  records in one or more databases and allows
  organising, accessing and sorting of the data in
  a variety of formats.

19
DBMS cont.

• Relational DBMS
• Most common type of DBMS. Data elements are
  stored in different tables made up of rows and
  columns. Relates data in different tables through
  the use of common data element(s).

20
Components of a DBMS
21
Data Security

• The database is a valuable resource needing
  protection.
• The DBMS provides database security by limiting
  access to the database to authorised personnel.
• Authorised users will generally be restricted as
  to the particular data they can access and
  whether they can update it.

22
Data Security

• Access is often controlled by passwords and by
  data views, which are definitions of restricted
  portions of the database.

23
Data Integrity

• The integrity and consistency of the database are
  protected via constraints on values that data
  items can have and by backup and recovery
  capabilities provided within the DBMS.
• Data constraint definitions are maintained in the
  data dictionary.

24
Data Integrity

• Backup and recovery are supported by software
  that automatically logs changes to the database
  and provides for a means of recovering the
  current state of the database in case of system
  failure.

25
Concurrent Data Access

• One of the chief functions of the DBMS is to
  support the access, retrieval and update of data
  in the database.
• The DBMS provides the physical mechanisms
  allowing multiple users to access a variety of
  related data quickly and efficiently.

26
User-Oriented Data Manipulation

• DBMS provides user-oriented data manipulation
  tools.
• Easy-to-use query languages allow users to
  formulate queries and request one-time reports
  directly from the database.
• Often query languages will contain facilities to
  format the results of queries as reports.

27
Data Manipulation

• Report generators have more powerful reporting
  facilities than those in the query language.

28
Application Development

• The DBMS commonly provides significant assistance
  to the application programmer.
• Tools for screen, menu and report generation
• application generators
• compilers
• data and view definition facilities

29
Application Development

• Modern database systems provide language
  components that are much more powerful than those
  of traditional languages, making the programming
  process itself considerably more efficient.
• Developer 2000/PowerBuilder for Oracle
• Visual Basic for Microsoft SQL server

30
Database Applications

• Databases range from those for a single user with
  a desktop computer to those on mainframe
  computers with thousands of users.
• Personal databases
• Workgroup databases
• Departmental databases
• Enterprise databases

31
Personal Computer Databases

• Designed to support one user with a standard
  alone PC.
• E.g. a sales person keeping track of this
  customer information with contact details.

32
Workgroup Databases

• A relatively small team of people (less than 25)
  who collaborate on the same project or
  application.
• E.g. a software development team maintaining a
  list of software objects.

33
Department Databases

• A department is a functional unit of an
  organisation. It is larger than a workgroup.
• Department databases are designed to support the
  various functions and activities of a department.
• E.g. a personnel database that is designed to
  track data concerning employees, jobs, skills and
  job assignments.

34
Enterprise Databases

• An enterprise is one whose scope is the entire
  organisation or enterprise.
• Such databases are intended to support
  organisation-wide operations and decision making.
• E.g. a large health care organisation that
  operates a group of medical centre's including
  hospitals, clinics and nursing homes.

35
Enterprise Databases cont.

• An enterprise database does support information
  needs from many departments. The most important
  type of enterprise database today is called a
  data warehouse.
• Data warehouse
• An integrated decision support database whose
  content is derived from the various operational
  databases.

36
Database Environment
37
3 Level ANSI/SPARC Architecture

• Made databases more independent of application
• Became a standard for the organisation of DBMS

38
3 Levels

• External
• Users and Applications view of data
• Conceptual
• Logical Data Model
• Physical
• Physical data Model

39

• 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

40
3 Level Architecture
user a
user i/program j
program x
sub-schema a
sub-schema i
sub-schema z
conceptual schema
physical schema
41
Physical View

• The DBMS must know
• exact physical location
• precise physical structure

Employee record
A.B.C. De Silva 222, Galle Road, Colombo
650370690VSenior Lecturer
Name (20 characters) Address (40
characters) NID (10 char)
Designation (15 char)
42
Physical View

• 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

43
Logical View

• The user/application must know
• existence
• logical reference

Employee
NID 650370690V
Name A.B.C. De Silva
Designation Senior Lecturer
Address 222, Galle Road, Colombo
44
Logical View

• 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
External View

• The user/application see
• authorised data
• own format

Lecturer
Name A.B.C. De Silva
Department Dept. of Computer Science
Designation Senior Lecturer
Age 35
46
External View

• This consists of user views of the database.
• Each definable user group will have its own view
  of the database.
• Each of these views gives a user-oriented
  description of the data elements and
  relationships of which the view is composed.

47
User View

• A logical description of some portion of the
  database that is required by a user to perform
  some task.
• E.g. Benefit application user will view part of
  the employee data, excluding data such as date of
  birth and salary.

48
External View

• It can be derived directly from the conceptual
  schema.
• The collection of all user views is the external
  level.
• SQL CREATE VIEW statement is used to create these
  views and SQL GRANT statement is used to restrict
  its use to a user group.

49
External View cont.

• External Views Allows to
• 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

50
External View cont.

• External Views Allows to
• 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

51
Mapping between Levels

• DBMS map or translate from one level to another.
• External ? Conceptual
• Conceptual ? Physical
• Database exists in reality only at the internal
  level.

52
DML/SQL
user a
user i/program j
program x
sub-schema a
sub-schema i
sub-schema z
SDDL
conceptual schema
DDL
physical schema
Practitioners DBA
Users
53
PractitionersDBA

• Data Definition Language (DDL)
• - is the language component of a DBMS that
  defines each data element as it appears in the
  database.
• Sub-Schema Data Definition Language (SDDL)
• - is the language component of a DBMS that
  defines data elements as it should appear to the
  end users and programmers.

54
User

• Data Manipulation Language (DML)
• - is a language associated with a DBMS that is
  employed by end users and programmers to
  manipulate data in the database.
• Structured Query Language (SQL)
• - pronounced as sequel, is the standard data
  manipulation for relational DBMSs.

55
Characteristics of Early DBMS

• Centralised Storage Management
• Transaction Management
• Recovery Facilities
• System Maintained Access Paths

56
Characteristics of Modern DBMS

• Query processing and optimisation
• Transaction management
• Concurrency control
• Database recovery
• Database security and authorisation
• Distributed databases
• Data warehousing and data mining

57
Function of a DBMS

• Functions of a DBMS are concerned with providing
  efficient flexible data processing capabilities
  without compromising data validity.
• Main Functions are
• Shared data
• Control redundancy
• Data integrity
• Data security

58
Capabilities of a DBMS

• Capabilities vary considerable, but basic
  operations are
• Data definition
• Data entry
• Data manipulation
• Data display

59
Database Approach

• Advantages
• Program-data independence
• Minimal data redundancy
• Improved data consistency
• Improved data sharing
• Increased productivity of application development
• Enforcement of standards
• Improved data quality
• Improved data accessibility and responsiveness
• Reduced program maintenance

60
Program-Data/Data Independence

• The separation of data descriptions (metadata)
  from the application programs that use the data.
• In the database approach data descriptions are
  stored in a central location called the data
  dictionary. This property allows an
  organisations data to change and evolve (within
  limits) without changing the application program
  that process the data.

61
Minimal Data Redundancy

• Data files are integrated into a single, logical
  structure. Each primary fact is recorded
  (ideally) in only one place in the database.
• E.g. Employee data not with the payroll and
  benefit files.
• Note Data redundancy is not eliminated entirely.
  Some data items will appear in more than one
  place (e.g. employee no.) to represent the
  relationship with others.

62
Improved Data Consistency

• By eliminating (or controlling) data redundancy,
  we greatly reduce the opportunities for
  inconsistency.
• E.g. employee address is stored only once and
  hence we cannot have disagreement on the stored
  values.
• Also, updating data values is greatly simplified
  and have avoid the wasted storage space.

63
Improved Data Sharing

• A database is designed as a shared corporate
  resource. Authorised users are granted permission
  to use the database, and each user (or group of
  users) is provided one or more user views to
  facilitate this use.
• E.g. employee data common to payroll, benefit
  applications will be shared among different users.

64
Increased Productivity of Application Development

• A major advantage of the database approach is
  that it greatly reduces the cost and time for
  developing new business applications.
• Programmer could concentrate on the specific
  functions required for the new application,
  without having to worry about design or low-level
  implementation details as related data have
  already been designed and implemented.

65
Increased Productivity of Application Development
cont.

• DBMS provides a number of high-level productivity
  tools such as forms and report generations and
  high-level languages that automate some of the
  activities of database design and implementation.

66
Enforcement of Standards

• When the database approach is implemented with
  full management support, the database
  administration function should be granted
  single-point authority and responsibility for
  establishing and enforcing data standards.
• Standards include naming conventions, data
  quality standards and uniform procedures for
  accessing, updating and protecting data.
• Powerful set of tools for developing and
  enforcing these standards are available for some
  DBMS.

67
Improved Data Quality

• A number of tools and processes are available to
  improve data quality.
• Database designers can specify integrity
  constraints that are enforced by the DBMS.
• One of the objectives of a data warehouse
  environment is to clean up operational data
  before they are placed in the data warehouse.
• Constraint
• A rule that cannot be violated by database users.

68
Improved Data Accessibility and Responsiveness

• With relational database, end users without
  programming experience can often retrieve and
  display data, even when it crosses traditional
  departmental boundaries.
• English-like query language SQL and query tools
  such as Query-By-Example provide such facilities.

69
Reduced Program Maintenance
Stored data are changed frequently for variety of
reasons such as new data items types are added,
and data formats change (e.g. date format from
two-digit to four digit). Data independence
allows to reduce the program maintenance time.
70
Database Approach

• Disadvantages
• DBMS are more vulnerable than file-based system
  because of the centralised nature of a large
  integrated database.
• If a failure occurs the recovery process is more
  complex and some times may results in lost
  transactions.
• Hardware, software and personnel cost are higher
  for DBMS.