1 / 27

Day 15: Access Chapter 2

Day 15: Access Chapter 2. Larry Reaves larry.reaves@mail.wvu.edu October 7, 2013. Last class. Access Overview Tables Queries Forms Reports Relationships. Tables. A table is a storage location in a database that holds related information

cerise
Download Presentation

Day 15: Access Chapter 2

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. Day 15:Access Chapter 2 Larry Reaveslarry.reaves@mail.wvu.edu October 7, 2013

  2. Last class • Access Overview • Tables • Queries • Forms • Reports • Relationships

  3. Tables • A table is a storage location in a database that holds related information • A table consists of records, each record is make up of fields • When designing a database, the first step is to identify the tables you need

  4. Choosing fields • Once you have identified the tables you need, you must add the necessary fields to each table using these guidelines: • Include the necessary data • Design for now and the future • Store data in its smallest parts • Add calculated fields to a table • Design to accommodate date arithmetic • Link tables using common fields

  5. Include the necessary data • Ask what information will be expected from the system and determine the data required to produce that information • If the information can be calculated from the data it should not be included as a separate data field • If it will be commonly used, you can add a calculated field

  6. Design for now and the future • Consider the future needs and build in the flexibility to satisfy those demands • Especially note the size of data types • A tinyint might be okay for an ID field for now, but it is limited to the values 0-255. After 256 records, you will not be able to create more records

  7. Store data in its smallest parts • By dividing data up as much as possible we create more flexibility • Example: names • You could store names as one field like “Larry Reaves” • However, if you store names in two separate fields: first_name and last_name it is easier to create reports using different formats for the names • “Larry Reaves” • “Reaves, Larry” • “Mr. Larry Reaves” • “Mr. Reaves”

  8. Add calculated fields • A calculated field produces a value from an expression or function that references one or more existing fields • Calculated fields are not available in Access versions earlier than 2010, and will be used on homework assignments • As always, Access 2010 is available in Open Lab or on the library computers

  9. Design to accommodate date arithmetic • When dealing with periods of time it is better to store dates than numeric values • Example: age • If you store a person’s age in the database, it must be updated every time they have a birthday • If instead you store the birthday, you can use date arithmetic to calculate their age

  10. Link tables using common fields • As you create tables and fields, keep in mind that the tables will be joined in relationships using common fields • Common fields must have the same data type and usually the same name, although different names are allowed • Avoid data redundancy • Data redundancy is storing duplicate data in two or more tables

  11. Creating tables • Once your design is complete, you can begin creating your tables in Access • Table details can be specified in Datasheet view, Design view, or imported from another database or from Excel • No matter how it was created, a table can always be modified later to add a new field or change an existing field

  12. Table and field names • Fields names should be descriptive and can include letters, numbers, and spaces • However, it is best to avoid spaces since they can cause problems when creating queries, forms, and reports • For this course we will almost always be using CamelCase for table and field names • The first letter of each word is capitalized and the spaces are removed • ThisIsALongExampleOfCamelCase

  13. Establishing a primary key • The primary key is the field or combination of fields that uniquely identifies each record in a table • Primary keys should be selected to use unique and infrequently changing data • Examples: • AccountNumber for an Accounts table • ISBN for a Books table • StudentId for a Students Table

  14. Primary Keys continued • When there is no natural primary key, you can create a primary key fields with the AutoNumber data type • This type will automatically increment each time a record is added

  15. Foreign Keys • A foreign key is a field in one table that is the primary key of a different table • This key relates the records in the foreign key table to the records in the table with the matching primary key

  16. Field properties • Fields have properties that determine how the field looks and behaves • Data type: specifies what type of data is expected for that field • Caption: allows you to set a readable label that is used in datasheet view, forms, and reports • Validation Rule: ensures the data entered is formatted properly

  17. More Field Properties • Default Value: this value is automatically used for new records when a data value for the field is not otherwise specified • Required: indicates that a value must be entered for the field • Indexed: when set to yes an index is maintained that allows faster lookup and sorting by that field

  18. Referential Integrity • When creating relationships, you have the option to “Enforce Referential Integrity” • When this option is checked, you cannot enter a foreign key in the related table unless the primary key exists in the primary table • You also can not delete a record from the primary table if it has related records (unless cascade delete is also enabled)

  19. Cascading • Cascade Updates: • When the primary key is changed, the foreign key in the related table is automatically updated • Cascade Delete • When the record with a specific primary key is deleted, all related records are also deleted • Use with caution

  20. Sharing Data with excel • External Data->Import & Link->Excel • Select file • Select worksheet • Specify if the first row contains column headings • Adjust properties for each field • Skip fields you don’t want to import • Choose a primary key • Name the table • External Data->Export->Excel

  21. Relationship types • One-to-Many • This is a relationship between the primary key in the first table and a foreign key in the second table. The second table can have many records with the same foreign key. • One-to-One • Two different tables use the same primary key • Many-to-Many • This type of relationship requires an additional table with two foreign keys per record: one for each primary key in the two related tables

  22. One-to-many • All the examples so far have been one-to-many • One-to-many is the most common relationship type

  23. One-to-one • Two tables have the same primary key • Two uses for one-to-one relationships: • Extending a table where the existing design must be preserved for legacy reasons (for example, custom software that relies on the existing design) • Security: Sometimes you must split the record into two tables: one that any user can view, and one that has sensitive information whose access is restricted

  24. Many-to-many • An additional table is used to link the two primary tables • This third table contains two foreign keys, one matching each primary key in the two related tables • Example: • Employees and Projects • Each Project is assigned to multiple Employees and each Employee has multiple Projects

  25. Establishing relationships • Table Tools->Table->Relationships or • Database Tools->Relationships • Drag the primary key to the foreign key (one-to-many) • Drag the primary key to the primary key (one-to-one) • Drag each primary key to each foreign key (many-to-many)

  26. Next Class • Queries • Query Wizard • Design View • Specifying query criteria (filtering results) • Sorting results • Copying and running queries

More Related