LECTURE 00-b

 SUBJECT NAME: Database Management and

Implementation
 13.13.1 Code: ITU07314

INSTRUCTOR: MR. ADAM B. MTAHO

Data Models
 Data models are key to the design of database systems.
 A data modeling schema is a method that allows us to
model or illustrate a database
 It is often in the form of a graphic diagram
 There are a number of different levels of data model that
we need to consider:
 Conceptual Data Model

 Implementation Model

 Physical Model
Data Models
 Conceptual Data Model:
 a high-level model of the data, describing what is

stored.
 The conceptual data model is often presented in the

form of a written document, which describes the data

using high-level concepts such as objects and
relationships.
 Examples of conceptual models are class models, using

standard notation such as Entity-Relationship models.

Data Models
 Implementation Model:
 an intermediate level describing how the data is

logically organized.
 This is often presented as a description of the interface

to the DBMS, and describes the data using medium-

level concepts such as relations or sets.
 Examples of logical models are relational models and

network models.
 Data Models
 Physical:
 very low-level compared to the conceptual and

implementation models, and describes how the data is

physically stored.
 This is often expressed in the form of commands in a

control language, using concepts like record formats or

index files.
 The implementation model can be defined in the statements

that create the database, such as part of the SQL language,

for example CREATE TABLE or CREATE INDEX.
 SQL language will be covered later in this course
Simplified database system environment
Three-level Database Architecture
 Databases are considered to have three levels: physical,
conceptual and external.
 The internal level (or physical level) describes the
physical storage structure of the database.
 The conceptual level has a conceptual schema, which
describes the structure of the entire database for the
community of users.
 The external level (or view level) includes a number of
external schemas or user views.
Three-level Database Architecture
 .

Figure 1.2:
 Data Independence
 Data independence is an important concept in database design.
 There are two kinds of data independence that we need to
consider
 Logical Data Independence:
 the conceptual schema can be altered without having to

modify the views or applications that access the database.

For example, an existing application may access customer
records in a database.
 If an additional attribute is added to the customer schema,

for example a reference indicating passport number, then

only applications or views that need to access the new data
item need to be modified.
 Data Independence
 Physical Data Independence:

 the physical schema can be changed without having to

change the conceptual schema.

 An example of such a change could be the addition of a

new index for accessing customer addresses.

 This does not affect the conceptual schema.

What Is a Data Modeling Schema
 The ER diagram (ERD) is a graphic tool that
facilitates data modeling. The ERD is a subset of
"semantic models" in a database.
 Semantic models refer to models that intend to
elicit meaning from data.
 ERDs are not the only semantic modeling tools,
but they are common and popular.
What Is an Entity Relationship (ER)
Diagram?
 The ER diagram is a semantic data modeling tool that is
used to accomplish the goal of abstractly describing or
portraying data.
 Abstractly described data is called a conceptual model.
 Conceptual model will lead us to a "schema.“
 A schema implies a permanent, fixed description of the
structure of the data.
 Therefore, when we agree that we have captured the correct
depiction of reality within our conceptual model, our ER
diagram, we can call it a schema.
Entity, Relationship, Attribute
 As the name implies, an ER diagram models data as
entities and relationships,
 Entities have attributes.
 An entity is a thing about which we store data, for
example, a person, a bank account, a building.
 It is "thing which can be distinctly identified."
 So an entity can be a person, place, object, event, or
concept about which we wish to store data.
Some examples of entities
 Examples of a person entity would be EMPLOYEE, VET, or
STUDENT.
 Examples of a place entity would be STATE or COUNTRY.
 Examples of an object entity would be BUILDING, AUTO,
or PRODUCT.
 Example of an event entity would be SALES, RETURNS, or
 REGISTRATION.
 Examples of a concept entity would be ACCOUNT or
DEPARTMENT
Entity
 Entity sets: collections of data about entities
 An entity instance is a single occurrence of an entity.
 Each instance of an entity must be uniquely identifiable so
that each instance is separate and distinctly identifiable
from all other instances of that type of entity.
 This unique identifier is called a key.
 A relationship is a link or association between entities.
Relationships are usually denoted by verb phrases.
 An attribute is a property or characteristic for an entity.
Database modeling-methodology
 Begin with “what is to be stored”?
 identify a central, "primary" entity — a category about
which we will store data.
 Determine what information we want to record about our
entity.
 Draw a diagram of our first-impression entity (our

primary entity).
 Translate the diagram into English.

 Present the English (and the diagram) back to the user

to see if we have it right and then progress from there.

ER Design Methodology

Figure 1.3 : An ER Diagram with Entity and Attributes

 An ER Diagram with One Entity and Five
Attributes
 .

Figure 1. 4: An ER Diagram with One Entity and Five

Attributes
More about Attributes

 Attributes are characteristics of entities that provide

descriptive details about the entities.
 There are several different kinds of attributes:
 simple or atomic,

 composite,

 Key attribute

 multi-valued, and

 derived.

 The properties of an attribute are its name, description,

format, and length
More about Attributes
 The Simple or Atomic Attribute
 Cannot be further broken down or subdivided, hence

the notion "atomic.

 An example of a simple or atomic attribute would be

Student Registration Number, where a student would

be expected to have only one, undivided Social
Security number
The Composite Attribute

 Also called a group attribute, is an attribute formed by

combining or aggregating related attributes

Figure 1.5: An ER Diagram with a Composite Attribute —name

The Multi-Valued Attribute
 Another type of non-simple attribute that has to be
managed is called a multi-valued attribute.

 The multi-valued attribute, as the name implies, may take

on more than one value for a given occurrence of an entity.

 For example, the attribute school could easily be multi-

valued if a person attends more than one school.
The Multi-Valued Attribute
 .

Figure 1.6: An ER Diagram with One Entity and Five

Attributes
The Derived Attribute
 Derived attributes are attributes that the user may envision
but may not be recorded per se.
 These derived attributes can be calculated from other data
in the database.
 An example of a derived attribute would be an age that
could be calculated once a student's birthdate is entered.

 a derived attribute is shown in a dashed oval

The Derived Attribute
 An ER Diagram with a Derived Attribute — age

Figure 1.7
keys

 The sense of a database is to store data for retrieval. An

attribute that may be used to find a particular entity
occurrence is called a key.

 If an attribute can be thought of as a unique identifier for

an entity, it is called a candidate key.

 When a candidate key is chosen to be the unique identifier,

it becomes the primary key for the entity.
keys
 An ER Diagram with a Primary Key or Unique Identifier
 Attribute

Figure 1.8
Strong vs weak Entities
 Entities that have at least one identified key can be called
strong entitie.

 Depend on other entities for their being

Strong vs weak Entities

Figure 1.9
Domain of attributes
 The "domain of values" is the set of values that a given
attribute may take on.
 The domain consists of all the possible legal values that are
permitted on an attribute.
 A data type is a broader term used to describe attributes, but
"data type" includes the idea of what operations are
allowable.
 Since database people are usually more concerned about
storage and retrieval, database "data types" usually just
focus on the "domain of values."
Mapping the Entity Diagram to a
Relational Database
 The process of converting an ER diagram into a database is
called mapping.
 Basically, a relational database is a database of two-
dimensional tables called "relations."
 The tables are composed of rows and columns.
 The rows are often called tuples and the columns, attributes.
In relational databases, all attributes (table columns) must
be atomic and keys must not be null.
 In addition, in relational databases, the actual physical
location of the data on a disk is not usually necessary to
know.
Mapping the Entity Diagram to a
Relational Database
 For strong entities: develop a new table (relation) for each
strong entity and make the indicated key of the strong
entity the primary key of the table.
 If more than one candidate key is indicated on the er
diagram, choose one for the primary key.
 Map atomic attributes from an entity — for entities with
atomic attributes: map entities to a table by forming
columns for the atomic attributes
An ER Diagram with One Entity and Five
Attributes
 .

Figure 2.0: An ER Diagram with One Entity and Five

Attributes
Defining a Relationship for Our New Entity
 Databases are designed to store related data. For example,
it would ordinarily make no sense to record data about
students and foreign currencies
 These concepts are not related.
 In a database we should be creating a collection of related
data.
 To make the SCHOOL entity and the STUDENT entity
function as a database, we have to add something — the
relationship that the entity SCHOOL has to the entity
STUDENT.
Defining a Relationship for Our New Entity
 A relationship in an ER diagram is a connection between
two or more entities, or between one entity and itself.
 The latter kind of relationship, between one entity and
itself, is known as a recursive relationship
 A relationship name is usually a verb or verb phrase that
denotes the connection between entities
The relationship is depicted
as attend
.

Figure 2.1. The STUDENT Entity with a Relationship to the SCHOOL

Relationship
 The degree of a relationship refers to the number of
entities that participate
 in the relationship. In Figure x, two entities are
participating in the relationship attend, so this is called a
binary relationship.
 The Relation :
 A STUDENT attends SCHOOLS and a SCHOOL is
attended by STUDENTS.
An ER Diagram of the STUDENT–
AUTOMOBILE
Database

Figure 2.1:
The Cardinality Ratio of a Relationship
 Cardinality is a rough measure of the number of entities
(one or more) that will be related to another entity (or
entities).
 For example, there are four ways in which the entities
AUTOMOBILE and STUDENT can be "numerically
involved" in a relationship:
 one-to-one (1:1),

 many-to-one (M:1),

 one-to-many (1:M), and

 many-to-many (M:N).
Relationships types

 One-to-One (1:1)
 In this type of relationship, one entity is associated with

one other entity, and vice versa. E.g. one automobile is

driven by one student and one student drives one
automobile
 Many-to-One (M:1)
 If the SA (STUDENT:AUTOMOBILE) relationship

(shown in Figure 3.6) were many-to-one, we would say

that many students are associated with one automobile and
one automobile is associated with many students; that is:
STUDENT:AUTOMOBILE::M:1
Relationships types

 One-to-Many (1:M)
 The sense of a one-to-many SA

(STUDENT:AUTOMOBILE) relationship (shown in

Figure 2.1) would be that a student is associated with many
automobiles and an automobile is associated with one
student.
 Many-to-Many (M:N)
 In many-to-many relationships, many occurrences of one

entity are associated with many of the other. Many-to-

many is depicted as M:N, as in M of one thing related to N
of another thing.