[SOLVED] case study database ER SQL INFO20003 Database Systems

$25

File Name: case_study_database_ER_SQL_INFO20003_Database_Systems.zip
File Size: 499.26 KB

5/5 - (1 vote)

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.

Only logged in customers who have purchased this product may leave a review.

Shopping Cart
[SOLVED] case study database ER SQL INFO20003 Database Systems
$25