discussion 03 (1)dbms accesss
TRANSCRIPT
-
7/29/2019 Discussion 03 (1)DBMS Accesss
1/13
Discussion Session2013.02.08
by Keehyung KimUW-Madison Business School
-
7/29/2019 Discussion 03 (1)DBMS Accesss
2/13
Main parts
Create four tables, set data type, and input data
Set Format correctly (percent, currency)
Set Input Mask for Phone field
Set validation rule for Email field
Extra credit
Add extra validation rules
Add extra fields and records
-
7/29/2019 Discussion 03 (1)DBMS Accesss
3/13
Purpose
Prevent redundant data in database
Use data in more efficient way
Relational Database (a database using relationship)
RowID Student Program Course Credits Instructor1 Tom B.S. Buddhism 3 Buddha
2 Tom B.S. Database 3 Lazimy
3 Jenny PhD Database 3 Lazimy
4 Jenny PhD Buddhism 3 Buddha
5 Jenny PhD Chistians 3 Jesus
6 Jenny PhD Evolution 3 Darwin
SID Student Program1 Tom B.S
2 Jenny PhD
CID Course Credits Instructor1 Database 3 Lazimy
2 Buddhism 3 Buddha
3 Chistians 3 Jesus
4 Evolution 3 Darwin
-
7/29/2019 Discussion 03 (1)DBMS Accesss
4/13
One-to-Many
Most frequently used relationship
The basic relationship in Access
One side should have a unique key
One-to-One
Both side should be linked by unique key fields
Many-to-Many
Access does not support direct Many-to-Many relationship
Need to make meta-table for this relationship
For example, tblOrderProductis meta-table for this relationship
-
7/29/2019 Discussion 03 (1)DBMS Accesss
5/13
One-to-many
One-to-onemany-to-many
-
7/29/2019 Discussion 03 (1)DBMS Accesss
6/13
Relationship between two fields within one table
Example: spouse relationship among customers
How-to
Add the same table one more time in Relationships window
Link two tables (the same table)
Go to Edit Relationship to see
correct relationship type
-
7/29/2019 Discussion 03 (1)DBMS Accesss
7/13
-
7/29/2019 Discussion 03 (1)DBMS Accesss
8/13
In one-to-many relationship,
- Parent table: tblCustomer(one-side)- Child table: tblOrder(many-side)
-
7/29/2019 Discussion 03 (1)DBMS Accesss
9/13
A Foreign Key is a field in a relational table that matches
a candidate key of another table.
The foreign key can be used to cross-reference tables.
Source: http://en.wikipedia.org/wiki/Foreign_key
Example
In previous slide, CID in tblOrder is Foreign Key.
http://en.wikipedia.org/wiki/Foreign_keyhttp://en.wikipedia.org/wiki/Foreign_key -
7/29/2019 Discussion 03 (1)DBMS Accesss
10/13
Give powerful limitation on editing
data to keep database consistent
Limitation given by Access
Cannot add a child record with a non-existing parent record
Cannot delete a parent record if it has child records
Cannot change a FK in child record if the FK does not have a
match in parent table
Cannot change a PK in parent record if linked with child records
-
7/29/2019 Discussion 03 (1)DBMS Accesss
11/13
Cascade update
When you change a PK in parent record,
all linked FKs in child records will be changed, too
Cascade delete
When you delete a parent record,
all linked child records will be deleted, too
Results
Keep consistency of database But, be extremely careful! The data can be all gone by one click!
Do not use Cascade option on your project!
-
7/29/2019 Discussion 03 (1)DBMS Accesss
12/13
Due date: before 9:55 am on Feb. 15 Main problems
Create relationships in Access
Understand the concept of PK and FK Create Self One-to-one relationship (Extra credit)
Submission
Your Access database file (.accdb)
Text file answering the five questions (.txt)
Electronically submit into Discussion Homework 2dropbox
-
7/29/2019 Discussion 03 (1)DBMS Accesss
13/13
Related readings: Chapter 5