Progettazione Logica

1
Progettazione Logica

• Giorgio Ghelli

2
The relational data model

• All data in flat tables of rows with a primary
  key (access operators later)
• Associations as foreign keys

Employees Employees Employees Employees
EmpId Name Salary DepId
232 John 1000 Y1
143 Mary 1200 X2
254 Joan 900 Y1
Departments Departments
DepId Budget
Y1 100000
X2 750000
3
The relational data model graphic notation
Employees
EmpId
DepId
Name
Salary
Departments
DepId
Budget
Employees
Departments
?
1
Employees
Departments
4
Phases of database design

• Conceptual design
• Logical design
• Physical design

5
Translating a conceptual plan to the relational
model

• Add an artificial primary key to any collection
  which needs one
• Translate associations and inclusions into
  foreign keys
• Flatten complex attributes
• Translate multivalued attributes into tables

6
Translating a conceptual plan

• 1-N associations become foreign keys

departments
departments
employees
employees
7
M-N associations

• Encoded as a table with two foreign keys

Employees Employees Employees
EmpId Name ...
232 John ...
143 Mary ...
254 Joan ...
Projects Projects
Budget PrId
23000 P1
32000 P2
ProEmp ProEmp
PId EId
P1 232
P1 143
P2 232
P2 254
8
M-N associations

• M-N associations become tables
• They get association attributes

projects
projects
ProEmp
Percentage
Percentage
employees
employees
9
Subcollections

• Conceptual
• Persons(PId, Name)
• Employees ext Persons(Dept, Salary)
• Consultants ext Persons(Project, Fee)
• Logical ?

10
Subcollections

• Conceptual
• Persons(PId, Name)
• Employees ext Persons(Dept, Salary)
• Consultants ext Persons(Project, Fee)
• Logical
• Persons(PId, Name)
• Employees(Dept, Salary, PId)
• Consultants(Project, Fee, PId)
• Persons(PId, Name, Kind, Dept, Salary, Project,
  Fee)

11
Subcollections
pId
persons
name
code
pId
persons
dept
employees
name
salary
dept
employees
salary
code
persons
name
kind
dept
salary
12
All together, now
father-of
persons
families
inhabits
tax-payers
flats
percentage