Unit 2 database.docx

13 Pages
Unlock Document

Computer Science

McMaster University Computer Science Chapter 2 Databases Fundamentals Session 1.1 Creating a Database The Access Window Visual Overview Terminology: - Shutter Bar/Open/Close Button: allows you to close and open the Navigation Pane; you might want to close the pane so that you have more room on the screen to view the object’s contents. - Navigation Pane: the area that list all the objects (tables, reports, and so on) in the database, and it is the main control center for opening and working with database objects - Datasheet View: shows the table’s contents as a datasheet. The status bar indicates the current view, in this case, Datasheet view. - Add & Delete group: contains options for adding different types of fields, including Text and Currency, to a table. - Click to Add column: provides another way for you to add fields to the table - Access Windows: the program window that appears when you create a new database or open an existing database. You use the window buttons to minimize, maximize, and close the Access windows. - Datasheet: it displays the table’s contents in rows and columns, similar to a table that you create in a Word document or an Excel spreadsheet. Each row will be a separate record in the table, and each column will contain the field values for one filed in the table. Access assigns the default name “Table 1” to the first new table you create. By default, Access creates the ID column as the primary key field for all new tables. The Microsoft Access Help button Opens the Help window, where you can find information about Access commands and features as well as instruction for using them. The Ribbon provides the main Access commands organized by task into tabs and groups. Introduction to Database Concepts Organizing Data Organizing, storing, maintaining, retrieving, and sorting this type of data are critical activities that enable a business to find and use information effectively. Before storing data on a computer, however, you must organize the data. You first step in organizing data is to identify the individual fields. A field is a single characteristics or attribute of a person, place, object, event or idea. Next, you group related fields together into tables. A table is a collection of fields that describes a person, place, objects, event or idea. The specific value, or content, of a filed is called the Field Value (record). (Figure 1-1 Txt Pg 224) Databases and Relationships A collection of related table is called a database, or a relational database. You connect the records in the separate table through a common field that appear in both tables. A Primary Key is a field, or a collection of fields, whose values uniquely identify each record in a table. No two records can contain the same value for the primary key field. When you include the primary key form one table as a field in a second table to form a relationship between the two tables, it is called a Foreign Key( does not necessarily contain unique value). Relational Database Management System (RDMBS) A database management system (DBMS) is a software program that lets you create databases and then manipulate data in them. Most of today’s database management systems, including Access, are called relational database management systems. In a relational database management system, data is organized as a collection of tables. As stated earlier, a relationship between two tables in a relational DBMS is formed through a common field. A relational DBMS controls the storage of database on disk and facilitates the creation, manipulation, and reporting of data. Relational DBMS provides following functions( Figure 1-3 Txt pg 226) 1. Create database structures containing fields, tables, and table relationship 2. Easily add new records, change field values in existing records, and delete records 3. Contains a built-in query language, which let you obtain immediate answers to the question you ask about you data. 4. Contains a buit0in report generator, which let you produce professional-looking, formatted reports from you data, 5. Protects databases through security, control and recover facilities. A company can benefit from a relational DBNS because it allows users working in different groups to share the same date. More than one user can enter data into a database, more than one user can retrieve and analyse data that other users entered. Finally, unlike other software programs, such as spreadsheet programs, a DBMS can handle massive amounts of data and can be used to create relationships among multiple tables, and has up to 225 people using the table at the same time. Creating a Database When you start Access, the first screen that appears is Backstage View(Figure 1-4 Txt pg 227), which is the starting place for you work in Access. Backstage view provides options for you to get information about the current database, create a new database, if open an existing database. To create a new database that does not contain any data or objects, you use the Blank database option. If the database you need to create contains objects that match those found in common database, you can use a template. A template is a predesigned database that includes professionally designed tables, reports and other database objects that can make it quick and essay for you to create a database. When you create a new database, make sure you have created your copy of the Access Starting Data Files. In the File Name Box on the right side of the screen select the default name (database 1) provided by Access and then type the name you wanted. If you don't type the filename extension, Access adds it automatically, and then save as Microsoft Access 2007 Databases( b/c Access 2007 and Access 2010 have the same file extension) Creating a Table in Datasheet View Table contain all the data in a database and are fundamental objects for you work in Access. There are different ways to create a table in ACCESS, including entering the files ad records for a table directly in Datasheet View. Renaming the Default Primary Key Field Access provides the ID field as the default primary key for a new table you create in Datasheet view. (Change it: Rename Field, AutoNumber data type assigned to the default primary key field (ID)) A primary key is a field, or a collection of fields, whose values uniquely identify each record in a table. Changing the Data Type of the Default Key Field Each field in an Access table must be assigned a data type. The data type determines what field values you can enter for the field. The AutoNumber data type automatically inserts a unique number in this field for every record. Therefore, it can serve as the primary key for any table you create. Text data type, which allows field values containing letters, digits, and other characters, and which is appropriate for identifying numbers, such as contract numbers, that are never used in calculation. Note the Unique check box in the Field Validation group is checked because the field assumed the each value in the primary key must be unique. No two records in the primary key will be allowed to have the same value. (Field-Column, Record-Row) (Select a column: a column is selected when you click a field value, in which case the background color of the column heading changes (the default color is orange) and the insertion point appears in the field value. You can also click the column heading to select a column, in which case the background color of both the column heading and the field value changes (the default colors are gray and blue, respectively) Datasheet view shows a table’s content in rows (record) and columns (field). Each column is headed by a field name inside a field selector, and each row has a record selector to its left. Clicking a field sector or a record selector (row/Column heading) selects that entire column or row (respectively), which you then can manipulate. A field selector is also called a column selector, and a record selector is also called a row selector. Entering Records - The pencil symbol indicates that the record is being edited. - The star symbol identifies the second row as the next row available for a new record. - You can also press the Enter key instead of the Tab key to move from one field to another, and to the next row. - Current Record box displays the number of the current record as well as the total number of records in the table. Saving a Table The records you enter are immediately stored in the database as soon as you enter them; however, the table’s design-the field names and characteristics of the fields themselves, plus any layout change to the datasheet-are not saved until you save the table. The new added record does not display in primary key order until you close and reopen the table, or switch between values. Access sorted and displayed the records in order by the value in the Primary key. Opening a Table The table in a database are listed in the Navigation Pane. You open a table, or any Access Object (query, form, report), by double-clicking the object name in the Navigation pane. Session 1.2 the Create Tab Options Visual Overview summary: The forms group contains options for creating a form. Form is a database object you use to enter, edit, and view research in a database. The Queries group contains options for creating a query, which is a question you ask about the data stored in a database. In response to a query, Access displays the specific records and fields that answer your question. The Query Wizard button opens a dialog box with different types of wizards that guide you through the steps to create a query. One of these, the Simple Query Wizard, allows you to select records and fields quickly to display in the query results. The Form tool quickly creates a form containing all the fields in the table (or query) on which you’re basing the form. The Form Wizard guides you through the process of creating form. 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. The Report wizard guides you through the process of creating a report. The reports group contains options for creating a report, which is a formatted printout (or screen display) of the contents of one or more tables (or queries) in a database. The Create tab provides options for creating various database objects, including table, forms and reports. The options appear on the tab grouped by object table. Copying Records from another Access Database There are many other ways to enter records in a table, including copying and pasting records from a table into the same database or into a different database. To use this method, however, the table must have the same structure – that is, the tables must contain the same fields, with the same design, and characteristics, in the same order. (Click the datasheet selector to select all the records in the table, Detail Procedure on Txt pg 246) Navigating a Datasheet To view fields or records not currently visible on the screen, you can use the horizontal and vertical scroll bars shown in pg 246 to navigate the data. The navigation buttons provide another way to move vertically through the records. The Current Record box appears between the two sets of navigation buttons and displays the number of the current record as well as the total number of records in the table.(Navigation Buttons on Pg 247) You can make a field the current field by clicking anywhere within the column for that field; you can make a record the current record by clicking anywhere within the record. Creating a Simple Query A Query is question you ask about the data stored in a database. When you create a query, you tell Access which fields you need and what criteria Access should use to select the records that will answer your question. Then Access displays only the information you want, so you don’t have to navigate through the entire database for the information. You need to select fields from the Available Field box to include them in the query (Selected Fields)- (You can also double-click a field to move it from the Available Field box to the Selected Field box). More detail steps are on pg 248. Each simple Query Wizard dialog box contains buttons on the bottom that allow you to move to the previous dialog box (Back button), move to the next dialog box(Next button), or cancel the creation process(Cancel button) You can also finish creating the object(Finish button) and accept the wizard’s defaults for the remaining options. The query results are not stored in the database; however, the query design is stored as part of the database with the name you specified. You can re-create the query results at any time by opening the query again. Creating a Simple Form You use a form to enter, edit, and view records in a database. Although you can perform this same function with tables and queries, forms can present data in many customized and useful ways. (Form created by the Form tool-Figure 1-22 pg 251) The form displays one record at a time in the contract table (usually shows the field value for the first record), providing the layout view and allow you to focus on the values for one record. Access displays the field values for the first record in the table and selects the first field value by placing a border around the value. As indicated in the status bar, the form is displayed in layout view. In layout view, you can make design changes to the form while it is displaying data, so that you can see the effects of the changes you make immediately. If you want to move the data, instead of using the arrows buttons, you have to use the Next record, Last record, Previous record, First record to move it. (Detail pg 251). Tips: Saving Database Objects In general, it is best to save a database object-query, form, or report-only if you anticipate using the object frequently or if it is time consuming to create because all objects use storage space on your disk. Creating a Simple Report A Report is formatted printout (or screen display) of the contents of one or more tables or queries. (Ex. The Report tool creates a simple report showing every field in the Contract table and places it on a tab name “Contract” (Format on Pg 253 Important Figure 1-23) The report shows each field in a column, with the field value for each record in a row, similar to a datasheet. However, the report offers a more visually appealing format for the data, with the column headings in a different color, borders around each field value, a graphic of a report at the top left, and the current day, date, and time at the top right. Also notice the dotted horizontal and vertical lines on the top and right, respectively, these lines mark the edge of the page and show where text will print on the page. Printing a Report You can also use the Print dialog box to print over database objects, such as table, report and query datasheets. Most often, table and query datasheet are used for viewing and entering data, reports are used for printing the data in a database. Viewing Objects in the Navigation Pane Objects displayed in the Navigation Pane(figure- pg258) All Access objects, which lists all the database objects in the pane. Each object type (Tables, Queries, Forms and Reports) appears in its own group. Each database object has a unique icon to its left to indicate the type of object. This makes it easy for you to identify the objects and choose which one you want to open work with. The arrow on the All Access Objects bar displays a menu with options for various ways to group and display objects in the Navigation Pane. The Search box enables you to enter text for Access to find. Note that Access searches for objects only in the categories and group currently displayed in the Navigation pane. Managing a Database By managing your databases, you can ensure that they operate in the most efficient way, that the data they contain is secure, and that you can work with the data effectively. Some of the activities involved in database management include compacting and repairing a database and backing up and restoring a database. Compacting and Repairing a Database Whenever you open an Access database and work in it, the size of the database increases. Further, when you delete records or when you delete or replace database objects-such as queries, forms, and reports-the space that had been occupied on the disk by the deleted or replaced records or object does not automatically become available for other records or objects. To make the space available, you must compact the database. Compacting a database rearranges the data and objects in a database to decrease its file size, thereby making more space available on your disk and letting you open and close the data base more quickly. When you compact a database, Access repairs the database at the same time. In many cases, Access detects that a database is damaged when you try to open it and gives you the options to compact and repair it at that time. (For example, the data in your database might become damaged, or corrupted, if you exit the Access program suddenly by turning off your computer. If you think your database might be damaged because it is behaving unpredictably, you can use the Compact & Repair Database option to open it. Access also allows you to set an option to compact and repair a database file automatically every time you choose it. By default, the compact on Close option is turned off. ( Although nothing visible happens on the screen, Access compacts the database, making it smaller, and repairs it at the same time. Backing Up and Restoring a Database Backing a database is the process of making a 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. Access provides a default filename for the backup copy that consists of the same filename as the database you are backing up and an underscore character, plus the current date. This file naming system makes it easy for you to keep track of your database backups and when they were created. To restore a backup database file, you simply copy the backup form the drive on which it is stored to your hard drive… Tutorial 2: Building a Database and Defining Table Relationships Session 2.1 Table Window in Design View Design View allows you to define or modify a table structure or the properties of the fields in a table. You can use the Description property to enter an optional description for a field to explain its purpose or usage. A field’s Description property can be up to 225 characters long, and its value appears on the status bar when you view the table datasheet. The bottom portion of the Table window in Design view is called the Field Properties pane. Here, you select values for all other field properties, most of which are optional. The purpose or characteristics of the current property (Field Name, in this case) appear in this section of the field properties pane. You can display more complete Help information about the current property by pressing the F1 key. The default name for a new table you create in Design view is Table1. This name appears on the tab for the new table. The top portion of the Table window in Design view is called the Table Design grid. Here, you enter values for the Field Name, Data Type, and Description field properties. In the Field Name column, you enter the name for each new field in the table. When you first open a new Table window in Design view, Field Name is the current property. In the Data Type column, you select the appropriate data type for each new field in the table. The data type determines what field values you can enter for a field and what other properties the field will have. The default data type for a new field is Text. After you assign a data type to a field, the general tab displays additional field properties for that data type. Initially, most field properties are assigned default values. When defining the fields in a table, you can move from the Table Design grid to the Field Properties pane by pressing the F6 key. Guidelines for Designing Database When you design a database, you should follow these guidelines: 1. Identify all the fields needed to produce the require information 2. Organize each piece of data into its smallest useful part (For example, Oren could store each customer’s complete name in one field called Customer Name instead of using two fields called FirstName and LastName) 3. Group related fields into tables.(For example, Oren grouped the fields related to contracts into the Contract table) 4. Determine each table’s primary key (the primary key with two or more fields is called composite key. For example, a school grade table would use a combination of student number and course code to serve as the primary key) 5. Include a common field in related tables (You use the common filed to connect one table logically with another table. For example, Oren’s Contract and Customer tables include the CustomerID as a common. Recall when you include the primary key from one table as a field in a second table to form a relationship, the field is called a foreign key) 6. Avoid data redundancy (when you store the same data in more than one place, data redundancy occurs. With the exception of common fields to connect table, you should avoid data redundancy because it wastes storage space and can cause inconsistencies) 7. Determine the properties of each field. - You need to identify the properties or characteristics, of each field so that the DBMS knows how to store, display, and process the field values. These properties include the field’s name, maximum number of characters or digits, description, valid values, and other field characteristics. Guidelines for Setting Filed Properties When you select or enter a value for a property, you set the property. Access has rules for naming fields and objects, assigning data types, and setting other field properties. 1. Naming Fields and Objects You must name each field table, and other object in an Access database. Access then stores their items in the database, using the names you supply. A field name must be unique within a table, but it can be used again in another table-(foreign key) 2. Assigning Field Data Types - Each field must have a data type, which is either assigned automatically, by Access or specifically by the table designer. The data type determines what field values you can enter for the field and what other properties the field will have(common data type: figure 2-4 pg 269) Ex. AutoNumber, Yes/No, Hyperlink 3. Setting Field Size The Field Size Property defines a field value’s maximum storage size for Text, Number, and AutoNumber fields only. The other data types have no Field Size property because their storage size is either a fixed, predetermined amount or is determined automatically by the field value itself. 4. Setting the Caption Property for Fields The Caption property for a field specified how the field name is displayed in database objects, including table and query datasheets, forms and reports. If you don’t set the Caption property, Access displays the field name as the column heading or label for field. Insight: Setting the Caption Property V.S Naming Fields Although Access allows you to include spaces in field name, this practice is not recommended because the spaces causes problems when you try to perform more complex tasks with the data in your database. Setting the Caption property allows you to follow best field naming practices, such as not including spaces in field names, while still providing users with more readable field names in datasheet, forms and reports. Changing the Format of a Field in Datasheet view The formatting group on the Fields tab in Datasheet view allows you to modify some formatting for certain field types. When you format a field, you change the way data is display, but not the actual values stored in the table.(field values appear unchanged, but the format is different) In Access, values for Text fields are left-aligned, and values for Number, Date/Time, and currency fields are right-alighted Creating a Table in Design View Creating a table in Design view involves entering the field names and defining the properties for the fields, specifying a primary key for the table, and then saving the table structure. (Different with creating a Table in Datasheet view)-Figure 2-9 Txt Pg 273 Steps for creating a table in Design View (create tab – Table Design button) 1. Defining Fields: When you first create a table in Design View, the insertion point is located in the first row’s Field Name box, ready for you to begin defining the first field in the table. You e
More Less

Related notes for COMPSCI 1BA3

Log In


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.