Transcript query - Reach

Test Review
“Exam 2: Prof.
Reinhardt”
Computer Resource Center
REACH 2015
1
Test Overview
• Very Little Application Problems
• Wiley Chapter’s 5, 7, 11
• Queries: Append, Delete, Update,
Calculate, Select
• Types of Relationships (One-many, manymany, one-one)
• Other Assess Database Concepts and
Definitions
2
Database Design
Basic Terminology:
• A table consists of data that is arrayed in rows and
columns.
• A row of data is called a record.
• A column of data is called a field.
Thus, a record is a set of related fields. The fields in a
table should be related to one another in some way.
3
Basic Terminology
• Primary key field is a field in which each record has
a unique value. e.g. The SSN
• Foreign key is the primary key of another table. e.g.
suppose we need to link the employee table with
the Hours worked table for payroll purposes. The
Employee ID number (EIN) is the primary key of the
employees table but the foreign key of the hours
worked table.
*Every foreign key must be associated with a primary
key in another table.
4
Basic Terminology
• A form is a database object that is created
from an existing table to make the process
of entering data more user-friendly
• A query is the database equivalent of a
question that is posed about data in a table
(or tables).
• Queries can be designed to search multiple
tables but these tables should be
connected by a join operation.
5
Cardinality of
Relationships
• In a one-to-one relationship, one instance of the first
entity is related to just one instance of the second
entity
• In a one-to-many relationship, one instance of the
first entity is related to many instances of the
second entity, but each instance of the second
entity is related to only one instance of the first
entity
• In a many-to-many relationship, one instance of the
second entity is related to many instances of the
second entity, and one instance of the second
entity is related to many instances of the first
6
Editing Relationships
Referential Integrity- a set of rules Access enforces to
maintain consistency between related tables when
you update data in a database.
Cascade Delete – When you select this option, if you
delete a record from one table, corresponding
records in the other table are also deleted
Cascade
7
8
Types of Queries
Select Query
• Queries that answer a question
• “Selects” relevant data from database records
• Helps you get just the data you need in a
Datasheet view
Append Query
• Adds a record to the end of the table
• Works by selecting records from one or more data
sources and copying them to an existing table
9
Types of Queries
Update Query
• Designed to change data in records
• Often updates information in real time (think online
purchases and updating the on-hand amount of
that item)
• Begins as a “Select” query, then begins by clicking
“update”
Delete Query
• Deletes entire records from a table
• Works similar to update query (must start with select
query)
10
Use Update or Delete?
Type of query
When to use it
Results
Use a delete query
To remove entire records
(rows) from a table or
from two related tables
simultaneously.
Delete queries remove all
the data in each field,
including the key value
that makes a record
unique
Use an update query
To delete individual field Makes it easier to delete
values from a table.
values by updating the
existing values to either a
null value (that is, no
data) or a zero-length
string (a pair of double
quotation marks with no
space between them). 11
12
Database Design
Database design concepts:
Entities an entity is a tangible thing or an event. It is a person,
place, thing or concept about which data can be collected.
Consider the following examples:
1) The database of a video store would have one entity named
video and another named customer (These are physical
entities).
2) Organizations incur expenses from paying hourly employees
and purchasing materials from suppliers. Hours worked and
purchases are event entities in the database of most
organizations.
3) The library lends books for free. If you were to think of
checking out a book as a sales transaction for zero revenue,
how would you handle the revenue generating event?
The event entity here is the number of checkouts.
13
Database Design
• A book can have more than one author. An author
can write more than one book. How would you
describe the relationship between authors and
books?
Many-to-many
• A member can borrow any number of books at one
checkout. A book can be checked out more than
once. How would you describe the relationship
between books and checkouts?
Many-to-many
14
Database Design
• Attributes an attribute is a characteristic of an
entity. These attributes become the table’s field.
• E.g. what are the attributes for the entity
“Customer”?
Customer ID, First name, Surname, Date of Birth,
Address and Phone no.
• What are the attributes for the entity “Fashion
Model”?
Name, Height, Weight, Dress size, Hair color and Eye
color.
15
Database Design Rules
• Rule 1: You do not need a table for the business
The database represents the entire business. Thus in
the practice example* The library is not an entity.
• Rule 2: Identify the entities in the business
description
In our example the entities are Members, employees
and books.
16
Database design rules
• Rule 3: Look for relationships among the entities
one-to-many, one-to-one, and many-to-many.
In our example: one-to-many: a member can check
out more than one book.
• Rule 4: Look for attributes of each entity and
Primary Key
designate a primary key.
Attributes of members: name, DOB, phone no., email
address, member ID card number …etc.
Employees: name, # of hours worked, job title,…etc.
Books: name, authors, type, status, member ID card
number…etc.
Foreign key
17
Database Design Rules
• Rule 5: Avoid data redundancy
you should not include extra (redundant) fields in a
table. Redundant fields take up extra disk space and
lead to data entry errors because the same value
must be entered in multiple tables.
Rule 6: Do not include a field if it can be calculated
from other fields
A calculated field is made using the query generator
as we will see later.
18
Metadata & Hierarchy of Data
• Metadata: Data about data. Metadata describes
how and when and by whom a particular set of
data was collected, and how the data is
formatted. A text document's metadata may
contain information about how long the document
is, who the author is, when the document was
written, and a short summary of the document.
• Hierarchy of Data: refers to the systematic
organization of data, often in a hierarchical form.
Data organization involves fields, records, files and
so on.
19
Data Dictionary
• Data Dictionary: In database management systems,
a file that defines the basic organization of a database.
A data dictionary contains a list of all files in the
database, the number of records in each file, and the
names and types of each field. Most database
management systems keep the data dictionary hidden
from users to prevent them from accidentally destroying
its contents.
• Data dictionaries do not contain any actual data from
the database, only bookkeeping information for
managing it. Without a data dictionary, however, a
database management system cannot access data
from the database.
20
Creating tables
• Create tab  Table design (in the tables group)
• Fill in the table’s fields.
Choose a suitable data type for each field.
• For example
text  Last Name
Date/time  Date Hired
Yes/No  US Citizen
•
Change the lengths of the text fields from 255 to 30
spaces.
21
Creating tables
• We need to make the Employee ID a primary key:
Select the Employee ID field then in the Table tools
Design click Primary Key tab
• After you finish click the File tab  Save object as 
then name your table
• Note that this is different from Save Database as which
saves the whole database.
22
Creating Compound Primary Key
• The two fields must be appear one after the other in
the table definition screen (plan ahead for that
format).
• Highlight one field, hold down the control key and
highlight the next field.
• Go to table tools  design tab Primary Key
23
Adding records to a table
• Double click the table’s name in the navigation
pane at the left of the screen then start typing data
directly into the cells.
• Enter your data one field value at a time.
• Each time you finish entering a value, press Enter to
move the cursor to the next cell.
• After you enter the data in the last cell in a row, the
cursor moves to the first cell in the next row and
Access automatically saves the record
• No need to save through the File tab.
24
Creating Queries
• Using Calculated Fields in Queries:
• E.g. suppose we have the following table.
•
if you have an existing field containing the number
of boxes of Girl Scout cookies sold, you may want to
see how much money was collected for
each cookie sale. In this example, the boxes sold
for $3.95 each.
25
Using Calculated Fields in Queries
• In this case we will create a calculated field in a
query.
• Create tab Query design
Don’t Forget the Colon.
Also the field name must be enclosed in square
brackets.
26
Using Calculated Fields in Queries
• How to format the calculated field output?
• 1. select the output column by clicking the line
above the calculated field expression.
• 2. the column darkens to indicate the selection.
• 3. Design tab  property sheet  format
27
Avoiding errors when making
calculated fields
1. Do not enter the expression in the criteria cell. Enter
it in the Field cell.
2. Spell, capitalize, and space a field’s name exactly
as you did in the table definition.
3. Don’t use parentheses or curly braces instead of
the square brackets.
28
“Relating” two or more tables
by the join operation
• Suppose you want to see the last names, employee
IDs, wage rates, salary status, and citizenship only
for US citizens and hourly workers.
• Problem: the data is spread across two tables.
• Solution: add both tables and pull down the five
fields you need.
• Step 1: Create tab  Query design
• Step 2: Click one table name and hold down the
ctrl button while choosing the other table name.
• Step 3: start pulling down the fields you need and
add the criteria expressions.
29
“Relating” two or more tables
by the join operation
30
“Relating” two or more tables
by the join operation
• you can use calculated fields using more than one
table.
• Just follow the same steps and add the calculated
field in the design view like what we did earlier.
31
Totals Queries
• Assume that you want to see two pieces of information
for hourly workers:
1. The average wage rate
2. 110 percent of the average rate
Step1:
Create the first query, click the design tab  Totals button
in the show/hide group. This will give us the average of the
wage rate field.
Note that: you should type the revised
heading for the wage rate field,
i.e. Average rate: wage rate.
N.B. we need the average of this field.
Also to get the hourly workers only,
enter Criteria=No.
32
Totals Queries
• Now begin a new Query.
But instead of basing it on a table, we will base it on
the previous query.
• Design the new query and create
a calculated field inside it.
33
Update Queries
• Suppose you want to give all non-salaried workers a
$0.50 pay raise.
If you have 3 workers  change the wage rate data in
the table.
If you have 3000 workers  it would be much faster and
more accurate to change the wage rate by using an
update query that adds $0.50 to each non-salaried
employee’s wage rate.
34
Update Queries
• First start by making a select query.
• Then click the update button
in the query type group.
35
Update Queries
We will write the updated
data in the Update to line
in the QBE grid.
We will update only the nonsalaried workers by using a filter
under the salaried field.
36
Update Queries
• When you run the query, the following warning
message will appear.
• Click yes, and the records will be updated.
37
Parameter Queries
• Suppose you want to know how many hours a
particular employee has worked.
1. Run a select query.
2. You will get a message
to enter the employee
ID.
Enter your employee ID
and you will get the
desired information
38
Creating Reports
• Create basic ungrouped report:
• Select the table  create tab  report
39
Creating Reports
• Create Grouped Report:
• Design tab  Group and Sort button in the grouping and tools
group.
• Click the Add group button
then select the desired
table (Employee ID)
40
Creating Reports
• To total the hours worked by each employee:
select the Hours column heading.
Then on the Design tab  totals button in the
grouping and totals group  Sum
Layout
View
41
Creating Reports
• Design tab  Report view from the views group
Report
view
(final
view)
42
Small Quiz
• (Database Concepts) The metadata for a database
describes which of the following properties of a
database?
• It defines the fields in the database tables
• It defines the structure of the database tables
• It defines the sizes and formats of the fields in the
database tables
• It identifies the primary keys
• All of the answers provided are correct
43
• (Database Concepts) Because it is relatively common and
everyone has one, the date of birth is often used as a primary
key in a database table for identifying employees within a
corporation.
True
False
• (Database Concepts) What is the term for a collection of
related records, such as the scores for all of the students in a
given section of CIS300?
•
•
•
•
•
Field
Character
Item
Table
Query
44
• (Database Concepts) Which of the following is the
smallest piece of data that can be stored within a
database? (select at least one, but no more than
two answers)
• bit
• byte
• character
• field
• record
• table
45
•
•
•
•
•
•
•
(Database Concepts) Which of the following terms
is often used to refer to the columns shown in a
database table? (select at least one, but no more
than two answers)
row
data element
metadata
field
record
data type
46
•
•
•
•
•
•
(Database Concepts) A field within a database is
defined as a single item of data that is common to
all records and occurs as an individual _____ within
a table.
cell
column
row
value
entry
47
• (Microsoft Access) The Report Wizard is an easy to
use feature in Access that guides you through a
series of questions and then generates a
customized report based on the answers you
provide.
• True
• False
48
• (Microsoft Access) Which of the following types of
fields is used to produce a value from an expression
or function?
• Calculated
• AutoNumber
• OLE Object
• Indexed
• Number
49
• (Microsoft Access) Which of the following objects
provides a simple approach for less-experienced
users to extract information from a database?
(select at least one, but no more than two answers)
•
•
•
•
•
•
Forms
Macros
Modules
Queries
Reports
Tables
50
• While a database itself is a collection of several related
files, the program used to build databases, populate
them with data, and manipulate the data is called a(n)
• _DBMS – Database Management System.
• There are two overall approaches to maintaining data:
the traditional file approach—which has no mechanism
for tagging, retrieving, and manipulating data—and the
____, which does have this mechanism.
• Database Approach
• Data Approach
• Datafile Approach
• Indexed file approach
51
52
53