Day 15: Access Chapter 2

1. Day 15:Access Chapter 2 Tazin AfrinTazin.Afrin@mail.wvu.edu February 27, 2014

2. Designing data • Let you have a bank account • What bank knows about you? • Name • Address • phone number • Social Security number • What accounts you have (checking, savings, money market) • If you have a credit card with that bank, and what its balance is. • How the data are stored ?

3. Tables • A table is a storage location in a database that holds related information. • A table consists of records, and each record is made up of a number of fields.

4. Tables • Good database design begins with the tables. • Tables provide the framework for all of the activities you perform in a database. • If the framework is poorly designed, the rest of the database will be poorly designed as well.

5. Design process • 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

6. Include the necessary data • Determine what data is necessary in the tables. • Ask yourself what information will be expected from the database • Then 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

7. Design for now and the future • Data requirements of an organization evolve over time, the information systems that hold the data must change as well. • Good database design must balance the data collection needs of the company with the cost associated with collection and storage.

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

9. Store data in its smallest parts • By dividing data up as much as possible we create more flexibility. • Store names 2 ways : • Single field • Tazin Afrin • Divide name to firstname and lastname • Tazin in fiestname column • Afrin in lastname column

10. Store data in its smallest parts • 2nd way is more flexible • Create a salutation letter • Mrs. Afrin • Ordering • Sort by lastname or firstname • Adress – split road, city, county, zip code.S

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

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

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

14. Creating Tables • Create a table by creating the fields in Design view or by entering table data into a new row in Datasheet view • You can import data from another database or application such as Excel. • Regardless of how a table is first created, you can always modify it later to include a new field or to change existing fields.

15. Field name • Should be descriptive of the data • Can be up to 64 characters in length, including letters, numbers, and spaces. • CamelCase notation • uses no spaces in multi-word field names, but uses uppercase letters to distinguish the first letter of each new word. • Example : • ProductCost not Productcost or productcost • LastName.

16. Establish a primary key • Primary key is the field (or combination of fields) that uniquely identifies each record in a table. • Select unique and infrequently changing data for the primary key. • you can create a primary key field with the AutoNumber data type. • The AutoNumber data type is a number that automatically increments each time a record is added.

17. primary key example • Bad primary key : • a complete address (street, city, state, and postal code) may be unique but would not make a good primary key because it is subject to change when someone moves. • Cannot use a person’s name as the primary key, because several people can have the same name. • Good primary key : • A customer’s account number, is unique and is a frequent choice for the primary key, in the Customers table.

18. Field properties • A field property is a characteristic of a field that determines how the field looks and behave • data type : select if text or numerical characters. • caption property : used to create a more readable label that appears in the top row in Datasheet view and in forms and reports.

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

20. Understanding Table Relationships • Relationships between tables are set in the Relationships window. • In this window, join lines are created to establish relationships between two tables. • The benefit of a relationship is to efficiently combine data from related tables for the purpose of creating queries, forms, and reports.

21. Establish referential integrity • When referential integrity isenforced, you cannot enter a foreign key value in a related table unless the primary key value exists in the primary table. • When creating relationships, you have the option to “Enforce Referential Integrity” • Check this option to establish referential integrity.

22. Set cascade options • When you create a relationship Access gives you two additional options – • 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

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

24. Next class • Single table query • Copy and run a query • Use query wizard • Create multi table query • Modify multi table query

25. Thank You Log Off