2 - 1/8/10
 Computer Science in a Nutshell … this...

Info iconThis preview shows page 1. Sign up to view the full content.

View Full Document Right Arrow Icon
This is the end of the preview. Sign up to access the rest of the document.

Unformatted text preview: 1/8/10
 Computer Science in a Nutshell … this course Important Formalizations
 Strong Emphasis
 Practice • Practical concepts Theory Formalization may not exactly match practical concept (often the core, e.g., SQL vs. Relational Algebra)
 • Formal definitions • Mathematical results • Skills • Tools Engineering • Performance tradeoffs Only a Bit
 Focus of the DB research community
 • Scalability • Reliability 1
 The limits of my language … •  “The limits of my language mean the limits of my world” –  L. Wittgenstein, Tractatus Logico-Philosophicus •  Here: –  Programming languages –  Database (query) languages –  Conceptual modeling languages –  Data models 2
 1
 1/8/10
 Introduction to Relational Databases Account
 Number
 101
 102
 103
 104
 105
 Owner
 J.
Smith
 W.
Wei
 J.
Smith
 M.
Jones
 H.
Mar?n
 Balance
 1000.00
 2000.00
 5000.00
 1000.00
 10000.00
 Type
 checking
 checking
 savings
 checking
 checking
 •  Assume this table has been defined to keep track of bank account –  Also referred to as a “relation” 3
 Relational Database Terminology The name of the table/relation
 Account
 Number
 101
 102
 103
 104
 105
 Owner
 J.
Smith
 W.
Wei
 J.
Smith
 M.
Jones
 H.
Mar?n
 Balance
 1000.00
 2000.00
 5000.00
 1000.00
 10000.00
 Type
 checking
 checking
 savings
 checking
 checking
 4
 2
 1/8/10
 Relational Database Terminology The name of the table (relation)
 The names of the “attributes” (columns)
 Account
 Number
 Owner
 Balance
 Type
 101
 J.
Smith
 1000.00
 checking
 102
 W.
Wei
 2000.00
 checking
 103
 J.
Smith
 5000.00
 savings
 104
 M.
Jones
 1000.00
 checking
 105
 H.
Mar?n
 10000.00
 checking
 5
 Relational Database Terminology The “schema” of the table
 Account
 Number
 101
 102
 103
 104
 105
 Owner
 J.
Smith
 W.
Wei
 J.
Smith
 M.
Jones
 H.
Mar?n
 Balance
 1000.00
 2000.00
 5000.00
 1000.00
 10000.00
 Type
 checking
 checking
 savings
 checking
 checking
 •  The schema sets the structure of the table •  The schema is the definition of the table –  Which generally includes more that what is shown here –  E.g., data types and constraints 6
 3
 1/8/10
 Account
 Number
 101
 102
 103
 104
 105
 Owner
 J.
Smith
 W.
Wei
 J.
Smith
 M.
Jones
 H.
Mar?n
 Balance
 1000.00
 2000.00
 5000.00
 1000.00
 10000.00
 Type
 checking
 checking
 savings
 checking
 checking
 Instance
 Rows
 …
 Relational Database Terminology •  Each entry in the table is called a “row”, “tuple”, or “record” (often used interchangeably) •  The (current) “instance” of the schema is a set of rows 7
 Relational Database Terminology The
“inten&on”
of
the
table
 Account
 Number
 101
 102
 104
 105
 107
 109
 Owner
 J.
Smith
 W.
Wei
 M.
Jones
 H.
Mar?n
 W.
Yu
 R.
Jones
 Balance
 1000.00
 2000.00
 1000.00
 10000.00
 7500.00
 432.55
 Type
 checking
 checking
 checking
 checking
 savings
 checking
 The
current
“extension”
(or
extent)
of
the
table
 •  Not used as often in relational databases –  mainly in deductive (logic-based) and object-oriented databases 8
 4
 1/8/10
 Relational Database Terminology Arity
of
a
table
is
the
number
of
aWributes
 Account
 Arity
of
this
rela?on
is
4
(because
there
are
4
aWributes)
 Number
 Owner
 Balance
 Type
 101
 J.
Smith
 1000.00
 checking
 Cardinality
 102
 W.
Wei
 2000.00
 checking
 of
this
 104
 M.
Jones
 1000.00
 checking
 instance
is
 6
(because
 105
 H.
Mar?n
 10000.00
 checking
 there
are
6
 107
 W.
Yu
 7500.00
 savings
 rows)
 109
 R.
Jones
 432.55
 checking
 “Cardinality”
of
a
table
is
the
number
of
rows
in
the
 current
instance
 •  
n
mathema?cs:
number
of
elements
of
a
set
 i 9
 Relational Database Terminology Account
 Number
 101
 102
 103
 104
 105
 Owner
 J.
Smith
 W.
Wei
 J.
Smith
 M.
Jones
 H.
Mar?n
 Deposit
 Account
 102
 102
 104
 105
 Transac?on‐id
 1
 2
 3
 4
 Check
 Account
 101
 101
 Check‐number
 Date
 Amount
 924
 10/23/09
 125.00
 925
 10/24/09
 23.98
 Balance
 1000.00
 2000.00
 5000.00
 1000.00
 10000.00
 Type
 checking
 checking
 savings
 checking
 checking
 Date
 10/22/09
 10/29/09
 10/29/09
 11/2/09
 Amount
 500.00
 200.00
 1000.00
 10000.00
 10
 5
 1/8/10
 Relational Database Terminology Account
 Number
 101
 102
 103
 104
 105
 Owner
 J.
Smith
 W.
Wei
 J.
Smith
 M.
Jones
 H.
Mar?n
 Deposit
 Account
 102
 102
 104
 105
 Transac?on‐id
 1
 2
 3
 4
 Check
 Account
 101
 101
 Check‐number
 Date
 Amount
 924
 10/23/09
 125.00
 925
 10/24/09
 23.98
 Balance
 1000.00
 2000.00
 5000.00
 1000.00
 10000.00
 Type
 checking
 checking
 savings
 checking
 checking
 Date
 10/22/09
 10/29/09
 10/29/09
 11/2/09
 •  Each table (typically) has a “key” •  The values of the key must be unique Amount
 500.00
 200.00
 1000.00
 10000.00
 What
is
the
key
for
 the
Check
table?
 11
 Relational Database Terminology Account
 Number
 101
 102
 103
 104
 105
 Owner
 J.
Smith
 W.
Wei
 J.
Smith
 M.
Jones
 H.
Mar?n
 Deposit
 Account
 102
 102
 104
 105
 Transac?on‐id
 1
 2
 3
 4
 Check
 Account
 101
 101
 Check‐number
 Date
 Amount
 924
 10/23/09
 125.00
 925
 10/24/09
 23.98
 Balance
 1000.00
 2000.00
 5000.00
 1000.00
 10000.00
 Type
 checking
 checking
 savings
 checking
 checking
 Date
 10/22/09
 10/29/09
 10/29/09
 11/2/09
 •  A “key” consists of one or more attributes •  We often underline key attributes Amount
 500.00
 200.00
 1000.00
 10000.00
 12
 6
 1/8/10
 Relational Database Terminology Account
 Number
 101
 102
 103
 104
 105
 Owner
 Balance
 J.
Smith
 1000.00
 W.
Wei
 2000.00
 J.
Smith
 5000.00
 M.
Jones
 1000.00
 H.
Mar?n
 10000.00
 Type
 checking
 checking
 savings
 checking
 checking
 Deposit
 Account
 102
 102
 104
 105
 106
 Transac?on‐id
 1
 2
 3
 4
 5
 Date
 10/22/09
 10/29/09
 10/29/09
 11/2/09
 12/5/09
 Amount
 500.00
 200.00
 1000.00
 10000.00
 555.00
 Is
this
legal?

 If
not,
how
do
we
prevent
it
from
happening?
 13
 Relational Database Terminology Account
 Number
 101
 102
 103
 104
 105
 Owner
 Balance
 J.
Smith
 1000.00
 W.
Wei
 2000.00
 J.
Smith
 5000.00
 M.
Jones
 1000.00
 H.
Mar?n
 10000.00
 Type
 checking
 checking
 savings
 checking
 checking
 Deposit
 Account
 102
 102
 104
 105
 106
 Transac?on‐id
 1
 2
 3
 4
 5
 Date
 10/22/09
 10/29/09
 10/29/09
 11/2/09
 12/5/09
 Amount
 500.00
 200.00
 1000.00
 10000.00
 555.00
 •  We say that Deposit.Account is a “foreign key” that references Account.Number –  I.e., each Deposit (row) must refer to an Account (row) •  If the DBMS enforces this constraint, we have “referential integrity” 14
 7
 1/8/10
 Relational Database Terminology Account
 Number
 101
 102
 103
 104
 105
 Owner
 Balance
 J.
Smith
 1000.00
 W.
Wei
 2000.00
 J.
Smith
 5000.00
 M.
Jones
 1000.00
 H.
Mar?n
 10000.00
 Type
 checking
 checking
 savings
 checking
 checking
 Check
 Account
 Check‐number
 Date
 Amount
 101
 924
 10/23/09
 125.00
 101
 925
 10/24/09
 23.98
 •  Are there any foreign keys in the Check table? Yes, Check.Account is a foreign key that references Account.Number 15
 Relational Database Terminology Account
 Number
 101
 102
 103
 104
 105
 Owner
 J.
Smith
 W.
Wei
 J.
Smith
 M.
Jones
 H.
Mar?n
 Deposit
 Account
 102
 102
 104
 105
 Transac?on‐id
 1
 2
 3
 4
 Check
 Account
 101
 101
 Check‐number
 Date
 Amount
 924
 10/23/09
 125.00
 925
 10/24/09
 23.98
 Balance
 1000.00
 2000.00
 5000.00
 1000.00
 10000.00
 Type
 checking
 checking
 savings
 checking
 checking
 Date
 10/22/09
 10/29/09
 10/29/09
 11/2/09
 Amount
 500.00
 200.00
 1000.00
 10000.00
 •  Foreign keys may or may not be part of the key for the table Deposit.Account
is
 not
part
of
the
key
 for
Deposit
 Check.Account
is
 part
of
the
key
for
 Deposit
 16
 8
 1/8/10
 Relational Database Terminology •  Consider the following sample data from a table Salesperson
 Company
 Age
 1
 Jones
 28
 2
 Smith
 28
 Commission
 $50,000
 $60,000
 Can
you
tell
what
the
key
for
this
table
is?
 17
 Relational Database Terminology •  Consider the following sample data from a table Salesperson
 Company
 Age
 1
 Jones
 28
 2
 Smith
 28
 Commission
 $50,000
 $60,000
 Now
can
you
tell
what
the
key
for
the
table
is?
 18
 9
 1/8/10
 Relational Database Terminology •  One possibility: Person Table with Id as the key Id
 1
 2
 Name
 Jones
 Smith
 Age
 28
 28
 Salary
 $50,000
 $60,000
 19
 Relational Database Terminology •  Another possibility: Sales Commission Table, by client company, per day Salesperson
 Company
 Day
 1
 Jones
 28
 2
 Smith
 28
 Commission
 $50,000
 $60,000
 Keys,
Table
Names,
A5ribute
Names
(help
to)

 tell
us
what
the
table
is
…

 (…
the
meaning,
or
“seman&cs”,
of
the
rela&on)
 20
 10
 1/8/10
 Relational Database Terminology Account
 Number
 101
 102
 …
 Owner
 J.
Smith
 W.
Wei
 Balance
 1000.00
 2000.00
 Type
 checking
 checking
 •  For every attribute of every table, the schema specifies allowable values. For example, Number must be an integer Owner must be a 30-character string Type must be “checking” or “savings” •  The set of allowed values for an attribute is called the “domain” of the attribute 21
 Specification of a Relational Database Schema •  Select the tables, with a name for each table –  A database schema may have multiple tables –  Each table has its own schema •  Select attributes for each table and give the domain for each attribute –  This is the basis of a relation (or table) schema •  … also: Specify the key(s) for each table –  There can be more than one key for a table –  There is only one primary key (more on this later) •  Specify all appropriate foreign keys 22
 11
 1/8/10
 Specification of a Relational Database Schema •  Another example database –  More standard notation; Each table has one primary key Teacher(Number,
Name,
Office,
E‐mail)
 Course(Number,
Name,
Descrip?on)
 Class‐Offering(Quarter,
Course,
Sec?on,
Teacher,
TimeDay)
 Student(Number,
Name,
Major,

Advisor)
 Completed(Student,
Course,
Quarter,
Sec?on,
Grade)
 23
 Specification of a Relational Database Schema •  Another example database –  with some foreign keys shown informally Teacher(Number,
Name,
Office,
E‐mail)
 Course(Number,
Name,
Descrip?on)
 Class‐Offering(Quarter,
Course,
Sec?on,
Teacher,
TimeDay)
 Student(Number,
Name,
Major,

Advisor)
 Completed(Student,
Course,
Quarter,
Sec?on,
Grade)
 What
foreign
keys
are
missing?
 24
 12
 1/8/10
 Specification of a Relational Database Schema •  Another example database –  with some foreign keys shown informally Teacher(Number,
Name,
Office,
E‐mail)
 Course(Number,
Name,
Descrip?on)
 Class‐Offering(Quarter,
Course,
Sec?on,
Teacher,
TimeDay)
 Student(Number,
Name,
Major,

Advisor)
 Completed(Student,
Course,
Quarter,
Sec?on,
Grade)
 25
 Specification of a Relational Database Schema •  Another example database –  with some foreign keys shown informally Teacher(Number,
Name,
Office,
E‐mail)
 Course(Number,
Name,
Descrip?on)
 Class‐Offering(Quarter,
Course,
Sec?on,
Teacher,
TimeDay)
 Student(Number,
Name,
Major,

Advisor)
 Completed(Student,
Course,
Quarter,
Sec?on,
Grade)
 What
are
the
limita&ons
of
this
schema?
 26
 13
 1/8/10
 First Version of Schema Account
 Number
 Owner
 Balance
 Type
 Deposit
 Account
 Transac?onId
 Date
 Amount
 Check
 Account
 CheckNumber
 Date
 Amount
 •  Do we need to change this schema to allow multiple people to own an account? If so, how? 27
 First Version of Schema Account
 Number
 Owner
 Balance
 Type
 Customer
 ID
 Name
 Deposit
 Account
 Transac?onId
 Date
 Amount
 AcctOwner
 AccountID
 Check
 Account
 CheckNumber
 Date
 Amount
 Phone
 Address
 CustomerID
 One approach: –  Keep Account.Number as a key –  Add a Customer table –  Add an AcctOwner table (“relationship”) •  What are the advantages/disadvantages of this approach? 28
 14
 1/8/10
 Typical Steps in the “Database Lifecycle” [Ch.2]
 Design
Data
Model
 (ER
Diagram)
 [Ch.21]
 Remove
Redundancy
 (Normaliza9on)
 [Ch.4,5]
 Manage
Tables
 (SQL)
 [Ch.3]
 Create
Schema
 (Rela9onal
Tables)
 [Ch.22]
 Determine
Indexes
 (Physical
Design)
 [Ch.6,7]
 Implement
Interface
 (Applica9on)
 29
 15
 ...
View Full Document

This note was uploaded on 02/06/2010 for the course CSE 302 taught by Professor Joel during the Summer '05 term at Punjab Engineering College.

Ask a homework question - tutors are online