introduction to ms access, building tables, creating keys, creating relationships
DESCRIPTION
Introduction to MS Access, building tables, creating keys, creating relationships. BSAD 141 Dave Novak. Topics Covered. Provide a brief introduction to MS Access Name and save a DB file Create tables and keys Create and enforce relationships Convert a partial ERD to design in Access - PowerPoint PPT PresentationTRANSCRIPT
Introduction to MS Access, building tables, creating keys, creating relationships
BSAD 141Dave Novak
Topics Covered
Provide a brief introduction to MS AccessName and save a DB fileCreate tables and keysCreate and enforce relationshipsConvert a partial ERD to design in AccessProvide examples of both entity integrity and
referential integrity using Access
Important to note!
If you are having trouble keeping up with the lecture or having some type of technical problems, you may need to read and work through the Tech Plug Ins that are shown in the course schedule
These will walk you through the basic features in MS Access and guide you through table and key creation as well as how to create relationships between tables
Example: Partial ERD Consider a relationship between a manager
and a projectAssume the following:
• Each manager can manage up to 5 different projects simultaneously
• Each manager must manage at least one project• Each project is assigned to only 1 manager (there
are no co-manager leads on the project)• When entered into the DBMS, each project must be
assigned to a manager
Example: Partial ERD Draw the partial ERD – discuss degree,
connectivity, and existence
Two Types of RDBMS Integrity Rules 1) Entity Integrity
2) Referential Integrity
Building Tables in Access
Create a DB file called “class-example1” Build 2 separate tables that correspond to
the partial ERD you just drewAttributes for Manager (ManagerID,
FirstName, LastName, Address, Salary)Attributes for Project (ProjectID,
ProjectName, Duration, Phase) Model the relationship in Access and
enforce referential integrity
Building Tables in Access
1) Create the “shell” or empty tables to begin with
2) Specify the data type for each attributeManager (ManagerID (txt), FirstName (txt),
LastName (txt), Address (txt), Salary (currency))
Project (ProjectID (txt), ProjectName (txt), Duration (txt), Phase(txt))
Link the tables using a Foreign Key
Using our ERD and what you know about the location of Foreign Keys FK: Which table does the FK appear in?What is the FK attribute?Go into design view and add the FK attribute
Enter Data in Manager Table: Entity Integrity Error
You get an error message if you violate entity integrity rules
Manager
ManagerID FirstName LastName Address Salary
765-98-2222 Dave Smith 2 Main St. $75,000
123-45-6789 Sue Jones 1 East St. $80,000
Mary Wells 1 North St. $78,000
Complete Manager Table
Just use 5 attributes and enter 3 records I give you
Manager
ManagerID FirstName LastName Address Salary
765-98-2222 Dave Smith 2 Main St. $75,000
123-45-6789 Sue Jones 1 East St. $80,000
123-76-8823 Mary Wells 1 North St. $78,000
Enter Data in Project Table
Just use the 4 attributes and enter the 4 records I give you!
Project
ProjectID ProjectName Duration Phase
PRJ-145 Health Care Database 14 months 2
PRJ-147Traveler's Insurance Payroll
System 26 months 1
PRJ-151 Capital Finance Highway 41 7 months 1
PRJ-187Economic Analysis UVM
Expansion 9 months 3
Populate the FK attribute: Referential Integrity Error
Project
ProjectID ProjectName Duration Phase ManagerID
PRJ-145 Health Care Database 14 months 2 756-98-2222
PRJ-147Traveler's Insurance Payroll
System 26 months 1 756-98-2222
PRJ-151 Capital Finance Highway 41 7 months 1 123-76-8823
PRJ-187Economic Analysis UVM
Expansion 9 months 3 654-98-0033
We will end up with a referential integrity violation
Referential Integrity
Manager ID?
One of the FK attribute values in the Project Table (the M side of the relationship) does not exist in the parent table (the Manager Table – the one side of the relationship). There is NO manager ID 654-98-0033, so that ID value cannot be assigned to a project
Manager
ManagerID FirstName LastName Address Salary
765-98-2222 Dave Smith 2 Main St. $75,000
123-45-6789 Sue Jones 1 East St. $80,000
123-76-8823 Mary Wells 1 North St. $78,000
Referential Integrity and Tables The specific value you enter for any FK
attribute value must FIRST exist in the parent table (the table that relates directly to the particular attribute – in our case where the FK is a PK)Example: I cannot be assigned to teach a
section of 141 (or any other class) if I am not first listed in the professor / teacher table
You have a value for ManagerID that exists in the Manager table
Design Rules
Normalization
Design Rules
NormalizationTypically a trade-off between higher levels of
normalization and processing speedAs redundancy is reduced, the time it takes
the DBMS to process a query or extract data could increase• Higher levels of normalization may not always be
desired where processing speed is critical
Design Rules
We want to convert this single table DB file to a relational DBMS format
Proj# ProjName ProjStartDate Emp# EmpName EmpAddress JobClass HrBillRateHrs
WorkedProj
Manager15 UVM DB Design 10/15/2009 12 John Jones 14 Green St. Elect. Eng. 85 3015 UVM DB Design 10/15/2009 34 Sally Smith 14 Green St. DB Designer 100 20 Y15 UVM DB Design 10/15/2009 22 Homer Simpson 34 Blue Ave. Sys Analyst 50 4021 Health Care Evaluation 7/1/2009 12 J. Jones 14 Green St. Elect. Eng. 85 15 Y21 Health Care Evaluation 7/1/2009 35 Jill Wilson 876 Lake Rd. Programmer 60 4521 Health Care Evaluation 7/1/2009 34 Sally Smith 14 Green St. DSS Analyst 70 4021 Health Care Evaluation 7/1/2009 22 Homer Simpson 34 Blue Ave. DB Designer 90 1530 Roadway B/C 9/15/2010 34 Sally Smith 14 Green St. Economist 75 50 Y30 Roadway B/C 9/15/2010 35 Jill Wilson 876 Lake Rd. Programmer 60 75
Design Rules
There are clearly some redundancy issues with the data
How do we address redundancy via normalization and organize the data into relational tables?
Step 1
The current format is just an Excel file, not a relational DBMS
Identify attributes of each entity (or fields to be placed in each table)
Step 2 Create tables to capture data we need
We want attributes and data related to a specific entity in one table and attributes and data related to another entity in a different table
Design tables to minimize need to enter redundant information
What do we do with attributes that pertain to both employee and project?
Step 3
Consider relationship connectivity (1:M, M:M) between the entities – may be a trial and error processWhat type of relationship is needed here?
• Can employees work on more than 1 project at a time, or only 1 project at a time?
• Is each project staffed by only 1 employee, or can each project be staffed my multiple employees?
We can tell by looking at the data in the Excel table
Step 4
Draw the partial ERD