Transcript PowerPoint Template

Surasit.com
Database Fundamental & Design
by A.Surasit Samaisut
Copyrights 2009-2010 : All Rights Reserved
Database
 Is an integrated collection of logically related records or files that is stored
in a computer system which consolidates records previously stored in
separate files into a common pool of data records that provides data for
many applications
 A database is a collection of information that is organized so that it can
easily be accessed, managed, and updated
 Databases can be classified according to types of content: bibliographic,
full-text, numeric, and images
 The structure is achieved by organizing the data according to a database
model. The most commonly used model today is the relational model
Page  2
Database Management System (DBMS)
 Collection of interrelated data
 Set of programs to access the data
 DBMS contains information about a particular enterprise
 DBMS provides an environment that is both convenient and efficient
Page  3
Database Applications
 Databases touch all aspects of our lives
 Database Applications:
• Banking: all transactions
• Airlines: reservations, schedules
• Universities: registration, grades
• Sales: customers, products, purchases
• Manufacturing: production, inventory, orders, supply chain
• Human resources: employee records, salaries, tax deductions
Page  4
DBMS Features and Capabilities
 Query ability
• Querying is the process of requesting attribute information from various
perspectives and combinations of factors. Example: "How many 2-door cars
in Texas are green?" A database query language and report writer allow
users to interactively interrogate the database, analyze its data and update it
according to the users privileges on data
 Backup and replication
• Copies of attributes need to be made regularly in case primary disks or other
equipment fails. A periodic copy of attributes may also be created for a
distant organization that cannot readily access the original. DBMS usually
provide utilities to facilitate the process of extracting and disseminating
attribute sets. When data is replicated between database servers, so that the
information remains consistent throughout the database system and users
cannot tell or even know which server in the DBMS they are using, the
system is said to exhibit replication transparency
Page  5
DBMS Features and Capabilities
 Rule enforcement
• Often one wants to apply rules to attributes so that the attributes are clean
and reliable. For example, we may have a rule that says each car can have
only one engine associated with it (identified by Engine Number). If
somebody tries to associate a second engine with a given car, we want the
DBMS to deny such a request and display an error message. However, with
changes in the model specification such as, in this example, hybrid gaselectric cars, rules may need to change. Ideally such rules should be able to
be added and removed as needed without significant data layout redesign
 Security
• Often it is desirable to limit who can see or change which attributes or groups
of attributes. This may be managed directly by individual, or by the
assignment of individuals and privileges to groups, or (in the most elaborate
models) through the assignment of individuals and groups to roles which are
then granted entitlements
Page  6
DBMS Features and Capabilities
 Computation
• There are common computations requested on attributes such as counting,
summing, averaging, sorting, grouping, cross-referencing, etc. Rather than
have each computer application implement these from scratch, they can rely
on the DBMS to supply such calculations
 Change and access logging
• Often one wants to know who accessed what attributes, what was changed,
and when it was changed. Logging services allow this by keeping a record of
access occurrences and changes
 Automated optimization
• If there are frequently occurring usage patterns or requests, some DBMS can
adjust themselves to improve the speed of those interactions. In some cases
the DBMS will merely provide tools to monitor performance, allowing a
human expert to make the necessary adjustments after reviewing the
statistics collected
Page  7
Examples of Database Management Systems
 Adabas, Adaptive Server Enterprise, Alpha Five, Computhink's ViewWise,
CSQL, Daffodil DB, DataEase, FileMaker, Firebird, Glom, IBM DB2, IBM
UniVerse, Ingres, Informix, InterSystems Caché, Kexi, WX2, Linter SQL
RDBMS, Mark Logic, Microsoft Access, Microsoft SQL Server, Microsoft
Visual FoxPro, MonetDB, MySQL, OpenLink Virtuoso, OpenOffice.org
Base, Oracle Database, ParAccel, PostgreSQL, Progress, SQL
Anywhere, SQLite, Teradata, Vertica Analytic Database
Page  8
Data Definition Language (DDL)
 Specification notation for defining the database schema
create table account (
account-number char(10),
balance
integer)
 DDL compiler generates a set of tables stored in a data dictionary
 Data dictionary contains metadata (i.e., data about data)
• Database schema
• Data storage and definition language
– Language in which the storage structure and access methods used by the
database system are specified
– Usually an extension of the data definition language
Page  9
Database Schema
 The schema of a database system is its structure described in a formal
language supported by the database management system (DBMS)
 In a relational database, the schema defines the tables, the fields in each
table, and the relationships between fields and table
 Schemas are generally stored in a data dictionary. Although a schema is
defined in text database language, the term is often used to refer to a
graphical depiction of the database structure
Page  10
Database Schema Table
 Database schema table contains
• Table name and its columns
– Begins with the title of database table and list all columns from the table in
the blanket with its relationship
• Attribute
– Column’s name
• Domain name
– Short word to describe the attribute
• Meaning
– Explains what the attribute do
• Domain Definition
– Definition of attribute with type and its size
Page  11
Database Schema Example
Page  12
Data Manipulation Language (DML)
 Language for accessing and manipulating the data organized by the
appropriate data model
• DML also known as query language
 Two classes of languages
• Procedural – user specifies what data is required and how to get those data
• Nonprocedural – user specifies what data is required without specifying how
to get those data
Page  13
Database Query Language
 A database query language and report writer allows users to interactively
interrogate the database, analyze its data and update it according to the
users privileges on data
 It also controls the security of the database. Data security prevents
unauthorized users from viewing or updating the database. Using
passwords, users are allowed access to the entire database or subsets of
it called subschemas
 For example, an employee database can contain all the data about an
individual employee, but one group of users may be authorized to view
only payroll data, while others are allowed access to only work history and
medical data
Page  14
Database Query Language
 If the DBMS provides a way to interactively enter and update the
database, as well as interrogate it, this capability allows for managing
personal databases. However, it may not leave an audit trail of actions or
provide the kinds of controls necessary in a multi-user organization.
These controls are only available when a set of application programs are
customized for each data entry and updating function
 SQL is the most widely used query language
 SQL stands for Structured Query Language
Page  15
Data Models
 A collection of tools for describing
• data
• data relationships
• data semantics
• data constraints
 Relational model
 Entity-Relationship model
 Other models:
• object-oriented model
• semi-structured data models
• Older models: network model and hierarchical model
Page  16
Relational Database Model
Page  17
Entity-Relationship Model
 Is an abstract and conceptual representation of data
 Is a database modeling method, used to produce a type of conceptual
schema or semantic data model of a system, often a relational database,
and its requirements in a top-down fashion
 Widely used for database design
– Database design in E-R model usually converted to design in the relational
model which is used for storage and processing
 Diagrams created using this process are called entity-relationship
diagrams, or E-R diagrams, or ER diagrams or ERDs for short
Page  18
E-R Diagram
 Rectangles represent entity sets.
 Diamonds represent relationship sets.
 Lines link attributes to entity sets and entity sets to relationship sets.
 Ellipses represent attributes
– Double ellipses represent multi-valued attributes
– Dashed ellipses denote derived attributes
 Underline indicates primary key attributes
Page  19
E-R Diagram Example
Page  20
E-R Diagram for the Banking Enterprise
Page  21
E-R Diagram Example
 Class Enrollment
 Car Insurance
Page  22
Entity Sets
 A database can be modeled as:
• a collection of entities and relationship among entities
 An entity is an object that exists and is distinguishable from other objects.
• Example: specific person, company, event, plant
 Entities have attributes
• Example: people have names and addresses
 An entity set is a set of entities of the same type that share the same
properties
• Example: set of all persons, companies, trees, holidays
Page  23
Attributes
 An entity is represented by a set of attributes, that is descriptive properties
possessed by all members of an entity set
 Domain – the set of permitted values for each attribute
 Attribute types:
• Simple and composite attributes
• Single-valued and multi-valued attributes
– E.g. multi-valued attribute: phone-numbers
• Derived attributes
– Can be computed from other attributes
– E.g. age, given date of birth
Page  24
Relationship Sets
 A relationship is an association among several entities
 A relationship set is a mathematical relation among entities, each taken
from entity sets
Page  25