Cộng đồng chia sẻ tri thức Lib24.vn

SQLServerCh10

d41d8cd98f00b204e9800998ecf8427e
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:
Tải xuống

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