Chapter 3: Relational Model I

1
Chapter 3 Relational Model I

• Structure of Relational Databases
• Convert a ER Design to a Relational Database

2
Relation

• Another name for table
• Columns attributes
• Rows tuples
• Content of a table instance of a relation

3
Attribute Types

• Each attribute of a relation has a name
• The set of allowed values for each attribute is
  called the domain of the attribute
• Attribute values are (normally) required to be
  atomic, that is, indivisible
• E.g. multivalued attribute values are not atomic
• E.g. composite attribute values are not atomic
• The special value null is a member of every
  domain

4
Example of a Relation
5
Formally

• Given sets D1, D2, . Dn a relation r is a subset
  of D1 x D2 x x DnThus a relation is a set of
  n-tuples (a1, a2, , an) where each ai ? Di

6
Relation Relates Things

• Things
• customer-name Jones, Smith, Curry,
  Lindsay customer-street Main, North,
  Park customer-city Harrison, Rye,
  Pittsfield
• Relation
• Then r (Jones, Main, Harrison),
  (Smith, North, Rye), (Curry,
  North, Rye), (Lindsay, Park,
  Pittsfield) is a relation over customer-name x
  customer-street x customer-city

7
Relation Schema

• A1, A2, , An are attributes
• R (A1, A2, , An ) is a relation schema
• E.g. Customer-schema
  (customer-name, customer-street, customer-city)
• r(R) is a relation on the relation schema R
• E.g. customer (Customer-schema)

8
Relation Instance

• The current values (relation instance) of a
  relation are specified by a table
• An element t of r is a tuple, represented by a
  row in a table

attributes (or columns)
customer-name
customer-street
customer-city
Jones Smith Curry Lindsay
Main North North Park
Harrison Rye Rye Pittsfield
tuples (or rows)
customer
9
Relations are Unordered

• Order of tuples is irrelevant (tuples may be
  stored in an arbitrary order)

10
Database

• In relational database, a database consists of
  many relations
• Both things and their relationships are
  represented by relations
• Normalization theory (Chapter 7) deals with how
  to design relational schemas

11
Keys

• Let K ? R
• K is a superkey of R if values for K are
  sufficient to identify a unique tuple of each
  possible relation r(R)
• by possible r we mean a relation r that could
  exist in the enterprise we are modeling.
• Example customer-name, customer-street and
  customer-name are both superkeys
  of Customer, if no two customers can possibly
  have the same name.

12
Candidate Keys

• K is a candidate key if K is minimal
• Example customer-name is a candidate key for
  Customer, since it is a superkey (assuming no two
  customers can possibly have the same name), and
  no subset of it is a superkey.

13
Convert ER to Relational Database

• Entity relation
• Attributes attributes
• Primary key primary key
• Relationship relation
• Attributes attributes
• We will talk about primary key later
• Weak entity set relation
• Attributes attributes
• We will talk about primary key later

14
Representing Entity Sets as Tables

• A strong entity set reduces to a table with the
  same attributes.
• The primary key of the entity set becomes the
  primary key of the relation.

15
(No Transcript)
16
Composite Attributes

• Composite attributes are flattened out by
  creating a separate attribute for each component
  attribute
• E.g. given entity set customer with composite
  attribute name with component attributes
  first-name and last-name the table corresponding
  to the entity set has two attributes
  name.first-name and name.last-name

17
Multivalued Attributes

• A multivalued attribute M of an entity E is
  represented by a separate table EM
• Table EM has attributes corresponding to the
  primary key of E and an attribute corresponding
  to multivalued attribute M
• E.g. Multivalued attribute dependent-names of
  employee is represented by a table
  employee-dependent-names( employee-id, dname)
• Each value of the multivalued attribute maps to a
  separate row of the table EM
• E.g., an employee entity with primary key John
  and dependents Johnson and Johndotir maps to
  two rows (John, Johnson) and (John,
  Johndotir)

18
Example

• The relation(s) the ER mapped to?
• customer(customer-id, first-name, last-name,
  middle-initial,date-of-birth,age,street-number,str
  eet-name,apartment-number,city,state,zip-code)
• customer-phone(customer-id,phone-number)

19
Representing Weak Entity Sets

• A weak entity set becomes a table that includes a
  column for the primary key of the identifying
  strong entity set
• The primary key of the relation consists of the
  union of the primary key of the strong entity set
  and the discriminator of the weak entity set.

20
Weak Entity Example
21
Representing Relationship Sets as Tables

• A many-to-many relationship set is represented as
  a table with attributes from the primary keys of
  the two participating entity sets, and any
  descriptive attributes of the relationship set.
• E.g. table for relationship set borrower
• The union of the primary keys of the related
  entity sets becomes a super key of the relation.

22
Many-to-many relationship What is the
relationship borrower has an attribute date?
23
Representing Relationship Sets as Tables

• Many-to-one and one-to-many relationship sets
  that are total on the many-side can be
  represented by adding an extra attribute to the
  many side, containing the primary key of the one
  side
• E.g. Instead of creating a table for
  relationship account-branch, add an attribute
  branch to the entity set account
• the primary key of the many entity set becomes
  the primary key that represents the relationship
  and the many side
• If participation is partial on the many side,
  replacing a table by an extra attribute in the
  relation corresponding to the many side could
  result in null values

24
Redundancy!
account(account-number,balance) branch(branch-name
,branch-city,assets) account-branch(account-number
,branch-name)
?
account(account-number,balance,branch-name) branch
(branch-name,branch-city,assets)
25
Representing Relationship Sets as Tables

• For one-to-one relationship sets, either side can
  be chosen to act as the many side
• That is, extra attribute can be added to either
  of the tables corresponding to the two entity
  sets

26
Determining Keys from E-R Sets

• Strong entity set. The primary key of the entity
  set becomes the primary key of the relation.
• Weak entity set. The primary key of the relation
  consists of the union of the primary key of the
  strong entity set and the discriminator of the
  weak entity set.

27
Determining Keys from E-R Sets

• Relationship set. The union of the primary keys
  of the related entity sets becomes a super key of
  the relation.
• For binary many-to-one relationship sets, the
  primary key of the many entity set becomes the
  primary key that represent both the relationship
  and the many side. Why?
• What about one-to-one relationship sets. Why?
• For many-to-many relationship sets, the union of
  the primary keys becomes the relations primary
  key. Why?

28
Representing Specialization as Tables

• Method 1
• Form a table for the higher level entity
• Form a table for each lower level entity set,
  include primary key of higher level entity set
  and local attributes

29

• Drawback getting information about, e.g.,
  employee requires accessing two tables

Person(name, street, city) Customer(name,
credit-rating) Employee(name, salary)
30
Representing Specialization as Tables

• Method 2
• Form a table for each entity set with all local
  and inherited attributes
• If specialization is total, table for generalized
  entity (person) not required to store information
• Can be defined as a view relation containing
  union of specialization tables
• But explicit table may still be needed for
  foreign key constraints

31

• Drawback street and city may be stored
  redundantly for persons who are both person and
  customers/employees

Person(name, street, city) Customer(name, street,
city,credit-rating) Employee(name,
street,citysalary)
32
ER for Banking Enterprise
33
Schema Diagram for the Banking Enterprise
34

• Convert the ER diagram to relational models.