GCSE Computing The Database concept

1. 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.

2. 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 relateddata 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.

3. 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.

4. 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.

5. 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.

6. 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

7. 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()

8. 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)

9. 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

10. 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.

11. Databases as Files • Sequential Files. INDEX DATA The position of the item is looked up in the index then a serial search is perform from that location. 0 2000 2005 Lookup 2001 1000 2000 2002 2003 3000 2004 2005 2006

12. 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.

13. 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. • What is Callie changed her name or got married – or moved house – what are the implications for this file then?

14. Data integrity, validationand 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

15. Data integrity, validationand Verification • Verification • Can be checked by humans • Data Entry Twice • Automated • Bar codes • Magnetic Strips • OCR • OMR • RFID • Smart Cards

16. Data Modelling • Hierarchical Databases

17. 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

18. 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)

19. 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.

20. 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 KEYFIELD is a special field that contains data uniquetothatrecord • All the recordson one ENTITYare stored in aTABLE • One or moretables thenmake up thedatabase FILE

21. 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.

22. Data Modelling • Task • Create a database for the A Level College Enrolment Scenario • What Validations can you put onto the system

23. GCSE Computing 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.

24. What is meant by a DBMS? • A DBMS (Database Management System) is used tomanage 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.

25. A DBMS will allow the user to: create tables and fields create relationships between tables add, editanddelete 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 What does a DBMS allow the user to do?

26. How does a DBMS allow separation of data from applications? • 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.

27. Why is it desirable to separate data from applications? • 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.

28. 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. What are the advantages of applications being data independent? Slide 28

29. 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 control(managing access rights and privileges to users) Database back-ups Customised reports and forms What are the principal features of a DBMS? Slide 29

30. 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. How can a DBMS be used to create customised data handling applications? Slide 30

31. Using a database • Transactions • A change to database is called a transaction • Need to be Controlled to prevent conflict • ACID • Atomicity • Transaction needs to be carried out completely or not at all. DB will refer to original state if transaction fails • Consistency • Must not break referential integrity. Must still be valid after transaction • Isolation • No transaction will interfere with another. Tables are locked when in use. • Durability • Transactions are persistent.

32. The Language of a Database SQL – Structured Query Language • The Structure of an SQL Statement

33. 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

34. 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_namedata_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.RunSQLstrsql End Sub

35. 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.RunSQLstrsql End Sub

36. 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.RunSQLstrsql End Sub

37. 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.RunSQLstrsql 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.

38. 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.RunSQLstrsql End Sub

39. 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.RunSQLstrsql End Sub

40. Features offered by a DBMS • Queries • Forms • Reports

41. 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.

42. Query Design. • TASK • Using the Basement Database – Perform the following queries. • Use the query help sheet as a guide. • Queries. • Some Criteria • >10 • <=100 • Between 10 and 20 • “Bob” • Like “B*” • Like [Form]![frmCustomer]![CustomerID] & “*” • <10/12/13 • <Date() • “Yes” 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

43. Forms • Task • With the teacher – create a user form for searching customers in the Basement Database system

44. Reports • Task • Create some customised reports for the queries you created in the Query Design Task.