Computing Academy GCSE Computer Science

Cost Disadvantages of NetworksFile Access The cost of buying and installing the equipment necessary for a computer Security network can be very high. Staffing There are many advantages to files being stored in a central location on a Viruses file server. If, however, the file server was to fail no users would be able to access their data. One of the main advantages of networks is the fact that resources are easy to access from any computer on the network. This is also one of the main disadvantages. Unauthorised people may try to hack in and gain access to information held on a computer network. Security measures such as firewalls are necessary. It will usually be necessary to have specialist staff to install and manage a computer network. Depending on the size and complexity of the network, it may be necessary to have several full time members of staff responsible for maintenance to ensure the network runs without issue. Computer viruses are usually designed to spread to other computers. An infected stand-alone computer cannot pass this virus on, but a virus that has infected a computer on a network could easily spread 100

Network ClassificationLocal Area Network (LAN)A LAN is a collection of computer systems (and peripherals such as printers) connectedtogether. A LAN will link computer systems within a building and even across severalbuildings on the same site (within the same geographical region), for example, thecomputer systems within a university campus (that has several buildings) will beconnected together using a LAN. The organisation, in this case the university, isresponsible for installing and managing the network and the network infrastructure(including all of the cabling).Wide Area Network (WAN)While a LAN is a collection of computer systems connected together on a single site, awide area network (WAN) is a collection of computer systems connected using additional, 101

rented infrastructure, usually spread across a wide geographical area. If a businesswanted to connect their LAN in Manchester to their LAN in London they would need torent cable infrastructure from telecoms companies between the two sites as they are notable to install their own cable running the length of the country! Networks connected usinginfrastructure rented from a third party are known as Wide Area Networks (WAN).Metropolitan Area Network (MAN)A MAN is a large computer network that spans a campus (such as a large university) oreven a metropolitan area (such as a town or city). In terms of geographical scope, a MANwill be larger than a LAN, but smaller than a WAN. A MAN may be owned and operated bya single organisation, but will usually be owned by more than one organisation and use thetelecoms network infrastructure.Wireless Personal Area Network (WPAN)Short range wireless networks are a relatively new technology but are incredibly useful.Using a mobile device to create a personal hotspot that then allows other computersystems to take advantage of the devices’ Internet connectivity is one example of aWPAN. Sending documents to print, or connecting a smartphone to a car stereo systemvia bluetooth are also examples of WPANs.Virtual Private Network (VPN)Installing network infrastructure can be extremely costly, especially when a WAN isrequired for communication between geographically remote sites. VPNs offer analternative.Rather than connecting to a private LAN or private WAN, computer systems simplyconnect to the Internet. Special software than creates a tunnel between authorisedcomputer systems (via the Internet) creating a simulated or virtual network. 102

Network InfrastructureIn order to build computer networks, we need infrastructure including servers, clients,network interfaces and cables.ServersServers are powerful computers that serve smaller client computers on the network. Theyprovide and manage services which may include printing (print servers), access to files (fileservers), websites (web servers) etc. One server may provide services to thousands ofclient computers.ClientsThe computers most of us use on a daily basis are clients. These are less powerful thanservers and rely on servers to provide certain services. If, for example, you wanted to print 103

a document to a networked printer, your computer (client) would send the request to aprint server; the print server would be receiving lots of requests from other clients too andwould need to manage these in a print queue. When you wish to view a webpage, the webbrowser application on your computer (client) would connect to the web server andrequest a webpage; the web server would be receiving lots of requests from other clientstoo and would need to manage these.Network Adapter / Network Interface CardIn order to be able to connect to a network, your computer must have a networkinterface card (NIC) or adapter. A NIC is a special piece of hardware built into mostmodern portable computer systems and can be a wired NIC or a wireless NIC. NICs canbe purchased and added to desktop computer systems if they do not already have one.HubsIf you have several computer systems that you’d like to connect to a network, you canconnect them all to a hub. When a packet of data arrives into a hub it is sent out to allconnected clients. 104

SwitchesOften, you don’t want to send data to every client on a network. A switch is an intelligenthub that can filter the data it receives and only send it on to the intended recipient(s).Switches manage the flow of packets of data sent across networks and ensure that datagets to the right place.ModemsA modulator-demodulator (Modem) is used to convert the digital information used bycomputer systems into analog signals that can be transmitted across the telephonenetwork.RoutersRouters are used when connecting together more than one network; they ensure thatnetwork traffic is transmitted and received properly from one network to the next. Routersmay be used when connecting together two local area networks (LAN), a LAN to a widearea network (WAN), or a LAN to the Internet.IP AddressesIn order to successfully send a message from one computer to another, we need to knowwhere to send it. When computers connect to a network they are assigned an InternetProtocol (IP) address; this is a unique address which is different to every other addresson the network.IP addresses consist of 4 groups of decimal numbers such as: 74.125.224.72Each decimal number represents a binary byte and computer systems will communicateusing binary - we however, write IP addresses in decimal form as it’s easier for us tounderstand.The IP address: 74.125.224.72 would be transmitted and read by computer systems as:01001010 01111101 11100000 01001000 105

IP addresses can be static, so that a computer will always have the same address, ordynamic, so that a computer may be allocated a different address each time it connectsto the network.MAC AddressesUnique Media Access Control (MAC) addresses are allocated to every Network InterfaceCard (NIC) by the manufacturer. Every network interface has a different MAC address, so alaptop with a wired network connection and a wireless network connection will have 2different MAC addresses.MAC addresses consist of six pairs of hexadecimal numbers such as: C1:B3:57:12:C1:F2BandwidthBandwidth describes how much data can be transmitted through a medium - which couldbe wired or wireless - at one time. If a network is being used by several users at the sametime - all of whom are transmitting or receiving data - the network may slow down as thereis not sufficient bandwidth.Bandwidth is measured in bits per second (bps). 106

Network TypesPeer-to-peerIn a peer-to-peer network there is no central control or administration; all computersconnected to the network are equal. Files, software, email and printers are managed byeach user individually on their own computer.While a peer-to-peer network is easy to configure (you just need to connect eachcomputer to a hub), they do have limited use because there is no central control so eachuser is responsible for installing their own software updates, managing their own security,performing their own backups etc. 107

Client-serverMost of the networks that we use regularly work on a client-server basis, or client-serverarchitecture. In this setup, files and software are usually stored on the server andrequested by the client as needed. Printers are connected to the server and print jobs aresent to the printer from the client via the server.While it can be a lengthy job to configure a client-server network, and specialistknowledge is required, this approach makes it easier to control security, performmaintenance and upgrades, and backup data.Each time we connect to the Internet we join a network and become a client; each time weuse a mobile phone we connect to thecellular network and become a client.Each time a client sends a request, this issent across the network (which mayinclude the Internet) to the server. Theserver will then collate the requested dataand respond to the client.The client-server model is used both in theconfiguration of computer and networkhardware and the applications that run onthat hardware.A request to print a document is sent froma client program (such as a wordprocessor) on a client computer to theprint server software on the print servercomputer. The server software processesthe print request and responds to theclient to confirm what action has beentaken. 108

When typing a web address into a browser, the browser software is client software,running on a client computer. The client computer sends the request across the Internet toa server machine where the web server software processes the request and responds withthe requested webpage.Packets and Packet SwitchingData sent between computers across a network - including the Internet needs to bebroken down into small chunks called packets. Whether it’s a word processed document,a single photograph or an entire movie - it will be split into packets before transmissionand reassembled on arrival at the destination.The early analogue telephone network used a system known as circuit switching to securea dedicated link between the person making the call and the person receiving the call. Thecall could not take place until this dedicated connection was made. When attempting to transmit data from one side of the world to the other, it is not possible to establish a dedicated continuous connection between the client and the server as this would tie up routers, switches and cables all over the world. Packet switching allows data to be sent without planning a dedicated route first. At each node along the journey - which might be a router or a switch - each packet of data is routed 109

along to the next node. If one node is busy, or offline, the packet can be routed via adifferent node.Packets that make up the same file, may travel to their destination via different routes andmay arrive at their destination in any order. The Transmission Control Protocol (TCP) isresponsible for ensuring that all expected packets have been received and that they arethen reassembled in the correct order. 110

Network TopologiesThere are several ways of configuring a computer network, each with benefits anddrawbacks. An organisation looking to setup a network will need to consider costs andperformance when choosing which topology (configuration) to use. 111

BusBus networks feature a central backbone cable to which all computer nodes includingservers and printers are connected. When any computer on the network needs to pass data to another computer, the packet of data is sent down the bus in both directions to every computer node on the network until the intended recipient is found. If more than one node attempts tosend data down the bus at the same time collisions occur and as such bus networks donot cope well will lots of traffic; they are only suitable for domestic home use, or for smallorganisations with few computer nodes.A bus network only features one main cable and and as such is relatively inexpensive tosetup.RingRing networks are setup in a circular configuration anddata travels only one way around the ring passingfrom one computer to the next with each computeracting as a repeater.Every node in a ring network is critical; if one of thecomputers is broken, it cannot pass on the data to thenext and the network will fail. 112

StarEvery node on a star network is connected to a centralserver or switch; this is the most common networkinfrastructure. Star networks take much longer tosetup and configure and and more expensive than thealternatives as it is necessary to run a cable from everycomputer back to the central server (which might bequite far away). Despite these drawbacks, theperformance of star networks is much greater than thealternatives and if any node breaks, it does not affectany other computer on the network. It is also verysimple to add additional nodes to the network withoutdisrupting existing nodes. 113

Network ProtocolsIn order for computers networks to function, all computer systems on the network(including clients and servers) need to understand one another. Protocols are the formalrules that set out how communication between computers on a network will work;successful communication can only happen if all computers follow these protocols.HandshakingBefore data can be transmitted or received, the client and the server need to acknowledgeone another, and agree on the set of protocols that will be used. Any interaction betweencomputers on a network starts with the handshaking process.The client sends a message to the server requesting a connection; kind of like a “hello”.The server may be busy or offline, but if it is available, the server will respond and grant aconnection. The client will then negotiate with the server until the protocols to be used 114

have been agreed. Once this has happened, the client can go ahead and request a file orservice.TCPTransmission Control Protocol (TCP) is the protocol used to break up large amounts ofdata into packets (small chunks of data) before they are sent over a network. The protocolensures that the packets are put back together correctly at the destination to recreate theoriginal data.IPInternet Protocol ensures that when sending data across a network it arrives at the correctintended IP address.SMTP / POP / IMAPThe Simple Mail Transfer Protocol (SMTP) is the most common protocol used forsending email. Post Office Protocol (POP) and Internet Message Access Protocol(IMAP) are common protocols for retrieving email. When checking your email using POP,the emails are usually downloaded to your computer and deleted from the mail server.When checking your email using IMAP, the emails are generally left on the mail server. Ifyou only use one computer system POP is a good choice, but if you wish to access emailfrom multiple devices you’ll need to use IMAP, as POP will delete them from the mailserver. 115

HTTPHypertext Transfer Protocol (HTTP) is the standard protocol used for transmittingwebpages (built using Hypertext Markup Language - HTML) over the Internet. Althoughmodern web browsers do this for us if we forget, when visiting any website, the domainname is also preceded by the http:// prefix to let the browser know that this protocolneeds to be used.FTPViewing websites is handled using HTTP, but in order to copy those website files to theweb server (so that they become live on the Internet), we need to use the File TransferProtocol (FTP). 116

The InternetThe Internet is a network of networks. The Internet consists of private LANs connected bymaking use of infrastructure owned and managed by third parties such as the telephonenetwork and fibre optic cables; the Internet is therefore a very large WAN. Key Point The Internet and the World Wide Web (WWW) are not the same. The Internet is the infrastructure of cables and routers that connect computer systems. The World Wide Web is a collection of webpages and resources hosted (stored) on the Internet. 117

Schools and universities have their own LANs; companies and corporations havenetworks; individuals have personal home networks. The Internet is simply how wedescribe all of these global networks connected to one another.The Internet isn’t owned or managed by one person, or one group, or one country.IP AddressesAs we saw in the previous Network Infrastructure section of this book, in order tosuccessfully send a message from one computer to another, we need to know where tosend it. This applies when talking about computer networks and the Internet.As illustrated in the diagram above, computers are usually connected to a router (eitherwirelessly or using a cable), which in turn is connected to the Internet. Each computer willhave a unique internal or private IP address; in the diagram above, the IP addresses of thecomputers on the LAN are 192.168.0.5, 192.168.0.6 and 192.168.0.7. The router will havean external, public IP address which in the diagram above is 86.23.4.12. 118

Should the computer with the internal IP address: 192.168.0.7 request a webpage, theweb server will send this webpage to the router with IP address 86.23.4.12. The router isresponsible for ensuring that the data is routed to the correct internal IP address.Try typing “what is my IP” into a Google and you will be shown your public, external IPaddress - that is, the IP address assigned to your router.Domain Name System (DNS)Websites are stored on web servers which are connected to the Internet. Websites areaccessed by typing in a Uniform Resource Locator (URL) or web address such as:http://www.google.co.uk. This URL is in fact an alias (alternative) for the IP address of theweb server on which the website is stored.It’s much easier to remember http://www.google.co.uk than http://74.125.224.72 but bothwill work.When you enter a URL such as http://www.google.co.uk into a web browser, the browsercommunicates with a special server called a Domain Name System (DNS) Server. TheDNS server runs a lookup in it’s database to find and translate the URL entered into thematching IP address. The DNS then returns the correct IP which allows the browser torequest the website from the correct computer.HardwareModemComputers use binary signals (1s and 0s, on and off) to transmit data, but the telephonenetwork is analogue and transmits a varying voltage signal. The telephone network is amajor part of the infrastructure that allows local area networks to connect with oneanother.In order to connect to the Internet, we need a modem (modulator-demodulator) to convertthe digital information used by computer systems into analog signals that can betransmitted across the telephone network. 119

RouterRouters are used to connect one network to another. When we connect to the Internet, weare essentially connecting our home network to another network (the Internet), so it isnecessary to use a router. Routers ensure that packets of data leaving one network arecorrectly passed to another network so that they reach the intended destination. It iscommon today to buy a single box that is both the modem and router in one. This boxalso often acts as a wireless access point too which allows our computers to access theInternet wirelessly.Hypertext Markup Language (HTML)HTML is the standard adopted for the creation and interpretation of webpages. Even themost complex modern webpages make use of HTML.HTML is a text based language that uses tags to define the content of webpages. Tagswork in pairs, so it is necessary to have an opening and a closing tag. To create a largeheading, for example, we would write: <h1>This text is our heading</h1>Notice that we have used the opening tag <h1> and the closing tag </h1>. Some of themost commonly used tags include:<head>...</head> Contains details about the webpage, such as the author of the page and the title.<title>...</title> The title will appear at the top of the web browser when viewing this page.<body>...</body> The content appearing between these tags forms the main page content.<p>...</p> Paragraphs are defined using these tags. Each paragraph will begin on a new line.<h1>...</h1> Heading tags define a title or heading. <h1> is the largest heading tag.<img src=”...” /> This tag inserts an image and tells the web browser the location of the image file.<a href=””>...</> This tag defines a hyperlink.See some of these tags in action in the following webpage written in HTML. 120

<!doctype html><html lang=\"en\"><head> <meta charset=\"utf-8\"> <title>Sheffield Wednesday Football Club</title></head><body><img src=\"http://upload.wikimedia.org/wikipedia/en/thumb/a/a5/Sheffield_Wednesday.svg/200px-Sheffield_Wednesday.svg.png\" /><h1>Welcome to the Unofficial Sheffield Wednesday Football ClubWebsite</h1><h3>A history of the club</h3><p>Sheffield Wednesday Football Club is a football club based inSheffield, South Yorkshire, England. They are one of the oldestprofessional clubs in the world and the third oldest in the Englishleague.</p><p>The Wednesday, as they were named until 1929, were foundingmembers of The Football Alliance in 1889, and its first championsthat inaugural season. The Wednesday joined The Football Leaguethree years later when the leagues merged. Sheffield Wednesday werealso one of the founding members of The Premier League in 1992.</p><p>For more information about Sheffield Wednesday visit <ahref=\"http://www.swfc.co.uk\"> this link</a></body></html> 121

The HTML on the previous page when viewed using a web browser appears like this:While HTML is used to define structure and content, Cascading Style Sheets (CSS) canbe used to control the style and appearance of webpages. 122

The following CSS code is responsible for transforming the original webpage into thestyled version.h1{ font-family: sans-serif; font-size: x-large; color: blue; text-align: center;}h3{ font-family: sans-serif; font-size: large; color: purple;}p{ font-family: sans-serif; font-size: medium; color: black;}a{ text-decoration: none;} 123

CompressionEverything we do on the Internet involves uploading data from our computers, ordownloading data to our computers. When listening to music using an online streamingservice or downloading a video file a significant amount of data needs to be downloaded.Faster Internet connectivity means that we do not now need to wait as long for data toupload and download, but compressing data so that it takes up less space can alsospeed up this process.Lossy CompressionRemoving some of the data from a file can reduce the file size and this makes transfer over a network faster. Removing data, means removing detail so it’s important not to remove too much detail otherwise the quality of the file being compressed will become unsatisfactory. We saw in the Data Representation chapter of this book that reducing the quality (compressing) a photograph from 24 bit (16,777,216 colours) to 8 bit(256 colours) means that the image takes up just 95KB of space rather than the original293KB of space. Although a lot of detail has been lost, it is not noticeable, as illustrated inthis 8 bit image of the Sacré-Cœur Basilica de Montmartre in Paris. 124

Lossless CompressionEncoding data can allow us to reduce the file size, without losing any data or quality.Peter Piper is a nursery rhyme and well known tongue twister: “Peter Piper picked a peck of pickled peppers. A peck of pickled peppers Peter Piper picked. If Peter Piper picked a peck of pickled peppers, Where's the peck of pickled peppers Peter Piper picked?”Currently this text consists of 34 words. In order to compress this section of text, we couldencode each word with a number. peter 1 piper 2 picked 3 4 a 5 peck 6 7 of 8pickled 9peppers 10 11 ifwhere’s theNow, rather than 34 words, we only need 11 numbers to represent the original text assome of the words are repeated.1 2 3 4 5 6 7 8. 4 5 6 7 8 1 2 3. 9 1 2 3 4 5 6 7 8, 10 11 5 6 7 8 1 2 3?We don’t need to store the same word more than once and this allows us to save space. 125

Web Standard File TypesThe following file formats are used for uncompressed images and sound:Name File Extension DescriptionBitmap .bmp Uncompressed image fileWaveform Audio File .wav Uncompressed sound file used by Microsoft Windows computer Format systemsAudio Interchange .aiff Uncompressed sound file used by Apple Macintosh computer File Format systemsThe need, however, to reduce file size when transmitting data over the Internet has led tothe development of new compressed file formats including:Name File Extension Type Description Lossless Graphics .gif Compressed bitmap image. Suitable for small imagesInterchange Format Lossy such as icons and logos. Only supports 8 bit images. LosslessJoint Photography .jpg Supports animation. Experts Group .png Lossy Compressed bitmap image. Commonly used forPortable Network Lossy photographs both on the Internet and offline. Graphics N/A An improved replacement for GIF. The most usedMoving Pictures .mpeg lossless image format on the Internet. Expert Group A set of standards used to encode and compress both Moving Pictures .mp3 sound and video. Several versions of MPEG exist areExpert Group Audio are used to broadcast terrestrial, cable and satellite Layer 3 transmissions and encode DVDs.Portable Document .pdf The de facto standard for distributing music on the Format Internet. The format is lossy, but the data removed is generally inaudible to humans anyway so the reduction in quality is not noticeable. The open standard for exchanging documents. Images and text are displayed as they were in the original and there is no need to have the software used to create the original. 126

TaskWrite out your own sentence or phrase and encode this using a losslesscompression algorithm similar to the Peter Piper example. 127

6 A database is a persistent store of related data that isDatabases organised in such a way as to make it useful

Thinking About DataWhat is a Database’25’, ‘Sheffield’ and ‘Volkswagen’ are pieces of data, but on their own lack meaning andorganisation. Does ‘25’ represent age? house number? miles? We need to organise thisdata so that we can understand it; then it can become useful. Key Point A database is a persistent store of related data that is organised in such a way as to make it useful. 129

Databases are not temporary deposits of information they are persistent or permanentcollections; as such they are stored in computer systems on persistent or permanentstorage (HDDs or SSDs) rather than volatile memory. Databases contain related data; allthe information about one person or one thing, for example, a company might have an‘employees’ database which contains all the related data about employees such as name,age, address etc. Task For each of the organisations listed below, list the data that they would need to collect, store, organise and search through. • the police • utility companies • hospitals • supermarkets • banksA hospital deals with thousands of patients each day and it’s important that they have lotsof information about each person they treat in order to provide the best possible care.Here is some information a hospital may collect about a patient. 325434 Asthma 100813 020688Hardman Chandos Steven EarpDoes all this make sense? Can you understand what this data means? 130

Records and FieldsLook again at the same data we’ve just seen. It didn’t really make sense before. Howabout now... ID FName LName DOB Ward Condition Doctor Admission325434 Steven Hardman 020688 Date Chandos Asthma Earp 100813By organising the related data (all of the data about one thing - in this case one person)into a record consisting of several fields it all now makes much more sense.Key PointA field is one piece of information about one thing or one person.A record is all of the related information about one thing or one person.Examples of fields include the FName field or the Doctor field in a hospital database, orthe Price field or Weight field in a supermarket database.A hospital database might have a record for Steven Hardman which includes all of thedata fields about Steven Hardman such as his ID, DOB, Ward, Condition, Doctor andAdmission Date. A supermarket products database might have a record for baked beansconsisting of all of the data fields about the baked beans product such as price, weight,brand, store aisle and best before date.Below is the ‘Patients’ table from a hospital database which contains 5 records and eachrecord consists of 8 fields. 131

Flat file DatabasesFlat file databases are set up using fields and records and used to store small amounts ofsimple data. The list of contacts stored in your smartphone is an example of a simple flatfile database and when viewed in a Database Management System (DBMS) might look likethe image below.While flat file databases are easy to setup, they are not appropriate when you wish to storeand manage larger quantities of related data. A hospital for example, needs to storeadditional information such as ward, doctor, condition etc. 132

Database Management SystemsOrganisations use Database Management Systems (DBMSs) to store, organise, add, edit,delete and search through records. A DBMS is a piece of software that allows us to createand work with databases. Just like a word processor allows us to create letters, a DBMSallows us to create databases. DBMSs also manage:• Automatic Backups• Concurrency Control• Data Integrity• Data Redundancy 133

Automatic BackupsMany DBMSs allow you to schedule automatic backups. Consider the implications to ahospital, popular social networking website, or bank of losing all of their data. It could costthese organisations millions of pounds and even endanger lives. It’s therefore critical thatorganisations backup their data.Concurrency ControlWhile a simple database might only need to be accessed by one person at once, somedatabases need to be accessed concurrently by multiple users. At the time of writing themost popular social networking website in the world has over 526 million active userseach day. This website, like many others, is driven by databases, and with 526 millionactive users each day, the chances are that more than one person will be accessing theirdatabases at the same time.DBMSs need to manage and control concurrency to avoid conflicts. Consider a ticketsales website allocating seats and taking payment for sporting fixtures; many users maybe viewing and selecting seats at the same time. What is to stop two people choosing andpaying for the same seats at the same time? Who would the seats belong to? 134

DBMSs can lock a record if it is currently being accessed by another user to prevent thisproblem from occurring. Once a record has been locked by one user, other users cannotedit that record. There is usually a time limit on record locking. When booking tickets theuser may be given a limited amount of time to make payment (as illustrated in the previousimage) during which the record for those seats in the database is locked to preventanybody else from purchasing them. Once the time has elapsed, if the user has not madepayment, the database record for those seats would be unlocked and they would becomeavailable for another user to purchase.Record locking prevents concurrency problems and conflicting updates.Data IntegrityIf a person has integrity it means you can take them at face value: they are honest andtrue. Data integrity means that the data is accurate, valid and as expected. If data isentered incorrectly into a DBMS, or if that data is not processed correctly integrity is lost. 135

When entering data into a computer system to be stored in a DBMS validation checks canbe performed on the data to assure the integrity of the data.Validation Check Description Format Length Date of Birth is usually entered as “DD / MM / YYYY”. Lookup Entering “01 / CB / QM34” would cause the format validation check to fail as only numbers were Presence expected. Range Type UK telephone numbers are no longer than 11 digits long. Entering 12 or more digits for a telephone number field would cause a length check to fail. When entering a car registration, for example, a lookup check might be performed to check the registration against a list to ensure that the registration actually exists. This check looks to see if each field has been completed. If a telephone number or email address field has been left blank, the presence check will fail. A range check ensures the data entered is within a valid range. A valid range for age might be 1-110. Somebody inputting their age as 130 is likely to be entering invalid data. A telephone number will have up to 12 digits and no letters. Entering letters in a telephone number field will cause the type check to fail.Verification is another method of ensuring data integrity and is often used along withvalidation. Typing in the same piece of data twice (such as when confirming a password) isan example of verification - it’s there to make sure you don’t accidentally press the wrongkey.Data RedundancyWithin organisations there are often several departments: in a hospital for example, thereare wards specialising in the heart, the brain, fractures and bones etc.If a patient visited several wards in a hospital and each ward created a new databaserecord for the patient data would be duplicated. Even if the patient only even visited one 136

ward, but attended several times, multiple records for that patient would be created asillustrated below where David Jones has attended several times on different dates.Having redundant (duplicated) data is a problem. Should the patient change their name,move house or get a new telephone number, each department would then need to updateevery single occurrence of the patient’s details in their records; if some departmentsupdated their records and other did not, there would be inconsistencies in the data.The solution is to have one version of the data in one place which is accessed by alldepartments. For anything other than very simple databases it is also often necessary touse relational databases with multiple tables to avoid data redundancy and remove thepossibility of inconsistencies. 137

Relational DatabasesEvery time a patient visits the hospital, a record of that visit needs to be kept. If we were touse a flat file database to record patient visits it may look like this: 138

Here we can see that David Jones has made several visits to the hospital and has beentreated by different doctors - but there are still 3 entries for David Jones in the database -his FName, LName and DOB have been duplicated leading to redundant data.Rather than using a flat file database, a hospital could use a relational database withseveral tables each focusing on one thing.The only data now stored in the patient table is ID, FName, LName and DOB. Each time apatient visits the hospital a new record is created in the PatientVisits table; this new recordwill include the Admission Data and Ward. Rather than entering the patient’s personaldetails into the PatientVisits table which would result in data redundancy, the Patient ID isincluded instead which links to the ID field of the Patient table. Similarly, rather thanincluding of details about the doctor in the PatientVisit table, the DoctorID is includedwhich links to the Doctor table.RelationsRelational databases have relations between entities in different tables which allows us tokeep data about different entities separate e.g. keeping data about a patient separate tothe data about a doctor.The entity relationship diagram below illustrates the relationship between a doctor whocan have many patients, but a patient only has one doctor. 139

In this diagram, the ‘one’ connection is made using a single line; this is sometimes alsoaccompanied by the number 1. The ‘many’ connection is represented using three lines(which look a little bit like a crows’ foot); this is sometimes instead represented with asingle line and the ‘n’ symbol.Key FieldsOne of the ideas behind relational databases is to eliminate data redundancy (theduplication of data). In order to enforce this, every record in a database should be unique.A primary key, or combined key can be setup to ensure that duplicate records are notpermitted.In our hospital database Patient ID is used to uniquely identify each patient, Doctor ID isused to uniquely identify each doctor and Visit ID is used to uniquely identify each patientvisit. Notice the key symbols which identify the primary keys in the diagram on theprevious page?Because each table in a database must have a primary key, this primary key can appear inother tables because of its relationship to data within those tables. When a primary keyfrom one table appears in another table, it is called a foreign key. In the hospital database,DoctorID and PatientID are stored in the PatientVisits table as a foreign keys. Foreign keyscreate the link that allows the data to be stored in separate tables and therefore removingredundancy. 140

Referential Integrity EnforcementReferential integrity refers to the relationship between tables. DBMSs use the relationssetup through primary and foreign keys to control and enforce referential integrity. Foreignkeys join tables and establish dependencies between tables. These dependencies meanthat if you change or delete a row in one table, you destroy the meaning of rows in othertables.Dr Earp’s ID: 3478, for example, might be linked to several patients through relations in thedatabase. If we try and simply delete the record for Dr Earp (perhaps if he has left thehospital) his ID becomes meaningless as 3478 no longer links to data about the doctor forthat patient.In a standard setup, the DBMS will not allow us to violate referential integrity and an errormessage will be shown if we try to delete rows (doctors) from the doctor table before wedelete or amend the associated patient records.If we need to remove a doctor from the database we will first need to go through and editany patient records linked to that doctor and assign them all a new doctor. When nofurther records link to the doctor we will be able to delete him.Security Measures and ViewsDatabases can be configured with multiple user profiles each with different privileges andrights. This ensures that only authorised people can access sensitive data, but it alsoremoves data that is not necessary and could cause confusion.Access is usually controlled through a username and password and once logged in,different people will see different views.General users of the database (such as a hospital secretary or receptionist) who maysimply need to enter new data or perform simple searches may have access to a formview similar to the one below. 141

Forms are designed to be user friendly and do not require the user to have an expertknowledge.The database designer who is responsible for ensuring the database system is wellmaintained may need to make changes to the fundamental structure of the database andthe relationships between tables. They will therefore need to access the logical view of thedatabase.When initially building the database and in future if changes are necessary the databasedesigner will need to access the database tables in design view where the field names andfield data types can be set as illustrated below. 142

Separating of Data and ApplicationsDBMSs ensure that data is always kept separate to the application programs that need toaccess it. This is important, as if application programs had free access to the data thiscould create conflicts with other programs also trying to access the data. A DBMS controls: which applications can access data; when they can access it and for how long; whether the application can just read data, or make changes too. Separating data from applications is also important as things change all the time. Consider a smartphone app which displays football fixtures. The app does not store this data; instead it will send a query to the DBMS which will return the requested 143

information. If the developers of the app make changes to the app, this will not affect thedatabase. If the structure of the database is changed, this will not affect the app. This alsomakes it possible for the database to be used by different apps or even entirely differentsystems. A website may also display football fixtures and fetch this data from the samedatabase. The separation of data from applications makes this possible.ACIDInteractions with databases are known as transactions and it is important that the DBMScontrols these transactions in order to prevent conflicts and maintain data integrity.DBMSs follow the ACID properties in order to guarantee that transactions are processedreliably.AtomicityAtomicity means ‘all or nothing’. It requires that either the whole transaction is completedor that none of it is completed. If one part of the transaction fails, the entire transactionfails, and the database state is left unchanged in the event of power failures, errors andcrashes.ConsistencyThe consistency property requires that any transaction will take the database from onevalid state to another. All data written to the database must follow any validation rules thathave been set up.IsolationNo transaction should interfere with any other transaction. The isolation property requiresthat either transactions execute serially (e.g. one at a time) or if they do executeconcurrently (in parallel at the same time) they behave as though they were ran serially anddo not affect other transactions.DurabilityThe durability property requires that once a transaction has been committed (completed),the transaction will remain completed even in the event of a power cut, crashes, or errors. 144

QueriesThe database can be used by people who need to query the database to find informationrelevant to them. Let’s say, for example, that Dr Earp wants to print off a list of his currentpatients; he could run a query to find all records where the search criterion on the Doctorfield is equal to Earp.Some DBMSs allow us to create queries visually using ‘query by example’ (QBE) asillustrated in the following image. Here, you can see that the criterion ‘Earp’ has beentyped in the ‘Criterion’ row under the ‘Doctor’ column. 145

There are many different types of DBMS software (just as there are many different types ofword processing software); some use a GUI and allow the user to work with windows,icons, menus and buttons, while others are controlled through a command line interfaceand expert users write textual commands in a programming language called StructuredQuery Language (SQL).Most medium and large organisations today including most of the popular websites thatwe use on a daily basis are driven by databases built and managed using SQL.Expert users such as database managers often find it quicker, easier and more flexible towrite queries using SQL. The SQL statement to run the same query as the one above builtusing QBE would be: SELECT \"ID\", \"FName\", \"LName\", \"DOB\", \"Ward\", \"Condition\", \"Doctor\", \"Admission Date\" FROM \"Patients\" WHERE \"Doctor\" = 'Earp' 146

Once the query has been execute the following results are returned. Notice how only thoserecords where “Doctor” = ‘Earp’ are returned.It is possible to run queries that contain multiple search criteria. The following query hastwo criteria and only results which match both will be displayed. SELECT \"ID\", \"FName\", \"LName\", \"DOB\", \"Ward\", \"Condition\", \"Doctor\", \"Admission Date\" FROM \"Patients\" WHERE \"Doctor\" = 'Earp' AND \"Condition\" = 'Lungs' 147

7 In 1854 a British mathematician, GeorgeAlgorithms Boole, developed Boolean Algebra

Logic GatesIn 1854 a British mathematician, George Boole, developed Boolean Algebra. Instead of analgebra that uses numbers, boolean algebra uses truth values, true (1) and false (0).We know that the very foundation of computing systems is based upon binary, 1 and 0, onor off. The ALU (part of the CPU) performs simple logical calculations by evaluating thesequences of 1s and 0s.The relationship between input and output can be illustrated using a truth table. In truthtables we use algebraic values such as A, B, C for inputs and P, Q and R for outputs 149