CSE 480: Database Systems

1
CSE 480 Database Systems

• Lecture 2 Entity-Relationship Modeling

Reference Read Chapter 3 of the textbook
2
Database Design

• Goal is to derive a specification of the database
  schema
• Schema is the description of the database (e.g.,
  names of tables, columns/attributes, attribute
  types, and constraints)

3
Database Design

• 4 key steps
• Requirement analysis
• Discover what information needs to be stored and
  how the stored information will be used
• Conceptual database design
• Create conceptual schema for the database using
  high-level data model (e.g., entity-relationship
  modeling)
• Logical database design
• Convert E-R model to implementation data model
  (relational model)
• Physical design
• Specify the internal storage structure, indexes,
  and file organizations for the database files

4
Example COMPANY Database

• Requirement analysis
• The company is organized into DEPARTMENTs.
• Each department has a unique name, unique number
  and an employee who manages the department. We
  keep track of the start date of the department
  manager. A department may have several locations.
• Each department controls a number of PROJECTs.
  Each project has a unique name, unique number and
  is located at a single location
• We store each EMPLOYEEs social security number,
  address, salary, sex, and birthdate. Each
  employee works for one department but may work on
  several projects. We need to keep track of the
  number of hours per week that an employee
  currently works on each project. We also keep
  track of the direct supervisor of each employee.
• Each employee may have a number of DEPENDENTs.
  For each dependent, we keep track of their name,
  sex, birthdate, and relationship to employee

5
Conceptual Design E-R Diagram
We will explain how to construct such a diagram
in the next two lectures
6
Logical Design Mapping to Relational Schema
We will explain how to map the E-R diagram to a
relational schema in lecture 7
7
Entity-Relationship (E-R) Diagram

• A design methodology for modeling the concepts in
  an enterprise (mini-world)
• Concepts
• Entity types
• Relationship types
• Constraints
• E-R Diagrams provide a graphical representation
  of the entities, relationships, and constraints
  that make up a given design

8
Entity Types The E in E-R Diagram

• Entities specific objects or things in the
  mini-world that are represented in the database
• Professor John Doe, Electrical Engineering
  Department, CSE480, the red car that always park
  next to the building entrance, etc
• Entity Type collection of similar entities
• Bob Doe and Mary Doe are STUDENTs
• Electrical Engineering is a DEPARTMENT
• E-R diagram models the entity types (not
  individual entities)

9
Attributes of Entity Types

• Attributes are properties associated with an
  entity type
• Attributes of EMPLOYEE entity type include Name,
  SSN, Employee ID, BirthDate, Address, Salary,
  StartDate, etc
• Attributes of STUDENT entity type include Name,
  PID, GPA, sex, major, last semester enrolled,
  etc.
• When designing the E-R diagram, you need to
• List all the entity types
• List the attributes associated with each entity
  type
• You also need to know the TYPE of each attribute
• Simple or composite
• Single-valued or multi-valued
• Stored or derived

10
Types of Attributes

• Simple (Atomic) vs Composite
• Simple (atomic) attributes are indivisible
• SSN, Gender, Salary,
• Composite attributes may be composed of several
  components
• Name (FirstName, MiddleName, LastName)
• May have nested components

11
Types of Attributes

• Single-valued vs. Multi-valued
• Single-valued one value for each entity
• Examples Age, Birth Date, SSN
• Multi-valued Multiple values for each entity
• Examples Colors of a CAR, Hobbies of a STUDENT,
  Email addresses of a PERSON
• Multi-valued composite (Complex) attribute
• Ex PreviousDegrees of a STUDENT denoted by
  PreviousDegrees (College, Year, Degree, Field)
• Ex (MSU, 1994, BS, CS), (UM, 1996, MS, CS)

12
Types of Attributes

• Stored vs Derived
• Derived attribute is not physically stored in the
  database its value is computed from other
  attributes or from related entities
• Examples
• Age (derived from Birth Date),
• NumberOfEmployees (derived by counting number of
  entities associated with the Employee entity
  type),
• GPA (derived by averaging the grades of each
  STUDENT entity from the GRADE_REPORT entity type)

13
Example MOVIE Entity Type

• MOVIE entity type has the following attributes

Attribute Simple Composite Single-valued Multi-valued Stored Derived
Title
Release_date
Director
Genre
Awards
Production_company
Attribute Simple Composite Single-valued Multi-valued Stored Derived
Title
Release_date
Director
Genre
Awards
Production_company
14
Constraints on Entity Types

• When designing the E-R diagram, you also need to
  think about constraints on the entity types
• Domain constraint
• Null constraint
• Key constraint

15
Domain Constraint

• Each attribute is associated with a set of
  values(domain or data type)
• Employee ID is CHAR(10),
• Salary is FLOAT,
• StartDate is DATE,
• SSN is CHAR(10)
• Hourly is BOOLEAN (Hourly employee vs
  Salaried employee)
• The domain of an attribute restricts the range of
  valid values an attribute can have (domain
  constraint)
• Example if SSN is CHAR(10) and you try to add a
  record with SSN 123-456-1211 to the database,
  it will violate the domain constraint of the
  attribute (so the DBMS will throw an error)

16
NULL Constraint

• A special placeholder to denote the following
• When an attribute is inapplicable to an entity
• When an attribute value is unknown or missing
• Not exactly a value
• If Johns blood type is NULL and Marys blood
  type is NULL, it does not mean that they both
  have the same attribute values
• Null constraint restricts whether an attribute
  value can be NULL

ID Name Weight Blood Pressure Blood Type
1 John Smith 160 NULL NULL
2 Mary Smith NULL 110/75 NULL
17
Key Constraint

• When you store a data instance (record) into the
  database, you expect to be able to retrieve it
  with a query
• To do this, you will need to distinguish each
  data instance from other instances in the
  database
• Key attribute attribute for which each entity
  must have unique value
• Examples SSN of EMPLOYEE, PID of STUDENT,
  DEPTNUMBER of DEPARTMENT
• Key constraint Prohibits two entities from
  having the same value for the key attribute
• Used to uniquely identify individual entities in
  a database

18
Key Constraint

• A key attribute may be composite
• Registration is a key of CAR entity type with
  components (Registration_State,
  Registration_Number)
• Minimality property superfluous attribute must
  not be included in the key
• Ex SSN is minimal whereas (SSN, Name) is not
  minimal
• Some entity types may have more than one key
• Ex VehicleID and (Registration_State,
  Registration_Number) are keys to CAR

19
Representing Entity Type in E-R Diagram
Composite attribute
No_Owners
Derived attribute
Multi-valued attributes have double ovals
Entity type
Attribute
20
Summary
21
Exercise COMPANY database

• Requirements
• The company is organized into DEPARTMENTs.
• Each department has a unique name, unique number
  and an employee who manages the department. We
  keep track of the start date of the department
  manager. A department may have several locations.
• Each department controls a number of PROJECTs.
  Each project has a unique name, unique number and
  is located at a single location
• We store each EMPLOYEEs social security number,
  address, salary, sex, and birthdate. Each
  employee works for one department but may work on
  several projects. We keep track of the number of
  hours per week that an employee currently works
  on each project. We also keep track of the direct
  supervisor of each employee.
• Each employee may have a number of DEPENDENTs.
  For each dependent, we keep track of their name,
  sex, birthdate, and relationship to employee

22
Exercise COMPANY database

• Requirements
• The company is organized into DEPARTMENTs.
• Each department has a unique name, unique number
  and an employee who manages the department. We
  keep track of the start date of the department
  manager. A department may have several locations.

23
Exercise COMPANY database

• Requirements
• Each department controls a number of PROJECTs.
  Each project has a unique name, unique number and
  is located at a single location

24
Exercise COMPANY database

• Requirements
• We store each EMPLOYEEs social security number,
  address, salary, sex, and birthdate. Each
  employee works for one department but may work on
  several projects. We keep track of the number of
  hours per week that an employee currently works
  on each project. We also keep track of the direct
  supervisor of each employee.

25
Exercise COMPANY database

• Requirements
• Each employee may have a number of DEPENDENTs.
  For each dependent, we keep track of their name,
  sex, birthdate, and relationship to employee

26
Entity Types

• This initial design is not complete

Entity types are not independent
27
Refining design by using relationships

• Relationships between entity types

28
Relationships and Relationship Types

• Relationship relates two or more entities
• EMPLOYEE John Doe works for Geography DEPARTMENT
• EMPLOYEE Mary Smith works for Chemistry
  DEPARTMENT
• Relationships of the same type are grouped into a
  relationship type

29
Representing Relationship Types in ER Diagram
30
Attributes of a Relationship Type
Relationship types can also have attributes
31
Relationship Types for COMPANY database
32
Relationship Types for COMPANY database
(Draw the rest of its attributes)
Not quite right yet! (We will revisit this in
lecture 3)
33
Recursive Relationships and Roles

• Relationship can relate elements of same entity
  type (recursive relationship)
• Ex Supervises relationship type relates two
  Employee entity types
• Mary supervises Bob
• Need to distinguish different entities
  participating in a relationship

34
Roles

• Use role name to indicate the role that a
  participating entity plays in a relationship
  instance
• SUPERVISION has roles Subordinate and Supervisor
• Role names must be provided for every recursive
  relationship type
• Role names are not necessary where all
  participating entity types are distinct

35
Relationship Degree

• Degree the number of entity types participating
  in a relationship type
• Binary relationships (WORKS_ON, MANAGES)
• Ternary relationship

SUPPLY
PROJECT
SUPPLIER
PART
36
Summary

• Conceptual database design
• Using E-R modeling
• Entity types
• Domain, null, and key constraints
• Relationship types, their attributes, roles, and
  degree
• Relationship Constraints? (next lecture)

37
Exercise

• Choose a domain, for example
• Airline reservation system
• Electronic medical records
• Online bookstore (e.g., Amazon)
• Law enforcement (e.g., FBI criminal database)
• Online photo sharing (e.g., Flickr)
• College football/basketball database
• Answer the following questions
• What are the entity types and their corresponding
  attributes?
• What are the relationship types and their
  corresponding attributes?
• What are the constraints? Are these constraints
  on entity types or relationship types?
• Is there any other constraints that cannot be
  easily modeled?
• Draw the E-R diagram