Transcript Slides

David M. Kroenke’s
Database Processing:
Fundamentals, Design, and Implementation
Chapter Seven:
SQL for Database Construction
and Application Processing
Part One
DAVID M. KROENKE’S DATABASE PROCESSING, 10th Edition
© 2006 Pearson Prentice Hall
7-1
View Ridge Gallery
• View Ridge Gallery is a small art gallery that has
been in business for 30 years .
• It sells contemporary European and
North American fine art.
• View Ridge has one owner,
three salespeople, and two workers.
• View Ridge owns all of the art that it sells;
it holds no items on a consignment basis.
DAVID M. KROENKE’S DATABASE PROCESSING, 10th Edition
© 2006 Pearson Prentice Hall
7-2
Application Requirements
• View Ridge application requirements:
– Track customers and their artist interests
– Record gallery's purchases
– Record customers' art purchases
– List the artists and works that have appeared
in the gallery
– Report how fast an artist's works have sold
and at what margin
– Show current inventory in a Web page
DAVID M. KROENKE’S DATABASE PROCESSING, 10th Edition
© 2006 Pearson Prentice Hall
7-3
View Ridge Gallery Database Design
DAVID M. KROENKE’S DATABASE PROCESSING, 10th Edition
© 2006 Pearson Prentice Hall
7-4
SQL DDL and DML
DAVID M. KROENKE’S DATABASE PROCESSING, 10th Edition
© 2006 Pearson Prentice Hall
7-5
The Database Design for ARTIST
and WORK
DAVID M. KROENKE’S DATABASE PROCESSING, 10th Edition
© 2006 Pearson Prentice Hall
7-6
CREATE TABLE
• CREATE TABLE statement is used for creating
relations
• Each column is described with three parts:
column name, data type, and optional
constraints
• Example:
DAVID M. KROENKE’S DATABASE PROCESSING, 10th Edition
© 2006 Pearson Prentice Hall
7-7
Data Types
DAVID M. KROENKE’S DATABASE PROCESSING, 10th Edition
© 2006 Pearson Prentice Hall
7-8
Constraints
• Constraints can be defined within the CREATE
TABLE statement, or they can be added to the
table after it is created using the ALTER table
statement
• Five types of constraints:
–
–
–
–
–
PRIMARY KEY may not have null values
UNIQUE may have null values
NULL/NOT NULL
FOREIGN KEY
CHECK
DAVID M. KROENKE’S DATABASE PROCESSING, 10th Edition
© 2006 Pearson Prentice Hall
7-9
Creating Relationships
DAVID M. KROENKE’S DATABASE PROCESSING, 10th Edition
© 2006 Pearson Prentice Hall
7-10
Implementing Cardinalities
DAVID M. KROENKE’S DATABASE PROCESSING, 10th Edition
© 2006 Pearson Prentice Hall
7-11
Default Values and Data Constraints
DAVID M. KROENKE’S DATABASE PROCESSING, 10th Edition
© 2006 Pearson Prentice Hall
7-12
SQL for Constraints
DAVID M. KROENKE’S DATABASE PROCESSING, 10th Edition
© 2006 Pearson Prentice Hall
7-13
ALTER Statement
• ALTER statement changes table structure,
properties, or constraints after it has been
created
• Example:
ALTER TABLE ASSIGNMENT
ADD CONSTRAINT EmployeeFK FOREIGN KEY (EmployeeNum)
REFERENCES EMPLOYEE (EmployeeNumber)
ON UPDATE CASCADE
ON DELETE NO ACTION;
DAVID M. KROENKE’S DATABASE PROCESSING, 10th Edition
© 2006 Pearson Prentice Hall
7-14
Adding and Dropping Columns
• The following statement will add a column
named MyColumn to the CUSTOMER
table:
ALTER TABLE CUSTOMER ADD MyColumn Char(5) NULL;
• You can drop an existing column with the
statement:
ALTER TABLE CUSTOMER DROP COLUMN MyColumn;
DAVID M. KROENKE’S DATABASE PROCESSING, 10th Edition
© 2006 Pearson Prentice Hall
7-15
Adding and Dropping Constraints
• ALTER can be used to add a constraint as
follows:
ALTER TABLE CUSTOMER ADD CONSTRAINT MyConstraint CHECK
([Name] NOT IN ('Robert No Pay'));
• ALTER can be used to drop a constraint:
ALTER TABLE CUSTOMER DROP CONSTRAINT MyConstraint;
DAVID M. KROENKE’S DATABASE PROCESSING, 10th Edition
© 2006 Pearson Prentice Hall
7-16
Removing Tables
• SQL DROP TABLE:
DROP TABLE [TRANSACTION];
• If there are constraints:
ALTER TABLE CUSTOMER_ARTIST_INT
DROP CONSTRAINT Customer_Artist_Int_CustomerFK;
ALTER TABLE [TRANSACTION]
DROP CONSTRAINT TransactionCustomerFK;
DROP TABLE CUSTOMER;
DAVID M. KROENKE’S DATABASE PROCESSING, 10th Edition
© 2006 Pearson Prentice Hall
7-17
SQL DML - INSERT
• INSERT command:
INSERT INTO ARTIST ([Name], Nationality, Birthdate,
DeceasedDate)
VALUES ('Tamayo', 'Mexican', 1927, 1998);
• Bulk INSERT:
INSERT INTO ARTIST ([Name], Nationality, Birthdate)
SELECT [Name], Nationality, Birthdate
FROM IMPORTED_ARTIST;
DAVID M. KROENKE’S DATABASE PROCESSING, 10th Edition
© 2006 Pearson Prentice Hall
7-18
David M. Kroenke’s
Database Processing
Fundamentals, Design, and Implementation
(10th Edition)
End of Presentation:
Chapter Seven Part One
DAVID M. KROENKE’S DATABASE PROCESSING, 10th Edition
© 2006 Pearson Prentice Hall
7-19