example database use of term: object fieldsparamesh/comp1000/ch2-tabledesign-que… · “all...
TRANSCRIPT
1
Copyright © 2008 Pearson Prentice
Hall. All rights reserved.
Relational Databases and Multi-Table Queries
DESIGNING TABLES AND
QUERIES
Example Database
• Inventory
Part No. Description Quantity Unit Price
P001 Widget 10 $1.50
P002 Gadget 12 $10.20
P201 Object 25 $0.12
Example Database • Student database
ID Name Faculty Degree Credit
Points
1234567 Jane Doe Science MSc 50
7654321 John Smith Arts BA 48
Fields
• Split that description of the object into a number of
attributes or fields. • Each field should have a simple and unambiguous
meaning. • The way in which fields are chosen should be guided
by an understanding of how the data is to be manipulated in the database.
• There is no single perfect way to do this; some art is involved.
• Some designs, however, are better than others.
John works for Peter 8 hrs a
day.
Use of term: object – not
ACCESS object.
2
Copyright © 2008 Pearson Prentice
Hall. All rights reserved.
Table Design Considerations – Store Data in its Smallest part
• For greater flexibility, store data in its smallest part – Instead of one field for an address, use many. – Instead of one field for a name, two or three.
Like this
Not like this
Field Examples – Bad
• There is nothing to prevent you from placing an entire body of data in a single field of a single record
Field Examples – Bad
• There is nothing to prevent you from placing each letter in a separate field
Fields
• Every field in Access has a data type (e.g., numeric, text, hyperlink, etc.)
• Giving a field a type restricts the data that can be entered into that field.
– Advantage: Information that does not conform to the data type cannot be entered into the database.
• This is one way to maintain integrity of data.
3
Field Data Types
• Autonumber - MS Access assigns unique id to each record
• Numeric
• Currency
• Yes/No - Boolean: true/false; on/off values
• Text - 255 characters (max)
• Memo - 65 535 chars (max)
• Date/time
• OLE (Object Linking and Embedding) object, eg. image; objects from other Microsoft applications or pictures
• Hyperlink – URL
Why Enforce Data Types?
• Prevent data entry errors.
• Reduce amount of storage space required to store database.
– For example: If we know the object is boolean, allocation is 1 byte (8 bits); if it is autonumber, it is long integer; that is, allocation is 4 bytes(32 bits).
Calculated Fields • Calculated field is a field we may use to hold some
important values computed from other fields.
• TotalPrice below is a calculated field.
Copyright © 2008 Pearson Prentice
Hall. All rights reserved. 11
Part
No.
Descrip
tion
Quant
ity
Unit
Price
P001 Widget 10 $1.50
P002 Gadget 12 $10.20
P201 Object 25 $0.12
TotalPrice =
quantity *
UnitPrice
Copyright © 2008 Pearson Prentice
Hall. All rights reserved.
Table Design Considerations
• Avoid Calculated Fields in Tables.
• Calculated fields should be used mainly in queries and reports.
Calculated fields
in a query
Calculated field in a report
4
Copyright © 2008 Pearson Prentice
Hall. All rights reserved. 13
Design view shows:
- Fields; and their
- Properties.
Properties of Fields
Copyright © 2008 Pearson Prentice
Hall. All rights reserved.
Table Design Considerations: Field Properties Setting Field Size Property
• Example above shows setting the field size (done in Table Design View)
• Always anticipate the current field size may one day need to be larger
Setting field size to 10. For text: 0 – 255 chars. (default: 50 chars) For Autonumber: Long integer. –2,147,483,648 to 2,147,483,647
For Integer: –32,768 to 32,767 (Obtained by selecting a field property and pressing function key F1)
Copyright © 2008 Pearson Prentice
Hall. All rights reserved.
Table Design Considerations – Field Properties
Validation Rules
• Used to avoid data entry errors by restricting what can be entered
• Validation text can be used to provide an explanation of the type of data that is allowed in a field
Set validation rules in the Field Properties grid of Table Design View Examples: < > 0 Entry must be a nonzero value.
> 1000 Or Is Null Entry must be blank or greater than 1000.
Copyright © 2008 Pearson Prentice
Hall. All rights reserved.
Table Design Consideration - Plan for Date Arithmetic
• Using a data type of date/time for all date fields allows the use of date arithmetic
Fields declared
as a data type
of Date/Time
5
Copyright © 2008 Pearson Prentice
Hall. All rights reserved.
Table Design Consideration – Sarbanes Oxley Act (SOX)
• Protects the general public and companies shareholders against fraudulent practices and accounting errors.
– Requires that all business records be saved for a period of fie years and be made available to the SEC (Securities and Exchange Commission) upon request
“All organizations … are required to establish and maintain an audit committee that shall
be responsible for appointment of any registerd public accounting firm ...”
Section 802(a) of the SOX, 18 U.S.C. § 1519 states:
“Whoever knowingly alters, destroys, mutilates, conceals, covers up, falsifies, or makes a
false entry in any record, document, or tangible object with the intent to impede, obstruct,
or influence the investigation or proper administration of any matter within the jurisdiction
of any department or agency of the United States or any case filed under title 11, or in
relation to or contemplation of any such matter or case, shall be fined under this title,
imprisoned not more than 20 years, or both. ”
Copyright © 2008 Pearson Prentice
Hall. All rights reserved.
Table Design Considerations - PNPI
Short for personal, non-public, information
PNPI laws govern the safeguarding of data such as:
social security numbers
credit card numbers
bank account numbers
medical records
Some ways of protecting PNPI
Encrypt Electronic Transmissions
Do Not Store PNPI Locally
Ensure PNPI Security When Working from
Home or Outside the University
Have Computer Equipment Audited
Copyright © 2008 Pearson Prentice
Hall. All rights reserved.
Table Design Considerations – Design Multiple Tables
• Using multiple tables helps reduce redundancy
– The process is also referred to as normalization
Multiple table tabs identify open tables
Multiple tables shown in the Navigation pane
Copyright © 2008 Pearson Prentice
Hall. All rights reserved. 20
NORMALIZATION: Problems with single table
Redundancy problem: Same information appears multiple times. Update
can lead to inconsistencies.
6
Copyright © 2008 Pearson Prentice
Hall. All rights reserved. 21
o It may not be possible to enter partial data.
o Partial deletion of a record may not be possible.
NORMALIZATION: Problems with single table
Copyright © 2008 Pearson Prentice
Hall. All rights reserved. 22
Referential Integrity
Consider the tables Customers and Orders.
1. We cannot add any record in the Orders table unless its customerID is valid in the Customers table.
2. If the primary key for a record in the Customers table changes, all corresponding records in the
Orders table must be modified .
3. If a record in the Customers table is deleted, all corresponding records in the Orders table must be
deleted .
Copyright © 2008 Pearson Prentice
Hall. All rights reserved.
Creating Tables – From the Create Tab • Enter table data directly in fields
– From the Create Tab, click Table • Use a table template
– From the Create Tab, click Table Templates
Enter data directly into a table,
including the field names
Enter field names, data
types and descriptions in
Table Design View
Begin with a template Copyright © 2008 Pearson Prentice
Hall. All rights reserved.
Creating Tables – From the Import Tab
• Click the application from which to import or • Choose the type of file you wish to import
Click the appropriate application
button Choose a file type to import
7
Copyright © 2008 Pearson Prentice
Hall. All rights reserved.
Create Tables – Specifying field names
• After choosing your method of creation begin implementing the table design – Use CamelCase notation for field names – Specify data types – Establish a primary key – Consider the need for a foreign key
Table View
Table Design View
Add field in Table View
Copyright © 2008 Pearson Prentice
Hall. All rights reserved.
Create Tables – Primary Key
• Tables are automatically created with an AutoNumber field which serves as the primary key
• To change the primary key – Select a field in Design View
– Click the primary key icon
Primary Key Field Primary Key icon
Copyright © 2008 Pearson Prentice
Hall. All rights reserved.
Create Tables – Consider a Foreign Key
• Based on the above example: – Customer Id is the foreign key in the Orders Table – This is referred to as a One to Many Relationship
Primary Key Customer ID –
occurring as a Primary Key in Customer Table
Customer ID –will only appear in one record; there must only be one unique id per customer
Foreign Key Customer ID –
occurring as a Regular Field in Orders Table
Customer ID may appear many times – one customer can place many orders
Copyright © 2008 Pearson Prentice
Hall. All rights reserved.
Working with Multiple Tables – Table Relationships
• The strength of Access is the fact that it is a relational database – This means you can have multiple tables and
create relationships between each table
– This helps eliminate redundant data
Relationship between two tables
8
Copyright © 2008 Pearson Prentice
Hall. All rights reserved.
Working with Multiple Tables – Referential Integrity
• Assures that the references to relationships between data is accurate
• Established when creating the relationship between two tables
Enforce Referential Integrity
Copyright © 2008 Pearson Prentice
Hall. All rights reserved.
Working with Multiple tables - Cascades
• When active, data changed in one table (that is in a relationship) will be changed in its related tables.
• Can be set when establishing relationships between tables.
Cascade update and cascade delete
Copyright © 2008 Pearson Prentice
Hall. All rights reserved.
Indexing
• Index properties can be set in the Field Properties pane of table Design View
• Relates the field values to the records that contain the field value
• Helps speed up some sorting and searching processes; but can slow down updation.
Indexed Property
Copyright © 2008 Pearson Prentice
Hall. All rights reserved.
Sharing Data with Excel
• Data can be imported from Excel
– It may be appended to an existing table
– It may be used to create a new table
Excel icon
External Data tab
9
Copyright © 2008 Pearson Prentice
Hall. All rights reserved.
Sharing Data with Excel
• Select the Excel file you would like to import
• Select how you would like to import the data
– Appended – added to the end of an existing table
– New table – creates a new table in a database
– Linked – create a new table that is linked to the source file in Excel
Select the Source
Select the destination
Copyright © 2008 Pearson Prentice
Hall. All rights reserved.
Establishing Relationships
• Click the Database tools and click the
Relationships icon • In the Relationship window, click and drag a
field name from one table to a field name in a related table
Relationships icon Click and drag to create a relationship
Copyright © 2008 Pearson Prentice
Hall. All rights reserved.
Establishing Relationships
• Enter the appropriate settings in the Edit relationships dialog box and click Create
• A join line will appear when one table is joined to another
Infinity symbol notes referential integrity has been applied
Set referential integrity and cascades
Copyright © 2008 Pearson Prentice
Hall. All rights reserved.
Queries
• Queries allow us to ask questions about data
• This record set that answers our question is called a dataset
Employees table
Dataset resulting from querying table for only employees who are Sales Representatives
10
Copyright © 2008 Pearson Prentice
Hall. All rights reserved. 37
Using Query Design View
• Query Design grid has two panes – the tables pane and the design pane
• Striking the F6 key will toggle you between sections
Table pane
Design
pane
Copyright © 2008 Pearson Prentice
Hall. All rights reserved.
Select Query
• Searches associated tables and returns a dataset that matches the query parameters
• Changes made to the dataset will be reflected in the associated tables
Copyright © 2008 Pearson Prentice
Hall. All rights reserved.
Specifying Criteria in a Select Query
• Field row – displays the field name
• Sort row – enables you to sort the dataset
• Show row – controls whether or not you see a field in the dataset
• Criteria row – determines the records that will be selected for display
Fields in design grid allow us to specify criteria for the dataset
Copyright © 2008 Pearson Prentice
Hall. All rights reserved.
Specifying Criteria – Currency and Operands
• Specify criteria with currency – Without the dollar sign – With or without the decimal point
• Use operands such as: – Less than and greater than – Equal to or not equal to
Greater than (>) operand
Currency amount entered without dollar sign
11
Copyright © 2008 Pearson Prentice
Hall. All rights reserved.
Specifying Criteria – Wildcards
• Asterisk - searches for a pattern that includes any number of characters in the position of the asterisk
• Question mark - searches for a pattern that includes a single character in the position of the question mark
Query with asterisk wildcard and resulting dataset
Query with question mark and asterisk wildcard and resulting dataset to specify criteria for the dataset
Copyright © 2008 Pearson Prentice
Hall. All rights reserved.
Specifying Criteria – Null Values
• IS NULL finds only records that have no value
• IS NOT NULL excludes Null value records
Is Null criteria and resulting dataset
IS NOT NULL criteria and partial resulting dataset
Copyright © 2008 Pearson Prentice
Hall. All rights reserved.
Specifying Criteria – And and Or
• OR finds records that can match one or more conditions
• AND finds records that must match all criteria specified
Or Criterion and resulting
dataset
And criterion and resulting
dataset
Copyright © 2008 Pearson Prentice
Hall. All rights reserved.
Copy a Query
• Right click on the query - chose Copy form the shortcut menu
• Right click and chose paste
• In the Paste as dialog box, give the query a new name
12
Copyright © 2008 Pearson Prentice
Hall. All rights reserved.
Run a Query
• Running, or executing, a query is done by clicking the Run command
Run command
Copyright © 2008 Pearson Prentice
Hall. All rights reserved.
Creating Queries – Using the Query Wizard
• From the Create tab, choose Query Wizard for the Other group
• Choose query type from the New Query dialog box
Query Wizard icon
Select Simple Query Wizard
Copyright © 2008 Pearson Prentice
Hall. All rights reserved.
Creating Queries – Using the Query Wizard: continued
• Select the Table/Queries to include and choose the desired fields
• Select aggregate totals needed in the Summary Options box
Copyright © 2008 Pearson Prentice
Hall. All rights reserved.
Creating Queries – Using the Query Wizard: continued
• Title your query and open in Datasheet View or Query Design View
13
Copyright © 2008 Pearson Prentice
Hall. All rights reserved.
Understand Large Database Differences
• Most large companies separate their database into front and back ends
– Front end – contains the objects needed to interact with data, but not the tables where the record values reside
– Back end – contains the tables where the data resides