ITEC 3220M Using and Designing Database Systems - PowerPoint PPT Presentation

Loading...

PPT – ITEC 3220M Using and Designing Database Systems PowerPoint presentation | free to download - id: 8b354-ZDc1Z



Loading


The Adobe Flash plugin is needed to view this content

Get the plugin now

View by Category
About This Presentation
Title:

ITEC 3220M Using and Designing Database Systems

Description:

Next, include the primary key attribute of the entity on the one-side of the ... Then a foreign key attribute is added within the same relation that references ... – PowerPoint PPT presentation

Number of Views:83
Avg rating:3.0/5.0
Slides: 59
Provided by: mathY
Learn more at: http://www.math.yorku.ca
Category:

less

Write a Comment
User Comments (0)
Transcript and Presenter's Notes

Title: ITEC 3220M Using and Designing Database Systems


1
ITEC 3220M Using and Designing Database Systems
  • Instructor Prof. Z. Yang
  • Course Website http//people.yorku.ca/zyang/itec
    3220m.htm
  • Office Tel 3049

2
Supertypes and Subtypes
  • Generalization hierarchy depicts relationships
    between higher-level supertype and lower-level
    subtype entities
  • Supertype contains the shared attributes
  • Subtype contains the unique attributes
  • Inheritance
  • Subtype entities inherit values of all attributes
    of the supertype
  • An instance of a subtype is also an instance of
    the supertype

3
Supertypes and Subtypes (Contd)
Supertype/ subtype relationships
General entity type
SUPERTYPE
And so forth
SUBTYPE1
SUBTYPE2
Specialized version of supertype
4
Supertypes and Subtypes (Contd)
  • Disjoint relationships
  • Unique subtypes
  • Non-overlapping
  • Indicated with a G
  • Overlapping subtypes
  • An instance of the supertype could be more than
    one of the subtypes
  • Indicated with a Gs

5
Generalization Hierarchy with Overlapping Subtypes
6
Chapter 5
  • Logical Database Design and Normalization of
    Database Tables

7
In this chapter, you will learn
  • How to transform ERD into relations
  • What normalization is and what role it plays in
    database design
  • About the normal forms 1NF, 2NF, 3NF, BCNF, and
    4NF
  • How normal forms can be transformed from lower
    normal forms to higher normal forms
  • Normalization and E-R modeling are used
    concurrently to produce a good database design
  • Some situations require denormalization to
    generate information efficiently

8
Transforming ERD into Relations
  • Step one Map regular entities
  • Each regular entity type in an ER diagram is
    transformed into a relation
  • The name given to the relation is generally the
    same as the entity type
  • Each simple attribute of the entity type becomes
    an attribute of the relation and the identifier
    of entity becomes the primary key of the
    corresponding relation

9
Example
STUDENT
Student_ID Student_Name Other_Attributes
10
Transforming ERD into Relations (Contd)
  • Step two Map weak entities
  • Create a new relation and include all of the
    simple attributes as the attributes of this
    relation. Then include the primary key of the
    identifying relation as a foreign key attribute
    in this new relation. The primary key of the new
    relation is the combination of this primary key
    of the identifying relation and the partial
    identifier of the weak entity type.

11
Example
EMPLOYEE
Employee_ID Employee_Name
DEPENDENT
Dependent_Name Employee_ID Date_of_birth Gender
12
Transforming ERD into Relations (Contd)
  • Step three Map binary relationship
  • Map Binary one-to-many relations
  • First create a relation for each of the two
    entity types participating in the relationship,
    using the procedure described in step one.
  • Next, include the primary key attribute of the
    entity on the one-side of the relationship as a
    foreign key in the relation that is on the
    many-side of the relationship.

13
Example
M
Customer
Customer_ID Customer_Name
Order
Order_ID Order_Date Customer_ID
14
Transforming ERD into Relations (Contd)
  • Step three Map binary relationship (Contd)
  • Map binary one-to-one relationships
  • First, two relationships are created one for each
    of the participating entity types.
  • Second, the primary key of one of the relations
    is included as a foreign key in the other
    relation.

15
Example
Centre_Name
1
1
Care Centre
Nurse
(0,1)
(1,1)
Nurse_ID Nurse_Name
Nurse
Care Centre
Centre_Name Location Nurse_in_charge
16
Transforming ERD into Relations (Contd)
  • Step Four Map composite Entities
  • First step
  • Create three relations one for each of the two
    participating entities, and the third for the
    composite entity. We refer to the relation formed
    from the composite entity as the composite
    relation
  • Second step
  • Identifier not assigned The default primary key
    for the composite relation consists of the two
    primary key attributes from the other two
    relations.
  • Identifier assigned The primary key for the
    composite relation is the identifier. The primary
    keys for the two participating entity types are
    included as foreign keys in the composite
    relation.

17
Example
1
(1,N)
(1,1)
M
M
(1,1)
1
(0,N)
18
Example
Order
Order_ID Order_Date
Order Line
Product_ID Order_ID Quantity
Product
Product_ID Description Standard_Price
19
Example
M
Customer_ID Customer_Name
Customer
Shipment_No Vendor_ID Customer_ID Date Amount
Shipment
Vendor
Vendor_ID Address
20
Transforming ERD into Relations (Contd)
  • Step Five Map unary relationship
  • Map unary one-to-may relationship
  • The entity type in the unary relationship is
    mapped to a relation using the procedure
    described in Step one. Then a foreign key
    attribute is added within the same relation that
    references the primary key values. A recursive
    foreign key is a foreign key in a relation that
    references the primary key values of that same
    relation.

21
Example
Employee
Employee_ID Name Birthdate Manager_ID
22
Transforming ERD into Relations (Contd)
  • Step six Map ternary relationship
  • Convert a ternary relationship to a composite
    entity
  • To map a composite entity that links three
    regular entities, we create a new composite
    relation. The default primary key of their
    relation consists of the three primary key
    attributes for the participating entities. Any
    attributes of the composite entity become
    attributes of the new relation

23
Example
(0,N)
1
(0,N)
1
(1,1)
(1,1)
M
M
(1,1)
M
(0,N)
1
24
Example
Patient_ID Patient_Name
Physician_ID Physician_Name
Patient_ID Physician_ID Treatment_Code Date Time Result
Treatment_Code Description
25
Transforming ERD into Relations (Contd)
  • Step seven Map supertype/subtype relationships
  • Create a separate relation for the supertype and
    for each of its subtype
  • Assign to the relation created for the supertype
    the attributes that are common to all members of
    the supertype, including the primary key
  • Assign to the relation for each subtype the
    primary key of the supertype, and only those
    attributes that are unique to that subtype
  • Assign one attribute of the supertype to function
    as the subtype discriminator

26
Example
27
Example
Employee_Number Employee_Name Address Employee_Type Date_Hired
H_Employee_Number Hourly_Rate
S_Employee_Number Annual_Salary Stock_Option
28
Database Tables and Normalization
  • Table is the basic building block in database
    design
  • Normalization is the process for assigning
    attributes to entities
  • Reduces data redundancies
  • Helps eliminate data anomalies
  • Produces controlled redundancies to link tables
  • Normalization stages
  • 1NF - First normal form
  • 2NF - Second normal form
  • 3NF - Third normal form
  • 4NF - Fourth normal form

29
Need for Normalization
30
Anomalies In the Table
  • PRO_NUM intended to be primary key
  • Table displays data anomalies
  • Update
  • Modifying JOB_CLASS
  • Insertion
  • New employee must be assigned project
  • Deletion
  • If employee deleted, other vital data lost

31
Conversion to First Normal Form
  • Repeating group
  • Derives its name from the fact that a group of
    multiple entries of same type can exist for any
    single key attribute occurrence
  • Relational table must not contain repeating
    groups
  • Normalizing table structure will reduce data
    redundancies
  • Normalization is three-step procedure

32
Conversion to First Normal Form (continued)
  • Step 1 Eliminate the Repeating Groups
  • Present data in tabular format, where each cell
    has single value and there are no repeating
    groups
  • Eliminate repeating groups, eliminate nulls by
    making sure that each repeating group attribute
    contains an appropriate data value

33
Conversion to First Normal Form (continued)
34
Conversion to First Normal Form (continued)
  • Step 2 Identify the Primary Key
  • Primary key must uniquely identify attribute
    value
  • New key must be composed

35
Conversion to First Normal Form (continued)
  • Step 3 Identify all dependencies
  • Dependencies can be depicted with help of a
    diagram
  • Dependency diagram
  • Depicts all dependencies found within given table
    structure
  • Helpful in getting birds-eye view of all
    relationships among tables attributes
  • Makes it less likely that will overlook an
    important dependency

36
Conversion to First Normal Form (continued)
37
Conversion to First Normal Form (continued)
  • First normal form describes tabular format in
    which
  • All key attributes are defined
  • There are no repeating groups in the table
  • All attributes are dependent on primary key
  • All relational tables satisfy 1NF requirements
  • Some tables contain partial dependencies
  • Dependencies based on only part of the primary
    key
  • Sometimes used for performance reasons, but
    should be used with caution
  • Still subject to data redundancies

38
Conversion to Second Normal Form
  • Relational database design can be improved by
    converting the database into second normal form
    (2NF)
  • Two steps

39
Conversion to Second Normal Form (continued)
  • Step 1 Write Each Key Component on a Separate
    Line
  • Write each key component on separate line, then
    write original (composite) key on last line
  • Each component will become key in new table

40
Conversion to Second Normal Form (continued)
  • Step 2 Assign Corresponding Dependent Attributes
  • Determine those attributes that are dependent on
    other attributes
  • At this point, most anomalies have been eliminated

41
Conversion to Second Normal Form (continued)
42
Conversion to Second Normal Form (continued)
  • Table is in second normal form (2NF) when
  • It is in 1NF and
  • It includes no partial dependencies
  • No attribute is dependent on only portion of
    primary key

43
Conversion to Third Normal Form
  • Data anomalies created are easily eliminated by
    completing three steps
  • Step 1 Identify Each New Determinant
  • For every transitive dependency, write its
    determinant as PK for new table
  • Determinant
  • Any attribute whose value determines other values
    within a row

44
Conversion to Third Normal Form (continued)
  • Step 2 Identify the Dependent Attributes
  • Identify attributes dependent on each determinant
    identified in Step 1 and identify dependency
  • Name table to reflect its contents and function

45
Conversion to Third Normal Form (continued)
  • Step 3 Remove the Dependent Attributes from
    Transitive Dependencies
  • Eliminate all dependent attributes in transitive
    relationship(s) from each of the tables that have
    such a transitive relationship
  • Draw new dependency diagram to show all tables
    defined in Steps 13
  • Check new tables as well as tables modified in
    Step 3 to make sure that each table has
    determinant and that no table contains
    inappropriate dependencies

46
Conversion to Third Normal Form (continued)
47
Conversion to Third Normal Form (continued)
  • A table is in third normal form (3NF) when both
    of the following are true
  • It is in 2NF
  • It contains no transitive dependencies

48
The Boyce-Codd Normal Form (BCNF)
  • Every determinant in table is a candidate key
  • Has same characteristics as primary key, but for
    some reason, not chosen to be primary key
  • When table contains only one candidate key, the
    3NF and the BCNF are equivalent
  • BCNF can be violated only when table contains
    more than one candidate key

49
The Boyce-Codd Normal Form (BCNF) (continued)
  • Most designers consider the BCNF as special case
    of 3NF
  • Table is in 3NF when it is in 2NF and there are
    no transitive dependencies
  • Table can be in 3NF and fails to meet BCNF
  • No partial dependencies, nor does it contain
    transitive dependencies
  • A nonkey attribute is the determinant of a key
    attribute

50
The Boyce-Codd Normal Form (BCNF) (continued)
51
The Boyce-Codd Normal Form (BCNF) (continued)
52
An Example
  • GRADE( Student_ID, Student_Name, Address, Major,
    Course_ID, Course_Title, Instructor_Name,
    Instructor_Office, Grade)

53
Normalization and Database Design
  • Normalization should be part of the design
    process
  • E-R Diagram provides macro view
  • Normalization provides micro view of entities
  • Focuses on characteristics of specific entities
  • May yield additional entities
  • Difficult to separate normalization from E-R
    diagramming
  • Business rules must be determined

54
Higher-Level Normal Forms
  • Fourth Normal Form (4NF)
  • Table is in 3NF
  • Has no multiple sets of multivalued dependencies

55
Conversion to 4NF
Stud-ID Course Service
1126 1212F Red Cross
1126 1620F United Way
1126 1320F
Stud-ID Course
1126 1212F
1126 1620F
1126 1320F
Stud-ID Course Service
1126 1212F
1126 1620F
1126 1320F
1126 Red Cross
1126 United Way
Stud-ID Service
1126 Red Cross
1126 United Way
Set of Tables in 4NF
Stud-ID Course Service
1126 1212F Red Cross
1126 1620F United Way
1126 1320F
Multivalued Dependencies
56
Denormalization
  • Normalization is one of many database design
    goals
  • Normalized table requires
  • Additional processing
  • Loss of system speed
  • Normalization purity is difficult to sustain due
    to conflict in
  • Design efficiency
  • Information requirements
  • Processing

57
Exercise
Part Supplier Data
Part_No Description Vendor_Name Address Unit_Cost
1234 Logic Chips Fast Chips Smart Chips Cupertino Phoenix 10.00 8.00
5678 Memory Chips Fast Chips Quality Chips Smart Chips Cupertino Austin Phoenix 3.00 2.00 5.00
58
Exercise(Contd)
  • Convert the table to a relation in first normal
    form (Named Part Supplier)
  • List the functional dependency in the Part
    Supplier and identify a candidate key
  • For the relation Part Supplier, identify the
    followings an insert anomaly, a delete anomaly,
    and a modification anomaly.
  • Draw a relation schema and show the functional
    dependencies
  • Develop a set of 3NF relations from Part Supplier
About PowerShow.com