Topic 5

Topic 5

1. A database is a system designed to store data in an organised way, making it easier to search for the information you need. A database needs to be reliable, consistent and have a structure that suits the data you collect. A database is stored in a format such as a CSV file saved on a hard drive.

2. Types of databases Databases can be computerised or paper based. An address book is an example of a paper based database. In an address book entries are stored in a logical order, normally by surname and each entry contains the same fields such as name, house number, street name etc.

3. Why are databases used? One of the main benefits of computer databases is that they make it easy to store information so it is quick and easy to find. For example, if you have music files on your computer, a media application like iTunes, Windows Media Player or Google Music organises that data so it is easy for you to quickly search for the artist or songs you want.

4. • Text: Text and numbers. Examples of text type fields are names and addresses and session activities. • Number: Numerical data on which you intend to perform mathematical calculations (except calculations involving money and date). A typical numeric field would be number of items in stock. • Auto number: Sequential whole numbers automatically inserted by Access. This makes a good primary key field as each number is unique for each record. However, as a consequence, you cannot edit this number and, if you later delete the record, that number is lost. This data type is used primarily to give uniqueness to each record- for example, a membership number or a product identification number. • Date/Time: Fairly obviously this accommodates dates and times. For example you might need to select all clients who joined after a specified date. • Currency: Currency data type is used for money. This data type maintains a fixed number of digits to the right of the decimal. • Yes/No: This d

5. A phrase database designers commonly think about is "garbage in, garbage out" or GIGO. This means that if you do not design your database properly, and do not take in useful information, you will not get useful information out of it.

6. Primary Key Each table should have a primary key. This is a field chosen to be unique for each record. Usually this is some sort of reference number. Foreign key A foreign key is used to link tables together and create a relationship between them. It is a primary key in one table that is linked to a field in another table.

7. A database is a Persistent system used to store data in an organised and structured manner. One of the main benefits of a database is that they are it makes it quick to and easy to find specific information. Data Mining is a term used to describe analysing large amounts of data to predict future events and trends. Weather forecasters use data mining to help them predict future weather by looking at previous events. A database is organised into a set of key components. tables store information on real world Entities. Attributes are used to store details on each entity. These are recorded as fields in a database. Each record in a database must have a Primary key This is used to uniquely identify each record in a table within a database. There are two types of database flat file or Relational. A flat-file database is a database of just one table. It can be created in database software or in a spreadsheet and is often saved as a CSV file. A flat file is useful because it is highly

8. Most databases are manipulated with a Database Management System (DBMS). A DBMS provides the software tools to create, maintain and interrogate a database as well as providing an interface to applications.

9. Multiple applications need to be able to access the same data stored in a database BUT this must not cause conflicts. To prevent this: • Records must be locked while one application is performing an update so that another application cannot produce an inconsistent result. • Inconsistent changes must be prevented – for example , an exam subject must not be deleted if there are students connected to it. • This is called maintaining referential integrity.

10. Features offered by a DBMS

    • Queries Queries are objects that extract data from a database. They are used to select the fields required and the conditions that need to be met. A query can be created by using an interactive graphic tool called “Query by example” QBE.
    • Forms This is a user friendly ways of allowing the user to see and modify. Without these sql would take programming expertise.
    • Reports These are often the printed out of data from the database, they can be tables or fully formed and well-presented reports. They are usual based on a queries.
    • Comparison conditions
      • A query however normally wants to select only records in a table that meet a set condition. We can achieve this in SQL in much the same way as in QBE. SELECT * FROM Employees WHERE { some logical condition}
      • Logical condition Description Example = This looks for a matching value within a filed. SELECT * FROM Employees WHERE city = ‘Hull’ != This is looking for all records that DO NOT match the condition. SELECT * FROM Employees WHERE city != ‘Hull’ > This looks for a number field that has a value greater than a value. Such as years > 6. SELECT * FROM Employees WHERE Years > 6 < This looks for a number field that has a value greater than a value. Such as years < 6. SELECT * FROM Employees WHERE Years < 6 >= This looks for a number field that has a value greater than a value. Such as years >= 10. SELECT * FROM Employees WHERE Years >=10
      • Logical operator Description Example AND Let’s you combine two fields SELECT * FROM Employees WHERE city = ‘Hull’ AND First name = ‘Sandra’ OR Allows either art of the statement to be true in order to return a result SELECT * FROM Employees WHERE First name = ‘John’ OR First name = ‘Tom’ NOT This operator wants to be false rather than true in order to return a result SELECT * FROM Employees WHERE NOT (Surname =’Jones’)