Presentation

Members:
1.Areej Imtiaz
2.Shahida Latif
3.Nimra Mumtaz
4.Asima batool

Presented To:
Professor Abubakr.
 Foundations of Relational Implementation

 Relational data definition

 Relational Data Manipulation
 Data Manipulation Language (DML)
8.1 Defining Relational Data

 Tasks in implementation of a relational DB

1. Structure of the DB must be defined to the DBMS.
developer uses (DDL) or some equivalent means such as graphical display
2. Database is allocated to physical media (Hard Disk)
3. Filled with data (Data filling in the tables)
8.1.1 Review of Terminology
 Properties of Relation
1. The entries in the relation are single value
2. All the entries in any column are of the same kind
each column(Attribute) has a unique name and order is not important to the
relation
Each attribute has a domain
3. No two rows(Tuples) in the relation are identical and the order of the rows
is not important
Example of Relation
 PATIENT relation
Relation structure
PATIENT(Name, Age, Gender, AccountNumber,
Physician)
If we add constraints on allowable data values to
the relation structure, we then have a relational
schema
 Confusion Regarding the Term Key
(1) During the Design- logical key
-Key refers to one or more columns that uniquely
identify a row in a relation
(2) During the Implementation- physical key
-Key refers a column on which the DBMS builds an
index or other data structure
-used to access rows quickly (physical key need
not be unique, and often, in fact, they are not)
Ex.:ORDER(OrderNumber, OrderDate, CustNumber,
Amount)
In relational design: OrderNumber is unique
identifier In relational Implementation: any of 4
columns could be a key
 Index

Some people consider

Physical key  Index , Logical Key  Key
Three reasons for defining Indexes
1. To allow rows to be quickly accessed by means of the indexed attribute’s
value
2. Facilitate sorting rows by that attribute
e.g. In ORDER, OrderDate might be defined as a key so that a report showing
orders by date can be more quickly generated
3. Uniqueness
Indexes do not have to be unique. But sometime DBMS creates an unique
index to ensure that no duplicated values are accepted by the DBMS
8.1.2 Implementing a Relational
Database
 Implementation procedures

(1) Defining the Database Structure to the DBMS

(2) Allocating Media Space

(3) Creating the Database Data

(1) Defining the Database Structure to the DBMS

 DDL (Data Definition Language)

Graphical definition facilities: DBMS products on PC
Textual DDL: DBMS products on Servers and mainframes
(1) Defining the Database Structure to the DBMS
(2) Allocating Media Space
 For personal database: assign the database to a
directory and give the database a name (DBMS
allocates storage space automatically)
 For server and mainframe: to improve
performance and control, the distribution of the
database data across disks and channels must be
carefully planned (e.g. is may be advantageous to
locate certain tables on the same disk, or it may
be important to ensure that certain tables are not
located on the same disk)
Ex.: consider an order object that composed of
data from ORDER, LINE-ITEM, ITEM tables
Application retrieves one row from ORDER, several
rows from LINE-ITEM, and one row from ITEM for
each LINE-ITEM row. LINE-ITEM rows for a given
order tend to be clustered together, but ITEM rows
are not at all clustered
(2) Allocating Media Space
 Suppose that an organization concurrently
processes many order and has one large, fast disk
and one small, slower disk.
 Developer must determine the best place to locate
the data
 1. Item table is stored on the larger, fast disk and ORDER
and LINE-ITEM data on the smaller, slower disk.
 2. ORDER and LINE-ITEM data for prior months’ order are
placed on the slower disk and all the data for this
month’s order are placed on the faster disk.
(3) Creating the Database Data

 Once database has been defined and allocated to physical storage, it

can be filled with data
 The means by which this is done depends on the application
requirements and the features of the DBMS
 the Best case: all of the data are already in a computer-sensible format,
and the DBMS has features and tools to facilitate importing the data from
magnetic media
 the Worst case: all of the data must be entered via manual key entry
using application programs created from scratch by the developers
8.2 Relational Data Manipulation

 8.2.1 Categories of Relational Data Manipulation Language

1. Relational algebra: Defines operators that work on
relations (akin to the operators +, -). Relational
algebra is hard to use, partly because it is
procedural. Must know not only what we want but
also how to get it
-infrequently used in commercial DB processing
-discussed as a foundation to learn SQL
2. Relational calculus: not procedural. Need to
express what we want without expressing how to
get it.
-Never used in commercial DB processing
3. Transform-Oriented Language: non-procedural language
that transform input data expressed as relations into
results expressed as a single relation
-These languages provide easy-to-use structure for
expressing what is desired regarding the data supplied
(SQUARE, SEQUEL, SQL)

4. Graphical language: Query by Example and Query-by-

Form
-Products: Approach, Access and Cyberprise DBApp
-With graphical interface, the user is presented a
materialization of one or more relations
-The materialization might be a data entry form, it
might be a spread sheet, or it might be some other
structure
-The DBMS maps the materialization to the underlying
relation and constructs queries (most likely in SQL) on
behalf of the user
8.2.2 DML Interfaces to the DBMS
 User interface to a database
 Form and Report capabilities supplied by DBMS
 Via a query/update language
 through application programs (by means of DBMS command)
(1) Data Manipulation by Means of Forms
 (2) Query/Update Language Interface

Ex.: Consider the following SQL statement that processes the

relation PATIENT(Name, Age, Gender, AccountNumber,
Physician)

SELECT Name, Age

FROM PATIENT
WHERE Physician = ‘Levy’
(3) Stored Procedure Interface

 Query languages have generally proved to be too

complicated for the average end user
 many end users have specialists write the query
procedures, which are stored as files
 such procedures can be written to be parameter
driven, thereby enabling the users to execute
them when they change the data

Ex.: DO BILLING FOR BDATE = '9/15/1996'

 (4) Application Program Interface
 Through application programs written such as COBOL,
BASIC, Perl, Pascal, C++ (some application programs
are written in languages provided by the DBMS vendors)
Two styles of application program interface to the DBMS
1. Application program makes function calls to routines in
a function library provided with the DBMS
e.g. to read a particular row of a table, the application
program calls the DBMS read function and passes
parameters that indicate the table to be accessed, the
data to be retrieved, the criteria for row selection,
and the like
Object-Oriented Syntax in Access 2000
set db= currentdb()
set rs= db.OpenRecordset(“PATIENT”)
Methods: rs.AllowDeletions rs.MoveFirst
(4) Application Program Interface

2. Used with mainframe and server DBMS products

-a set of high level data access commands is
defined by the DBMS vendor
-These commands (which are peculiar to database
processing and not part of any standard language)
are embedded in the application program code