-Identify entity types

-Determine Primary Keys

-Add relationships

-Identify Min and Max cardinality

-simplify relationships

-document

-add generalization hierarchy

split common attributes into smaller attributes; can facilitate search of embedded data

*replace an attribute with a collection of attributes

Make a weak entity a strong entity and change associated identifying relationships into non-identifying relationships.

-makes it easier to reference entity type in table design;

reference to a weak entity will involve a combined FK with more than 1 column

-most useful for associative entity types, esp M

*remove identifying dependance symbols and possibly add a PK

should be used sparingly;  useful if there are multiple attributes that do not apply to all entities & there is an accepted classification of entities; can also be allied to a collection of entity types

*Starting from supertype:add subtypes, a GH, & redistribute attributes to subtypes

*starting from subtypes: add supertype, GH, and redistribute common attributes and relationships to supertype

-Relationships is redundant if it can be derived from other relationships; be careful removing b/c can be serious error

-

M-N Relationship

(Many-to-Many)

First Normal Form

(1NF)

Second Normal Form

(2NF)

Third Normal Form

(3NF)

the process on combining tables so that they are easier to query

evaluate ONE time and produces a table

-a nested query in which the inner query has no direct reference to the outer query

executes one time for EACH row in the outer query

-a nested query in which the inner query DOES reference the outer query

DBMS service to process a query on a view by executing the query directly on the stored view. The stored view can be materialized on demand or periodically rebuilt from the base table.

1) Create View

2) Run query against view

DBMS service to process a query on a view involving the execution of only ONE query. A query using a view is translated into a query using base tables by replacing references to the view with its definition

*DBMS will bypass view & extract data from original tables

a table derived from base or physical tables using a query

*A view is the Users perception on the database presented in a form

a formatted display of a query using indentation to show grouping and sorting

*major advantage is that users can grasp more readily the meaning of data that are sorted and arranged in an indented manner.

Using a Type I nested query, list the customer number, the name (first and last), and the city of each customer who has a balance greater than $150 and placed an order in February 2004.

SELECT CustNo, CustFirstName, CusstLastName, CustCity

 FROM Customer

 WHERE CustBal > 150 AND CustNo IN

  (SELECT CustNo

    FROM OrderTbl

        WHERE OrdDate BETWEEN #02/01/2010# AND #02/29/2010#)

SELECT CustNo, CustFirstName, CustLastName CustCity

 FROM Customer

 WHERE CustBal > 150 AND EXISTS

    (SELECT CustNo

       FROM OrderTbl

       WHERE OrdDate BETWEEN #02/01/2010# AND #02/29/2010#

          AND Customer.CustNo = OrderTbl.CustNo)

SELECT ProdNo, ProdName, ProdPrice

  FROM Product

  WHERE ProdPrice > 150 AND ProdNo IN

    (SELECT ProdNo

      FROM OrdLine

      WHERE OrdNo IN

        (SELECT OrdNo

          FROM OrderTbl

          WHERE OrdDate = #01/23/2010# ))

Using two Type I nested queries and another join style, list the product number, the name, and the price of products with a price greater than $150 that were ordered in January 2004 by customers with balances greater than $400.

SELECT ProdNo,  ProdName, ProdPrice

  FROM Product

  WHERE ProdPrice > 150 AND ProdNO IN

     (SELECT ProdNo

        FROM OrdLine

        WHERE OrdNo IN

         (SELECT OredrTbl.OrdNo

            FROM OrederTbl, Customer

            WHERE OrdDate BETWEEN #01/01/2010# AND #01/31/2010#

              AND OrderTbl.CustNo =Customer.CustNo AND CustBal > 400 ))

List the order number, the order date, the employee number, and the employee name (first and last) of orders placed on January 23, 2004. List the order even if there is not an associated employee.

SELECT OrdNo, OrdDate, Employee.EmpNo, EmpFirstName, EmpLastName

  FROM OrderTbl LEFT JOIN Employee ON OrderTbl.EmpNo = Employee.EmpNo

  WHERE OrdDate = #01/23/2010#

List the order number, the order date, the employee number, the employee name (first and last), the customer number, and the customer name (first and last) of orders placed on January 23, 2004. List the order even if there is not an associated employee.

SELECT OrdNo, OrdDate, Employee.EmpNo, EmpirstName, EmpLastName, Customer.CustNo, CustFirstName, CustLastName

  FROM (OrderTbl LEFT JOIN Employee ON OrderTbl.EmpNo = Employy.EmpNo)

     INNER JOIN Customer ON Customer.CustNo = OrderTbl.CustNo

  WHERE OrdDate = #01/23/2010#

List all the people in the database. The resulting table should have all columns of the Customer and Employee tables. Match the Customer and Employee tables on first and last names. If a customer does not match any employees, the columns pertaining to the Employee table will be blank. Similarly for an employee who does not match any customers, the columns pertaining to the Customer table will be blank.

SELECT Customer*, Employee*

  FROM Customer LEFT JOIN Employee

    ON Customer.CustFirstName =Employee.EmpFirstName

    AND Customer.CustLastName = Employee.CustLastName

  UNION

SELECT Customer*, Employee*

  FROM Customer RIGHT JOIN Employee

     ON Customer.CustFirstName = Employee.EmpFirstName

     AND Customer.CustLastName = Employee.EmpLastName

For each ‘Ink Jet’ product ordered in January 2004, list the order number, the order date, the customer number, the customer name (first and last), the employee number (if present), the employee name (first and last), the quantity ordered, the product number, and the product name. Include products containing ‘Ink Jet’ in the product name. Include both internet (no employee) and phone orders (taken by an employee).

SELECT OrderTbl.OrdNo, OrdDate, Employee.EmpNo, EmpFirstName, EmpLastName, Customer.CustNo, 

             CustomerFirstName, CustomerLastName, OrdLine.Qty, Product.ProdNo, , ProdName

  FROM ((( OrdTbl LEFT JOIN Employee ON OrderTbl.EmpNo = Employee.EmpNo)

      INNER JOIN Customer ON Customer.CustNo = OrderTbl.CustNo)

      INNER JOIN OrdLine ON OrderTbl.OrdNo = OreLine.OrdNo)

      INNER JOIN Product ON OrdLine.ProdNo = Product.ProdNo

  WHERE OrdDate BETWEEN #01/01/2010# AND #01/31/2010# AND ProdName LIKE '*Ink Jet*'

Using a Type II nested query, list the customer number and the customer name of Colorado customers who have not placed orders in February 2004.

SELECT Customer.CustNo, CustomerFirstName, CustLastName

  FROM Customer

  WHERE CustState = 'CO' AND NOT EXISTS

     (SELECT *

        FROM OrderTbl

        WHERE OrdDate BETWEEN #02/01/2010# AND #02/29/2010#

           AND OrderTbl.CustNo = Customer.CustNo )

Using a Type I nested query, list the customer number and the customer name of Colorado customers who have not placed orders in February 2004.

SELECT Customer.CustNo, CustFirstName, CustLastName

  FROM Customer

  WHERE CustState = 'CO' AND CustNo NOT IN

     (SELECT CustNo

      FROM OrderTbl

      WHERE OrdDate BETWEEN #02/01/2010# AND #02/29/2010#)