ICT_Lecture_5

1.

Database Systems
Lecture

2.

The purpose of the lecture
• Learn to use database management software to
develop data-intensive applications
• Become familiar with fundamental DBMS
(СУБД) concepts
• Gain exposure to future trends in databases

3.

3
Typical roles and career path for
database professionals
• Data Architect
• Database Architect
• Database Administrator (DBA)
• Application Developer

4.

4
What is a Database?

5.

5
Database
• A database is
▫ a shared collection of logically related data
▫ a self-describing collection of integrated records
Data Storage in a Relational Database

6.

6
Database Management System (DBMS)
• A software system that enables users to
▫ Define the data,
▫ Maintain it
▫ Control its access
• Uses Structured Query Language (SQL) for data
manipulation.
• Controls the overall access to the database

7.

7
What does a DBMS allow the user to do?
A DBMS will allow the user to:
• create tables and fields
• create relationships between tables
• add, edit and delete data
• maintain the integrity of the data in the database
• set the access rights of the database users
• allow the database to be searched using queries
• create forms to help data input and viewing the
information in the database
• create reports to output information from the
database

8.

8
Database System

9.

9
Database System
• Components of a Database System
▫ An application specific database
▫ A DBMS that maintains this database
▫ Application Software that manipulates the
database

10.

10
Database creation
• Database creation involves using software to define and build the
structures to hold the data. In a database file the data is structured
in a particular way.
▫ A single item of data is stored in a named Field
▫ A complete set of fields makes up a Record, the Key Field is a special
field that contains data unique to that record
▫ All records on one Entity are stored in a Table
▫ One or more tables then
make up the database File

11.

11
Some popular DBMS
• Proprietary
▫ Oracle
▫ Microsoft SQL Server
▫ IBM DB2
▫ Microsoft Access
• Open-source
▫ MySQL
▫ PostgreSQL

12.

12
Two Approaches to Data Storage
• There are two ways to store data:
▫ The data can be stored in traditional data files
(text files)
▫ The data can be stored in databases

13.

13
Pros of using a database over data files
• Data Abstraction
▫ Similar to data abstraction in OOP
▫ Applications are not concerned with the
organization of the data as compared to one using
flat file system
▫ The structure of data is separated from the actual
data which:
Minimizes the dependencies between application
programs and the data they use
Reduces the impact of charge

14.

14
Pros of using a database over data files
• Reliability
▫ Data consistency despite software and hardware
failures
▫ Provides integrity constraints to keep data
consistent. Integrity constraints represent the
meaning of the data.
▫ Two Examples of integrity constraints:
Bank accounts may not have balances below $0
Two customers cannot buy the same seat on an airline flight.

15.

15
Pros of using a database over data files
• Efficiency
▫ DBMS provides efficiency both in terms of space and
access time to the appropriate data
Space efficiency is achieved by minimizing data redundancy
Process efficiency achieved through
▫ Query optimizations
▫ Eliminating multiple updates to maintain data consistency
▫ Use of access methods that act as index to retrieve the data
Concurrent access to data
▫ Improves system throughout

16.

16
Pros of using a database over data files
• Other advantages
▫ More information from the same data due to better
data organization
▫ Improved security by means of access rights to specific
data
▫ Economy of scale by using a central repository for data
across the organization
▫ Improved maintenance through data independence

17.

17
Cons of using a database over data files
• Increased complexity
▫ Complexity in terms of constructing the data model
▫ Failure to understand the database system can lead to bad design
decisions having serious implications for an organization
• Cost of DBMS
▫ Cost varies significantly with number of users the DBMS should support
▫ Recurrent maintenance cost of the DBMS through version updates
• Additional hardware cost for improved performance
▫ The DBMS consumes additional space for storing indexes, organizing
data etc., to improve performance of data retrieval.

18.

18
Cons of using a database over data
files
• High impact of a failure
▫ Users and applications are fully dependent on the database
▫ Hence failure of some components in the database can bring
operations to a halt.
• Performance – in specific applications
▫ File based systems are built for specific applications and hence
are highly specialized to maximize performance
▫ But databases tradeoff performance to cater for a wide range of
applications.

19.

19
Operations and Constraints
• Operations to be performed by the system
▫ Maintain information about library members
▫ Titles included in the library holdings
▫ Track borrowed books
▫ Maintain hold requests
• Constraints
▫ Differentiate an original and a copy of the book
▫ The same title can have multiple copies

20.

20
Data Entities and their Relationships

21.

21
Data Entities
• Member
▫ Represents each member of the library using their:
Membership Number
Driving License Number
Name
Address
Phone number
• Title
▫ An unique entity representing each title of a book using:
The book’s title
The book identified
First author
ISBN
Call number
Year of Publication
Publisher

22.

22
Data Entities
• Book
▫ For each of the book this maintains:
The book identifier
Title call number
Status
Borrower’s member number
Due date
• Hold
▫ For books that are requested to be on hold:
The member id of the person requesting the hold
The requested title’s call number
Date the hold was requested

23.

23
Integrity Constraints
• Useful for encoding the library rules as part of
the database
▫ Examples:
Preventing a library member from borrowing a book
if they have already borrowed 5 books, the
maximum number of outstanding books per
member.
Prevent unregistered users from borrowing books.

24.

24
Information model
• An information model is an abstract, formal
representation of entities that includes their
properties, relationships and the operations that
can be performed on them.

25.

25
Data Model
• The primary motivation behind the concept is to
formalize the description of a problem domain
without constraining how that description will
be mapped to an actual implementation in
software. There may be many mappings of the
Information Model. Such mappings are called
data models

26.

26
Relationship between an Information
Model and a Data Model

27.

27
Types of information models
• Network (CODASYL): 1970’s
• Hierarchical (IMS): late 1960’s and 1970’s
• Relational: 1970’s and early 1980’s
• Entity-Relationship: 1970’s
• Extended Relational: 1980’s
• Semantic: late 1970’s and 1980’s
• Object-oriented: late 1980’s and early 1990’s
• Object-relational: late 1980’s and early 1990’s
• Semi-structured (XML): late 1990’s to the
present

28.

28
Entity-Relationship model
• The relational data model is simple and elegant. It has a solid mathematic
foundation based on sets theory and predicate calculus and is the most used
data model for databases today.
• Peter Chen (1976) proposed thinking of a database as a collection of
instances of entities.
• Entities are objects that have an existence independent of any other entities
in the database.
• Entities have attributes, which are the data elements that characterize the
entity.
• One or more of these attributes could be designated to be a key.
• Lastly, there could be relationships between entities. Relationships could be
1-to-1, 1-to-n, n-to-1 or m-to-n, depending on how the entities participated
in the relationship. Relationships could also have attributes that described
the relationship.

29.

29