Transcript Database - Department of Computer Science

CS157B Lecture 1
Prof. Sin-Min Lee
Department of Computer Science
San Jose State University
Tuesday Thursday
10:15 – 11:30
Database System Concepts
Also by appointment
2.2
©Silberschatz, Korth and Sudarshan
Your evaluation in this course is determined by:
30%
Class Presentation
10%
Presentation report
5%
??!
Database System Concepts
2.3
©Silberschatz, Korth and Sudarshan
You are required to write up
your report using LaTeX.
LaTeX is the standard tool
for typesetting scientific
articles.
Read
http://www.latex-project.org/intro.html
Inventor of TeX
Database System Concepts
2.4
©Silberschatz, Korth and Sudarshan
Database System Concepts
2.5
©Silberschatz, Korth and Sudarshan
??
Sometimes
Database System Concepts
2.6
©Silberschatz, Korth and Sudarshan
Database System Concepts
2.7
©Silberschatz, Korth and Sudarshan
Database System Concepts
2.8
©Silberschatz, Korth and Sudarshan
Database System Concepts
2.9
©Silberschatz, Korth and Sudarshan
Outline of Course
 Study of principals and techniques of databases
 Grades assigned as in information sheet
 Examples of use of databases
 Programming projects in database design and implementation
 Programming in Microsoft Access
 Programming in Java with Oracle
 Development of a web site with database support
Database System Concepts
2.10
©Silberschatz, Korth and Sudarshan
Textbook and class meetings
 Principles of Database Systems With Internet and Java
Applications
 by Greg Riccardi
 2001, Addison-Wesley
 Lectures and recitations
Database System Concepts
2.11
©Silberschatz, Korth and Sudarshan
Students’ Role in Class
 Attend class, please.
 The class notes are available, but they are not the full classroom
experience
 Recitation sections are provided to personalize and enhance your
learning environment
 You are paying for this, take advantage of it
 Read the book.
 There are many topics covered in the text, but not the lectures
 There are many details and examples in the text
 Seek help during office hours
Database System Concepts
2.12
©Silberschatz, Korth and Sudarshan
Current Events
 Each lecture will cover current events that affect the database
industry
 Please bring info to lectures and recitations
Database System Concepts
2.13
©Silberschatz, Korth and Sudarshan
Why Study Files and Databases?
 Next few slides address the following
 Importance of Databases to Economy
 Can you get a job in the database field?
 Representation of Information
 Add meaning to data
 Management of Complexity
 Divide system into layers, focus on data
 Management of Access and Security
 Efficiency of Access and Storage
 Separate data, allow optimizations
Database System Concepts
2.14
©Silberschatz, Korth and Sudarshan
Importance of Databases to Economy
 Expanding use of databases in retail sales
 Walmart, retail sales information tracking
 LL Bean, catalog sales information tracking
 Examples of analyses
 Sales of items
 Comparisons between daily totals of items sold and items in inventory
 Seasonal variations in sales of specific and similar items
 Relative sales of similar items with different features
 Market-basket collections (all items in a single purchase)
 Average and variation in total purchase amount
 Average and variation in number and price of items
 Correlation between sales of items in a single purchase
 Customer analysis
 Behavior of average customer
 Preferences of individual customers
Database System Concepts
2.15
©Silberschatz, Korth and Sudarshan
Importance of Database Companies
 Oracle is the 2nd largest software company
 It’s stock has outperformed S&P 500 and Microsoft
 This picture is the stock performance, as shown on the BigCharts
Web site from July, 1999 to July, 2000
Database System Concepts
2.16
©Silberschatz, Korth and Sudarshan
E commerce
 Companies are fighting for the market
 See Oracle Web site
 See IBM Web site
 XML and XSL at Microsoft
 http://www.microsoft.com/xml
 http://msdn.microsoft.com/xml/demos/
 http://msdn.microsoft.com/isapi/msdnlib.idc?theURL=/library/techart/
fm2koffice.htm
Database System Concepts
2.17
©Silberschatz, Korth and Sudarshan
Representation of Information
 Data is collections of bits
 physical database
 Information is data with meaning
 logical database
 Representation of meta-data
 database system is self-describing
 Database Management System (DBMS)
 define information content
 construct database
 manipulate by queries, reports and updates
 data plus software
Database System Concepts
2.18
©Silberschatz, Korth and Sudarshan
Management of Complexity
 Insulation between programs and data
 Program-data independence
 Program-operation independence
 Data abstraction
 conceptual model for users
 physical model for administrators
 Sharing data and multi-user transactions
 People




Database System Concepts
Database administrators
Database designers
Applications programmers
End users
2.19
©Silberschatz, Korth and Sudarshan
Management of Access and Security
 Controlling redundancy
 inconsistency and duplication
 Restricting unauthorized access
 Enforcing integrity constraints
 Providing backup and recovery
 Persistent storage for program objects
Database System Concepts
2.20
©Silberschatz, Korth and Sudarshan
Efficiency of Access and Storage
Cost of Access for Seagate Cheetah Disk
 Seek time
 Move access arm to the cylinder
 Avg 6 msec, min 0.6 msec
 Rotational delay
 1000 rpm, one revolution per 6 millisecond
 Average 3 msec
 Total latency max 12 msec, avg 9 msec
 Transfer rate
 24 Mbytes/sec
 Speed of memory access, Athlon 750 mhz
 Latency <100 nanosecond, 10,000 times faster than disk
 Transfer rate 1.6 GBytes/sec, 7 times faster than disk
Database System Concepts
2.21
©Silberschatz, Korth and Sudarshan
Hierarchical Cost of Storage
 Registers and Cache are fixed size
 Primary storage, memory (RAM) limited by hardware
 1000 Mbytes per CPU
 Secondary storage, disk, also limited by hardware
 100 Gbytes per CPU
 Tertiary storage, tape, etc. limited by storage volume
Database System Concepts
2.22
©Silberschatz, Korth and Sudarshan
Vocabulary
 Glossary of terms
 Define the terms as used in this subject
 Database literature is filled with terms
 Example of terms
 Data, bits
 Information, bits with meaning (type)
 Entity
 Schema
Database System Concepts
2.23
©Silberschatz, Korth and Sudarshan
Client-server computing
 Examples from web sites
 New York Times
 Pricewatch
 Industry movement
Database System Concepts
2.24
©Silberschatz, Korth and Sudarshan
What is a Database?
 Database is a collection of data
 data is known facts with implicit meaning
 database is logically coherent, organized.
 database is designed, built and populated for a specific purpose.
 Database management system
 collection of programs which support creation and maintenance of
databases.
Database System Concepts
2.25
©Silberschatz, Korth and Sudarshan
Time Line for Database Systems

before 1960 transition from punched card and tape

1960s, from file management to databases
 Bachman (GE) IDS and data structure diagrams
 IMS from IBM, Hierarchical Data Model
 IMS DB/DC, Network Model and communication
 SABRE, multi-user access with network

1970s, CODASYL and Relational Model
 Codd (IBM) Relational Model
 Chen introduced Entity Relationship Model
 Query languages developed (SQL)

1980s, Client/Server DBs, Oracle, DB2
 PC databases, DBase, Paradox, etc.
 SQL standard for definition and manipulation

1990s, web-based information delivery
 Trends: expert DBs, object DBs, distributed DBs
Database System Concepts
2.26
©Silberschatz, Korth and Sudarshan
Concepts and Architecture
 Data Model is a set of concepts that can be used to describe the
structure of a database
 data types, relationships, and constraints
 basic operations, for retrieval and updates
 user-defined operations, behavior
 3 types of data models
 High level or conceptual model
 entities,attributes, and relationships
 low-level or physical model
 record formats, indexes and access paths
 representational or implementation model
 record structures or object models
Database System Concepts
2.27
©Silberschatz, Korth and Sudarshan
Data Modeling
 A data model is a specification of the information content of a
system
 conceptual data model describes information in terms the users will
understand
 logical data model describes information in a way that can be used
to build a database
 physical data model describes information in terms of its
representation in physical storage
Database System Concepts
2.28
©Silberschatz, Korth and Sudarshan
Schemas and Instances
 Schema is the structure of a database
 intention or meaning of the data
 data models are schemas
 table definitions are schemas
 class definitions are schemas
 Instances are the contents of a database
 extension or values of the data
 objects are instances
 objects in a database are typically rows in a table
Database System Concepts
2.29
©Silberschatz, Korth and Sudarshan
Levels of database schemas
 Different schemas are presented to different users
External View 1
External View 2
External View 3
External level
logical to external mappings
Logical Schema
Logical level
internal to logical mapping
disk
Internal Schema
Database System Concepts
2.30
Internal level
©Silberschatz, Korth and Sudarshan
Data Independence
 Logical data independence
 Change in conceptual schema does not require change in external
schemas
 Expand or contract database with no change to external applications
 View mappings must be changed
 Physical data independence
 Change in internal schema does require change in conceptual
schema
 Reorganize the file and index structure, especially for improved
performance
 Conceptual mapping must be changed
Database System Concepts
2.31
©Silberschatz, Korth and Sudarshan
Database Languages
 DDL, data definition language, conceptual schema
 describe conceptual schemas
 SDL, storage definition language, internal schema
 describe file structures, indexes
 VDL, view definition language, external schema
 DML, data manipulation language
 High-level or non-procedural (e.g. SQL)
 Select Last Name from Roster where Section = 2
 Low-level or procedural
 For r in Roster loop
if r.section = 2 then
result.Add ( r.lastname );
Database System Concepts
2.32
©Silberschatz, Korth and Sudarshan
Information Engineering
Planning
Analysis
Design
Implementation
Database System Concepts
2.33
©Silberschatz, Korth and Sudarshan
Database Design Process
Application 1
External
Model
Application 2
Application 3
Application 4
External
Model
External
Model
External
Model
Application 1
Conceptual
requirements
Application 2
Conceptual
requirements
Application 3
Conceptual
requirements
Conceptual
Model
Logical
Model
Internal
Model
Application 4
Conceptual
requirements
Database System Concepts
2.34
©Silberschatz, Korth and Sudarshan
Stages in Database Design
 Requirements formulation and analysis
 Conceptual Design -- Conceptual Model
 Implementation Design -- Logical Model
 Physical Design --Physical Model
Database System Concepts
2.35
©Silberschatz, Korth and Sudarshan
Database Design Process
 Requirements formulation and analysis
 Purpose: Identify and describe the data that are used by the
organization
 Results: Metadata identified, Data Dictionary, Conceptual Model-ER diagram
Database System Concepts
2.36
©Silberschatz, Korth and Sudarshan
Database Design Process
 Requirements Formulation and analysis
 Systems Analysis Process
 Examine all of the information sources used in existing
applications
 Identify the characteristics of each data element
– numeric
– text
– date/time
– etc.
 Examine the tasks carried out using the information
 Examine results or reports created using the information
Database System Concepts
2.37
©Silberschatz, Korth and Sudarshan
Database Design Process
 Conceptual Model
 Merge the collective needs of all applications
 Determine what Entities are being used
 Some object about which information is to maintained
 What are the Attributes of those entities?
 Properties or characteristics of the entity
 What attributes uniquely identify the entity
 What are the Relationships between entities
 How the entities interact with each other?
Database System Concepts
2.38
©Silberschatz, Korth and Sudarshan
Database Design Process
 Logical Model
 How is each entity and relationship represented in the Data Model of
the DBMS
 Hierarchic?
 Network?
 Relational?
 Object-Oriented?
Database System Concepts
2.39
©Silberschatz, Korth and Sudarshan
Database Design Process
 Physical (AKA Internal) Model
 Choices of index file structure
 Choices of data storage formats
 Choices of disk layout
Database System Concepts
2.40
©Silberschatz, Korth and Sudarshan
Chapter 2, Representing Information
with Data Models
 Entity Relationship (ER) Model
 high-level, conceptual data model
 Specify conceptual schema
 conceptual database design
 Identify the data requirements of users and detailed descriptions
of data types, relationships and constraints.
 Concentrate on specifying the properties of the data, not storage.
Database System Concepts
2.41
©Silberschatz, Korth and Sudarshan
An Example of ER Modeling
 Company database
 Department
 name, number, manager (employee), start date of manager
 Projects controlled by department
 name, number, single location
 Employees
 name, ssn, address, salary, sex, birthdate
 assigned to department, several projects
 Dependents of employees
Database System Concepts
2.42
©Silberschatz, Korth and Sudarshan
Principals of ER Modeling
 Entities and classes
 Entity, a thing in the real world
 Entity Class, the structure of a collection of similar entities
 Attributes
 Attribute, a property of an entity
 Each entity has a value for each of its attributes
 Types of attributes
 simple vs. composite, single-valued vs. multi-valued, stored vs.
derived
 domains of attributes
Database System Concepts
2.43
©Silberschatz, Korth and Sudarshan
Relationships Between Entities
 Relationship type defines a set of associations among given
types.
 Relationsip Instances are particular relationships among
objects.
 Examples of relationship types in company database
 Manages: 1:1 between employee and department
 Works-for: 1:N between department and employee
 Controls: 1:N between department and project
Database System Concepts
2.44
©Silberschatz, Korth and Sudarshan
Relationships, Roles, and Structural
Constraints
 Roles are attributes that signify the function of a particular entity
(type) in a relationship




Employee manages department
Department is managed by employee
Employee works-for department
Department has employees who work for it
 Constraints can be
 cardinality
 Each department can have no more than one manager
 participation
 Each department must have a manager
Database System Concepts
2.45
©Silberschatz, Korth and Sudarshan
ER schema diagram for Company
M
Works
For
1
Employee
Department
Manages
1
1
Controls
M
Works
On
Supervises
Project
Depends
On
Dependent
Database System Concepts
2.46
©Silberschatz, Korth and Sudarshan
Entity Classes for BigHit Video
Entity Class
Customer
Videotape
Employee
PayStatement
TimeCard
Store
Rental
PurchaseOrder
Vendor
Database System Concepts
Description
A customer of the business
An item in the rental inventory
A person who works in one or more
stores
A record of the wages paid to an
employee
A record of a block of time worked by an
employee at a store
One of the retail outlets of BigHit Video
The rental of a videotape by a customer
for a specific period and cost
A request to purchase an item
A company that sells items to BigHit
Video
2.47
©Silberschatz, Korth and Sudarshan
Sample Attribute Specifications
Attribute
title
lastName
firstName
ssn
accountId
Type
string
string
string
string
number
Domain of values
unbounded
30 characters
30 characters
10 digits
4 byte integer
otherUsers set
set of strings of 30 characters
number
Rentals
address
4 byte integer
Database System Concepts
number
composite 2 strings of 30 characters, one
string of 2 characters, and one
string of 9 digits.
2.48
Description
The title of an item
The last name of a person
The first name of a person
A social security number
The identifier of a customer
account
Names of other people
authorized to use this
account
Number of rentals for a
customer
An address that consists of a
street, city, state and zipcode
©Silberschatz, Korth and Sudarshan
Entity Classes, Attributes and
Constraints
Class
Attribute
Customer
accountId
lastName
firstName
address
otherUsers
numberRentals
videotapeId
title
genre
datePaid
hoursWorked
amountPaid
Videotape
PayStatement
Database System Concepts
2.49
Constraints or further
description
key
not null
derived
key
not null
©Silberschatz, Korth and Sudarshan
Entities, instances of classes
custo lastName firstName address
merId
101
Block
Jane
102
Hamilton
Cherry
103
Harrison
Kate
104
Breaux
Carroll
Database System Concepts
otherUsers
street
1010
Main St.
3230
Dade St.
103
Dodd
Hall
76 Main
St.
numb
erRe
ntals
city
state zipcode
Apopka FL
30458
Joe Block,
3
Greg Jones
Dade
FL
30555
1
City
Apopka FL
30457
0
Apopka FL
2.50
30458
Judy
2
Breaux,
Cyrus
Lambeaux,
Jean Deaux
©Silberschatz, Korth and Sudarshan
Relationships Between Entities
 Relationship type defines a set of associations among given
types.
 Relationship Instances are particular relationships among
objects.
 Examples of relationship types in company database
 Manages: 1:1 between employee and department
 Works-for: 1:N between department and employee
 Controls: 1:N between department and project
Database System Concepts
2.51
©Silberschatz, Korth and Sudarshan
Relationships, Roles, and Structural
Constraints
 Roles are attributes that signify the function of a particular entity
(type) in a relationship
 Employee manages department
 Employee works-for department
 Constraints can be
 cardinality
 Each department can have no more than one manager
 participation
 Each department must have a manager
Database System Concepts
2.52
©Silberschatz, Korth and Sudarshan
Relationship Types and Instances
 Marriage relationship type
 Person related to Person
 One person has the role of “wife” one has the role of “husband”
 Relationship type may have one or more attributes
 e.g. weddingDate
 Marriage relationship (instance)
 Jane Block is married to Joe Block (relationship)
 Jane Block is the wife of Joe Block (role)
 Joe Block is the husband of Jane Block (role)
 Parent-child relationship type
 A person may have zero or more children
Database System Concepts
2.53
©Silberschatz, Korth and Sudarshan
Relationships are always one-to-one
 A relationship is an instance
 These pictures are sets of instances
Customer
(accountId)
Rents
Videotape
(videotapeId)
Customer
(accountId)
101
101
123
102
Videotape
(videotapeId)
101
101
123
102
145
145
103
103
90987
104
99787
Database System Concepts
PreviouslyRented
2.54
90987
104
99787
©Silberschatz, Korth and Sudarshan
Find the Entities, Attributes and
Relationships
BigHit Video
Rental Receipt
Account Id: 101
Videotape Id: 90987 date: January 9, 1999
cost: $2.99
Jane Block
Elizabeth
date due: January 11, 1999
1010 Main St.
Apopka, FL 30458
Database System Concepts
2.55
©Silberschatz, Korth and Sudarshan
ER schema diagram for BigHit Video
Key
Attribute
Relationship
Type
Attribute
lastName
f irstName
videotapeId
accountId
title
genre
balance
Customer
otherUsers
1
M
Rents
Entity
Class
Videotape
Multi-valued
Attribute
numberRentals
address
dateDue
cost
date
Acquired
dateRented
Derived
Attribute
Database System Concepts
street
zipcode
city
Composite
Attribute
state
2.56
rating
length
Cardinality
Constraint
©Silberschatz, Korth and Sudarshan
Chapter 4: Relational Model
 Structure of Relational Databases
Posted on Sun, Apr. 20, 2003
IBM database developer dead at 79
`RELATIONAL' MODEL IS BASIS OF TODAY'S
TRANSACTIONS
By Lisa M. Krieger
Mercury News
Database System Concepts
2.57
©Silberschatz, Korth and Sudarshan
Edgar F. Codd, an IBM computer pioneer who created the
``relational database model'' that underlies a $7 billion
industry of storing the world's online business data, died of
heart failure at home Friday in Williams Island, Fla. He was
79.
Bank accounts, credit cards, stock trading, travel
reservations, online auctions and innumerable other nowroutine data transactions all rely on Codd's model, based on
highly abstract and complex mathematical theory.
Before Codd's landmark research paper in 1970, it was
possible to store lots of information -- but analyzing it was
difficult, requiring lines and lines of code for even simple
tasks.
Database System Concepts
2.58
©Silberschatz, Korth and Sudarshan
His model made it possible to access large amounts of data from
small computers, giving businesses and government agencies
something they desperately needed: quick and easy access to
information.
``He had a vision about data that was considered radical at the
time,'' said computer scientist Don Chamberlin, also of IBM.
Larry Ellison of Oracle used Codd's model to build the first
commercially available relational database management system.
As complex and abstract as the math he loved, over the decades
Codd retained his British accent, his dry wit and his love of a
strong cup of tea, say family members.
Codd was the youngest of seven children born to a leather
processor and his schoolteacher wife in the remote town of
Portland, England.
He attended Oxford University on a full scholarship, earning
degrees in math and chemistry. Although eligible for a military
deferment because of his studies, he chose to fly in the Royal Air
Force.
Database System Concepts
2.59
©Silberschatz, Korth and Sudarshan
Codd first came to the United States in 1948, at the age of 25. He
found work with IBM as a programming mathematician for an
early proto-computer that filled two floors of a Manhattan office
building.
In 1953, Codd moved to Canada, frustrated that no one insisted
that Sen. Joseph McCarthy produce proof of his charges that
Communists were embedded in the U.S. government.
He later returned and became a U.S. citizen. In 1965, he earned a
doctorate from the University of Michigan in Ann Arbor.
A disappointing job rating from his supervisor in Poughkeepsie,
N.Y., spurred Codd to transfer to IBM's Santa Teresa development
laboratories in San Jose.
There he found existing data management systems ``seat-of-thepants, with no theory at all,'' he recalled in one interview. ``I
began reading documentation,'' Codd said, ``and I was
disgusted.''
Database System Concepts
2.60
©Silberschatz, Korth and Sudarshan
He proposed a solution that leaned heavily on mathematical logic:
the relational model.
He believed that all the information in a database should be
represented as values in the rows and columns of tables, and that
no information should be represented by pointers or connections
among records.
But support for the traditional database system within IBM was
large, powerful and antagonistic. It was at a meeting of a highlevel IBM technical committee that the relational model caught the
attention of IBM chairman Frank Cary. IBM subsequently
announced SQL/DS, its first relational product, in 1981. DB2, for
larger MVS machines, was announced in 1983.
``When he put two and two together, he didn't think about what
they added up to, but what they meant,'' said son Ronald Codd,
47, of Alamo. ``He had this natural ability to see a situation and
reach a conclusion that was a step beyond what people would
ordinarily think
Database System Concepts
2.61
©Silberschatz, Korth and Sudarshan
Codd's life changed in 1983, when he suffered a serious injury from
a fall. After his recovery, he retired from IBM and quit his beloved
hobby of recreational flying. But he continued to work until 1999,
commuting to his San Jose office at Codd and Date Consulting
Group, joined by longtime IBM collaborator Chris Date and
mathematician Sharon Weinberg, another IBM colleague, who after
12 years of courtship became Codd's second wife.
Edgar F. Codd
Born: Aug. 23, 1923, in Portland, England
Died: April 18, 2003, in Williams Island, Fla.
Database System Concepts
2.62
©Silberschatz, Korth and Sudarshan
An Appreciation
by C. J. Date
Ted Codd was a genuine computing
pioneer. He was also an inspiration to
all of us who had the fortune to know
him and work with him. He began his
career in 1949 as a programming
mathematician for IBM on the Selective
Sequence Electronic Calculator. He
subsequently participated in the
development of several important IBM
products, including its first commercial
electronic computer (IBM 701) and the
STRETCH machine, which led to IBM's
7090 mainframe technology. Then, in
the 1960's, he turned his attention to
the problem of managing large
commercial databases — and over the
next few years he created, single
handed, the invention with which his
name will forever be associated: the
relational model of data.
Database System Concepts
2.63
©Silberschatz, Korth and Sudarshan
The relational model is widely recognized as one of the great
technical innovations of the 20th century. Codd described it
and explored its implications in a series of research papers —
staggering in their originality--which he published throughout
the period 1969-1979. The effect of those papers was twofold:
They changed for good the way the IT world (including the
academic component f that world in particular) perceived the
database management problem; and they laid the foundation
for an entire new industry, the relational database industry,
now worth many billions of dollars a year. In fact, not only did
Codd's relational model set the entire discipline of database
management on a solid scientific footing, it also formed the
basis for a technology that has had, and continues to have, a
major impact on the very fabric of our society. It is no
exaggeration to say that Ted Codd is the intellectual father of
the modern database field.
Database System Concepts
2.64
©Silberschatz, Korth and Sudarshan
Codd's supreme achievement with the relational model should not be
allowed to eclipse the fact that he made major original contributions in
several other important areas as well, including multiprogramming,
natural language processing, and more recently Enterprise Delta (a
relational approach to business rules management), for which he and
his wife were granted a US patent. The depth and breadth of his
contributions were recognized by the long list of honors and elected
positions that were conferred on him during his lifetime, including IBM
Fellow; elected ACM Fellow; elected Fellow of the Britain Computer
Society; elected member of the National Academy of Engineering; and
elected member of the American Academy of Arts and Sciences. In
1981 he received the ACM Turing Award, the most prestigious award
in the field of computer science. He also received an outstanding
recognition award from IEEE; the very first annual Achievement Award
from the international DB2 Users Group: and another annual
achievement award from DAMA in 2001. Computerworld, in
celebration of the 25th anniversary of its publication, selected him as
one of 25 individuals in or related to the field of computing who have
had the most effect on our society. And Forbes magazine, which in
December 2002 published a list of the most important innovations and
contributions for each of the 85 years of its existence, selected for the
year 1970 the relational model of data, by E. F. Codd.
Database System Concepts
2.65
©Silberschatz, Korth and Sudarshan
Ted Codd was a native of England and a Royal Air Force veteran of World
War II. He moved to the United States in 1946 and became a naturalized
US citizen. He held MA degrees in mathematics and chemistry from Oxford
University and MS and PhD degrees in communication sciences from the
University of Michigan. He is survived by his wife Sharon and her parents,
Sol and Nora Boroff, of Williams Island, Florida; a brother David Codd and
his wife, Barbara and a sister, Katherine Codd, all of England; and a second
sister, Lucy Pickard, of Hamilton, Ontario. He also leaves four children and
their families; Katherine Codd Clark, her husband Lawrence, and their
daughters, Shannon and Allison, of Palo Alto, California; Ronald E. F. Codd,
his wife Susie, and their son, Ryan and daughter, Alexis, of Alamo,
California; Frank Codd and his wife, Aydes of Castro Valley, CA; and David
Codd, his wife Ileana, and their daughter Melissa and son, Andrew, of Boca
Raton, Florida. He also leaves nieces and nephews in England, Canada,
and Australia, as well as many, many friends and colleagues worldwide. He
is mourned and greatly missed by all.
Database System Concepts
2.66
©Silberschatz, Korth and Sudarshan
Example of a Relation
Database System Concepts
2.67
©Silberschatz, Korth and Sudarshan
Basic Structure
 Formally, given sets D1, D2, …. Dn a relation r is a subset of
D1 x D2 x … x Dn
Thus a relation is a set of n-tuples (a1, a2, …, an) where
each ai  Di
 Example: if
customer-name = {Jones, Smith, Curry, Lindsay}
customer-street = {Main, North, Park}
customer-city = {Harrison, Rye, Pittsfield}
Then r = { (Jones, Main, Harrison),
(Smith, North, Rye),
(Curry, North, Rye),
(Lindsay, Park, Pittsfield)}
is a relation over customer-name x customer-street x customer-city
Database System Concepts
2.68
©Silberschatz, Korth and Sudarshan
Attribute Types
 Each attribute of a relation has a name
 The set of allowed values for each attribute is called the domain
of the attribute
 Attribute values are (normally) required to be atomic, that is,
indivisible
 E.g. multivalued attribute values are not atomic
 E.g. composite attribute values are not atomic
 The special value null is a member of every domain
 The null value causes complications in the definition of many
operations
 we shall ignore the effect of null values in our main presentation
and consider their effect later
Database System Concepts
2.69
©Silberschatz, Korth and Sudarshan
Relation Schema
 A1, A2, …, An are attributes
 R = (A1, A2, …, An ) is a relation schema
E.g. Customer-schema =
(customer-name, customer-street, customer-city)
 r(R) is a relation on the relation schema R
E.g.
Database System Concepts
customer (Customer-schema)
2.70
©Silberschatz, Korth and Sudarshan
Relation Instance
 The current values (relation instance) of a relation are
specified by a table
 An element t of r is a tuple, represented by a row in a table
attributes
(or columns)
customer-name customer-street
Jones
Smith
Curry
Lindsay
Main
North
North
Park
customer-city
Harrison
Rye
Rye
Pittsfield
tuples
(or rows)
customer
Database System Concepts
2.71
©Silberschatz, Korth and Sudarshan
Relations are Unordered
 Order of tuples is irrelevant (tuples may be stored in an arbitrary order)
 E.g. account relation with unordered tuples
Database System Concepts
2.72
©Silberschatz, Korth and Sudarshan
Database
 A database consists of multiple relations
 Information about an enterprise is broken up into parts, with each
relation storing one part of the information
E.g.: account : stores information about accounts
depositor : stores information about which customer
owns which account
customer : stores information about customers
 Storing all information as a single relation such as
bank(account-number, balance, customer-name, ..)
results in
 repetition of information (e.g. two customers own an account)
 the need for null values (e.g. represent a customer without an
account)
 Normalization theory (Chapter 7) deals with how to design
relational schemas
Database System Concepts
2.73
©Silberschatz, Korth and Sudarshan
The customer Relation
Database System Concepts
2.74
©Silberschatz, Korth and Sudarshan
The depositor Relation
Database System Concepts
2.75
©Silberschatz, Korth and Sudarshan