Study Guides (238,399)
Canada (115,129)

Computer Science Exam Review _ Access 2010.docx

27 Pages
Unlock Document

McMaster University
Computer Science
Anthony Hurst

Computer Science Exam Review – Access 2010 Fundamentals Backstage = select current/existing dbase or create new (blank or template) [see Figure 1-4, textbook p.227] Query = “ask dbase a question” = English  RDBMS syntax; Form (input), Report (output) Navigation Pane – database objects; Datasheet View – direct entry of fields and records What is the name of the Windows file manager? How do you see file “extensions” (i.e., file-types) in the display of filenames on your computer? Organizing Data • The first step is to identify each entity, and its individual attributes Entity: a person, object or event • Next, define each entity’s attributes as a set of fields; each Access entity is called a table Each field (i.e., column) has a specific data-type A set of field values is called a record (i.e., row) • Enter data values into your table(s) and store the data as an Access 2010 file (.accdb) Formal terms: attributes, Customer relation, tuples. An Access table is a two-dimensional representation of a relation. Databases and Relationships • A group of related tables is properly known as a relational database; Access simply uses “database” • You can connect the records in the separate tables through a common field (also called a join field) • A primary key (PK) is a field, or a collection of fields, whose values uniquely identify each record in a table • When you include the PK from one table as a field in a second table, to form a relationship between the two tables, the field is called a foreign key (FK) with respect to its use in the second table Other design models for database structure: network, hierarchical A join operation creates something akin to a result table containing “master records”, in which the join field is often duplicated in the result table • Data Dictionary: Design documentation that describes all pertinent information about the database definitions (tables, fields, data-types, constraints, relationships, keys, etc.) • Keys: Keys involving more than one field are referred to as composite, concatenated or compound keys A field which has a set of unique values and can therefore be used to “identify” a particular record is known as a candidate key; if a candidate key does not exist naturally, an artificial one must be created (i.e., to become the PK) • Restrictions on Keys: PK must have unique values; no duplicates allowed PK must not have a null value; a data value is required For each non-null FK value, a corresponding PK value must exist elsewhere (i.e., in another table in the database) FK values may be duplicated, but “satellite” data are different for each occurrence; FK can have null value in some instances NOTE: keys are often, BUT NOT NECESSARILY, used as the basis for sorting the records in a table Types of Relationships • Types of relationships among tables: One to One (1:1) One to Many (1:N) Many to Many (N:M) • Examples: 1:1 … Husband/Wife 1:N … Parent/Child; Account/Transaction M:N … Doctor/Patient; Supplier/Customer; Bus/Route; Book/Author • Relationships expressed in English with the help of verbs: Patient visits Doctor Producer makes Film Athlete plays on a Team Inventory has Products 1:1 is rare in the real world, but is often imposed on a set of relations for design reasons Designing a Relational Database • Consider the “multi-valued” attributes that exist in the following pair of tables (expressed in Relational Notation): HR: Employee, Child’s Name LIBRARY: Title, Author • Normalization = a process used by designers to eliminate undesirable anomalies that can occur during the daily management of the database; traditional problems associated with accuracy and integrity are avoided through careful analysis of the table relationships; a typical approach is to decompose large tables into smaller, related tables e.g., 1NF: no multi-valued attributes are allowed in tables e.g., 3NF: no transitive dependencies (i.e., non-primary key values depend solely upon primary key values) Note: 3NF is considered the minimal normal form necessary for a working database design Special Considerations • Sharing and Security Isolation DBA can create Views for individuals/groups, to protect sensitive data (e.g., government records, client data, employee personal data, business operational data) Locking shared data classic scenario = Readers & Writers Problem Protection user passwords, capability controls, time controls, monitoring Recovery backup, transaction logging, checkpoints Overall database design formally known as a “schema”, views are “sub-schemas” NOTE: Access uses the term “view” in a very different manner [Datasheet View, Design View are not sub-schemas] • Real-Time Operation Serialization sequencing of operations, time-stamping of transactions Update anomalies, Lost Updates E.g., banking, international transactions, stock markets, library book loans, air traffic control, astronomy, airline or hotel reservations, bus or taxi dispatching Scientific experiments (real-time data capture) Time Zones Overall database design formally known as a “schema”, views are “sub-schemas” NOTE: Access uses the term “view” in a very different manner [Datasheet View, Design View are not sub-schemas] Relational DBMS • A database management system (DBMS) is a software program that lets you create databases and then manipulate data in them Creating a Table in Datasheet View • Click the Create tab on the Ribbon • In the Tables group, click the Table button. • Accept the default ID primary key field with the AutoNumber data-type, or rename the field and change its data-type, if necessary [see note below] • In the Add & Delete group on the Fields tab, click the button for the type of field you want to add to the table (for example, click the Text button), and then type the field name. Repeat this step to add all the necessary fields to the table Note: Generally wise to avoid using AutoNumber data-type for PK; also avoid accepting Access suggestions for default PK (i.e., explicitly choose your own) • In the first row below the field names, enter the value for each field in the first record, pressing the tab or Enter key to move to the next field • After entering the value for the last field in the first record, press the Tab or Enter key to move to the next row, and then enter the values for the next record. Continue this process until you have entered all the records for the table • Click the Save button on the Quick Access Toolbar, enter a name for the table, and then click the OK button Entering Records Saving a Table • Click the Save button on the Quick Access Toolbar The Save As dialog box opens • In the Table Name text box, type the name for the table • Click the OK button File, Save-As is the method used to store a database in an earlier file format (Access-97, Access-2007, etc.) Opening a Database • Start Access. If necessary, click the File tab to display Backstage view • Click the Open command in the navigation bar to display the Open dialog box • Navigate to the database file you want to open, and then click the file • Click the Open button Navigation buttons at bottom left; records sorted on PK In the tutorial exercise, this table’s records will be copied into the current working table in the database called Belmont Creating a Simple Query • A query can be thought of as a question you “ask” about the data stored in the database E.g., Which Canadian provinces have an average summer temperature above 25 Celsius? E.g., Which salespersons have sold the most trucks so far this year? • Simple Query Wizard used to quickly select records & fields to form a question • Queries are saved as objects in the Access file The query is not actually entered in a natural language; several methods available (e.g., SQL, QBE, Wizard, Report) • The Simple Query Wizard allows you to select records and fields quickly Typically, a query result is not stored permanently in the database – only the query design is saved as an object (in SQL form) Creating a Simple Form • A form is an object you use to enter, edit, and view records in a database • You can design your own forms, use the Form Wizard, or use the Form tool to create a simple form quickly and easily Creating a Simple Report • A report is a formatted printout (or screen display) of the contents of one or more tables in a database • The Report tool places all the fields from a selected table (or query) on a report, making it the quickest way to create a report Printing a Report • Open the report in any view, or select the report in the Navigation Pane • To print the report with the default print settings, click the File tab to display Backstage view, click the Print tab, and then click Quick Print or • To display the Print dialog box and select the options you want for printing the report, click the File tab, click the Print tab, and then click Print (or, if the report is displayed in Print Preview, click the Print button in the Print group on the Print Preview tab) Another printing option is to use CuteWriter to print to a PDF file; SPECIAL NOTE: you will not be able to perform printing tasks in the tutorial labs Viewing Objects in the Navigation Pane Compacting and Repairing a Database • Compacting a database rearranges database objects and data to decrease the file size • Make sure the database file you want to compact and repair is open • Click the File tab to display Backstage view • Make sure the Info tab is selected in the navigation bar • Click the Compact & Repair Database button Backing Up and Restoring a Database • Backing up a database is the process of making a backup copy of the database file to protect your database against loss or damage • The Back Up Database command enables you to back up your database file from within the Access program, while you are working on your database • Always back up your backup. Building a Database and Defining Table Relationships Guidelines for Desigining Databases • Identify all the fields needed to produce the required information • Organize each piece of data into its smallest useful part • Group related fields into tables • Determine each table’s primary key • Include a common field in related tables • Avoid data redundancy • Determine the properties of each field Guidelines for Setting Field Properties • You must name each field, table, and other object • Choose an appropriate data type Note that the records are not “named” as such. How is each record distinguished from any other record? • The Field Size property defines a field value’s max
More Less

Related notes for COMPSCI 1BA3

Log In


Don't have an account?

Join OneClass

Access over 10 million pages of study
documents for 1.3 million courses.

Sign up

Join to view


By registering, I agree to the Terms and Privacy Policies
Already have an account?
Just a few more details

So we can recommend you notes for your school.

Reset Password

Please enter below the email address you registered with and we will send you a link to reset your password.

Add your courses

Get notes from the top students in your class.