Relational Database

1
Relational Database

• IST 210
• Spring 2001

2
Dr. Edgar F. (Ted) Codd

• To fellow pilots and aircrew in the Royal Air
  Force during World War II and the dons at Oxford
  These people were the source of my
  determination to fight for what I believed was
  right during the ten or more years in which
  government, industry, and commerce were strongly
  opposed to the relational approach to database
  management.

3
Relational Definitions

• Relation named, 2-dimensional table of data
• Every table has a unique name.
• Every attribute value is atomic (single-value)
• Every row is unique.
• Attributes in tables have unique names.
• The order of the columns is irrelevant.
• The order of the rows is irrelevant.

4
Relational Keys and Structures

• Primary Key an attribute (or combination of
  attributes) that uniquely identifies each row
  (record).
• Composite Key primary key that is made up of
  more than one attribute.
• Foreign Key an attribute in a table of a
  database that serves as the primary key of
  another table.

5
Schema for four tables (Pine Valley Furniture)
6
Instance of a relational database
7
Integrity Constraints (1 of 2)

• Domain Constraints
• Allowable values for an attribute.
• All values that appear in column of table must
  come from same domain.
• Entity Integrity
• No primary key attribute may be null.
• Operational Constraints
• Business rules.

8
Integrity Constraints (2 of 2)

• Referential Integrity rule that states that
  each foreign key value must match a primary key
  value in the other table or else the foreign key
  value must be null.
• Maintains consistency among tables.
• Example Inserting new records (slide 10)

9
Example of Domain Definitions
10
Referential integrity constraints (Pine Valley
Furniture)
11
Well-Structured Tables

• A table that contains minimal redundancy, and
  lets users insert, change, and delete records
  without errors or inconsistencies.

12
Anomalies

• Anomaly error or inconsistency that occurs when
  user tries to update a table with redundant data
  (pg. 211, 6-2b)
• Insertion Anomaly
• Deletion Anomaly
• Modification Anomaly

13
Transforming E-R Diagrams into Tables

• Map Regular Entities to Tables (Fig. 6-8).
• Composite attributes Use only their simple,
  component attributes (Fig. 6-9, next slide).
• Multi-valued Attribute - Becomes a separate table
  with a foreign key taken from the superior entity
  (Fig. 6-10).

14
Mapping a composite attribute
(a) CUSTOMER entity type with composite attribute
15
(b) CUSTOMER table with address detail
16
Transforming E-R Diagrams Into Tables

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

17
Example of mapping a weak entity
(a) Weak entity DEPENDENT
18
(b) tables resulting from weak entity
19
Transforming E-R Diagrams Into Tables

• 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 table 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).

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

• 4. Map Associative Entities
• Identifier Not Assigned
• Default primary key for the association relation
  is the primary keys of the two entities (Fig.
  6-15).
• Identifier Assigned
• It is natural and familiar to end-users.
• Default identifier may not be unique.
• (Fig. 6-16).

27
Mapping an associative entity with an identifier
(a) Associative entity (SHIPMENT)
28
(b) Three tables
29
Transforming E-R Diagrams Into Tables

• 5. Map Unary Relationships
• One-to-Many - Recursive foreign key in the same
  table that references the primary key values
  (Fig. 6-17).
• Many-to-Many - Bill-of-materials Two tables
• 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).

30
Transforming E-R Diagrams Into Relations

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

31
Mapping a ternary relationship
(a) Ternary relationship with associative entity
32
(b) Mapping the ternary relationship
33
Data Normalization

• Primarily a tool to validate and improve a
  logical design so that it satisfies certain
  constraints that avoid unnecessary duplication of
  data.
• The process of decomposing relations with
  anomalies to produce smaller, well-structured
  tables.

34
Functional Dependencies

• Functional Dependency A constraint between 2
  attributes or 2 sets of attributes.
• The value of one attribute (the determinant)
  uniquely determines the value of another
  attribute. Figure 6-23.

35
Keys

• Candidate Key an attribute or combination of
  attributes that uniquely identifies a row in a
  table.
• Unique identification the value of the key is
  unique for every row in the table
• Nonredundancy no attribute in the key can be
  deleted without destroying the property of unique
  identification.
• Each non-key field is functionally dependent on
  every candidate key.

36
Steps in normalization
37
First Normal Form (1NF)

• No multi-valued attributes.
• Every attribute value is atomic.
• Fig. 6-2a, b. EMPLOYEE2 is in 1NF.

38
Second Normal Form (2NF)

• 1NF and every non-key attribute is fully
  functionally dependent on the primary key.
• Every non-key attribute must be defined by the
  entire key, not by only part of the key.
• No partial functional dependencies.
• Fig. 6-2b, 6-23b.

39
Third Normal Form (3NF)

• 2NF and no transitive dependencies (functional
  dependency between non-key attributes.)
• Fig. 6-23, 6-24, 6-25.

40
table with transitive dependency
(a) SALES table with simple data
41
(b) Transitive dependency in SALES table
42
Removing a transitive dependency
(a) Decomposing the SALES table
43
(b) Relations in 3NF
44
Merging Tables(View Integration)

• Why merge tables?
• Teams with separate E-R diagrams
• Integrating existing databases with new
  information requirements, view integration
• New data requirements that are introduced during
  the database life cycle, after database has been
  developed.

45
View Integration Problems

• Synonyms Different names, same meaning.
• Homonyms Same name, different meanings.
• Transitive Dependencies e.g. (Stu_ID, Major)
  (Stu_ID, Advisor).