What Is Database
This document was uploaded by user and they confirmed that they have the permission to share it. If you are author or own the copyright of this book, please report to us by using this DMCA report form. Report DMCA
Overview
Download & View What Is Database as PDF for free.
More details
- Words: 6,762
- Pages: 26
What is a Database? Doc 5.91 Ver 1 Date July 2000
Author Vivien Hall Central Computing Services
Abstract This document provides an overview of some of the database systems and tools that are available to Birkbeck College students and staff. By studying this material you will not become an expert in using any particular system. However, you should have a better idea of what is available, what your future training needs might be, and what training material is available. You may wish to omit some of the exercises that are not relevant to you. For example if your work does not require you to compile bibliographies then you could omit Endnote. Students need to omit the 'room booking' example, as they will not be able to access staff databases.
Contents 1
Introduction ...................................................................................................................1
2
Overview of Database Design .......................................................................................2 2.1 Business Analysis.....................................................................................................2 2.2 Logical Design .........................................................................................................2 2.3 Physical Design ........................................................................................................3 2.4 Build .........................................................................................................................3 2.5 Entering Data in a database ......................................................................................3
3
Managing data with Microsoft Office 97.......................................................................4 3.1 Steps in creating a mail shot.....................................................................................4 3.2 Creating a Main Document ......................................................................................4 3.3 Opening an Microsoft Excel 97 data source ............................................................6 3.4 Editing a Main Document ........................................................................................7
4
Bibliographic Data - Endnote ........................................................................................9 4.1 What is Endnote? .....................................................................................................9 4.2 Inserting a list of references into Microsoft Word for Windows 97 ........................9
5
Library databases .........................................................................................................13 5.1 Training provided by the Library ...........................................................................13 5.2 Accessing CD-ROMs from CCS workstations ......................................................13
6
Web based Databases for Birkbeck Staff ....................................................................15 6.1 On line Room Booking System..............................................................................15
Birkbeck College Central Computing Services
i
Doc. 5.91 Ver1
What is a database?
6.2 CHAOS ..................................................................................................................16 7
Using a Microsoft Access 97 template ........................................................................17 7.1 Creating the database..............................................................................................17 7.2 Using the database..................................................................................................20 7.3 What does it all mean? ...........................................................................................23
8
Finding Out More ........................................................................................................24
About this Document Italics are used to introduce new terminology, for emphasis, and for proprietary names. For example copying is a term that is defined later in the text, A4 paper is emphasised. Windows NT is a proprietary name for the software which controls the computer. Words which the computer uses to prompt you, and titles of boxes are in bold type, e.g. the Locate Value box. Words that you type or choose are in bold type, e.g. File. Keys which you press are shown in small capitals e.g. press ALT. Press KEY1 + KEY2 means press both keys together e.g. press CTRL + Z Press KEY1, KEY2 means press each key consecutively e.g. press A, B. Tasks for you to try as you work through the notes are labelled Exercise, e.g. Exercise 1 Importing Database Objects and the steps that you do to perform the task are marked by round bullets (•). Hints and tips are indicated by
The Microsoft Windows logo in the margin indicates that the information in the box is not specific to Microsoft Access 97 but is relevant to all Windows NT programs.
ii
Birkbeck College Central Computing Services
What is a database?
Doc 5.91 Ver 1
1 Introduction Designing a successful large database is a skilled and specialist job. Organisations spend many staff hours designing and implementing databases. The aim of this workshop is to Provide you with an overview of what is involved in designing and implementing a database. Show you some alternative techniques for managing data, and to help you to decide what you need to learn about next in order to improve your working practices. By the end of the workshop you will not be an expert in using any particular system. However, you should have a better idea of what is available, what your future training needs might be, and what training material is available. Some data management options that we will look at today are: Using Microsoft Word for Windows 97 mail merge and Microsoft Excel 97 to create a form letter. Using an existing Endnote database to illustrate how Endnote can combine with Microsoft Word for Windows 97 to generate a list of bibliographic references in a document. Students and staff can access Library databases using a Web browser. Members of staff have access to various Web based databases on the Birkbeck Staff Intranet. Even if you do decide that you need your own Microsoft Access 97 database, you may find that Microsoft Access 97 has a built in template that you can use. Today we will see an example of a student/instructor database. Although it may not meet your requirements exactly it will give you something to think about!
Birkbeck College Central Computing Services
1
Doc. 5.91 Ver1
What is a database?
2 Overview of Database Design The lifecycle is usually broken down into 4 steps Business Analysis Logical Design Physical Design Build Databases are built like houses. That is, you first plan what you are going to build, how many rooms, how big, what is the layout of the rooms relative to each other. Then you start at the bottom, lay the foundations, build the walls and add the roof. You can then go on to add other components. In practice these steps will not follow neatly one after the other, there is usually constant cross checking and re-iteration between the various stages. Particularly with small projects, there is considerable overlap between the steps. On larger projects there will be a great deal of discussion and paperwork before the IT specialists are ready to build the system. 2.1
Business Analysis
An important first step in the business analysis is to formulate an Information Strategy, that is, to decide what information is of value to the organisation. The College has adopted an Information Strategy which is available on the Web at http://intra.bbk.ac.uk/comm/CILTS/99Dec/. Staff should discuss with their Head of School or Department or the Registry whether your information needs can be met. When considering your needs in relationship to the College Information Strategy you may be surprised that I referred you to your School or Department head rather than CCS. However, as you may have read in the press, the design of Information Systems is littered with a history of costly failures. Two main reasons for this are: End users rush into a technical solution before they have considered what information they need in order to support their business. As a first step you need to consider the information needs within your School or Department. Only when you have thought about what information you need will you be referred to the CCS Management Information System Team (MIS) for a technical solution. By contrast end users create a wish list of what they would like to do, without consideration of what is feasible with current technology. So it is important to involve IT professionals at an early stage. This is done at Birkbeck via various College committees such as the Computer Policy Committee (CPC). 2.2
Logical Design
The next step in the design process is to think about the structure of your data. A database is a simply structured collection of data, with the emphasis very much on structured. Everyone encounters databases in their daily lives, even if it is not in electronic form. Telephone books, diaries, and mailing order catalogues are all instances of databases. Most databases now are based on the data model known as the relational model. You use a relational database management system or RDBMS to build your database. Relational databases are built up of Tables. It is at this stage that you decide what tables are needed.
2
Birkbeck College Central Computing Services
What is a database?
Doc 5.91 Ver 1
You also need to think about other practical aspects such as: What amount of data is involved? Hundreds of records? Thousands? How resilient does it need to be? Could your work continue if all the data was lost? How secure does the data need to be - are there people who must not see the data? Are their any implications under the Data protection Act? (available on the staff intranet at http://intra.bbk.ac.uk/web/policy/dp.html) Having defined your data structures etc. you should cross check with the original business analysis. 2.3
Physical Design
Having thought about what data structures you need you can now think about how you will store the data. For example, if there is only a small amount of data then you might store it in a spreadsheet. At the other extreme you might be using a Web Browser to access a College Management Information System (MIS). For either of these extreme examples you do not need to worry about learning Microsoft Access 97. Training in Microsoft Excel 97 would be more appropriate if you decide that a spreadsheet is appropriate. CCS MIS team provide training and documentation on use of MIS databases. 2.4
Build
This is the point at which you create your information system, whether it is a Microsoft Access 97 database, a simple spreadsheet, or an Endnote bibliographic database. 2.5
Entering Data in a database
It is important to realise that the database is the place where you store data, not the data itself. In the following sections we will look at some examples of databases. In all these examples the database has been created for you ready to use. In some examples the data has also been entered and you will use the data to achieve an end result (e.g. create a bibliography, create a form letter). In other examples you will have opportunities to enter data for yourself (e.g. Microsoft Access 97). In the Library example you will use the database to retrieve and view information rather than entering it.
Birkbeck College Central Computing Services
3
Doc. 5.91 Ver1
What is a database?
3 Managing data with Microsoft Office 97 A common task that you might need to perform is to send multiple copies of a letter to different people. This is called a form letter. The technique that you use for creating a form letter is called Mail Merge. Mail Merge is used to extract data from a data source and merge it with a main document to generate form letters, labels, or envelopes. CCS Document 5.81 explains how to create a form letter and data source for letters and labels using Microsoft Word for Windows 97 as the data source. Today you will see how other programs such as Microsoft Excel 97 and Microsoft Access 97 can be used as your data source.
It is important to realise that data sources are interchangeable. For example, today we will see an example using data from a spreadsheet to create a form letter. However, I could just as easily have created an exercise where you got your form letter data from a database.
For the benefit of people who are new to Mail Merge full instructions are given. However if you have already used Mail Merge using a Microsoft Word for Windows 97 data source you may like to work out for yourself how to create a Mail Merge form letter that takes its data from the spreadsheet Q:\Samples\excel97\cats.xls. 3.1
Steps in creating a mail shot
You need to create or open a data source and main document for yourself. Then Microsoft Word for Windows 97 can use these objects to create a form letter for you. Points to note: You may create your own data source or you may use one that has been created for you such as a database or spreadsheet. Your data source will consist of data fields (referred to as Merge fields in the context of mail merge). Microsoft Word for Windows 97 defines a data field as a category of information in a data source. A data field corresponds to one column of information in the data source. The name of each data field is listed in the first row (header row) of the data source. PostalCode and LastName are examples of data field names. You also need to create a main document. This is a special sort of letter. Instead of typing the same letter over and over again with different names, addresses etc. you type the letter once, inserting place markers for the Merge fields. When you merge your main document and data source, Word for Windows 97 will substitute the merge field values for the place markers. Word for Windows 97 will also create envelopes or labels. You specify which fields from the data source you want to appear on an envelope or label, and where you want them to appear. 3.2
Creating a Main Document
The first task in a mail shot is to create the main document, that is the letter that is going to contain the data items such as names etc.
4
Birkbeck College Central Computing Services
What is a database?
Doc 5.91 Ver 1
Exercise 1 Create an Empty Form Letter • •
Log in to the workstation and start Microsoft Word for Windows 97. Select Mail Merge from the Tools menu. You will see the Mail Merge Helper dialogue box (Figure 1). Notice that all the buttons except the Create button are greyed out. Figure 1 Mail Merge Helper Dialogue Box
• • •
Click on the Create button. (The Mail Merge Helper tells you what to do in the box at the top of the Mail Merge Helper box, i.e. Begin by choosing the Create button) On the menu that drops down, select Form Letters. When the Mail Merge Helper asks you to choose between Active Window and New Document choose Active Window.
Some hints for when you are creating your own documents: When the Mail Merge Helper prompts you to choose the Active Window or a New Document: If your current document is empty, as it was in the previous exercise, choose Active Window. If you have already opened a form letter that you have created choose Active Window. If your current document is not an empty document or a form letter then choose New Document.
Birkbeck College Central Computing Services
5
Doc. 5.91 Ver1
3.3
What is a database?
Opening an Microsoft Excel 97 data source
When you have selected a document your Mail Merge Helper box should appear as in Figure 2, advising you to choose a data source. You will see that the Get Data button is no longer greyed out. Figure 2 Mail Merge Helper - Choose Data Source
Exercise 2 Open a Microsoft Excel 97 Data Source • • •
Click on the Get Data button On the menu that drops down, select Open Data Source In the Open Data Source dialogue box choose Files of Type MS Excel Worksheets (*.xls) (see Figure 3) Figure 3 File Type Menu for Data Source
6
Birkbeck College Central Computing Services
What is a database?
•
Doc 5.91 Ver 1
Click on the downward pointing arrow on the right of the box labelled Look In. (Figure 4) Click Here Figure 4 'Look In' box
•
In the menu that drops down, Click on the drive labelled Q: (Figure 5). The contents of Q: will be displayed in the pane beneath. Figure 5 Networked Drive Q:
• • • •
Double click on the folder labelled Samples. Again, the contents of the folder will be displayed. Double click on the folder labelled Excel97. This time you will see some spreadsheet documents, indicated by the icon shown left.. Double click on the spreadsheet called cats. You will be asked whether you want to open the entire spreadsheet or just the Cats sheet. Click on Cats to select it, then click on O.K. The document will now open.
The shorthand way of describing the document that you have opened is Q:\Samples\excel97\cats.xls
The spreadsheet that you are using is a read only spreadsheet on a shared network drive. Read-only shared drives allow you and your colleagues to access common data for tasks such as mail merge. You are using the Microsoft Excel 97 spreadsheet as a very simple database. 3.4
Editing a Main Document
If your main document is empty then Microsoft Word for Windows 97 will remind you, and prompt you to click on the Edit Main Document button. (Figure 6). Otherwise, if you were using an existing letter you would click on the Edit button in the Mail Merge Helper window. Figure 6 Edit Main Document
Birkbeck College Central Computing Services
7
Doc. 5.91 Ver1
What is a database?
Figure 7 Mail Merge Tool Bar
You will see that Word for Windows 97 has an extra toolbar (Figure 7) under the formatting toolbars. As with other Microsoft Office 97 toolbars you can find out what the buttons are for by using Hover Help. Exercise 3 Insert Merge Fields • Inside the Word Document window type the word Dear followed by a space. • Click on the Insert Merge Field button, and then select the Name field. Notice how the field will now appear in the letter as <> • Press the Enter key and type in the following text: You are invited to join us at midnight on 25th December 2000 on the Blasted Heath. There will be delicacies such as eye of toad and leg of newt for you to enjoy. RSVP The 3 Witches Dunsinane Wood • Use hover help to locate the Merge to New Document button on the Mail Merge toolbar. Click on this button to generate a Form Letter. When the Form letter appears on the screen, you will notice how the fields in the Word for Windows 97 document have been replaced by values from the data source. If you wanted to, you could now print the letters • When you have finished viewing the Form Letter document, close it without saving it. • Save your letter as N:\My Documents\witches.doc.
8
Birkbeck College Central Computing Services
What is a database?
Doc 5.91 Ver 1
4 Bibliographic Data - Endnote 4.1
What is Endnote?
A common requirement is to manage bibliographic data. You could develop your own bibliographic database using Microsoft Access 97. However this would be superfluous, as bibliographic database management systems are available. Endnote is the CCS supported database for managing bibliographic data, that is, lists of references. You can create your own database or download them from online databases. Endnote supports a wide range of styles for most of the common journals and publications. In order to use Endnote you need to have it installed on your computer. It is installed on all CCS managed machines in student workstations. FCE staff who use CCS managed machines should contact the FCE System Support Officer (Neil Hearn) on ext. 6310. Administrative staff should contact the System Administrator (Dorma Powell) on ext. 6595. Schools and Faculties who do not use CCS managed machines will need to purchase licences, as the suppliers do not offer a site licence option. Supplier details can be found at http://www.cherwell.com/ We will not create an Endnote database today, as that would occupy a whole workshop in itself. Instead we will use an existing Endnote database to illustrate how Endnote can combine with Microsoft Word for Windows 97 to generate a list of bibliographic references in Microsoft Word for Windows 97. 4.2
Inserting a list of references into Microsoft Word for Windows 97
CCS document 5.76 explains in detail how to create Endnote databases, how to insert citations into your documents, how to access Endnote libraries, and much more. Today we will use an existing database to see how easy it is to put a list of references into a Microsoft Word for Windows 97 document. Note that the example you will look at today uses only one of the many styles available to Endnote, so do not worry if it is not the style that you normally use.
In the following example you will open an Endnote Library held in a file Q:/Samples/Endnote/Course/sample.enl. Although the Endnote windows have a slightly different appearance to the Microsoft Excel 97 example in Exercise 2, the technique for opening a file is very similar. Before you follow the step by step instructions given in Exercise 4 for opening the Library try to open it for yourself. Exercise 4 Open an Endnote Library • • •
Open a new Microsoft Word for Windows 97 document. Type the word Bibliography and press the ENTER key a couple of times. In Microsoft Word for Windows 97 click on the Tools menu, then click on Go To Endnote. The Endnote program will start & you will see an Endnote window on top of your Microsoft Word for Windows 97 document, with a dialogue box asking you to open an Endnote library.
Birkbeck College Central Computing Services
9
Doc. 5.91 Ver1
•
What is a database?
You now need to open an Endnote Library. If Endnote displays a map of the desktop (Figure 8) then double click on My Computer to see all the drives on your computer. If Endnote displays the N:\My Documents folder then click on the go up a folder button (see margin) until you can see all the drives. Figure 8 Map of Desktop
• • • •
When you can see all the drives on your computer double click on the drive labelled Q: The contents of Q: will be displayed in the window. On the Q drive double click on the folder labelled Samples to display its contents. In the Samples folder double click on the folder labelled Endnote to display its contents. In the Endnote folder double click on the folder labelled Course to display its contents, then click on sample.enl. Your screen should now look like Figure 9 Figure 9 Endnote Library
10
Birkbeck College Central Computing Services
What is a database?
•
Doc 5.91 Ver 1
Click on the Open button to open the Endnote database. As this is a shared example you will get a message warning you that the database is locked. Click on O.K to accept the warning. Your database should look like Figure 10 Figure 10 An Endnote database
The Endnote Library is used to store all your references. Usually you will only want to copy selected ones into your document. When you have selected the ones that you want Endnote will format them for you and store them on the clipboard1 for you to paste into your Microsoft Word for Windows 97 document. Exercise 5 Create a List of References • •
Click on one of the references in the list to highlight it. Hold down the CTRL key and click on a few more (Figure 11) Figure 11 Selected References
1
The clipboard is a special part of the computer memory that can be used as temporary storage for text and graphics that you copy and paste between documents and/or programs.
Birkbeck College Central Computing Services
11
Doc. 5.91 Ver1
• • •
What is a database?
On the Endnote Edit menu click on Copy Formatted. Endnote will now format the selected items and store them on the clipboard. Make Microsoft Word for Windows 97 the active program by clicking on the Microsoft Word for Windows 97 icon (see left) which is on the taskbar at the bottom of your screen Position the cursor at the end of your Microsoft Word for Windows 97 document and click on the paste button. A formatted bibliography looking something like Figure 12 will appear in your document. Figure 12 Endnote Bibliography
• • •
12
You can change the font to fit in with the rest of the document if you wish. Save your document as N:\My Documents\Mybiblio.doc Close Microsoft Word for Windows 97 and Endnote.
Birkbeck College Central Computing Services
What is a database?
Doc 5.91 Ver 1
5 Library databases 5.1
Training provided by the Library
Generally students are given training in how to use the Library effectively as part of an induction programme at the beginning of their course. Subject librarians will contact School or departmental library representatives and course directors about induction arrangements during the summer. More advanced sessions can be arranged for students later in their course of study. The nature, content, and length of these sessions can be negotiated with subject librarians. Possible sessions include:
How to use the computer catalogue How to use electronic journals and databases Subject resources on the Internet How to do a literature search
You may need authorisation to access certain information. For databases that belong to Birkbeck College you will need a Birkbeck CCS username and password. These may be distributed by Schools or administrative departments or obtained from CCS Help Desk on production of Registry student card or staff ID card. For databases outside Birkbeck you may need an ATHENS username and password. These are available from the Library. For a full list of all Library databases and method of access see http://www.bbk.ac.uk/lib/cdrom.html For our example today we will look at some of the databases held on Library CDROMs 5.2
Accessing CD-ROMs from CCS workstations
Today we will look at how to access the Library CD-ROM services from CCS workstations. To make it easier for students to access the CD-ROM service an icon has been added to the desk top on student workstations. If you are not using a workstation then you can still access them by starting your Web browser and entering the URL http://cdrom1.bbk.ac.uk/Iris/ Exercise 6 Using the Birkbeck CD-ROM Database • •
Double click on the CD-ROM icon on the desktop (see left) Click on the Start Button (see left), which will take you to a list of CD-ROM's.
Birkbeck College Central Computing Services
13
Doc. 5.91 Ver1
What is a database?
CCS managed workstations have all the necessary software installed to allow you to view the CD-ROM databases. If you are viewing from your own PC or from a School or Faculty that has its own PC's then you may get a message that you need an ICA Plugin (Figure 13). This is a special program that allows you to view the CDROM database. Students and staff using home PC's can download it for themselves. Staff should contact the IT support person in your School or Faculty. Figure 13 Needs ICA Plugin
14
Birkbeck College Central Computing Services
What is a database?
Doc 5.91 Ver 1
6 Web based Databases for Birkbeck Staff One of the features of a database is that it can provide end users with forms that they can fill in. These forms pass information to the database and the database returns results, for example a screen display of information or a report. Database designers are increasingly making use of the World Wide Web to implement their databases. Two examples that can be used by College staff are the On-Line Room Booking System and the Corporate Higher Education Administrative On-Line Support. Staff should be able to access both these systems via their Web browser using their CCS username and password. 6.1
On line Room Booking System
Staff can book rooms by filling in Web based forms. Here is an example of a booking request. NB - this is not an exercise for you to try yourself. The database that is shown here is a live database so please do not make any entries unless you genuinely need to book a room. You can reach the room booking database either by going via the Administrative Departments page or directly at http://staff.bbk.ac.uk/ef/roomrequest.html First you will be asked to enter a staff username and pass word (Figure 14). This will normally be the username and password that you use for reading email. If there is an authorisation problem a screen will appear telling you what to do. Figure 14 Username/Password Prompt
When you have entered your username and password you will see a web based Room Booking Request Form. Click in the first box so that the cursor appears as a blinking light and type your name. You can move between boxes either by pressing the TAB key to go forward (shift + TAB to go back) or clicking in a box to select it. When you have completed the form click on the button labelled Submit Room Request. (Figure 15) Figure 15 Room Request Buttons
Birkbeck College Central Computing Services
15
Doc. 5.91 Ver1
What is a database?
If you need to clear the form click on the button labelled Clear Form 6.2
CHAOS
The Planning Office has implemented a management information system designed for administrative support. The Corporate Higher Education Administrative On-line Support system, or the CHAOS system for short, is an online database containing information about the student population at Birkbeck. You can query the database to produce on-line reports and graphical analyses that:
Monitor enrolment numbers by course / school / faculty. Monitor target intake numbers by course. Calculate forecast student numbers. Calculate FTE student numbers. Calculate forecast tuition fee income.
The database is available to all staff with a CCS username and password on the staff Intranet at http://staff.bbk.ac.uk/po/chaos/chaos.html, or go to the Planning Office web page and follow the CHAOS link. If you have any queries or comments please ring David McGhie on extension 6276 or Mike de Halpert on extension 6311.
16
Birkbeck College Central Computing Services
What is a database?
Doc 5.91 Ver 1
7 Using a Microsoft Access 97 template As we discussed in section 2, designing a database is not an exercise to be undertaken lightly. For example, the workshop material for CCS workshops is in 3 parts (Documents 5.69, 5.70, and 5.82) and workshops normally occupy 9 hours of class time. However, we can take a look at an example very easily because Microsoft Access 97 comes with tools that can create commonly used databases for you. We will use a template to create a Microsoft Access 97 database. A template is a document or file having a pre-set format, used as a starting point for a particular application so that the format does not have to be recreated each time it is used. You may be familiar with using a template in Microsoft Word for Windows 97 or Microsoft Excel 97. Microsoft Access 97 comes with some quite sophisticated templates, as you will see. A database consists of various components (known as objects in Microsoft Access 97 jargon). For example Table Objects are used to store the data. Form Objects are used to enter data into tables Report Objects are used to display printed reports about the data in the tables. 7.1
Creating the database
Wizards are special programs that Microsoft Access 97 can run to help you create objects in the database, or even whole databases. In the next exercise you will see how to create a database using a wizard. In section 2.5 we saw that the database is not the same as the data in the database. In the students and classes example you will ask the wizard to add some sample data so that you can experiment. However, if you wanted to replace the imaginary students and classes with information about your own students and classes then you could do this. We will see an example of how easy it is to add a new class and instructor to the data. Putting data into a database is not the tricky bit! The trick is to get the database design right!
Birkbeck College Central Computing Services
17
Doc. 5.91 Ver1
What is a database?
Exercise 7 Create a database using a Wizard •
From Start, Programs start Microsoft Access 97 (click on the Office Assistant close button if necessary to get rid of it) In the Create a new database window (Figure 16) make sure that Database Wizard is selected and click on O.K.
•
Figure 16 Create a database using a wizard
Select Database Wizard
•
In the New Database Window (Figure 17) make sure the database tab is selected and click on students and classes.mdz (scroll down the window if you cannot see it). Figure 17 New Database
18
Birkbeck College Central Computing Services
What is a database?
• •
Doc 5.91 Ver 1
Click on O.K In the File New Database window (Figure 18) save your database as N:\My Documents\bbkstudents.mdb Figure 18 File Database
• • • • • • •
Click on Create In the first database wizard screen click on Next> In the second screen make sure that Yes Include sample data is checked (i.e. there is a ✔ next to it). If ther eis no check mark then click in the box and one will appear. Click on Next> In the third screen choose a style for screen displays and click on Next> In the fourth screen choose a style for printed reports and click on next. In the fifth screen change the title to Birkbeck Students and Classes and click on next> In the final screen click on Finish
Wait a few minutes while Microsoft Access 97 creates the database.
Birkbeck College Central Computing Services
19
Doc. 5.91 Ver1
7.2
What is a database?
Using the database
The way that you use a database will, of course, depend on how it has been designed. RDBMS's such as Microsoft Access 97 offer the designer considerable choice both in the appearance of the database, and the way a database is used. Note that in the following discussion these are not exercises for you to do yourself. They are general guidelines about working with this database. There are some exercises for you to try later when you have read the guidelines. First we will look at some common techniques that are used in this database. The switchboard is a commonly used way of selecting functions that you wish to perform.. To use any of the items on the switchboard menu (Figure 19) click on the button next to one the labels, e.g. Enter/View Classes. This starts a form that will allow you to enter data into the database. Figure 19 Switchboard Example
20
Birkbeck College Central Computing Services
What is a database?
Doc 5.91 Ver 1
To move between fields on a form either click in a field or use the tab key to cycle through the fields (shift + tab to move backwards) To exit from a form click on the X in the top right corner of the form (Figure 20). Click here to close form
Figure 20 Form Example
Enter data in the boxes
Some boxes use a drop down menu (known as a pick list in Microsoft Access 97 jargon). Click the box. If a drop down arrow appears this means that there is a menu. Click on the drop down arrow. When a menu drops down click on an item to select it. (Figure 21) Figure 21 Pick List Example Click on a pick list item to select it
Birkbeck College Central Computing Services
Click on drop down arrow to get picklist
21
Doc. 5.91 Ver1
What is a database?
Some forms use subforms, that is a form within a form. For example, the classes form allows you to add students to a class. Click in a blank line in the subform, then click the drop down arrow to get a pick list. Figure 22 Sub Form example
Suppose we want to add a new class called 'What is a database' It is run by CCS department. This department is not in the existing database so we will need to enter its details. The instructor is Vivien Hall. Again, she is not yet in the database so we will need to enter her details. The main switchboard offers you the option to add a class so you might think you can start there. However, if you look at the form classes you will see that a class has a department and an instructor. So you will need to enter data about the instructor and department before you can enter data about classes.
If you are already familiar with using databases then you may want to skip these exercises. Exercise 8 Enter a Department • • • • •
22
Click on the button next to Enter and View Other Information. The Forms Switchboard appears. Click on the button next to Enter and View Departments. A Departments Form appears. Click on the New Record button (see left) to create a new record Enter data for department CCS, with Department # (number) as 99 and Dr. J.S. Gill as the Department Chair. Close the form
Birkbeck College Central Computing Services
What is a database?
Doc 5.91 Ver 1
Exercise 9 Enter an Instructor • •
Use the Enter and View Instructors button on the Departments Form to open the Instructors Form and enter data about a new instructor Vivien Hall (leave the phone number blank for now, we will discuss the reason why later) Close the form.
Exercise 10 Enter Class Details • • • • • • • 7.3
Click on the button next to Return to Main Switchboard to leave the Forms Switchboard. On the Main Switchboard click on Enter/View Classes to open the Classes form. Enter a new class called What is a database? Choose CCS from the departments pick list Choose Vivien Hall from the instructors pick list Add two or three students to the class. This is all the data that you need to enter for now, so close the form. What does it all mean?
Although the demonstration that you have just seen does not teach you about designing a database, it introduces various concepts that an end user of a database will find useful. You, as an end user, did not need to worry about the internal structure of the database as all the internal data structures (tables, fields) were created for you. However, if you prefer to use a more familiar terminology, e.g. lecturers instead of instructors, Schools instead of departments it is easy to change the names that are displayed. Doc 5.69 explains how to change the names of fields in tables. The designers provided forms that told you what sort of data you could enter e.g. data about classes, instructors, and departments. The CCS training material provides plenty of examples of creating forms. You used buttons on the main switchboard to trigger events. Doc 5.82 explains how to build your own switchboard. You were offered menus (picklists) so that you could only choose values that were valid. For example, you could only have a class run by an instructor who was known to the database. Again the CCS training material explains how to create your own picklists I advised you to omit the telephone numbers because these fields use a mask, that ensures that numbers are in the correct format. Unfortunately, the default masks are in USA format, so UK phone numbers will not be accepted. However, this is something that is very simple to correct, and is covered in CCS Document 5.69.
Birkbeck College Central Computing Services
23
Doc. 5.91 Ver1
What is a database?
8 Finding Out More 1. CCS Docs 5.69 Building Applications with Microsoft Access 97 - Part 1 Introduction to Microsoft Access 97' 2. CCS Docs 5.70 Building Applications with Microsoft Access 97 - Part 2 More Microsoft Access 97' 3. CCS Docs 5.82 Building Applications with Microsoft Access 97 - Part 3 Designing the User Interface' 1. Microsoft Access 97 videos are available for short term loan from the Library 2. Lorna Weatherill and Vivienne Hemingway (1994), Using and Designing Databases for Academic Work: A Practical Guide, ITTI. Available for reference at CCS Help Desk. 3. A Student’s Guide to Microsoft Access 97 may be purchased from the Student's Union shop (price approximately £7) 4. Peter Domanski & Philip Irvine, A Practical Guide to Relational Database Design, Domanski-Irvine Book Company, ISBN 9526043 02. See web page http://www.personal.u-net.com/~dibookco/Book1.htm for details. 5. S.Coles, J.Rowley, Access 97 - Basic Skills, Letts 1997, ISBN 1 85805 299 8 Self training material available from Dillons (£8.95 in February 1999) 6. S.Coles, J.Rowley, Access 97 - Further Skills, Letts 1997, ISBN 1 85805 300 5 Further self training material available from Dillons (£8.95 in February 1999) 7. http://www.woodyswatch.com/ - subscribe to a free email magazine containing hints and tips on Microsoft Office 97 applications. 8. http://www.freeskills.com/ - free internet based training 9. http://www.learninglink.bbk.ac.uk/learnlnk/site.htm - subscription courses on the Internet.
24
Birkbeck College Central Computing Services
Author Vivien Hall Central Computing Services
Abstract This document provides an overview of some of the database systems and tools that are available to Birkbeck College students and staff. By studying this material you will not become an expert in using any particular system. However, you should have a better idea of what is available, what your future training needs might be, and what training material is available. You may wish to omit some of the exercises that are not relevant to you. For example if your work does not require you to compile bibliographies then you could omit Endnote. Students need to omit the 'room booking' example, as they will not be able to access staff databases.
Contents 1
Introduction ...................................................................................................................1
2
Overview of Database Design .......................................................................................2 2.1 Business Analysis.....................................................................................................2 2.2 Logical Design .........................................................................................................2 2.3 Physical Design ........................................................................................................3 2.4 Build .........................................................................................................................3 2.5 Entering Data in a database ......................................................................................3
3
Managing data with Microsoft Office 97.......................................................................4 3.1 Steps in creating a mail shot.....................................................................................4 3.2 Creating a Main Document ......................................................................................4 3.3 Opening an Microsoft Excel 97 data source ............................................................6 3.4 Editing a Main Document ........................................................................................7
4
Bibliographic Data - Endnote ........................................................................................9 4.1 What is Endnote? .....................................................................................................9 4.2 Inserting a list of references into Microsoft Word for Windows 97 ........................9
5
Library databases .........................................................................................................13 5.1 Training provided by the Library ...........................................................................13 5.2 Accessing CD-ROMs from CCS workstations ......................................................13
6
Web based Databases for Birkbeck Staff ....................................................................15 6.1 On line Room Booking System..............................................................................15
Birkbeck College Central Computing Services
i
Doc. 5.91 Ver1
What is a database?
6.2 CHAOS ..................................................................................................................16 7
Using a Microsoft Access 97 template ........................................................................17 7.1 Creating the database..............................................................................................17 7.2 Using the database..................................................................................................20 7.3 What does it all mean? ...........................................................................................23
8
Finding Out More ........................................................................................................24
About this Document Italics are used to introduce new terminology, for emphasis, and for proprietary names. For example copying is a term that is defined later in the text, A4 paper is emphasised. Windows NT is a proprietary name for the software which controls the computer. Words which the computer uses to prompt you, and titles of boxes are in bold type, e.g. the Locate Value box. Words that you type or choose are in bold type, e.g. File. Keys which you press are shown in small capitals e.g. press ALT. Press KEY1 + KEY2 means press both keys together e.g. press CTRL + Z Press KEY1, KEY2 means press each key consecutively e.g. press A, B. Tasks for you to try as you work through the notes are labelled Exercise, e.g. Exercise 1 Importing Database Objects and the steps that you do to perform the task are marked by round bullets (•). Hints and tips are indicated by
The Microsoft Windows logo in the margin indicates that the information in the box is not specific to Microsoft Access 97 but is relevant to all Windows NT programs.
ii
Birkbeck College Central Computing Services
What is a database?
Doc 5.91 Ver 1
1 Introduction Designing a successful large database is a skilled and specialist job. Organisations spend many staff hours designing and implementing databases. The aim of this workshop is to Provide you with an overview of what is involved in designing and implementing a database. Show you some alternative techniques for managing data, and to help you to decide what you need to learn about next in order to improve your working practices. By the end of the workshop you will not be an expert in using any particular system. However, you should have a better idea of what is available, what your future training needs might be, and what training material is available. Some data management options that we will look at today are: Using Microsoft Word for Windows 97 mail merge and Microsoft Excel 97 to create a form letter. Using an existing Endnote database to illustrate how Endnote can combine with Microsoft Word for Windows 97 to generate a list of bibliographic references in a document. Students and staff can access Library databases using a Web browser. Members of staff have access to various Web based databases on the Birkbeck Staff Intranet. Even if you do decide that you need your own Microsoft Access 97 database, you may find that Microsoft Access 97 has a built in template that you can use. Today we will see an example of a student/instructor database. Although it may not meet your requirements exactly it will give you something to think about!
Birkbeck College Central Computing Services
1
Doc. 5.91 Ver1
What is a database?
2 Overview of Database Design The lifecycle is usually broken down into 4 steps Business Analysis Logical Design Physical Design Build Databases are built like houses. That is, you first plan what you are going to build, how many rooms, how big, what is the layout of the rooms relative to each other. Then you start at the bottom, lay the foundations, build the walls and add the roof. You can then go on to add other components. In practice these steps will not follow neatly one after the other, there is usually constant cross checking and re-iteration between the various stages. Particularly with small projects, there is considerable overlap between the steps. On larger projects there will be a great deal of discussion and paperwork before the IT specialists are ready to build the system. 2.1
Business Analysis
An important first step in the business analysis is to formulate an Information Strategy, that is, to decide what information is of value to the organisation. The College has adopted an Information Strategy which is available on the Web at http://intra.bbk.ac.uk/comm/CILTS/99Dec/. Staff should discuss with their Head of School or Department or the Registry whether your information needs can be met. When considering your needs in relationship to the College Information Strategy you may be surprised that I referred you to your School or Department head rather than CCS. However, as you may have read in the press, the design of Information Systems is littered with a history of costly failures. Two main reasons for this are: End users rush into a technical solution before they have considered what information they need in order to support their business. As a first step you need to consider the information needs within your School or Department. Only when you have thought about what information you need will you be referred to the CCS Management Information System Team (MIS) for a technical solution. By contrast end users create a wish list of what they would like to do, without consideration of what is feasible with current technology. So it is important to involve IT professionals at an early stage. This is done at Birkbeck via various College committees such as the Computer Policy Committee (CPC). 2.2
Logical Design
The next step in the design process is to think about the structure of your data. A database is a simply structured collection of data, with the emphasis very much on structured. Everyone encounters databases in their daily lives, even if it is not in electronic form. Telephone books, diaries, and mailing order catalogues are all instances of databases. Most databases now are based on the data model known as the relational model. You use a relational database management system or RDBMS to build your database. Relational databases are built up of Tables. It is at this stage that you decide what tables are needed.
2
Birkbeck College Central Computing Services
What is a database?
Doc 5.91 Ver 1
You also need to think about other practical aspects such as: What amount of data is involved? Hundreds of records? Thousands? How resilient does it need to be? Could your work continue if all the data was lost? How secure does the data need to be - are there people who must not see the data? Are their any implications under the Data protection Act? (available on the staff intranet at http://intra.bbk.ac.uk/web/policy/dp.html) Having defined your data structures etc. you should cross check with the original business analysis. 2.3
Physical Design
Having thought about what data structures you need you can now think about how you will store the data. For example, if there is only a small amount of data then you might store it in a spreadsheet. At the other extreme you might be using a Web Browser to access a College Management Information System (MIS). For either of these extreme examples you do not need to worry about learning Microsoft Access 97. Training in Microsoft Excel 97 would be more appropriate if you decide that a spreadsheet is appropriate. CCS MIS team provide training and documentation on use of MIS databases. 2.4
Build
This is the point at which you create your information system, whether it is a Microsoft Access 97 database, a simple spreadsheet, or an Endnote bibliographic database. 2.5
Entering Data in a database
It is important to realise that the database is the place where you store data, not the data itself. In the following sections we will look at some examples of databases. In all these examples the database has been created for you ready to use. In some examples the data has also been entered and you will use the data to achieve an end result (e.g. create a bibliography, create a form letter). In other examples you will have opportunities to enter data for yourself (e.g. Microsoft Access 97). In the Library example you will use the database to retrieve and view information rather than entering it.
Birkbeck College Central Computing Services
3
Doc. 5.91 Ver1
What is a database?
3 Managing data with Microsoft Office 97 A common task that you might need to perform is to send multiple copies of a letter to different people. This is called a form letter. The technique that you use for creating a form letter is called Mail Merge. Mail Merge is used to extract data from a data source and merge it with a main document to generate form letters, labels, or envelopes. CCS Document 5.81 explains how to create a form letter and data source for letters and labels using Microsoft Word for Windows 97 as the data source. Today you will see how other programs such as Microsoft Excel 97 and Microsoft Access 97 can be used as your data source.
It is important to realise that data sources are interchangeable. For example, today we will see an example using data from a spreadsheet to create a form letter. However, I could just as easily have created an exercise where you got your form letter data from a database.
For the benefit of people who are new to Mail Merge full instructions are given. However if you have already used Mail Merge using a Microsoft Word for Windows 97 data source you may like to work out for yourself how to create a Mail Merge form letter that takes its data from the spreadsheet Q:\Samples\excel97\cats.xls. 3.1
Steps in creating a mail shot
You need to create or open a data source and main document for yourself. Then Microsoft Word for Windows 97 can use these objects to create a form letter for you. Points to note: You may create your own data source or you may use one that has been created for you such as a database or spreadsheet. Your data source will consist of data fields (referred to as Merge fields in the context of mail merge). Microsoft Word for Windows 97 defines a data field as a category of information in a data source. A data field corresponds to one column of information in the data source. The name of each data field is listed in the first row (header row) of the data source. PostalCode and LastName are examples of data field names. You also need to create a main document. This is a special sort of letter. Instead of typing the same letter over and over again with different names, addresses etc. you type the letter once, inserting place markers for the Merge fields. When you merge your main document and data source, Word for Windows 97 will substitute the merge field values for the place markers. Word for Windows 97 will also create envelopes or labels. You specify which fields from the data source you want to appear on an envelope or label, and where you want them to appear. 3.2
Creating a Main Document
The first task in a mail shot is to create the main document, that is the letter that is going to contain the data items such as names etc.
4
Birkbeck College Central Computing Services
What is a database?
Doc 5.91 Ver 1
Exercise 1 Create an Empty Form Letter • •
Log in to the workstation and start Microsoft Word for Windows 97. Select Mail Merge from the Tools menu. You will see the Mail Merge Helper dialogue box (Figure 1). Notice that all the buttons except the Create button are greyed out. Figure 1 Mail Merge Helper Dialogue Box
• • •
Click on the Create button. (The Mail Merge Helper tells you what to do in the box at the top of the Mail Merge Helper box, i.e. Begin by choosing the Create button) On the menu that drops down, select Form Letters. When the Mail Merge Helper asks you to choose between Active Window and New Document choose Active Window.
Some hints for when you are creating your own documents: When the Mail Merge Helper prompts you to choose the Active Window or a New Document: If your current document is empty, as it was in the previous exercise, choose Active Window. If you have already opened a form letter that you have created choose Active Window. If your current document is not an empty document or a form letter then choose New Document.
Birkbeck College Central Computing Services
5
Doc. 5.91 Ver1
3.3
What is a database?
Opening an Microsoft Excel 97 data source
When you have selected a document your Mail Merge Helper box should appear as in Figure 2, advising you to choose a data source. You will see that the Get Data button is no longer greyed out. Figure 2 Mail Merge Helper - Choose Data Source
Exercise 2 Open a Microsoft Excel 97 Data Source • • •
Click on the Get Data button On the menu that drops down, select Open Data Source In the Open Data Source dialogue box choose Files of Type MS Excel Worksheets (*.xls) (see Figure 3) Figure 3 File Type Menu for Data Source
6
Birkbeck College Central Computing Services
What is a database?
•
Doc 5.91 Ver 1
Click on the downward pointing arrow on the right of the box labelled Look In. (Figure 4) Click Here Figure 4 'Look In' box
•
In the menu that drops down, Click on the drive labelled Q: (Figure 5). The contents of Q: will be displayed in the pane beneath. Figure 5 Networked Drive Q:
• • • •
Double click on the folder labelled Samples. Again, the contents of the folder will be displayed. Double click on the folder labelled Excel97. This time you will see some spreadsheet documents, indicated by the icon shown left.. Double click on the spreadsheet called cats. You will be asked whether you want to open the entire spreadsheet or just the Cats sheet. Click on Cats to select it, then click on O.K. The document will now open.
The shorthand way of describing the document that you have opened is Q:\Samples\excel97\cats.xls
The spreadsheet that you are using is a read only spreadsheet on a shared network drive. Read-only shared drives allow you and your colleagues to access common data for tasks such as mail merge. You are using the Microsoft Excel 97 spreadsheet as a very simple database. 3.4
Editing a Main Document
If your main document is empty then Microsoft Word for Windows 97 will remind you, and prompt you to click on the Edit Main Document button. (Figure 6). Otherwise, if you were using an existing letter you would click on the Edit button in the Mail Merge Helper window. Figure 6 Edit Main Document
Birkbeck College Central Computing Services
7
Doc. 5.91 Ver1
What is a database?
Figure 7 Mail Merge Tool Bar
You will see that Word for Windows 97 has an extra toolbar (Figure 7) under the formatting toolbars. As with other Microsoft Office 97 toolbars you can find out what the buttons are for by using Hover Help. Exercise 3 Insert Merge Fields • Inside the Word Document window type the word Dear followed by a space. • Click on the Insert Merge Field button, and then select the Name field. Notice how the field will now appear in the letter as <
8
Birkbeck College Central Computing Services
What is a database?
Doc 5.91 Ver 1
4 Bibliographic Data - Endnote 4.1
What is Endnote?
A common requirement is to manage bibliographic data. You could develop your own bibliographic database using Microsoft Access 97. However this would be superfluous, as bibliographic database management systems are available. Endnote is the CCS supported database for managing bibliographic data, that is, lists of references. You can create your own database or download them from online databases. Endnote supports a wide range of styles for most of the common journals and publications. In order to use Endnote you need to have it installed on your computer. It is installed on all CCS managed machines in student workstations. FCE staff who use CCS managed machines should contact the FCE System Support Officer (Neil Hearn) on ext. 6310. Administrative staff should contact the System Administrator (Dorma Powell) on ext. 6595. Schools and Faculties who do not use CCS managed machines will need to purchase licences, as the suppliers do not offer a site licence option. Supplier details can be found at http://www.cherwell.com/ We will not create an Endnote database today, as that would occupy a whole workshop in itself. Instead we will use an existing Endnote database to illustrate how Endnote can combine with Microsoft Word for Windows 97 to generate a list of bibliographic references in Microsoft Word for Windows 97. 4.2
Inserting a list of references into Microsoft Word for Windows 97
CCS document 5.76 explains in detail how to create Endnote databases, how to insert citations into your documents, how to access Endnote libraries, and much more. Today we will use an existing database to see how easy it is to put a list of references into a Microsoft Word for Windows 97 document. Note that the example you will look at today uses only one of the many styles available to Endnote, so do not worry if it is not the style that you normally use.
In the following example you will open an Endnote Library held in a file Q:/Samples/Endnote/Course/sample.enl. Although the Endnote windows have a slightly different appearance to the Microsoft Excel 97 example in Exercise 2, the technique for opening a file is very similar. Before you follow the step by step instructions given in Exercise 4 for opening the Library try to open it for yourself. Exercise 4 Open an Endnote Library • • •
Open a new Microsoft Word for Windows 97 document. Type the word Bibliography and press the ENTER key a couple of times. In Microsoft Word for Windows 97 click on the Tools menu, then click on Go To Endnote. The Endnote program will start & you will see an Endnote window on top of your Microsoft Word for Windows 97 document, with a dialogue box asking you to open an Endnote library.
Birkbeck College Central Computing Services
9
Doc. 5.91 Ver1
•
What is a database?
You now need to open an Endnote Library. If Endnote displays a map of the desktop (Figure 8) then double click on My Computer to see all the drives on your computer. If Endnote displays the N:\My Documents folder then click on the go up a folder button (see margin) until you can see all the drives. Figure 8 Map of Desktop
• • • •
When you can see all the drives on your computer double click on the drive labelled Q: The contents of Q: will be displayed in the window. On the Q drive double click on the folder labelled Samples to display its contents. In the Samples folder double click on the folder labelled Endnote to display its contents. In the Endnote folder double click on the folder labelled Course to display its contents, then click on sample.enl. Your screen should now look like Figure 9 Figure 9 Endnote Library
10
Birkbeck College Central Computing Services
What is a database?
•
Doc 5.91 Ver 1
Click on the Open button to open the Endnote database. As this is a shared example you will get a message warning you that the database is locked. Click on O.K to accept the warning. Your database should look like Figure 10 Figure 10 An Endnote database
The Endnote Library is used to store all your references. Usually you will only want to copy selected ones into your document. When you have selected the ones that you want Endnote will format them for you and store them on the clipboard1 for you to paste into your Microsoft Word for Windows 97 document. Exercise 5 Create a List of References • •
Click on one of the references in the list to highlight it. Hold down the CTRL key and click on a few more (Figure 11) Figure 11 Selected References
1
The clipboard is a special part of the computer memory that can be used as temporary storage for text and graphics that you copy and paste between documents and/or programs.
Birkbeck College Central Computing Services
11
Doc. 5.91 Ver1
• • •
What is a database?
On the Endnote Edit menu click on Copy Formatted. Endnote will now format the selected items and store them on the clipboard. Make Microsoft Word for Windows 97 the active program by clicking on the Microsoft Word for Windows 97 icon (see left) which is on the taskbar at the bottom of your screen Position the cursor at the end of your Microsoft Word for Windows 97 document and click on the paste button. A formatted bibliography looking something like Figure 12 will appear in your document. Figure 12 Endnote Bibliography
• • •
12
You can change the font to fit in with the rest of the document if you wish. Save your document as N:\My Documents\Mybiblio.doc Close Microsoft Word for Windows 97 and Endnote.
Birkbeck College Central Computing Services
What is a database?
Doc 5.91 Ver 1
5 Library databases 5.1
Training provided by the Library
Generally students are given training in how to use the Library effectively as part of an induction programme at the beginning of their course. Subject librarians will contact School or departmental library representatives and course directors about induction arrangements during the summer. More advanced sessions can be arranged for students later in their course of study. The nature, content, and length of these sessions can be negotiated with subject librarians. Possible sessions include:
How to use the computer catalogue How to use electronic journals and databases Subject resources on the Internet How to do a literature search
You may need authorisation to access certain information. For databases that belong to Birkbeck College you will need a Birkbeck CCS username and password. These may be distributed by Schools or administrative departments or obtained from CCS Help Desk on production of Registry student card or staff ID card. For databases outside Birkbeck you may need an ATHENS username and password. These are available from the Library. For a full list of all Library databases and method of access see http://www.bbk.ac.uk/lib/cdrom.html For our example today we will look at some of the databases held on Library CDROMs 5.2
Accessing CD-ROMs from CCS workstations
Today we will look at how to access the Library CD-ROM services from CCS workstations. To make it easier for students to access the CD-ROM service an icon has been added to the desk top on student workstations. If you are not using a workstation then you can still access them by starting your Web browser and entering the URL http://cdrom1.bbk.ac.uk/Iris/ Exercise 6 Using the Birkbeck CD-ROM Database • •
Double click on the CD-ROM icon on the desktop (see left) Click on the Start Button (see left), which will take you to a list of CD-ROM's.
Birkbeck College Central Computing Services
13
Doc. 5.91 Ver1
What is a database?
CCS managed workstations have all the necessary software installed to allow you to view the CD-ROM databases. If you are viewing from your own PC or from a School or Faculty that has its own PC's then you may get a message that you need an ICA Plugin (Figure 13). This is a special program that allows you to view the CDROM database. Students and staff using home PC's can download it for themselves. Staff should contact the IT support person in your School or Faculty. Figure 13 Needs ICA Plugin
14
Birkbeck College Central Computing Services
What is a database?
Doc 5.91 Ver 1
6 Web based Databases for Birkbeck Staff One of the features of a database is that it can provide end users with forms that they can fill in. These forms pass information to the database and the database returns results, for example a screen display of information or a report. Database designers are increasingly making use of the World Wide Web to implement their databases. Two examples that can be used by College staff are the On-Line Room Booking System and the Corporate Higher Education Administrative On-Line Support. Staff should be able to access both these systems via their Web browser using their CCS username and password. 6.1
On line Room Booking System
Staff can book rooms by filling in Web based forms. Here is an example of a booking request. NB - this is not an exercise for you to try yourself. The database that is shown here is a live database so please do not make any entries unless you genuinely need to book a room. You can reach the room booking database either by going via the Administrative Departments page or directly at http://staff.bbk.ac.uk/ef/roomrequest.html First you will be asked to enter a staff username and pass word (Figure 14). This will normally be the username and password that you use for reading email. If there is an authorisation problem a screen will appear telling you what to do. Figure 14 Username/Password Prompt
When you have entered your username and password you will see a web based Room Booking Request Form. Click in the first box so that the cursor appears as a blinking light and type your name. You can move between boxes either by pressing the TAB key to go forward (shift + TAB to go back) or clicking in a box to select it. When you have completed the form click on the button labelled Submit Room Request. (Figure 15) Figure 15 Room Request Buttons
Birkbeck College Central Computing Services
15
Doc. 5.91 Ver1
What is a database?
If you need to clear the form click on the button labelled Clear Form 6.2
CHAOS
The Planning Office has implemented a management information system designed for administrative support. The Corporate Higher Education Administrative On-line Support system, or the CHAOS system for short, is an online database containing information about the student population at Birkbeck. You can query the database to produce on-line reports and graphical analyses that:
Monitor enrolment numbers by course / school / faculty. Monitor target intake numbers by course. Calculate forecast student numbers. Calculate FTE student numbers. Calculate forecast tuition fee income.
The database is available to all staff with a CCS username and password on the staff Intranet at http://staff.bbk.ac.uk/po/chaos/chaos.html, or go to the Planning Office web page and follow the CHAOS link. If you have any queries or comments please ring David McGhie on extension 6276 or Mike de Halpert on extension 6311.
16
Birkbeck College Central Computing Services
What is a database?
Doc 5.91 Ver 1
7 Using a Microsoft Access 97 template As we discussed in section 2, designing a database is not an exercise to be undertaken lightly. For example, the workshop material for CCS workshops is in 3 parts (Documents 5.69, 5.70, and 5.82) and workshops normally occupy 9 hours of class time. However, we can take a look at an example very easily because Microsoft Access 97 comes with tools that can create commonly used databases for you. We will use a template to create a Microsoft Access 97 database. A template is a document or file having a pre-set format, used as a starting point for a particular application so that the format does not have to be recreated each time it is used. You may be familiar with using a template in Microsoft Word for Windows 97 or Microsoft Excel 97. Microsoft Access 97 comes with some quite sophisticated templates, as you will see. A database consists of various components (known as objects in Microsoft Access 97 jargon). For example Table Objects are used to store the data. Form Objects are used to enter data into tables Report Objects are used to display printed reports about the data in the tables. 7.1
Creating the database
Wizards are special programs that Microsoft Access 97 can run to help you create objects in the database, or even whole databases. In the next exercise you will see how to create a database using a wizard. In section 2.5 we saw that the database is not the same as the data in the database. In the students and classes example you will ask the wizard to add some sample data so that you can experiment. However, if you wanted to replace the imaginary students and classes with information about your own students and classes then you could do this. We will see an example of how easy it is to add a new class and instructor to the data. Putting data into a database is not the tricky bit! The trick is to get the database design right!
Birkbeck College Central Computing Services
17
Doc. 5.91 Ver1
What is a database?
Exercise 7 Create a database using a Wizard •
From Start, Programs start Microsoft Access 97 (click on the Office Assistant close button if necessary to get rid of it) In the Create a new database window (Figure 16) make sure that Database Wizard is selected and click on O.K.
•
Figure 16 Create a database using a wizard
Select Database Wizard
•
In the New Database Window (Figure 17) make sure the database tab is selected and click on students and classes.mdz (scroll down the window if you cannot see it). Figure 17 New Database
18
Birkbeck College Central Computing Services
What is a database?
• •
Doc 5.91 Ver 1
Click on O.K In the File New Database window (Figure 18) save your database as N:\My Documents\bbkstudents.mdb Figure 18 File Database
• • • • • • •
Click on Create In the first database wizard screen click on Next> In the second screen make sure that Yes Include sample data is checked (i.e. there is a ✔ next to it). If ther eis no check mark then click in the box and one will appear. Click on Next> In the third screen choose a style for screen displays and click on Next> In the fourth screen choose a style for printed reports and click on next. In the fifth screen change the title to Birkbeck Students and Classes and click on next> In the final screen click on Finish
Wait a few minutes while Microsoft Access 97 creates the database.
Birkbeck College Central Computing Services
19
Doc. 5.91 Ver1
7.2
What is a database?
Using the database
The way that you use a database will, of course, depend on how it has been designed. RDBMS's such as Microsoft Access 97 offer the designer considerable choice both in the appearance of the database, and the way a database is used. Note that in the following discussion these are not exercises for you to do yourself. They are general guidelines about working with this database. There are some exercises for you to try later when you have read the guidelines. First we will look at some common techniques that are used in this database. The switchboard is a commonly used way of selecting functions that you wish to perform.. To use any of the items on the switchboard menu (Figure 19) click on the button next to one the labels, e.g. Enter/View Classes. This starts a form that will allow you to enter data into the database. Figure 19 Switchboard Example
20
Birkbeck College Central Computing Services
What is a database?
Doc 5.91 Ver 1
To move between fields on a form either click in a field or use the tab key to cycle through the fields (shift + tab to move backwards) To exit from a form click on the X in the top right corner of the form (Figure 20). Click here to close form
Figure 20 Form Example
Enter data in the boxes
Some boxes use a drop down menu (known as a pick list in Microsoft Access 97 jargon). Click the box. If a drop down arrow appears this means that there is a menu. Click on the drop down arrow. When a menu drops down click on an item to select it. (Figure 21) Figure 21 Pick List Example Click on a pick list item to select it
Birkbeck College Central Computing Services
Click on drop down arrow to get picklist
21
Doc. 5.91 Ver1
What is a database?
Some forms use subforms, that is a form within a form. For example, the classes form allows you to add students to a class. Click in a blank line in the subform, then click the drop down arrow to get a pick list. Figure 22 Sub Form example
Suppose we want to add a new class called 'What is a database' It is run by CCS department. This department is not in the existing database so we will need to enter its details. The instructor is Vivien Hall. Again, she is not yet in the database so we will need to enter her details. The main switchboard offers you the option to add a class so you might think you can start there. However, if you look at the form classes you will see that a class has a department and an instructor. So you will need to enter data about the instructor and department before you can enter data about classes.
If you are already familiar with using databases then you may want to skip these exercises. Exercise 8 Enter a Department • • • • •
22
Click on the button next to Enter and View Other Information. The Forms Switchboard appears. Click on the button next to Enter and View Departments. A Departments Form appears. Click on the New Record button (see left) to create a new record Enter data for department CCS, with Department # (number) as 99 and Dr. J.S. Gill as the Department Chair. Close the form
Birkbeck College Central Computing Services
What is a database?
Doc 5.91 Ver 1
Exercise 9 Enter an Instructor • •
Use the Enter and View Instructors button on the Departments Form to open the Instructors Form and enter data about a new instructor Vivien Hall (leave the phone number blank for now, we will discuss the reason why later) Close the form.
Exercise 10 Enter Class Details • • • • • • • 7.3
Click on the button next to Return to Main Switchboard to leave the Forms Switchboard. On the Main Switchboard click on Enter/View Classes to open the Classes form. Enter a new class called What is a database? Choose CCS from the departments pick list Choose Vivien Hall from the instructors pick list Add two or three students to the class. This is all the data that you need to enter for now, so close the form. What does it all mean?
Although the demonstration that you have just seen does not teach you about designing a database, it introduces various concepts that an end user of a database will find useful. You, as an end user, did not need to worry about the internal structure of the database as all the internal data structures (tables, fields) were created for you. However, if you prefer to use a more familiar terminology, e.g. lecturers instead of instructors, Schools instead of departments it is easy to change the names that are displayed. Doc 5.69 explains how to change the names of fields in tables. The designers provided forms that told you what sort of data you could enter e.g. data about classes, instructors, and departments. The CCS training material provides plenty of examples of creating forms. You used buttons on the main switchboard to trigger events. Doc 5.82 explains how to build your own switchboard. You were offered menus (picklists) so that you could only choose values that were valid. For example, you could only have a class run by an instructor who was known to the database. Again the CCS training material explains how to create your own picklists I advised you to omit the telephone numbers because these fields use a mask, that ensures that numbers are in the correct format. Unfortunately, the default masks are in USA format, so UK phone numbers will not be accepted. However, this is something that is very simple to correct, and is covered in CCS Document 5.69.
Birkbeck College Central Computing Services
23
Doc. 5.91 Ver1
What is a database?
8 Finding Out More 1. CCS Docs 5.69 Building Applications with Microsoft Access 97 - Part 1 Introduction to Microsoft Access 97' 2. CCS Docs 5.70 Building Applications with Microsoft Access 97 - Part 2 More Microsoft Access 97' 3. CCS Docs 5.82 Building Applications with Microsoft Access 97 - Part 3 Designing the User Interface' 1. Microsoft Access 97 videos are available for short term loan from the Library 2. Lorna Weatherill and Vivienne Hemingway (1994), Using and Designing Databases for Academic Work: A Practical Guide, ITTI. Available for reference at CCS Help Desk. 3. A Student’s Guide to Microsoft Access 97 may be purchased from the Student's Union shop (price approximately £7) 4. Peter Domanski & Philip Irvine, A Practical Guide to Relational Database Design, Domanski-Irvine Book Company, ISBN 9526043 02. See web page http://www.personal.u-net.com/~dibookco/Book1.htm for details. 5. S.Coles, J.Rowley, Access 97 - Basic Skills, Letts 1997, ISBN 1 85805 299 8 Self training material available from Dillons (£8.95 in February 1999) 6. S.Coles, J.Rowley, Access 97 - Further Skills, Letts 1997, ISBN 1 85805 300 5 Further self training material available from Dillons (£8.95 in February 1999) 7. http://www.woodyswatch.com/ - subscribe to a free email magazine containing hints and tips on Microsoft Office 97 applications. 8. http://www.freeskills.com/ - free internet based training 9. http://www.learninglink.bbk.ac.uk/learnlnk/site.htm - subscription courses on the Internet.
24
Birkbeck College Central Computing Services
Related Documents
What Is Database
November 2019 13
What Is A Database Link Oracle
May 2020 4
What Is
October 2019 115
