程序代写代做代考 database ER algorithm SQL Database Design 1: Conceptual

Database Design 1: Conceptual
Modelling

MONASH
INFORMATION
TECHNOLOGY

2

ANSI/SPARC architecture

View 1 View 2 View n

Conceptual
Schema

Internal
Schema

External level

Conceptual level

Internal level

3

The Database Design Life Cycle

Requirements
Definition

Conceptual
Design

Logical
Design

Physical
Design

4

Requirements Definition
▪ Identify and analyse user views.
▪ A ‘user view’ may be a report to be produced or a

particular type of transaction that should be
supported.
▪ Corresponds to the external level of the

ANSI/SPARC architecture.
▪ Output is a statement of specifications which

describes the user views’ particular requirements
and constraints.

5

Student view

6

Staff and Student View

7

Admin View

8

ER Modeling
▪ ER (Entity-Relationship) model developed by Peter

Chen in 1976 to aid database design.
▪ May be used for conceptual (ERD)/logical design

(ERD like).
▪ ER diagrams give a visual indication of the design.
▪ Basic components:

– Entity
– Attribute
– Relationship

9

Conceptual Design
▪ Develop the enterprise data model.
▪ Corresponds to the conceptual level of the

ANSI/SPARC architecture.
▪ Independent of all physical implementation

considerations.
▪ Various design methodologies may be employed,

including the ER (Entity-Relationship) approach.

10

Conceptual Level (ER Model) – Keys only

Ass 1 Part A

11

Conceptual Level (ER Model) – All Attributes

Ass 1 Part B

12

Logical Design
▪ Develop a data model which targets a particular

database model (e.g. relational, hierarchical,
network, object-oriented).
▪ Independent of any implementation details which are

specific to any particular DBMS package.
▪ Normalisation technique (see week 5) is used to test

the correctness of the logical model.
▪ May also be considered to correspond to the

conceptual level of the ANSI/SPARC architecture.

13

Logical Level (Logical Model)

Ass 1 Part B

14

Physical Design
▪ Develop a strategy for the physical implementation

of the logical data model.
▪ Choose appropriate storage structures, indexes, file

organisations and access methods which will most
efficiently support the user requirements.
▪ Physical design phase is dependent on the particular

DBMS environment in use.
▪ ANSI/SPARC internal level.
▪ Shown in SQL Developer Data Modeller as the

Relational Model

15

Physical Level – Starting point

ENTITY RELATIONSHIP
DIAGRAM

17

ERD – Notation

Chen

Information
Engineering/James
Martin/Crows foot

18

ERD – Notation cont’d
Chen’s Notation

▪ Semantically rich.
▪ Complex diagram.
▪ ‘Pure’ conceptual level.

Information Engineering

▪ Less semantics.
▪ Simpler diagram.
▪ Mix between conceptual

and logical levels.

Entity, Attributes and
Relationships

24

A B
supports

A B
supports

A B
supports

one to many

one to one

many to many

27

29

Weak vs Strong Entity
▪ Strong entity

– Has a key which may be defined without reference to other entities.
– For example EMPLOYEE entity.

▪ Weak entity
– Has a key which requires the existence of one or more other entities.
– For example FAMILY entity – need to include the key of employee to

create a suitable key for family
▪ Database designer often determines whether an entity can be described

as weak based on business rules
– customer pays monthly account

• Key: cust_no, date_paid, or
• Key: payment_no (surrogate? – not at conceptual level)

30

Weak vs Strong Entity

31

Identifying vs Non-Identifying Relationship
▪ Identifying

▪ Identifier of A is part of identifier
of B.

▪ Shown with solid line
▪ Enrolment’s PK includes student

id, which is an identifier of
student.

▪ Non-identifying

▪ Identifier of A is NOT part of
identifier of B.

▪ Shown with broken line
▪ Department no (identifier of

department) is not part of
Employee’s identifier.

A B
supports

A B
supports

32

Types of Attributes

▪ Simple
– Cannot be subdivided
– Age, sex, marital status

▪ Composite
– Can be subdivided into

additional attributes
– Address into street, city, zip

▪ Single-valued
– Can have only a single

value
– Person has one social

security number

▪ Multi-valued
– Can have many values
– Person may have several

college degrees
▪ Derived

– Can be derived with
algorithm

– Age can be derived from
date of birth

34

Multivalued Attribute
▪ An attribute that has a

list of values.
▪ For example:

– Car colour may
consist of body
colour, trim colour,
bumper colour.

▪ Crow’s foot notation
does not support
multivalued attributes.
Values are listed as a
separate attribute.

35

Resolving Multivalued Attributes

38

Associative (or Composite) Entity