Tutorial 1

Reviewing Database Objects

DBMS

n Database management system (DBMS) -
Software that is used to manage, store, retrieve, and order large amounts of information

n Relational database management system (RDBMS) - In an RDBMS, you can link tables through a common field, and thereby combine data in new objects in order to minimize data duplication.

Objects and Properties

n Object - A code-based abstraction of some real entity.

n Access object - An item that can be created, manipulated, controlled, or programmed

n Property - A characteristic, or attribute, of an object

n Database window - What you see when you open an Access database. It is the command center for working with objects

Sample Data throughout the text

n The data for MovieCam Technologies is presented as an example.

q The database is kept small in order to provide data students can easily observe. This is a sampling of data and that, in a real situation; the data would be much larger.

q Many of the topics related to database management make more sense if you can picture the data in very large quantities.

Learn how Access saves a database.

n The “Save” button in Access differs from the Save buttons on other Windows programs.

q     Save button in Access, you are saving the design of the Access object not the data itself.

q     Access saves data as it is entered.

n      For this reason, the location at which you are storing your database must always be accessible while working with a particular database.

n      For example, if the database is located on a diskette, the diskette must be in the diskette drive at all times while working with this particular database.

Naming Conventions
(required by Access)

n Object Names – up to 64 characters

q    Include any combination of letters, numbers, spaces, and special characters, except . ! ` [ ]

q    Cannot begin with a space

q    Cannot include control characters

q    Table, view, or stored procedure names cannot include a quotation mark

q    Table and query names must be unique

Naming Conventions
(suggested by developers)

n Include tag (lowercase letters) – identify type of object

q tbl table, frm form, qry query

n Add prefix to tag further identify object

q frms represents a sub-form

n Include descriptive name contains no spaces

q Capitalize first letter of each word

n Keep object names short

n Use plural names for tables

q tblEmployees not tblEmployee

Sample Object Names The Database Window
n The Access window contains a menu bar, a toolbar, a task pane, and a status bar.

n task pane is used to either create a new database or open an existing database.

q To create a new database, you make a selection from the new section of the task pane.

q To open an existing database, students can either select from the list of “recently opened” databases or from the “more files” option, which will allow them to “browse” for the desired database.

n database window contains a menu bar, an objects bar, and a groups bar.

n objects bar lists all the objects available in the database.

q list of objects consists of tables, queries, forms, reports, pages, macros, and modules.

Note: objects bar represents the seven different types of objects available in Access. Tables

n Relational database - is a collection of tables that are related to one another based on a common field.

n Tables - within the database contain a collection of records.

n Records - within the table contain a collection of fields.

n Field - in the table represents a particular characteristic of the data.

Primary and foreign keys

n A field, or a collection of fields, is designated as the primary key.

n primary key uniquely identifies a record in the table.

n When the primary key of one table is represented in a second table to form a relationship, it is called a foreign key.

n Example: (Figure 1-5).

Data validation

n Validation rule - An optional expression (formula) that can be created at the table or field level.

n Table validation rule - A validation rule that allows you to test the validity of one field compared to another

n Field validation rule -A validation rule that allows you to validate a field compared to a constant

n Validation text - A message that appears in the warning message box that opens if the validation rule is violated

Validation Rules & Text

Table and Field properties

nField Size property - This property defines the maximum size for data stored in a Text, Number, or AutoNumber field.

nFormat property - You can use the Format property to specify a standard predefined display format for AutoNumber, Number, Date/Time, Currency, and Yes/No fields

nInput Mask property - The input mask controls how data is actually entered into a field, as opposed to how it is displayed after it is entered (which is controlled by the Format property)

nCaption property - This property displays the specified caption text rather than the field name as the column heading in Datasheet view for a table or query.

nDefault Value property - When you specify a value in a field’s Default Value property, Access automatically enters that value for that field in each new record.

nNull - A blank value

nRequired property - If you set this property to Yes, text or a value must be entered into the field, or Access will not allow the record to be saved.

nIndexed property - You can use the Indexed property to create an index on a field

nNew Values property - This property applies only to a field with an AutoNumber data type and specifies how a number is automatically generated for that field when a new record is added to a table.

nPrecision property - This property can be applied only to fields of Number data type with their Field Size property set to Decimal.

nScale property - This property can be applied only to Number fields whose Field Size property has been specified as Decimal.

nDecimal Places property - This property, which applies only to Number and Currency fields, determines the number of decimal places displayed to the right of the decimal point.

nUnicode Compression - Microsoft Access 2002 uses the Unicode character-encoding scheme to represent data in a Text, Memo, or Hyperlink field.

Queries

n Select queries - Commonly used to combine fields from more than one table into a single object.

n Query results, a recordset, are a temporary view of the data.

q query does not create a new table.

n Query specifies what fields you want to see and which records should be included.

q query is like you are looking at the data through an overlay.

q query is run, the resulting datasheet includes only the fields and records that meet the specifications of the query.

n Parameter query - A query which, when run, a dialog box is displayed on the screen that prompts for information such as a criterion for retrieving records or a value to be inserted in a field.

n Crosstab query - A crosstab query displays summarized values from one or more tables in a spreadsheet format. Crosstab queries can calculate sums, averages, counts, or other type of totals for data that is grouped by two types of information - one down the left side of the datasheet and another across the top

Action query

n Action query - Action queries include update, delete, append, and make-table queries.

q An action query is different from a select query in that it modifies the data in the underlying table when it is run.

n Action queries which actually do produce new data.

SQL – Specific Queries

n SQL - specific queries are written in SQL using the SQL view, and cannot be created in Design view like other types of queries

q SQL view is accessed from the query’s Design view by clicking View on menu bar, then SQL view

4 Types SQL – Specific Queries

Union – query for similar data from two or more unrelated tables
Pass-through – send commands directly to ODBC databases (SQL Server 2003)

work directly with tables on database server instead of linking to them, run stored procedures

Advantage - Query and data retrieval run on server side of client relationship
Data-definition – create, delete, or alter tables or create indexes
Subqueries – a secondary query

Forms

n Form allows the user to view data one record at a time.

q     useful for maintaining, viewing, and printing records

q     create in the forms design window or Forms Wizard

n      AutoForm wizard easiest way to create a form

q     AutoForm wizard will use the table (or query) selected as the basis of the form to create a form that displays all the fields of the table (or query).

n      form has a navigation bar just like the navigation bar seen on the datasheet view.

q     Cycle property - Used to specify what happens when you press the TAB key and the focus is in the last control on a bound form.

Reports

n Printed version of the data can be formatted according to specifications.

q     data in the printout can consist of data from a single table or multiple tables.

q     Reports Wizard that allows you to easily create a report.

n      AutoReport wizard is the easiest way to create a report

n      The report can be based on a table(s) or it can be based on a query. If the report is based on a query, it will contain the same fields that were selected for the query.

n Note: normal procedure in database development is to first develop tables and queries. Then create forms and reports based on the tables and/or queries.

q     good idea to do a design before actually creating the report.

q     design should specify which fields should be included on the report and how the report should be laid out.

n      doing this activity will save time in the long term won’t have to make so many changes later.

n      doing a preview will save paper as they will be able to make changes before printing.

Pages

n data in the database can be made available as an HTML page by creating a Data Access Page.

q     in the background, Access is actually creating HTML code to display the data in the database.

q     In this section, creating a very simple page and accepting the defaults.

q     with just a little more effort create a page that is very attractive and presentable on a company intranet or even on the Internet.

Macros

n recorded macro in Excel or Word.

q easy it is to have the macro recorder record a series of actions that can be repeated simply by running the macro.

n macros in Access are quite different then macros in other applications.

q     In particular, there is no macro recorder in Access.

q     Rather, a macro in Access uses a collection of predefined actions that can be used to automate repeated tasks.

q     macros are rather restrictive and they will usually opt for writing modules in VBA.

Modules

n Modules are made up of VBA statements written by the programmer/developer.

q     learning to use VBA with Access will greatly enhance the functions they will be able to perform in the database.

q     VBA provides a powerful and fast way to interact with the data in the database.

q     in order to be a good Access developer, it is essential that they learn to program Access object using VBA.

IFS 110 Computer Assignment 1

REVIEW ASSIGNMENTS
AC pages 40-41

Return to IFS 110 Notes!