SEG 3550 Fundamentals of Information Systems

1
SEG 3550Fundamentals of Information Systems

• Tutorial 2

2
Overview of Database Design Process

• Two main activities
• Database design
• Applications design
• Focus in this part on database design
• To design the conceptual schema for a database
  application
• Applications design focuses on the programs and
  interfaces that access the database
• Generally considered part of software engineering

3
Overview of Database Design Process
4
ER Model Concepts

• Entity Relationship Model (ER Model) is a popular
  high-level conceptual data model used for the
  conceptual design of database applications.
• ER model has three main concepts
• Entities
• Attributes
• Relationships

5
ER Model Concepts - Entity

• Entity
• Entities are specific objects or things in the
  mini-world that are represented in the database.
• For example the EMPLOYEE John Smith, the Research
  DEPARTMENT, the ProductX PROJECT
• Entity set define a collection (or set) of
  entities that have the same attributes.
• Each entity type is described by its name and
  attributes.
• Figure 3.6 shows two entity types, EMPLOYEE and
  COMPANY.

6
ER Model Concepts - Attribute

• Attribute
• Attributes are properties used to describe an
  entity.
• For example an EMPLOYEE entity may have the
  attributes Name, SSN, Address, Sex, BirthDate
• A specific entity will have a value for each of
  its attributes.
• For example a specific employee entity may have
  Name'John Smith', SSN'123456789', Address
  '731, Fondren, Houston, TX', Sex'M',
  BirthDate'09-JAN-55
• Each attribute has a value set (or Domain or
  data type) associated with it.
• For example. integer, string, subrange,
  enumerated type,

7
Types of Attributes (1)

• Simple versus composite
• Simple (or atomic) Each entity has a single
• atomic value for the attribute,
• e.g. SSN or Sex.
• Composite Composition may form a hierarchy
• where some components are themselves composite.
• e.g. Address(StreetAddress, City, State, Zip) or
  Name((FirstName, MiddleName, LastName).
• Single-valued versus multivalued
• Single-valued a single value for a particular
  entity
• e.g. Age is a single-valued attribute of person.
• Multivalued An entity may have multiple values
  for that attribute
• e.g. a Colors attribute for a car, or a
  PreviousDegrees attribute for a person.
• Denoted as Colors or PreviousDegrees

8
Types of Attributes (2)

• Stored versus derived
• For example, the Age and BirthDate attributes of
  a person. The value of Age can be determined from
  the current(todays) date and the value of that
  persons BirthDate.
• The Age attribute is called a derived attribute.
  (or be derivable from the BirthDate attribute.)
• The BirthDate attribute is called a stored
  attribute.
• In general, composite and multi-valued attributes
  may be nested arbitrarily to any number of
  levels, although this is rare.
• For example, PreviousDegrees of a PERSON is a
  composite multi-valued attribute denoted by
  PreviousDegrees (College, Year, Degree, Field)
• Multiple PreviousDegrees values can exist
• Each has four subcomponent attributes
• College, Year, Degree, Field

9
Types of Attributes (3)

• Null Values in some cases a particular entity
  may not have an applicable value for an
  attribute.
• The meaning of Null can be classified into
• Not applicable
• A person with no college degree would have null
  for College Degree.
• Unknown
• Exists but is missing
• If the Height attribute of a person is listed as
  null.
• Not known
• If the HomePhone attribute of a person is null.

10
Key Attributes (1)

• Super key any set of attributes such that the
  values of the attributes (taken together)
  uniquely identify one entity in the entity set
• For example, HKID, SID, NAME, SID.
• Candidate key Minimal super key -- a super key
  with no redundant attributes
• For example, HKID, SID.
• Primary key A primary key is one of the
  candidate keys, designated by the database
  designer
• For example, SID.
• Every primary key is also a candidate key every
  candidate key is also a super key, but not vice
  versa

11
ER Model Concepts - Relationship

• A relationship relates two or more distinct
  entities with a specific meaning.
• For example,
• EMPLOYEE John Smith works on the ProductX
  PROJECT,
• EMPLOYEE Franklin Wong manages the Research
  DEPARTMENT.
• The degree of a relationship set is the number of
  participating entity types.
• Relationship types of degree 2 are called binary
• Relationship types of degree 3 are called ternary
  and of degree n are called n-ary
• In general, an n-ary relationship is not
  equivalent to n binary relationships
• For example,
• Both MANAGES and WORKS_ON are binary
  relationships.

12
One-to-One (11) Relationship
12
13
Many-to-One (N1) Relationship
13
14
Many-to-Many (MN) Relationship
14
15
Recursive Relationship

• A relationship whose with the same participating
  entity type in distinct roles.
• For example, the SUPERVISION relationship
• EMPLOYEE participates twice in two distinct
  roles
• supervisor (or boss) role
• supervisee (or subordinate) role
• Each relationship instance relates two distinct
  EMPLOYEE entities
• One employee in supervisor role
• One employee in supervisee role
• Figure 3.11 show a recursive relationship.
• In figure, first role participation labeled with
  1 and second role participation labeled with 2.
• In ER diagram, need to display role names to
  distinguish participations.

15
16
Example of Recursive Relationship
16
17
Attribute of Relationship

• A relationship can have attributes
• For example, HoursPerWeek of WORKS_ON
• Its value for each relationship instance
  describes the number of hours per week that an
  EMPLOYEE works on a PROJECT.
• A value of HoursPerWeek depends on a particular
  (employee, project) combination
• For example, StartDate of MANAGES
• Its value for each relationship instance
  describes the date on which a manager started
  managing a department
• Most relationship attributes are used with MN
  relationships
• In 1N relationships, they can be transferred to
  the entity type on the N-side of the relationship

17
18
Constraints on Relationships

• Constraints on Relationship
• (Also known as ratio constraints)
• Cardinality Ratio (specifies maximum
  participation)
• One-to-one (11)
• One-to-many (1N) or Many-to-one (N1)
• Many-to-many (MN)
• Existence Dependency Constraint (specifies
  minimum participation) (also called participation
  constraint)
• zero (optional participation, not
  existence-dependent)
• one or more (mandatory participation,
  existence-dependent)

18
19
Alternative (min,max) notation for relationship
structural constraints

• Specified on each participation of an entity set
  E in a relationship set R
• Specifies that each entity e in E participates in
  at least min and at most max relationship
  instances in R
• Default(no constraint) min0, maxn
• Must have min?max, min?0, max ?1
• Derived from the knowledge of mini-world
  constraints

20
Example

• A department has exactly one manager and an
  employee can manage at most one department.
• Specify(1, 1) for participation of DEPARTMENT in
  MANAGES
• Specify(0, 1) for participation of EMPLOYEE in
  MANAGES
• An employee can work for exactly one department
  but a department can have any number of employees
• Specify(1, 1) for participation of EMPLOYEE in
  WORKS_FOR
• Specify(0, n) for participation of DEPARTMENT in
  WORKS_FOR

21
The (min,max) notation for relationship
constraints
22
Symbols used in ER diagrams

• In ER diagrams, an entity type is displayed in a
  rectangular box
• Attributes are displayed in ovals
• Each attribute is connected to its entity type
• Components of a composite attribute are connected
  to the oval representing the composite attribute
• Each primary key attribute is underlined
• Multivalued attributes displayed in double ovals
• Relationships are displayed in Diamond-shaped
• Connected to the participating entity types via
  straight lines

23
Exercise

• The bank is organized into branches. Each branch
  is located in a particular city and it identified
  by a unique name. The bank monitors the assets of
  each branch.
• Bank customers are identified by their
  customer-id values. The bank stores each
  customers name, and the street and city where
  the customer lives. Customers may have accounts
  and can take out loans. A customer may be
  associated with a particular banker, who may act
  as a loan officer or personal banker for the
  customer.
• Bank employees are identified by their
  employee-id values. The bank administration
  stores the name and telephone number of each
  employee, the names of the employees dependents,
  and the employee-id number of the employees
  manager. The bank also keeps track of the
  employees start date and, thus, length of
  employment.

24
loan
account