Published on September 29, 2015
1. Page 1 of 28 2.3 Databases Introduction to Databases A database is a way of storing information in a structured, logical way. They are used to collect and organize information such as customer details for a business, medical records at a surgery, or stock items at a warehouse. Database content is easy to manage and information can be accessed and updated quickly and efficiently. A database can store and handle vast amounts of data. A user can sort and search a database to find any desired data. Most databases have the following properties: 1. Tables Data is stored in rows and columns (similar to a spreadsheet – the main difference is HOW the data is organized). Each row in a table is called a record which is made up of a number of fields (columns in the table). The data type in the fields is usually either text, numeric or date/time. Most databases contain a number of tables which are usually linked together in some way. 2. Records A record is a collection of fields that contains data about a single object – it is a row within a table. 3. Fields A field is a single category of data within a database, which appears in all the records of a table – it is a column within a table. Key fields A key field is used to identify the records within a database. There are two types of keys: Primary key; Secondary key. Primary key The Primary key is a unique field that identifies a single record. This prevents any records from having the same value. Some ‘natural’ primary keys are: CarRegistrationNumber; ISBN – a 10-digit code that uniquely identifies a book; MAC number – a 6-part number that uniquely identifies a network card National Insurance Number – can uniquely identify employees of a company (not usable for under 16s or for non-British nationalists!) Secondary key A Secondary key is a non-unique field, used in a search that does not always produce only one matching record. Some typical secondary keys are: LastName; PostCode; DateOfBirth;
2. Page 2 of 28 2.3 Databases The following terms are used to describe parts of a database: Record structure Before setting up a database, the record structure must be decided to make better use of the memory and backing store, and to make searching and report creation easier. For example, a car showroom wants to record details of the cars it sells. Before setting up the database, the following attributes need to be decided: Field Name Field type Field size Format Input Mask Validation Rule Key Field
3. Page 3 of 28 2.3 Databases Record structure Field name Field type Format Registration number Alphanumeric Up to 7 characters - the key field Make Alphanumeric Up to 15 characters Model Alphanumeric Up to 15 characters Date first registered Date DDMMYY Price Currency Up to 5 numbers Taxed Yes/No (Boolean) 1 character Y/N Etc… When designing a database it is important to choose the correct field type. This ensures that the data stored is usable and it makes validation easier. For example, if the price paid for goods was stored in a text field, then the database wouldn’t be able to add each individual figure to produce a total.
4. Page 4 of 28 2.3 Databases Following is the list of common data types:
5. Page 5 of 28 2.3 Databases Coding of data: Any system will need to have data collected, entered and stored. One method of storing data is to assign codes to it. This usually means shortening the original data in an agreed manner. The agreement is between the users of the system. This coding scheme could be part of the training of how to use the system, and it could also be documented within the system for new users. If the coding is completely obvious then there is no such need for formal documentation. For example if a field called 'Gender' has only two values 'M' and 'F'. It should be obvious from the field name that this refers to Male and Female. Example 1 Original data: Monday; Tuesday; Wednesday; Thursday; Friday Coded data: Mon; Tues; Wed; Thurs; Fri Example 2 Original data: Extra Large; Large; Medium; Small Coded data: XL; L; M; S
6. Page 6 of 28 2.3 Databases Advantages of coding Disadvantages of coding Data entry can be faster Coarsening of data Data entry can be more accurate Meaning of data can be obscured Validation can further improve accuracy Value judgments are difficult to code Less storage space required If people don't know the code it can slow down data entry Faster searching for data If codes are complicated they might be entered incorrectly Coded data can be more secure if people don't know what it means Might run out of code combinations Estimate the size of a file from its structure and the number of records
7. Page 7 of 28 2.3 Databases The basic formula for estimating the size of a file is: If we consider a file with 200 records, which stores the details of an organisation’s customers: CUSTOMER(RefCode, Name, PostCode, Telephone, DoB, Age) We can estimate the size of the record as follows: Thus 200 records would require: Note that to determine the maximum field length, an extreme case was considered and several bytes added to play safe.
8. Page 8 of 28 2.3 Databases Database Management System: We have discussed the structure of a database as consisting of one or more tables, each of which contains records and fields of various data types. The next requirement is to have a system in place that can act upon that data as well as creating and maintaining the database itself. This is the role of the 'database management system' usually referred to as a DBMS. A DBMS is an application designed to control all aspects of a database. The DBMS will have a command language. This includes command statements for: Creating an empty database Deleting an entire database Creating and deleting tables Inserting new records into tables Updating and deleting records Being able to extract data sets Being able to produce reports that summarizes the data Being able to process the data to create new data There are many database management systems that are either commercial products or free open source applications. Examples include Name Comment MySQL A very popular, free open source system, widely used on web sites Access Included in some versions of the Microsoft Office suite Base Part of the free Open Office suite Oracle A multi-user enterprise level database management system. Widely used in industry
9. Page 9 of 28 2.3 Databases Queries Queries most commonly allow information to be retrieved from tables. Since the information is often spread across several tables, queries allow it to be viewed as one single datasheet. They also allow filtering so only the records required are seen. Queries can be either results seen directly on the screen or the output to another form or report. Example of a query: (house > 200 000) OR (no_of_rooms < 4). Points to note: (1) there are 10 records in this section of the database (2) Each record has 4 fields (3) Sample queries: (Smallest size (microns) < 1) OR (Toxic = “yes”) Would output records 4, 7, 8 and 9 (Largest size (microns) > 99) AND (Toxic = “no”) Would output records 1, 2, 3, 6 and 10 The query should match up with the field titles to ensure a correct search is carried out. Search engines on the Internet work in a similar way; the clever part is how the information is stored on the databases so that the time to do the search (based on key words) and the effectiveness of the search itself results in a very powerful and very useful tool.
10. Page 10 of 28 2.3 Databases SELECT STATEMENT The SELECT statement is a SQL query to retrieve records from database. SELECT SYNTAX The syntax for the SQL SELECT statement is: SELECT expressions FROM tables WHERE conditions; Expressions are the columns or calculations that you wish to retrieve. Tables are the tables that you wish to retrieve records from. There must be at least one table listed in the FROM clause. Conditions are conditions that must be met for the records to be selected. SQL SELECT EXAMPLE - SELECT ALL FIELDS FROM ONE TABLE Let's look at how to use a SQL SELECT query to select all fields from a table. SELECT * FROM suppliers WHERE city = 'Newark' ORDER BY city DESC; In this SQL SELECT statement example, we've used “*” to signify that we wish to view all fields from the suppliers table where the supplier resides in Newark. The result set is sorted by city in descending order.
11. Page 11 of 28 2.3 Databases SQL SELECT EXAMPLE - SELECTING INDIVIDUAL FIELDS FROM ONE TABLE You can also use the SQL SELECT statement to select individual fields from the table, as opposed to all fields from the table. For example: SELECT supplier_name, city, state FROM suppliers WHERE supplier_id > 1000 ORDER BY name ASC, city DESC; This SQL SELECT example would return only the supplier_name, city, and state fields from the suppliers table where the supplier_id value is greater than 1000. The results are sorted by supplier_name in ascending order and then city in descending order. SELECT Column Example Below is a selection from the "Customers" table: CustomerID CustomerName ContactName Address City PostalCode Country 1 Alfreds Futterkiste Maria Anders Obere Str. 57 Berlin 12209 Germany 2 Ana Trujillo Emparedados y helados Ana Trujillo Avda. de la Constitución 2222 México D.F. 05021 Mexico 3 Antonio Moreno Taquería Antonio Moreno Mataderos 2312 México D.F. 05023 Mexico 4 Around the Horn Thomas Hardy 120 Hanover Sq. London WA1 1DP UK 5 Berglunds snabbköp Christina Berglund Berguvsvägen 8 Luleå S-958 22 Sweden
12. Page 12 of 28 2.3 Databases SELECT Column Example The following SQL statement selects the "CustomerName" and "City" columns from the "Customers" table: Example SELECT CustomerName,City FROM Customers; SELECT * Example The following SQL statement selects all the columns from the "Customers" table: Example SELECT * FROM Customers; The SQL WHERE Clause The WHERE clause is used to extract only those records that fulfill a specified criterion. SQL WHERE Syntax: SELECT column_name,column_name FROM table_name WHERE column_name operator value; WHERE Clause Example The following SQL statement selects all the customers from the country "Mexico", in the "Customers" table: Example SELECT * FROM Customers WHERE Country='Mexico'; Text Fields vs. Numeric Fields SQL requires single quotes around text values (most database systems will also allow double quotes). However, numeric fields should not be enclosed in quotes: Example SELECT * FROM Customers WHERE CustomerID=1; Operators in the WHERE Clause The following operators can be used in the WHERE clause:
13. Page 13 of 28 2.3 Databases The SQL AND & OR Operators The AND operator displays a record if both the first condition AND the second condition are true. The OR operator displays a record if either the first condition OR the second condition is true. Combining AND & OR You can also combine AND & OR (use parenthesis to form complex expressions). The following SQL statement selects all customers from the country "Germany" AND the city must be equal to "Berlin" OR "München", in the "Customers" table: Example SELECT * FROM Customers WHERE Country='Germany' AND (City='Berlin' OR City='München'); Operator Description = Equal <> Not equal. Note: In some versions of SQL this operator may be written as != > Greater than < Less than >= Greater than or equal <= Less than or equal BETWEEN Between an inclusive range LIKE Search for a pattern IN To specify multiple possible values for a column
14. Page 14 of 28 2.3 Databases Database Questions: Oct/Nov 2006: A car dealer uses a database to keep details of cars in stock. Part of the stock file is shown below. (a) (i) State the fieldname that should be used as the key field. (ii) Explain the purpose of a key field. (b) The following search condition is input: (Price($) < 5000) AND (Model = Golf) Write down the records that match the above search condition using only RegNo. (c) Write down a search condition to find cars with an Engine greater than 1400cc or which have less than 5 Doors. (d) When a car is sold, the sale needs to be linked to a customer file. Suggest a new field which could be used to link the stock file to the customer file. May/June 2007: A hospital has decided to computerise its administration system. (a) Give three ways this could affect the hospital workers. The hospital will be using a database which holds confidential personal data. (b) State two precautions that the hospital should take to prevent unauthorised access to the data. (c) Describe how the database could be recovered if it became corrupted. (d) Give one example, in each case, of when it would be necessary to amend data, delete data and insert data into the patient database.
15. Page 15 of 28 2.3 Databases Oct/Nov 2007: A school Science department is going to use a database to record details about its equipment. (a) Give two advantages of using a computer system rather than a manual filing system. (b) Part of the database is shown below: (i) As data is entered it needs to be verified. Describe one way this could be done. (ii) Data also needs to be validated. Using fields from the database as examples, describe two different validation checks which could be performed on the data. Oct/Nov 2008: To gain access to a database, a user must first type in a user ID and then a password which needs to be verified. (a) How is a password usually verified? (b) In spite of these safeguards, unauthorised access to the database is still possible. What could be done: (i) to prevent data being used by unauthorised people? (ii) to prevent loss of data once the database has been illegally accessed? (c) Personal data is protected to some extent by a Data Protection Act. Give two requirements of a Data Protection Act.
16. Page 16 of 28 2.3 Databases Oct/Nov 2008: 15) A database has been produced showing solar system statistics. (a) How many records are there in this database? (b) The following search condition was typed in: (Number of moons > 0) AND (Diameter (km) < 15000) Using Name of planet, write down the results of this search: (c) Write down a search condition to find out which planets have rings or have a diameter more than 50000 km. (d) Name a different validation check for each of the following fields. (i) Maximum surface temperature (0C) (ii) Name of planet (e) The data in the database was sorted in descending order using the Number of moons field. Using Name of planet only, write down the results of this sort.
17. Page 17 of 28 2.3 Databases Oct/Nov 2009: A radio station keeps a database of all its music CDs. Here is part of this database: (a) How many records are there in the database section? (b) If the following query was input: (CD length (mins) < 60) AND (number of hit tracks > 1) using Reference Number only, write down which data items would be output. (c) Write down a query to select which CDs are special edition or have more than 10 tracks. (d) The database is sorted in descending order on CD length (mins). Using Reference Number only, write down the order of the records following this sort. (e) The radio station has a phone-in service where a listener texts the title of the CD on their mobile phone. The popularity of each CD is then known and which CDs the radio station should play. (i) How would this information be stored? (ii) How could this information be linked to the database? May/June 2009: Explain, using examples where appropriate, the meaning of these computer terms. (b) relational database
18. Page 18 of 28 2.3 Databases May/June 2009: 17) A car sales company uses a database. Here are three tables from the database: (a) How many records are shown in the Customer Details table? (b) (i) Which field connects the New Car Sales table with the Customer Details table? (ii) Which field connects the New Car Sales table with the Car Manufacturer table? (c) Give two reasons why List of Extras in the Car Manufacturer table is stored in code form. (d) A customer goes into the showroom and the salesperson keys in 162154. What fields and information would be shown on the output screen? (e) Give one advantage to the car sales company of holding customer information on a database.
19. Page 19 of 28 2.3 Databases May/June 2010: A database has been set up to bring together information about the world’s tallest buildings. A section of the database is shown below. (a) How many records are in the section of the database shown? (b) (b) Using Ref No. only, which records would be output if the following search condition was entered: (Year < 1990) AND (Height (m) > 375)? (c) Write down a search condition to find out how many buildings are in China or how many buildings have more than 80 floors. (d) For each of the following fields give a different validation check. Year Ref No. (e) The database was sorted in descending order of Year. Using Ref No. only, write down the results of the sort:
20. Page 20 of 28 2.3 Databases May/June 2010: 11) A database has been set up showing information about cars: (a) Using Car ref only, write down which cars would be output if the following search condition was used: (No of doors = 4) AND (Fuel used (km/litre) > 15) (b) Write down a search condition to find out which cars have engines larger than 1.8 litres OR have CO2 emissions higher than 150 g/km. (c) The database is sorted in ascending order on Fuel used (km/litre). Using Car ref only, write down the results of the sort. Oct/Nov 2010: 10) A database has been set up to store information about aircraft. A section is shown below. (a) How many fields are in each record? (b) Using Ref No only, what records would be output if the following search condition was entered: (Max Weight(kg) > 350 000) AND (Wing Span(m) < 66)? (c) Write down the search condition to find out which aircraft have a length greater than 74 metres or have a maximum speed less than 900 kph.
21. Page 21 of 28 2.3 Databases May/June 2011: A database showing the population of world cities has been produced. A section of the database is shown below. (a) How many records are shown above? (b) Using Ref No only, which records would be found if the following search condition was typed in (Country = “India” OR Area = “America”) AND (Capital = “No”) (c) Write a search condition to find the cities in Asia with a city population greater than 17 million OR an urban population greater than 20 million. (d) Give one advantage of using Y or N rather than Yes or No in the Capital column. Oct/Nov 2011: An airport has a number of hotels nearby. A database has been set up to give customer’s information to allow them to select a hotel. (a) How many records are shown in the database? (b) Which field in each record must be unique? (c) The following search condition was typed in: (No. of stars > 3) OR (Hotel parking = Y) Using Hotel Ref only, which records would be found? (d) Write down the search condition to find which hotels were less than 10 km from the airport and charged under $100 per person. (e) The database was sorted into descending order using No. of rooms. Using Hotel Ref only, write down the sorted order of records.
22. Page 22 of 28 2.3 Databases Oct/Nov 2011: A database has been set up to show details about countries. Part of the database is shown below. (a) How many fields are in each record? (b) Using Country code only, what would be output if the following search condition was used? (Population (millions) > 1000) OR (Continent = “Asia”) (c) Write down a search condition to find which countries have a land area less than 3 million square km and also have a coastline. (d) If the database was sorted in descending order of population size, using Country code only, what would be the order of countries in the database?
23. Page 23 of 28 2.3 Databases May/June 2012: A database was set up to show the properties of certain chemical elements. Part of the database is shown below. (a) How many fields are in each record? (b) The following search condition was entered: (Melting Point (C) < 40) AND (Atomic Weight > 100) Using Element Symbol only, which records would be output? (c) We need to know which elements have an atomic number greater than 50 and are solid at room temperature. Write down the search condition to find out these elements. (d) The data are to be sorted in descending order of Boiling Point (C). Write down the new order of records using the Element Symbol only.
24. Page 24 of 28 2.3 Databases Oct/Nov 2012: A database was set up showing the largest ocean-going liners. Part of the database is shown below. (a) How many records are shown in the above part? (b) Using Liner ID only, what would be output if the following search condition was typed in: (Year built < 2000) AND (Country of Registration = Country of Construction)? (c) Write the search condition to find out which liners have a gross tonnage larger than 80 000 or are registered in the UK.
25. Page 25 of 28 2.3 Databases May/June 2013: 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? (b) The following search condition was entered: (No of countries < 30) AND (Head office = “Americas”) Using Code only, which records would be output? (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?
26. Page 26 of 28 2.3 Databases May/June 2013: A survey of motorways was carried out and a database was produced. A section of the database is shown below. (a) How many fields and how many records are shown? (i) number of fields (ii) number of records (b) Using Motorway ID only, what would be output if the following search condition was used? (Length (km) > 100) AND (Number of lanes > 3) (c) What search condition is needed to find the motorways where the number of cars per day exceeds 50 000 or the toll charge per kilometre is greater than $0.50?
27. Page 27 of 28 2.3 Databases Oct/Nov 2013 P12: 3) A motor car manufacturers offer various combinations of Seat colors Seat materials Car paint colors A database was set up to help customers choose which seat and paint combinations were possible. (NOTE: N = no, not a possible combination, Y = yes, combination is possible) (a) How many records are shown in the database?  (b) The following search condition was entered: (cloth = “Y”) AND (blue = “Y”) Using code only, which records will be found?  (c) A customer wanted to know the possible combinations for a car with leather seats and either silver or grey paint colour. What search condition would need to be input?  (d) A customer decided to buy a green car. He wanted to know which seat colours and seat materials were not a possible combination with green paint. What search condition would he need to enter?  (e) Give one advantage of using the codes Y and N in the database rather than using Yes and No. 
28. Page 28 of 28 2.3 Databases Oct/Nov 2013 P13: 9 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?  (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”)  (ii) What useful information does this search produce?  (c) Write a search condition to locate items costing more than $2.00 or have a stock value exceeding $300.00. 
Create an Access database. This article covers the basic process of starting Access 2010 and creating a database that will be used on desktop machines, not ...
Zak Muscovitch - International Domain Name and Website Attorney ... Domain Name Research Tools: Using the Global Brand Database By Zak Muscovitch .
Complete information for ZAK gene (Protein Coding), Sterile Alpha Motif And Leucine Zipper Containing Kinase AZK, including: function, proteins, disorders ...
Zak Ruvalcaba. Involved in the Web ... Zak is founder of and advisor to Module Media, ... Securing Passwords in Your Database. by Zak Ruvalcaba. Jul 09, 2004.
ZAC M&E Database System Document 1. PURPOSE OF ZAC HIV AND AIDS M&E DATABASE (‘M&E DATABASE’) The purpose of the database is to: 1.1.
King Zak Industries – Database Notes CDH Energy Corp. March 2014 Table 1 Database Notes
Zak # Here is an example of how mysql_fetch_row is used. # Connect to your MySQL database server mysql_connect ('localhost', 'username', 'password') or die ...
TRY is a network of vegetation scientists headed by DIVERSITAS/IGBP, and the Max Planck Institute for Biogeochemistry, providing a global archive of ...
Zak Zodiac, Bexx Active Also known as Zak Zodiac, Bexx, Zak Knight, Zak Bevis, Mini Michaels, Mini Mysterio, Pink Lady