dimanche 26 juin 2016

Normalizing a database

I'm new to databases and require some assistance. I am a bit lost but have laid down all the tables for UNF and would like to convert them to 3NF outlying the Primary Keys and Foreign Keys in each table. I just want to know if I've done this correctly. If not, can you assist me in seeing where I went wrong? There are some that I couldn't figure out how to go from 1NF to 2NF and just jumped straight to 3NF. I do not have any other human resources to direct my questions to so I will be greatful to any help I receive from you all. Please and Thank you all so very much.

1.

  • UNF

    • CustomerNumber (P)
    • CustomerName
    • InvoiceNumber (P)
    • InvoiceDate
    • ProductID (P)
    • ProductName
    • Price
    • Quantity
    • ProductEdition
  • 1NF

    • Customer =TABLE NAME
    • CustomerNumber = PRIMARY KEY
    • CustomerName
    • Invoice = TABLE NAME
    • InvoiceNumber = PRIMARYKEY
    • InvoiceDate
    • CustomerNumber = PRIMARY KEY
    • ProductID = PRIMARY KEY
    • Quantity
    • ProductName
    • Price
    • ProductEdition
  • 2NF

    • Customer = TABLE NAME
    • CustomerNumber = PRIMARY KEY
    • CustomerName

    • Invoice = TABLE NAME

    • InvoiceNumber = PRIMARY KEY
    • InvoiceDate
    • Quantity

    • Product = TABLE NAME

    • ProductiD = PRIMARY KEY
    • ProductName
    • Price
    • ProductEdition
  • 3NF

    • Customer = table name
    • CustomerNumber (P) = primary key
    • CustomerName

    • Invoice = table name

    • InvoiceNumber (P) = primary key
    • InvoiceDate
    • InvoiceCost

    • Product = tablename

    • ProductiD (P) = primary key
    • ProductName
    • ProductEdition
    • Price

    • InvoiceCost = table name

    • Quantity
    • ProductID
    • InvoiceNumber (P) = primary key
    • Product

InvoiceNumber to ProductID = One-to-many relationship

2.

  • UNF

    • ProductID
    • Title
    • Edition
    • PurchaseCost
    • RetailPrice
    • Author
  • 1NF

    • Product = table name
    • ProductID (P)
    • Title
    • Edition
    • Author

    • ProductCost

    • ProductID (P)
    • PurchaseCost
    • RetailPrice
  • 3nf

    • Product = table name
    • ProductID (P)
    • Title
    • Edition
    • Author

    • ProductCost = table name

    • ProductID (P)
    • PurchaseCost
    • RetailPrice

Title to Author= one to many Purchase cost and ProductID = one to one relationship

3.

  • UNF

    • EMP_ID
    • EMP_NAME
    • SOC_INS
    • POSITION
    • SUPERVISOR
    • HIREDATE
  • 1NF

    • Employee
    • EMP_ID
    • EMP_NAME
    • Position
    • Hiredate

    • Supervisor

    • SupervisorName
    • SupervisorID
  • 3NF

    • Employee
    • EMP_ID
    • EMP_NAME
    • PositionID
    • Hiredate
    • SupervisorID
    • SOC_INS

    • Supervisor

    • SupervisorName
    • SupervisorID

    • Position

    • PositionID
    • JobTitle

EMP_ID has a one to one relationship with SOC_INS

4.

  • UNF

    • Cust_id
    • Name
    • Location
    • Sales_rep
  • 1NF

    • Customer = table name
    • Cust_ID = primary key
    • Name
    • Location

    • SalesReps = table name

    • Sales_Rep = primary key
  • 3NF

    • Customer = table name
    • Cust_ID = primary key
    • Name

    • SalesReps = table name

    • Sales_Rep = primary key
    • RepName

    • Location = tablename

    • City
    • LocationID = primary key

Cust_id to sales_rep = one to one Sales_rep to cust_id = one to many

Aucun commentaire:

Enregistrer un commentaire