Need Help with SQL Server (Indexes)
Heidi21 
SQLServer2016_Chapter10slides.pptxChapter 10
How to design a database
Murach's SQL Server 2019
© 2019, Mike Murach & Associates, Inc.
C10, 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.
Murach's SQL Server 2019
© 2019, Mike Murach & Associates, Inc.
C10, Slide 2
A database system is modeled after a real-world system
Murach's SQL Server 2019
© 2019, Mike Murach & Associates, Inc.
C10, Slide 3
The six basic steps for designing a data structure
Step 1: Identify the data elements
Step 2: Subdivide each element into its smallest useful components
Step 3: Identify the tables and assign columns
Step 4: Identify the primary and foreign keys
Step 5: Review whether the data structure is normalized
Step 6: Identify the indexes
Murach's SQL Server 2019
© 2019, Mike Murach & Associates, Inc.
C10, Slide 4
An invoice that’s used to identify data elements
Murach's SQL Server 2019
© 2019, Mike Murach & Associates, Inc.
C10, Slide 5
The data elements on the invoice document
Vendor name Item extension
Vendor address Vendor sales contact name
Vendor phone number Vendor sales contact extension
Vendor fax number Vendor AR contact name
Vendor web address Vendor AR contact extension
Invoice number Invoice total
Invoice date
Invoice terms
Item part number
Item quantity
Item description
Item unit price
Murach's SQL Server 2019
© 2019, Mike Murach & Associates, Inc.
C10, Slide 6
A name that’s divided into first and last names
Murach's SQL Server 2019
© 2019, Mike Murach & Associates, Inc.
C10, Slide 7
An address that’s divided into its components
Murach's SQL Server 2019
© 2019, Mike Murach & Associates, Inc.
C10, Slide 8
Possible tables and columns for an A/P system (part 1)
Vendors
Vendor name Vendor contact first name
Vendor address Vendor contact last name
Vendor city Vendor contact phone
Vendor state Vendor AR first name
Vendor zip code Vendor AR last name
Vendor phone number Vendor AR phone
Vendor fax number Terms*
Vendor web address Account number*
Murach's SQL Server 2019
© 2019, Mike Murach & Associates, Inc.
C10, Slide 9
Possible tables and columns for an A/P system (part 2)
Invoices Invoice line items
Invoice number* Invoice number*
Invoice date Item part number
Terms* Item quantity
Invoice total Item description
Payment date Item unit price
Payment total Item extension
Invoice due date Account number*
Credit total Sequence number
Account number*
Murach's SQL Server 2019
© 2019, Mike Murach & Associates, Inc.
C10, 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.
Murach's SQL Server 2019
© 2019, Mike Murach & Associates, Inc.
C10, Slide 11
The relationships between the tables in the accounts payable system
Murach's SQL Server 2019
© 2019, Mike Murach & Associates, Inc.
C10, Slide 12
Two tables with a many-to-many relationship
Murach's SQL Server 2019
© 2019, Mike Murach & Associates, Inc.
C10, Slide 13
Two tables with a one-to-one relationship
Murach's SQL Server 2019
© 2019, Mike Murach & Associates, Inc.
C10, 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
Murach's SQL Server 2019
© 2019, Mike Murach & Associates, Inc.
C10, Slide 15
Terms related to database design
Entity
Attribute
Instance
Entity-relationship (ER) modeling
Referential integrity
Declarative referential integrity (DRI)
Foreign key constraint
Orphaned
Trigger
Murach's SQL Server 2019
© 2019, Mike Murach & Associates, Inc.
C10, Slide 16
Two tables that need to be normalized
A table that contains repeating columns
A table that contains redundant data
Murach's SQL Server 2019
© 2019, Mike Murach & Associates, Inc.
C10, Slide 17
The accounts payable system in third normal form
Murach's SQL Server 2019
© 2019, Mike Murach & Associates, Inc.
C10, Slide 18
Terms related to normalization
Normalization
Data redundancy
Unnormalized data structure
Normalized data structure
Normal forms
Murach's SQL Server 2019
© 2019, Mike Murach & Associates, Inc.
C10, Slide 19
When to create an index
When the column is a foreign key
When the column is used frequently in search conditions or joins
When the column contains a large number of distinct values
When the column is updated infrequently
Murach's SQL Server 2019
© 2019, Mike Murach & Associates, Inc.
C10, Slide 20
When to reassign the clustered index
When the column is used in almost every search condition
When the column contains mostly distinct values
When the column is small
When the column values seldom, if ever, change
When most queries against the column will return large result sets
Murach's SQL Server 2019
© 2019, Mike Murach & Associates, Inc.
C10, Slide 21
Terms related to indexes
Index
Clustered index
Nonclustered index
Composite index
Murach's SQL Server 2019
© 2019, Mike Murach & Associates, Inc.
C10, Slide 22
The first three normal forms
Normal form Description
First (1NF) The value stored at the intersection of each row and column must be a scalar value, and a table must not contain any repeating columns.
Second (2NF) Every non-key column must depend on the entire primary key.
Third (3NF) Every non-key column must depend only on the primary key.
Note
Most designers stop at the third normal form.
Murach's SQL Server 2019
© 2019, Mike Murach & Associates, Inc.
C10, Slide 23
The next four normal forms
Normal form Description
Boyce-Codd (BCNF) A non-key column can’t be dependent on another non-key column.
Fourth (4NF) A table must not have more than one multivalued dependency, where the primary key has a one-to-many relationship to non-key columns.
Fifth (5NF) The data structure is split into smaller and smaller tables until all redundancy has been eliminated.
Domain-key (DKNF) Every constraint on the relationship is or dependent only on key constraints and Sixth(6NF) domain constraints, where a domain is the set of allowable values for a column.
Murach's SQL Server 2019
© 2019, Mike Murach & Associates, Inc.
C10, Slide 24
The benefits of normalization
More tables, and each table can have a clustered index, the database has more clustered indexes. That makes data retrieval more efficient.
Each table contains information about a single entity, and each index has fewer columns (usually one) and fewer rows. That makes data retrieval and insert, update, and delete operations more efficient.
Each table has fewer indexes, which makes insert, update, and delete operations more efficient.
Data redundancy is minimized, which simplifies maintenance and reduces storage.
Murach's SQL Server 2019
© 2019, Mike Murach & Associates, Inc.
C10, Slide 25
Unnormalized invoice data
The invoice data with a column that contains repeating values
The invoice data with repeating columns
Murach's SQL Server 2019
© 2019, Mike Murach & Associates, Inc.
C10, Slide 26
The invoice data in first normal form
Murach's SQL Server 2019
© 2019, Mike Murach & Associates, Inc.
C10, Slide 27
The invoice data in first normal form with keys added
Murach's SQL Server 2019
© 2019, Mike Murach & Associates, Inc.
C10, Slide 28
The invoice data in second normal form
Murach's SQL Server 2019
© 2019, Mike Murach & Associates, Inc.
C10, Slide 29
The AP system in second normal form
Murach's SQL Server 2019
© 2019, Mike Murach & Associates, Inc.
C10, Slide 30
Questions about the structure
Does the vendor information (VendorName, VendorAddress, etc.) depend only on the InvoiceID column?
Does the Terms column depend only on the InvoiceID column?
Does the AccountNo column depend only on the InvoiceID column?
Can the InvoiceDueDate and InvoiceLineItemAmount columns be derived from other data?
Murach's SQL Server 2019
© 2019, Mike Murach & Associates, Inc.
C10, Slide 31
The AP system in third normal form
Murach's SQL Server 2019
© 2019, Mike Murach & Associates, Inc.
C10, Slide 32
The AP system in fifth normal form
Murach's SQL Server 2019
© 2019, Mike Murach & Associates, Inc.
C10, Slide 33
When to denormalize
When a column from a joined table is used repeatedly in search criteria.
If a table is updated infrequently.
Include columns with derived values when those values are used frequently in search conditions.
Murach's SQL Server 2019
© 2019, Mike Murach & Associates, Inc.
C10, Slide 34
People
Documents
Facilities
Other
systems
Real-world systemDatabase system
R
o
w
s
Columns
Tables
