SQLServerCh10
Gửi bởi: Khoa CNTT - HCEM 24 tháng 2 2021 lúc 9:18:49 | Được cập nhật: 24 tháng 3 lúc 11:49:15 Kiểu file: PPTX | Lượt xem: 126 | Lượt Download: 1 | File size: 1.34512 Mb
Nội dung tài liệu
Tải xuống
Link tài liệu:
Các tài liệu liên quan
Có thể bạn quan tâm
Thông tin tài liệu
Chapter 10
How to design
a database
Book Title, C1
© 2012, Mike Murach & Associates, Inc.
Slide 1
Objectives
Applied
Given the specifications for a database modeled on a real-world
system, design the database. Identify tables, columns, keys,
relationships, and indexes for the new database.
Given a diagram for an unnormalized database, normalize the
structure to the third normal form.
Knowledge
In general terms, describe the criteria for indexing a column.
Explain how referential integrity prevents deletion, insertion, and
update anomalies.
Explain how normalizing a database to the third normal form
improves database performance.
Book Title, C1
© 2012, Mike Murach & Associates, Inc.
Slide 2
A database system is modeled
after a real-world system
Real-world system
Database system
Tables
People
Documents
Columns
Rows
Facilities
Other
systems
Book Title, C1
© 2012, Mike Murach & Associates, Inc.
Slide 3
The six basic steps for designing a data structure
Step 1:
Step 2:
Step 3:
Step 4:
Step 5:
Step 6:
Book Title, C1
Identify the data elements
Subdivide each element into its smallest useful components
Identify the tables and assign columns
Identify the primary and foreign keys
Review whether the data structure is normalized
Identify the indexes
© 2012, Mike Murach & Associates, Inc.
Slide 4
An invoice that’s used to identify data elements
Acme Fabrication, Inc.
Custom Contraptions, Contrivances and Confabulations
1234 West Industrial Way East Los Angeles California 90022
800.555.1212
fax 562.555.1213
Part No.
Qty.
CUST345
12
457332
50173
7
4375
Invoice Number:
I01-1088
Invoice Date:
06/05/12
www.acmefabrication.com Terms:
Description
Net 30
Unit Price
Design service, hr
Extension
100.00
1200.00
79.90
559.30
Duct tape, black, yd
1.09
4768.75
4.79
9.58
75.00
525.00
125.00
250.00
Baling wire, 25x3ft roll
328771
2
Rubber tubing, 100ft roll
CUST281
7
Assembly, hr
CUST917
2
Testing, hr
Sales Tax
245.20
Your salesperson:
Ruben Goldberg, ext 4512
Accounts receivable:
Inigo Jones, ext 4901
$7,557.83
PLEASE PAY THIS AMOUNT
Thanks for your business!
Book Title, C1
© 2012, Mike Murach & Associates, Inc.
Slide 5
The data elements on the invoice document
Vendor name
Vendor address
Vendor phone number
Vendor fax number
Vendor web address
Invoice number
Invoice date
Invoice terms
Item part number
Item quantity
Item description
Item unit price
Book Title, C1
Item extension
Vendor sales contact name
Vendor sales contact extension
Vendor AR contact name
Vendor AR contact extension
Invoice total
© 2012, Mike Murach & Associates, Inc.
Slide 6
A name that’s divided into first and last names
Vendor sales contact name
Ruben Goldberg
Vendor sales contact first name
Vendor sales contact last name
Ruben
Goldberg
Book Title, C1
© 2012, Mike Murach & Associates, Inc.
Slide 7
An address that’s divided into its components
Vendor address
1234 West Industrial Way, East Los Angeles, California 90022
Street and number
City
State
Zip
1234 West Industrial Way
East Los Angeles
California
90022
Book Title, C1
© 2012, Mike Murach & Associates, Inc.
Slide 8
Possible tables and columns for an A/P system
Vendors
Vendor name
Vendor address
Vendor city
Vendor state
Vendor zip code
Vendor phone number
Vendor fax number
Vendor web address
Vendor contact first name
Vendor contact last name
Vendor contact phone
Vendor AR first name
Book Title, C1
Invoices
Invoice number*
Invoice date
Terms*
Invoice total
Payment date
Payment total
Invoice due date
Credit total
Account number*
© 2012, Mike Murach & Associates, Inc.
Slide 9
Possible tables and columns for an A/P system
(continued)
Vendors
Vendor AR last name
Vendor AR phone
Terms*
Account number*
Book Title, C1
Invoice line items
Invoice number*
Item part number
Item quantity
Item description
Item unit price
Item extension
Account number*
Sequence number
© 2012, Mike Murach & Associates, Inc.
Slide 10
The notation for identifying data elements
Data elements that were previously identified but aren’t needed
are crossed out.
Data elements that were added are displayed in italics.
Data elements that are related to two or more entities are followed
by an asterisk.
Book Title, C1
© 2012, Mike Murach & Associates, Inc.
Slide 11
The relationships between the
Vendors
Invoices
InvoiceLineItems
VendorID
VendorName
VendorAddress
VendorCity
VendorState
VendorZipCode
VendorPhone
VendorContactFName
VendorContactLName
Terms
AccountNo
InvoiceID
VendorID
InvoiceNumber
InvoiceDate
InvoiceTotal
PaymentTotal
CreditTotal
Terms
InvoiceDueDate
PaymentDate
AccountNo
InvoiceID
InvoiceSequence
AccountNo
InvoiceLineItemDescription
ItemQuantity
ItemUnitPrice
InvoiceLineItemAmount
Book Title, C1
© 2012, Mike Murach & Associates, Inc.
Slide 12
Two tables with a many-to-many relationship
Employees
Memberships
Committees
EmployeeID
FirstName
LastName
EmployeeID
CommitteeID
CommitteeID
CommitteeName
Linking table
Book Title, C1
© 2012, Mike Murach & Associates, Inc.
Slide 13
Two tables with a one-to-one relationship
Employees
EmployeePhotos
EmployeeID
FirstName
LastName
EmployeeID
EmployeePhoto
Book Title, C1
© 2012, Mike Murach & Associates, Inc.
Slide 14
Operations that can violate referential integrity
Deleting a row from the primary key table
If the foreign key table contains one or more rows related to the
deleted row
Inserting a row in the foreign key table
If the foreign key value doesn’t have a matching primary key
value in the related table
Updating the value of a foreign key
If the new foreign key value doesn’t have a matching primary key
value in the related table
Updating the value of a primary key
If the foreign key table contains one or more rows related to the
row that’s changed
Book Title, C1
© 2012, Mike Murach & Associates, Inc.
Slide 15
Terms
Entity
Attribute
Instance
Entity-relationship (ER) modeling
Referential integrity
Declarative referential integrity (DRI)
Foreign key constraint
Orphaned
Trigger
Book Title, C1
© 2012, Mike Murach & Associates, Inc.
Slide 16
Two tables that need to be normalized
A table that contains repeating columns
A table that contains redundant data
Book Title, C1
© 2012, Mike Murach & Associates, Inc.
Slide 17
The accounts payable system in third normal form
Vendors
Invoices
InvoiceLineItems
VendorID
VendorName
VendorAddress
VendorCity
VendorState
VendorZipCode
VendorPhone
VendorContactFName
VendorContactLName
DefaultTermsID
DefaultAccountNo
InvoiceID
VendorID
InvoiceNumber
InvoiceDate
InvoiceTotal
PaymentTotal
CreditTotal
TermsID
InvoiceDueDate
PaymentDate
InvoiceID
InvoiceSequence
AccountNo
InvoiceLineItemAmount
InvoiceLineItemDescription
Terms
GLAccounts
AccountNo
AccountDescription
TermsID
TermsDescription
TermsDueDays
Book Title, C1
© 2012, Mike Murach & Associates, Inc.
Slide 18
How to design
a database
Book Title, C1
© 2012, Mike Murach & Associates, Inc.
Slide 1
Objectives
Applied
Given the specifications for a database modeled on a real-world
system, design the database. Identify tables, columns, keys,
relationships, and indexes for the new database.
Given a diagram for an unnormalized database, normalize the
structure to the third normal form.
Knowledge
In general terms, describe the criteria for indexing a column.
Explain how referential integrity prevents deletion, insertion, and
update anomalies.
Explain how normalizing a database to the third normal form
improves database performance.
Book Title, C1
© 2012, Mike Murach & Associates, Inc.
Slide 2
A database system is modeled
after a real-world system
Real-world system
Database system
Tables
People
Documents
Columns
Rows
Facilities
Other
systems
Book Title, C1
© 2012, Mike Murach & Associates, Inc.
Slide 3
The six basic steps for designing a data structure
Step 1:
Step 2:
Step 3:
Step 4:
Step 5:
Step 6:
Book Title, C1
Identify the data elements
Subdivide each element into its smallest useful components
Identify the tables and assign columns
Identify the primary and foreign keys
Review whether the data structure is normalized
Identify the indexes
© 2012, Mike Murach & Associates, Inc.
Slide 4
An invoice that’s used to identify data elements
Acme Fabrication, Inc.
Custom Contraptions, Contrivances and Confabulations
1234 West Industrial Way East Los Angeles California 90022
800.555.1212
fax 562.555.1213
Part No.
Qty.
CUST345
12
457332
50173
7
4375
Invoice Number:
I01-1088
Invoice Date:
06/05/12
www.acmefabrication.com Terms:
Description
Net 30
Unit Price
Design service, hr
Extension
100.00
1200.00
79.90
559.30
Duct tape, black, yd
1.09
4768.75
4.79
9.58
75.00
525.00
125.00
250.00
Baling wire, 25x3ft roll
328771
2
Rubber tubing, 100ft roll
CUST281
7
Assembly, hr
CUST917
2
Testing, hr
Sales Tax
245.20
Your salesperson:
Ruben Goldberg, ext 4512
Accounts receivable:
Inigo Jones, ext 4901
$7,557.83
PLEASE PAY THIS AMOUNT
Thanks for your business!
Book Title, C1
© 2012, Mike Murach & Associates, Inc.
Slide 5
The data elements on the invoice document
Vendor name
Vendor address
Vendor phone number
Vendor fax number
Vendor web address
Invoice number
Invoice date
Invoice terms
Item part number
Item quantity
Item description
Item unit price
Book Title, C1
Item extension
Vendor sales contact name
Vendor sales contact extension
Vendor AR contact name
Vendor AR contact extension
Invoice total
© 2012, Mike Murach & Associates, Inc.
Slide 6
A name that’s divided into first and last names
Vendor sales contact name
Ruben Goldberg
Vendor sales contact first name
Vendor sales contact last name
Ruben
Goldberg
Book Title, C1
© 2012, Mike Murach & Associates, Inc.
Slide 7
An address that’s divided into its components
Vendor address
1234 West Industrial Way, East Los Angeles, California 90022
Street and number
City
State
Zip
1234 West Industrial Way
East Los Angeles
California
90022
Book Title, C1
© 2012, Mike Murach & Associates, Inc.
Slide 8
Possible tables and columns for an A/P system
Vendors
Vendor name
Vendor address
Vendor city
Vendor state
Vendor zip code
Vendor phone number
Vendor fax number
Vendor web address
Vendor contact first name
Vendor contact last name
Vendor contact phone
Vendor AR first name
Book Title, C1
Invoices
Invoice number*
Invoice date
Terms*
Invoice total
Payment date
Payment total
Invoice due date
Credit total
Account number*
© 2012, Mike Murach & Associates, Inc.
Slide 9
Possible tables and columns for an A/P system
(continued)
Vendors
Vendor AR last name
Vendor AR phone
Terms*
Account number*
Book Title, C1
Invoice line items
Invoice number*
Item part number
Item quantity
Item description
Item unit price
Item extension
Account number*
Sequence number
© 2012, Mike Murach & Associates, Inc.
Slide 10
The notation for identifying data elements
Data elements that were previously identified but aren’t needed
are crossed out.
Data elements that were added are displayed in italics.
Data elements that are related to two or more entities are followed
by an asterisk.
Book Title, C1
© 2012, Mike Murach & Associates, Inc.
Slide 11
The relationships between the
Vendors
Invoices
InvoiceLineItems
VendorID
VendorName
VendorAddress
VendorCity
VendorState
VendorZipCode
VendorPhone
VendorContactFName
VendorContactLName
Terms
AccountNo
InvoiceID
VendorID
InvoiceNumber
InvoiceDate
InvoiceTotal
PaymentTotal
CreditTotal
Terms
InvoiceDueDate
PaymentDate
AccountNo
InvoiceID
InvoiceSequence
AccountNo
InvoiceLineItemDescription
ItemQuantity
ItemUnitPrice
InvoiceLineItemAmount
Book Title, C1
© 2012, Mike Murach & Associates, Inc.
Slide 12
Two tables with a many-to-many relationship
Employees
Memberships
Committees
EmployeeID
FirstName
LastName
EmployeeID
CommitteeID
CommitteeID
CommitteeName
Linking table
Book Title, C1
© 2012, Mike Murach & Associates, Inc.
Slide 13
Two tables with a one-to-one relationship
Employees
EmployeePhotos
EmployeeID
FirstName
LastName
EmployeeID
EmployeePhoto
Book Title, C1
© 2012, Mike Murach & Associates, Inc.
Slide 14
Operations that can violate referential integrity
Deleting a row from the primary key table
If the foreign key table contains one or more rows related to the
deleted row
Inserting a row in the foreign key table
If the foreign key value doesn’t have a matching primary key
value in the related table
Updating the value of a foreign key
If the new foreign key value doesn’t have a matching primary key
value in the related table
Updating the value of a primary key
If the foreign key table contains one or more rows related to the
row that’s changed
Book Title, C1
© 2012, Mike Murach & Associates, Inc.
Slide 15
Terms
Entity
Attribute
Instance
Entity-relationship (ER) modeling
Referential integrity
Declarative referential integrity (DRI)
Foreign key constraint
Orphaned
Trigger
Book Title, C1
© 2012, Mike Murach & Associates, Inc.
Slide 16
Two tables that need to be normalized
A table that contains repeating columns
A table that contains redundant data
Book Title, C1
© 2012, Mike Murach & Associates, Inc.
Slide 17
The accounts payable system in third normal form
Vendors
Invoices
InvoiceLineItems
VendorID
VendorName
VendorAddress
VendorCity
VendorState
VendorZipCode
VendorPhone
VendorContactFName
VendorContactLName
DefaultTermsID
DefaultAccountNo
InvoiceID
VendorID
InvoiceNumber
InvoiceDate
InvoiceTotal
PaymentTotal
CreditTotal
TermsID
InvoiceDueDate
PaymentDate
InvoiceID
InvoiceSequence
AccountNo
InvoiceLineItemAmount
InvoiceLineItemDescription
Terms
GLAccounts
AccountNo
AccountDescription
TermsID
TermsDescription
TermsDueDays
Book Title, C1
© 2012, Mike Murach & Associates, Inc.
Slide 18