Migrating Data from a Table on the Many Side of a M:1 Relationship - PowerPoint PPT Presentation

1 / 15
About This Presentation
Title:

Migrating Data from a Table on the Many Side of a M:1 Relationship

Description:

Ensure name and data type match corresponding primary keys. except that these are not autonumber ... SET tblWorker1.RankID = [tblRank1].[RankID]; Migrating Data, ... – PowerPoint PPT presentation

Number of Views:20
Avg rating:3.0/5.0
Slides: 16
Provided by: Steve49
Category:

less

Transcript and Presenter's Notes

Title: Migrating Data from a Table on the Many Side of a M:1 Relationship


1
Migrating Data from a Table on the Many Side of
a M1 Relationship
  • If you came to this presentation via a web
    browser,
  • right-click and choose Full Screen before
    proceeding.
  • Click mouse or press space bar to continue.
  • This presentation was prepared by Professor Steve
    Ross, with the advice of other MIS Faculty, for
    use in MIS Classes at Western Washington
    University. Please contact Dr. Ross for
    permission to use in other settings..

2
The Original Table
  • What entities have been combined in this table?
  • What are the attributes of each entity?

3
Desired Structure
4
The Challenge
  • Extract data from the original worker table,
    creating tables for rank and department
  • Insert foreign key values in place of fields
    migrated from the original table

5
Migrating Data, Step 1
  • Map fields from current table to new table(s)
  • WorkerID autonumber, keep in current table
  • LastName keep in current table
  • FirstName keep in current table
  • AnnualPayIn1000 keep in current table
  • Rank will move to tblRank
  • BudgetAuthorityIn1000 will move to tblRank
  • PersonalStaff will move to tblRank
  • DepartmentName will move to tblDepartment

6
Migrating Data, Step 2
  • Specify primary and foreign keys
  • tblRank
  • RankID PK, autonumber
  • tblDepartment
  • DepartmentID PK, autonumber
  • tblWorker
  • WorkerID PK, autonumber
  • RankID FK replaces Rank
  • DepartmentID FK replaces DepartmentName

7
Migrating Data, Step 3
  • Create the new table(s)
  • In Access, use design view
  • Otherwise, CREATE TABLE command

8
Migrating Data, Step 4
  • Create and execute an action query to insert into
    a new table distinct combinations of the fields
    from the original table
  • INSERT INTO tblRank1 (Rank, BudgetAuthorityIn1000,
    PersonalStaff)
  • SELECT DISTINCT Rank, BudgetAuthorityIn1000,Person
    alStaff
  • FROM tblWorkerV1

9
Migrating Data, Step 5
  • Open the new table and check the results

10
Migrating Data, Step 6
  • If you wish to preserve the original table, copy
    and paste it under a new name before proceeding.
  • Add the foreign key fields to the original table
    structure (or copy thereof).
  • Ensure name and data type match corresponding
    primary keys
  • except that these are not autonumber
  • No default value should be assigned, erase any
    that is automatically created

11
Migrating Data, Step 6 (contd)
DELETE THIS!
12
Migrating Data, Step 7
  • Create and execute an UPDATE query to insert FK
    values
  • Join the tables based on all fields copied to the
    new table
  • UPDATE tblWorker1 INNER JOIN tblRank1 ON
    (tblWorker1.PersonalStaff tblRank1.PersonalStaff
    ) AND (tblWorker1.BudgetAuthorityIn1000
    tblRank1.BudgetAuthorityIn1000) AND
    (tblWorker1.Rank tblRank1.Rank)
  • SET tblWorker1.RankID tblRank1.RankID

13
Migrating Data, Step 8a
  • Open the original table (or copy) and check the
    results of the update command

14
Migrating Data, Step 8b
  • Remove extraneous columns from the table

15
Migrating Data, Step 9
  • Update Relationships and create queries to
    replace references to original table
Write a Comment
User Comments (0)
About PowerShow.com