Progettazione Logica Giorgio Ghelli Logical modeling The relational

Progettazione Logica Giorgio Ghelli Logical modeling

The relational data model • All data in flat tables of rows with a primary key (access operators: later) • Associations as foreign keys Employees Emp. Id Name Salary Dep. Id 232 John 1000 Y 1 Departments Dep. Id Budget Y 1 100000 143 Mary 1200 X 2 254 Joan Y 1 Logical modeling 900 750000 2

The relational data model: graphic notation Employees Departments Emp. Id Dep. Id Name Salary Dep. Id Budget Employees Logical modeling Departments 1 Departments 3

Phases of database design • Conceptual design • Logical design • Physical design Logical modeling 4

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 Logical modeling 5

Translating a conceptual plan • 1 -N associations become foreign keys departments employees Logical modeling 6

M-N associations • Encoded as a table with two foreign keys Projects Budget Pr. Id 23000 P 1 32000 P 2 Logical modeling Pro. Emp PId EId P 1 232 P 1 143 P 2 232 P 2 254 Employees Emp. Id Name. . . 232 John. . . 143 Mary. . . 254 Joan . . . 7

M-N associations • M-N associations become tables • They get association attributes projects Percentage employees Logical modeling projects Percentage Pro. Emp employees 8

Subcollections • Conceptual: – Persons(PId, Name) • Employees ext Persons(Dept, Salary) • Consultants ext Persons(Project, Fee) • Logical: ? Logical modeling 9

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) Logical modeling 10

Subcollections persons p. Id name employees dept salary Logical modeling p. Id name code employees dept salary persons code name kind dept salary 11

All together, now father-of persons families inhabits tax-payers flats percentage Logical modeling 12