3902 Chapter 1

1

• 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

2

• 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

3
Sample database
Course CName CNo CrHrs Dept
Database 8803 3 CS
C 2606 3 CS
Student Name StNo Class Major
Grades StNo SId Grade
Smith 17 1 CS
17 25 A
Brown 8 2 CS
17 43 B
Section SId CNo Semester Yr
Instructor
32 8803 Spring 2000 Smith
25 8803 Winter 2000 Smith
43 2606 Spring 2000 Jones
4
University database schema Course Cname string(
20) CNo string(20) CrHrs integer Dept. Stri
ng(20) grades StNo integer SId integer Grade
String(20)
Student Name string(20) StNo integer Class i
nteger Major String(20) Section SID integer C
No string(20) Semester integer Yr integer Ins
tructor string(20)
5
Schema evolution Course Cname string(20) CNo
string(20) CrHrs integer Dept. String(20)
Student Name string(20) StNo integer Class i
nteger Major String(20) Prerequisite CNo strin
g(20) Pre-CNo string(20)
...
new table added
6
Database evolution Course Cname string(20) Cno
string(20) CrHrs integer Dept. String(20)
Student Name string(20) StNo integer Class i
nteger Major String(20) Age integer Sex stri
ng(20)
...
new attributes added
7
Sample database
Course CName CNo CrHrs Dept
Database 8803 3 CS
C 2606 3 CS
Student Name StNo Class Major
Grades StNo Sid Grade
Smith 17 1 CS
17 25 A
Brown 8 2 CS
17 43 B
Section SId CNo Semester Yr
Instructor
32 8803 Spring 2000 Smith
25 8803 Winter 2000 Smith
43 2606 Spring 2000 Jones
8
Database state changed
Course CName CNo CrHrs Dept
Database 8803 3 CS
C 2606 3 CS
Student Name StNo Class Major
Grades StNo Sid Grade
Smith 17 1 CS
17 25 A
Brown 8 2 CS
17 43 B
Section SId CNo Semester Yr
Instructor
32 8803 Spring 2000 Smith
25 8803 Winter 2000 Smith
9
Definition
We define the database 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
Construction
Manipulation
10
Three-schema architecture
A specific user or groups view of the database
External view
External view
Describes the whole database for all users
Conceptual schema
Physical storage structures and details
Internal schema
11

• 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

12
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

13

• 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))

14
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

15

• 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
16

• 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

17
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)
18

• DBMS interfaces
• menu-based
• forms-based
• GUI Graphic User Interface
• natural language
• for parametric users
• for dba

19
Example of a menu-based interface from MS Access
20
Example of a GUI from StarTracker
21
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.
22

• 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.

23
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))
24
Meta data in System Catalog
REL_AND_ATTR_CATALOG
REL_NAME ATTR_NAME ATTR_TYPE MEMBER_OF_PK
MEMBER_OF_FK FK_RELATION
... ...
... ...
25

• 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.

26

• 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.

27

• Classifying DBMSs
• data model
• Relational /object-oriented/hierarchical/network/o
  bject-relational
• 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, ...

28
With attributes, etc
name
number
location
1
works for
lname
fname
minit
department
N
salary
address
name
sex
number of employees
1
startdate
1
controls
ssn
manages
employee
1
bdate
1
N
N
hours
supervisor
N
degree
supervisee
M
works on
supervision
1
project
dependents of
name
number
location
N
dependent
relationship
birthdate
name
sex
29

• 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)
• owners PK partial key becomes PK
• 3. For each binary 11 relationship choose an
  entity and include the others PK in it as an FK.
  Include any attributes of the relationship
• 4. For each binary 1n relationship, choose the
  n-side entity and include an FK w.r.t the other
  entity. Include any attributes of the relationship

30

• 5. For each binary MN 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