Database

musicincurableData Management

Jan 31, 2013 (4 years and 6 months ago)

132 views

Database Fundamental & Design

by A.Surasit Samaisut

S
urasit.com

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

Page


2

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


3

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


4

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


5

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


6

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


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


7

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


8

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


9

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


10

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


11

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


12

Database Schema Example

Page


13

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


14

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


15

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


16

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


17

Relational Database Model


Page


18

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


19

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


20

E
-
R Diagram Example

Page


21

E
-
R Diagram for the Banking Enterprise


Page


22

E
-
R Diagram Example


Class Enrollment






Car Insurance

Page


23

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


24

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


25

Relationship Sets


A relationship is an association among several entities



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