Transcript Slide 1

Database Environment
• Entity (Tables)
• Highest level:
• A person, place, object, event, or idea for which data is to be stored
and processed
• Only one specific Entity Name per db
• Ex. SalesRep, Customer, Inventory, Rates, Order, Vendor
• Attribute
• Characteristic or property of an Entity
• Specific data relative solely to the specific entity
• Entity: Person – Attributes: eye color, height, weight
• Entity: SalesRep – Attributes: Rep#, name, Address
• Entity: Customer – Attributes: Cus#, Business Name, Name, Address
• Entity: Inventory – Attributes: Inv#, Description, Vendor#, Cost
• Entity: Vendor – Attributes: Vendor#, Business Name, Rep#, Phone#
• Entity: Order – Attributes: Cus#, Rep#, Inv#, Qty, Cost, Total
• Also called a FIELD or COLUMN by some db vendors
• Relationship
• An association between entities
• Ex. SalesRep may have many Customers
• Ex. A Customer will have only one SalesRep
Cis303b_class02.ppt
Database/File Access
User
Terminal/PC
Computer
Software
Database
Engine
DBMS
DISK
File
Table
Table
Database
MEMORY
Table
Cis303b_class02.ppt
Table
Database Layout
Database: mybusiness.db
Table (Entity)
Customer
Table (Entity)
Inventory
Attributes
Cus#
Bus Name
Name
Address
Attributes
Inv#
Description
Vendor#
Cost
Table (Entity)
SalesRep
Attributes
Rep#
Name
Address
Cis303b_class02.ppt
Table (Entity)
Vendor
Attributes
Vendor#
Bus Name
Rep#
Phone#
Table (Entity)
Order
Attributes
Cus#
Rep#
Inv#
Qty
Cost
Total
Relationships
Database Layout
Database: mybusiness.db
Table (Entity)
Inventory
Table (Entity)
Customer
Attributes
Cus#
Bus Name
Name
Address
Rep#
Table (Entity)
SalesRep
Attributes
Rep#
Name
Address
Cis303b_class02.ppt
Attributes
Inv#
Description
Vendor#
Cost
Table (Entity)
Vendor
Attributes
Vendor#
Bus Name
Rep#
Phone#
Table (Entity)
Order
Attributes
Cus#
Rep#
Inv#
Vendor#
Qty
Cost
Total
Definitions
Relational Database:
A collection of relations (Entities - Tables)
Entity:
A separately identifiable, high order, item
Table:
The mechanism that contains unique data
concerning one specific entity
Relationships:
• The common element that ties two or more Tables
together.
• Each Table must relate to at least one other Table.
• A Table may relate to more than one other Table
Table Column:
• All data is common in meaning
• Data in each column may be unique for each Row
Table Row:
• Each Row represents one occurrence of a unique
transaction (data might be the same – a customer places an order of 10
items on Feb. 12, 2002 and then places another order of 10 items on Feb. 12,2002
– wanted more)
Table Key:
A single (minimal) column must contain unique
data
Cis303b_class02.ppt
Definitions
Unnormalized Relation:
Each Row in the Table does not have a single
occurrence of data
Records in a Table (Tuples):
Rows in a Table
Fields in a Table (Attributes):
Columns in the Table
Qualify a Table:
• Multiple Tables can have the same name for a
Column
• Identify the specific Table by using the Table name
followed by the Column name
Primary Key:
• At lease one Table Column that contains all unique
Row data
Query:
• Accessing (inputting) data in a database
• Equivalent to a ‘Read’ or ‘Get’ in other languages
Cis303b_class02.ppt
Query-By-Example
(QBE)
A visual method for writing queries
Enter database information into an on-screen grid
Access Design Windows
• Upper portion of the Design Window
• Field (Column) selection of the Table
• Lower portion of the Design Window
• The Design Grid
• Field: the Primary Key to the Table
• Table: the Table name
• Sort: the Field (Column) sort order
• Show: which Fields (Columns) to display
• Criteria: specific data to narrow the report
(AND condition for Compound Criteria)
• Or: default for Field data selection is AND in
the Criteria Row
Cis303b_class02.ppt
Criteria
Criteria:
Conditions that must be satisfied for a Row to be selected
for the query
Ex.:
Only customer number #123
Only Sales Rep # 987
Only sales over $500.00 for Sales Rep #456
Simple Criteria:
Only one condition required to satisfy the query
Ex.: Query for a report for all sales for a specific Sales
Rep
Compound Criteria:
More than one condition required to satisfy the query
Ex.: Query for a report for all sales for a specific Sales
Rep where the sales are over $500.00
Cis303b_class02.ppt
Computed Fields
Computed (Calculated) Field:
• The data displayed is a result of a computation between
one and/or more Fields
• Enter the formula in an empty ‘Field’ Row position
• Enter the name (your choosing) for the computed field
• Enter a colon
:
• Enter a left square bracket
[
• Enter the 1st Table Field to be used in the calculation
• Enter a right square bracket
]
• Enter arithmetic function
• Addition (+), Subtraction (-) Multiplication (*)
Division (/)
• Enter a left square bracket
[
• Enter the 2nd Table Field to be used
• Enter a right square bracket
•Ex.:
]
AvailableCredit:[CreditLimit]-[Balance]
Cis303b_class02.ppt
Calculating Statistics
(Aggregate Functions)
Build in to Access
• Count: total the number of entries in a column
• Sum: total the values in a column
• Avg: Average of the values in a column
• Max: Maximum – largest value in a column
• Min: Minimum – smallest value in a column
Grouping
Identifying records that have one or more identical
attributes
Ex.:
• Identifying all the customers for one particular sales rep
• Identifying all sales over $500.00
• Identifying all customers in a particular state
Cis303b_class02.ppt
Sorting
Placing the output records in a particular order
(sequence)
Sort Key
• The field(s) a report is sorted (order)
• Primary Key (Primary Sort Key, Major Sort Key)
• The highest order
• Secondary Key (Secondary Sort Key, Minor Sort Key)
• The subordinate order to the Primary Key
• Encapsulated in the Primary Key records
Joining
Selecting records from more than one Table
Select the related Tables
Must have a common Field
Data in both Tables is matched and a record selected
via the common Field
Cis303b_class02.ppt
Update Queries
Changes ALL fields of selected records (matched
Criteria)
CAUTION:
Before doing an Update, run a report query to ensure
the selection criteria is correct
Delete Queries
Remove ALL Records (Rows) of selected records
(matched Criteria)
CAUTION:
Before doing an Update, run a report query to ensure
the selection criteria is correct
Create a new Table
Create a Table that does not currently exist
Cis303b_class02.ppt
Select
Structured Query Language (SQL) programming statement
Format:
SELECT fieldname1, fieldname2, fieldname3
FROM tablename
WHERE criteria
GIVING newtablename;
Ex.:
SELECT salesrep;
Produce a listing of all sales representatives Table in no
order
SELECT name, address FROM salesrep;
Produce a listing of all sales representatives name and
address from the salesrep Table in no order
SELECT name, address FROM salesrep WHERE state=‘MI’
Produce a listing of all sales representatives name and
adedress from the salesrep Table in no order that are from
Michigan
Cis303b_class02.ppt
Join
Structured Query Language (SQL) programming statement
Data from more than one Table ‘joined’ to create a multitable report or a new Table
Format:
JOIN tablename1, tablename2
WHERE tablename1.fieldname=tablename2.fieldname
GIVING newtablename;
Project
Structured Query Language (SQL) programming statement
Select specific output fields of a Join to a new output table
Format:
PROJECT table_name_from_the_ join
OVER (desired_field_name1, desired_field_name2,
desired_field_name3)
GIVING newtablename;
Cis303b_class02.ppt
Sample Join/Project
JOIN customer rep
WHERE customer.repnum=rep.repnum
GIVING temptable
PROJECT temptable
OVER (customernum, customername, repnum, lastname)
GIVING new_temp_table;
Explanation
JOIN customer rep
Customer Table and rep Table are joined (accessed)
WHERE customer.repnum=rep.repnum
The common field in each table is identified
GIVING temptable
New Table created from the JOIN statement
PROJECT temptable
Identify input table to use to select specific fields
OVER (customernum, customername, repnum, lastname)
Select the fields to be extracted for the new table
GIVING new_temp_table;
Create the new table with the selected fields
Cis303b_class02.ppt
Union of Tables
Two or more Tables JOINed
Data in ALL tables will be extracted to create a new table
All tables must have same structure (Union Compatible)
• Same number of columns
• Corresponding columns must be of same type data
Table 1
Table 2
Union
Cis303b_class02.ppt
Union of Tables
Tables on page 30 and 31
JOIN orders, customer
WHERE orders.customernum=customer.customernum
GIVING temp1;
Create a table (temp1) with all data where the customer number in both
tables match
PROJECT temp1 OVER customernum, customername
GIVING temp2;
Create a table (temp2) with all the customer number and customer name
SELECT customer WHERE repnum = ’65’ GIVING temp3;
Create a table (temp3) with all records from the customer table where the rep
number is 65
PROJECT temp3 OVER customernum, customername
GIVING temp4;
Create a table (temp4) with only the customer number and customer name
UNION temp2 WITH temp4 GIVING answer
Table TEMP1: all data of all customers that have placed an order
Table TEMP2: name and number only of all customers that have
place an order (fields customer number, customer name)
Table TEMP3: all data of customers assigned to sales rep 65
Table TEMP4: name and number only of all customers that have
sales rep 65 (fields customer number, customer name)
Table ANSWER: all customers that have placed an order or have
sales rep 65
Cis303b_class02.ppt
Intersection of Tables
Two or more Tables JOINed
Data COMMON to all ALL tables will be extracted to create a
new table
All tables must have same structure (Union Compatible)
• Same number of columns
• Corresponding columns must be of same type data
Table 1
Table 2
Intersect
Cis303b_class02.ppt
Intersection of Tables
Tables on page 30 and 31
JOIN orders, customer
WHERE orders.customernum=customer.customernum
GIVING temp1;
Create a table (temp1) with all data where the customer number in both
tables match
PROJECT temp1 OVER customernum, customername
GIVING temp2;
Create a table (temp2) with all the customer number and customer name
SELECT customer WHERE repnum = ’65’ GIVING temp3;
Create a table (temp3) with all records from the customer table where the rep
number is 65
PROJECT temp3 OVER customernum, customername
GIVING temp4;
Create a table (temp4) with only the customer number and customer name
INTERSECT temp2 WITH temp4 GIVING answer
Table TEMP1: all data of all customers that have placed an order
Table TEMP2: name and number only of all customers that have
place an order (fields customer number, customer name)
Table TEMP3: all data of customers assigned to sales rep 65
Table TEMP4: name and number only of all customers that have
sales rep 65 (fields customer number, customer name)
Table ANSWER: all customers that have placed an order and have
sales rep 65
Cis303b_class02.ppt
Difference of Tables
Two or more Tables JOINed
Data COMMON to all ALL tables will be extracted to create a
new table
All tables must have same structure (Union Compatible)
• Same number of columns
• Corresponding columns must be of same type data
Table 1
Table 1
Table 2
Subtract
Cis303b_class02.ppt
Table 2
Equal
Subtraction of Tables
Tables on page 30 and 31
JOIN orders, customer
WHERE orders.customernum=customer.customernum
GIVING temp1;
Create a table (temp1) with all data where the customer number in both
tables match
PROJECT temp1 OVER customernum, customername
GIVING temp2;
Create a table (temp2) with all the customer number and customer name
SELECT customer WHERE repnum = ’65’ GIVING temp3;
Create a table (temp3) with all records from the customer table where the rep
number is 65
PROJECT temp3 OVER customernum, customername
GIVING temp4;
Create a table (temp4) with only the customer number and customer name
SUBTRACT temp4 FROM temp2 GIVING answer
Table TEMP1: all data of all customers that have placed an order
Table TEMP2: name and number only of all customers that have
place an order (fields customer number, customer name)
Table TEMP3: all data of customers assigned to sales rep 65
Table TEMP4: name and number only of all customers that have
sales rep 65 (fields customer number, customer name)
Table ANSWER: all customers that have NOT placed an order with
sales rep 65
Cis303b_class02.ppt
Product of Tables
(Multiplying Tables)
[Cartesian Product]
Concatenating EVERY ROW in Table1 with EVERY ROW in
Table 2
The number of Rows of one table multiplied by the number
of Rows of the second table
Table 1
Table 1
Table 2
Product
Cis303b_class02.ppt
Table 2
Equal
Division of Tables
(Dividing Tables)
All the matches between the primary table and the divisor
table are selected
Primary
Primary
Divisor
Divide
Cis303b_class02.ppt
Divisor
Equal