Database Concept

Prepared by: Raquel Ofreneo, MIT

Outline

u The Need for Databases

u Data Models
u Relational Databases
u Database Design
 Database
u Database is a collection of integrated data or records

o The term “database” can refer to any collection of

data items
o Record is a representation of a physical or
conceptual entity
o Table – is the core structure for storing data
- store each data separately in its own column
- all of the columns put together create a table
 Database

o Field – is part of a record and contains a single

piece of data for the subject of the record. Each
record contains five (5) fields
o Record – is composed of fields and contains all the
data about one particular person, company or item
in a database. Records appear as rows in the
database table above.
Database versus file system
 Database
u Purpose of database system: Previously data is stored
in files
Drawbacks of using file system to store data
o Data redundancy and inconsistency – multiple file
formats and duplication of information in different
files
o Difficulty in accessing data – need to write a new
program to carry out each task
o Data Isolation – multiple files and formats
o Data security – Hard to provide user access to
some, but not all, data
 Database
Types of databases

u Single-userdatabase – A database that supports

only one user at a time
u Multiuser database – a database that supports
multiple concurrent users
Concurrency – in a public workgroup database, they are larger than the
personal and they can handle multiple users accessing the same data at the same
time.

u Workgroup database – a multiuser database that

usually supports fewer than 50 users or is used for
a specific department in an organization
 Database
Types of databases

Ø Enterprise database – the overall company data

representation, which provides support for present
and expected future needs
u Centralized database – a database located a single
site
Ø Cloud database – a database that is created and
maintained using cloud services, such as Microsoft
Azure or Amazon AWS
Database Management System (DBMS)

u Database management system (DBMS)

u Isa collection of data and set of programs to
access and store those data in a easy and efficient
manner
u The collection of programs that manages the
database structure and control access to data
stored in the database
Database Management System (DBMS)

u Database Applications:
u Banking: transactions
u Airlines: reservations, schedules
u Universities: registration, grades
u Sales: customers, products, purchases
u Online retailers: order tracking, customized
recommendations
u Manufacturing: production, inventory, orders, supply chain
u Human resources: employee records, salaries, tax
deductions
 Database
What is a database management system?
Is a software which is used to manage database
Databases are usually associated with software allowing
the data to be managed or queried
o SQL Server
o Microsoft Access
o IBM DB2
o MySQL
o Oracle
 University Database Example

u Application program examples

u Add new students, instructors, and courses
u Register students for courses, and generate class rosters
u Assign grades to students, compute grade point averages
(GPA) and generate transcripts

u In the early days, database applications were built directly on

top of file systems
 What is DB2

u Developed by IBM
u An abbreviation for “IBM Database 2”
u Introduced in June 1983
u Support the relational model
u Platform-specific DB2 product
u Supports SQL
 What is DB2

u Version of DB2 is DB2 10.5

u DB2 for Linux, Unix and Windows (informally known
as DB2 LUW)
u DB2 for z/OS (mainframe)
u DB2 for I (formerly OS/400)
u DB2 for VM/VSE
DB2 Interface
 Levels of Abstraction
u Physical level: the way a computer “sees” or stores data
describes how a record (e.g., instructor) is stored.
u Logical level: the way a person views data within the context of a problem
domain
- describes data stored in database, and the relationships among the
data.
type instructor = record
ID : string;
name : string;
dept_name : string;
salary : integer;
end;
u View level: application programs hide details of data types. Views can
also hide information (such as an employee’s salary) for security purposes.
 View of Data

An architecture for a database system

view level

view 1 view 2 … view n

logical
level

physical
level
Data Models
 Data Models
Data model – a representation, usually graphic, of a
complex “real-world” data structure.
u Hierarchical model – This model is based on an upside-down
tree structure in which record is called a segment. The top
record is the root segment. Each segment has a 1:M
relationship to the segment directly below it.
u Network model – An early data model that represent data as
a collection of records types in 1:M relationships.
o Schema – is the conceptual organization of the entire as
viewed by the database administrator
o - a logical groupings of database objects such as
tables, indexes, views and queries that are related to each
other
 Data Models

Ø Subschema – defines the portion of the database

“seen” by the application programs that actually
produce the desired information from the data within
the database
Ø A data manipulation language (DML) –the set of
commands that allows an end user to manipulate the
data in the database such as SELECT, INSERT, UPDATE,
DELETE, COMMIT and ROLLBACK
Ø also known a query language
Ø SQL is the most widely used commercial language
 Data Definition Language (DDL)

u A schema data definition language (DDL) – a

language that allows a database administrator to
define the database structure, schema and
subschema
u Specification notation for defining the database schema
Example: create table instructor (
ID char(5),
name varchar(20),
dept_name varchar(20),
salary numeric(8,2))
u DDL compiler generates a set of table templates stored in a data dictionary
u Data dictionary contains metadata (i.e., data about data)
u Database schema
u Integrity constraints - Primary key (ID uniquely identifies instructors)
u Authorization - Who can access what
 Data Models
u Relational model – Each relation (table) is conceptually
represented as a two-dimensional structure of intersecting
rows and columns. The relations are related to each other
through the sharing of common entity characteristics (values
in columns)
u Entity-Relationship data model (mainly for database design)
- A diagram that depicts an entity relationship model’s
entities, attributes, and relations.
u Object-oriented data model (OODM) – a data model whose
basic modeling structure is an object
u Extensible Markup Language (XML) – a database system that
stores and manages semistructured XML data
u NoSQL – a new generation of DBMS that is not based on a
traditional relational database model
Relational Model
u All the data is stored in various tables.
u Example of tabular data in the relational model
Relational Model
Entity Relationship Diagram ERD
u ERD – a diagram that depicts an entity relationship
model’s entities, attributes, and relations.
Object-Oriented Model
u Object-oriented data model (OODM) a data model
whose basic modeling structure is an object

Object Representation
 XML: Extensible Markup Language

u The ability to specify new tags, and to create nested tag

structures made XML a great way to exchange data, not just
documents
u XML has become the basis for all new generation data
interchange formats.
u A wide variety of tools is available for parsing, browsing and
querying XML documents/data
 Data Models
u NoSQL – is a large-scale distributed database system that
stores structured and unstructured data in efficient ways

Characteristics of NoSQL database:

o They are not based on the relational model and SQL
o They support highly distributed database architectures
o They provide high scalability, high availability and fault
tolerance
o They support very large amount of sparse data
o They are geared toward performance rather than transaction
consistency.