DESIGNING DATABASE ITDA1001

Write My Assignment FREE

ITDA1001 [Lesson 2] Copyright © 2018 VIT, All Rights Reserved 1
Copyright © 2015 – 2018, Victorian Institute of Technology.
The contents contained in this document may not be reproduced in any form or by any means, without the written permission of VIT,
other than for the purpose for which it has been supplied. VIT and its logo are trademarks of Victorian Institute of Technology.
DESIGNING DATABASE
ITDA1001 [Lesson 2] Copyright © 2018 VIT, All Rights Reserved 2
Topics
• Models
• What are Models used for?
• Database and Models
• Data Models
• Categories of Data Models
• Conceptual data models
• Representational (or implementation) data models
• Low-level or physical data models
• Entity Relationship Diagrams
• Entity
• Attribute
ITDA1001 [Lesson 2] Copyright © 2018 VIT, All Rights Reserved 3
Topics (Cont…)
• Types of Attributes
• Weak Entity
• Relationship
• Degree
• Degree (Unary) – One Entity
• Degree (Binary) – Two Entity
• Degree (Ternary & n-ary)
• Cardinality
• Relationships
• Relational Model
• Mapping E-R and Relational
ITDA1001 [Lesson 2] Copyright © 2018 VIT, All Rights Reserved 4
Topics (Cont…)
• Mapping – Relationships
• Components – Summary
• Notations
ITDA1001 [Lesson 2] Copyright © 2018 VIT, All Rights Reserved 5
Models
• A model in general is an ‘abstract’ of reality.
• What are some examples of models?
• These can be created using cardboard or CAD/CAM
software or clay models.
• We can build model things which exist nor or does not exist
yet.
Scale model of a building Scale model of a plane
ITDA1001 [Lesson 2] Copyright © 2018 VIT, All Rights Reserved 6
Models (Cont…)
• Models can be expressed in different ways using different
tools.
• Used to show same thing in different ways
• Show different levels of detail
• Target different audiences
• Remove irrelevant detail.
• Example
– scale clay model of a car showing how it would look
– blueprints of the car, showing all measurements etc.
– computer simulation of the car in working order
ITDA1001 [Lesson 2] Copyright © 2018 VIT, All Rights Reserved 7
What are Models used for?
• The general purpose for models are:
– getting a better “overall” view of a real life application;
– communication between same level;
– communication to different levels.
• In databases, we can model an actual database and/or
reality.
ITDA1001 [Lesson 2] Copyright © 2018 VIT, All Rights Reserved 8
Database and Models
• One fundamental characteristic of the database approach is
that it provides some level of data abstraction. Data
abstraction generally refers to the suppression of details of
data organization and storage, and the highlighting of the
essential features for an improved understanding of data.
• One of the main characteristics of the database approach is
to support data abstraction so that different users can
perceive data at their preferred level of detail.
ITDA1001 [Lesson 2] Copyright © 2018 VIT, All Rights Reserved 9
Data Models
• A data model: A collection of concepts that can be used to
describe the structure of a database which provides the
necessary means to achieve this abstraction.
– Here, structure of a database means the data types, relationships,
and constraints that apply to the data. Most data models also include
a set of basic operations for specifying retrievals and updates on
the database.
– In addition to the basic operations provided by the data model, it is
becoming more common to include concepts in the data model to
specify the dynamic aspect or behavior of a database application.
This allows the database designer to specify a set of valid userdefined operations that are allowed on the database objects.
• Many data models have been proposed, which we can
categorize according to the types of concepts they use to
describe the database structure.
ITDA1001 [Lesson 2] Copyright © 2018 VIT, All Rights Reserved 10
Categories of Data Models
• High-level or conceptual data models
– provide concepts that are close to the way many users perceive
data.
• Representational (or implementation) data models
– provide concepts that may be understood by end users
but that are not too far removed from the way data is
organized within the computer. Representational data
models hide some details of data storage but can be
implemented on a computer system in a direct way.
• Low-level or physical data models
– provide concepts that describe the details of how data is stored in
the computer. Concepts provided by low-level data models are
generally meant for computer specialists, not for typical end users.
ITDA1001 [Lesson 2] Copyright © 2018 VIT, All Rights Reserved 11
Conceptual data models
• Use concepts such as entities, attributes, and
relationships.
– An entity represents a real-world object or concept, such as an
employee or a project, that is described in the database.
– An attribute represents some property of interest that further
describes an entity, such as the employee’s name or salary.
– A relationship among two or more entities represents an interaction
among the entities; for example, a works-on relationship between an
employee and a project.
• Examples
– Entity Relationship Models
– Relational Model
ITDA1001 [Lesson 2] Copyright © 2018 VIT, All Rights Reserved 12
Conceptual data models (Cont…)
• Conceptual modeling is an important phase in designing a
successful database application.
• The first step shown is requirements collection and analysis.
During this step, the database designers interview
prospective database users to understand and document
their data requirements. The result of this step is a concisely
written set of users’ requirements.
• Once all the requirements have been collected and
analyzed, the next step is to create a conceptual schema for
the database, using a high-level conceptual data model.
This step is called conceptual design.
Requirement Collection
and Analysis
Conceptual Design
Data Requirements
ITDA1001 [Lesson 2] Copyright © 2018 VIT, All Rights Reserved 13
Conceptual data models (Cont…)
• The conceptual schema is a concise description of the data
requirements of the users and includes detailed descriptions
of the entity types, relationships, and constraints.
• These are expressed using the concepts provided by the
high-level data model.
• These concepts do not include implementation details, they
are usually easier to understand and can be used to
communicate with nontechnical users.
• The high-level conceptual schema can also be used as a
reference to ensure that all users’ data requirements are
met and that the requirements do not include conflicts.
• This approach enables the database designers to
concentrate on specifying the properties of the data, without
being concerned with storage details.
ITDA1001 [Lesson 2] Copyright © 2018 VIT, All Rights Reserved 14
Representational (or implementation)
data models
• Representation data model used most frequently in
traditional commercial DBMSs. These include the widely
used relational data model. Also network and
hierarchical models have been widely used in the past.
• Representational data models represent data by using
record structures and hence are sometimes called recordbased data models.
ITDA1001 [Lesson 2] Copyright © 2018 VIT, All Rights Reserved 15
Low-level or physical data models
• Physical data models describe how data is stored as files in
the computer by representing information such as record
formats, record orderings, and access paths.
• An access path is a structure that makes the search for
particular database records efficient.
• An index is an example of an access path that allows direct
access to data using an index term or a keyword.
ITDA1001 [Lesson 2] Copyright © 2018 VIT, All Rights Reserved 16
Entity Relationship Diagrams
• Most popular model currently used for database modelling.
• Designed to be platform independent.
• Primary components
– Entities
– Relationships
– Attributes
ITDA1001 [Lesson 2] Copyright © 2018 VIT, All Rights Reserved 17
Entity
• Object or Event. Real or Imaginary
• Interesting enough to us that we want to record something
about it
• Entity drawn as a box and a name
• Represents a collection of individual objects that we can
uniquely identify from each other.
• Objects are called “entity instances”
– Same thing as a table
– This box is the Entity STUDENT
– represents the collection of individual students
STUDENT
ITDA1001 [Lesson 2] Copyright © 2018 VIT, All Rights Reserved 18
Attribute
• Same thing as a field or column
• A property or characteristic of an entity
• A piece of data that we want to keep about the entity.
• Drawn as an ellipse connected to the entity.

STUDENT

Surname
Given
StudentNo
PhoneNo
ITDA1001 [Lesson 2] Copyright © 2018 VIT, All Rights Reserved 19
Types of Attributes
• The primary key attribute is underlined.
• A multi-valued attribute is an attribute that can have several
values. It is shown in a double ellipse.
– example: Phone – mobile and landline
• Composite Attribute is an attribute that has many
components
– Example : Phone – Country Code – Area Code

STUDENT

StudentNo
PhoneNo
STUDENT
PhoneNo
CountryCode AreaCode
Surname
Given
ITDA1001 [Lesson 2] Copyright © 2018 VIT, All Rights Reserved 20
Weak Entity
§ Some entity sets in real world naturally depend on some
other entity set
§ They can be uniquely identified only if combined with another entity
set
– Examples:
• child from parents, payment of a loan
• The partial key of a weak entity is the attribute that
distinguishes among all the entities of a weak entity related
to the same owning entity.
ITDA1001 [Lesson 2] Copyright © 2018 VIT, All Rights Reserved 21
Weak Entity (Cont..)
• Double rectangles for weak entity set
• Double diamond for weak entity relationship
• Dashed underscore for Partial Key

loan

payment

Loanpayment
LoanNumber Amount
Paymentnumber
PaymentAmount
PaymentDate
ITDA1001 [Lesson 2] Copyright © 2018 VIT, All Rights Reserved 22
Relationship
• Some type of association between entities
• Shown as a diamond joining entities together with a
relationship name.
• Therefore, the Relationship represents the collection of
individual associations.
ITDA1001 [Lesson 2] Copyright © 2018 VIT, All Rights Reserved 23
Relationship (Cont…)
• This diamond represents the collection of individual
associations between individual students and programmes

STUDENT

STUDENT

PROGRAMME

Enrol

PROGRAMME

ITDA1001 [Lesson 2] Copyright © 2018 VIT, All Rights Reserved 24
Degree
• Number of Entities a relationship is involved in
– Unary (Only one entity)
– Binary (Two entities)
– Ternary (Three entities)
– N-ARY (More than two entities)
ITDA1001 [Lesson 2] Copyright © 2018 VIT, All Rights Reserved 25
Degree (Unary) – One Entity
• A Unary relationship is a relationship of an entity to itself.
• The associations are between different objects of the same
entity.

STAFF

Supervise

STAFF

ITDA1001 [Lesson 2] Copyright © 2018 VIT, All Rights Reserved 26
Degree (Binary) – Two Entity
• Relationship between two entities
• Most common

STUDENT

enrol

PROGRAMME

ITDA1001 [Lesson 2] Copyright © 2018 VIT, All Rights Reserved 27
Degree (Ternary & n-ary)
• Ternary is between 3 entities
• We can actually have a relationship between any number of
entities.

SUPPLIER

PART

WAREHOUSE

has
ITDA1001 [Lesson 2] Copyright © 2018 VIT, All Rights Reserved 28
Cardinality
• Determines how many associations are allowed between
individual objects involved within a relationship.
• Enables us to constrain the relationships to only allow
particular situations
– Example : how many courses can a student have ?
ITDA1001 [Lesson 2] Copyright © 2018 VIT, All Rights Reserved 29
Cardinality (One to One)
• A student can only have 1 library card
• A library card can only be for 1 student

STUDENT

LIBRARY CARD

STUDENT

LIB CARD

ITDA1001 [Lesson 2] Copyright © 2018 VIT, All Rights Reserved 30
Cardinality (One to Many)
• A student can only have one programme
• A programme can have many students

STUDENT

PROGRAMME

STUDENT

PROGRAMME

ITDA1001 [Lesson 2] Copyright © 2018 VIT, All Rights Reserved 31
Cardinality (Many to Many)
• A student can have many courses
• A course can have many students

STUDENT

COURSE

STUDENT

COURSE

ITDA1001 [Lesson 2] Copyright © 2018 VIT, All Rights Reserved 32
Relationships
• Relationships can also have attributes
• There is no clear rule to determine between a relationship
and an entity
• Often, an entity can become a relationship and a
relationship can be re-drawn as an entity.
ITDA1001 [Lesson 2] Copyright © 2018 VIT, All Rights Reserved 33
Relational Model
• A model of tables and fields
– (lower level than E-R)
• Fields cannot be multivalued
• A Primary key is the unique identifier of the table, and can
be 1 or more fields.
• A Foreign key is a field that is a primary key in another
table.
ITDA1001 [Lesson 2] Copyright © 2018 VIT, All Rights Reserved 34
Relational Model (Cont…)
STUDENT
Surname
Given
DOB
PhoneNo
ProgCode
PROGRAMME
ProgName
DeptNo
StudentNo ProgCode
Table Name
Primary
Key
Foreign
Key
ITDA1001 [Lesson 2] Copyright © 2018 VIT, All Rights Reserved 35
Mapping E-R and Relational
• Both models, model the same thing, a database structure
(existing or future)
• One model can be re-drawn to the other
E-R RELATIONAL
ENTITY
ATTRIBUTE
TABLE
FIELD
ITDA1001 [Lesson 2] Copyright © 2018 VIT, All Rights Reserved 36
Mapping – Relationships
• Relationships in the E-R model are represented by Foreign
Keys in the relational model
• Foreign keys are NOT attributes in the E-R model, they
represent the relationship diamond.
• One more time, foreign keys are NOT E-R attributes!
ITDA1001 [Lesson 2] Copyright © 2018 VIT, All Rights Reserved 37
Mapping – 1:1 Relationship
• Put the foreign key on either side

STUDENT

LIBRARY CARD

STUDENT
Surname
Given
PhoneNo
LIB CARD
Limit
StudentNo
StudentNo Barcode
ITDA1001 [Lesson 2] Copyright © 2018 VIT, All Rights Reserved 38
Mapping – 1:1 Relationship
• OR Put the foreign key on other side
NOT BOTH sides

STUDENT

LIBRARY CARD

STUDENT
Surname
Given
PhoneNo
Barcode
LIB CARD
Limit
StudentNo Barcode
ITDA1001 [Lesson 2] Copyright © 2018 VIT, All Rights Reserved 39
Mapping – 1:M Relationship
• Put the foreign key on the Many side

STUDENT

PROGRAMME

STUDENT
Surname
Given
PhoneNo
ProgCode
PROGRAMME
ProgName
DeptNo
StudentNo ProgCode
ITDA1001 [Lesson 2] Copyright © 2018 VIT, All Rights Reserved 40
Mapping – M:N Relationship
• Create a new table to represent the relationship
• Primary keys of both sides are combined to make the
primary key of the new table.
ITDA1001 [Lesson 2] Copyright © 2018 VIT, All Rights Reserved 41
Mapping – M:N Relationship

STUDENT

COURSE

STUDENT
Surname
Given
PhoneNo
StudentNo
COURSE
CrsName
DeptNo
CrsCode
ENROL
Result
StudentNo+
CrsCode
ITDA1001 [Lesson 2] Copyright © 2018 VIT, All Rights Reserved 42
Mapping – M:N Relationship (Alternative)

STUDENT

COURSE

ENROL

STUDENT
Surname
Given
PhoneNo
StudentNo
COURSE
CrsName
DeptNo
CrsCode
ENROL
Result
StudentNo+
CrsCode

ITDA1001 [Lesson 2] Copyright © 2018 VIT, All Rights Reserved 43
Components – Summary

STUDENT	COURSE
Enrol

StudentNo Surname CrsCode
belong
ProgCode
ProgName
DeptNo
CrsName
Year

PROGRAMME

Given PhoneNO
ITDA1001 [Lesson 2] Copyright © 2018 VIT, All Rights Reserved 44
Notations
ITDA1001 [Lesson 2] Copyright © 2018 VIT, All Rights Reserved 45
Notations (Cont…)
ITDA1001 [Lesson 2] Copyright © 2018 VIT, All Rights Reserved 46
Summary
• Revision of Key Concepts
• Class Work
– Lesson 2 – Activity 2 (Lesson_2 Activity.docx)
• Questions and Answer

The post DESIGNING DATABASE ITDA1001 appeared first on My Assignment Online.

Expert Help With This Assignment — On Your Terms