INFO20003 Database Systems
Dr Renata Borovica-Gajic
Lecture 05
Modelling with MySQL Workbench
INFO20003 Database Systems Week 3
1
Coverage
• Modelling with MySQL Workbench
• Recap & further design
– Conceptual Design – Logical Design
– Physical Design
INFO20003 Database Systems © University of Melbourne 2
Conventions of ER Modelling (Workbench)
• Entity
• Identifier or key:
– Fully identifies an instance
• Partial Identifier:
– Identifies an instance in conjunction
with one or more partial identifiers
• Attributes types:
– Mandatory – NOT NULL (blue diamond) – Optional – NULL (empty diamond)
– Derived []
• [YearsEmployed]
– Multivalued {}
• {Skill}
– Composite ()
• Name (First, Middle, Last)
• Attributes
INFO20003 Database Systems © University of Melbourne 4
A note on derived attributes
• Derived attributes imply that their values can be derived from some other attributes in the database. As a result, they do not need to be stored physically – they disappear at the physical design.
Example:
For employees we want to be able to show for how many years they have
been employed. SSN
Name
Contract start
Years employed
Derived attribute (Chen’s notation)
Employee
INFO20003 Database Systems © University of Melbourne 5
Conventions of ER Modelling (Workbench)
• Relationship Degrees
Unary
Entity3
Ternary
Entity2
Entity3
Entity1
Binary
Ternary with attributes
Entity2
Entity1
● RelationshipAttribute1 ● RelationshipAttribute2
INFO20003 Database Systems © University of Melbourne 6
Conventions of ER Modelling (Workbench)
• 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
• Cardinality Constraints
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
INFO20003 Database Systems © University of Melbourne 7
Conventions of ER Modelling (Workbench)
Strong Entity:
• Can exist by itself
• E.g. Customer Card & Customer
o
Identifying relationship
Weak Entity
• Can’t exist without the owner • E.g. BillDetaiLine
INFO20003 Database Systems © University of Melbourne 8
Single Entity (Conceptual Model)
INFO20003 Database Systems © University of Melbourne 13
Convert from Conceptual to Logical design
• Convert the ER into a logical (rel.) model
– Customer(CustomerID, CustFirstName, CustMiddleName, CustLastName, BusinessName, CustType, CustAddLine1, CustAddLine2, CustSuburb, CustPostcode, CustCountry)
• Tasks checklist (from conceptual to logical):
1. Flatten composite and multi-valued attributes
• Multi-value attributes can become another table
2. Resolve many-many relationships
• Create an associative entity
3. Resolve one-many relationships
• Add foreign keys at crows foot end of relationships (on the many side in the case of crows foot)
INFO20003 Database Systems © University of Melbourne 14
Convert from Logical
•
Generate attribute data types (with NULL/NOT NULL)
Physical Design:
Implementation:
CREATE TABLE Customer(
CustomerID INT 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));
INFO20003 Database Systems © University of Melbourne 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
INFO20003 Database Systems © University of Melbourne 17
From Conceptual to Logical Design – Account
Conceptual Design:
Tasks checklist:
1. Flatten composite and multi-valued attributes X
2. Resolve many-many relationships X
3. Resolve one-many relationships
• SeeFK1–CustomerID
• Everyrowintheaccounttable must have a CustomerID from
Customer (referential integrity)
Logical Design:
Account(AccountID, AccountName, OutstandingBalance, CustomerID)
Note: Underline = PK, italic and underline = FK, underline and bold = PFK
INFO20003 Database Systems © University of Melbourne 18
Physical Design & Implementation- Account
Physical design: Implementation:
int
INFO20003 Database Systems © University of Melbourne 19
Dealing with Multi-Valued Attributes: Approach 2
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
StaffRole is an example of a weak entity
– We show this with a solid line in Workbench
INFO20003 Database Systems © University of Melbourne 20
Many to Many Relationship
• How do we deal with customer addresses?
– If customers can change addresses
• AND imagine that we need to store a history of addresses for customers.
– At the conceptual level it looks like this:
AddressDateFrom AddressDateTo
INFO20003 Database Systems © University of Melbourne 21
Many to Many – Logical design (Workbench)
• When converting the conceptual to logical diagram we create an Associative Entity between the other 2 entities
Note: AddressDateFrom/To are descriptive attributes of the relationship
They go into the associative entity for M-M
INFO20003 Database Systems © University of Melbourne 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
INFO20003 Database Systems © University of Melbourne 23
Many to Many – Physical Model & Implementation
INFO20003 Database Systems © University of Melbourne 24
Binary One-One Relationship
• Rule: Move the key from the one side to the other side DateAssigned
is in charge of
• 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
INFO20003 Database Systems © University of Melbourne 25
Binary One-One Relationship – Logical and Physical Design
• Logical Design:
– Nurse(NurseID, Name, DateOfBirth)
– CareCentre(CentreID, Location, NurseID, DateAssigned)
• Physical Design:
INFO20003 Database Systems © University of Melbourne 26
Summary of Binary Relationships From conceptual to logical
• One-to-Many
– Primary key on the one side becomes a foreign key on the many
side (in the case of Crow’s foot)
• 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
INFO20003 Database Systems © University of Melbourne 27
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
INFO20003 Database Systems © University of Melbourne 28
Unary: One-to-One
Conceptual Design:
Logical Design:
• Person (ID, Name, DateOfBirth, SpouseID)
Implementation:
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);
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
SpouseID
INFO20003 Database Systems © University of Melbourne 29
Unary: One-to-Many
•
Logical Design:
Employee (ID, Name, DateOfBirth, ManagerID)
Conceptual Design:
Implementation:
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);
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
INFO20003 Database Systems © University of Melbourne 30
Unary: Many-to-Many
• Logical Design:
– Create Associative Entity like usual – Generate logical model
• Item(ID, Name, UnitCost)
• Component(ID, ComponentID, Quantity)
Quantity
Item
contains
Item
INFO20003 Database Systems © University of Melbourne 31
Unary: Many-to-Many Implementation
• Implementation
INFO20003 Database Systems © University of Melbourne 32
Ternary relationships: Many to Many
• Relationships between three entities
• Logical Design:
– GenerateanAssociativeEntity
– ThreeOne-to-Manyrelationships
– Same rules then apply as One- to-Many
Supplies
Item
Warehouse
Supplier
● ShippingMode ● UnitCost
INFO20003 Database Systems © University of Melbourne 34
Strong and Weak Entity (Identifying Relationship)
• How to map an Identifying relationship
– Map it the same way: Foreign Key goes into the relationship at the crow’s 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
INFO20003 Database Systems © University of Melbourne 35
Conceptual Model Mapping (LMS)
Relationship cardinalities and constraints
Concept Chen’s not. Crow’s foot not.
INFO20003 Database Systems © University of Melbourne 36
What’s Examinable
• Need to be able to draw conceptual, logical and physical
diagrams
• Assignment 1: Conceptual Chen’s pen and paper, Physical
Crow’s foot with MySQL Workbench
• Create table SQL statements
INFO20003 Database Systems © University of Melbourne 38
Next Lecture
• Hands on Modelling
• Please read the case study prior to the lecture:
• LMS/Week 3 Medicare study
INFO20003 Database Systems © University of Melbourne 39