Database Fundamentals
Database Fundamentals
UML Translation
1
UML Translation Process
UML can often be translated automatically into relational schemas
MS-SQL Diagrams feature
DBDesigner Fork
ArgoUML
The next lot of slides will show how this can be done manually following some basic rules
2
UML Translation Process
Classes
Every UML Class becomes its own relation of the same name
The Primary Key (PK) becomes the relations PK
3
Student
StudentID {PK}
EmailID {CK}
StudentName
Course
CourseID {PK}
CourseName
Enrols in
1..*
0..*
Relational Schema1:
Student(StudentID, EmailID, StudentName)
PK = (StudentID)
CK = (EmailID)
Relational Schema2:
Course(CourseID, CourseName)
PK = (CourseID)
UML Translation Process
Associations
One : One Relationship (mandatory)
Associations can be represented by a new relation that contains the PK from both sides of the association
4
Class1
Attribute 1 {PK}
Attribute 2
Class2
Attribute 1 {PK}
Attribute 2
1
1
Relational Schema:
Class1(Attribute1, Attribute2)
PK(Attribute1)
Class2(Attribute1, Attribute2)
PK(Attribute1)
Description
New Relation Schema:
Description(Class1Attr1, Class2Attr1)
PK (Class1Attr1, Class2Attr2)
FK(Class1Attr1) ~> Class1(Attribute1)
FK(Class2Attr1) ~> Class2(Attribute1)
Description
Class1 PK Class 2 PK
NOT ALWAYS Necessary
UML Translation Process
Associations
One : One Relationship (mandatory)
5
Class1
Attribute 1 {PK}
Attribute 2
Class2Attr1 (FK)
Class2
Attribute 1 {PK}
Attribute 2
1
1
Relational Schema:
Class1(Attribute1, Attribute2, Class2Attr1)
PK=(Attribute1)
FK(Class2Attr1) Class2(Attribute1)
Class2(Attribute1, Attribute2)
Description
Alternatively, store the PK of one of the classes as a FK in the other. Choose it such that empty values are avoided if the relationship is optional
UML Translation Process
Associations
One : One Relationship (optional)
Customers
acctNo (pk)
customerName
StoreCards
cardNo (pk)
level
Has →
Note: this is from the perspective of a single retailer
1
0..1
acctNo custName
001 Bruce
002 Wayne
003 Mitch
004 Melissa
cardNo level
A1 1
A2 1
A3 4
Schema Option1:
Schema Option2:
6
CustomerStoreCard
001 NULL
002 A1
003 A2
004 A3
Customers
StoreCards
6
UML Translation Process
Associations
One : One Relationship (optional)
Customers
acctNo (pk)
customerName
StoreCards
cardNo (pk)
level
Has →
Note: this is from the perspective of a single retailer
CustomerStoreCard
001 NULL
002 A1
003 A2
004 A3
1
0..1
Same PK
Schema Option1:
Schema Option2:
7
Customers
StoreCards
acctNo custName
001 Bruce
002 Wayne
003 Mitch
004 Melissa
cardNo level
A1 1
A2 1
A3 4
Same PK
Option 1: Store AcctNo in the StoreCards table
Option 2: store CardNo and Level in the customer table and ditch the StoreCards table.
Option 3: Store the CardNo value in the Customer relation and keep the StoreCards table.
7
UML Translation Process
Associations
One : One Relationship (optional)
Customers
acctNo (pk)
customerName
StoreCards
cardNo (pk)
level
Has →
Note: this is from the perspective of a single retailer
CustomerStoreCard
001 NULL
002 A1
003 A2
004 A3
1
0..1
Schema Option1:
Schema Option2:
8
Customers
StoreCards
acctNo custName
001 Bruce
002 Wayne
003 Mitch
004 Melissa
cardNo level acctNo
A1 1 002
A2 1 003
A3 4 004
Same PK
Option 1: Store AcctNo in the StoreCards table
Option 2: store CardNo and Level in the customer table and ditch the StoreCards table.
Option 3: Store the CardNo value in the Customer relation and keep the StoreCards table.
8
UML Translation Process
Associations
One : One Relationship (optional)
People
personID (pk)
firstName
lastName
dateOfBirth
AustralianPassport
passportNo (PK)
dateIssued
dateExpires
1.. 1
0..1
Has →
Note: this is from the perspective of Australian citizens holding standard Australian Passports
Schema Option1:
Schema Option2:
PK?
9
Option 1: Store FirstName, LastName AND DOB in the Aust Passport table.
Option2(BAD): Store PassportNo in the People table
9
UML Translation Process
Rule 1
In a optional 0..1 : 0..1 relationship or a mandatory 1:1 relationship, copy the key of ONE side and store it in the OTHER as a FOREIGN KEY
There is no need to create a new relation. Minimise storage of empty values.
Logical Schemas
Person(PersonID, …,RingID)
PK(PersonID)
FK(RingID) ~> Ring (RingID)
NoseRing(RingID, …)
PK(RingID)
Person
PersonID (CK)
…
NoseRing
RingID (CK)
…
0..1
0..1
10
?
Person(PersonID)
PK(PersonID)
NoseRing(RingID, …, PersonID)
PK(RingID)
FK(PersonID) ~> Person(PersonID)
Person
PersonID (CK)
…
NoseRing
RingID (CK)
…
0..1
0..1
UML Translation Process
Rule 1
In a optional 0..1 : 0..1 relationship or a mandatory 1:1 relationship, copy the key of ONE side and store it in the OTHER as a FOREIGN KEY
There is no need to create a new relation. Minimise storage of empty values.
Person
PersonID (CK)
…
Heart
HeartID (CK)
…
1
1
Person
PersonID (CK)
…
StoreCard
CardNo (CK)
…
1
0..1
Logical Schemas
Person(PersonID, …)
PK(PersonID)
StoreCard(CardNo, …, PersonID)
PK(CardNo)
FK(PersonID) ~> Person (PersonID)
Person(PersonID, …, HeartID)
PK(PersonID)
FK(HeartID) ~> Heart(HeartID)
Heart(HeartID, …)
PK(HeartID)
11
11
UML Translation Process
Associations
One : Many Relationships
Does not require an additional relation
Instead take the PK from the one side and apply it to the many side as a FOREIGN KEY.
12
Class1
Attr 1 {PK}
Attr 2
Class2
Attr1 {PK}
Attr 2
1
1..*
Relational Schema:
Class1(Attr1, Attr2)
PK=(Attribute1)
Class2(Class2Attr1, Class2Attr2, Class1Attr1)
PK=(Class2Attr1)
FK(Class1Attr1) ~> Class1(Attr1)
Description
UML Data Modelling – Basic Concepts
Multiplicity
One-to-Many (1:m) and Many-to-One (m:1)
Many elements of one object are related to at most one of the other object
0..1 1..*
Nerds
NerdID (PK)
FirstName
LastName
DateOfBirth
CanDoVulcanSalute
1.. 1
1..*
Collects →
StarTrekToys
ToyID (PK)
Character
BoxOpened
Condition
Nerd
01
02
03
StarTrekToys
T1 Spock
T2 Captain Picard
T3 Spock
T4 Data
NerdyToys
01 T1 (Spock)
01 T4 (Data)
02 T2 (Captain Picard)
03 T3 (Spock)
NerdyToys
13
13
UML Data Modelling – Basic Concepts
Multiplicity
One-to-Many (1:m) and Many-to-One (m:1)
Many elements of one object are related to at most one of the other object
0..1 1..*
Nerds
NerdID (PK)
FirstName
LastName
DateOfBirth
CanDoVulcanSalute
1.. 1
1..*
Collects →
StarTrekToys
ToyID (PK)
Character
BoxOpened
Condition
Nerd
01
02
03
StarTrekToys
T1 Spock
T2 Captain Picard
T3 Spock
T4 Data
NerdyToys
01 T1 (Spock)
01 T4 (Data)
02 T2 (Captain Picard)
03 T3 (Spock)
If we have 0..1 or 1..1 on One side of the relation, then the other side will be the key
Same Primary
Key
14
Duplicate
UML Translation Process
Rule 2
If we have 0..1 or 1..1 one on only one side of the relationship, then the other side is the key of the new relation
This means the new relation is not necessary, instead, create a copy of the key from the one side and store it in the other relation as a FOREIGN KEY:
Person
PersonID (CK)
…
StoreCard
CardNo (CK)
…
1
0..1
Person
PersonID (CK)
…
SwearJar
JarID (CK)
…
1
*
Person
PersonID (CK)
…
Puppy
PuppyID (CK)
…
0..1
*
Logical Schemas
Person(PersonID, …)
PK(PersonID)
StoreCard(CardNo, …, PersonID)
PK(CardNo)
FK(PersonID) ~> Person (PersonID)
Person(PersonID, …)
PK(PersonID)
SwearJar(JarID, …, PersonID)
PK(JarID)
FK(PersonID) ~> Person (PersonID)
Person(PersonID, …)
PK(PersonID)
Puppy(PuppyID, …, PersonID)
PK(PuppyID)
FK(PersonID) ~> Person (PersonID)
15
UML Translation Process
Associations
many: many Relationship
Results in a new relation that contains the key of both classes as the PK
16
Class1
Attr 1 {PK}
Attr 2
Class2
Attr 1 {PK}
Attr 2
1..*
1..*
Relational Schema:
Class1(Attr1, Attr2)
Class2(Attr1, Attr2)
Description(Class1Attr1, Class2Attr1)
Description
UML Translation Process
Association Classes
Results is a new relation containing the PK from both sides of the association
Any Association Class attributes are added to the new relation
17
Student
StudentID {PK}
EmailID {CK}
StudentName
Course
CourseID {PK}
CourseName
Enrols in
1..*
0..*
Relational Schema:
Enrolment(StudentID, CourseID, DateCommenced, Mark)
PK(StudentID, CourseID)
FK(StudentID) ~> Student(StudentID)
FK(CourseID) ~> Course(CourseID)
Enrolment
DateCommenced
Mark
UML Data Modelling – Basic Concepts
Association Classes vs 1 : many and many :1
18
Student
StudentID {PK}
EmailID {CK}
StudentName
Course
CourseID {PK}
CourseName
Enrolment
DateCommenced
Mark
Enrols in
1..*
1..*
Student
StudentID {PK}
EmailID {CK}
StudentName
Course
CourseID {PK}
CourseName
Enrolment
DateCommenced
Mark
1
*
*
1
Captures that there WAS at least ONE an interaction
Captures EVERY interaction
Enrolment(StudentID, CourseID, DateCommenced, Mark)
PK(StudentID, CourseID)
FK(StudentID) ~> Student(StudentID)
FK(CourseID) ~> Course(CourseID)
Enrolment(StudentID, CourseID, DateCommenced, Mark)
PK(StudentID, CourseID, DateCommencd)
FK(StudentID) ~> Student(StudentID)
FK(CourseID) ~> Course(CourseID)
UMLET Tool ONLY
UMLET, DBDesingerFork, Management Studio
UML Translation Process
Recursive/Self Associations
A Class that associates with itself
Results in a relation that contains the key of both classes as the PK
Any association class attributes get added to the new relation
19
Employee
ID {PK}
Name
1..*
Relational Schema:
Employee(ID, Name)
PK = ID
0..1
Supervisor
Supervisee
Supervise
Relational Schema:
Employee(ID, Name, SupervisorID)
PK = ID
FK(SupervisorID) ~> Employee(ID)
Or you can place the EmployeeID and SupervisorID into their own relation
19
UML Translation Process
Recursive/Self Associations
A Class that associates with itself
Results in a relation that contains the key of both classes as the PK
Any association class attributes get added to the new relation
20
Employee
ID {PK}
Name
1..*
Relational Schema:
Employee(ID, Name)
PK = ID
1..*
Supervisor
Supervisee
Supervise
Relational Schema:
Employee(ID, Name)
PK = ID
Supervision(SupervisorID, SuperviseeID)
PK = (SupervisorID, SuperviseeID)
FK (SupervisorID) ~> Employee(ID)
FK (SuperviseeID) ~> Employee(ID)
UML Translation Process
Classes – Structured (multi-value) Domains
An attribute that consists of sub-components
Violates basic relational theory (single value attributes)
21
Student
StudentID {PK}
EmailID {CK}
StudentName
Address
Street
Suburb
Postcode
Approach 1:
Student(StudentID, EmailID, StudentName, Address:String)
PK = (StudentID)
Approach 2:
Student(StudentID, EmailID, StudentName, Street, Suburb, Postcode)
PK = (StudentID)
Approach 3:
Addresses(AddressID, Street, Suburb, Postcode)
PK = (AddressID)
Student(StudentID, EmailID, StudentName, AddressID)
PK = (StudentID)
FK(AddressID) ~> Address(AddressID)
1NF ??
Use Approach 2 If you can guarantee every student will have at MOST one address
Approach 3 would be the preferred since the addresses can change and be re-used.
21
UML Translation Process
Inheritance/Sub Classes – Vertical Inheritance
Each class is translated into its own relation with the PK of the parent
The PK of each relation is the PK of the parent class
Suitable for Disjoint (OR) where every person is either one of a staff member, student or contractor
22
Relational Schema Variation 1:
Person(PersonID, PersonName)
Staff(PersonID, Position)
Student(PersonID, GPA)
Contractor(PersonID)
In each new relation FK(PersonID) ~> Person(PersonID)
22
UML Translation Process
Inheritance/Sub Classes – Horizontal Inheritance
Each class is translated into its own relation with all attributes of the parent
The PK of each relation is the PK of the parent class
Ok for Mandatory Disjoint Participation where Every person is either a student or a staff member
Saves re-combining records across tables
23
Relational Schema Variation 2:
Person(PersonID, PersonName)
Staff(PersonID, PersonName, Position)
Student(PersonID, PersonName, GPA)
Contractor(PersonID, PersonName)
23
UML Translation Process
Inheritance/Sub Classes – Horizontal Inheritance Variation
Create a complex relation consisting of all attributes
The PK of each relation is the PK of the parent class
Suitable for Non-Disjoint (AND) – overlapping relationships
Every person is represented by at least two of the sub-classes
24
Relational Schema Variation 3:
Person(PersonID, PersonName, Position, GPA, category)
What about our contractors???
24
Inheritance in One Relation – Poor solution
© Pearson Education Limited 1995, 2005
Using one table (horizontal inheritance) can introduce too many empty (NULL) values.
25
25
UML Translation Process
What about 1:many with an association class. . .
Pretty rare but possible!
What would be the best PK of the association class ?
26
Customers
custID (pk)
custName
Addresses
addID (pk)
address
Has →
1
*
custID custName
001 Bruce
002 Wayne
003 Mitch
004 Melissa
addID address
A001 …
A002 …
A003 …
custID addID dateFrom
001 A001 1/1/2000
002 A002 1/1/2005
002 A003 1/1/2015
Customers
Addresses
CustomerAddresses
dateFrom
Customers(acctNo, customerName)
StoreCards(cardNo, level, acctNo)
/docProps/thumbnail.jpeg