INFO20003 Database Systems
INFO20003 Database Systems 1
INFO20003 Database Systems
Lecture 05
Modelling with MySQL Workbench
Semester 2 2018, Week 3
Dr Renata Borovica-Gajic
INFO20003 Database Systems 2
Coverage
Modelling with MySQL Workbench
Recap & further design
Conceptual Design
Logical Design
Physical Design
University of Melbourne 2018
INFO20003 Database Systems 4
Conventions of ER Modelling (Workbench)
Entity
Attributes
Identifier or key:
Fully identifies an instance
Partial Identifier:
Identifies an instance in
conjunction with one or more
partial identifiers
Attributes types:
Mandatory (blue diamond)
Optional (empty diamond)
Derived []
[YearsEmployed]
Multivalued {}
{Skill}
Composite ()
Name (First, Middle, Last)
University of Melbourne 2018
INFO20003 Database Systems 5
Conventions of ER Modelling (Workbench)
Relationship Degrees
Unary
Ternary
Binary
Entity2 Entity1
Entity3
Ternary with
attributes
Entity2 Entity1
Entity3
RelationshipAttribute1
RelationshipAttribute2
University of Melbourne 2018
INFO20003 Database Systems 6
Cardinality Constraints Relationship Cardinality
One to One
Each entity will have exactly 0
or 1 related entity
One to Many
One of the entities will have 0,
1 or more related entities, the
other will have 0 or 1.
Many to Many
Each of the entities will have 0,
1 or more related entities
Optional Many
Partial participation
Without key constraint
Mandatory Many
Total participation
Without key constraint
Optional One
Partial participation
Key constraint
Mandatory One
Total participation
Key constraint
Conventions of ER Modelling (Workbench)
University of Melbourne 2018
INFO20003 Database Systems 7
Strong Entity:
Can exist by itself
E.g. Customer Card & Customer
Weak Entity
Cant exist without the owner
E.g. BillDetaiLine
Conventions of ER Modelling (Workbench)
o
University of Melbourne 2018
INFO20003 Database Systems 12
Single Entity (Conceptual)
University of Melbourne 2018
INFO20003 Database Systems 13
Convert from Conceptual to Logical
design (Single Entity)
Convert the ER into a logical (rel.) model
Customer=(CustomerID,
CustFirstName, CustMiddleName,
CustLastName, BusinessName,
CustType, CustAddLine1,
CustAddLine2, CustSuburb,
CustPostcode, CustCountry)
Tasks checklist:
1. Convert composite and multi-valued attributes
Multi-Attribute values can become another table
2. Resolve many-many relationships
3. Add foreign keys at crows foot end of relationships
(on the many side)
University of Melbourne 2018
INFO20003 Database Systems 15
Convert from Logical to Physical Design
Generate attribute data types
CREATE TABLE Customer(
CustomerID smallint NOT NULL,
CustFirstName VARCHAR(100),
CustMiddleName VARCHAR(100),
CustLastName VARCHAR(100) NOT NULL,
BussinessName VARCHAR(100),
CustType VARCHAR(1) NOT NULL,
CustAddressLine1 VARCHAR(100) NOT NULL,
CustAddressLine2 VARCHAR(100) NOT NULL,
CustSuburb VARCHAR(60) NOT NULL,
CustPostcode CHAR(6) NOT NULL,
CustCountry VARCHAR(60) NOT NULL,
PRIMARY KEY (CustomerID));
University of Melbourne 2018
Physical Design: Implementation:
INFO20003 Database Systems 16
More than One Entity
A customer can have a number of Accounts
The tables are linked through a foreign key
CustID CustomerF
irstName
CustMiddle
Name
CustLast
Name
BusinessN
ame
CustType
1 Peter Smith Personal
2 James Jones JJ
Enterprises
Company
AccountID AccountName OutstandingB
alance
CustID
01 Peter Smith 245.25 1
05 JJ Ent. 552.39 2
06 JJ Ent. Mgr 10.25 2
University of Melbourne 2018
INFO20003 Database Systems 17
From Conceptual to Logical Design
Account
Tasks checklist:
1. Convert composite and multi-valued
attributes
2. Resolve many-many relationships
3. Add foreign keys at crows foot end
of relationships
See FK1 CustomerID
Every row in the account table
must have a CustomerID from
Customer (referential integrity)
Conceptual Design: Logical Design:
Account=(AccountID,
AccountName,
OutstandingBalance,
CustomerID)
Note: Underline = PK,
italic and underline = FK,
underline and bold = PFK
University of Melbourne 2018
X
X
INFO20003 Database Systems 18
Physical Design & Implementation-
Account
University of Melbourne 2018
Implementation:Physical design:
INFO20003 Database Systems 19
Dealing with Multi-Valued Attributes
StaffRole is an example
of a weak entity
We show this with
a solid line in
Workbench
Conceptual Design: Logical Design: Physical Design:
If staff have only 2-3 roles
you may decide to have
these within the Employee
table at physical design to
save on JOIN time
University of Melbourne 2018
INFO20003 Database Systems 20
Many to Many Relationship
How to deal with customer addresses
The fact is that customers change addresses
AND we probably need to store a history of addresses for
customers.
At the conceptual level it looks like this:
AddressDateFrom
AddressDateTo
University of Melbourne 2018
INFO20003 Database Systems 21
When converting the conceptual to the logical diagram we
create an Associative Entity between the other 2 entities
Many to Many Logical design (Workbench)
University of Melbourne 2018
Note: AddressDateFrom/To are descriptive attributes of the relationship
They go into the associative entity for M-M
INFO20003 Database Systems 22
Many to Many Logical Model
Customer=(CustomerID, CustFirstName, CustMiddleName,
CustLastName, BusinessName, CustType)
Address=(AddressID, StreetNumber, StreetName,
StreetType, AddressType, AddressTypeIdentifier,
MinorMunicipality, MajorMunicipality, GoverningDisctrict,
Country, PostalArea)
Customer_Has_Address=(CustomerID, AddressID,
AddressDateFrom, AddressDateTo)
Note: Underline
= PK, italic and
underline = FK,
underline and
bold = PFK
University of Melbourne 2018
INFO20003 Database Systems 23
Many to Many Physical Model &
Implementation
University of Melbourne 2018
INFO20003 Database Systems 24
Rule: Move the key from the one side to the other side
But we have 2 one sides. Which one?
Need to decide whether to put the foreign key inside Nurse or CareCentre
(in which case you would have the Date_Assigned in the same location)
Where would the least NULL values be?
The rule is the OPTIONAL side of the relationship gets the foreign key
Binary One-One Relationship
DateAssigned
is in charge of
University of Melbourne 2018
INFO20003 Database Systems 25
Binary One-One Relationship Logical
and Physical Design
Logical
Nurse = (NurseID, Name, DateOfBirth)
CareCentre = (CentreID, Location, NurseID, DateAssigned)
Physical
University of Melbourne 2018
INFO20003 Database Systems 26
Summary of Binary Relationships
One-to-Many
Primary key on the one side becomes a foreign key on the many
side
Many-to-Many
Create an Associative Entity (a new relation) with the primary
keys of the two entities it relates to as the combined primary key
One-to-One
Need to decide where to put the foreign key
The primary key on the mandatory side becomes a foreign key
on the optional side
If two optional or two mandatory, pick one arbitrarily
University of Melbourne 2018
INFO20003 Database Systems 27
How to map an Identifying relationship
Map it the same way: Foreign Key goes into the relationship at
the crows foot end.
Only Difference is: The Foreign Key becomes part of the
Primary Key
Logical Design
Loan = (LoanID, Amount)
Payment = (PaymentNumber, LoanID, Date, Amount)
Physical Design as per normal one-to-many
Strong and Weak Entity- Identifying Relationship
University of Melbourne 2018
INFO20003 Database Systems 28
Unary Relationships
Operate in the same way as binary relationships
One-to-One
Put a Foreign key in the relation
One-to-Many
Put a Foreign key in the relation
Many-to-Many
Generate an Associative Entity
Put two Foreign keys in the Associative Entity
Need 2 different names for the Foreign keys
Both Foreign keys become the combined key of the Associative
Entity
University of Melbourne 2018
INFO20003 Database Systems 29
Unary: One-to-One
Person = (ID, Name, DateOfBirth,
SpouseID)
Conceptual Design:
Logical Design:
ID Name DOB SpouseID
1 Ann 1969-06-12 3
2 Fred 1971-05-09 NULL
3 Chon 1982-02-10 1
4 Nancy 1991-01-01 NULL
CREATE TABLE Person (
ID INT NOT NULL,
Name VARCHAR(100) NOT NULL,
DateOfBirth DATE NOT NULL,
SpouseID INT,
PRIMARY KEY (ID),
FOREIGN KEY (SpouseID)
REFERENCES Person (ID)
ON DELETE RESTRICT
ON UPDATE CASCADE);
SpouseID
Implementation:
University of Melbourne 2018
INFO20003 Database Systems 30
Unary: One-to-Many
Employee = (ID, Name,
DateOfBirth, ManagerID)
Conceptual Design: Implementation:
ID Name DOB MngrID
1 Ann 1969-06-12 NULL
2 Fred 1971-05-09 1
3 Chon 1982-02-10 1
4 Nancy 1991-01-01 1
Logical Design:
CREATE TABLE Employee(
ID smallint NOT NULL,
Name VARCHAR(100) NOT NULL,
DateOfBirth DATE NOT NULL,
ManagerID smallint ,
PRIMARY KEY (ID),
FOREIGN KEY (ManagerID)
REFERENCES Employee(ID)
ON DELETE RESTRICT
ON UPDATE CASCADE);
University of Melbourne 2018
INFO20003 Database Systems 31
Unary: Many-to-Many
Logical Design:
Create Associative Entity like usual
Generate logical model
Item = (ID, Name, UnitCost)
Component = (ID, ComponentID, Quantity)
ItemItem
Quantity
contains
University of Melbourne 2018
INFO20003 Database Systems 32
Unary: Many-to-Many Implementation
Implementation
University of Melbourne 2018
INFO20003 Database Systems 34
Ternary relationships
Relationships between three
entities
Logical Design:
Generate an Associative Entity
Three One-to-Many relationships
Same rules then apply as One-
to-Many
Warehouse
Supplies
Supplier
Item
ShippingMode
UnitCost
University of Melbourne 2018
INFO20003 Database Systems 35
Conceptual Model Mapping
Concept Chens not. Crows foot not. Relationship cardinalities and constraints
INFO20003 Database Systems 37
Whats Examinable
Need to be able to draw conceptual, logical and physical
diagrams
Assignment 1: Conceptual Chens pen and paper, Physical
Crows foot with Workbench
Create table SQL statements
University of Melbourne 2018
INFO20003 Database Systems 38
Next Lecture
Hands on Modelling
Please read the case study prior to the lecture:
LMS/Resources
University of Melbourne 2018
Reviews
There are no reviews yet.