Transcript Information storage: Introduction of database

Information storage: Introduction of database
10/7/2004
Xiangming Mu
What is database system
• Database
•
•
•
•
a collection of related data
represents some aspect of the real world
organized data for retrieval, maintenance
has intended group of users
• Database Management System (DBMS)
• a collection of programs for creating and using a database (a
software package)
• defines a DB: data type, structure, constraints
• constructs a DB: storing the data
• manipulates a DB: query, update, report
• Database System
• A Database + a DBMS
Data Models
• Conceptual (high-level) Data Model
• concepts: entity, attribute, relationship
• Entity-Relationship model (DBMS-independent)
• Representational (implementation-level)
• data represented by record structure
• i.e.) relational, network, hierarchical
• Physical (low-level)
• describes how data is stored in the disk
DB Schema vs. DB State
• Database Schema (intension)
• description of the database
• is specified during database design
• Database State (extension of the schema)
• current state of the database: a snapshot; it is an
actual data instances (occurrences) in a DB
• changes over time by update
• initially, a database is empty state with no data
• then, populate (load) the database with data
• DBMS checks every state of the database
• does it satisfy the structure and constraints specified in the
schema?
Three-Schema Architecture
1. An Internal Schema (internal level)
• physical storage structure, access paths
• uses a physical data model
2. A Conceptual Schema (conceptual level)
•
•
•
•
describes whole database structure
data types, constraints, user operations
hides the details of storage structure
uses conceptual/implementation data models
3. Multiple External Schemas (external level)
• each describes a part of the database for a particular user group
and hides the rest
• support multiple views of a database
• same data model as the conceptual schema
Three-Schema Architecture (cont’)
• Mappings for multi-level DBMS
– to transform a request specified at one level into the
request at another level
– access: external  conceptual  internal  DB
– retrieve: DB  internal  conceptual  external
• Three-Schema Architecture
• advantage: true data independence
• disadvantage: overhead cost of mappings
DBMS Languages
• Data Definition Language (DDL)
• to define DB schemas
• VDL (View Definition Lang.): external schema
• SDL (Storage Definition Lang.): internal schema
• Data Manipulation Language (DML)
• to specify database requests: update, retrieval
• high-level DML: which data to retrieve
• low-level DML: how to retrieve it
Data Models Overview
• Relational Data Model
• database represented as a collection of tables
• each table stored as a separate file
• uses higher-level query language (SQL)
• Object Data Model
• database defined in terms of objects, their properties, and their
operations
• classes: objects with same structure & behavior
• methods specify operations of each class
• Others
• Object-relational, hierarchical tree, XML, etc…
Conceptual Data Models
• Conceptual design of database
applications, and DBMS-independent
• Including database structure and
constraints --- Entity-Relationship (ER)
• Independent from its implementations (on
representation level and physical level)
E-R Model
• Representation
• data as entities, attributes, and relationships
• E-R schema diagram (example)
• Entity
•
•
•
•
a "thing" (object, event) in the real world
physical vs. conceptual existence: car vs. job
has a set of properties to completely describe it
Need a primary “key” attribute to identify a unique record of the
entity
• Attributes
– an entity has a value for each of its attributes
Relationship
• Relates two or more distinct entities with a specific
meaning (example)
• Relationship Instance
• an association among at least two entities belonging to one or
more entity types
• ‘John works in Accounting Department’
• Cardinality of a Relationship
• 1:1 (each entity can only participate once)
• 1:N (one entity can participate once, the other can participate
many times)
• M:N (each entity can participate many times)
What is SQL
•
SQL
•
•
•
Structured Query Language, for commercial relational
DBMSs
a DBMS may have its own extensions
SQL can
•
•
•
•
•
•
create a relational DB schema
create/modify/delete tables
extract information from the DB (query)
define views on the database
specify constraints, security, authorization
be embedded in a general-purpose programming language
(C, PASCAL, Java)
SQL Features
•
Data Definition Language (DDL)
–
allows database tables to be created or deleted.
CREATE TABLE - creates a new database table
ALTER TABLE - alters (changes) a database table
DROP TABLE - deletes a database table
•
Data Manipulation Language (DML)
–
–
modify and query database states
describes which data to retrieve, not how to retrieve it
SELECT - extracts data from a database table
UPDATE - updates data in a database table
DELETE - deletes data from a database table
INSERT INTO - inserts new data into a database table
An SQL example
 SQL Query Example
• retrieve the names of all employees who
work for the department number 4
• SELECT FNAME, LNAME
FROM
EMPLOYEE
WHERE DNO = 4;