Computer Science Chapter 13 PDF

Preview

Summary

Databases...

Description

CIE IGCSE COMPUTER SCIENCE Practical problem-solving and programming Chapter 13 - Databases 13.1 Introduction A DATABASE is a structured collection of data that allows people to extract information in a way that meets their needs. The data can include text, numbers, pictures: anything that can be stored in a computer. Databases are very useful in preventing data problems occurring because:   

data is only stored once – no data duplication if any changes or additions are made it only has to be done once – the data is consistent the same data is used by everyone.

13. 2 What are databases used for? To store information about people, for example:  

patients in a hospital students at a school.

To store information about things, for example:  

cars to be sold books in a library.

To store information about events, for example:  

hotel bookings results of races.

13.3 the structure of a database Inside a database, data is stored in TABLES, which consists of many RECORDS and each record consists of several FIELDS.

Tables contain data about one type of item, person or event, for example:   

a table of patients a table of books a table of doctor’s appointments.

Each record within a table contains data about a single item, person or event, for example: 

Winnie Sing (a hospital patient) 1

 

IGCSE Computer Science (a book) The 15:45 appointment on 27 January 2014.

Each field contains one specific piece of information about a single item, person or event, for example: 



For a hospital o first name o family name o date of admission o consultant o ward number o bed number. For a book the fields could include: o title o author o ISBN.

In order to be sure that each record can be found easily and to prevent more than one copy of the same record being kept, each record includes a PRIMARY KEY field. Each primary key field in the table is unique.

The primary key can be a field that is already used, provided it is unique, for example the ISBN in the book table, or a new field added to each record, for example, a unique hospital number could be added to each hospital patient’s record. Sometimes, a primary key can be formed by using two or more existing fields, for example, the doctor’s appointments could have a primary key made from the date and the time of each appointment.

13.4 Practical use of a database A student should be able to do the following:   

define a single-table database from a given data storage requirements choose a suitable primary key for a database table perform a query-by-example from given search criteria.

In order to do this a relational database management system needs to be used. The following case study shows how to set up a database with Microsoft Access and complete the tasks described above.

Case Study Boys and girls between the ages of seven and eleven can join a club scout group. (http://en.wikipedia.org/wiki/Cub_Scout) Each cub scout group needs to keep records about its members. Most groups will keep the following information about each cub in their group: Personal Details Form 2

To ensure the records are up to date, please fill out the information below. Without a completed form, the child will not be able to participate in meetings/activities.

13.4.1 Defining a database This section shows how to define a single-table database from given data storage requirements and choose a suitable primary key. To create the cub scout database, open Access, select the Blank Database template and type the filename CubScout and click the Create button.

3

Select the table design view and name the table Cub.

4

Set up the fields to match the data collection form and include the primary key. Each field will require a meaningful name and a data type must be selected. The basic data types were introduced in Chapter 11. They are available in Access but the names are different.

Access also has other data types that will be useful: Date/Time and Currency.

Access allows validation checks to be built in for each field, for example the gender field:

5

13.4.2 Getting information from a database This section shows how to perform a query-by-example (QBE) from given search criteria. The cub scout leader wants to be reminded before the first meeting in the month of any cub scouts who will have a birthday that month. To set up a QBE to perform this task, open the database CubScout, select the Create tab followed by Query Design.

Then add table Cub.

6

The cub scout leader wants to see the Name of any cub with a Date of Birth this month. Select the fields Name and Date of Birth, check the box to display the Name and check that the month of the Date of Birth is the same as the current month.

7

Then run the query to test it.

End-of-chapter questions 1)

(7010/0420 P13 Q12 J2013) A database was set up to compare oil companies. A section of the database is shown below:

a)

How many fields are there in each record? [1]  7 8

b)

2)

The following search condition was entered: (No of countries < 30) AND (Head office = “Americas”) Using Code only, which records would be output? [2]  KO, OS, SN c) What search condition is needed to find out which oil companies have a share price less than $50 or whose profits were greater than 8 billion dollars? [2]  (Share price ($)) < 50.00 OR (Profits (billion $) > 8.0) (7010/0420 P13 Q9 N2013) A database was set up to keep track of goods in a shop. A section of the database is shown below:

a)

How many records are shown in this section of database? [1]  8

b) i.

Using Item code only, what would be output if the following search was carried out? (Number in stock < Re-order level) AND (Items ordered = “No”)? [2]  1515  1801 ii. What useful information does this search produce? [1]  Checks whether new goods have (yet) to be ordered  To maintain stock levels c) Write a search condition to locate items costing more than $2.00 or which have a stock value exceeding $300.00 [2]  (Price of item ($) > 2) OR (Value of stock ($) > 300)

9...