database design and maintenance. review proper database design ensures that the data is represented...
Post on 19-Dec-2015
218 views
TRANSCRIPT
Database Database Design and Design and MaintenanMaintenancece
ReviewReview• Proper database design ensures that the data is
represented properly, tables are joined correctly, and that data can be easily and accurately retrieved.
Relational Database TermsRelational Database Terms
• Relation = Table• Tuple = Row• Attribute = Field
Relational Database Relational Database CharacteristicsCharacteristics
• No two tuples can be exactly the same.• The order of tuples has no significance.• Each attribute must describe the relation, and have a unique
name.• Each attribute can have only one value in a tuple.• An attribute must have the same set of possible values
(domain) in all tuples.
Building a Building a Relational DatabaseRelational Database
• Designing tables• Creating tables• Joining tables• Designing and creating
other objects
Recognizing Table TypesRecognizing Table Types• Master tables – contain data about people and things.• Lookup tables – contain data about groups or categories of
information.• Bridge or Transaction tables – contain data about transactions
and events. Often used to simplify many-to-many joins.
Identifying a Primary Key Identifying a Primary Key • One or more fields that uniquely identify each
record• Primary key field must not be blank in any record.
Data DependencyData Dependency• Functional dependency - when any attribute determines the
value of another attribute.• Transitive dependency – when a non-key attribute determines
the value of another attribute.• Partial dependency – when only one field in a multiple-field
primary key determines the value of another attribute.
Well-Structured RelationsWell-Structured Relations
What constitutes a well-structured relation? Intuitively, a well-structured relation contains minimal redundancy and allows users to insert, modify, and delete rows in a table without errors or inconsistencies.
EmpID Name Dept Salary
230 Pillsbury Marketing 58,000
241 Marshall Finance 68,400
277 Marco Accounting 66,000
279 Gunston Marketing 42,400
290 Jaffe Planning 49,000
EMPLOYEE1 Table
Well-Structured RelationsWell-Structured Relations
EMPLOYEE1 is a well-structured relation. Each row of the table contains data describing one employee, and any modification of an employee’s data (such as a change in salary) is confined to one row in the table.
EmpID Name Dept Salary
230 Pillsbury Marketing 58,000
241 Marshall Finance 68,400
277 Marco Accounting 66,000
279 Gunston Marketing 42,400
290 Jaffe Planning 49,000
EMPLOYEE1 Table
Well-Structured RelationsWell-Structured RelationsIn contrast, EMPLOYEE2 is not a well-structured relation. Notice the redundancy. For example, values for EmpID, Name, Dept, and Salary appear in two separate rows for employees 241 and 290.
EmpID Name Dept Salary Course Date
230 Pillsbury Marketing 58,000 C++ 2/12/06
241 Marshall Finance 68,400 SPSS 5/30/07
241 Marshall Finance 68,400 Web Design 11/2/08
277 Marco Accounting 66,000 C# 12/8/07
279 Gunston Marketing 42,400 Java 9/10/06
290 Jaffe Planning 49,000 Tax Acct 4/22/06
290 Jaffe Planning 49,000 Bus Adm 6/6/08
EMPLOYEE2 Table
Data AnomaliesData Anomalies
There are three types of data anomalies:• Insertion anomaly – Suppose we need to add a new employee to
EMPLOYEE2. Since the primary key is (EmpID, Course), to insert a row both EmpID and Course must be supplied. This is an anomaly, because the user should be able to enter employee data without supplying Course data.
• Deletion anomaly – Suppose that the data for employee 241 are deleted. This will result in losing information that this employee completed a course (SPSS) on 5/30/07.
• Modification anomaly – Suppose that employee 290 gets a salary increase. We must record the increase in each of the rows for that employee; otherwise the data will be inconsistent.
Redundancies in a table may result in errors or inconsistencies (called anomalies) when a user attempts to update the data in the table.
The problem with relation EMPLOYEE2 is that it contains data about two entities: EMPLOYEE and COURSE. We will use normalization techniques to split EMPLOYEE2 into two relations, one for employee data and one for course data.
Normalizing TablesNormalizing TablesOn the previous four slides we presented an intuitive discussion of well-
structured relations. We need a more formal procedure for designing them. Normalization is the process of successively reducing relations with anomalies to produce smaller, well-structured relations. Some of the goals are:
Minimize data redundancy, thereby avoiding anomalies and conserving storage space.
Simplify the enforcement of referential integrity constraints. Make it easier to maintain data (insert, delete, update). Provide a better design that is an improved representation of the real
world and a stronger basis for future growth.
First Normal FormFirst Normal Form• All fields describe the entity represented by the
table.• All fields contain simplest possible values.• No multivalued attributes (also called repeating
groups).
Home Town
Chicago, IL
NOT
City State
Chicago IL
1st NORMAL
Not 1NFNot 1NF
EmpID Dept CourseNameDateCompleted
203 Finance Tax Accounting 6/22/07
421 Info Systems JavaDatabase Mgt
10/7/076/4/06
666 Marketing
Another multivalued
attribute
A multivalued
attribute
1NF - Eliminating multivalued 1NF - Eliminating multivalued attributesattributes
EmpID Dept CourseNameDateCompleted
203 Finance Tax Accounting 6/22/07
421 Info Systems Java 10/7/07
421 Info Systems Database Mgt 6/4/06
666 Marketing
This new table does have only single-valued attributes and so satisfies 1NF. However, as we saw, the table still has some undesirable properties.
Second Normal FormSecond Normal Form• Table is in First Normal Form.• No partial dependencies exist. (No nonkey fields
are determined by only part of a multiple-field primary key, i.e., nonkeys are identified by the whole primary key)
*Primary key
NOT 2nd NORMAL
Course#* Grade
CIS 101 B
Student ID*
12345
Course#* Name
CIS 101 Higgins
Student ID*
12345
DeterminesDetermines
Third Normal FormThird Normal Form• Table is in Second Normal Form.• No transitive dependencies (no nonkey fields are
determined by other nonkey fields, i.e., nonkeys are identified by only the primary key).
Course# * Textbook
CIS 101 Intro to CIS
Credits
3
NOT3rd NORMAL
*Primary key
Course# * Textbook
CIS 101 Intro to CIS
Book Price
$45.99
Determines Determines
Fourth and Fifth Normal FormFourth and Fifth Normal Form• Fourth Normal Form – Table is 3NF and has at most
one multivalued dependency. Can produce records with many blank values.
• Fifth Normal Form – the table cannot be split into further tables.
Advanced Field PropertiesAdvanced Field Properties
• Lookup fields• Multiple-field primary
keys• Indexes
Lookup FieldLookup Field• Looks up a value in a joined table.• Specify “Lookup wizard” in Data Type list.• Creates an editable query.
Using a Lookup FieldUsing a Lookup Field
Order Customer ID1008 S349
Customer ID Name S349 Smith,Ben
Orders CustomersLooks up Namein Customer table
Looks up data values from another table (or you can create your own list).
S349Smith,Ben
Multiple-field Primary KeysMultiple-field Primary Keys• Also called compound keys or composite keys• A value in one field in the key can be repeated in
multiple records, but not in all fields of the primary key.
Course# * Grade
CIS 101 A
Student ID*
12345
*Primary key
CIS 200 B12345
IndexIndex• Field property that increases
search speed.• Speeds up sorting and searching
in Datasheet view and all database objects.
Joining TablesJoining Tables• One-to-Many join is the most common.• Other join types:
– One-to-One
– Many-to-Many
Table 2
Table 1
One to Many
Rec. 1
Rec. 1 Rec. 2
One to One
Rec. 1
Rec. 1
Many to Many
Rec. 1
Rec. 1 Rec. 2
Rec. 2
Join TypesJoin Types• Inner Join • Left Outer Join • Right Outer Join
Join types are discussed in the slides to follow, but also see the discussion at Join-queries.htm.
The default type - includes records with corresponding values in both tables.
LASTNAME FIRSTNAME DEPARTMENTGray Eric CISGray Nadine HRSCedarman Yvonne HRSMalderer Kevin HRSNale Rusty
DEPARTMENT CODE NAMECIS Computer Information SystemsHRS Human ResourcesWHS Warehouse
Only red records are
included in join.
No department for Nale
No Warehouse employees
Inner JoinInner Join
1
CISHRSHRSHRS
WHS
Includes all records from One table and corresponding records from Many table
Left Outer JoinLeft Outer Join
LASTNAME FIRSTNAME DEPARTMENTGray Eric CISGray Nadine HRSCedarman Yvonne HRSMalderer Kevin HRSNale Rusty
DEPARTMENT CODE NAMECIS Computer Information SystemsHRS Human ResourcesWHS Warehouse
Rusty Nale not included – no department assigned.
1
How to Change the Join Properties in Query Design ViewHow to Change the Join Properties in Query Design View
Double-click on the join line that connects the tables.
Click the bullet next to the join property desired.
For example, in the 240students.mdb database, suppose we want all students and the pets they own. All students should be listed regardless of whether they own a pet or not.
This is perfect for a Left Outer Join, because it will select a student from tblStudents (the “One” table) even if there isn’t a match (between ID and OwnerID) in tblPets (the “Many” table).
Here’s the SQL and the result of running the queryHere’s the SQL and the result of running the query
• The result of running this query (in the 240students.mdb database) Left-Join.htm.
SELECT ID, FirstName, Name, Breed
FROM tblStudents LEFT JOIN tblPets
ON tblStudents.ID = tblPets.OwnerID
LASTNAME FIRSTNAME DEPARTMENTGray Eric CISGray Nadine HRSCedarman Yvonne HRSMalderer Kevin HRSNale Rusty
DEPARTMENT CODE NAMECIS Computer Information SystemsHRS Human ResourcesWHS Warehouse
Warehouse dept. not
included – no employees assigned.
Includes all records from Many table and corresponding records from One table.
Right Outer JoinRight Outer Join
1
Join TypesJoin TypesJoins displayed as Venn Diagrams
AB
Inner JoinA B
U
AB
Left Outer JoinA is “one” table
A and B are tablesGreen striped area is join dynaset.
AB
Right Outer JoinB is “many” table
Join TypesJoin TypesSelected by double clicking on join line between two tables in Relationship window, and clicking Join Type button.
Referential IntegrityReferential Integrity• Referential integrity keeps the relationships between
tables valid.• All foreign keys have values that correspond to
records in the referenced table• Maintain referential integrity by:
– Updating and deleting records when matching records in a joined table are updated and deleted.
– Eliminating unmatched and duplicated records in joined tables.
Enforcing Referential Enforcing Referential IntegrityIntegrity
• Normalize tables• Set field properties• Use lookup fields• Select specific join type settings• Create and run Find Duplicate and Find
Unmatched queries
Find Duplicate Records QueryFind Duplicate Records Query• Locates records with duplicate values in Many
table.
One table Many table
Duplicates
Find Unmatched Find Unmatched Records QueryRecords Query
Locates records in Many table not associated with record in One table
One table Many table
No match
Maintaining DatabasesMaintaining Databases• Older versions of Access can be converted to newer versions, and
vice versa. • Databases can be compacted and repaired using the Tools,
Database Utilities command.• Databases can be split into two databases: data and objects (back
and front end).• Databases can be documented using the Tools, Analyze,
Documenter command.• Database performance can be analyzed using the Tools, Analyze,
Performance command.
Object GroupsObject Groups• Named set of shortcuts that point to database
objects• Grouped objects are listed together in a single
window.
Modifying Access Modifying Access EnvironmentEnvironment
• Tools, Options command allows changes to the behavior of Access.
• Access standard toolbars can be modified.