1 / 23

Introduction to MS Access, building tables, creating keys, creating relationships

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

clovis
Télécharger la présentation

Introduction to MS Access, building tables, creating keys, creating relationships

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. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. Introduction to MS Access, building tables, creating keys, creating relationships BSAD 141 Dave Novak

  2. 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 • Provide examples of both entity integrity and referential integrity using Access

  3. 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

  4. Example: Partial ERD • Consider a relationship between a manager and a project • Assume 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

  5. Example: Partial ERD • Draw the partial ERD – discuss degree, connectivity, and existence

  6. Two Types of RDBMS Integrity Rules • 1) Entity Integrity • 2) Referential Integrity

  7. Building Tables in Access • Create a DB file called “class-example1” • Build 2 separate tables that correspond to the partial ERD you just drew • Attributes for Manager (ManagerID, FirstName, LastName, Address, Salary) • Attributes for Project (ProjectID, ProjectName, Duration, Phase) • Model the relationship in Access and enforce referential integrity

  8. Building Tables in Access • 1) Create the “shell” or empty tables to begin with • 2) Specify the data type for each attribute • Manager (ManagerID (txt), FirstName (txt), LastName (txt), Address (txt), Salary (currency)) • Project (ProjectID (txt), ProjectName (txt), Duration (txt), Phase(txt))

  9. 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

  10. Enter Data in Manager Table: Entity Integrity Error You get an error message if you violate entity integrity rules

  11. Complete Manager Table Just use 5 attributes and enter 3 records I give you

  12. Enter Data in Project Table Just use the 4 attributes and enter the 4 records I give you!

  13. Populate the FK attribute: Referential Integrity Error We will end up with a referential integrity violation

  14. Referential Integrity 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 ID?

  15. 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

  16. Design Rules • Normalization

  17. Design Rules • Normalization • Typically a trade-off between higher levels of normalization and processing speed • As 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

  18. Design Rules We want to convert this single table DB file to a relational DBMS format

  19. 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?

  20. 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)

  21. 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?

  22. Step 3 • Consider relationship connectivity (1:M, M:M) between the entities – may be a trial and error process • What 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

  23. Step 4 • Draw the partial ERD

More Related