From week 3 Tutorial - PowerPoint PPT Presentation

Loading...

PPT – From week 3 Tutorial PowerPoint presentation | free to download - id: 7dacc0-NWEyY



Loading


The Adobe Flash plugin is needed to view this content

Get the plugin now

View by Category
About This Presentation
Title:

From week 3 Tutorial

Description:

From week 3 Tutorial CAddr CPhone CUSTOMER Makes CId CName PaidStatus OId ORDER ODate PName PId PART IsRequestedIn PDesc – PowerPoint PPT presentation

Number of Views:63
Avg rating:3.0/5.0
Slides: 21
Provided by: spo133
Category:

less

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

Title: From week 3 Tutorial


1
From week 3 Tutorial
CAddr
CUSTOMER
CId
CName
PaidStatus
OId
ORDER
ODate
PName
PId
PART
PDesc
2
Mapping Infinite Multi-valued Attributes
PaidStatus
OId
ORDER
ODate
PName
PId
PART
PDesc
3
Mapping relationships
CAddr
CUSTOMER
CUSTPHONE
CId
CName
CPhone
4
Mapping relationships
CUSTPHONE
CPhone
PName
PId
PART
PDesc
5
Final Schema
PId
CId
CId
6
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
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
Joined Relations
Var1 Var2
a c
b d
Var3 Var4
b c
e f
1
2
3
4
9
JOIN
Table 1
Table 2
TID Var1
1 a
2 b
3 c
SID Var2
1 d
3 e
5 f
The Relations for (TIDSID) is
The Cartesian Product is
TID Var1 SID Var2
1 a 1 d
1 a 3 e
1 a 5 f
2 b 1 d
2 b 3 e
2 b 5 f
3 c 1 d
3 c 3 e
3 c 5 f
10
JOIN
Table 1
Table 2
TID Var1
1 a
2 b
3 c
SID Var2
1 d
3 e
5 f
The Cartesian Product for (TID-SID) is
The Cartesian Product is
TID Var1 SID Var2
1 a 1 d
3 c 3 e
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 tablein 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 -gt customer-name customer-street
    customer-city
  • Branch-schema (branch-id, branch-name,
    branch-city)
  • branch-id -gt branch-name branch-city
  • Loan-info-schema (branch-id, customer-id,
    loan-number, amount)
  • loan-number -gt 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)
About PowerShow.com