Specific Objectives Content

1. explain the concept of a database; Definition of database: repository of information; collection of tables that are related to each other.

2. use terminology commonly associated with a database: Database terminology: table (relation), entity, tuple, attribute, primary key, secondary key, composite key, candidate key, alternate key, foreign key.

3. distinguish among terminology associated with files and databases: Row (tuple, record), column (attribute, field), key. Data types: alphanumeric; numeric; data; logical.

4. outline the advantages and limitations of databases: Comparison with files with regards to: Speed; Ad hoc queries; standardization; present multiple views of the same data.

5. create a database: Table structure with at least three data types and populate with at least twenty-five records. Use of wizard is prohibited.

6. modify a table structure: Adding new fields, deleting fields, changing field definitions.

7. sort a database: Arranging data in numeric, alphabetic; or alphanumeric format. Report generating facilities of the database package, including use of sorting, grouping statistical and summary features, for example, count, sum and average.

8. establish relationships: Use of joins, merge tables/files.

9. query a database using multiple search conditions: Using more than one criterion; use of select, calculated fields.

10. determine the results of a search on a database given multiple conditions: Two or more fields involving the use of relational and logical operators.

11. generate reports to the screen, printer and files: Report generating facilities of the database package, including use of sorting, grouping, statistical and summary features, for example, count, sum and average.

A database is a structured collection of interrelated data. In a relational database, data is stored in tables.

The difference between the terms “data” and “information”:

A piece of Data is a fact that can be represented as audio, video, text or number(s). By itself, it may not be relevant, but if it is processed and put in a certain context, it takes on meaning and becomes information. Information is the context, the meaning attached to the data. For an example, the sound for alarm is data. If you listened to it out of context, it would be just an unpleasant and penetrating sound. After it was assigned to the meaning "imminent danger", it became (well known) information.

Examples of large database use:

 plane ticket reservations

 governmental institutions databases

 bank accounts

 patient data

1.1 Database organization

What database tables contain and what table data apply to?

A Table is the most important database element. It is composed of rows and columns that contain information on a certain subject. For example, the table "Customers" contains the necessary information about customers.

A Record in a table is displayed as a line and contains information about a subject, e.g. relevant information about a customer. Fieldsare individual record characteristics which are in columns within a table. The type of data that can be entered in a field is specified as the data type (textual, logical, currency,numeric etc.), e.g. name, address, city, telephone, e-mail etc.

Besides the type, within the fields we can also determine:

 Field size

 Format – e.g. we can specify what currency indicator is added to the amount

 Default Value – if nothing is entered, the value that we enter here will be written in

the field

 Validation Rule – checks whether the entered information is correct, i.e. checks

whether the set criteria is met

Why should each table field contain only one data element, one type of information?

In Figure 1, Table records and fields, you can see an example of good table design, i.e. each field contains only one data element. Name is entered in one field, last name in another, street and number (address) in the third etc. A bad design example would be this:

the previous example, there are two data elements in one field (first and last name in the Name field) and three data elements in another (street number, city and zip code). The data is inflexible, making it very difficult to sort and create queries and reports.

Better design example in comparison to the previous table would be this:

Figure 1

With every data element in a separate field, it is easier to sort and create queries and reports. Connection between data types and field content Field content is a particular data type within a table. It can be text, number, currency, date and time, yes / no, etc.

To add a field, click the right mouse button on the table header labeled Click to Add. Another way to add a field is via the Design View, in the Data Typecolumn.

To add a field, click the right mouse button on the table header labeled Click to Add. Another way to add a field is via the Design View, in the Data Typecolumn.

Basic field propertiesTable fields have the appropriate properties, such as field size, formatting and default values. These properties can be changed in the Field Properties.

Field Size– for example, pre-set to 255 text characters.

Default Value– for example, for Yes/No data type, default value is 0.

Primary Key
	A Primary Keyis a field, which clearly identifies each table record. A very important part of designing a database is to determine the primary key for each table. You should take into account that it is unique for each record. Figure 9 shows the primary key as an ID field which is assigned as the AutoNumberdata type, which means that by entering each new record, the system itself generates a new unique ID (number). Indexes are automatically assigned to primary keys, which significantly accelerates the search and data retrieval. Primary keys are often used to create relationships between tables.
Indexes Data locations are stored in indexes. Table indexes are used as term indexes in books - when searching for information, its location is looked up via an index. We use them to speed up the search and data retrieval. For tables with a small number of records, using indexes is not required, because speed improvement when searching or sorting is negligible. However, if we have a lot of data, by setting the appropriate indexes on certain fields, we can expect a significant search and sort acceleration.
Connecting tables – relationships Often, a database consists of several tables. For example, an internet shop database could include, among other tables, Customers and Shipping tables. The mere fact that we can have multiple tables within a database suggests that the tables can be linked. The link between tables within a database is called a Relationship. Relationships (connections) between tables are made through primary key fields and on the principle of field equality. We connect tables to be able to make queries, reports and forms with data located in multiple tables. In Figure 10 we have connected a Customer ID field which is the primary key of the Customers table with the Customer ID field from the Shipping table.
To create a relationship between two or more tables, we need to have fields that have the same data type in the relevant tables (* fields need not have the same name!). In Figure 10 there are Customer ID fields, which are identical in the Customers table and in the Shipping table.
Relationship types: 1:M(One To Many) – one field in the relationship is the primary key of its table. This is the most common relationship type. 1:1(One to One) – both fields in the relationship are primary keys of their tables. This is the most uncommon relationship type. M:M(Many to Many) –in this relationship, a third table is created in which the primary key consists of two fields that are foreign keys from the linked tables.
Create a relationship between tables In order to create a relationship between tables, on the Database Toolstab, within the Relationships group, choose the Relationshipstool To display the tables you want to connect, choose the Show Tabletool on the Designtab in the Tools group.
The Show Tabledialogue box opens (Fig. 11.) in which you first select and then press the Addbutton to add the tables to be displayed within the Relationshipspane.
A relationship can also be created via the drag and drop method by following these steps: 1. position the cursor over the first table primary key (ID field to Customers table) 2. press and hold the left mouse button, pull the cursor over to the Customer ID field in the Shopping cart table and release the mouse button 3. Edit Relationshipsdialogue box opens 4. press the Createbutton to create a relationship
Why is it important to preserve the referential integrity between tables? The referential integrityensures the integrity of all database records. By its application, validity of the relationship between the tables is confirmed and accidental modification or deletion of the related data is prevented. In the Edit Relationshipsdialogue box, check the Enforce Referential Integrityoption. After that, if you try to delete a record from the Customers table (which is connected to the Shopping cart table), a Microsoft Access application warns that the record cannot be deleted, because the other table includes the related records
Delete a relationship between tables First, select the relationship, and then:  press the right mouse button and from the quick menu choose the Delete command  press the Delete key on the keyboard
Who creates, manages, fills out and maintains a database? Database designersare experts who create professional databases. Database administratorsare responsible for database maintenance and functionality. Among their tasks is the determination of data access permissions for a particular category of users. Database administrators ensure database recovery in case of failure or major errors. A Database userhandles data entry, data maintenance and information retrieval. A Database administratorhas the highest level of database user rights (access and manipulation). He/she grants or withholds data access rights. The database administrator is responsible for functioning, backup and database recovery in case of failure.

Date: 2015-12-17; view: 1269