CMSC424: Database Design

1
CMSC424 Database Design

• Lecture 3

2
Database Design Steps
Entity-relationship Model Typically used
for conceptual database design

• Three Levels of Modeling

Conceptual Data Model
Logical Data Model
Relational Model Typically used for
logical database design
Physical Data Model
3
Review Entity-Relationship Model

• Basics

Entity set
E1
R
Relationship set
Attribute (primary key if underlined)
a
4
Relationship Cardinalities

• One-to-One
• One-to-Many
• One customer can be associated with many accounts.

has
customer
account
has
customer
account
5
Relationship Cardinalities

• One-to-One
• One-to-Many
• Many-to-One
• Many customers can be associated with one account.

has
customer
account
has
customer
account
has
customer
account
6
Relationship Cardinalities

• One-to-One
• One-to-Many
• Many-to-One
• Many-to-Many

has
customer
account
has
customer
account
has
customer
account
has
customer
account
7
Relationship Set Keys

• Is cust-id, account-number a candidate key ?
• Depends

access-date
number
cust-id
has
customer
account

• If one-to-many relationship (as shown),
  account-number is a candidate key
• A given customer can have many accounts, but at
  most one account holder per account allowed
• So account number sufficient to uniquely identify
  a relationship

8
Thoughts

• Nothing about actual data
• How is it stored ?
• No talk about the query languages
• How do we access the data ?
• Semantic vs Syntactic Data Models
• Remember E/R Model is used for conceptual
  modeling
• Many conceptual models have the same properties
• They are much more about representing the
  knowledge than about database storage/querying

9
Thoughts

• Basic design principles
• Faithful
• Must make sense
• Satisfies the application requirements
• Models the requisite domain knowledge
• If not modeled, lost afterwards
• Avoid redundancy
• Potential for inconsistencies
• Go for simplicity
• Typically an iterative process that goes back and
  forth

10
Relational Data Model
Introduced by Ted Codd (late 60s early 70s)

• Before Network Data Model (Cobol as DDL,
  DML)
• Very contentious Database Wars (Charlie
  Bachman vs. Mike Stonebraker)

Relational data model contributes

• Separation of logical, physical data models (data
  independence)
• Declarative query languages
• Formal semantics
• Query optimization (key to commercial success)

1st prototypes

• Ingres ? CA
• Postgres ? Illustra ? Informix ? IBM
• System R ? Oracle, DB2

11
Key Abstraction Relation
Account
Terms

• Tables (aka Relations)

Why called Relations?
12
Why Called Relations?
Mathematical relations

• Given sets R 1, 2, 3, S 3, 4
• R ? S (1, 3), (1, 4), (2, 3), (2, 4), (3, 3),
  (3, 4)
• A relation on R, S is any subset (?) of R ? S
  (e.g (1, 4), (3, 4))

Account Í Branches ? Accounts ? Balances
(Downtown, A-101, 500), (Brighton, A-201,
900), (Brighton, A-217, 500)
13
Relations
Account
Relational database semantics defined in terms of
mathematical relations
14
Relations
Account
Terms

• Tables (aka Relations)

• Rows (aka tuples)

• Columns (aka attributes)

• Schema (e.g. Acct_Schema (bname, acct_no,
  balance))

15
Definitions

• Relation Schema (or Schema)
• A list of attributes and their domains
• We will require the domains to be atomic
• E.g. account(account-number, branch-name,
  balance)
• Relation Instance
• A particular instantiation of a relation
  with actual values
• Will change with time

Programming language equivalent A variable (e.g.
x)
Programming language equivalent Value of a
variable
16
Rest of the Class

• Converting from an E/R diagram to a relational
  schema
• Remember We still use E/R models for conceptual
  modeling of the database
• Relational Algebra
• Data retrieval language

17
E/R Diagrams ? Relations

• Convert entity sets into a relational schema with
  the same set of attributes

Customer
Customer_Schema(cname, ccity, cstreet)
cname
ccity
cstreet
assets
bcity
bname
Branch_Schema(bname, bcity, assets)
Branch
18
E/R Diagrams ? Relations

• Convert relationship sets also into a relational
  schema
• Remember A relationship is completely described
  by primary keys of associate entities and its own
  attributes

balance
acct-no
Account_Schema(acct-no, balance)
Account
access-date
Depositor_Schema(cname, acct-no, access-date)
Depositor
Customer
Customer_Schema(cname, ccity, cstreet)
Well Not quite. We can do better. It depends on
the relationship cardinality
ccity
cname
cstreet
19
E/R Diagrams ? Relations

• Say One-to-Many Relationship from Customer to
  Account
• ? Many accounts per customer

balance
acct-no
Account_Schema(acct-no, balance, cname,
access-date)
Account
access-date
Depositor
Customer
Customer_Schema(cname, ccity, cstreet)
ccity
cname
Exactly same information, fewer tables
cstreet
20
E/R Diagrams ? Relations
E1
a1

an
21
E/R Diagrams ? Relations
E1
a1

an
Not the whole story for Relationship Sets
22
E/R Diagrams ? Relations
R
23
E/R Diagrams ? Relations
R
R
24
E/R Diagrams ? Relations
R
R
R
25
E/R Diagrams ? Relations
R
R
R
R
26
Translating E/R Diagrams to Relations
assets
bcity
balance
bname
acct_no
Acct-Branch
Account
Branch
Loan-Branch
Depositor
Borrower
Customer
Loan
ccity
cname
lno
amt
cstreet
Q. How many tables does this get translated into?
A. 6 (account, branch, customer, loan, depositor,
borrower)
27
Bank Database

28
Bank Database

29
E/R Diagrams Relations
30
E/R Diagrams Relations
Emp
Emp-Phones
31
E/R Diagrams Relations
32
E/R Diagrams Relations
33
E/R Diagrams Relations

• Subclasses example
• Method 1
• Account (acct_no, balance)
• SAccount (acct_no, interest)
• CAccount (acct_no, overdraft)
• Method 2
• SAccount (acct_no, balance, interest)
• CAccount (acct_no, balance, overdraft)

Q When is method 2 not possible?
A When subclassing is partial
34
Keys and Relations
As in the E/R Model

• 1. Superkeys
• set of attributes of table for which every row
  has distinct set of values
• 2. Candidate keys
• minimal superkeys
• 3. Primary keys
• DBA-chosen candidate keys

e.g., Branch (bname, bcity, assets) Þ