Transcript CREATE TABLE

IT390 Business Database Administration
Unit 3:
Microsoft Transact SQL
and the Query Analyzer
© 2006 ITT Educational Services Inc.
Course Name: IT390 Business Database
Administration Unit 3 Slide 1
Objectives
•
•
•
•
•
•
•
•
•
Briefly trace the history of SQL
Apply Key commands and features of Microsoft SQL
Database Server and Transact SQL.
Explain other key commands associated with the Microsoft
SQL Server and Transact SQL language.
Create and Manage databases with Transact SQL and
Enterprise Manager.
Create a Database with Transact SQL and Enterprise
Manager.
Explain how to create and manage Microsoft Databases
through both the Query Analyzer and Enterprise Manager.
Explain Normalization with Data Models.
Describe the fundamentals of Normalization with the
Relational Data Model and basic Relational Design.
Describe normalization anomalies and their results.
© 2006 ITT Educational Services Inc.
Course Name: IT390 Business Database
Administration Unit 3 Slide 2
Databases: An Overview
History
SQL came into existence when Dr. E. F. Codd, who worked for
IBM, defined the relational model for databases.
In 1986 and 1987, American National Standards Institute
(ANSI) and International Standards Organization (ISO) formed
SQL Standards committees. As a result, SQL became an open
standard known as ANSI-SQL.
To implement the designs in a relational database
management system (RDBMS), you need a tool. Structured
query language (SQL) is one such tool.
© 2006 ITT Educational Services Inc.
Course Name: IT390 Business Database
Administration Unit 3 Slide 3
Think about it…
• What is SQL?
• Why is SQL known as a non-procedural language? Is it just
another language?
© 2006 ITT Educational Services Inc.
Course Name: IT390 Business Database
Administration Unit 3 Slide 4
SQL Products and Application Developers
© 2006 ITT Educational Services Inc.
Course Name: IT390 Business Database
Administration Unit 3 Slide 5
Processing of a SQL Command
You can perform various database operations by using SQL
commands. These operations include updating data in a
database and retrieving information from a database.
When you query a database using an SQL command, the SQL
query is sent to an RDBMS.
The SQL query process can be summarized as follows:
• A user makes a request by using an SQL statement.
• The RDBMS receives the request and forwards it to
the database engine.
• The database engine processes the request and
sends the result to the RDBMS.
• The RDBMS forwards the result to the user.
© 2006 ITT Educational Services Inc.
Course Name: IT390 Business Database
Administration Unit 3 Slide 6
Processing of a SQL Command
• SQL provides a number of commands to perform
various database-related tasks. You perform
database-related tasks in SQL by using three different
primary types of SQL (sub)languages:
 Data Definition language (DDL)
 Data Manipulation Language (DML)
 Data Control Language (DCL)
 For instance, a table that is created using a DDL
command stores the :
 Name of a table
 Columns of a table
 Constraints on the table
© 2006 ITT Educational Services Inc.
Course Name: IT390 Business Database
Administration Unit 3 Slide 7
Sub-Languages of SQL
Sub-Languages of SQL
DDL
DML
© 2006 ITT Educational Services Inc.
Course Name: IT390 Business Database
Administration Unit 3 Slide 8
TCL
DCL
Activity
• Identify the sub-languages of SQL.
1) Joe, the database administrator of
Ethnic Blends Inc., wants to create a new
table named Brands, which will be a
part of the organization’s database.
© 2006 ITT Educational Services Inc.
Course Name: IT390 Business Database
Administration Unit 3 Slide 9
Solution
•
•
•
•
For case 1: DDL
For case 2: DML
For case 3: TCL
For case 4: DCL
© 2006 ITT Educational Services Inc.
Course Name: IT390 Business Database
Administration Unit 3 Slide 10
Data Definition Language
© 2006 ITT Educational Services Inc.
Course Name: IT390 Business Database
Administration Unit 3 Slide 11
Creating a Database (RAW SQL CODE!)
•
The syntax for creating a database in T-SQL is:
CREATE DATABASE <db_name>
[ON [PRIMARY]
[ <filename> [,...n] ]
[, <filegroupname> [,...n] ]
]
[ LOG ON { <logfiles> [,...n]} ]
[ FOR LOAD | FOR ATTACH ]
<filespec> ::=
( [ NAME = file_name, ]
FILENAME = 'os_filename'
[, SIZE = filesize]
[, MAXSIZE = { maximum_size | UNLIMITED }
]
[, FILEGROWTH = file_growthincrement] )
[,...n]
<filegroup> ::=
FILEGROUP name_filegroup <filsespec>
[,...n]
© 2006 ITT Educational Services Inc.
Course Name: IT390 Business Database
Administration Unit 3 Slide 12
Creating a Database (cont.)
•
You can use also use Enterprise Manager
to create a database.
© 2006 ITT Educational Services Inc.
Course Name: IT390 Business Database
Administration Unit 3 Slide 13
System Databases (some Databases already exist!)
•
They are of the following types:

master

model

msdb

tempdb
© 2006 ITT Educational Services Inc.
Course Name: IT390 Business Database
Administration Unit 3 Slide 14
DDL: Using the CREATE TABLE Command
• You can use the CREATE TABLE command to do
the following:
 Create a new table.
 Create a new table with columns.
 Create a new table with constraints defined on
columns.
• You use the CREATE TABLE command to create
a new table. The syntax for creating a new table
is:
CREATE TABLE <table name>
© 2006 ITT Educational Services Inc.
Course Name: IT390 Business Database
Administration Unit 3 Slide 15
DDL: Using the CREATE TABLE Command
• You can use the CREATE TABLE command to
create a new table with columns:
CREATE TABLE <table name>
<column name> <data type>
• You can use some optional constraints with the
basic CREATE TABLE command. The syntax
having the optional constraints is as follows:
CREATE TABLE <table name>
<column name> <data type>
© 2006 ITT Educational Services Inc.
Course Name: IT390 Business Database
Administration Unit 3 Slide 16
Using Other DDL Commands
•DDL provides the following commands to modify and delete
existing objects in a database:
 ALTER
 DROP
 TRUNCATE
 COMMENT
© 2006 ITT Educational Services Inc.
Course Name: IT390 Business Database
Administration Unit 3 Slide 17
Modifying Table and Column
• The syntax to modify an existing table is:
ALTER TABLE <table name>
• The syntax to alter a column in a table is:
ALTER TABLE <table name>
ALTER COLUMN <column name>
© 2006 ITT Educational Services Inc.
Course Name: IT390 Business Database
Administration Unit 3 Slide 18
Using Other DDL Commands
•ALTER command: You use the ALTER
command to add columns, add constraints, or
modify columns and constraints that were
already created using the CREATE command.
• The syntax to use the ALTER TABLE command
to add a column to a table is as follows:
ALTER TABLE <table name>
ADD <column name> <data type> <constraint>
© 2006 ITT Educational Services Inc.
Course Name: IT390 Business Database
Administration Unit 3 Slide 19
Using Other DDL Commands (cont.)
Deleting Table and Column Objects
•DROP Command: You use the DROP command to
delete a table or columns that has constraints.
• The syntax to use a DROP command to delete an
object is as follows:
DROP <OBJECT><object name>.
Here, the <OBJECT> could be a TABLE, COLUMN, or a
CONSTRAINT with its respective name.
DROP OBJECT <object name>
where,
object name can be the name of
a column or a constraint
© 2006 ITT Educational Services Inc.
Course Name: IT390 Business Database
Administration Unit 3 Slide 20
Using Other DDL Commands (cont.)
•You use the TRUNCATE command to delete only
specific rows in a table.
• The syntax to use a TRUNCATE command to
delete an object is as follows:
TRUNCATE <table name>
© 2006 ITT Educational Services Inc.
Course Name: IT390 Business Database
Administration Unit 3 Slide 21
Using Other DDL Commands
(cont.)
• The COMMENT command enables you add a
comment about a table or a column.
COMMENT ON TABLE <table name> IS “………….”
The COMMENT in T-SQL is “ -- “
© 2006 ITT Educational Services Inc.
Course Name: IT390 Business Database
Administration Unit 3 Slide 22
SQL Language
• Designed to talk to relational databases
• Perform various functions:
DDL-Data Definition
DML-Data Manipulation
DCL-Data Control (Security)
• We will talk about DML
© 2006 ITT Educational Services Inc.
Course Name: IT390 Business Database
Administration Unit 3 Slide 23
The Syntax of the DML “SELECT” statement
© 2006 ITT Educational Services Inc.
Course Name: IT390 Business Database
Administration Unit 3 Slide 24
Class Activity 2
• Will the following SELECT statements run and what is
the difference between them?
select * from garment
SELECT * FROM garment
© 2006 ITT Educational Services Inc.
Course Name: IT390 Business Database
Administration Unit 3 Slide 25
Solution 2
• Both the statements will run as SQL is not normally case
sensitive. BE CAREFUL though…it is good to be in the
habit of consistent coding for READABILITY.
© 2006 ITT Educational Services Inc.
Course Name: IT390 Business Database
Administration Unit 3 Slide 26
Activity
• In an Employee table, there are 3 columns named
firstname, lastname, and middlename. Is there any error
in the following query?
SELECT firstname lastname middlename
FROM Employee
© 2006 ITT Educational Services Inc.
Course Name: IT390 Business Database
Administration Unit 3 Slide 27
Solution
• The column names should be separated by a comma (,).
• The correct code is:
SELECT firstname, lastname,
middlename FROM employee
© 2006 ITT Educational Services Inc.
Course Name: IT390 Business Database
Administration Unit 3 Slide 28
Activity
• Which of the following syntax will not give an error?
select * from garment
SELECT * FROM
garment
select *
From garment
Select
* From garment
© 2006 ITT Educational Services Inc.
Course Name: IT390 Business Database
Administration Unit 3 Slide 29
Solution
• All the statements will work.
© 2006 ITT Educational Services Inc.
Course Name: IT390 Business Database
Administration Unit 3 Slide 30
Normalization Premise
• We have received one or more tables of existing data
• The data is to be stored in a new database
• QUESTION: Should the data be stored as received, or
should it be transformed for storage?
© 2006 ITT Educational Services Inc.
Course Name: IT390 Business Database
Administration Unit 3 Slide 31
How Many Tables?
Should we store these two tables as they are, or should we combine
them into one table in our new database?
© 2006 ITT Educational Services Inc.
Course Name: IT390 Business Database
Administration Unit 3 Slide 32
But first • We need to understand MUCH more about:


The relational model
Relational model terminology
© 2006 ITT Educational Services Inc.
Course Name: IT390 Business Database
Administration Unit 3 Slide 33
The Relational Model
• Introduced in 1970
• Created by E.F. Codd
 He was an IBM engineer
 The model used mathematics known as “relational algebra”
• Now the standard model for commercial DBMS products
© 2006 ITT Educational Services Inc.
Course Name: IT390 Business Database
Administration Unit 3 Slide 34
Important Relational Model Terms
•
•
•
•
•
•
•
•
•
•
•
•
Entity
Relation
Functional Dependency
Determinant
Candidate Key
Composite Key
Primary Key
Surrogate Key
Foreign Key
Referential integrity constraint
Normal Form
Multivalued Dependency
© 2006 ITT Educational Services Inc.
Course Name: IT390 Business Database
Administration Unit 3 Slide 35
Entity
• An entity is some identifiable thing that users want to
track:
 Customers
 Computers
 Sales
© 2006 ITT Educational Services Inc.
Course Name: IT390 Business Database
Administration Unit 3 Slide 36
Relation
• Relational DBMS products store data about entities in
relations, which are a special type of table
• A relation is a two-dimensional table that has the
following characteristics:
 Rows contain data about an entity
 Columns contain data about attributes of the entity
 All entries in a column are of the same kind
 Each column has a unique name
 Cells of the table hold a single value
 The order of the columns is unimportant
 The order of the rows is unimportant
 No two rows may be identical
© 2006 ITT Educational Services Inc.
Course Name: IT390 Business Database
Administration Unit 3 Slide 37
A Relation
© 2006 ITT Educational Services Inc.
Course Name: IT390 Business Database
Administration Unit 3 Slide 38
A Relation with Values
of Varying Length
© 2006 ITT Educational Services Inc.
Course Name: IT390 Business Database
Administration Unit 3 Slide 39
Tables That Are Not Relations:
Multiple Entries per Cell
© 2006 ITT Educational Services Inc.
Course Name: IT390 Business Database
Administration Unit 3 Slide 40
Tables That Are Not Relations:
Table with Required Row Order
© 2006 ITT Educational Services Inc.
Course Name: IT390 Business Database
Administration Unit 3 Slide 41
Alternative Terminology
• Although not all tables are relations, the terms table
and relation are normally used interchangeably
• The following sets of terms are equivalent:
© 2006 ITT Educational Services Inc.
Course Name: IT390 Business Database
Administration Unit 3 Slide 42
Functional Dependency
• A functional dependency occurs when the value of one
(a set of) attribute(s) determines the value of a second
(set of) attribute(s):
StudentID  StudentName
StudentID  (DormName, DormRoom, Fee)
• The attribute on the left side of the functional dependency
is called the determinant.
• Functional dependencies may be based on equations:
ExtendedPrice = Quantity X UnitPrice
(Quantity, UnitPrice)  ExtendedPrice
• Function dependencies are not equations!
© 2006 ITT Educational Services Inc.
Course Name: IT390 Business Database
Administration Unit 3 Slide 43
Functional Dependencies Are Not Equations
ObjectColor  Weight
ObjectColor  Shape
ObjectColor  (Weight, Shape)
© 2006 ITT Educational Services Inc.
Course Name: IT390 Business Database
Administration Unit 3 Slide 44
Composite Determinants
• Composite determinant: A determinant
of a functional dependency that consists
of more than one attribute
(StudentName, ClassName)  (Grade)
© 2006 ITT Educational Services Inc.
Course Name: IT390 Business Database
Administration Unit 3 Slide 45
Functional Dependency Rules
• If A  (B, C), then A  B and A C
• If (A,B)  C, then neither A nor B determines C by itself
© 2006 ITT Educational Services Inc.
Course Name: IT390 Business Database
Administration Unit 3 Slide 46
Functional Dependencies in the
SKU_DATA Table
© 2006 ITT Educational Services Inc.
Course Name: IT390 Business Database
Administration Unit 3 Slide 47
Functional Dependencies in the
SKU_DATA Table
SKU  (SKU_Description, Department, Buyer)
SKU_Description  (SKU, Department, Buyer)
Buyer  Department
© 2006 ITT Educational Services Inc.
Course Name: IT390 Business Database
Administration Unit 3 Slide 48
Functional Dependencies in the
ORDER_ITEM Table
© 2006 ITT Educational Services Inc.
Course Name: IT390 Business Database
Administration Unit 3 Slide 49
Functional Dependencies in the
ORDER_ITEM Table
(OrderNumber, SKU) 
(Quantity, Price,
ExtendedPrice)
(Quantity, Price)  (ExtendedPrice)
© 2006 ITT Educational Services Inc.
Course Name: IT390 Business Database
Administration Unit 3 Slide 50
What Makes Determinant Values Unique?
• A determinant is unique in a relation if, and only if, it
determines every other column in the relation
• You cannot find the determinants of all functional
dependencies simply by looking for unique values in one
column:
 Data set limitations
 Must be logically a determinant
© 2006 ITT Educational Services Inc.
Course Name: IT390 Business Database
Administration Unit 3 Slide 51
Keys
• A key is a combination of one or more columns
that is used to identify rows in a relation
• A composite key is a key that consists of two
or more columns
© 2006 ITT Educational Services Inc.
Course Name: IT390 Business Database
Administration Unit 3 Slide 52
Candidate and Primary Keys
• A candidate key is a key that determines all of the other
columns in a relation
• A primary key is a candidate key selected as the primary
means of identifying rows in a relation:
 There is one and only one primary key per relation
 The primary key may be a composite key
 The ideal primary key is short, numeric and never
changes
© 2006 ITT Educational Services Inc.
Course Name: IT390 Business Database
Administration Unit 3 Slide 53
Surrogate Keys
• A surrogate key as an artificial column added to a
relation to serve as a primary key:
 DBMS supplied
 Short, numeric and never changes – an ideal primary
key!
 Has artificial values that are meaningless to users
 Normally hidden in forms and reports
© 2006 ITT Educational Services Inc.
Course Name: IT390 Business Database
Administration Unit 3 Slide 54
Surrogate Keys
NOTE: The primary key of the relation is underlined
below:
• RENTAL_PROPERTY without surrogate key:
RENTAL_PROPERTY (Street, City,
State/Province, Zip/PostalCode, Country,
Rental_Rate)
• RENTAL_PROPERTY with surrogate key:
RENTAL_PROPERTY (PropertyID, Street, City,
State/Province, Zip/PostalCode, Country,
Rental_Rate)
© 2006 ITT Educational Services Inc.
Course Name: IT390 Business Database
Administration Unit 3 Slide 55
Foreign Keys
• A foreign key is the primary key of one relation that is
placed in another relation to form a link between the
relations:


A foreign key can be a single column or a composite
key
The term refers to the fact that key values are foreign
to the relation in which they appear as foreign key
values
© 2006 ITT Educational Services Inc.
Course Name: IT390 Business Database
Administration Unit 3 Slide 56
Foreign Keys
NOTE:
The primary keys of the relations are underlined and any
foreign keys are in italics in the relations below.
DEPARTMENT (DepartmentName, BudgetCode, ManagerName)
EMPLOYEE
(EmployeeNumber, EmployeeName,
DepartmentName)
© 2006 ITT Educational Services Inc.
Course Name: IT390 Business Database
Administration Unit 3 Slide 57
The Referential Integrity Constraint
• A referential integrity constraint is a statement that
limits the values of the foreign key to those already
existing as primary key values in the corresponding
relation
© 2006 ITT Educational Services Inc.
Course Name: IT390 Business Database
Administration Unit 3 Slide 58
Foreign Key with a
Referential Integrity Constraint
NOTE: The primary key of the relation is underlined and
any foreign keys are in italics in the relations below:
SKU_DATA
ORDER_ITEM
(SKU, SKU_Description, Department, Buyer)
(OrderNumber, SKU, Quantity, Price,
ExtendedPrice)
Where ORDER_ITEM.SKU must exist in SKU_DATA.SKU
© 2006 ITT Educational Services Inc.
Course Name: IT390 Business Database
Administration Unit 3 Slide 59
Modification Anomalies
• Deletion Anomaly
• Insertion Anomaly
• Update Anomaly
© 2006 ITT Educational Services Inc.
Course Name: IT390 Business Database
Administration Unit 3 Slide 60
Modification Anomalies
• The EQUIPMENT_REPAIR table before and after an
incorrect update operation on AcquisitionCost for Type =
Drill Press:
© 2006 ITT Educational Services Inc.
Course Name: IT390 Business Database
Administration Unit 3 Slide 61
Normal Forms
• Relations are categorized as a normal form based on
which modification anomalies or other problems that they
are subject to:
© 2006 ITT Educational Services Inc.
Course Name: IT390 Business Database
Administration Unit 3 Slide 62
Normal Forms
• 1NF – A table that qualifies as a relation is in 1NF
• 2NF – A relation is in 2NF if all of its nonkey attributes are
dependent on all of the primary key
• 3NF – A relation is in 3NF if it is in 2NF and has no
determinants except the primary key
• Boyce-Codd Normal Form (BCNF) – A relation is in
BCNF if every determinant is a candidate key
“I swear to construct my tables so that all nonkey
columns are dependent on the key, the whole key and
nothing but the key, so help me Codd.”
© 2006 ITT Educational Services Inc.
Course Name: IT390 Business Database
Administration Unit 3 Slide 63
Eliminating Modification Anomalies from Functional
Dependencies in Relations
• Put all relations into Boyce-Codd Normal Form (BCNF):
© 2006 ITT Educational Services Inc.
Course Name: IT390 Business Database
Administration Unit 3 Slide 64
Putting a Relation into BCNF:
EQUIPMENT_REPAIR
© 2006 ITT Educational Services Inc.
Course Name: IT390 Business Database
Administration Unit 3 Slide 65
Putting a Relation into BCNF:
EQUIPMENT_REPAIR
EQUIPMENT_REPAIR (ItemNumber, Type, AcquisitionCost,
RepairNumber, RepairDate,
RepairAmount)
ItemNumber  (Type, AcquisitionCost)
RepairNumber  (ItemNumber, Type, AcquisitionCost,
RepairDate, RepairAmount)
ITEM
(ItemNumber, Type, AcquisitionCost)
REPAIR (ItemNumber, RepairNumber, RepairDate,
RepairAmount)
Where REPAIR.ItemNumber must exist in
ITEM.ItemNumber
© 2006 ITT Educational Services Inc.
Course Name: IT390 Business Database
Administration Unit 3 Slide 66
Putting a Relation into BCNF:
New Relations
© 2006 ITT Educational Services Inc.
Course Name: IT390 Business Database
Administration Unit 3 Slide 67
Putting a Relation into BCNF:
SKU_DATA
© 2006 ITT Educational Services Inc.
Course Name: IT390 Business Database
Administration Unit 3 Slide 68
Putting a Relation into BCNF:
SKU_DATA
SKU_DATA
(SKU, SKU_Description, Department, Buyer)
SKU  (SKU_Description, Department, Buyer)
SKU_Description  (SKU, Department, Buyer)
Buyer  Department
SKU_DATA
(SKU, SKU_Description, Buyer)
BUYER (Buyer, Department)
Where BUYER.Buyer must exist in SKU_DATA.Buyer
© 2006 ITT Educational Services Inc.
Course Name: IT390 Business Database
Administration Unit 3 Slide 69
Putting a Relation into BCNF:
New Relations
© 2006 ITT Educational Services Inc.
Course Name: IT390 Business Database
Administration Unit 3 Slide 70
Multi-valued Dependencies
• A multi-valued dependency occurs when a determinant
determines a particular set of values:
Employee  Degree
Employee  Sibling
PartKit  Part
• The determinant of a multivaled dependency can never
be a primary key
© 2006 ITT Educational Services Inc.
Course Name: IT390 Business Database
Administration Unit 3 Slide 71
Multi-valued Dependencies
© 2006 ITT Educational Services Inc.
Course Name: IT390 Business Database
Administration Unit 3 Slide 72
Eliminating Anomalies from Multi-valued
Dependencies
• Multi-valued dependencies are not a problem if they are
in a separate relation, so:
 Always put multivaled dependencies into their own
relation
 This is known as Fourth Normal Form (4NF)
© 2006 ITT Educational Services Inc.
Course Name: IT390 Business Database
Administration Unit 3 Slide 73
Normalization Premise
• We have received one or
more tables of existing
data
• The data is to be stored in
a new database
• QUESTION: Should the
data be stored as
received, or should it be
transformed for storage?
Should we store these two tables as they are, or should
we combine them into one table in our new database?
© 2006 ITT Educational Services Inc.
Course Name: IT390 Business Database
Administration Unit 3 Slide 74
Assessing Table Structure
© 2006 ITT Educational Services Inc.
Course Name: IT390 Business Database
Administration Unit 3 Slide 75
Counting Rows in a Table
• To count the number of rows in a table use the SQL builtin function COUNT(*):
SELECT
FROM
COUNT(*) AS NumRows
SKU_DATA;
© 2006 ITT Educational Services Inc.
Course Name: IT390 Business Database
Administration Unit 3 Slide 76
Examining the Columns
• To determine the number and type of columns in a table,
use an SQL SELECT statement
• To limit the number of rows retreived, use the SQL TOP
{NumberOfRows} keyword:
SELECT TOP (10) *
FROM
SKU_DATA;
© 2006 ITT Educational Services Inc.
Course Name: IT390 Business Database
Administration Unit 3 Slide 77
Checking Validity of Assumed Referential
Integrity Constraints
• Given two tables with an assumed foreign key constraint:
SKU_DATA (SKU, SKU_Description, Department,
Buyer)
BUYER
(BuyerName, Department)
Where SKU_DATA.Buyer must exist in
BUYER.BuyerName
© 2006 ITT Educational Services Inc.
Course Name: IT390 Business Database
Administration Unit 3 Slide 78
Checking Validity of Assumed Referential
Integrity Constraints
• To find any foreign key values that violate the foreign key
constraint:
SELECT
Buyer
FROM
SKU_DATA
WHERE
Buyer NOT IT
(SELECT
Buyer
FROM
SKU_DATA,
BUYER
WHERE SKU_DATA.BUYER =
BUYER.BuyerName;
© 2006 ITT Educational Services Inc.
Course Name: IT390 Business Database
Administration Unit 3 Slide 79
Type of Database
• Updateable database or read-only database?
• If updateable database, we normally want tables in BCNF
• If read-only database, we may not use BCNF tables
© 2006 ITT Educational Services Inc.
Course Name: IT390 Business Database
Administration Unit 3 Slide 80
Normalization:
Advantages and Disadvantages
© 2006 ITT Educational Services Inc.
Course Name: IT390 Business Database
Administration Unit 3 Slide 81
Non-Normalized Table:
EQUIPMENT_REPAIR
© 2006 ITT Educational Services Inc.
Course Name: IT390 Business Database
Administration Unit 3 Slide 82
Normalized Tables:
ITEM and REPAIR
© 2006 ITT Educational Services Inc.
Course Name: IT390 Business Database
Administration Unit 3 Slide 83
Copying Data to New Tables
• To copy data from one table to another, use the
SQL command INSERT INTO TableName
command:
INSERT INTO ITEM
SELECT
DISTINCT ItemNumber, Type,
AcquisitionCost
FROM
EQUIPMENT_REPAIR;
INSERT INTO REPAIR
SELECT
ItemNumber, RepairNumber,
RepairDate, RepairAmmount
FROM
EQUIPMENT_REPAIR;
© 2006 ITT Educational Services Inc.
Course Name: IT390 Business Database
Administration Unit 3 Slide 84
Choosing Not to Use BCNF
• BCNF is used to control anomalies from functional
dependencies
• There are times when BCNF is not desirable
• The classic example is ZIP codes:
 ZIP codes almost never change
 Any anomalies are likely to be caught by normal
business practices
 Not having to use SQL to join data in two tables will
speed up application processing
© 2006 ITT Educational Services Inc.
Course Name: IT390 Business Database
Administration Unit 3 Slide 85
Multi-valued Dependencies
• Anomalies from multivalued dependencies are very
problematic
• Always place the columns of a multivalued dependency
into a separtate table (4NF)
© 2006 ITT Educational Services Inc.
Course Name: IT390 Business Database
Administration Unit 3 Slide 86
Designing Read-Only Databases
• Read-only databases are non-operational databases
using data extracted from operational databases
• They are used for querying, reporting and data mining
applications
• They are never updated (in the operational database
sense – they may have new data imported form time-totime)
© 2006 ITT Educational Services Inc.
Course Name: IT390 Business Database
Administration Unit 3 Slide 87
Denormalization
• For read-only databases, normalization is seldom an
advantage
 Application processing speed is more important
• Denormalization is the joining of data in normalized
tables prior to storing the data
• The data is then stored in non-normalized tables
© 2006 ITT Educational Services Inc.
Course Name: IT390 Business Database
Administration Unit 3 Slide 88
Normalized Tables
© 2006 ITT Educational Services Inc.
Course Name: IT390 Business Database
Administration Unit 3 Slide 89
Denormalizing the Data
INSERT INTO PAYMENT_DATA
SELECT
STUDENT.SID, Name, CLUB.Club,
Cost, AmtPaid
FROM
STUDENT, PAYMENT, CLUB
WHERE
STUDENT.SID = PAYMENT.SID
AND
PAYMENT.Club = CLUB.Club;
© 2006 ITT Educational Services Inc.
Course Name: IT390 Business Database
Administration Unit 3 Slide 90
Customized Tables
• Read-only databases are
often designed with many
copies of the same data,
but with each copy
customized for a specific
application
• Consider the PRODUCT
table:
© 2006 ITT Educational Services Inc.
Course Name: IT390 Business Database
Administration Unit 3 Slide 91
Customized Tables
PRODUCT_PURCHASING (SKU, SKU_Description, VendorNumber,
VendorName, VendorContact_1, VendorContact_2, VendorStreet,
VendorCity, VendorState, VendorZip)
PRODUCT_USAGE (SKU, SKU_Description, QuantitySoldPastYear,
QuantitySoldPastQuarter, QuantitySoldPastMonth)
PRODUCT_WEB (SKU, DetailPicture, ThumbnailPicture,
MarketingShortDescription, MarketingLongDescription, PartColor)
PRODUCT_INVENTORY (SKU, PartNumber, SKU_Description,
UnitsCode, BinNumber, ProductionKeyCode)
© 2006 ITT Educational Services Inc.
Course Name: IT390 Business Database
Administration Unit 3 Slide 92
Common Design Problems
© 2006 ITT Educational Services Inc.
Course Name: IT390 Business Database
Administration Unit 3 Slide 93
The Multivalue, Multicolumn Problem
• The multivalue, multicolumn problem occurs when
multiple values of an attribute are stored in more that one
column:
EMPLOYEE (EmpNumber, Name, Email,
Auto1_LicenseNumber, Auto2_LicenseNumber,
Auto3_LicenseNumber)
• This is another form of a multivalued dependency
• Solution: Like the 4NF solution for multivalued
dependencies, use a separate table to store the multiple
values
© 2006 ITT Educational Services Inc.
Course Name: IT390 Business Database
Administration Unit 3 Slide 94
Inconsistent Values
• Inconsistent values occur when different users or
different data sources use slightly different forms of the
same data value:
 Different codings:
• SKU_Description = 'Corn, Large Can'
• SKU_Description = 'Can, Corn, Large'
• SKU_Description = 'Large Can Corn‘
 Different spellings:
• Coffee, Cofee, Coffeee
© 2006 ITT Educational Services Inc.
Course Name: IT390 Business Database
Administration Unit 3 Slide 95
Inconsistent Values
• Particularly problematic are primary or foreign
key values
• To detect:
 Use referential integrity check already
discussed for checking keys
 Use the SQL GROUP BY clause on
suspected columns
SELECT
NameCount
FROM
GROUP BY
SKU_Description, COUNT(*) AS
SKU_DATA
SKU_Description;
© 2006 ITT Educational Services Inc.
Course Name: IT390 Business Database
Administration Unit 3 Slide 96
Missing Values
• A missing value or null value is a value that
has never been provided
© 2006 ITT Educational Services Inc.
Course Name: IT390 Business Database
Administration Unit 3 Slide 97
Null Values
Null values are ambiguous:
 May indicate that a value is inappropriate:
• DateOfLastChildbirth is inappropriate for a male

May indicate that a value is appropriate but unknown
• DateOfLastChildbirth is appropriate for a female, but
may be unknown

May indicate that a value is appropriate and known,
but has never been entered:
• DateOfLastChildbirth is appropriate for a female, and
may be known but no one has recorded it in the
database
© 2006 ITT Educational Services Inc.
Course Name: IT390 Business Database
Administration Unit 3 Slide 98
Checking for Null Values
• Use the SQL keyword IS NULL to check for null values:
SELECT
FROM
WHERE
COUNT(*) AS QuantityNullCount
ORDER_ITEM
Quantity IS NULL;
© 2006 ITT Educational Services Inc.
Course Name: IT390 Business Database
Administration Unit 3 Slide 99
The General-Purpose Remarks Column
• A general-purpose remarks column is a column with a
name such as:
 Remarks
 Comments
 Notes
• It often contains important data stored in an inconsistent,
verbal and verbose way
 A typical use is to store data on a customer’s
interests.
• Such a column may:
 Be used inconsistently
 Hold multiple data items
© 2006 ITT Educational Services Inc.
Course Name: IT390 Business Database
Administration Unit 3 Slide 100
Summary
• Database Definitions and Manipulations are based on
• Major portion of SQL are divided up into sublanguages.
• DDL is one sublanguage in SQL with which we can create
databases, tables and manipulate those entities.
• A key SQL command in DML is the SELECT statement
• Normalization with Data Models is a key concept to
understanding how a relational database functions and how it
must be properly arranged.
• Without normalization, there will be anomalies and their results
will affect the functioning of the database negatively.
• There are many factors to consider when creating a database
including the type of database and to what extent it will be
normalized.
© 2006 ITT Educational Services Inc.
Course Name: IT390 Business Database
Administration Unit 3 Slide 101
Summary

Did you understand the key points from the
Lesson?

Do you have any questions?
© 2006 ITT Educational Services Inc.
Course Name: IT390 Business Database
Administration Unit 3 Slide 102