1 / 20

From week 3 Tutorial

CPhone. Makes. IsRequestedIn. From week 3 Tutorial. CAddr. CUSTOMER. CId. CName. PaidStatus. OId. ORDER. ODate. PId. PName. PART. PDesc. CAddr. CUSTOMER. CUSTPHONE. CId. CName. CPhone. Makes. Makes. CUSTOMER. IsRequestedIn. CId. CName. CAddr. CUSTPHONE. CPhone. CId.

melyssa-garrett
Download Presentation

From week 3 Tutorial

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. CPhone Makes IsRequestedIn From week 3 Tutorial CAddr CUSTOMER CId CName PaidStatus OId ORDER ODate PId PName PART PDesc

  2. CAddr CUSTOMER CUSTPHONE CId CName CPhone Makes Makes CUSTOMER IsRequestedIn CId CName CAddr CUSTPHONE CPhone CId Mapping Infinite Multi-valued Attributes PaidStatus OId ORDER ODate PId PName PART PDesc

  3. PaidStatus Makes Makes OId ORDER ODate PId PName PART PART PDesc PId PName PDesc IsRequestedIn ORDER PId OId PaidStatus ODate Mapping relationships CAddr CUSTOMER CUSTPHONE CId CName CPhone

  4. CAddr CUSTOMER CId CName PaidStatus Makes Makes OId ORDER ODate PART PId PName PDesc IsRequestedIn ORDER CId PId OId PaidStatus ODate CUSTOMER CId CName CAddr Mapping relationships CUSTPHONE CPhone PId PName PART PDesc

  5. PART PId PName PDesc ORDER OId PaidStatus ODate CUSTPHONE CUSTOMER CPhone CId CName CAddr Final Schema PId CId CId

  6. PART PId PName PDesc CUSTOMER CId CName CAddr SQL Data Definition CREATE TABLE Part (PId INTEGER, PName CHAR(20), PDesc CHAR(30), PRIMARY KEY (PId)) CREATE TABLE Customer (Cid INTEGER, CName CHAR(20), CAddr CHAR(40), PRIMARY KEY (Cid))

  7. PId CId PART PId PName PDesc ORDER OId PaidStatus ODate CUSTOMER CId CName CAddr SQL Data Definition CREATE TABLE Order (OId INTEGER, ODate DATE, PaidStatus CHAR(1), PId INTEGER, CId INTEGER, PRIMARY KEY (OId), FOREIGN KEY (PId) REFERENCES Part, FOREIGN KEY (Cid) REFERENCES Customer)

  8. Var1 Var2 Var3 Var4 2 3 4 1 a a b b d c c d b b e e c f c f Joined Relations 1 2 3 4

  9. JOIN Table 1 Table 2 The Relations for (TID=SID) is: The Cartesian Product is

  10. JOIN Table 1 Table 2 The Cartesian Product for (TID-SID) is: The Cartesian Product is

  11. Functional Dependencies and Keys • Functional Dependency: The value of one attribute (the determinant) determines the value of another attribute • Candidate Key: • A unique identifier. One of the candidate keys will become the primary key • E.g. perhaps there is both credit card number and SS# in a table…in this case both are candidate keys • Each non-key field is functionally dependent on every candidate key

  12. Steps in normalization

  13. First Normal Form • No multi-valued attributes • Every attribute value is atomic • multi-valued attributes  it is not a relation

  14. Second Normal Form • 1NF PLUS every non-key attribute is fully functionally dependent on the ENTIRE primary key • Every non-key attribute must be defined by the entire key, not by only part of the key • No partial functional dependencies

  15. Functional Dependencies Full Dependency Transitive Dependencies Partial Dependencies Partial Dependencies Order_ID  Order_Date, Customer_ID, Customer_Name, Customer_Address Customer_ID  Customer_Name, Customer_Address Product_ID  Product_Description, Product_Finish, Unit_Price Order_ID, Product_ID  Order_Quantity Therefore, NOT in 2nd Normal Form

  16. Getting it into Second Normal Form: Removing Partial Dependencies Partial Dependencies are removed, but there are still transitive dependencies

  17. Third Normal Form • 2NF PLUS no transitive dependencies (functional dependencies on non-primary-key attributes) • Note: this is called transitive, because the primary key is a determinant for another attribute, which in turn is a determinant for a third • Solution: non-key determinant with transitive dependencies go into a new table; non-key determinant becomes primary key in the new table and stays as foreign key in the old table

  18. Getting it into Third Normal Form Transitive dependencies are removed

  19. Boyce-Codd Normal Form (BCNF) Customer-street Branch-name Branch-city Customer-id Customer-city Branch-ID Customer-name GetLoan Customer Branch Loan-Number Amount

  20. Boyce-Codd Normal Form (BCNF) • Customer-schema = (customer-id, customer-name, customer-street, customer-city) customer-id -> customer-name customer-street customer-city • Branch-schema = (branch-id, branch-name, branch-city) branch-id -> branch-name branch-city • Loan-info-schema (branch-id, customer-id, loan-number, amount) loan-number -> amount branch-id e.g. (Melbourne101, c1234, L-44, $1000) (Melbourne101, c1235, L-44, $1000) NOT in BCNF Loan-schema (loan-number, branch-id, amount) Borrower-schema (customer-id, loan-number)

More Related