GCSE Computing The Database concept CANDIDATES SHOULD BE ABLE TO: DESCRIBE A DATABASE AS A PERSISTENT ORGANISED STORE OF DATA EXPLAIN THE USE OF DATA HANDLING SOFTWARE TO CREATE, MAINTAIN AND INTERROGATE A DATABASE. Slide 1 What is a database? A database is a persistent, organised store of related data. A database is persistent because the data and structures are maintained even when the applications that use the data are no longer running. A database is organised because the data is stored in a very structured way, using tables, records and fields so that users and data handling applications can easily add, delete, edit, search and manipulate the data. A database is made up of related data because the individual items of data have a connection of some sort. For example, a database of students would contain related items such as name, date of birth, address, classes etc. Slide 2 Database examples - Hospital databases Hospital databases maintain details of patients, doctors and treatments. The databases manage and co-ordinate admissions, consultations, treatments, staffing and stock control. Slide 3 Database examples – Businesses Businesses use databases to keep track of sales, stock and staff etc. and to analyse their own performance. Databases also help businesses to monitor trends in customers’ purchases. This helps businesses identify market opportunities. Slide 4 Database examples - Internet Search engines Internet Search engines, such as Google, Bing, Yahoo, etc. all have powerful databases behind the scenes to collect the details of websites that are used in searches. The search engine indexes web pages it finds and uses this index to answer user queries. Slide 5 Computerised databases Computerised databases have several advantages over paper-based manual databases. These include: the ability for the data to be accessed by more than one person at the same time the ability to interrogate or query the data in many different ways and view the resulting answers The ability to rapidly sort the data by multiple criteria the ability for changes to the data to be made quickly available to all end users the reduction of errors in repetitive tasks due to the processing accuracy of data handling software the output of data in a range of different formats to suit user needs (e.g. graphs, reports, forms, etc.), either for viewing on screen or as print-outs Slide 6 Databases as Files Serial Files In Python – Create the following file. #program to write 10 values to a disk file. f = open(‘workfile.txt’,’w’) for x in range (1,11) f.write(str(x)) f.write(‘\n’) f.close() Databases as Files Serial Files This creates a serial file. It can be read also as follows #program to read 10 values from a disk file f = open(‘workfile.txt’,’r’) for x in range (1,11): f.read(x) print(x) Databases as Files Serial Files To search for an item in a serial file is easy as they are stored one after each other. Using the following algorithm – can you create a python program to search for a value in a serial file. Input search item Go to first item in the file Repeat if item = search item then output item endif move to next item Until item found or not more data If item not found output item not found endif Databases as Files Sequential Files. Sequential files are files where the data is in some sort of order. Ordered files are much quicker to search. You can use good search algorithms such as the Binary search. Sequential files can be searched using an index. Databases as Files Sequential Files. Lookup 2005 INDEX DATA 0 2000 1000 2001 2000 2002 3000 2003 2004 2005 2006 The position of the item is looked up in the index then a serial search is perform from that location. Flat File database A database that has only one entity such as Addresses can be stored as a flat file. Which means all the data about that one thing is stored in a single table/file. First name Last name Telephone Street City Postcode DOB Claire 01242 234234 1434 Aenean Road Cheltenham GL52 XXX 28/06/1999 Pate Data Redundancy However, if we wanted an address book that now stored orders – in a flat file we would have to duplicate data each time someone ordered something. First name Last Name Tel Street City Post DOB Cod e Order Item Date Callie Hodge 01242 234234 1 The Place Cheltenham GLX XXX 05/07/19 78 Printer 28/05/20 14 Callie Hodge 01242 234234 1 The Place Cheltenham GLX XXX 05/07/19 78 Toner 28/05/20 14 What is Callie changed her name or got married – or moved house – what are the implications for this file then? Data integrity, validation and Verification Check Digit A calculation performed on a number that generates another digit which is appended to the number – bar codes, isbn etc. If when the number is entered and the check digit is not calculated correct – then this is in error. Format Check Data needs to conform to a particular pattern – INPUT Masks TASK. Length check Lookup Check Presence check Range Check Type Check Data integrity, validation and Verification Verification Can be checked by humans Data Entry Twice Automated Bar codes Magnetic Strips OCR OMR RFID Smart Cards Data Modelling Hierarchical Databases Data Modelling Entity Relational Databases Entity – An object, person, event or thing of interest to an organisation and about which data is recorded. Relationship – an association or link between two entities Data Modelling Types of relationship One to one (1:1) One to Many (1:n) Many to One (n:1) Many to Many (m:n) Data Modelling Consider this scenario: A level College Enrollment A college that enrols students for AS and A2 courses. Here are the data requirements: A student Can enrol on a number of courses A course can contain many students A course is taught by one lecturer A lecturer can teach one or more course. A student belongs to one set A set can have many students. Create an entity relationship diagram for above. Database creation Database creation involves using software to define and build the structures to hold the data. In a database file the data is structured in a particular way. A single item of data is stored in a named FIELD A complete set of fields makes up a RECORD, the KEY FIELD is a special field that contains data unique to that record All the records on one ENTITY are stored in a TABLE One or more tables then make up the database FILE Slide 20 Database creation involves the following steps: Each field would be created, selecting a data type to match the data to be stored. An existing field is set as the key field or a field is created for this purpose. Once the complete set of fields have been created and any validation rules added, they are saved as a table. Data is then entered into the database fields, each complete set of fields forming a single record with a unique entry in the KEY FIELD. For example, in a database of students; A TABLE would store all the data on all the students An individual RECORD would store the data on a single student Several FIELDS would store the data (attributes) of the student such as Student ID, Forename, Surname etc. A KEY FIELD such as 'StudentID' can store a unique number to identify that student. This database FILE would contain just one table and is known as a flat-file database. Data Modelling Task Create a database for the A Level College Enrolment Scenario What Validations can you put onto the system GCSE Computing Slide 23 The DBMS Candidates should be able to: describe how a DBMS allows the separation of data from applications and why this is desirable describe the principal features of a DBMS and how they can be used to create customised data handling applications. What is meant by a DBMS? A DBMS (Database Management System) is used to manage a database. It is an integrated suite of data handling software that controls and manages the organisation, storage and access of data in a database. A DBMS allows the data to be separated from the actual applications that use the data. Slide 24 What does a DBMS allow the user to do? A DBMS will allow the user to: create tables and fields create relationships between tables add, edit and delete data maintain the integrity of the data in the database set the access rights of the database users allow the database to be searched using queries create forms to help data input and viewing the information in the database create reports to output information from the database Slide 25 How does a DBMS allow separation of data from applications? Slide 26 A DBMS can present users (and other application programs) with views of the data that are particular to the needs of the application. The views are largely independent of the way that the data is physically stored in the database files. Application programs that need to use the data do not have to include code to directly access the database. Instead, the programs send requests and instructions to the database via the DBMS. Why is it desirable to separate data from applications? Slide 27 A prime advantage of modern databases is that the database and applications that use the data can be altered independently of each other. Without a DBMS, applications were data dependent. This means that: The way in which the data was organised and the way in which it was accessed were both decided by the requirements of the application that accessed it. An understanding of how the data was stored and how it needed to be accessed had to be coded into the application. What are the advantages of applications being data independent? Avoids data duplication Different applications need different views of the same data. If applications are data-dependent, they need their own slightly different copy of the data. This data duplication leads to wasted storage space and time wasted entering the same information in different places. Avoids data inconsistency When data is duplicated for different applications, this can also lead to data inconsistency. For example: if an employee’s address is updated in the personnel database file, but not on the payroll database file, this will lead to inconsistencies in the employee’s data. The database AND the applications are easier to modify Ideally the person responsible for looking after the database, the database administrator (DBA), must have the freedom to change the physical storage of the database structures in response to changing business requirements without having to modify existing applications. When applications are data-dependent, changes made to the database can require major modifications the applications that access it. Slide 28 What are the principal features of a DBMS? A good DBMS should provide the following features: Elimination of data redundancy Managing data integrity Handling the security of the database Supporting querying Applying data validation checks Operating transaction controls (managing concurrent access). Facilitating centralised and privileges to users) control (managing access rights Database back-ups Customised reports and forms Slide 29 How can a DBMS be used to create customised data handling applications? A DBMS is able to present different areas of an organisation with views of the data that are particular to their needs. This can customised forms for data input and editing and reports for data output. Applications can be therefore be created for different areas of an organisation. For example, the forms and reports developed for users in the Sales department of a business can differ from those developed for the Marketing department, even though the underlying database is the same. Slide 30 Using a database Transactions A change to database is called a transaction Need to be Controlled to prevent conflict ACID Atomicity Consistency Must not break referential integrity. Must still be valid after transaction Isolation Transaction needs to be carried out completely or not at all. DB will refer to original state if transaction fails No transaction will interfere with another. Tables are locked when in use. Durability Transactions are persistent. The Language of a Database SQL – Structured Query Language The Structure of an SQL Statement The Language of a Database SQL – Structured Query Language Data Type Qualifiers When supplying values to an SQL statement, for example as query criteria, their data type must be correctly defined by a "qualifier". This is done by enclosing the value between a pair of appropriate characters. Text must be enclosed in either single quotes (') or double quotes ("), for example: WHERE tblStaff.Department = "Marketing” or WHERE tblStaff.Department = 'Marketing’ A Date should be enclosed in hash marks (#) also called pound or number signs, for example: WHERE tblStaff.BirthDate = #09/27/1950# A number, of any sort, needs no qualifier and can be entered as it is, for example: WHERE tblInvoices.InvoiceNumber > 1500 The Language of a Database DDL – Database Definition Language DDL is the language Databases use to create tables You can create a table using just SQL as well, by using the CREATE command as follows. CREATE TABLE tablename(field_name data_type required, next field…..); Try entering in the following code into a module in a blank database: Sub createtbl() Dim strsql As String strsql = "CREATE TABLE tblTest([StaffID] COUNTER CONSTRAINT ndxStaffID PRIMARY KEY," _ & "[FirstName] Text(25),[LastName] TEXT(30), [BirthDate] DATETIME);" DoCmd.RunSQL strsql End Sub DDL When your table is created you can then use SQL to enter in records. Try the following in the same module and run it. Check to see that the table you have just created now has the new values. Sub inserttbl() Dim strsql As String strsql = "INSERT INTO tblTest ([FirstName], [LastName], [BirthDate])" _ & " VALUES ('Martin', 'Green', #09/27/1950#);" DoCmd.RunSQL strsql End Sub DDL If you wanted to add a new field to your table dynamically – SQL can also do this as well. Add the following code to the module and run the subroutine and see what happed you your table. Sub addtbl() Dim strsql As String strsql = "ALTER TABLE tblTest ADD COLUMN [Age] BYTE;" DoCmd.RunSQL strsql End Sub DDL If you then wanted to modify the data because you have entered a new field for instance – SQL will allow you to do that as well. Sub modifytbl() Dim strsql As String strsql = "UPDATE tblTest SET [Age]=52 WHERE [FirstName]='Martin' AND [LastName]='Green';" DoCmd.RunSQL strsql End Sub The value “52” has been hardcoded here – but you could have a function work out the age from other fields, store that answer in a variable and have that assigned here. DDL Now if you wanted the delete a row the SQL statement for that is as follows: Sub deletetbl() Dim strsql As String strsql = "DELETE FROM tblTest WHERE [FirstName]='Martin';" DoCmd.RunSQL strsql End Sub DDL And finally if you wanted to removed the table altogether then the DROP command will allow you to do this: Sub droptbl() Dim strsql As String strsql = "DROP TABLE tblTest" DoCmd.RunSQL strsql End Sub Features offered by a DBMS Queries Forms Reports Query Design The example of a simple query below uses an Microsoft Access database table and a query to find the names of all students who are in form 10B. This involves the following steps: Adding the 'Students' table to the query; Selecting the fields to display; Adding the search criteria against the correct field, in this case 'Form‘; Running the query. Slide 41 Query Design. 1) 2) 3) 4) 5) 6) 7) 8) TASK Using the Basement Database – Perform the following queries. Use the query help sheet as a guide. All the Davies that live in a close All the Doctors All the Men who are not doctors All the Customers that have one letter for the First Name All the customers with a surname alphabetically after Morris All the Records that have a country specified. All the CDs that are between £5 and £10 All the Best of albums Queries. Some Criteria • • • • • • • • • >10 <=100 Between 10 and 20 “Bob” Like “B*” Like [Form]![frmCustomer]![CustomerI D] & “*” <10/12/13 <Date() “Yes” Forms Task With the teacher – create a user form for searching customers in the Basement Database system Reports Task Create some customised reports for the queries you created in the Query Design Task.