1.1 cas cs 460/660 entity/relationship model. 1.2 review what a dbms offer? efficient data...
TRANSCRIPT
1.2
ReviewReview
What a DBMS offer?
Efficient data organization
Efficient data retrieval
Data integrity
1.3
Review-the big pictureReview-the big picture
Data Modelling Relational E-R
Query Languages SQL Relational Algebra Relational Calculus
Storing Data File Indexes Buffer Pool Management
Query Optimization External Sorting Join Algorithms Query Plans, Cost Estimation
Data Integrity
Data Organization
Data retrieval
1.4
Databases Model the Real WorldDatabases Model the Real World
“Data Model” translates real world things into structures computers can store
Many models: Relational, E-R, O-O, Network, Hierarchical, etc.
Relational (more next time) Rows & Columns
Keys & Foreign Keys to link Relations
sid name login age gpa
53666 Jones jones@cs 18 3.4 53688 Smith smith@eecs 18 3.2 53650 Smith smith@math 19 3.8
Enrolled Students
1.5
Problems with Relational ModelProblems with Relational Model
With complicated schemas, it may be hard for a person to understand the structure from the data definition.
CREATE TABLE Students(sid CHAR(20), name CHAR(20), login CHAR(10), age INTEGER, gpa FLOAT)
CREATE TABLE Enrolled(sid CHAR(20), cid CHAR(20), grade CHAR(2))
Studentscid grade sid Carnatic101 C 53666 Reggae203 B 53666 Topology112 A 53650 History105 B 53666
Enrolled
1.6
One Solution: The E-R ModelOne Solution: The E-R Model
Instead of relations, it has: Entities and Relationships
These are described with diagrams both structure, notation more obvious to humans
lot
name
Employee
ssn
Works_In
sincedname
budgetdid
Department
1.7
Steps in Database DesignSteps in Database Design
Requirements Analysis user needs; what must database do?
Conceptual Design high level descr (often done w/ER model)
Logical Design translate ER into DBMS data model
Schema Refinement consistency, normalization
Physical Design indexes, disk layout
Security Design who accesses what, and how
1.8
Example: DBA for Bank of AmericaExample: DBA for Bank of America
Requirements Specification Determine the requirements of clients ( Database to store
information about customers, accounts, loans, branches, transactions, …)
Conceptual Design Express client requirements in terms of E/R model.
Confirm with clients that requirements are correct.
Specify required data operations
Logical Design Convert E/R model to relational, object-based, XML-based,…
Physical Design Specify file organizations, build indexes
1.9
ER Model BasicsER Model Basics
Entity: Real-world thing, distinguishable from other objects.
Noun phrase (e.g., Bob Smith, Comm Ave Branch, Account 1234, etc)
Entity described by set of attributes.
Entity Set: A collection of similar entities. E.g., all employees. All entities in an entity set have the same set of attributes. (Until we consider
hierarchies, anyway!)
Each attribute has a domain.
Employees
ssnname
lot
1.10
ER Model Basics (Contd.)ER Model Basics (Contd.)
Relationship: Association among two or more entities. E.g., Bob Smith works in Pharmacy department. relationships can have their own attributes.
Verb phrases (e.g., works_at, enrolled_in, etc)
Relationship Set: Collection of similar relationships.
An n-ary relationship set R relates n entity sets E1 ... En ; each relationship in R involves entities e1 E1, ..., en En
lot
name
Employees
ssn
Works_In
sincedname
budgetdid
Departments
1.11
E/R Data ModelE/R Data ModelAn ExampleAn Example
Employees Works_At
essn
Branches
ename
phone
children
since seniority bname bcity
Works_Formanager
workerEntity Set
Relationship Set
Attribute
Employee
Works_For
phone
Lots of notation to come.
1.12
E/R Data ModelE/R Data ModelTypes of AttributesTypes of Attributes
Defaultename
children
seniority
Multivalued
Derived
Employees Works_At
essn
Branches
ename
phone
children
since seniority bname bcity
Works_Formanager
worker
1.13
E/R Data ModelE/R Data ModelTypes of relationshipsTypes of relationships
Many-to-One (n:1)
Many-to-Many (n:m)Works_At
Works_For
Employees Works_At
essn
Branches
ename
phone
children
since seniority bname bcity
Works_Formanager
worker
1.14
E/R Data ModelE/R Data ModelRecursive relationshipsRecursive relationships
Employeemanager
workerWorks_For
Recursive relationships: Must be declared with roles
Employees Works_At
essn
Branches
ename
phone
children
since seniority bname bcity
Works_Formanager
worker
1.15
An Example: Employees can have multiple phones
E/R Data ModelE/R Data ModelDesign Issue #1: Entity Sets vs. AttributesDesign Issue #1: Entity Sets vs. Attributes
Employees
phone_no phone_loc
Employees
no loc
PhonesUsesvs(a) (b)
To resolve, determine how phones are used 1. Can many employees share a phone?
(If yes, then (b))
2. Can employees have multiple phones?
(if yes, then (b), or (a) with multivalued attributes)
3. Else
(a), perhaps with composite attributes
Employees
phonenoloc
1.16
An Example: How to model bank loans
E/R Data ModelE/R Data ModelDesign Issue #2: Entity Sets vs. Relationship SetsDesign Issue #2: Entity Sets vs. Relationship Sets
Customers
cssn cname
vs
(a)
To resolve, determine how loans are issued 1. Can there be more than one customer per loan?
If yes, then (a). Otherwise, loan info must be replicated for each customer (wasteful, potential update anomalies)
2. Is loan a noun or a verb?
Both, but more of a noun to a bank. (hence (a) probably more appropriate)
Borrows Loans
lno amt
Customers
cssn cname
(b)
Loans Branches
bname bcity
lno amt
1.17
An Example:
E/R Data ModelE/R Data ModelDesign Issue #3: Relationship CardinalitiesDesign Issue #3: Relationship Cardinalities
Customers Borrows Loans? ?
Variations on Borrows: 1. Can a customer hold multiple loans?
2. Can a loan be jointly held by more than 1 customer?
1.18
E/R Data ModelE/R Data ModelDesign Issue #3: Relationship CardinalitiesDesign Issue #3: Relationship Cardinalities
Type Illustrated Multiple Loans? Joint Loans?
One-to-One (1:1) No No
Many-to-one (n:1) No Yes
One-to-many (1:n) Yes No
Many-to-many (n:m) Yes Yes
Cardinalities of Borrows:
Borrows
Borrows
Borrows
Borrows
Customers Borrows Loans? ?
1.19
E/R Data ModelE/R Data ModelDesign Issue #3: Relationship Cardinalities (cont)Design Issue #3: Relationship Cardinalities (cont)
In general...
1:1
n:1
1:n
n:m
1.20
An Example: Works_At
E/R Data ModelE/R Data ModelDesign Issue #4: N-ary vs Binary Relationship SetsDesign Issue #4: N-ary vs Binary Relationship Sets
Employees Works_at Branches
Depts
Employees WAE Branches
Depts
WABinary:
Ternary:
WAB
WAD
vs(Joe, Moody, Acct) Works_At
(Joe, w3) WAE
(Moody, w3) WAB
(Acct, w3) WAD
Choose n-arywhen possible!(Avoids redundancy,update anomalies)
1.21
Key = set of attributes identifying individual entities or relationships
E/R Data ModelE/R Data ModelKeysKeys
Employees
essn ename eaddress ephone
A. Superkey: any attribute set that distinguishes identities e.g., {essn}, {essn, ename, eaddress}
B. Candidate Key: “minimal superkey” (can’t remove attributes and preserve “keyness”) e.g., {essn}, {ename, eaddress}
C. Primary Key: candidate key chosen as the key by a DBA e.g., {essn} (denoted by underline)
1.22
E/R Data ModelE/R Data ModelRelationship Set KeysRelationship Set Keys
Employees
essn ename ...
Works_At Branches
bname bcity ...
Q: What attributes needed to represent relationships in Works_At?
since
e1e2
e3
b1
b2
A: {essn, bname, since}
1.23
E/R Data ModelE/R Data ModelRelationship Set Keys (cont.)Relationship Set Keys (cont.)
Q: What are the candidate keys of Works_At?
e1e2
e3
b1
b2
A: {essn}
Employees
essn ename ...
Works_At Branches
bname bcity ...since
1.24
E/R Data ModelE/R Data ModelRelationship Set Keys (cont.)Relationship Set Keys (cont.)
Q: What are the candidate keys if Works_At is...?
A: {essn, bname} Assumption: employees have <= 1 record per branch
b. n:m
a. 1:n
c. 1:1
A: {bname}
A: {essn}, {bname}
Employees
essn ename ...
Works_At Branches
bname bcity ...since
1.25
General Rules for Relationship Set Keys
E/R Data ModelE/R Data ModelRelationship Set Keys (cont.)Relationship Set Keys (cont.)
E1
P (E1) ...
R E2
P (E2) ...
If R is:
R1:11:nn:1n:m
Candidate KeysP (E1) or P (E2)
P (E2)P (E1)
P (E1) P (E2)
1.26
Idea: Existence of one entity depends on another
Example: Loans and Loan Payments
E/R Data ModelE/R Data ModelExistence Dependencies and Weak Entity SetsExistence Dependencies and Weak Entity Sets
Loans
lno lamt
Loan_Pmt Payments
pno pdate pamt
Weak Entity Set
Identifying Relationship
Total Participation
1.27
E/R Data ModelE/R Data ModelExistence Dependencies and Weak Entity SetsExistence Dependencies and Weak Entity Sets
Weak Entity Sets
existence of payments depends upon loans
have no superkeys: different payment records (for different loans) can be identical
instead of keys, discriminators: discriminate between payments for given loan (e.g., pno)
Loans
lno lamt
Loan_Pmt Payments
pno pdate pamt
1.28
E/R Data ModelE/R Data ModelExistence Dependencies and Weak Entity SetsExistence Dependencies and Weak Entity Sets
Identifying Relationships
We say:
Loan is identifying owner in Loan_Pmt
Payment is weak entity set in Loan_Pmt
Payment is existence dependent on Loan
Loans
lno lamt
Loan_Pmt Payments
pno pdate pamt
1.29
E/R Data ModelE/R Data ModelExistence Dependencies and Weak Entity SetsExistence Dependencies and Weak Entity Sets
All elements of Payment appear in Loan_Pmt
Total Participation
Loans
lno lamt
Loan_Pmt Payments
pno pdate pamt
1.30
E/R Data ModelE/R Data ModelExistence Dependencies and Weak Entity SetsExistence Dependencies and Weak Entity Sets
E1
attam
E2
attb1 attbn
Q. Is {attb1, …, attbn} a superkey of E2?
... ...atta1
A: No
Q. Name a candidate key of E2
A: {atta1, attb1}
R
1.31
E/R Data ModelE/R Data ModelExtensions to the Model: Specialization and GeneralizationExtensions to the Model: Specialization and Generalization
An Example: Customers can have checking and savings accts
Checking ~ Savings (many of the same attributes)
Old Way:
Customers Has1 Savings Accts
acct_no balance interest
Has2 Checking Accts
acct_no balance overdraft
1.32
E/R Data ModelE/R Data ModelExtensions to the Model: Specialization and GeneralizationExtensions to the Model: Specialization and Generalization
Customers Has Accounts
acct_no balance
Checking Accts
overdraftinterest
Isa
Savings Accts
An Example: Customers can have checking and savings accts
Checking ~ Savings (many of the same attributes)
New Way:
superclass
subclasses
1.33
E/R Data ModelE/R Data ModelExtensions to the Model: Specialization and GeneralizationExtensions to the Model: Specialization and Generalization
Subclass Distinctions:
1. User-Defined vs. Condition-Defined
User: Membership in subclasses explicitly determined (e.g., Employee, Manager < Person)
Condition: Membership predicate associated with subclasses - e.g:
Person
Isa
Child Adult Senior
age < 18 age18 age65
1.34
E/R Data ModelE/R Data ModelExtensions to the Model: Specialization and GeneralizationExtensions to the Model: Specialization and Generalization
Subclass Distinctions:
2. Overlapping vs. Disjoint
Overlapping: Entities can belong to >1 entity set (e.g., Adult, Senior)
Disjoint: Entities belong to exactly 1 entity set
(e.g., Child)
Person
Isa
Child Adult Senior
age < 18 age18 age65
1.35
E/R Data ModelE/R Data ModelExtensions to the Model: Specialization and GeneralizationExtensions to the Model: Specialization and Generalization
Subclass Distinctions:
3. Total vs. Partial Membership
Total: Every entity of superclass belongs to a subclass e.g.,
Partial: Some entities of superclass do not belong to any
subclass (e.g., if Adults condition is age21 )
Person
Isa
Child Adult Senior
age < 18 age18 age65
1.36
E/R Data ModelE/R Data ModelExtensions to the Model: AggregationExtensions to the Model: Aggregation
E/R: No relationships between relationships
E.g.: Associate loan officers with Borrows relationship set
Customers LoansBorrows
Employees
Loan_Officer
?
Associate Loan Officer with Loan?
What if we want a loan officer for every (customer, loan) pair?
1.37
E/R Data ModelE/R Data ModelExtensions to the Model: AggregationExtensions to the Model: Aggregation
E/R: No relationships between relationships
E.g.: Associate loan officers with Borrows relationship set
Customers LoansBorrows
Employees
Loan_Officer
Associate Loan Officer with Borrows?
Must First Aggregate
1.38
E/R Data ModelE/R Data ModelSummarySummary
Entities, Relationships (sets)
Both can have attributes (simple, multivalued, derived, composite)
Cardinality or relationship sets (1:1, n:1, n:m)
Keys: superkeys, candidate keys, primary key
DBA chooses primary key for entity sets
Automatically determined for relationship sets
Weak Entity Sets, Existence Dependence, Total/Partial Participation
Specialization and Generalization (E/R + inheritance)
Aggregation (E/R + higher-order relationships)
1.39
These things get pretty hairy!These things get pretty hairy!
Many E-R diagrams cover entire walls!
A modest example:
1.40
A Cadastral E-R DiagramA Cadastral E-R Diagram
cadastral: showing or recording property boundaries, subdivision lines, buildings, and related details
Source: US Dept. Interior Bureau of Land Management,Federal Geographic Data Committee Cadastral Subcommittee
http://www.fairview-industries.com/standardmodule/cad-erd.htm