[SOLVED] CS INFO20003 Database Systems

INFO20003 Database Systems
Dr Renata Borovica-Gajic
Lecture 05
Modelling with MySQL Workbench

Copyright By Assignmentchef assignmentchef

INFO20003 Database Systems Week 3

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)
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 {}
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.
For employees we want to be able to show for how many years they have
been employed. SSN
Contract start
Years employed
Derived attribute (Chens notation)
INFO20003 Database Systems University of Melbourne 5

Conventions of ER Modelling (Workbench)
Relationship Degrees
Ternary with attributes
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
Identifying relationship
Weak Entity
Cant 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
CustomerF irstName
CustMiddle Name
CustLast Name
BusinessN ame

JJ Enterprises
AccountName
OutstandingB alance

JJ Ent. Mgr
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
SeeFK1CustomerID
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:
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 Crows 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);
1969-06-12
1971-05-09
1982-02-10
1991-01-01
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);
1969-06-12
1971-05-09
1982-02-10
1991-01-01
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)
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
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 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
INFO20003 Database Systems University of Melbourne 35

Conceptual Model Mapping (LMS)
Relationship cardinalities and constraints
Concept Chens not. Crows foot not.
INFO20003 Database Systems University of Melbourne 36

Whats Examinable
Need to be able to draw conceptual, logical and physical
Assignment 1: Conceptual Chens pen and paper, Physical
Crows 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

CS: assignmentchef QQ: 1823890830 Email: [email protected]