Ie 423 design of decision support systems
Download
1 / 34

IE 423 – Design of Decision Support Systems - PowerPoint PPT Presentation


  • 71 Views
  • Uploaded on

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.

loader
I am the owner, or an agent authorized to act on behalf of the owner, of the copyrighted work described.
capcha
Download Presentation

PowerPoint Slideshow about ' IE 423 – Design of Decision Support Systems' - sage


An Image/Link below is provided (as is) to download presentation

Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author.While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server.


- - - - - - - - - - - - - - - - - - - - - - - - - - E N D - - - - - - - - - - - - - - - - - - - - - - - - - -
Presentation Transcript
Ie 423 design of decision support systems

IE 423 – Design of Decision Support Systems

Database development – Relationships and Queries


Important events
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 mdb3
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?




Neighbor08a mdb4
Neighbor08a.mdb

Make a relationship between these two tables

How do you do this?

What kind of relationship is this?


Neighbor08a mdb5
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 mdb7
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
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 mean1
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




Referential integrity what does it really mean3
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 mdb1
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 mdb2
College.mdb

Solution – create two one-to-many relationships,…

…but to do this you are missing something –

What?


College mdb3
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 mdb4
College.mdb

Build a Registrations table

In general terms this is sometimes called a linking table


College mdb5
College.mdb

Define the relationships between

Students & Registrations

Courses & Registrations

Register students for courses


Queries
Queries

You’ve built tables,…

You’ve added data…

So, how do you use this data

Queries (at least that’s one answer)


Queries1
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


Queries2
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


Queries3
Queries

Select Queries

Single table query


Queries4
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


Queries5
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


Queries6
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


Queries7
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


Queries8
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


Queries9
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 includedInner 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 tableOuter Join


Queries10
Queries

Select Queries

Complex Queries

OK, so what is a left outer join vs. a right outer join?


ad