Relational%20Data%20Models

1
Relational Data Models
2
Relational Model

• The relational model represents information in
  tables (called relations)
• Each table represents a set of entities
• Each column of a table represents the attribute
  values of the entities
• Each row of a table represents a single entity
• The relational model consists of relational
  schemas which specify the structures of the
  tables

3
Relational Model

• A database schema is a collection of table
  definitions
• The info. about the structure of the tables is
  called metadata

4
Relational model

• A relational table is a set of rows each
  representing the values of a specific collection
  of attributes of a single entity
• All attributes (composite, multi-valued) must be
  translated into atomic attributes. This
  simplifies accessing and manipulating the
  information

5
Relational model constraints

• Atomicity In a relational model, all attributes
  are single valued (atomic)
• Key constraints Every table (relation) must have
  a key
• No 2 rows have the same values for all of the
  attribute
• A key declaration is a constraint
• A key cannot have a NULL value
• A table is not allowed to have 2 different rows
  that have the same value for the key
• Database systems enforce key constraints
• By blocking any attempt to modify a table that
  will result in a violation of the key constraint

6
Translating E-R diagrams

• We will translate the E-R diagram into relational
  model by mapping entity classes and relation
  types into relational schemas
• We will follow some specific rules during the
  translation process
• We will map each of the E-R components into a
  relational schema

7
Translating E-R diagrams

• Entity classes
• Simple attributes
• Composite attributes
• Key attributes
• Relationship types of different cardinalities
• Weak entity classes
• Multi-valued attributes

8
Representing Entity Classes

• For each strong entity classes in your E-R model
  create a relation schema
• Rule 1a Define a relation schema by the same
  name
• Rule 1b For each single-valued attribute of the
  entity class
• create an attribute by the same name in the
  relation schema and specify a type for the
  attribute
• Rule 1c Define the key of the new relation
  schema as the key of the entity class
• If the entity class key consists of multiple
  simple attributes, the key of the relation schema
  will be that set of attributes.
• Underline your selected key attribute in each
  schema in order to identify the key.

9
Representing Entity Classes Examples

• Schema Customer (account string, name string,
  address string, credit_card number, password
  number) Schema Movie (id number, title string,
  genre string, length number)

10
Representing Composite Attributes

• Rule 2. For each composite attribute of a strong
  entity class
• create an attribute in the relation schema for
  each component attribute
• If appropriate, use the name of the composite
  attribute as a prefix for each of the component
  attribute names
• Schema Customer (account string, lastName
  string, firstName string, street string, city
  string, state string, zipcode number, credit_card
  number, password number)

11
Derived Attributes

• The relational model does not directly support
  derived attributes
• The relational model supports derived attributes
  through the use of defined procedures

12
Representing Relationships

• Relationship will be represented in the
  relational model either by attributes or by
  tables
• The cardinality of the relationship plays an
  important role in deciding a suitable
  representation

13
One-to-many relationships

• For a one-to-many relationship type
• We can represent the relationship by adding an
  attribute called foreign key to the entity of
  cardinality one
• Rule 3 For each one-to-many relationship type R
  between subject class S and target class T
• add the key attributes of class S to class T as
  foreign keys
• Name the attributes using the role that S plays
  in relationship type R
• Add the attributes of the relationship type R to
  target class T.

14
One-to-many relationships
15
One-to-many relationships

• Referential integrity constraint For every
  entity in the class T the value of the foreign
  key should be the key of some entity in the class
  S
• Referential integrity constraints are enforced by
  the database system (Also during updates)

16
One-to-one relationships

• The foreign key attributes may be added to either
  schema
• Each entity class is to-one in the relationship
  type
• Choose which class to include the foreign key
  attributes
• One option is to try to minimize the number of
  null values
• Rule 4 For each one-to-one relationship type
  between two classes, choose one class to be the
  subject and one to be the target
• Add the key attributes of the subject class to
  the target schema as foreign key attributes
• Add the attributes of the relationship type to
  the target schema, just as in Rule 3

17
Many-to-many relationships

• Many-to-Many relationship types between 2 classes
  cannot be represented as simple attributes in
  either related table
• Rule 5 For each many-to-many relationship type R
  between classes S and T
• Create a new relation schema R
• Add attributes to represent the key of S and the
  key of T as foreign key attributes
• The key of schema R is the combination of those
  attributes
• Add the relationship attributes to schema R, as
  in Rule 3
• The new table is called the bridge or junction
  table

18
Many-to-many relationships

• Schema WorksIn
• (ssn string references Employee, storeId number
  references Store)

ssn storeId
358-44-7865 3
579-98-8778 5
358-44-7865 5
19
Weak Entity Classes

• Weak Entity classes have no keys of their own
• Create keys from
• Foreign keys of identifying relationship types
• Partial keys of the weak class
• Rule 6 For each weak entity class W
• Create a new relation schema with the same name
• For each identifying relationship,
• Add the key attributes of the related class to
  the new schema as foreign key attributes
• Declare the key of the schema to be the
  combination of
• the foreign key attributes and
• The partial key attributes of the weak entity
  class
• Add the simple and composite attributes of class
  W to the schema, as in Rules 2 and 3

20
Weak Entity Classes

• Schema Dependent (employeeSsn number, name
  string, relation string, Bdate string)

Ssn
Name
EMPLOYEE
111111
1
DEPENDENTS_OF
N
DEPENDENT
Name
Bdate
Relation
21
Multi-valued Attributes

• Represent each multi-valued attribute as if it
  were a weak entity class with
• identifying relationship with owner class
• All composite attributes of multi-valued
  attribute are partial keys
• Rule 7 For each multi-valued attribute M of an
  entity class C
• Define a new relation schema M
• Add the components of attribute M to the new
  schema
• Add the key attributes of the schema that
  contains the other attributes of C to M as a
  foreign key
• Define the key of the new schema to be the
  combination of all of its attributes

22
Multi-valued Attributes
23
Example

• EntitiesCustomer (key account , last name,
  first name, address, email, credit card,
  password)Movie (key id, title, genre, length)
• Sale (key id, cost, date, credit
  card)Shopping Cart (key id, date)

24
Example
Relationship Type Entity Class Entity Class Cardinality Ratio Attibutes
Purchases Customer Sale one-to-many
Includes Sale Movie many-to-many quantity
Selects Customer ShoppingCart one-to-many
Includes ShoppingCart Movie many-to-many quantity