程序代写代做代考 case study database ER SQL INFO20003 Database Systems

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

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)

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

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)

INFO20003 Database Systems 7

Strong Entity:

• Can exist by itself

• E.g. Customer Card & Customer

Weak Entity

• Can’t exist without the owner

• E.g. BillDetaiLine

Conventions of ER Modelling (Workbench)

INFO20003 Database Systems 12

Single Entity (Conceptual)

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)

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));

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

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

X
X

INFO20003 Database Systems 18

Physical Design & Implementation-

Account

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

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

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)

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

INFO20003 Database Systems 23

Many to Many – Physical Model &

Implementation

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

INFO20003 Database Systems 25

Binary One-One Relationship – Logical

and Physical Design

• Logical

– Nurse = (NurseID, Name, DateOfBirth)

– CareCentre = (CentreID, Location, NurseID, DateAssigned)

• Physical

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

INFO20003 Database Systems 27

• 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

Strong and Weak Entity- Identifying Relationship

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

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:

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);

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

INFO20003 Database Systems 32

Unary: Many-to-Many Implementation

• Implementation

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

INFO20003 Database Systems 35

Conceptual Model Mapping

Concept Chen’s not. Crow’s foot not. Relationship cardinalities and constraints

INFO20003 Database Systems 37

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 Workbench

• Create table SQL statements

INFO20003 Database Systems 38

Next Lecture

• Hands on Modelling

• Please read the case study prior to the lecture:

• LMS/Resources

Related Posts