chap08 - Chapter 8: Advanced SQL Modern Database Management...

Info iconThis preview shows pages 1–8. Sign up to view the full content.

View Full Document Right Arrow Icon
1 Chapter 8: Advanced SQL Modern Database Management Jeffrey A. Hoffer, Mary B. Prescott, Fred R. McFadden
Background image of page 1

Info iconThis preview has intentionally blurred sections. Sign up to view the full version.

View Full DocumentRight Arrow Icon
3 The Physical Design Stage of SDLC (figures 2-4, 2-5 revisited) Purpose –programming, testing, training, installation, documenting Deliverable – operational programs, documentation, training materials, program/data structures Database activity – physical database design and database implementation Planning Analysis Physical Design Implementation Maintenance Logical Design Physical Design Implementation
Background image of page 2
4 Processing Multiple Tables – Joins Join – a relational operation that causes two or more tables with a common domain to be combined into a single table or view Equi-join – a join in which the joining condition is based on equality between values in the common columns; common columns appear redundantly in the result table Natural join – an equi-join in which one of the duplicate columns is eliminated in the result table Outer join – a join in which rows that do not have matching values in common columns are nonetheless included in the result table (as opposed to inner join, in which rows must have matching values in order to appear in the result table) Union join – includes all columns from each table in the join, and an instance for each row of each table The common columns in joined tables are usually the primary key of the dominant table and the foreign key of the dependent table in 1:M relationships
Background image of page 3

Info iconThis preview has intentionally blurred sections. Sign up to view the full version.

View Full DocumentRight Arrow Icon
5 The following slides create tables for this enterprise data model
Background image of page 4
6 These tables are used in queries that follow Figure 8-1 Pine Valley Furniture Company Customer and Order tables with pointers from customers to their orders
Background image of page 5

Info iconThis preview has intentionally blurred sections. Sign up to view the full version.

View Full DocumentRight Arrow Icon
7 For each customer who placed an order, what is the customer’s name and order number? SELECT CUSTOMER_T.CUSTOMER_ID, CUSTOMER_NAME, ORDER_ID FROM CUSTOMER_T, ORDER_T WHERE CUSTOMER_T.CUSTOMER_ID = ORDER_T.CUSTOMER_ID; Join involves multiple tables in FROM clause Natural Join Example WHERE clause performs the equality check for common columns of the two tables Note: from Fig. 1, you see that only 10 Customers have links with orders. Only 10 rows will be returned from this join.
Background image of page 6
8 List the customer name, ID number, and order number for all customers. Results will include customer information for customers that do and do not have an order SELECT CUSTOMER_T.CUSTOMER_ID, CUSTOMER_NAME,
Background image of page 7

Info iconThis preview has intentionally blurred sections. Sign up to view the full version.

View Full DocumentRight Arrow Icon
Image of page 8
This is the end of the preview. Sign up to access the rest of the document.

Page1 / 30

chap08 - Chapter 8: Advanced SQL Modern Database Management...

This preview shows document pages 1 - 8. Sign up to view the full document.

View Full Document Right Arrow Icon
Ask a homework question - tutors are online