Transcript Database applications - The University of Winnipeg

Database system concepts and architecture
Outline: Concepts and Architecture
(Chapter 2 – 3rd, 4th, 5th, and 6th ed.)
• Database schema
• Working process with a database system
• Database system architecture
• Data independence concept
• Database language
• Database application
• Interfaces
• Database environment
Sept. 2012
Yangjun Chen
ACS-3902
1
Database system concepts and architecture
Schema: a description of a database -- meta data
• the intension of the database
Schema evolution: if the database definition changes, we
say it evolves.
Database State: the data in the database at a particular point
in time
• the extension of the schema
Sept. 2012
Yangjun Chen
ACS-3902
2
Database system concepts and architecture
Sample database
Course CName CNo CrHrs Dept
Database 8803 3
CS
C
2606 3
CS
Student Name StNo Class Major
Smith 17
1
CS
Brown
8
2
CS
Grades StNo SId Grade
17 25 A
17 43 B
Section SId CNo Semester Yr
Instructor
32 8803 Spring 2000 Smith
25 8803 Winter 2000 Smith
43
Sept. 2012
2606
Spring 2000 Jones
Yangjun Chen
ACS-3902
3
Database system concepts and architecture
University database schema:
Student
Course
Cname
CNo
CrHrs
Dept.
Sept. 2012
string(20)
integer
integer
String(20)
Section
grades
StNo
SId
Grade
Name
StNo
Class
Major
string(20)
string(20)
integer
String(20)
SID
CNo
Semester
Yr
Instructor
integer
integer
String(20)
Yangjun Chen
ACS-3902
integer
string(20)
integer
integer
string(20)
4
Database system concepts and architecture
Schema evolution:
Student
Course
Cname
CNo
CrHrs
Dept.
Name
StNo
Class
Major
string(20)
string(20)
integer
String(20)
string(20)
integer
integer
String(20)
Prerequisite
CNo
Pre-CNo
… ...
Sept. 2012
Yangjun Chen
ACS-3902
string(20)
string(20)
5
Database system concepts and architecture
Database evolution:
Student
Course
Cname
Cno
CrHrs
Dept.
Name
StNo
Class
Major
Age
Sex
string(20)
string(20)
integer
String(20)
string(20)
integer
integer
String(20)
integer
string(20)
… ...
Sept. 2012
Yangjun Chen
ACS-3902
6
Database system concepts and architecture
Sample database
Course CName CNo CrHrs Dept
Database 8803 3
CS
C
2606 3
CS
Student Name StNo Class Major
Smith 17
1
CS
Brown
8
2
CS
Grades StNo Sid Grade
17 25 A
17 43 B
Section SId CNo Semester Yr
Instructor
32 8803 Spring 2000 Smith
25 8803 Winter 2000 Smith
43
Sept. 2012
2606
Spring 2000 Jones
Yangjun Chen
ACS-3902
7
Database system concepts and architecture
Database state changed:
Course CName CNo CrHrs Dept
Database 8803 3
CS
C
2606 3
CS
Student Name StNo Class Major
Smith 17
1
CS
Brown
8
2
CS
Grades StNo Sid Grade
17 25 A
17 43 B
Section SId CNo Semester Yr
Instructor
32 8803 Spring 2000 Smith
25 8803 Winter 2000 Smith
Sept. 2012
Yangjun Chen
ACS-3902
8
Database system concepts and architecture
We define the database
Definition
Construction
Manipulation
database state is the empty state with no
data
initial state when database is populated
(loaded)
current state changes with each insert,
delete, update
hopefully, the database goes from one
correct/valid state to another
Sept. 2012
Yangjun Chen
ACS-3902
9
Database system concepts and architecture
Three-schema architecture
External
view
External
view
Describes the
whole database
for all users
Conceptual
schema
Physical storage
structures and
details
Internal
schema
Sept. 2012
Yangjun Chen
A specific user or
groups view of the
database
ACS-3902
10
Database system concepts and architecture
Data independence
Ability to change the database at one level with no impact to
the next higher level
• physical data independence - the ability to change the
physical schema without affecting the conceptual schema
• typical example: add a new index
• logical data independence - the ability to change the
conceptual schema without affecting existing external
views or application programs
• typical example: add an attribute
Sept. 2012
Yangjun Chen
ACS-3902
11
Database system concepts and architecture
DBMS Languages
Data definition language (DDL): used to define the data
schema. Ideally one schema definition language per level
• in practice there might be one for all levels
• SQL provides DDL capabilities for the conceptual and
external levels
Sept. 2012
Yangjun Chen
ACS-3902
12
Database system concepts and architecture
DDL - Examples:
• Create schema:
Create schema COMPANY authorization JSMITH;
• Create table:
Create table EMPLOYEE
(FNAME
VARCHAR(15)
NOT NULL,
MINIT
CHAR,
LNAME
VARCHAR(15)
NOT NULL,
SSN
CHAR(9)
NOT NULL,
BDATE
DATE,
ADDRESS
VARCHAR(30),
SEX
CHAR,
SALARY
DECIMAL(10, 2),
SUPERSSN
CHAR(9),
DNO
INT
NOT NULL,
PRIMARY KEY(SSN),
FOREIGN KEY(SUPERSSN) REFERENCES EMPLOYEE(SSN),
FOREIGN KEY(DNO) REFERENCES DEPARTMENT(DNUMBER));
Sept. 2012
Yangjun Chen
ACS-3902
13
Database system concepts and architecture
DBMS Languages
Data Manipulation language (DML): Used to manipulate
data.
• typical systems provide a general purpose language for
inserting, updating, deleting, and retrieving data
• two distinctions: set-oriented and row-at-a-time
Sept. 2012
Yangjun Chen
ACS-3902
14
Database system concepts and architecture
DML - Examples:
• Insert
• Update
• Delete
INSERT INTO employee ( fname, lname, ssn, dno )
VALUES ( "Joe", "Smith", 909, 1);
UPDATE employee SET salary = 100000
WHERE ssn = 909;
DELETE FROM employee WHERE ssn = 909;
Query: Select salary from employee;
Sept. 2012
Yangjun Chen
ACS-3902
15
Database system concepts and architecture
Set-oriented operations
• select Name, StNo from student where Name like “M%”
• EXEC SQL DELETE FROM authors WHERE au_lname = 'White'
Record-at-a-time
• procedural - need loops, etc
• navigate through data obtaining 1 record at a time
• note that SQL does permit this via cursors
Sept. 2012
Yangjun Chen
ACS-3902
16
Database system concepts and architecture
Database applications
If an application program that accesses the database embeds
DML commands within it, then we have a host language and
a data sublanguage.
EXEC SQL DECLARE C1 CURSOR FOR
SELECT au_fname, au_lname FROM authors FOR BROWSE;
EXEC SQL OPEN C1;
while (SQLCODE == 0)
{
EXEC SQL FETCH C1 INTO :fname, :lname;
print (“the author name is:”, fname, laname);
}
Sept. 2012
Yangjun Chen
ACS-3902
17
Database system concepts and architecture
DBMS interfaces
• menu-based
• forms-based
• GUI – Graphic User Interface
• natural language
• for parametric users
• for dba
Sept. 2012
Yangjun Chen
ACS-3902
18
Database system concepts and architecture
Example of a menu-based interface: from MS Access
Sept. 2012
Yangjun Chen
ACS-3902
19
Database system concepts and architecture
Example of a GUI from StarTracker
Sept. 2012
Yangjun Chen
ACS-3902
20
Database system concepts and architecture
Example of a natural language interface: (from MS SS7)
English Query translates a natural-language question about data in the
database to a set of SQL SELECT statements that can then be executed
against the SQL Server database to get the answer.
For example, given a car sales database, an application can send English
Query a string containing the question, “How many blue Fords were sold
in 2011?”
English Query returns to the application an SQL statement such as:
SELECT COUNT(*)
FROM CarSales
WHERE Make = 'Ford’ AND Color = 'Blue'
AND DATEPART(yyyy, SalesDate) = ’2011'
The application can then execute the SQL statement against the SQL
Server database to get a number. It can return to the user.
Note the comment: English Query works best with a normalized
database.
Sept. 2012
Yangjun Chen
ACS-3902
21
Database system concepts and architecture
DBMS environment
see figure 2.3
• stored data manager
A module to control access to DBMS information that is stored on disk,
whether it is part of the database or the catalog.
• DDL compiler
A module to process schema definition, specified in the DDL, and store
description of the schema (meta-data) in the DBMS catalog.
• DML compiler
It translates the DML commands into object code for database access.
• run-time database processor
It handles database access at run time; it receives retrieval or update
operations and carries them out on the database.
Sept. 2012
Yangjun Chen
ACS-3902
22
Database system concepts and architecture
A DDL statement:
Create table EMPLOYEE
(FNAME
VARCHAR(15)
NOT NULL,
MINIT
CHAR,
LNAME
VARCHAR(15)
NOT NULL,
SSN
CHAR(9)
NOT NULL,
BDATE
DATE,
ADDRESS
VARCHAR(30),
SEX
CHAR,
SALARY
DECIMAL(10, 2),
SUPERSSN
CHAR(9),
DNO
INT
NOT NULL,
PRIMARY KEY(SSN),
FOREIGN KEY(SUPERSSN) REFERENCES EMPLOYEE(SSN),
FOREIGN KEY(DNO) REFERENCES DEPARTMENT(DNUMBER));
Sept. 2012
Yangjun Chen
ACS-3902
23
Database system concepts and architecture
Meta data in System Catalog:
REL_AND_ATTR_CATALOG
REL_NAME ATTR_NAME ATTR_TYPE MEMBER_OF_PK MEMBER_OF_FK FK_RELATION
FNAME
VSTR15
no
no
EMPLOYEE
SUPERSSN
STR9
no
yes
EMPLOYEE
EMPLOYEE
DNO
INTEGER
no
yes
DEPARTMENT
EMPLOYEE
... ...
... ...
Sept. 2012
Yangjun Chen
ACS-3902
24
Database system concepts and architecture
DBMS environment
• query compiler
It handles high-level queries that are entered interactively. It parses,
analyzes, and compiles or interprets a query by creating database access
code, and then generates calls to the rum-time processor for executing
the code.
• pre-compiler
It extracts DML commands from an application program which is
written in host programming language like C, Pascal, etc.
Sept. 2012
Yangjun Chen
ACS-3902
25
Database system concepts and architecture
DBMS utilities
• loading
loading existing files - such as text files or sequential files - into the
database.
• Backup
creating a backup copy of the database, usually by dumping the entire
database into tape.
• file reorganization
reorganizing a database file into a different file organization to get a better
performance.
• performance monitoring
monitoring database usage and providing statistics to the DBA.
Sept. 2012
Yangjun Chen
ACS-3902
26
Database system concepts and architecture
Classifying DBMSs
data model:
Relational /object-oriented/hierarchical/network/objectrelational
users: single-user/multi-user
location: distributed/centralized
cooperation: homogeneous/heterogeneous
OLTP: on-line transaction processing
• Used to run the day-to-day operations of a business
• event-oriented: take an order, make a reservation, payment for
goods, withdraw cash, ...
Sept. 2012
Yangjun Chen
ACS-3902
27
Database system concepts and architecture
name number
With attributes, etc:
fname
name
ssn
bdate
degree
1
lname
minit
sex
address
employee
supervisor
1
1
1
N hours
supervisee
N
M
1
works on
supervision
dependents of
N
dependent
sex
number of
employees
controls
manages
N
name
department
works for
N
salary
startdate
1
location
birthdate relationship
project
name number
location
Database system concepts and architecture
ER-to-Relational mapping
1. Create a relation for each strong entity type
2. Create a relation for each weak entity type
• include primary key of owner (an FK - foreign key)
• owner’s PK + partial key becomes PK
3. For each binary 1:1 relationship choose an entity and
include the other’s PK in it as an FK. Include any
attributes of the relationship
4. For each binary 1:n relationship, choose the n-side entity
and include an FK w.r.t the other entity. Include any
attributes of the relationship
Database system concepts and architecture
5. For each binary M:N relationship, create a relation for the
relationship
• include PKs of both participating entities and any
attributes of the relationship
• PK is the concatenation of the participating entity PKs
6. For each multivalued attribute create a new relation
• include the PK attributes of the entity type
• PK is the PK of the entity type and the multivalued
attribute
7. For each n-ary relationship, create a relation for the
relationship
• include PKs of all participating entities and any attributes
of the relationship
• PK may be the concatenation of the participating entity
PKs