ch 02 datamodelling i
TRANSCRIPT
-
7/31/2019 Ch 02 DataModelling I
1/21
Business Information Systems
Data Modeling - I
-
7/31/2019 Ch 02 DataModelling I
2/21
What is a Data Model?
student id
student last name
student first name
student major
STUDENTcourse id
course title
course number of credits
course instructor name
COURSE
attends/is taught to
DATA MODEL
The specification ofdata structures andbusiness rules to represent business
requirements.
2
-
7/31/2019 Ch 02 DataModelling I
3/21
Entities
Each entity should be fully defined by the businesscommunityto: Identify why the business needs this information
Improve understanding
Avoid redundancy
Each ENTITY should be named using: Unique entity names in a model
A non-technical, business-like name
A singular noun that describes a singular instance (no collective
nouns)
ENTITYA person, place, thing, event, or concept about which thebusiness keeps data.
4
-
7/31/2019 Ch 02 DataModelling I
4/21
Defining the Entity
Each ENTITY represents
a set/collection of likeindividual objects calledinstances.
Two types of entities:
Independent: depends onno other entity for its
identification
Dependent: depends on oneor more entities for itsidentification
LINE ITEM
ORDER
5
-
7/31/2019 Ch 02 DataModelling I
5/21
Attributes
attributesemployee first name
employee last name
employee address
employee phone number
EMPLOYEE
ATTRIBUTE
A distinct characteristic of an ENTITY forwhich data is maintained.
ENTITY Name(above the box)
employee id
Attribute Names :
Uniqueattribute names in amodel (or entity)
A non-technical, business-
likename
Asingular nounthat
describes a singularinstance (no collectivenouns)
All lowercase (Erwinstandard), with spaces
6
-
7/31/2019 Ch 02 DataModelling I
6/21
Attribute Types
Two types of attributes:
Key Non-key
consultant id
consultant first name
consultant last name
consultant specialization
consultant hourly rate
CONSULTANTKey Attribute(s)(above the line)
Non-KeyAttribute(s)
(below the line)
7
-
7/31/2019 Ch 02 DataModelling I
7/21
How do we determine keys?
CANDIDATE KEY
Any attribute or group of attributes which serves touniquely identify each instance of an ENTITY.
BOOK BOOK BOOK
author first name
author last name
book title
book edition
book publisherbook year published
book isbn
book lc catalog number
author first name
author last name
book title
book edition
book publisherbook year published
book isbn
book lc catalog number
author first name
author last name
book title
book edition
book publisherbook year published
book isbn
book lc catalog number
8
-
7/31/2019 Ch 02 DataModelling I
8/21
Primary Keys
PRIMARY KEY
An ATTRIBUTE or group of attributes thatuniquely identifies an instance of the entity.
book isbn
author first name
author last name
book title
book edition
book publisher
book year published
book lc catalog number
BOOK
The primary key is always placedabove the line in an Entity
Factors to consider: Should be efficient
Must not contain any nullparts
Values must remain static
Should be a data element inyour control
9
-
7/31/2019 Ch 02 DataModelling I
9/21
Surrogate Keys
transaction id
account id
customer id
cash machine id
transaction date
CASH MACHINE TRANSACTION
Composite Key Surrogate Key
account id
customer id
cash machine id
transaction date
CASH MACHINE TRANSACTION
SURROGATE KEYA contrived, non-intelligent, single-attribute key used to
replace a long composite key.
10
-
7/31/2019 Ch 02 DataModelling I
10/21
The story so far
An entity is something about which we want to store information
Entities are identified through the system analysis processAn entity has attributes which are also identified during systemanalysis process
Information about an entity is stored in values of the attributes
Key attributes identify an instance of an entity uniquely
If there are more than one key attribute they are called candidatekeys
Based on the application one of them is chosen as the primary key
If it is not possible to have a single attribute as the key attribute,
the primary key is created as a composite of 2 or more non key attributes or a single but new and artificial attribute called surrogate key is created
11
-
7/31/2019 Ch 02 DataModelling I
11/21
Relationships
MOVIE
MOVIE COPY
is rented as
RELATIONSHIP
A logical link between two entities thatrepresents a business rule or constraint.
12
-
7/31/2019 Ch 02 DataModelling I
12/21
Relationships
is ordered from/sends usPART SUPPLIER
13
1:1 One to one mandatory; One student has one
address1:N One to many mandatory ; A customer havingmultiple addresses
1:1 One to one optional; An employee may or may
not have a spouse1:N One to many optional; One order having manyorder items
M:N many to many; Many parts being supplied by
many suppliers
-
7/31/2019 Ch 02 DataModelling I
13/21
Relationship Cardinality
CUSTOMER ORDERplaces
BOOKSTUDENThas
PASSENGERAIRPLANE SEAT is ticketed for
TYRECARrequires
N
A customer places zero, one, or more orders. An order relatesto only one customer.
Each student has one or more books. A book is with one student
Each seat will seat zero or one passenger. One passenger can sit ononly one seat.
Each car has exactly N tyres
14
-
7/31/2019 Ch 02 DataModelling I
14/21
Creating ER Diagram
Identify and list all the entities of the system
For each entity list the attributesIdentify the candidate keys and choose the primary keybased on the application
For each pair of entities, decide if there is a relationship
If there is a relationship, decide what kind
Use tools like Visio to draw the ER Diagram
Discuss the ER model with end users (walkthrough)
15
-
7/31/2019 Ch 02 DataModelling I
15/21
Relationship Rules
1:1 mandatory; this means that both are parts of larger
entity; One entity is merged with the other to create anentity which has attributes of both entities
1: 1 optional; primary key of one entity is placed asattribute of the entity which is optional
1:n optional/ mandatory; primary key of singular entityis placed as an additional attribute in the entity that canhave multi occurrences
N:M; create new artificial entity which has primary key
of both original entities as attributes
16
-
7/31/2019 Ch 02 DataModelling I
16/21
OMS
17
Product Order CustomerN M MN
Product Order Customer
N M
MN
Order Item
Note: For clarity in understanding the type of relationship, the relationship names are not metioned in this diagram
-
7/31/2019 Ch 02 DataModelling I
17/21
Foreign Keys
FOREIGN KEY (FK)
A primary key of a parent entity that iscontributed to a child entity across arelationship.
contains
order number (FK)line item sequence number
product codeline item quantitylineitem numberline item description
LINE ITEMORDER
order number
order dateorder salesrepresentative
order customer
-
7/31/2019 Ch 02 DataModelling I
18/21
Referential Integrity : What & Why ?
What is the impact of:
Inserting, updating, or deleting a Parent Primary Key value?
Inserting, updating, or deleting a Child Foreign Key value?
None of these actions should break the relationshipfrom Child to Parent
Options can be specified as to how the DBMS shouldmanage these actions to maintain referential integrity
REFERENTIAL INTEGRITY (RI)Rules that determine what happens when a
Parent or Child instance is inserted, updated or
deleted.
18
-
7/31/2019 Ch 02 DataModelling I
19/21
Exercise
Identify the entity, attributes and the key
Order Date
Customer Name
Customer AddressCustomer Credit Rating
Order Value
Item 1 Desc
Item 1 unit price
Item 1 quantity
Item 1 Deliver DateItem 2 Desc
Item 2 unit price
Item 2 quantity
Item 2 Deliver Date
ORDER
-
7/31/2019 Ch 02 DataModelling I
20/21
Apply the ER diagram rules for the data model of a
library system
Supplier Parts ProductN M MN
-
7/31/2019 Ch 02 DataModelling I
21/21
Next Class
Data Normalization