1Tutorial 2Building a Database and Defining Relationships20pening a Database Two ways to open an existing database File | Open File | Open | Recent Documents list Access first saves any open database can have only 1 accdb open using the Access User Interface can have >1 accdb open using VBA code When a database file is opened, Access also creates in the same folder an .LACCDB file with same name this file contains user and record locking information23Guidelines for Designing Databases Identify all fields needed to produce required information what do reports need to show? what queries will be run? Organize each piece of data into its smallest useful part Group related fields into tables entities frequently get their own tables place each field in an appropriate table (normalization, RD appendix) Determine each table’s primary key to uniquely identifiy each record in a table (no duplicates) composite primary key Include a common field in related tables include foreign key as common field to join related tables4Guidelines for Designing Databases (continued) Avoid data redundancy (pg. AC 55) 3 main reasons data redundancy is a problem: Determine the properties of each field properties determine an object’s appearance and behavior Name, Data Type, Field Size, Description, many others35Naming Fields and Objects Assign each object a descriptive name Object names < 64 characters letters, digits no periods, exclamation point, accent grave, brackets avoid spaces although Access allows, many other DBMS don’t objects of the same type cannot have the same name6Assigning Field Data Types Short Text (formerly Text) for storing text entries (no calculations) defaults to 255 bytes stored as a variable-length string Long Text (formerly Memo) longer, free-form text entries, typically sentences or paragraphs < 1GB, of which you can display 65,535 characters in a control on a form or report Number for storing numeric values that are non-monetary can be used for calculations several varieties…47Assigning Field Data Types Date/Time important in many business applications can be used for date/time calculations requires 8 bytes Currency for monetary values can be used for calculations AutoNumber (Tutorials 1, 12) a field containing values that Access automatically increments as each new record is added to table guarantees a unique field value for each record… for a PK we can’t control its values8Assigning Field Data Types Yes/No (logical) presence/absence of an attribute OLE Object objects such as pictures, sound, data files from other applications not directly modifiable in Access Hyperlink a path to an object, document, web page, or other destination Attachment (new Access 2007, Tut 12) attach a file (image, spreadsheet, document) to a record in your database  can view and edit attached files, depending on field properties provide greater flexibility than OLE; compressed to store more efficiently Lookup Wizard (Tutorial 5) not actually a data type used to help set properties for a foreign key field to make it easy to link a record in one table to a related record in another59Creating Table in Design View Table an object that stores data in rows (records) & columns (fields) each record in a table has the same field structure created Billing table in Design View creating a table involves specifying each field and its properties declaring a primary key naming the table object Create tab | Table Design Table Design grid on top and Field Properties beneath hints for currently selected property press [F1] to enter Help System10Assigning Field Sizes Field Size a property that controls the maximum number of characters that can be entered into a Text field, or the range and kind of values that can be entered into a Number or AutoNumber field is predefined for other data types (eg Date/Time, Currency) Practice Time use Help to learn about the Field Size property press [F1] to get context-sensitive help special focus: Number field size options (page AC 58) balance field storage needs vs. resources consumed611Creating Table in Design View Specifications for the new Payment table Fields: TransactionID – PK and Text(48) DateIssued – Date/Time PayeeWriterID – Text(4) Amount - Currency Purpose – Text(30) AuthorizedBy – Text(25) TaxDeductible- Yes/No Practice Time Create this Payment table in your issue 25.accdb Enter 2 records for payments made to yourself, using your WriterID value for the PayeeWriterID12Specifying the Primary Key Advantages of having a primary key for each table: each record can be uniquely identified makes it possible for a table to participate in relationships Access automatically prevents duplicates Access requires the primary key be provided (not be left null) Access displays rows in primary key sequence (by default) Access stores records in order they were entered (until you compact) How do you designate a table’s primary key? How is a table’s primary key identified? Practice Time Payment table: try to duplicate a TransactionID & try to omit it713Modifying the Structure of an Access Table Moving a Field click the field selector | drag to desired location | drop Adding a Field Design tab | Insert Row new field is inserted below the current field Deleting a Field right-click field selector | Delete Rows | Yes be careful, an entire column of field values at risk! Changing Field Properties select the field | modify properties in lower pane14Practice Time Make the following changes to the Writer table: Add new field named Gender to store 1-byte codes (M or F)  Add new field named Homepage to store the URL of the writer’s web page Switch to Datasheet view and enter your Gender into your writer record Also enter a valid URL in for your homepage Test that the Homepage hyperlink works815Selected Field Properties Field Size property Description property text displayed in Status Bar when insertion point is in field up to 255 characters Decimal Places property specifies number of digits to display to right of decimal point Format property to customize how numbers,