Transcript presentation source

Database Management Systems & Programming
LIS 558 - Week 7
Relational Database
Implementation
Faculty of Information & Media Studies
Summer 2000
Lecture Outline

Assignment #1
Quiz
Half-way Summary & Review

Break




Database Implementation &
Demonstration
Events & Windows Interface
Assignment #1
1
Customer
places
(1,1)
(0,N)
Customer (CID, Lname, Fname, Phone, Address,
City, Province, CCardType, CCardNumber...)
Order (Ord#, C_ID, OrdDate, ShipDate…)
Order-Item (CatID, Ord# , NumOrdered, Price, )
ItemCatalogue (CatID, Description, Price,
NumInStock)
1
Catalogue
Items
Order
Items
Order
M
(1,1) (1,N)
(0,N) (1,1)
M
M
1
Assignment #1
1
Customer
M
(1,1)
(0,N)
Order
(1,N)
Customer (CID, Lname, Fname, Phone, Address,
City, Province, CCardType, CCardNumber...)
Order (Ord#, C_ID, OrdDate, ShipDate…)
Order-Item (OrderItemID, Ord#, CatID,
NumOrdered, Price)
ItemCatalogue (CatID, Description, Price,
NumInStock, ...)
(1,1)
Catalogue
1
Order
Item
taken from
(0,N)
(1,1)
M
1
M
Content of LIS558
Theoretical
•
•
•
•
•
•
•
•
•
•
•
•
•
Practical
fundamental concepts
overview of data processing
data requirements
design methodology
data modeling techniques
problems in database
design
normalization
basics of SQL
relational database
implementation
application design and user
interface issues
transaction management
multi-user/client-server
databases
web architecture
Halfway
Point
of Course!
• database basics &
terminology
• database schemas
• constructing tables
• editing and maintaining
databases
• queries and filters
• sorting
• creating forms
• developing reports
• customizing forms and
reports
• user interface
development
Seven Step Conceptual Design Methodology
1 Prepare statement of purpose and define
database objectives
2 Analyze current database (e.g., reports, forms,
etc.), assess user requirements, and create data
model
interview transcripts
 meeting minutes
 observational notes
 document analyses
 business mission and strategy
statements
 questionnaire results
 business forms

training manuals
 flow charts
 consultant reports
 reports
 job descriptions
 computer generated output

3 Create data structures (tables, fields, field
specifications, establish keys)
Seven Step Conceptual Design Methodology
4 Determine table relationships
5 Define and determine business rules critical to
database design (e.g., required fields, validation
rules)
6 Determine and establish user views of data
7 Review data integrity and reiterate design
methodology
E-R Modeling Concepts
Maximum
Cardinality
Entities
1 : 1
1 : N
M : N
Relationship
Type
Minimum
Cardinality
Objects
Mandatory
Optional
Relationships
Recursive
Degree
Binary
Domains
Ternary
Attributes
Values
N-ary
Entity-Relationship Design



Top-down design approach - develop a list
of entities and attributes that are of
interest to the enterprise
E-R Design consists of determining
entities, attributes, relationships,
relationship types, level of participation in
relationships, identifiers, and then drawing
an E-R Diagram
E-R Diagram (model) is a blueprint from
which a well-structured database is
created
Steps to E-R Transformation
1. Identify entity types
2. Identify relationships
3. Determine relationship type
4. Determine level of participation
5. Assign an identifier for each entity
6. Draw completed E-R diagram
7. Deduce a set of preliminary skeleton tables along
with a proposed primary key for each table (using
seven rules)
8. Develop a list of all attributes of interest (not
already listed and systematically assign each to a
table in such a way to achieve a 3NF design (i.e., no
repeating groups, no partial dependencies, and no
transitive dependencies
Database Design Problems

Numerous structural problems can arise
•
•
•
•
•
Redundancy
Multi-valued problems
Update anomalies
Insertion anomalies
Deletion anomalies
Rules for Normalization

Bottom-up design approach or used as a
method to check E-R model
1NF
2NF
3NF

- remove repeating values
- remove partial dependencies
- remove transitive dependencies
For most database designs 3NF is
sufficient
Implementation Outline






Maintaining Data Consistency & Integrity
Understanding Table Joins
Access Query Types
More SQL
Windows & Events
Building the User Interface
Steps to Relational Implementation
1. Define the database structure to the DBMS
2. Allocate Media Space
3. Create/enter the Database Data
Guidelines for Ideal Table Design
1. Each table should represents a single theme or subject
2. Tables should include primary keys that uniquely identify each record of
each table
3. Avoid the use of smart keys that attempt to embed meaning into primary
keys (keys should be meaningless)
4. The domain of primary keys should be large enough to accommodate the
identification of unique rows for the entire potential universe of records
5. A primary key should be a unique, random or sequential collection of
alphabetic, numeric or alphanumeric characters
6. Use the suffix ID in constructing primary keys to ensure they are
readily identifiable.
7. Tables should not contain multipart fields
8. Tables should not contain multivalued fields
9. Tables should not contain calculated or derived fields
10. Tables should not contain unnecessary duplicate fields
11. There should be a minimum amount of redundant data
Database Definition Revisited

Database Definition
• A collection of data, organized logically and
managed by a unifying set of principles,
procedures, and functionalities, which helps
guarantee the consistent application and
interpretation of that data


1st half - considered methodology for
achieving logical organization of data
Now we’ll look at mechanisms for ensuring
data quality
Relational Database Management Systems


Provide mechanisms for minimizing data
entry error and inconsistency
How is data quality, consistency, and
integrity maintained in Microsoft AccessTM:
•
•
•
•
•
•
formats & input masks
validation rules
validation text
status line descriptions
lookup lists and tables
use of referential integrity
• database, table, and field properties

Examine these features by example
Simple Circulation System
PATRON
PatronID, ...
E-R Model
M:N
BORROW
BOOK
Call Number, ...
From CASE # 5
• Binary Relationship
Patron (PatronID, PName, PPhone)
• Many-to-Many type
Book (CallNumber, Title, Author)
• Optional level of
Borrow (PatronID, CallNumber)
participation on both
sides of relationship
• PatronID and CallNumber
are entity identifiers
Access ‘97 Implementation of Model
Database Properties
Database Properties
Database Options
Database Options
Database Startup Options
Database Startup Properties
Implementation of Circulation System Model
Database Object Prefixes
Call Number Field Properties
Title Field Properties
Author Field Properties
Year Field Properties
Table Properties
Table Properties
Selecting Data Type
Formats & Input Masks
Formats & Input Masks
Formats & Input Masks
Formats & Input Masks
Formats & Input Masks
Field Validation
Field Validation
Author Field Properties
Be cautious when using
required property
Lookup Lists & Tables
Lookup Lists & Tables
Lookup Lists & Tables
Lookup Lists & Tables
Referential Integrity
Referential Integrity
Referential Integrity



Referential integrity is a mechanism that enforces the
ties between data in separate tables and prevents them
from being broken
Referential integrity minimizes the undesirable
likelihood of the existence of a record in the child
table for which there is no corresponding record in the
parent table - referred to as an orphan (or dangling)
record
Prior to setting referential integrity, ensure that
• the field used to tie two tables together (the link
field) must be a primary key field in the parent table
and a foreign key in the child table
• the link fields have an identical data type
• the two tables are in the same database container
Referential Integrity in Access ’97



A value cannot be entered in the foreign key field of the related
table if that value doesn't exist in the primary key of the parent
table. A Null value in the foreign key is allowed, specifying that the
records are unrelated.
A record cannot be
deleted from a parent
table if matching
records exist in a
related table.
A primary key value in
the parent table cannot
be changed, if that
record has related
records.
Determined by
MS Access on
the basis of
primary key
settings.
Referential Integrity
Referential Integrity Options in Access ’97
Cascade Update
Special override of the referential integrity mechanism in
order to be able to edit the primary key in the one table;
MS Access will automatically make the same change to the
foreign key in the child table so the relationship is
maintained.
Cascade Delete
Special override of the referential integrity mechanism to
facilitate deleting records in the parent table even when
there are related records in the child table. All related
records in the child table will automatically be deleted so
that there will be no orphan records.
Do not use these options unless you realize the full implications of
making the selection.
Relationship Integrity
is a way of minimizing data errors
MS Access 97 On-line Help
Table Joins
Table Joins
Table Joins
Class Outline






Maintaining Data Consistency & Integrity
Understanding Table Joins
Access Query Types
More SQL
Windows & Events
Building the User Interface
Three Access Query Types

General Query Type
• Select Queries
– most frequently used type
– can select records, create new calculated (derived)
fields, or summarize data
• Action Queries
–Make table
- Creates a new table from a dynaset
- Create backup copies of tables
- Save history table of old records being deleted from
another table
- Create table for export
Microsoft Access Query Types
–Update
•Make global changes to data in one or more tables
•Add, change, or delete specified value
–Append
•Add records from one or more tables to the end of
an existing table
•Useful for appending old records to the end of
history or archive table
–Delete
•Deletes a group of records from one or more tables
on the basis of some specified (or non-existent) values
in one or more fields
Microsoft Access Query Types
• Special Queries
–Cross Tab
•Groups data from one or more tables into categories
and displays results in spreadsheet fashion
•Used for data comparisons
•Summarize data
•View data trends (change in one field changes values
in other fields)
•Particularly useful as data source for reports and
charts
Microsoft Access Query Types
–Pass-Through
•Used to send commands directly to an SQL database
server
•Advanced programming feature used to embed
queries in database applications
–Data Definition
•SQL language statements for creating or changing
database object
•Advanced programming feature
–Union Queries
•SQL language statements used to combine fields
from two or more tables or queries
•Advanced programming feature
Example Query in Access
Example Query in Access
Example Query in Access
Example Query in Access
Access SQL


Behind every Query-By-Example
(QBE) query there is a corresponding
SQL query
Constructing most queries is
considerably easier using the QBE
design sheet
SQL Examples
Example Table Structure
Example Table Structure
SQL Examples
SELECT Orders.[Customer ID],
Orders.[Part No], Orders.Quantity,
Orders.[Unit Price]
FROM Orders;
SELECT name, city
FROM customers;
SQL Examples
SELECT *
FROM customers;
SELECT Name, City
FROM customers
WHERE state = "TX";
SQL Examples
SELECT city & ", " &
state & " " & zip
FROM customers;
SELECT name AS [Customer Name],
city & ", " & state & " " & zip AS
Location
FROM customers;
SQL Examples
SELECT name, street, city
FROM customers
ORDER BY city;
SELECT name, street, city
FROM customers
WHERE state="CA"
ORDER BY city;
SQL Examples
SELECT name, city, zip
FROM customers
WHERE state="CA"
ORDER BY zip DESC;
SELECT name, city, state
FROM customers
ORDER BY state, city;
SQL Examples
SELECT name, contact
FROM customers
WHERE contact IS NULL;
SELECT name, contact
FROM customers
WHERE contact IS NOT NULL;
SQL Examples
SELECT DISTINCT city
FROM customers
WHERE city IS NOT
NULL;
SELECT [Part No],
Sum(quantity*[Unit Price]) AS
Total
FROM Orders
GROUP BY [Part No];
SQL Examples
SELECT name, city
FROM customers
WHERE city =
(SELECT city
FROM customers
WHERE contact = "Gina Mullin");
SELECT [Part No], Sum(Quantity*[Unit
Price]) AS Total
FROM orders
WHERE [Customer ID]=(SELECT id
FROM customers
WHERE contact = "Gina Mullin")
GROUP BY [Part No];
SQL & Programming
Private Sub City_List_AfterUpdate()
Dim SQLStmt, R As Recordset
SQLStmt = "SELECT DISTINCT state FROM businesses " _
& "WHERE state IS NOT NULL AND City = '" & Me![City List] & " ';"
Me![State List].RowSource = SQLStmt
Me![State List].Requery
Set R = CurrentDb.OpenRecordset(SQLStmt)
If R.RecordCount > 0 Then
R.MoveLast
If R.RecordCount = 1 Then
Me![State List] = R![State]
End If
End If
SQLStmt = "SELECT DISTINCT zip FROM businesses” _
& “WHERE city = '" & Me![City List]
& "' AND state = '”_
& Me![State List] & "’ AND zip IS NOT NULL;"
Me![Zip List].RowSource = SQLStmt
Me![Zip List].Requery
Set R = CurrentDb.OpenRecordset(SQLStmt)
If R.RecordCount > 0 Then
R.MoveLast
If R.RecordCount = 1 Then
Me![Zip List] = R![Zip]
End If
End If
End Sub
Class Outline






Maintaining Data Consistency & Integrity
Understanding Table Joins
Access Query Types
More SQL
Windows & Events
Building the User Interface
Assignment #4 Form
Construction
1
Customer
1
places
Category
Has-A
M
1
Book
Order
Items
Order
M
M
1
M
Assignment #4 Form
Construction





Create main form and subforms
Create tab control on main form
Use subform wizard to place subforms on
each of the tabs and automatically link
subforms to main form
See Adamski lab text section 5.3
See Novalis Chapter 4
Components of Windows Interface









forms
reports
queries
help
mouse
cursors
icons
images
text








toolbars
command buttons
accelerator keys
shortcut keys
scroll bars
menus
context-sensitive
help
general help
Anatomy of Event-Driven Applications

Windows is event-driven
• Messages passed upward/downward between
operating system (OS) and application (and
amongst modules)
• OS controls and routes messages and handles
all low-level hardware/software complexities
• Application built on Windows uses builtin
standardized messages and events
• Each object has set of properties and events
• Many properties readable and changeable both
at design time and run time
• Events can be trapped at run time and
associated code/macros is run
Show Events Form - Orders.mdb
Show Events Form - Orders.mdb
Show Events Form - Orders.mdb
Combo Box Design Properties
Combo Box Format Properties
Combo Box Data Properties
Combo Box Other Properties
Combo Box Event Properties
Code Associated With Event
Private Sub BillTo_AfterUpdate()
' Enable customer controls and
' update ShipTo controls
' with new customer information.
CustomerID.Enabled = True
CustomerID.Locked = True
CompanyName.Enabled = True
Address.Enabled = True
City.Enabled = True
Region.Enabled = True
PostalCode.Enabled = True
Country.Enabled = True
ContactName.Enabled = True
ContactTitle.Enabled = True
Phone.Enabled = True
Fax.Enabled = True
ShipName = CompanyName
ShipAddress = Address
ShipCity = City
ShipRegion = Region
ShipPostalCode = PostalCode
ShipCountry = Country
' Enable the subform for
data input.
Me!OrdersSubform.Enabled
= True
Me!OrdersSubform.Locked =
False
End Sub
Components in Detail

See O’Brien (Microsoft Access
Developer’s Handbook - Chapter 5 User Interface and Application Design)
for a detailed description of
components, associated properties, and
usage.
Practical User Interface Design

Design of user interface a critical
success factor
• consistency
• user-centred
• compliant with established platform
standards
Building Applications

Macros provide simple mechanism for
building applications
• summary sheets available next week
• Novalis’ Access 97 Macro & VBA Handbook
Chapter’s 5-9

Switchboard Manager
Next Week



Application & User Interface Design
Read O’Brien Chapter 5-6 for next
week
Don’t forget to start thinking about
your project proposal