Download Database Design and Microsoft Access: A Comprehensive Guide and more Study notes Database Management Systems (DBMS) in PDF only on Docsity!
DATABASE MANAGEMENT SYSTEMS (DBMS)
A database is a collection of information that's related to a particular subject or purpose, such as tracking customer orders or maintaining a music collection. Microsoft Access, is one such program, however the field of database programs is full of other rival programs that compete successfully these include Paradox, dBase, FoxPro, Sybase, Oracle, Lotus Approach, FoxBASE+ etc. If your database isn't stored on a computer, or only parts of it are, you may be tracking information from a variety of sources that you have to coordinate and organize yourself. Unlike other database programs, when using Microsoft Access, you can manage all your information from a single database file. Within this file, You divide your data into separate storage containers called tables ; view, add, and update table data by using forms ; find and retrieve just the data you want by using queries ; and analyze or print data in a specific layout by using reports. You can also allow users to view, update, or analyze the database's data from the Internet or an intranet by creating data access pages.
STEPS IN DESIGNING A DATABASE
Before you use Microsoft Access to actually build the tables, forms, and other objects that will make up your database, it is important to take time to design your database. Whether you are using a Microsoft Access or any other database program, good database design is the keystone to creating a database that does what you want it to do effectively, accurately, and efficiently. This topic provides reference information about these basic steps in designing a database:
- Determine The Purpose Of Your Database
- Determine The Tables You Need In The Database
- Determine The Fields You Need In The Tables
- Identify Fields With Unique Values In Each Record
- Enter Data And Create Other Database Objects STEP ONE Step 1. Determine The Purpose Of Your Database The first step in designing a database is to determine its purpose and how it's to be used. You need to know what information you want from the database. From that, you can determine what subjects you need to store facts about (the tables) and what facts you need to store about each subject (the fields in the tables). This may be achieved using the following fact finding techniques: (a.) Conducting Interviews with the would be users of the database (b.) Through Observation (c.) Through Questionnaires (d.) Through Document Review STEP TWO Step 2. Determine The Tables You Need Determining the tables can be the trickiest step in the database design process. That's because the results you want from your database — the reports you want to print, the forms you want to use, the questions you want answered — don't necessarily provide clues about the structure of the tables that produce them. You don't need to immediately start designing your tables using Microsoft Access. In fact, it may be better to sketch out and rework your design on paper first. STEP THREE Step 3. Determine the fields you need Each table contains information about the same subject , and each field in a table contains individual facts about the table's subject. For example, a customer table may include company name, address, city, state, and phone number fields. ( Fields – Refer to an element of a table that contains a specific item of information, such as a last name. When sketching out the fields for each table, the following tips should be borne in mind:
Relate each field directly to the subject of the table.
Don't include derived or calculated data (data that is
the result of an expression – i.e. any combination of operators, constants, literal values, functions, and names of fields (columns), controls, and properties that evaluates to a single value.
Include all the information you need.
Store information in its smallest logical parts (for
example, Surname, First Name and Last Name, rather than Name).
Step 4. Determine the Data Type for each Field Once you have determined your fields, Decide what kind of data type to use for a field. Data types determine the kind of information that can be store within the specified field. Essentially, they refer to the attribute of a variable or field that determines what kind of data it can hold e.g. the Text and Memo field data types allow the field to store either text or numbers, but the Number data type will allow the field to store numbers only.
- When Microsoft Access first starts up, a dialog box is automatically displayed with options to create a new database or open an existing one. If this dialog box is displayed, click Database Wizard , and then click OK. If you have already opened a database or closed the dialog box that displays when Microsoft Access starts up, click New Database on the toolbar.
- On the Databases tab, double-click the icon for the kind of database you want to create.
- Specify a name and location for the database.
- Click Create to start defining your new database. Creating a Database from scratch
- When Microsoft Access first starts up, a dialog box is automatically displayed with options to create a new database or open an existing one. If this dialog box is displayed, click Blank Database , and then click OK. If you have already opened a database or closed the dialog box that displays when Microsoft Access starts up, click New Database on the toolbar, and then double-click the Blank Database icon on the General tab.
- Specify a name and location for the database and click Create. After you create a blank database, you must perform additional steps to define the objects that will make up your database.
DATABASE OBJECTS
The Database Window To work with all the Objects in a Microsoft Access Database, use the Database Window. Under Objects, click Object of interest, and use NEW button or “Create…” to create new Objects. Alternatively to modify the existing objects, select the object of interest and click design. Tables This is the fundamental structure of a relational database management system. In Microsoft Access, a table is an object that stores data in records. The data is usually about a particular category of things, such as employees or orders. Store data once in one table, but view it from multiple different locations. When you update the data or make changes, it is automatically updated everywhere it appears. To store your data, create one table for each type of information that you track. Query Is a question about the data stored in your tables, or a request to perform an action on the data. To find and retrieve just the data that meets conditions that you specify, including data from multiple tables, create a query. A query can also update or delete multiple records at the same time, and perform predefined or custom calculations on your data. Form Is a Microsoft Access database object on which you place controls for taking actions or for entering, displaying, and editing data in fields. To easily view, enter, and change data directly in a table, create a form. Report Refers to a Microsoft Access database object that prints information formatted and organized according to your specifications. Examples of reports are sales summaries, phone lists, and mailing labels. To analyze your data or present it a certain way in print, create a report. For example, you might print one report that groups data and calculates totals, and another report with different data formatted for printing mailing labels. Data Access Page A Web page that has a connection to a database; in a data access page, you can view, add to, edit, and manipulate the data stored in the database. To make data available on the Internet or an intranet for interactive reporting, data entry, or data analysis, use a data access page. Microsoft Access retrieves the data from one or more tables and displays it on the screen with the layout you choose in the Page Wizard, or a layout that you create from scratch.
Macro
An action or a set of actions you can use to automate tasks.
Module
A collection of declarations, statements, and procedures stored together as one named unit. Microsoft Access has two types of modules: standard modules and class modules. Form and report modules are class modules containing code that's local to the form or report.
Object 1. Tables:
A table is a collection of data about a specific topic, such as products or suppliers. Ideally they are data structures. Containing information in columnar manner. Using a separate table for each topic means that you store that data only once, which makes your database more efficient, and reduces data-entry errors. Tables organize data into columns (called fields) and rows (called records). A common field relates two tables so that Microsoft Access can bring together the data from the two tables for viewing, editing, or printing. This key is known as a Primary key in one of the tables, while on the table where it is used to establish the link, it is called the Foreign Key. In table Design view, you can create an entire table from scratch, or add, delete, or customize the fields in an existing table. In table Datasheet view, you can add, edit, view, or otherwise work with the data in a table.
If you are creating a Number field, in the bottom part of the window set the FieldSize property to the size you want. To set a display format for your field, in the bottom part of the window click in the Format box, click the arrow, and select the format you want. When Should I create a field for Numbers or Currency? Microsoft Access provides two field data types to store data containing numeric values: Number or Currency. Use a Number field to store numeric data to be used for mathematical calculations, except calculations that involve money or that require a high degree of accuracy. The kind and size of numeric values that can be stored in a Number field is controlled by setting the FieldSize property. For example, the Byte field size will only store whole numbers (no decimal values) from 0 to 255. Use a Currency field to prevent rounding off during calculations. A Currency field is accurate to 15 digits to the left of the decimal point and 4 digits to the right. Number and Currency fields also provide predefined display formats SETTING DESCRIPTION General Number (Default) Display the number as entered. Currency Use the thousand separator; follow the settings specified in Regional Settings in Windows Control Panel for negative amounts, decimal and currency symbols, and decimal places. Euro Use the currency format, with the euro symbol, regardless of the currency symbol specified in Regional Settings in Windows Control Panel. Fixed Display at least one digit; follow the settings specified in Regional Settings in Windows Control Panel for negative amounts, decimal and currency symbols, and decimal places. Standard Use the thousand separator; follow the settings specified in Regional Settings in Windows Control Panel for negative amounts, decimal symbols, and decimal places. Percent Multiply the value by 100 and append a percent sign (%); follow the settings specified in Regional Settings in Windows Control Panel for negative amounts, decimal symbols, and decimal places. Scientific Use standard scientific notation. When should I create a field for Date/Time? Storing dates and times in a Date/Time field ensures that dates and times will be sorted properly. Also, changes made to the date or time formats that are specified through Regional Settings, Windows Control Panel. You can set the Format property to predefined date and time formats or use custom formats for the Date/Time data type. The following table shows the predefined Format property settings for the Date/Time data type. SETTING DESCRIPTION General Date (Default) If the value is a date only, no time is displayed; if the value is a time only, no date is displayed. This setting is a combination of the Short Date and Long Time settings. Examples: 4/3/93, 05:34:00 PM, and 4/3/93 05:34:00 PM. Long Date Same as the Long Date setting in the Regional Settings Properties dialog box in Windows Control Panel. Example: Saturday, April 3, 1993. Medium Date Example: 3-Apr-93. Short Date Same as the Short Date setting in the Regional Settings Properties dialog box in Windows Control Panel. Example: 4/3/93. Warning The Short Date setting assumes that dates between 1/1/00 and 12/31/29 are twenty-first century dates (that is, the years are assumed to be 2000 to 2029). Dates between 1/1/30 and 12/31/99 are assumed to be twentieth century dates (that is, the years are assumed to be 1930 to 1999). Long Time Same as the setting on the Time tab in the Regional Settings Properties dialog box in Windows Control Panel. Example: 5:34:23 PM.
SYMBOL DESCRIPTION
< Force all characters to lowercase. > Force all characters to uppercase.
Medium Time Example: 5:34 PM. Short Time Example: 17:34.
Creating a field that automatically generates numbers
To create an incrementing AutoNumber, leave the property settings in the bottom part of the window as they are (the FieldSize property is set to Long Integer and the NewValues property is set to Increment by default). To create a random AutoNumber, in the bottom part of the window on the General tab, set the NewValues property to Random. Microsoft Access provides the AutoNumber data type to create fields that automatically enter a number when a record is added. Once a number is generated for a record, it can't be deleted or changed. An AutoNumber field can generate two kinds of numbers: - AutoNumbers that increment by one are the most common kind of AutoNumber and are a good choice for use as a table's primary key. Random AutoNumbers will generate a random number that is unique to each record within the table.
Creating a field for Yes/No, True/False, and On/Off datatype
To display True/False or On/Off instead of Yes/No (the default) in the field, in the bottom part of the window on the General tab, click in the Format box and select the format you want. You can set the Format property to the Yes/No, True / False , or On/Off predefined formats or to a custom format for the Yes/No data type. Microsoft Access uses a check box control as the default control for the Yes/No data type. Predefined and custom formats are ignored when a check box control is used. Therefore, these formats apply only to data that is displayed in a text box control. If you specify one predefined format and then enter an equivalent value, the predefined format of equivalent value will be displayed. For example, if you enter True or On in a text box control with its Format property set to Yes/No, the value is automatically converted to Yes. Example showing a custom yes/no format for a text box control. The control displays the word "Always" in blue text for Yes, True , or On, and the word "Never" in red text for No, False , or Off.
;"Always"[Blue];"Never"[Red]
Creating a field to store OLE objects (Pictures, Word Documents etc)
In the Data Type column, click the arrow and select OLE Object. Note OLE Object fields are used to store data such as Microsoft Word or Microsoft Excel documents, pictures, sound, and other types of binary data created in other programs. OLE objects can be linked to or embedded in a field in a Microsoft Access table.
Creating a field that looks up data from another table
- In Design view, open the table where you want to add the lookup field.
- Do one of the following: To insert a new field within the table, click in the row below where you want to add the field, and then click Insert Rows on the toolbar, or to add a new field at the end of the table, click in the first blank row. Type the name for the field in the Field Name column. If the field you want to use as the foreign key for the Lookup field already exists, click in that field's row. For example, if you have a Products table that has a SupplierID field already defined, and you want to change it to a Lookup field to display supplier names from your Suppliers table, click in the SupplierID field's row.
- In the Data Type column, click the arrow and select Lookup Wizard.
- Click the option that indicates you want the Lookup field to look up the values in a table or query.
- Click Next and follow the directions in the remaining Lookup Wizard dialog boxes. When you click the Finish button, Microsoft Access creates the Lookup field and sets certain field properties based on the choices you made in the wizard. The combo box or list box is created automatically for the form.
CHARACTER DESCRIPTION 0 Digit (0 through 9, entry required). 9 Digit or space (entry not required).
Digit or space (entry not required; blank positions converted to spaces).
L Letter (A through Z, entry required). ? Letter (A through Z, entry optional). A Letter or digit (entry required). a Letter or digit (entry optional). & Any character or a space (entry required). C Any character or a space (entry optional).
. , : ; - / Decimal placeholder and thousands, date, and time separators. < Causes all characters that follow to be converted to lowercase. > Causes all characters that follow to be converted to uppercase. ! Causes the input mask to display from right to left, rather than from left to right. Characters typed into the mask always fill it from left to right. \ Causes the character that follows to be displayed as a literal character. Used to display any of the characters listed in this table as literal characters (for example, \A is displayed as just A). Password Password creates a password entry text box. Any character typed in the text box is stored as the character but is displayed as an asterisk (*).
Default Value…
You can use the Default Value property of a field to specify a value that is automatically entered into that field when you add a new record
Examples of Default Values in Fields
FIELD EXPRESSION DEFAULT FIELD VALUE Quantity 1 1 Region "MT" MT Region "New York, N.Y." New York, N.Y (note that you must enclose the value in quotes if it includes punctuation) Fax " " A zero-length string to indicate that by default this field should be empty Order Date Date( ) Today's date
Validation Rules…
Validation rule - A rule that sets limits or conditions on what can be entered in one or more fields. You can set validation rules for a field, a record, or a control on a form. A validation rule for a field or control is checked when you move to a different field or control. A validation rule for a record is checked when you move to a different record. Microsoft Access displays the message specified in the Validation Text property whenever the rule is violated.
Examples of Field Validation Rules in Tables and on Forms
VALIDATIONRULE SETTING VALIDATIONTEXT SETTING <>0 Please enter a nonzero value. 0 Or >100 Value must be either 0 or over 100. Like "K???" Value must be four characters beginning with the letter K. <#1/1/96# Enter a date before 1996. >=#1/1/97# And <#1/1/98# Date must be in 1997.
Object 2. FORMS
Creating a form by using AutoForm
AutoForm creates a form that displays all fields and records in the underlying table or query. If the record source you select has related tables or queries, the form will also include all the fields and records from those record sources.
- In the Database window, click Forms under Objects.
- Click the New button on the Database window toolbar. 3. In the New Form dialog box, click one of the following wizards: AutoForm: Columnar Each field appears on a separate line with a label to its left. AutoForm: Tabular The fields in each record appear on one line, with the labels displayed once at the top of the form. AutoForm: Datasheet The fields in each record appear in row-and-column format, with one record in each row and one field in each column. The field names appear at the top of each column.
- Click the table or query that contains the data you want to base your form on.
- Click OK. Microsoft Access applies the last autoformat you used to the form. If you haven't created a form with a wizard before or haven't used the AutoFormat command on the Format menu, it uses the Standard autoformat.
Creating a form with a Wizard
- In the Database window, click Forms under Objects.
- Click the New button on the Database window toolbar.
- In the New Form dialog box, click the wizard that you want to use. A description of the wizard appears in the left side of the dialog box. 4. Click the name of the table or other record source that includes the data you want to base your form on. Note You don't need to do this step if you click the Form Wizard option — you can specify the record source for the form in the wizard.
- Click OK.
- If you clicked Form Wizard , Chart Wizard , or PivotTable Wizard in step 3, follow the directions in the wizard dialog boxes. If you clicked AutoForm: Columnar , AutoForm: Tabular , or AutoForm: Datasheet , Microsoft Access automatically creates your form. If the resulting form doesn't look the way you want, you can change it in Design view.
Creating a Blank form and Select a record source
- In the Database window, click Forms under Objects.
- Click the New button on the Database window toolbar.
- In the New Form dialog box, click Design View. 4. Click the name of the table or other record source that includes the data you want to base your form on. If the form won't contain data (for example, if you want to create a form to use as a switchboard to open other forms or reports, or if you want to create a custom dialog box), don't select anything from this list. Note If you want to create a form that uses data from more than one table, base your form on a query.
Object 3. Queries
You use queries to view, change, and analyze data in different ways. You can also use them as the source of records for forms, reports, and data access pages. Other typical uses of a query include:- Retrieving records using specific criteria or meeting certain conditions Brining together data from multiple tables, and sorting it in a particular order. Performing calculations on groups of records Calculating a sum, count or another type of total, and then grouping the results by two types of information (cross tab) The most common type of query is a select query. A select query retrieves data from one or more tables by using criteria you specify and then displays it in the order you want.
Examples of Criteria Expressions for Queries or Advanced Filters
You can use a criteria expression in the Criteria cell, either in the Advanced Filter/Sort window or in query Design view. Examples of expressions that use text values as criteria FIELD EXPRESSION DESCRIPTION ShipCity "London" Displays orders shipped to London. ShipCity "London" Or "Hedge End" Uses the Or operator to display orders shipped to London or Hedge End. ShippedDate Between #1/5/95# And #1/10/95# Uses the Between...And operator to display orders shipped no earlier than 5-Jan-95 and no later than 10-Jan-95. ShippedDate #2/2/95# Displays orders shipped on 2-Feb-95. ShipCountry In("Canada", "UK") Uses the In operator to display orders shipped to Canada or the UK. ShipCountry Not "USA" Uses the Not operator to display orders shipped to countries other than the USA. ShipName Like "S*" Orders shipped to customers whose name starts with the letter S.
Examples of expressions that calculate or manipulate dates, and then use the result as criteria
Field Expression Description RequiredDate Between Date( ) And DateAdd("m", 3, Date( )) Uses the Between...And operator and the DateAdd and Date functions to display orders required between today's date and three months from today's date. OrderDate < Date( )- 30 Uses the Date function to display orders more than 30 days old. OrderDate Year([OrderDate])=1996 Uses the Year function to display orders with order dates in 1996. OrderDate DatePart("q", [OrderDate])=4 Uses the DatePart function to display orders for the fourth calendar quarter. Examples of expressions that use part of a field's value as criteria FIELD EXPRESSION DISPLAYS ShipName Like "S" Orders shipped to customers whose names start with the letter S. ShipName Like "Imports" Orders shipped to customers whose names end with the word "Imports". ShipName Like "[A-D]" Orders shipped to customers whose names start with A through D. ShipName Like "ar*" Orders shipped to customers whose names include the letter sequence "ar".
Ways to perform Calculations in a Query – Calculated Totals
There are many types of calculations you can perform in a query. For example, you can calculate the sum or average of the values in one field, multiply the values in two fields, or calculate the date three months from the current date. When you display the results of a calculation in a field, the results aren't actually stored in the underlying table. Instead, Microsoft Access reruns the calculation each time you run the query so that the results are always based on the most current data in the database. Therefore, you can't manually update the calculated results. To display the results of a calculation in a field, you can use a predefined calculation that Microsoft Access provides or custom calculations you define. A custom calculation enables you to perform numeric, date, and text calculations on each record using data from one or more fields. For example, with a custom calculation, you can multiply one field's values by a set amount, find the difference between two dates stored in separate fields, combine several values in a Text field, or create subqueries. For custom calculations, you need to create a new calculated field.
The expression can be made up of multiple calculations — for example, Sum([UnitsInStock]+
[UnitsOnOrder]). You can also use a calculation to update data from an update query.
Examples of manipulating text values in calculated fields
You enter the expression in the Field cell in the query design grid. The fields you reference in the expression are from the tables or queries in the current query. EXPRESSION DESCRIPTION FullName: [FirstName] & " " & [LastName] Displays the value of the FirstName and LastName fields, separated by a space, in the FullName field. Address2: [City] & " " & [Region] & " " & [PostalCode] Displays the value of the City, Region, and PostalCode fields, separated by spaces, in the Address2 field.
Examples of performing arithmetic operations in calculated fields
You enter the expression in the Field cell in the query design grid. The fields you reference in the expression are from the tables or queries in the current query. EXPRESSION DESCRIPTION PrimeFreight: [Freight] * 1.1 Displays freight charges plus 10 percent in the PrimeFreight field. OrderAmount: [Quantity] * [UnitPrice] Displays the product of the values in the Quantity and UnitPrice fields in the OrderAmount field. LeadTime: [RequiredDate] - [ShippedDate] Displays the difference between the values in the RequiredDate and ShippedDate fields in the LeadTime field. TotalStock: [UnitsInStock]+[UnitsOnOrder] Displays the sum of the values in the UnitsInStock and UnitsOnOrder fields in the TotalStock field.
Examples of manipulating and calculating dates in calculated fields
You enter the expression in the Field cell in the query design grid. The fields you reference in the expression are from the tables or queries in the current query. EXPRESSION DESCRIPTION LagTime: DateDiff("d", [OrderDate], [ShippedDate]) Uses the DateDiff function to display in the LagTime field the number of days between the order date and ship date. YearHired: DatePart("yyyy",[HireDate]) Uses the DatePart function to display in the YearHired field the year each employee was hired. Age: Year(Date())-Year([BirthDate]) Returns the employees age in years by subtracting the birthdate from today’s date indicated in years. Date( )- 30 Uses the Date function to display the date 30 days prior to the current date.
Object 4. Reports:
A report is an effective way to present your data in a printed format. Because you have control over the size and appearance of everything on a report, you can display the information the way you want to see it. Most of the information in a report comes from an underlying table or query, which is the source of the report's data.
Creating a report using AutoReport
With AutoReport, you select one record source and either columnar or tabular layout; AutoReport uses all the fields from the record source and applies the last autoformat you used to the report. AutoReport creates a report that displays all fields and records in the underlying table or query.
- In the Database window, click Reports under Objects.
- Click the New button on the Database window toolbar. 3. In the New Report dialog box, click one of the following wizards: AutoReport: Columnar. Each field appears on a separate line with a label to its left. AutoReport: Tabular. The fields in each record appear on one line, and the labels print once at the top of each page.
- Click the table or query that contains the data you want to base your report on.
- Click OK. Microsoft Access applies the last autoformat you used to the report. If you haven't created a report with a wizard before or haven't used the AutoFormat command on the Format menu, it uses the Standard autoformat.
Creating a report with a Wizard
The wizard asks you detailed questions about the record sources, fields, layout, and format you want and creates a report based on your answers.
- In the Database window, click Reports under Objects.
- Click the New button on the Database window toolbar.
- In the New Report dialog box, click the wizard that you want to use. A description of the wizard appears in the left side of the dialog box. 4. Click the table or query that contains the data you want to base your report on. Note Microsoft Access uses this table or query as the default record source for the report. However, you can change the record source in the wizard and select fields from other tables and queries.
- Click OK.
- If you clicked Report Wizard , Chart Wizard , or Label Wizard in step 3, follow the directions in the wizard dialog boxes. If you click AutoReport: Tabular or AutoReport: Columnar , Microsoft Access automatically creates your report. If the resulting report doesn't look the way you want, you can change it in Design view.
