Friday, 29 May 2015

Databases and Database Users

Databases are used in every part of day-to-day life.  Examples of common database use include: depositing or withdrawing money from a bank, making a travel reservation, accessing a library catalog, buying something from the internet via a web page, etc.

These are examples of traditional database applications, where data is stored either in textual or numeric format.  Less traditional database applications that are starting to become more popular include multimedia databases, which store pictures, video clips, and sounds, Geographic Information Systems (GIS) that store maps, satellite images and weather data.

Introduction and Definitions

 Database – a collection of related data.
 Data – facts that can be recorded and have implicit meaning

For example, you may record the names, telephone numbers and addresses of everyone you know, either using an address book, or storing on a hard drive and accessing it using applications such as MS Access and Excel.  Here you are using a database, a collection of related data with an implicit meaning.

Implicit properties of a database include:
  • A database represents some aspect of the real world, sometimes called the mini world, or Universe of Discourse.  Changes to the mini world are reflected in the database.
  • A database is a logically coherent collection of data with some inherent meaning.  A random assortment of data cannot be correctly referred to as a database.
  • A database is designed, built and populated with data for a specific purpose.  It has an intended group of users and some preconceived applications in which these users are interested.

So to summarize, a database has some source from which data is taken, a degree of interaction with the events in the real world, and an audience that is actively interested in the contents of a database.

The size and complexity of a database can vary.  The Bilkent library database may include hundreds of thousands of items, whereas a small company may maintain a database for only 30 employees.

A database is not necessarily computerized.  It can be generated and maintained manually, or it may be computerized.

 Database Terminology

Any firm that gathers extensive amounts of information to be retrieved at some point in the future depends on a file system (generally a database system) that allows for the access of specific information in a timely and cost effective manor. This information is usually stored in files, records and fields.

A file is a set of related information stored together.  It is a collection of information relative to a number of related entities.  For example, a bank needs to collect various types of information about customers, employees, savings accounts, demand accounts, etc.  Each one of these is a ‘subpackage’ of information, stored as a file.  They may have 4 files, and Employee File, a Customer File, a Savings Account file and a Demand Account file. 

A record is a collection of information about an entity.  If we look at the example from above, the Customer File, a record is a collection of information about a customer entity.  The record contains information required by the bank about each customer, and stored for each customer.  For example, information contained in a customer record may include at least first_name, last_name, id_number, birth_date.  Each of these pieces of information are taken together to form a customer record.  Because each record in the file contain the same number and types of fields, the records have the same type.

A field is a piece of information about an entity, contained in a record.  In the above example, the 4 pieces of information, first_name, last_name, id_number and birth_date are all fields.

Therefore we can use the following definitions, a record is a collection of related fields, and a file is a collection of related records, of the same type.

Insertion, Deletion, Update

The information being stored in files needs to be maintained.  This maintenance requires three operations, Insertion, Deletion and Update.


In order to satisfy many requests (such as “Find the Balance of account 123-456”), one must first retrieve the corresponding record from the file, and examine the contents of the balance field.  In order to retrieve the record, it must first be accessed.  To access the correct customer record, the first step is to locate the target file which contains the information to be retrieved.  In this case it may be the Accounts File.  Then from the Accounts file, the target record must be located among all the records in the file, using some access mechanism.  The target record will be one in which the account number field value is equal to 123-456.

In most cases there is a field in a record that identifies that particular record uniquely.  This field is referred to as the primary key of the record.   In some cases, there may not be a single field that uniquely identifies a record and serve as the primary key.  In these cases, a combination of two or more fields can serve as the primary key.  This is called a composite key.

Let’s assume there is a field for Customer Name.  In this case, because it is likely that two customers will have the same name, therefore duplicate values, Customer Name can’t be used as a primary key.  This is referred to as a secondary key.  A secondary key is a field which identifies a record, but that does not uniquely identify a record. 

In some cases there may be more than one field that uniquely identifies a record.  For example, if there was a field for customer ID number, it should be unique for all records.  In this case,  the fields that could also serve as the primary key are called candidate keys.  The database designer must choose one candidate key to serve as the primary key.

Database Management System (DBMS)

Definition: a collection of programs that enables users to create and maintain a database. 
A general-purpose software system that makes it possible to define, construct, manipulate and share databases among various users and applications.

§  Defining: specifying the data types, structures and constraints for the data to be stored in the database.
§  Constructing: the process of storing the data itself on some storage medium that is controlled by the DBMS
§  Manipulating: includes such functions as querying the database to retrieve specific data, updating the database to reflect changes in the mini world, and generating reports from the data.
§  Sharing: allows multiple users and programs to access the database concurrently.
Other functions include protecting the database and maintaining it over a long period of time. 
§  Protection: includes system protection against software/hardware malfunction and security protection against unauthorized/malicious access.
§  Maintaining: the DBMS should allow the database to evolve as requirements change over time.

Databases can be implemented by general purpose DMBSs, or special purpose custom software can be written to create and maintain a database.
Database System: database and DBMS software together.

Database System

Example: University Database

Used to maintain information concerning students, courses and grades in a university environment. 

The database has five files, each which stores data records of the same type.

§  The Student file stores data of each student
§  The Course file stores data on  each course,
§  The Section file stores data on each section of a course,
§  The Grade_Report stores information on the grades students receive in the various sections they have completed,
§  The Prerequisite file stores the prerequisite of each course.

DEFINE: To define the database, we must specify the structure of the records in the file by specifying the different types of data elemets to be stored in each record (elements of each file)

CONSTRUCT: To construct the database, we store data to represent each student, course, section, grade report and prerequisite as a record in the appropriate file.  The records in various files may be related.  Records for Smith in the Student file is related to two records in the Grade_Report file that specify Smith’s grades in tow sections.  Each record in the Prerequisite file relates two course records, on representing the course and the other representing the prerequisite.

Most medium to large databases include many types of records, and have many relationships among the records.

MANIPULATE: To manipulate involves querying and updating.  For example, to retrieve the list of all courses and Grades of Smith is a query.  An example of an update is to change the class of Smith to 2.  These informal queries and updates must first be translated to the query language used by the DBMS to be processes.

Characteristics of the Database Approach

A number of characteristics distinguish the database approach from the traditional approach of programming with files.  In traditional file processing, each user defines and implements the files needed for a specific software application as part of programming the application.

For example, one user, the grade reporting office, may keep a file on students and their grades.  Programs to print a transcript and to enter new grades into the file are implemented as part of the application.  A second user, the accounting office, may keep track of students fees and their payments.  Although both are interested in the data about students, each user maintains separate files and programs to manipulate the files because each requires some data not available from the other user’s files.

This redundancy in defining and storing data results in wasted storage space and in redundant efforts to maintain common, up-to-date data.

With the database approach, a single repository of data is maintained that is defined once and is then accesses by various users.  The main characteristics of the database approach versus file processing are as follows:

§  Self describing nature of the database system
§  Insulation between programs and data and data abstraction
§  Support of multiple views of the data
§  Sharing of data and multi-user transaction processing.

Self-Describing Nature of a Database System

§  A fundamental characteristic f the database approach is that the database system contains not only the database itself, but a complete definition or description of the database structure and constraints.
§  The definition is stored in the DBMS catalog which contains information such as the structure of each file, the type and storage format of each data item, and various constraints on the data.
§  The information stored in the catalog is called metadata, and it describes the structure of the primary database.
§  The catalog is used by the DBMS software and also by database users who need information about the database structure.
§  A general purpose database application is not written for a specific database application, so it must refer to the catalog to know the structure of the files in a specific database, such as the type and format of data it will access.
§  The DBMS software must work equally well with any number of database applications.
§  In traditional file processing, the data definition is part of the application programs themselves; hence they are constrained to work with only a specific database (i.e. a banking database, or a university database).  For example, an application written in C++ may have a strict or class declaration.
§  File processing software can access only specific databases; DBMS software can access diverse databases by extracting the database definitions from the catalog, and using these definitions.

Example: In the example database, the catalog will store the definitions of all files shown.  The database designer prior to creating the actual database specifies the definitions.  Whenever a request is made to access for example the name of a student record, the DBMS software refers to the catalog to determine the structure of the student file and the position and size of the name data item within a student record.  In traditional file processing, the size and file structure are coded within the application that accesses the data item.

Insulation between Programs and Data, and Data Abstraction

§  In traditional file processing, the structure of the data files is embedded in the application programs so any changes to the structure of a file may require changing all programs that access the file.

§  By contrast, DBMS access programs in most cases do not require such changes.  The structure of data files is stored in the catalog separately from the access programs.  This is called program-data independence.

§  For example, a file access program may be written so that it can only access student records with the exact structure specified by the starting position and length.  If we add another piece of data to the student record, such as BirthDate, the program must then be changed.

§  In a DBMS environment, we just need to change the description of a student record in the catalog to reflect the inclusion of the new data item BirthDate, and no programs need to be changed.  The next time a program refers to the catalog, the new structure of Student records will be accessed and used.

§  In some types of database systems, such as Object Oriented database systems users can define operations on data as part of the database.

§  An operation is specified in two parts.  The interface of an operation includes the operation name and the data types of its arguments.  The implementation of the operation is specified separately and can be changed without affecting the interface.

§  User application programs can operate on the data by invoking the operations through their names and arguments regardless of how the operations are implemented.  This can be called program-operation independence.

§  This is called data abstraction.  A DBMS provides users with the conceptual representation of data that does not include details of how the data is stored or how the operations are implemented.  A data model is a type of data abstraction that is used to provide the conceptual representation.

Example:  The internal implementation of a file may be defined by its record length (the number of characters in each record), and each data item may be specified by its starting byte within a record and its length in bytes.  But a typical database user is not concerned with the location of each data item within a record or its length. 

The DBMS hides details of the file storage organization from the user.

Support of Multiple Views of the Data

§  A database typically has many users, each of whom may require a different perspective or view of the database.
§  A view may be a subset of the database, or it may contain virtual data that is derived from the database files, but not explicitly stored.  (Give examples of these types of data)
§  Users generally do not need to know if the data is stored or derived.

Sharing of Data and Multi-User Transaction Processing

§  A multi-user DBMS must allow multiple users to access the database at the same time.
§  This is important if data for multiple applications is to be integrated (brought together) and maintained in a single database.
§  The DBMS must include concurrency control software to ensure that several users trying to update the same data do so in a controlled manor.
§  For example, when several reservation clerks try to assign a seat on a flight, the DBMS should ensure that each seat can be accesses by only one clerk.
§  An important concept in database applications is a transaction.  It is an executing program or process that includes on or more database accesses, such as reading or updating of database records. 
§  Each transaction is supposed to execute a logically correct database access if executed in its entirety without interference from other transactions.
§  The isolation property ensures that each transaction appears to execute in isolation from other transactions, even though hundreds of transactions may be executing concurrently.

Database Users

Database administrators – DBA is responsible for authorizing access to the database, for coordinating and monitoring its use, and acquiring software and hardware resources as needed.

Database designers – identify data to be stored in the database and choosing appropriate structures to represent and store the data.  Most of these functions are done before the database is implemented and populated with the data.  It is the responsibility of the database designers to communicate with all prospective users to understand their requirements and come up with a design that meets these requirements.  Database designers interact with all potential users and develop views of the database that meet the data and processing requirements of these groups.  The final database must support the requirements of all user groups.

End Users
§  Casual End Users – occasionally access, may need different information each time.  Use query language to specify requests.
§  Naïve or parametric end users – main job is to query and update the database using standard queries and updates.  These canned transactions have been carefully programmed and tested. Examples?
§  Sophisticated end users – engineers, scientists, analysts who implement applications to meet their requirements.
§  Stand alone users – maintain personal databases using ready made packages.

System Analysts and Programmers
§  Determine the end user requirements (especially naïve and parametric end users) and develop specifications for canned transactions that meet the requirements.

§  Application programmers implement the specifications as programs, then test and debug the programs.

Wednesday, 27 May 2015

Ms Office Applications Theory Examination 2015

You should be able to do (and answer questions) about the basic Windows functions, such as the following:

  1. How do you start up (Word, Excel, Access, and PowerPoint)?
  2. How do you close a Word document without closing Word?
  3. How do you close the (Word, Excel, Access, PowerPoint) program?
    1.               Name any three ways to select text: _________________      ________________ ________________
    2.               How would you select the entire document in MS Word? ________________________________
    3.               Describe how to copy the first paragraph of a document and paste it elsewhere. _____________________
    4.               How do you get some text or graphics onto the clipboard? __________________________________
    5.               How do you change the margins for you word document? ______________________________________
    6.               How do you change the left indent for a paragraph? ____________________________________
    7.               How do you change the right indent for a paragraph? ___________________________________
    8.               A hanging indent refers to formatting in which...
               a. Text in the first line is not indented as much as the remaining lines of the paragraph.
               b. All lines of a paragraph are indented more than the paragraph above it.
               c. All lines of a paragraph are aligned under the same tab stop.
               d. The text in the first line is indented more than the remaining lines of a paragraph.
    9.               How do you change the text alignment for a paragraph so that it's (centered, aligned on the left, aligned on the right, justified)? _______________________________________________________
10.               Which formatting feature is ON the Home tab?
a. Paragraph Alignment
b. Line Spacing
c. Increase Indent
d. All of the above
11.               What's the difference between Sans Serif fonts and Serif fonts?_______________________
12.               How do you turn bullets on and/or off for a list of items? ______________________________
13.               What button do you click to show the paragraph, tab markers, and other hidden characters in your document?
14.               To insert a picture into a Word document, you click on which tab? _________________
15.               What two tabs do you typically use when using MS Word in this class? ______________
16.               How do you add or remove office ribbon? _____________________________
17.               How would you replace all occurrences of "Cornerstone" with "CTI"? _______________
18.               How would you change the number of columns in an MS Word document? ___________________________
19.               How would you change the paper orientation to Landscape (rather than Portraits) in MS Word?
20.               How do you spell check a document? ______________________________________________
21.               How do you insert Clip Art? ___________________________
22.               How would you insert left tabs at the 4 and 6 inch marks on a page? ________________________________________________________
    1.               Although we use the term "spreadsheet" as a generic term, a document produced by Excel is actually called a ________________________________________________________________
    2.               Each Excel document consists of several _______________________ with tabs at the bottom of the document window.
    3.               The intersection of a column and a row is called a ____________________
    4.               The cell which is currently being accessed or used is called the _____________ cell.
    5.               You can see the name of the current cell in what part of the Excel Window? ___________________
    6.               Likewise, the contents of the active cell is shown in what box? ___________________________
    7.               How would you change to a currency format for numbers in a cell? ____________________________
    8.               What and where is the "Copy Handle" (or Cell Handle) in a cell? _____________________________
    9.               How do you use the fill handle to copy a formula to other adjacent cells?
10.               Which of the following is a valid Excel cell address: A10, 10A, 1A3 ________
11.               What are three kinds of data that you can put into an Excel cell? ___________________________
12.               How would you write a formula B3 times E1, making E1 an absolute cell reference? __________________________________________
13.               You've typed a title into cell A1. How do you use "Merge and Center" to center that title over columns A through E? __________________________________________
14.               Give an example of an Excel function: _____________________________
15.               What's the difference between a formula and a function? _____________________________
16.               How do you use Autofit? ________________________________
17.               What's the difference between an absolute cell reference and a relative cell reference?
18.               What kind of cell reference is $H$1 ? ________________________
19.               How do you select a range of cells? _____________________________________
20.               Tell one way to move a range of cells from the present location to another location?
21.               Where do you click to delete a row or column? _____________________________________
22.               How do you insert a new row or column? _____________________________________
23.               Do you have to select data before you make a chart? ________________________________
24.               How do you create a chart in Excel 2007? _____________________________________
25.               How would you print out some Excel data with its chart? ________________________________
26.               How would you print in landscape mode? __________________________________________
27.               How do you change the font face and/or size in a cell? ____________________________________
28.               Give two ways to change the width of a column. ________________________________________
29.               Write the formula for the average of cells A2:A20, without listing every cell:
30.               Write the formula, using the PMT function, for the monthly payment, where the Principle(amt of loan) is in cell B4, the yearly interest rate is in B5, and the term(in years) is in B6. Remember, you have to divide the interest rate by 12, and multiply the term by 12. =___________________________________________________

    1.               A database can consist of: (a) one or more tables (b) only one table (c) is the same as a table (d) none of those? ______________________________
    2.               Give an example of a database that you could create with Access. _______________________________
    3.               Which of the following could definitely not be an example of a database? (a) a dictionary, (b) a bookstore inventory (c) student information at UALR (d) an interoffice memo? __________________________
    4.               List four of the "Objects" that are available on the database window. __________________________
    5.               What's the difference between a "Database" and a "Table"? _________________________________
    6.               How can you tell the name of a Database?
    7.               How can you tell the name of a Table? __________________________________________
    8.               How would you list Database, Table, Record, and Field in terms of a hierarchy? __________________________________________
    9.               Looking at a database as a hierarchy, a table can be described as a set of related (a) fields (b) objects (c) records (d) queries? __________________________
10.               A record can be described as a set of related (a) fields (b) objects (c) queries (d) forms? __________________________
11.               How do you switch between database objects when you have a database window open? __________________________
12.               Looking at the Database window, what icon or Tab can you make to change the design of a table? __________________________
13.               Looking at the Table datasheet, what icon or Tab can you make to change the design of a table?
14.               What is the purpose of a database Form? __________________________
15.               What is the purpose of a database Report? __________________________
16.               How do you enter new data into a table? __________________________
17.               How do you edit the data in the table on the datasheet? __________________________
18.               How do you find a record with a particular value (in a table)? __________________________
19.               In a hypothetical "Address" table, how would you use a "Filter" to select all records with city=Nairobi? __________________________
20.               What's the difference between using the "Find" command and using a "Filter"? __________________________
21.               What's a difference between a "Filter" and a "Query"? __________________________
22.               When you're using the Form Wizard, what do the two arrows pointing to the right (>>) do? __________________________
23.               In Forms and Reports, what is a "Control"? __________________________
24.               In Forms and Reports, how do you change the size of a Control? __________________________
25.               When you create an Access database table, once you define a field you can't change its name (True or False)? __________________________
26.               When you created your report and form, what sequence of choices did you make to do it (meaning where do you click on the windows)? __________________________
27.               Which of the following can be saved for future use (a) Find (b) Filter (c) Query (d) all of the above)
28.               With which of the following can you select certain fields out of the record to display, rather than the whole record? (a) Find (b) Filter (c) Query (d) all of the above

    1.               What's the purpose of PowerPoint? __________________________
    2.               Which of the following does PowerPoint create (a) 35mm slides (b) on-screen presentations (c) transparencies or (d) all of those?
    3.               Name three of the different Views available to you in PowerPoint. __________________________
    4.               Which PowerPoint Views allow you to view slides one slide at a time? __________________________
    5.               Which PowerPoint view displays several slides on the screen? __________________________
    6.               How do you actually start the slide show (what choices)? __________________________
    7.               PowerPoint allows you to (a) use already created presentations, (b) use colorful slide design templates for a presentation you create, (c) both of those?
    8.               How do you open an existing presentation in PowerPoint? __________________________
    9.               How do you enter or change text on a slide of a PowerPoint presentation? __________________________
10.               How do you save a presentation to a flash disk? __________________________
11.               How do you save a presentation for use at another site that doesn't have PowerPoint? __________________________
12.               How do you get HELP in PowerPoint? __________________________
13.               When you're actually making a presentation, how do you advance to the next slide? __________________________
14.               When you're actually making a presentation, how do you go back to the previous slide? __________________________
15.               How do you print only one slide per page in PowerPoint? __________________________
16.               How do you print several slides per page in PowerPoint? __________________________