CS580 Advanced DB Concepts

1
CS580 Advanced DB Concepts

• Ch 3
• Irena Pevac

2
E-R Model

• Entities
• Attributes
• Relationships

3
Entities

• Entities are important objects in the miniworld
  described in DB
• Objects with physical existance
• Student, Employee, Car, Patient, Doctor, Plane
• Objects that exist conceptualy
• Job, Enrollment, Course, CourseSection, Contract,
  Operation, Project

4
Entities

• Entity type or entity class
• Represents all individual members or instances of
  the collection of that type
• Student type represents all students in the
  miniworld

• Entity instance
• Represents one particular instance of the object
  from the class of objects
• Student Mary Simon as an individual in the same
  miniworld

5
Attributes

• Important features of an entity
• that describe properties of an entity
• that are relevant for the miniworld business and
  have to be recorded in DB
• COURSE entity has attributes
• Course code
• Course Title
• Course Description
• Number of Credits

6
Example of CUSTOMER Entity
7
Attribute types

• Simple and composite
• Single-valued and multivalued
• Stored and derived

8
Simple Attributes

• Simple attribute is atomic and cannot be further
  divided
• They describe simple features of an entity
• Number of credits for the COURSE entity
• Title for the COURSE entity
• Age for EMPLOYEE entity

9
Composite Attributes

• Composite attribute has further features that are
  part of it
• Address may consists of
• Name, Street address, Town, Zip, State, Country
• Street address may consist of
• Number, Street name, Apartment Number
• Name may consists of
• First name, Last name, Middle name
• Phone may consists of
• Area code, Phone number, Extension

10
Single Valued Attributes

• Single Valued attributes have only one value for
  each instance of an entity
• Each attribute Number of Credits has single value
  for each COURSE entity instance
• Each attribute Title has single value for each
  COURSE
• Each attribute Age and/or Sex has single value
  for each EMPLOYEE

11
Multivalued Attributes

• Phone attribute may be multivalued when person
  has several phones (home, office, mobile)
• Address attribute may be mutivalued for STUDENT
  when student has permanent and temporary address
• Fax Number may be multivalued when company or
  department have more than one fax machines

12
Stored versus Derived Attributes

• Stored attributes are those whose values will be
  entered into DB when it is populated with data
• Birth date for an EMPLOYEE
• Derived Attributes are functionally related to
  existing attribute(s) that are stored and can be
  calculated from that other attributes values
• Age of an EMPLOYEE can be calculated from
  his/hers Birth date

13
Null Values

• Some attributes do not have a value for some
  instances of an entity
• Null values are used when attribute value is
  unknown, not available, or not applicable
• Apartment number is not applicable for all single
  home addresses
• Date of death is not applicable for live patients
• Grade may be null for students who did not
  complete the course. It is unknown when course is
  in progress.

14
Complex Attributes

• Composite and multivalued attributes can be
  nested in an arbitrary way to form Complex
  attributes
• Use () for components of composite attributes
• Use around multivalued attributes
• Phone(Area code, Phone number)
• Adress
• (
• StreetAddress( Number, Street name),
• City,
• State,
• Zip
• )

15
Entity Type or class and Entity Set

• Entity type or entity class is defined by its
  name and the list of its attributes. It describes
  the schema or structure for the set of entities
• COURSE-SECTION( Course code, Section,Semester,Year
  , Instructor)
• Entity set is set of entity instances with their
  corresponding values for attributes
• cs1 (CS580, 70, Spring, 2002, Pevac)
• cs2 (CS501, 70, Fall, 2001, Pevac)
• cs3 (CS501, 71, Spring, 2002, Calvert)

16
Key

• Key is one or more attributes that uniquely
  identifies each entity instance from the entity
  set
• Each entity instance has different values for key
  attributes

17
Key Examples

• Entity COMPANY( Name, Address, President)
• Name is key because each company has unique name.
  Once some company uses certain name no other
  company can have that same name
• Entity STUDENT(StudID, Name, SSN, gpa)
• Name is not a key because there might be more
  students with the same name
• StudID is key if it has unique value for each
  student
• SSN is key because each person has unique SSN

18
Key Examples

• COURSE-SECTION( Course code, Section,
  Semester,Year, Instructor)
• Course code is not a key since more than one
  section has same course code
• cs2 (CS501, 70, Fall, 2001, Pevac)
• cs3 (CS501, 71, Spring, 2002, Calvert)
• Section is not a key since different sections
  have same value for Section
• cs1 (CS580, 70, Spring,
  2002, Pevac)
• cs2 (CS501, 70, Fall,
  2001, Pevac)

19
Key Examples

• COURSE-SECTION( Course code, Section,
  Semester,Year, Instructor)
• Semester is not a key since in each semester we
  offer many course-sections
• cs1 (CS580, 70, Spring, 2002, Pevac)
• cs3 (CS501, 71, Spring, 2002, Calvert)
• Year is not a key since in each year we offer
  many course-sections
• cs1 (CS580, 70, Spring, 2002, Pevac)
• cs3 (CS501, 71, Spring, 2002, Calvert)
• Instructor is not a key since same instructor
  teaches many course-sections
• cs1 (CS580, 70, Spring, 2002, Pevac)
• cs2 (CS501, 70, Fall, 2001, Pevac)

20
Key examples

• COURSE-SECTION( Course code, Section, Semester,
  Year, Instructor)
• Course code, Section pair has unique values in
  one particular semester but not in time interval
  that spans throughout several semesters, so this
  pair is not a key
• cs1 (CS580, 70, Spring, 2002, Pevac)
• cs4 (CS580, 70, Fall, 2001, Pevac)

21
Key examples

• COURSE-SECTION( Course code, Section, Semester,
  Year, Instructor)
• Course code, Section, Semester triple has unique
  values in one particular year but not in time
  interval that spans throughout several years, so
  this pair is not a key
• cs1 (CS580, 70, Spring, 2002, Pevac)
• cs5 (CS580, 70, Spring, 2001, Pevac)

22
Key examples

• What is the key ???
• COURSE-SECTION( Course code, Section, Semester,
  Year, Instructor)
• Course code, Section, Semester, year quadruple
  has always unique values , so this is a key
• Specific values for this four attributes uniquely
  identifies one course section.
• No two sections of any course will have this four
  values the same
• cs1 (CS580, 70, Spring, 2002, Pevac)
• csx (CS580, 70, Spring, 2002, xxxxxx)
• no other section has values CS580, 70, Spring,
  2002

23
Key

• STUDENT(StudId, SSN, Name, GPA)
• StudId is key
• SSN is key
• StudId and SSN each individually can be key
• COURSE_SECTION( Course code, Section, Semester,
  Year, Instructor)
• Course code, Section, Semester, Year is key
• Course code, Section, Semester, Year as an
  4-tuple is a key

24
Attribute Domains

• Domain or value set is set of possible values for
  simple attribute
• Age for an EMPLOYEE has domain
• Set of integers between 16 and 70
• SSN for any PERSON has domain
• String consisting of three digits, dash, two
  digits, dash, four digits
• ddd-dd-dddd

25
Relationship Type

• A relationship type R among n entities is set of
  associations among instances of these entities.
• Let relationship type R between STUDENT and
  COURSE-SECTION be taking course section.
• Taking course section relationship defines which
  student takes what course section

26
Relationship Instances

• Relationship Instances ri for relationship R
  among n entities E1, E2, E3, En is an
  association of n entitiy instances where each
  entity instance belongs to exactly one on n
  participating entities.
• ri (e1, e2, e3,..,en) where ei is an entity
  instance from Ei

27
Relationship Instances

• Let relationship type R be
• taking course section
• E1 is STUDENT
• Let STUDENT instances Bill, Tom, Sue
• E2 is COURSE-SECTION
• Let COURSE-SECTION instances cs1, cs1
• Relationship Instances are
• (Bill, cs1), (Bill, cs2), (Tom,cs1), (Sue,cs2)
• Set of pairs specifies set of enrollments

28
Relationship Instances Examples

• Fig 3.9 page 54 Some instances of WORKS_FOR
  relationship between EMPLOYEE and DEPARTMENT
• Fig 3.10 page 55 Some relationship instances
  among entities SUPPLIER, PROJECT, and PART

29
Recursive Relationships

• When a relationship is between entities from the
  same entity set it is called recursive
  relationship
• Example Fig 3.11 page 56
• Same entity instance can be supervisor or
  supervisee
• e1 supervises e2 and e3
• e4 supervises e6 and e7
• e5 supervises e1 and e4

30
Recursive Relationship
31
Constraints on Relationship Types

• Business rules in the miniworld put constraints
  which limit the combinations of entities in the
  relationship set
• Rule Each student gets exactly one advisor
• Constraint No student instance is in advising
  relationship with more than one advisor instance

32
Advising Example
33
Cardinality Ratio for Binary Relationships

• Cardinality ratio specifies the number of
  relationship instances that an entity can
  participate in.
• STUDENT ADVISOR has cadinality ratio N1

34
Cardinality Ratio 11

• A president is a president of one country.
  Each country has one president
• PRESIDENT COUNTRY 11
• A car is assigned to exactly one employee. An
  employee can be assigned at most one car
• CAR EMPLOYEE 11
• A student writes a thesis at the end of the
  program
• Each thesis is authored by one student.
• STUDENT THESIS 11

35
Cardinality Ratio 1N or N1

• A senator serves as senator of one country. A
  country has several senators
• SENATOR COUNTRY N 1
• A patient is assigned to exactly one primary
  care physician. A physician is PCP for many
  patients
• PATIENT PHYSICIAN N 1
• A student writes several papers while in the
  program. Each paper is authored by one student.
• STUDENT PAPER 1 N

36
Cardinality Ratio NM

• A book may have several authors. The same author
  may write several books.
• BOOK AUTHOR NM
• A student takes several course sections. A course
  section has several students.
• COURSE-SECTION STUDENT NM
• A student may be member of several clubs. Each
  club has several students.
• STUDENT CLUB N M

37
Participation Constraints

• Total Participation means that every entity
  instance must participate in a relationship.
• EMPLOYEE is assigned a CAR
• Car has total participation since every instance
  of a car must have an employee to whom it is
  assigned
• Partial Participation means that some entity
  instances participate in a relationship and some
  do not.
• Employee has partial relationship since not every
  employee has a car assigned to him/her

38
E-R Diagrams

39
E-R Diagrams
40
Attributes of Relationship Types

• Relationship types can also have attributes.
• PRESIDENT serves as president of a COUNTRY
• start date, end date, vice president
  attributes for the relationship can be migrated
  to any of the two participating entities (11
  relationship)

41
Attributes of 1N Relationships

• SENATOR serves as a senator of a COUNTRY
• start date, end date attributes for the
  relationship can be migrated to SENATOR side
  entitiy (to the entity on the N side of 1N
  relationship)
• In this case it is the only possibility.
  Attributes cannot be moved to the other entity
  that is on 1 side of a relationship (COUNTRY)

42
Attributes of NM Relationships

• STUDENT is taking a COURSE-SECTION
• Attribute grade is an attribute for the
  relationship is taking. It cannot be moved to
  any of the two participating entities.
• A grade is given to an instance of student taking
  an instance of course-section.

43
Weak Entity Types

• Weak Entities are entity types that do not have
  key attributes of their own. They are identified
  by being related to specific entities from
  another entity type plus some of their
  attributes.
• The entity on which weak entity relies is called
  identifying entity or owner entity type.

44
Weak Entities Examples

• EMPLOYEE Dependent
• BOOK Edition
• SOFTWARE Release
• A weak entity type always has total participation
  constraint in a relationship
• A weak entity normally has a partial key, which
  is the set of attributes that can uniquely
  identify weak entities owned by the same owner
  entity.