DAY 15 ACCESS CHAPTER 2 Tazin Afrin Tazin

DAY 15: ACCESS CHAPTER 2 Tazin Afrin Tazin. Afrin@mail. wvu. edu October 08, 2013 1

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 ? 2

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

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

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 arithmetic – Link tables using common fields 5

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 6

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

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 8

STORE DATA IN ITS SMALLEST PARTS • By dividing data up as much as possible we create more flexibility. • Store names 2 ways : 1. Single field • Tazin Afrin 2. Divide name to firstname and lastname • • Tazin in fiestname column Afrin in lastname column 9

STORE DATA IN ITS SMALLEST PARTS • 2 nd way is more flexible – Create a salutation letter – Mrs. Afrin • Ordering – Sort by lastname or firstname • Adress – split road, city, county, zip code. S 10

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 11

DESIGN TO ACCOMMODATE 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 12

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 13

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

FIELD NAME • Should be descriptive of the data • Can be up to 64 characters in length, including letters, numbers, and spaces. • Camel. Case notation – uses no spaces in multi-word field names, but uses uppercase letters to distinguish the first letter of each new word. – Example : • Product. Cost not Productcost or productcost • Last. Name. 15

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 Auto. Number data type. • The Auto. Number data type is a number that automatically increments each time a record is added. 16

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

FOREIGN KEY • A foreign key is a column or combination of columns that is used to establish and enforce a link between the data in two tables. • It controls the data that can be stored in the another 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 is enforced, 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

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 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 • • • Single table query Copy and run a query Use query wizard Create multi table query Modify multi table query 27

THANK YOU LOG OFF