Transcript DB Tables and Relationships

IE 423 – Design of Decision
Support Systems
Database
development –
Relationships and
Queries
Important Events
0 days to St. Patrick’s Day
Happy St. Patrick’s Day
2 days to deadline for DSS Analysis
Report
4 days to Spring Break
Neighbor08a.mdb
Neighbor08a.mdb
Neighbor08a.mdb
Neighbor08a.mdb
Remember that we split our House
Survey data into two relations (tables)
…so now we need to create a table for
the rest of the data
Don’t forget that each record must have
a unique house identifier
…and what we will use this for?
New tables for Neighbor08
database
New tables for Neighbor08
database
Neighbor08a.mdb
Make a relationship between these two
tables
How do you do this?
What kind of relationship is this?
Neighbor08a.mdb
Then, we are also interested in who lives in
these houses…
…so create another table for people
You will need –






PersonID
HouseID –to tell which house they live in
FirstName
LastName
DOB (what data type?)
CellPhone (use an input mask)
Neighbor08a.mdb
Neighbor08a.mdb
Make a relationship between what two
tables?
How do you do this?
What kind of relationship is this?
Referential Integrity – what does
it really mean?
When we have a one-to-many relationship, we might
call the table on the one side the parent table,…
And the table on the many side the child table
And for any given relationship, we can call the
primary/foreign key pair in the related tables linking
fields
Referential Integrity – all foreign key values
in a child table must come from (match)
primary key values in the parent table
Referential Integrity – what does
it really mean?
Referential Integrity – Five Rules





The Linking field in the parent table must be a primary key
or must be indexed with unique value entries
Linking fields must be of the same data type, exception
autonumber and number (with length=ReplicationID)
When you enforces Referential Integrity no existing records
in the tables can violate referential integrity
With Referential Integrity enforced, no record can be
deleted where its key value matches a value use in foreign
key in the child table
With Referential Integrity enforced, we cannot change
values of the linking field in the parent table there is a
corresponding value in the linking field of the child table
College.mdb
Referential Integrity – what does
it really mean?
Referential Integrity – what does
it really mean?
Cascading



So, how do we do these things?
Access helps
Cascading Deletes
 If you delete the record in the parent table that contains
the Primary key value, access will automatically delete
matching records in the child table
 Think about this

Cascading Deletes
 If you change a primary key value in the parent table,
Access will automatically change the corresponding
foreign key values in all records in child table
College.mdb
Suppose you need to create a course
registration system for college courses (or
workshops, etc.)
What are the objects that you need to work
with?

Students, Courses, ???
So, you have a table of students
You have table of courses
How do you relate these two tables?
Does this present any problems?
College.mdb
Solution – create two one-to-many
relationships,…
…but to do this you are missing
something –
What?
College.mdb
***Open College.mdb
Build a registration system



Enroll students in courses
Any student can be in one or more courses
Any course can have one or more (or zero)
students
College.mdb
Build a Registrations table
In general terms this is sometimes
called a linking table
College.mdb
Define the relationships between


Students & Registrations
Courses & Registrations
Register students for courses
Queries
You’ve built tables,…
You’ve added data…
So, how do you use this data
Queries (at least that’s one answer)
Queries
Queries retrieve and, maybe, manipulate data
in your database
Several kinds of queries

Select Query – retrieves data from tables
 Simple Query – from a single table
 Complex Query – from multiple tables



Parameter Query – dynamic input of criteria
Crosstab Queries – statistical aggregation of data
…
Queries
Several kinds of queries (…continued)

Action Queries – operate on data in a table
or multiple tables
 Delete Queries – delete records based on
selection criteria
 Update Queries – modify field values based on
selection criteria
 Append Query – appends records from other
tables to existing table
Queries
Select Queries

Single table query
Queries
Select Queries




Single table query
Similar to defining a table, …
…except query field come from other table fields
(or other queries)
To build a query you need
 To create column (fields)
 Pick the table from which to pull the field (no really an
issue in a simple query
 Sort or not
 Show field or not
 Define Select criteria
Queries
Select Queries




Fields and tables drag and drop
Sort – left to right order – if checked
Show – do you want to see it?
Criteria
 Logical test for record selection


Criteria in multiple columns – “AND”ed
Logical OR – add criteria to more rows
Queries
Select Queries




Build a simple query to
Select all Senior Industrial Engineering
students
Select all Seniors and Juniors
Select all Senior I.E. student and all Juniors
Queries
Select Queries




Build a simple query to
Select all Senior Industrial Engineering
students
Select all Seniors and Juniors
Select all Senior I.E. student and all Juniors
Queries
Select Queries

Complex Queries
 Similar to simple queries, except…
 Add more tables to the design grid
 Tables need relationships
 …may be already defined,…
 …or you can design them in the query design
grid
Queries
Select Queries

Complex Queries
 Connecting multiple tables in a query is called a
join operation
 Two major kinds of joins
 Results include records from both source
tables only if records match, otherwise not
included Inner Join
 Include a selected record from one table
(regardless of whether it has a match in the
other table) and records from the other table
that match records in the first table Outer
Join
Queries
Select Queries

Complex Queries
 OK, so what is a left outer join vs. a right outer
join?