Databases
Lecture 7 – Logical Design
Bernhard Reus
1
Logical Design for a Relational Data Model
Are they relational?
Does he do logical?
Conceptual Design provides an E/R diagram with comments.
For a RDBMS we need a relational model.
In this lecture:
• Howtomap an E/R
diagram into a Relational Database Schema
6
[C&B, Ch.16]
©Bernhard Reus, University of Sussex, 2004–16
From Conceptual to Logical
• Logical Design is the process of modelling enterprise data based on a specific kind of data model (e.g. relational) independent of a particular DBMS.
• For RDBMS we use the relational model and produce relational schemas.
• Conceptual Schema (E/R diagram) is converted into a Relational Database Schema (see Lec.6).
• The following slides show how this is done.
7
The recipe
= an algorithm
8
©Bernhard Reus, University of Sussex, 2004–16
Entity Types
• Each entity type becomes a schema…
• … where schema attributes are simple attributes.
• Composite attributes must therefore be decomposed (recursively).
• A primary key is chosen from the candidate keys.
Multiple-valued attributes discussed later
9
street
town postcode
Example
lname
address
Staff staffNo
Staff(staffNo,lname,street,town,postcode)
10
©Bernhard Reus, University of Sussex, 2004–16
Weak Entity Types
• Include all attributes together with the primary key attributes of the parent entity type as foreign keys.
• Choose as primary key
The foreign key attributes = primary key attributes of
the parent entity type…
… plus the partial key of the weak entity type
11
Example
name
maxRent Preference rank
Client 1 States M clientNo
location
Preference(clientNo,rank,maxRent,location) primary key (clientNo,rank)
foreign key clientNo references Client(clientNo)
12
Foreign Key !
©Bernhard Reus, University of Sussex, 2004–16
1:M (1:1) Relationship Types
• Convert the participating entity types as shown.
• The entity type on the 1-side is called the parent.
• The entity type on the M-side is called the child.
• Add to the attribute list of the child entity type the primary key attributes of the parent type as a foreign key and all relationship attributes.
• In the 1:1 case choose as child an entity type with total participation (if possible).
13
Example
address
1 Employs M Staff
111 Managed_by
Foreign key for Employs
branchNo
Branch
staffNo lname
Staff(staffNo,lname,branchNo) Branch(branchNo,address,staffNo)
Foreign key for
Managed_by
14
©Bernhard Reus, University of Sussex, 2004–16
M:N Relationships
• First translate participating entity types.
• Create a new relation schema for the relship.
• Include all relationship attributes.
• Include as foreign keys the primary key attributes of both (translated) participating entity types.
• Choose as primary key all the foreign keys included in the previous step.
15
name Client clientNo
Example
MN
Views
viewDate
propNo Property
address
Views(clientNo,propNo,viewDate)
Foreign Key
Foreign Key
16
©Bernhard Reus, University of Sussex, 2004–16
1:N (1:1) Revisited
• Ifonlyfewentitiesareintherelationshiponecan
reduce null entries representing the relationship …
• Cross-referenceoption:
… as a new table with two foreign keys (as new primary key) corresponding to the two primary keys of the (translated) participating entity types (see M:N).
• Mergedrelationoption(1:1withbothparticipations total): … as a single relation, into which both entity types and the relationship have been merged.
17
Example: Cross Reference
Branch 1 Managed_by 1 Staff
Branch(branchNo,address,staffNo) Staff(staffNo,address,salary,jobtitle)
Branch(branchNo,address) Managed_By(branchNo,staffNo) Staff(staffNo,address,salary,jobtitle)
18
©Bernhard Reus, University of Sussex, 2004–16
Example: Merged Relation
Branch 1 Managed by 1 Manager
Total participation on both sides needed
Total participation on both sides needed
Branch(branchNo,address,managerId) Manager(managerId,mgName,mgAddress, salary)
Branch(branchNo,address,mgName,mgAddress,salary)
19
N-ary Relationship Types
• Createanewrelationschema.
• Includeallrelationshipattributes.
• Includeasforeignkeystheprimarykeyattributesofall
(translated) participating entity types.
• Chooseasprimarykeythoseforeignkeysincludedinthe previous step that represent a many participation and all but one of the foreign keys that represent a one participation.
If there are k>1 foreign keys that represent cardinality one we get k-1 additional candidate keys (as we can choose
which to use).
20
©Bernhard Reus, University of Sussex, 2004–16
Example (ternary relationship)
lname
startDate
branchNo
staffNo Staff
1
Registers
1
address Branch
Alternatively, we could choose (clientNo,branchNo) as primary key and (clientNo,staffNo) as candidate key
21
M Registers(clientNo,staffNo,branchNo,startDate)
Additional candidate key for schema Registers: (clientNo,branchNo)
name
Client
clientNo
Recursive Relationships • Just like any other relationship
• 1:M
foreign key attribute needs to have a name different from the the primary key attribute. Use role name!
• M:N
foreign key reference used twice so need to use different names for those. Use roles name!
22
©Bernhard Reus, University of Sussex, 2004–16
Example Recursive 1:M Relationship
Supervisor 1
Supervises
Staff
lname
M
Supervisee
staffNo
Foreign key for Supervises; Can’t use staffNo twice.
23
Staff(staffNo,lname,supervisor)
foreign key supervisor references Staff(staffno)
Multi-valued Attribute
• Createanewrelationschema.
• Includethemulti-valuedattribute(eliminatecomposite ones).
• Includeasforeignkeytheprimarykeyofthe(translated) entity type it belongs to.
• Chooseasprimarykeyofthenewrelationthecomposite of both attributes.
• Theschemaoftheentitytypethemulti-valuedattribute belongs to does not refer to the new schema.
24
©Bernhard Reus, University of Sussex, 2004–16
Example
address phoneNumber Staff
staffNo
PhoneNumbers(phoneNumber,staffNo) foreign key staffNo references Staff(staffNo) Staff(staffNo,address)
25
Specialisation/Generalisation
• Translation depends on the constraints of subclass/superclass relationship :
– Mandatory: merge parent and child entity type
– Optional: relate child entity type to parent entity type
using foreign key
– And: merge child entity types and distinguish using an extra discriminator attribute
– Or: keep child entity types separate
26
©Bernhard Reus, University of Sussex, 2004–16
Specification/Generalisation
• Translation depends on the constraints of subclass/superclass relationship :
constraints
translation
{Mandatory,And}
Merge all parent /child entity types into one schema, adding a discriminator attribute to distinguish the different types
{Optional,And}
Create two schemas, one for the superclass entity type and one for all the subclass entity types, merging their attributes, adding a discriminator attribute to distinguish the different types, and making its primary key a foreign key referencing the (translated) superclass;
{Mandatory,Or}
Create a schema for each pair of parent&child entity type merging their attributes
{Optional,Or}
Create a schema for parent entity type and each child entity type. Primary key of children is foreign key referencing parent.
27
Example Specialisation
Branch 1 Employs M Staff
1
staffNo
lname
{Mandatory,And}
Managed_by
1
Manager Managed
skills startDate
Staff(staffNo,lname,branchNo,discrim,skills,startDate)
Discrim’s value is a tag: either manager or managed or manager_and_managed
Do the translation for the other 3 possible
constraint combinations from previous slide. 28
©Bernhard Reus, University of Sussex, 2004–16
Impact translating or constraint staffNo
lname
{Mandatory,Or}
Branch 1 Employs M Staff
1
1
Managed_by
Manager
skills
Managed
startDate
29
StaffManager(staffNo,lname,branchNo,skills)
StaffManaged(staffNo,lname,branchNo,startDate)
Branch(branchNo,…,managerNo)
foreign key managerNo StaffManager(staffNo)
Derived Attributes
• Do not need to be stored (but could and be updated
automatically)
• Trade-off between
– time re-computing their values and – space storing them in the database.
• Logical Model: do not include them in the Schema but make a note about the derived attribute.
30
©Bernhard Reus, University of Sussex, 2004–16
Logical Schema: Integrity • The Relational Database Schema generated includes:
– Entity Constraints (on the keys):
Primary keys only contain non-null values.
Additional not null constraints can be added where needed
– Referential Constraints:
• Any value of a Foreign Key must be matched by a value in the
referenced column(s) or be null.
• values of Foreign Keys representing a total participation in a relationship must not be null.
– Domain & Enterprise constraints:
according to the semantics of the attributes.
31
Logical Schema Document
branchNo
Branch 1 Employs M Staff
address lname staffNo 11
1
Staff(staffNo,lname,branchNo)
primary key staffNo
foreign key branchNo references Branch(branchNo) branchNo not null
Branch(branchNo,address,managerId)
primary key branchNo
foreign key managerId references Staff(staffNo) managerId not null
Managed_by
32
©Bernhard Reus, University of Sussex, 2004–16
Logical Schema Document as Diagram
Staff(staffNo,lname,branchNo)
primary key staffNo
foreign key branchNo references Branch(branchNo) branchNo not null
Branch(branchNo,address,managerId)
primary key branchNo
foreign key managerId references Staff(staffNo) managerId not null
33
Staff
PK
FK
staffNo
lname
branchNo NOT NULL
Branch
PK
FK
branchNo
address
managerId NOT NULL
Some Observations
• Relationshiptypesarenotmodelledexplicitlybutby
using foreign keys.
• Entityandrelationshiptypesaremodelledasrelations (tables).
• Totalparticipationingeneralisnotreflectedinthe translation. On the many-side (1:M) it can be enforced by adding a constraint not null on foreign key.
• Somecardinalityconstraintsonn-aryrelationshipsare expressed via additional candidate keys.
• Somedatabasedesigntoolscancreatearelational database schema from an E/R diagram automatically.
34
©Bernhard Reus, University of Sussex, 2004–16