CS580 Advanced Database Topics

1
CS580Advanced Database Topics

• Chapter 9
• E-R and EER ? Relations
• Irena Pevac

2
Relational Model Concepts

• Database is collection of relations
• A relation is a table of n-tuples
• All values in any one column are of the same data
  type
  
• Each row is different n-tuple
• The order of rows is unimportant
• The order of column is unimportant
• Each cell - the intersection of a row and a
  column has single value (no lists, no multiple
  values)

3
Relational Schema

• Relations schema specifies the name of relation
  and a list of its attributes separated by comas
  and enclosed in parenthesis.
  
• R(A1,A2,A3,An)
• STUDENT(StudId, Name, Phone, Address, Age, Gpa)

4
Domain

• Data type of values for one column is called a
  domain. It is a set of atomic indivisible
  values.
  
• Examples of domain values
• USA phone numbers the set of 10 digit numbers
  that are phone numbers in USA
  
• Gpa floating point value between 0 and 4.0
• Employee ages integers between 16 and 70.

5
Relational Concepts

• Dom(Ai) domain of an attribute Ai
• Degree of a relation the number of attributes
  in a relational schema
  
• Relation state r(R) is set of all tuples at some
  point in time
  
• r(R) t1,t2,t3,tk, ti are n-tuples
• Each tuple ti vi e dom(Ai)

6
E-R to Relational Mapping

• EMPLOYEE( EmpId, FirstName, LastName, Title)
• CAR(CarId, Make, Model, EmpId)
• EmpId is a foreign key in CAR

7
11 into Relation

• Each entity type E corresponds to a relation E.
• Each simple attribute from E is put into relation
  E
  
• Composite attribute A with simple attributes A1,
  A2,, An will have all simple attributes A1,
  A2,, An in relation E.
  
• For multivalued attribute new relation is added
  with attribute included plus key of the entity
  where it resides.
  
• To show relationship the key of one entity is put
  as a foreign key into relation corresponding to
  second entity.

8
EMPLOYEE-CAR

• If EMPLOYEE entity had phone that is multivalued
  composite attribute with Area code and Phone
  number in addition to EmpId, Name, Address
  
• EMPLOYEE( EmpID, FirstName, LastName, Title)
• PHONE(Area code, Phone number, EmpId)
• CAR(CarId, Make, Model, EmpId)

9
1N into Relations
10
1N into Relations

• COUNTRY(CountryName, Population)
• SENATOR(FirstName, LastName, Age, CountryName)
• Each entity has corresponding relation
• Each simple attribute is an attribute in that
  relation.
  
• Each simple attribute from composite attribute is
  an attribute in that relation.
  
• The relationship is represented with key from the
  entity on one side of the relationship put into
  relation corresponding to the entity on many side
  of the relationship.
• If any of the entities has multivalued attribute
  that would require additional relation.
  

11
NM into Relations
12

• Each entity has corresponding relation
• Each simple attribute from one entity is an
  attribute in that relation.
  
• Each simple attribute from a composite attribute
  is an attribute in that relation.
  
• The relationship is represented with another so
  called intersection relation. That relation
  contains the keys from the entities on both sides
  of the MN relationship plus any attributes from
  the relationship.
• If any of the entities has multivalued attribute
  that would require additional relation.

13
STUDENT COURSE_SECTION

• STUDENT(ssn, firstName, lastName)
• COURSESECTION(coursecode, sectionNumber,
  instructor)
  
• ENROLLMENT(ssn, courseCode, sectionNumber, grade)

14
WEAK ENTITIES INTO RELATIONS

• COURSESECTION is weak id-dependent entitiy
• COURSE has attributes DeptCode, CourseNumber,
  Title, Credits
  
• COURSESECTION has attributes DeptCode,
  CourseNumber, SectionNumber, Instructor
  
• COURSE (DeptCode, CourseNumber, Title, Credits)
• COURSESECTION (DeptCode, CourseNumber,
  SectionNumber, Instructor)

15
N-ary Relationship into Relations
16
N-ary Relationship into Relations

• Every entity has corresponding relation
• N-ary relationship has additional relation.
• This relation is a combination of foreign keys
  of the relations of all participating entities.
  It also contains any attributes coming from the
  relationship.

17
Supply Example

• SUPPLIER( SupplierId, SupplierName)
• PROJECT(ProjectId, ProjectTitle)
• PART(PartID, PartName, Description)
• SUPPLY(SupplierID, ProjectID, PartID)

18
Superclass/Subclass into Relations case 1

• Superclass S
• Subclasses S1, S2, ,Sn
• One relation for S with all attributes from S in
  it plus one or more flags
  
• One relation for each Si with attributes for Si
  where i e 1,2,,n
  
• The key from S is put into each relation Si
• This works for disjoint, overlapping, partial or
  total.
  

19
PERSON Example case 1

• PERSON has ssn and phone
• FEMALE has numberOfpregnancies
• MALE has armyServed, placeServed
• PERSON(ssn, areaCode, phone, personType)
• FEMALE(ssn, numberOfPregnancies)
• MALE(ssn, armyServed, placeServed)

20
Superclass/Subclasses into Relations case2

• Superclass S
• Subclasses S1, S2, ,Sn
• One relation for each Si with attributes for Si
  plus all attributes from S in each Si where i e
  1,2,,n
  
• This works for disjoint and total participation.
• For overlapping subtypes one instance will be in
  several tables
  
• For partial participation entity instance from
  supertype is not belonging to any of subtypes, so
  it does not have place in any of tables.
  

21
PERSON Example case 2

• PERSON has ssn and phone
• FEMALE has numberOfpregnancies
• MALE has armyServed, placeServed
• FEMALE(ssn, areaCode, phone, numberOfPregnancies)
  
• MALE(ssn, areaCode, phone, armyServed,
  placeServed)

22
Superclass/Subclass into Relations case 3

• Superclass S
• Subclasses S1, S2, ,Sn
• One relation containing all attributes for each
  Si plus all attributes from S plus SubtypeType
  attribute
  
• This works for disjoint participation.
• For overlapping subtypes one instance would have
  multiple values for Subtype Type attribute which
  should not happen

23
PERSON Example case 3

• PERSON has ssn and phone
• FEMALE has numberOfpregnancies
• MALE has armyServed, placeServed
• PERSON (ssn, areaCode, phone, numberOfPregnancies,
  armyServed, placeServed, PersonType)
  
• Problem are null values for numberOfPregnancies
  for male persons
  

24
Superclass/Subclasses into Relations case4

• Superclass S
• Subclasses S1, S2, ,Sn
• One relation containing all attributes for each
  Si plus all attributes from S plus one
  isSubtype-k-Type attribute for each subtype
  
• This works for overlapping participation.

25
STUDENT Example case 4

• STUDENT has ssn and phone
• STUDENT501 has tests and projects
• STUDENT580 has design, and SQL
• STUDENT (ssn, areaCode, phone, tests, projects,
  design,SQL, is501, is580)
  
• Student taking both cs501 and cs580 will have
  true, true.
  

26
Shared Subclasses into Relations

• Subclass Sab is a subclass of class A and of
  class B.
  
• These classes must have the same key attribute
  otherwise it would be modeled as a category.
  
• Use any of cases1,2,3, or 4 for subclasses.

27
ENGINEERING_MANAGER Example

• ENGINEERING_MANAGER is subtype of ENGINEER and a
  subtype of MANAGER and both ENGINEER and MANAGER
  are subtypes of EMPLOYEE.
  
• ssn is key for EMPLOYEE.
• ENGINEER has Title
• MANAGER has Number of supevisees
• EMPLOYEE( ssn, Title, Numsupervisees, is
  engineer, is manager)

28
Recursive Relationships 11

• PERSON SPONSORS PERSON
• Each sponsor only sponsors one person. Each
  person can only be sponsored by at most one
  person.
  
• Person set Bill, Tom, Sue, Anna
• Bill sponsors Tom
• Tom sponsors Sue
• Sue sponsors Anna

29
Recursive Relationships 11 example

• PERSON(Sponsor-ssn, Name,, Sponsored ssn)
• Each row will contain sponsor and all attributes
  for him/her plus sponsoree ssn.
  

30
Recursive Relationships 1N

• EMPLOYEE REFERS EMPLOYEE
• Each employee can refer several employees. Each
  refered employee can only have one referent.
  
• Employee set Bill, Tom, Sue, Anna, Tim
• Bill refers Tom, Sue
• Tom refers Bill, Anna
• Anna refers Tim

31
Recursive Relationships 1N into Relations

• REFERALS( Refered, Job, Referee)

32
Recursive Relationships NM into Relations

• EMPLOYEE REFERS EMPLOYEE
• Each employee can refer several employees. Each
  refered employee can have several referents.
  
• Employee set Bill, Tom, Sue, Anna, Tim
• Bill refers Tom, Sue, Anna
• Tom refers Bill, Anna
• Anna refers Tim, Tom

33
Recursive Relationships MN into Relations
34
Second table
35
Categories into Relations

• A category is a subclass of the union of two or
  more superclasses that have different keys and
  are of different type.
  
• For category we introduce new key attribute
  called a surrogate key and put it into every
  relation.
  
• W have one relation for category plus one
  relation for each supertype.

36
OWNER example

• OWNER(OwnerId, OwnerType, )
• PERSON(ssn, DriversLicenceNo, Name, HomeAddress,
  OwnerId)
  
• BANK( BankName, BankAddress, OwnerId)
• COMPANY(CompanyName, CompanyType, OwnerId)
• Surogate key is added into all relations.