Logical Database Design and the Relational Model

1
Logical Database Designand the Relational Model

• Chapter 6
• MIS 2403
• Dr. Segall
• Spring 2001

2
The Relational Data Model

• Introduced in a journal article written in 1970
  by E. F. Codd, a scientist with IBM
• Early research prototypes of relational systems
  were developed throughout the 1970s
• Commercial RDBMS emerged in the 1980s and came to
  dominate the database market, a situation that
  continues to the present time

3
Components of the Relational Data Model

• 1. Data Structure - Data are organized in
  two-dimensional tables (called relations) with
  rows and columns
• 2. Data Manipulation - Data stored in the tables
  may be manipulated through the use of a command
  language (e.g., SQL)
• 3. Data Integrity - Business rules may be defined
  that maintain the integrity of data that is
  manipulated

4
Properties of Relations

• Each relation in a given database has a unique
  name
• Each attribute within a given table has a unique
  name
• Every attribute value is atomic
  (single-valued)
• Every row is unique
• The order of the columns is irrelevant
• The order of the rows is irrelevant

5
Relational Keys

• Primary Key
• attribute whose value uniquely identifies
  (differentiates) each row in a relation (e.g.,
  Employee_ID)
• Composite Key - a primary key made up of more
  than one attribute (e.g., F_Name M_Name
  L_Name)
• Foreign Key
• attribute in one relation that serves as the
  primary key of another relation in the same
  database (i.e., linking field)

6
Product_Id
Customer_id
Order_Id
Customer
Product
Order
Consists
Makes
o
0
Order_date
Product_name
Product_price
Customer_name
city
address
First_name
Last_name
Middle_name
state
zip
7
Customer_id
Product_id
Product_id
Customer_id
Order_id
Order_id
Quantity
Customer
Product
Order Line
Order
Makes
o
0
Order_date
Product_name
Product_price
Customer_name
city
address
First_name
Last_name
Middle_name
state
zip
8
Example of Four Relations
Note the Primary Keys (underlined), including the
composite primary key, and the Foreign Keys
(either dashed underline or solid underline if
the foreign key is also part of a composite
primary key
9
Diagramming Referential Integrity Constraints
10
Well-Structured Relations

• A relation that contains minimal redundancy and
  allows users to insert, modify, and delete the
  rows in a table without errors or
  inconsistencies
• Thus, such relations avoid
• Insertion Anomalies
• Deletion Anomalies
• Update Anomalies

See next 3 slides and p. 217 for more on anomalies
11
Anomalies

• Insertion Anomalies
• are experienced when we attempt to store a value
  for one attribute but cannot because the value of
  another attribute is unknown
• e.g., cannot add a new customers information
  until an order number is ready to be entered

12
Anomalies

• Deletion Anomalies
• are experienced when a value for one attribute we
  wish to keep is unexpectedly removed when a value
  for another attribute is deleted
• e.g., cannot delete the sole order for a customer
  without deleting the only copy of the customers
  information also

13
Anomalies

• Update Anomalies
• are experienced when changes to multiple
  instances of an entity (rows of a table) are
  needed to effect an update to a single value of
  an attribute
• e.g., cannot completely update a customers
  address without changing it for every order
  placed by that customer

14
Integrity Constraints

• Domain Constraint
• constrains allowable values for an attribute
  (e.g., data type, field size, valid range)
• Entity Integrity
• prohibits null values for primary key
• Operational Constraint
• business rules
• Referential Integrity
• constrains a foreign key value to match a primary
  key value in a related table

15
Transforming E-R Diagrams into Relations

• 1. Map Regular Entities to Relations (Fig.
  6-8)
• Composite Attributes Use only their simple,
  component attributes (Fig. 6-9)
• Multivalued Attribute Becomes a separate
  relation with a foreign key taken from the
  superior entity (Fig. 6-10)

16
Mapping a Composite Attribute
(a) CUSTOMER entity type with composite attribute
17
Mapping a Multivalued Attribute
(a) EMPLOYEE entity type with multivalued
attribute
18
Transforming E-R Diagrams Into Relations

• 2. Map Weak Entities
• Becomes a separate relation with a foreign key
  taken from the superior entity (Fig. 6-11)

19
Example of mapping a weak entity
(a) Weak entity DEPENDENT
20
(b) Relations resulting from mapping weak entity
21
Transforming E-R Diagrams Into Relations

• 3. Map Binary Relationships
• One-to-Many - Primary key on the one side becomes
  a foreign key on the many side (Fig. 6-12)
• Many-to-Many - Create a new relation with the
  primary keys of the two entities as its primary
  key (Fig. 6-13)
• One-to-One - Primary key on the mandatory side
  becomes a foreign key on the optional side (Fig.
  6-14)

22
Example of mapping a 1M relationship
(a) Relationship between customers and orders
23
(b) Mapping the relationship
24
Example of mapping an MN relationship
(a) Requests relationship (MN)
25
(b) Three resulting relations
26
Mapping a binary 11 relationship
(a) Binary 11 relationship
27
(b) Resulting relations
28
Transforming E-R Diagrams Into Relations

• 4. Map Associative Entities
• Identifier Not Assigned
• Default primary key for the associative relation
  (also called the intersection table) is a
  composite key composed of the primary keys of the
  two entities (Fig. 6-15)
• Identifier Assigned
• May use if one is available that is natural and
  familiar to end-users
• Use if the default identifier may not be unique
  (Fig. 6-16)

29
Mapping associative entity with identifier not
assigned
(a) Order_Line as associative entity
ORDER LINE
Note similarity of this situation to the MN
relationship shown on slide 22
30
(b) Three resulting relations
31
Mapping an associative entity with an identifier
(a) Associative entity (SHIPMENT)
0
0
32
(b) Three resulting relations
33
Transforming E-R Diagrams Into Relations

• 5. Map Unary (Recursive) Relationships
• One-to-Many Recursive foreign key in the same
  relation (Fig. 6-17)
• Many-to-Many (e.g., bill-of-materials) Two
  relations
• One for the entity type
• One for an associative relation in which the
  primary key has two attributes, both taken from
  the primary key of the entity (Fig. 6-18)

34
Mapping a unary 1M relationship
(a) EMPLOYEE entity type with Manages relationship
35
Mapping a unary MN relationship
(a) Bill-of-materials relationship
36
Transforming E-R Diagrams Into Relations

• 6. Map Ternary (and n-ary) Relationships
• One relation for each entity and one for the
  associative entity (Fig. 6-19)

37
Mapping a ternary relationship
(a) Ternary relationship with associative entity
38
(b) Mapping the ternary relationship
39
Transforming E-R Diagrams Into Relations

• 7. Map Supertype/Subtype Relationships
• Create a separate relation for the supertype and
  each of the subtypes
• Assign common attributes to the supertype
• Assign to the subtypes the primary key of the
  supertype and those attributes unique to that
  subtype
• Assign attribute(s) to the supertype to function
  as subtype discriminator(s)
• (Fig. 6-20, 6-21)

40
Supertype/subtype relationships
41
Mapping Supertype/subtype relationships to
relations
42
Data Normalization is

• A formal process for grouping attributes into
  relations
• A tool to validate and improve logical designs so
  that they satisfy certain constraints to avoid
  unnecessary duplication of data
• The process of decomposing relations with
  anomalies to produce smaller, well-structured
  relations

43
Functional Dependency

• Functional Dependency The value of one attribute
  (the determinant) determines the value of other
  attributes
• A B

44
Steps in normalization (Fig. 6-22)
45
First Normal Form

• No multi-valued attributes
• Every attribute value is atomic

46
Second Normal Form

• 1NF and every non-key attribute is fully
  functionally dependent on the primary key (i.e.,
  no partial functional dependencies)
• This means that every non-key attribute must be
  defined by the entire key, not by only part of
  the key
• Note problem with Fig. 6-23b

47
Third Normal Form

• 2NF and no transitive dependencies (functional
  dependency between non-key attributes)
• Note problem with Fig. 6-24b(see next 4 slides)

48
Relation with transitive dependency
(a) SALES relation with simple data
49
(b) Transitive dependency in SALES relation
Note In the text, the Region and Salesperson
fields are reversed
50
Removing a transitive dependency
(a) Decomposing the SALES relation
51
(b) Relations in 3NF
52
Merging Relations(View Integration Problems)

• Synonyms Different names, same meaning(e.g.,
  Student_No and Student_ID)
• Homonyms Same name, different meanings(e.g.,
  Phone - home or work?)
• Transitive Dependencies Can create when merging
  two relations together (e.g., (Stu ID, Major)
  merged with (Stu ID, Advisor))
• Supertype/Subtype Use caution that you dont
  inappropriately generalize subtypes together

53
Logical Database Designand the Relational Model

• Chapter 6
• THE END!!!