musicincurableData Management

Jan 31, 2013 (5 years and 3 months ago)


Database Fundamental & Design

by A.Surasit Samaisut


C o p y r i g h t s 2 0 0 9
2 0 1 0 : A l l R i g h t s R e s e r v e d




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,
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



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



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



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



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 gas
electric cars, rules may need to change. Ideally such rules should be able to
be added and removed as needed without significant data layout redesign


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



DBMS Features and Capabilities


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



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,
Base, Oracle Database, ParAccel, PostgreSQL, Progress, SQL
Anywhere, SQLite, Teradata, Vertica Analytic Database



Data Definition Language (DDL)

Specification notation for defining the database schema

create table 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



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



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


Column’s name

Domain name

Short word to describe the attribute


Explains what the attribute do

Domain Definition

Definition of attribute with type and its size



Database Schema Example



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


user specifies what data is required and how to get those data


user specifies what data is required without specifying how
to get those data



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



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



Data Models

A collection of tools for describing


data relationships

data semantics

data constraints

Relational model

Relationship model

Other models:

oriented model

structured data models

Older models: network model and hierarchical model



Relational Database Model



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
diagrams, or E
R diagrams, or ER diagrams or ERDs for short



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



R Diagram Example



R Diagram for the Banking Enterprise



R Diagram Example

Class Enrollment

Car Insurance



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

Example: set of all persons, companies, trees, holidays




An entity is represented by a set of attributes, that is descriptive properties
possessed by all members of an entity set


the set of permitted values for each attribute

Attribute types:

Simple and composite attributes

valued and multi
valued attributes

E.g. multi
valued attribute: phone

Derived attributes

Can be computed from other attributes

E.g. age, given date of birth



Relationship Sets

A relationship is an association among several entities

A relationship set is a mathematical relation among
entities, each taken
from entity sets