Topic outline

  • General

  • UNIT 1: COMPUTER SECURITY

    Key Unit Competency:

    To be able to enumerate various security threats and ensure security of computer.

    INTRODUCTORY ACTIVITY

    A. Apply the following scenario.

    1. In the school computer lab, some computers have been damaged by humidity, some computer screens that have been broken and other computers are not password protected. In groups, respond to the following questions. What is wrong with such A computer lab?

    2. Identify the risks of an unsecured computer

    3. Describe how this computer lab can be secured

    4. Describe how data stored in a computer can be protected

    5. What are the measures that can be considered to protect the computer lab physically and logically?

    Describe what you see on the picture bellow.

    1. Is this computer lab well organized?

    2. Identify the security risks.

    3. Propose solutions to improve the security of material inside this computer lab

    1.1 Why computer security?

    ACTIVITY 1.1

    Visit the school computer lab and answer the following questions?

    1. In the school computer lab, are all computers secured? If yes, describe how they are secured. If not, what to do to enhance the security of computers?

    2. Describe the simple measures that can be taken to protect the computers in the school computer lab

    Introduction

    Nowadays, computers become an indispensable tool in the life of human beings. They are used in banking, in shopping, in communicating between people through emails and chats, etc. However, some intruders are joining the conversations and try to read emails of others without permission. Most of the time, they misuse their computers by attacking other systems, sending forged emails from computers, or examining personal information stored in others’ computers.

    Computer security refers to techniques developed to safeguard information and systems stored on computers.

    The protection of data (information security) is important. It reduces the probability of hardware and software problems and it increases the security of data stored in computers.

    Why is computer security important?

    Computer security is important for many reasons:

    Computer security helps to keep safely data and equipment functioning properly and provide access only to appropriate people.

    Computer security prevent unauthorized persons to enter in others computers without their consents.

    Computer security helps to keep healthily computers against viruses, malware and other unintentional soft wares that can prevent computers to run smoothly.

    Computer network need to be protected because Cyber criminals, hackers, and identity thieves present real and dangerous threats to any online system.

    APPLICATION ACTIVITY 1.1

    1. By using simple words, define computer security?

    2. In pairs, discuss and write a brief report on importance of computer security at your school, in Rwanda and in the whole world.

    3. Write down some cases of computer security break that have happened in Rwanda and in the world?

    1.2 Computer threats

    LEARNING ACTIVITIES 1.2

    A hard disk is connected into a broken computer system case like the one below. When the computer is switched on, it shows that something is wrong. The user cannot work on it properly. It is very difficult to send a document to hard disk.

    1. Describe what is happening to make the computer not functioning correctly.

    2. Propose solutions to this problem

    1.2.1 Threat definition

    A threat, in the context of computer security, refers to anything that has the potential to cause serious harm to a computer system. A threat is an activity/ attack/ situation that may happen, with the potential to cause serious damage. Threats can lead to attacks on computer systems, networks and more.

    1.2.2 Threat categories

    Knowing how to identify computer security threats is the first step in protecting a computer. The threats could be intentional, accidental or caused by natural disasters. Computer threats are categorized in two categories; physical threats and logical threats:

    Physical threats

    Digital storage media and hardware are subject to numerous internal and external forces that can damage or destroy their readability. Below are some cases of physical threats:

    •Improper storage environment (temperature, humidity, light, dust),

    •Over use (mainly for physical contact media),

    •Natural disaster (fire, flood, earthquake),

    •Infrastructure failure (plumbing, electrical, climate control),

    •Inadequate hardware maintenance,

    •Hardware malfunction

    Logical threats

    Are events or attacks that remove, corrupt, deny access, allow access, or steal information from a computer without physical presence of somebody. These include viruses, worms, Trojans, spyware, adware, SQL injection etc.

    Threats to information systems can cause:

    •Hardware failure: A malfunction within the electronic circuits or electro mechanical components (disks, tapes) of a computer system. Example: a CPU socket damaged.

    •Software failure: The inability of a program to continue processing due to erroneous logic. Example: a crash of a computer program.

    •Electrical problems: are faults caused by electric like a low-resistance connection between two points in an electric circuit through which the current tends to flow rather than along the intended path.

    •User errors: is an error made by the human user of a computer system in interacting with it. Example: a system file deleted unintentionally by a user.

    •Telecommunication problems. Example: when the antenna are not working

    •Program changes; modifications made to program. Example: a simple modification in a program can affect the whole software.

    •Theft of data, software, services and equipment. When a physical or logical component of a system is stolen, the whole system stops. Example: a computer cannot run without a RAM or cannot run with a corrupted software

    APPLICATION ACTIVITY 1.2

    1. Discuss the difference between logical and physical threats and give examples for each?

    2. Explain the difference between hardware and software failure

    3. Explain how a computer user can cause the errors to information system.

    4. In the school computer lab, take one computer and remove the RAM. What is happening to the system?

    5. After analyzing the school computer lab, enumerate the different threats that are existing with the computer

    1.3 Computer attacks

    LEARNING ACTIVITY 1.3.

    Suppose that yesterday when students were using computers in the school computer lab, everything worked well. They brought from outside different storage devices like flash disks and external hard disks used to copy various documents to the computers. Today morning, when the Lab attendant switched on the computers, most of them displayed suspected messages.

    Discuss in groups what may be the cause of such behaviors. How the problem can be addressed in the laboratory?

    In computer and computer networks an attack is any attempt to expose, alter, disable, destroy, steal or gain unauthorized access to or make unauthorized use of an asset. An attack can be active or passive.

    An “active attack” attempts to alter system resources or affect their operation.

    A “passive attack” attempts to learn or make use of information from the system but does not affect system resources.

    The different kinds of attacks are summarized in the following image:


    However, the frequent computer attacks are virus, worms, Trojan, spyware, Shoulder Surfing, Denial-of-Service, eavesdropping, social engineering and cyber crimes

    1.3.1 Virus

    A virus is a self-duplicating computer program or piece of code that is loaded onto a computer without the user’s knowledge and runs against his/her wishes.Viruses can spread themselves from computer to computer, interfering with data and software. A virus is attached to small pieces of computer code, software, or documents. The virus executes when the software is run on a computer. If the virus is spread to other computers, those computers could continue to spread the virus.

    Some viruses work by hiding on the first sector of a disk and loaded into memory. Other viruses insert themselves onto program files that start applications. Those files have the extension of .exe and .com the last category of viruses are viruses which infect programs that contain powerful macro languages like programming languages.

    The common viruses are:

    a. Worms

    A worm is a computer program that sits in the computer memory, duplicates itself continuously until the system runs out of memory and crashes. Worms infect networks by replicating themselves and transmitting their multiple copies to all the nodes connected on the network.

    b.Trojan

    A Trojan may appear to be something interesting and harmless, such as a game, but when it runs it may have harmful effects. Unlike worms, Trojans do not replicate themselves but they are destructive.

    Trojan are used by hackers to gain access into a machine without the permission of the user.

    Normally when someone visits some websites which are malicious in nature, the trojan gets downloaded or may come from an infected source.

    The Trojan gets installed in the computer and later on helps the hackers to gain access into that computer.

    1.3.2 Denial of Service (DoS)

    When a denial of service (DoS) attack occurs, a computer or a network user is unable to access resources like e-mail and the Internet. An attack can be directed at an operating system or at the network.

    1.3.3 Spyware

    Just like virus, Spyware also comes under that category of malware attacks, which means that it is a code or program written for doing some damage to the computer.

    Although the working of spyware is different from the other types of malware mentioned, Spyware as the name suggests is used to spy into a system.

    The job of the spyware is to silently sit inside the host system and observe the activities of the system.It may also come from other sources like detachable storage devices. Spyware sits quietly in the system and copies all the relevant information being input and processed.

    Example of how spyware work:

    Suppose a user is logging on to any bank.

    Once the website of the bank opens, the user id and login password is input.

    After that if the user wants to do a financial transaction, the transaction password has to be entered. All this information is quietly registered by the spyware. Then the spyware sends all the information recorded from the user’s computer to its parent i.e. probably a hacker somewhere on the Internet. The information may be transmitted even while the user is using the system.

    Once the hacker has the user’s information, like bank name, login id and password, nothing can stop him/her from transferring the money from that account to anywhere else.

    APPLICATION ACTIVITY 1.3a

    1. What is a computer attack? Differentiate active attack to passive attack.

    2. Explain the difference between worms and Trojan

    3. Explain briefly how virus are categorized?

    1.3.3 Social Engineering

    A Social Engineer is a person who is able to gain access to equipment or a network by tricking people into providing him/her the necessary access information. Often, the Social Engineer gains the confidence of an employee and convinces him/her to divulge username and password information.

    A Social Engineer may pose as a technician to try to gain entry into a facility. Once inside, he/she may look over shoulders to gather information, seek out papers on desks with passwords, or obtain a company directory with e-mail addresses.

    So, Social Engineering is a technique/method used by someone by trying to socialize with someone else with the purpose of picking/getting his/her credentials or user name and password with intention to use them during his/her absence. A user never feels that revealing his/her credentials to someone that he/she trusts is wrong.

    Example:

    •A husband gives his credentials to her wife and vice versa.

    •An Administrative Assistant gives his/her credentials to his/her boss.

    •Etc

    1.3.4 Shoulder Surfing

    In computer security, shoulder surfing is a type of social engineering technique used to obtain information such as personal identification number, password and other confidential data by looking over the victim’s shoulder. This attack can be performed either from a closer range by directly looking over the owner of information.

          

    1.3.5 Eavesdropping

    Eavesdropping refers to the unauthorized monitoring of other people’s communications. It can be conducted on ordinary telephone systems, emails, instant messaging or other Internet services.

    1.3.6 Cybercrimes

    Cybercrime, also called computer crime, is any illegal activity that involves ICT tools such as a computer or network-connected device, such as a mobile phone.

    Different types of cybercrimes:

    •Cyberbullying

    Cyberbullying is bullying that takes place using electronic technology. Electronic technology includes devices and equipment such as cell phones, computers, and tablets as well as communication tools including social media sites, text messages, chat, and websites.

    Examples of cyberbullying include text messages, rumors sent by email or posted on social networking sites, and embarrassing pictures, videos, websites, or fake profiles, posting hurtful images, making online threats, and sending hurtful emails or texts.

    Example: when someone tweets or posts on social media:

    Today, the president of United States resigns because he failed to supply laptops in schools.

    •Sexting

    Sexting is the sending and receiving of text, photo or video messages of children and young people that are inappropriate and sexually explicit.

    These images are mostly self-generated and shared through mobile phone MMS, Skype and social networking sites where images can be posted and shared such as Facebook, WhatsApp, Twitter, Tumblr, Flickr, YouTube, Instagram, Snapchat etc.

    •Grooming

    “Grooming” is the way sexual predators get from bad intentions to sexual exploitation. Sometimes it involves flattery, sometimes sympathy and other times offers of gifts, money, transportation, or modeling jobs.

    1.3.7 Website hacking

    1.3.7.1 Definitions

    Hacking

    Hacking is a term used to describe actions taken by someone to gain unauthorized access to a computer belonging to other people. It is the process by which cyber criminals gain access to your computer. After entering in that computer, a hacker can find weaknesses (or preexisting bugs) in the security settings and exploit them in order to access available information. He/she can also install a Trojan horse, providing a back door for hackers to enter and search for your information.

    1.3.7.2 Website hacking techniques

    Most of the information belonging to different individuals or institutions either private or public bring attention of outsiders who may want to break inside. The use of this information differ according to the interest of its users. The owners or administrators of websites should then put in place measures to protect their information and the users. There exist different techniques used by hackers and hence, there are many tools to deal with many kinds of theft and protect the websites.

    Rwanda Information Society Authority (RISA) has pledged stronger cooperation with the public in enhancing cyber security for all computers in Rwanda amid an alert over an outbreak of a security attack that has affected over 150 countries. According to a statement, the cyber security attack is known as ransomware and bears different variations like WannaCrypt, WannaCry, WannaCryptor or Wcrya. The broad based ransomware attack has appeared in at least eight Asian nations, a dozen countries in Europe, Turkey and the United Arab Emirates and Argentina and appears to be sweeping around the globe, researchers said. The cyber-attack mostly affects computers that run Microsoft Operating Systems, by automatically encrypting the files and blocking the user’s access to the entire system. Over the last decade Rwanda’s strong growth through ICT promotion has brought untold opportunities and prosperity in the country. And as we globally face this challenge in cyber security, as a country we strongly believe that an integrated strategy to ensure effective regulation to our cyber security is significant at this point.

    To mitigate this outbreak, RISA gave a set of actions to ensure lasting national prevention and protection:

    1. Users are required to maintain daily backups of critical data including application, databases, mails systems, and user’s files. Backups should be regularly tested for data restoration.

    2. All computers should be installed with latest security updates (specifically including MS17-010. Patch)

    3. Until the security patch is applied, the Server Message Block v1 (SMB v1) should be disabled on all computers.

    4. The LAN perimeter firewall should be configured with a rule to block all incoming SMB traffic on port 445.

    5. All computers should be upgraded to Windows 10 to benefit from the latest protection from Microsoft. The Windows Defender Antivirus, which can detect the above malware, should also be enabled on all Windows systems.

    6. Ensure your Antivirus signatures are up to date as major vendors are all working to deliver updated signatures to detect/ prevent this.

    7. All users are advised not to open any suspicious email especially one that has an attachment, furthermore all users are advised not to download any files that they are not sure of the source.

    Comprehensive action to strengthen our information and communication Technology sector countrywide is taken. However, in case of any compromise or attack, RISA advises that the affected computer/PC must be removed from the network and the incident must also be reported to Rwanda Computer Security Incident Response team with immediate effect.

    WannaCry exploited a vulnerability in the Windows operating system and was among a large number of hacking tools and other files that a group known as the Shadow Brokers released on the internet. Shadow Brokers said that they obtained it from a secret NSA server.

    The identity of Shadow Brokers is unknown though many security experts believe the group that surfaced in 2016 is linked to the Russian government.

    1.3.8 Unwanted content

    During the use of internet, some webmasters through the usage of cookies and other applications had managed that when someone is navigating in a given website, unwanted webpages are opened. This happens mostly for advertising of other products or services rendered by other institutions or companies that contracted the webmasters. Or, it happens by itself with malicious software.

    1.3.9 Pornography and violence

    1.3.9.1 Pornography

    These are images and video whose focus is on n sex or sexual arousal either directly or indirectly. In that group of videos, many films on pornography are produced and sold all over the world. This kind of films hurts adults, children, couples, families, and society.

    Families, Non-Government Organizations and government institutions in charge of education and social welfare need to fight seriously against it malpractice which addict our society and hence affect the whole development.

    In Rwanda, the institutional and national ICT policies contain articles prohibiting to watch pornographic film in offices. The selling of these films is also officially banned.

    1.3.9.2 Cyber violence

    Cyber violence is defined as online behavior that constitutes or leads to assault against the well-being (physical, psychological, emotional) of an individual or group.

    What distinguishes cyber violence from traditional off-line forms of violence is that in the former case, some significant portion of the behavior takes place online, although it may even be carried out in offline contexts. There exist four basic types of cyber violence namely online contact leading to off-line abuse, cyber stalking, online harassment, degrading representations.

    1.3.10. Hate media and unwanted content

    The hate media is a form of violence, which helps to demonize and stigmatize people that belong to different groups of society. This type of media has incited haters among citizens and in some cases influenced most of the genocide that the world has known.

    APPLICATION ACTIVITY 1.3 b

    1. Which types of computer attacks had happened your school computer lab or in Rwanda or in sub region?

    2. How to recognize that a document in a computer is infected by virus?

    3. Explain what is social engineering in computer security;

    4. Explain the difference between social engineering and Shoulder Surfing techniques;

    5. Explain the different types of cybercrimes

    1.4 Sources of virus and other attacks

    LEARNING ACTIVITIES 1.4

    In the school computer lab, when one student inserted a flash disk in one computer, he/she gets a virus detection message and all files are immediately deleted and the flash disk becomes empty

    Another student discovers that when he/she opens a document in my MS Word, the content is displayed in unknown characters.

    The last students gets a message that looks like the one below:

    In groups, discuss what should be the causes for such problems.

    Source of viruses

    Virus infection in computers can be contacted through different means. Below are the common causes of computer virus attack.

    A. Internet

    It can not be denied that internet is one of the common sources of virus infection. This fact is not a real surprise and there is no point to stop using internet henceforth. Majority of all computer users are unaware as when viruses attack computer systems. Almost every computer user click/download everything that comes their way and therefore, unknowingly, invites the possibility of virus attacks.

    B. Downloadable Programs

    One of the possible sources of virus attacks is downloadable programs from the web. Unreliable sources and internet newsgroups are one of the main sources of computer virus attacks. Downloadable files are one of the best possible sources of virus. Any type of executable program including games, freeware, screen savers as well as executable files are one of the major sources of computer virus attacks.

    C. Cracked Software

    Cracked Software proves to be yet another source of virus attacks. Most people who download cracked and illegal versions of software online are unaware of the reality that they may contain virus sources as well. Such cracked forms of illegal files contain viruses and bugs that are difficult to detect as well as to remove.

    D.Email Attachments

    Email attachments are one of the other popular sources of computer virus attacks. Hence, you must handle email attachments with extreme care, especially if the email comes from an unknown sender. Installation of a good antivirus is necessary if one desires to eliminate the possibility of virus attacks.

    E. Removable media

    Removable media such as CDs, USB flash disks,... can be a source of viruses when the files they contain which may have been taken from other electronic devices, have been infected.

    F.  Bluetooth Transfer

    Viruses can be contacted through a transfer of documents via a Bluetooth, once one of the computers is infected with a virus or the document to be transferred is infected.

    APPLICATION ACTIVITY 1.4

    1. Describe different ways of how internet is a virus source

    2. Explain how CD is a source of virus in computer

    1.5 Damage caused by Threats

    LEARNING ACTIVITIES 1.5

    From the previous lessons in this unit, different kinds of threats have been studied with their characteristics and their modes of contamination.

    Discuss in groups the possible damages that they could cause in a computer, a school computer laboratory and in any network.

    The consequences of the damages may vary according to the specific type of malware and the type of device that is infected plus the nature of the data that is stored on or accessed the device.

    Whereas in some cases the results of a malware infection may be invisible to the user, in other cases the damage can have serious consequences.

    Damages caused by threats:

    For home users

    -It can infect a computer if the user clicks on an infected banner or if he/she downloads and opens an attachment from a spam email or if he/she ends up on an infected website

    -Harvest user’s data and send it to cyber criminal servers to use it in future attacks

    -Destroy user’s data, it happens when the encryption key was not downloaded correctly and won’t work when trying to decrypt your data

    -Hide from being detected by antivirus products because of its communication mechanisms

    -Enlist your computer in a botnet and use its resources to launch attacks on other victims.

    -Performance dropped when the user is not doing anything heavy.

    On corporate network

    -Web efacements and Semantic Attacks are used to propagate false information by changing the web page content subtly.

    -In Domain Name Server (DNS) Attacks, when the user requests for a particular website to the DNS server, then he/she is diverted to an unwanted website because of a wrong Internet Protocol (IP) address generated by the DNS server (DHCP).

    -Distributed Denial of Service (DDoS) Attacks involves high volume of communications to the targeted computers. It is the strategy that cyber attackers use to slow down those targeted computers.

    -There are compound attacks whereby attackers can combine a number of attacks and make a series out of them which can destroy everything by leaving no possibility of recovery.

    APPLICATION ACTIVITY 1.5

    1. List and explain damages caused by virus in a computer system

    2. List what damages caused by threats for home users

    3. Enumerate other damages caused by virus that are not listed above

    4. Use an example, explain how virus damage can reduce the production of any institution

    1.6 Threats protection and precaution

    ACTIVITY 1.6

    In the school computer laboratory, to enter in a secured computer with password, someone needs to be given that password. If it has been forgotten, it becomes impossible to work with that computer. In groups, answer to the following questions:

    1. What is the importance of login to computer with password?

    2. List other security measures that can be used to protect a computer.

    3. Explain how data stored in computer can be protected from damage?

    As with any business asset, hardware, software, networks, and data resources need to be protected and secured to ensure quality, performance, and beneficial use.

    They are four simple ways of protecting a computer:

    1. To install antivirus software

    2. To install firewall.

    3. To install anti-spyware software.

    4. To use complex and secure passwords.

    Effective security measures can reduce errors, fraud, and losses.

    1.6.1 Antivirus

    i. Definition

    Antivirus software are computer programs that attempt to identify, neutralize or eliminate harmful softwares. The term “antivirus” is used because the earliest lessons were designed to combat a wide range of threats, including worms, trojan and other malware. Antivirus software typically uses three different approaches to accomplish their tasks:

    - The first way is to examine file looking like kwon viruses that match virus definition in virus dictionary

    -The second way is to try and to recognize unusual behavior from a program which might signify a threat

    -The last way is to prevent the execution of all computer codes which has not been identified as truth worthy

    ii. Virus detection

    An antivirus needs to scan the system in order to detect a security threat such as a virus. There exist 3 possible actions, depending to the user’s choice, when a virus has been found in a file:

    • Move to quarantine: the infected file will be moved in protected repertory. It will thus be inaccessible and the code of the virus will not be executed.

    • Repair/ Disinfect: the antivirus can also try to repair an infected file, i.e. to remove the code of the virus from the file. This is needed especially for program files.

    • Delete: in this case, if the infected file cannot be repaired, there is no other alternative rather than deleting it. It is especially useful when this file is not essential to system, especially if it is not a program file.

    After scanning an internal or external storage device, a report is generated in form of an interface. An example is shown below.

    iii. Anti-Virus installation

    In this case Kaspersky 2017 is going to be used as an example of how to install an antivirus.

    Before the installation of Kaspersky 2017 on a computer, the following preparation has to be made:

    •Make sure that the software is on external storage device or on another computer is a network;

    •Check if the computer meets the requirements of Kaspersky Anti-Virus 2017;

    •Make sure no antivirus software of Kaspersky Lab or other vendors is installed on your computer;

    •Check if there is any incompatible software, remove it;

    •Close all running applications;

    Check if it is Kaspersky Anti-Virus 2017 installation under Windows 10, then click on the Desktop tile on the start screen.

    Standard Installation:

    1. Download the installation file from Kaspersky Lab website and run it. Then, the user follows the instructions given by the system.

    2. If the antivirus Kaspersky installation file is saved in another computer of the same network, connect to that computer and run the executive file from it or displace it using a removable storage. The instructions will be followed as in the point above

    3. Insert the disc into the CD/DVD drive if it contains the Kaspersky installation file. If the installation does not start automatically, run the installation file manually. Click Install.

    4. Read the License Agreement in the window that appear afterward by clicking on the respective link. Accept its terms to install the application. Respond to other windows that may display in halfway during the installation

    5. Wait until installation is completed. Make sure the Start Kaspersky Anti-Virus check box is selected, then click Finish.

    Updating an antivirus

    As mentioned above, new viruses are created every day. But if an antivirus is not aware of the signature of the newly developed virus, it will not know it and this enables the virus to attack the computer. It is important to regularly update the list of signatures of antivirus and if possible every day or at least after 3 days. These signatures are offered by the company which has developed the antivirus used. Signatures of viruses are kept in a database created by the company that created the antivirus. The steps to go through while updating an anti virus software depends on the type of that anti virus.

    1.6.2 Anti spyware

    Anti-spyware software is a type of program designed to prevent and detect unwanted spyware program installations and to remove those programs if installed. Detection may be either rule based or based on downloaded definition files that identify currently active spyware programs. Notice that most anti-virus software such as AVG contain inbuilt anti spyware software.

    There exist many anti spyware software but the most popular are the following: AVG Anti Spyware, CheckFlow Anti Spyware 2005, CounterSpy, NoAdware, Avast and ScanSpyware

    1.6.3 Firewall

    Computers connected to communication networks, such as the internet, are particularly vulnerable to electronic attack because so many people have access to them. These computers can be protected by using firewall computers or firewall software placed between the networked computers and the network. The firewall examines, filters, and reports on all information passing through the network to ensure its appropriateness.

    1.6.4 Parental Control

    (Family Safety for any user)

    The Parental Controls feature is a valuable tool for controlling the amount of time the children spend on the computer and the programs they’re using. Parental controls can filter the web, blocking inadvertent access to inappropriate websites.

    1.6.5 Access control

    Access control is a security technique that can be used to regulate who or what can view or use resources in a computing environment.

    The access control model used by some operating systems ensures authorized use of its objects by security principles. Security principles include users and groups. Security principles perform actions on objects, which include files, folders, printers, registry keys and Active Directory.

    Username and Password

    The user can protect the access to the operating system. The administrator defines the passwords of users who are allowed to use the computer. If users do not enter the valid credentials (Username and Password), access will be denied.

    •New account and password creation

    1. From your current account, go to Settings > Accounts > Other People.

    1. Click Add someone else to this PC.

    2. That dialog box wants you to enter the email address associated with a Microsoft account. Ignore that box and instead click I don’t have this person’s sign-in information.


    3. The previous option opens a new dialog box that encourages you to create a new Microsoft account, which is not your goal. Ignore the fields at the top of this dialog box and instead click Add a user without a Microsoft account.


    4. Now create that local user account, entering a short descriptive user name, a password you’ll be able to remember, and a password hint (which is mandatory).

    1.6.6 Identification

    Identification occurs when a user (or any subject) claims or professes an identity. This can be accomplished with a username, a process ID, a smart card, or anything else that can uniquely identify a subject. Security systems use this identity when determining if a subject can access an object.

    In computer security, general access control includes authentication and authorization. Authentication and access control are often combined into a single operation, so that access is approved based on successful authentication, or based on an anonymous access token. Authentication methods and tokens include passwords, biometric scans, physical keys, electronic keys and devices.

    *Authentication

    Authentication is a process in which the credentials provided are compared to those on file in a database of authorized users’ information on a local operating system or within an authentication server. If the credentials match, the process is completed and the user is granted authorization for access. The permissions and folders returned define both the environment the user sees and the way he can interact with it, including hours of access and other rights such as the amount of allocated storage space.

    Therefore, Authentication verifies the identity and authentication enables authorization

    *Authorization

    Authorization is the process of giving someone permission to do or have something. In multi-user computer systems, a system administrator defines for the system which users are allowed access to the system and what privileges of use (such as access to which file directories, hours of access, amount of allocated storage space, and so forth).

    1.6.7 Biometric authentication

    Biometric authentication is a security process that relies on the unique biological characteristics of an individual to verify that he/she is who is says he/she is. Typically, biometric authentication is used to manage access to physical and digital resources such as buildings, rooms and computing devices.

    Types of biometric authentication technologies:

    Retina scans produce an image of the blood vessel pattern in the light-sensitive surface lining the individual’s inner eye.

    Iris recognition is used to identify individuals based on unique patterns within the ring-shaped region surrounding the pupil of the eye.

    Finger scanning, the digital version of the ink-and-paper fingerprinting process, and works with details in the pattern of raised areas and branches in a human finger image.

    Finger vein ID is based on the unique vascular pattern in an individual’s finger.

    Facial recognition systems work with numeric codes called face prints, which identify 80 nodal points on a human face.

    Voice identification systems rely on characteristics created by the shape of the speaker’s mouth and throat, rather than more variable conditions

    1.6.8 Encryption and Decryption

    Definition of Key Terms

    Cryptography means “secret writing.” However, the term is used to refer to the science and art of transforming messages to make them secure and immune to attacks.

    Encryption is the process of encoding a message or information in such a way that only authorized parties can access it. Encryption does not of itself prevent intervention, but denies the intelligible content to a would-be interceptor.

    To read an encrypted file, you must have access to a secret key or password that enables you to decrypt it. Unencrypted data is called plain text; encrypted data is referred to as cipher text.

    Decryption is the process of taking encoded or encrypted text or other data and converting it back into text that you or the computer can read and understand. This term could be used to describe a method of un encrypting the data manually or with un encrypting the data using the proper codes or keys. It is reversing encryption process

    Cryptosystem: A combination of encryption and decryption methods

    Cleartext or Plaintext: The original message, before being transformed, is called plaintext. After the message is transformed, it is called ciphertext. An encryption algorithm transforms the plaintext into ciphertext; a decryption algorithm transforms the ciphertext back into plaintext.

    1.6.9 Data Backup and recovery point

    a. Data backup

    Just as the system restore points allow the restoration of operating system configuration files, backup tools allow the recovery of data. The user can use the Microsoft backup tool to perform backups as required.

    Storing backup copies of data and having backup computer capabilities are important basic safeguards because the data can then be restored if it was altered or destroyed by a computer crime or accident. Here are some considerations for data backups:

    •Computer data should be backed up frequently

    •Should be stored nearby in secure locations.

    •Transporting sensitive data to storage locations should also be done securely.

    By using MS windows 10, the backup is done in this way:

    1. Open control panel.

    2. Click on system and security.

    3. Click on backup and restore (windows 7).

    5. Under “where do you want to save the backup?” choose location.

    6. Using the “on a hard disk” drop-down menu, select the storage to save the backup and click on start back up button

    b. Recovery point

    i. Fix problems with system restore

    Microsoft Windows Operating System helps to recover from problems that might stop it from working properly, but there may come a time when it needs some manual intervention. Microsoft’s latest operating system has a similar set of recovery tools as easier versions for this, but not all work in the way you would expect and there are some new options at your disposal, too.

    ii. System restore on windows 10

    As with earlier versions of windows, system restore allows to ‘rewind’ windows installation to an earlier working state, without affecting the documents inside the computer. This is possible because windows automatically saves restore points when something significant happens, such as installing a windows update or a new application the idea being that if it goes wrong, the last restore point (or an even earlier one) can be returned back and get things performing as they were previously. The problem is that system restore is disabled by default in windows 10 and should therefore be enabled before benefiting from its features. Here is how to enable it:

    •Open system restore

    Search for system restore in the windows Operating System search box and select create a restore point from the list of results. When the system properties dialog box appears, click the system protection tab and then click the configure button. The following interface is coming from Microsoft Windows 10 Operating System.

    •Enable system restore

    Click to enable turn on system protection and then use the max usage slider to determine how much of your hard drive to use to store restore points — 5% to 10% is usually sufficient — and click OK. If you ever need to create a restore point manually, return to this dialog box and click the Create button, otherwise Microsoft windows 10 will create them automatically.

    •Restore your PC

    Whenever you want to return to a restore point, open the system properties dialog box again (see step 1), click the system protection tab and then click the System restore button. Follow the on-screen instructions and select the desired restore point when prompted. You can also click the scan for affected programs button before going any further, to see what might change on your PC afterwards. When you’re happy to proceed, click next.

    a. If system restore doesn’t work event

    Some serious windows problems can prevent you from rewinding to a restore point with system restore, but all is not lost. All you need to do is start windows 10 in safe mode. This bare bones windows mode only runs the essential parts of windows, which means any problematic apps, drivers or settings will be disabled. System restore will then usually be successful.

    1. Open advanced start-up

    Go to start > settings > update & security > recovery and click restart now below advanced start-up.


    2. Start system restore in safe mode

    Windows will then restart and display a choose an option menu. Select troubleshoot > advanced options > system restore and use system restore in the usual way.

    b. Recovering from more serious problems

    System restore won’t always rescue your PC from very serious problems, but windows 10 still has extra trick up its sleeve. It can restore windows to a factory fresh state without affecting your documents, although everything else (including apps) are removed. Even so, you should make sure you have a back up of your important files before using this option, just in case.

    1. Open reset this PC

    Go to start > settings > update & security > recovery and click get started below reset this PC.

    2. Reset windows 10, but save your files

    On the next screen, click keep my files and follow the on-screen instructions to reset windows 10. You’ll see a list of apps that will be removed and be asked to confirm your choice before going any further.

    3. Reset this PC from safe mode

    As with system restore, serious windows 10 problems can prevent reset this PC from working, but you can also run it from safe mode to bypass this. Follow step 1 under if system restore won’t work above, but instead choose troubleshoot > reset this PC > keep my files under step 2.

    APPLICATION ACTIVITY 1.6

    1. By using clear example, compare access control and parental control

    2. In a bank, any customer can create and use an identity (e.g., a user name and password) to log into that bank's online service but the bank's policy must ensure that the user can only access to his/her individual ac-count online once his/her identity is verified.a. Identify which type of security is used in this bank?b. Why is it important?

    3. What is the purpose of creating different computer users?

    4. After anti-virus installation, where do you place the unusable CD and CD box?

    5. At your school, the anti-virus used or installed in computers it was for haw many users?

    Suppose that, the anti-virus keys are stolen by someone else to use it on network. What can be the consequences for that anti-virus users in that school? How the computer users can avoid that illegal action?

    6. In the computer lab, configure the firewall

    END UNITY ASSESSMENT

    Part 1. Written

    1. What is computer security? What is the purpose of computer security?

    2. With an example, explain how you can protect a computer from physical threats?

    3. Use three characters in an information exchange scenario; we use computers called Mulisa, Ndoli, and Kamana. Mulisa is the person who needs to send secure data.Ndoli is the recipient of the data. Kamana is the person who somehow disturbs the communication between Mulisa and Ndoli by intercepting messages to uncover the data or by sending her own disguised messages.

        a. In the scenario, identify to whom belong clear text, plain text and ciphertext

        b. Differentiate cipher tex to plaintext

    4. Explain the difference between finger scanning and finger vein ID

    5. By using example, explain access control in authorization

    6. Which type of attack that enable a computer user to access his/her e-mail address?

    7. What do you understand by social engineering technique? Give examples

    8. It is heard that some web site become hacked by unknown people. Which strategies can be used to avoid young Rwandan programmers to engage in that action?

    9. Using an arrow match the following in Group A with their corresponding in B

    Part 2 Practice

    In school computer lab, every student takes a computer and do the following activities:

    1. With one external hard disk, make a backup of the local disk drive C

    2. Update the anti-virus found on the computers

    3. Add a new user on the computer

    Files: 3
  • UNIT 2: LAN ARCHITECTURE, NETWORK PROTOCOLS AND MODELS

    Key Unit Competency: To be able to identify computer network models, protocols and configure network devices

    Introductory Activity

    Look at figure 2.1 below and answer the following questions:

                                     

    1. Describe what you see.

    2. Are the above computers communicating? How and why?

    3. In which case the communication may not be possible?

    4. What type of network does the figure above represent?

    5. How the Computers A and B are connected?

    6. Is there any other way of connecting A, B and C

    2.1 LAN architecture

    Activity 2.1:

    Visit your school computer lab and look at the existing Network and answer the followings questions:

    1. Describe how computers are connected to the Network?

    2. Determine the type of the logical or physical arrangement of network devices (nodes) in that network.

    2.1.1 Definition of LAN Architecture?

    A Local Area Network (LAN) architecture is the overall design of a computers network that interconnects computers within a limited area such as a residence, school, laboratory, university campus of office building. The LAN architecture consists of three levels: Physical, Media Access Control (MAC) and Logical Link Control (LLC).

    •The LLC provides connection management, if needed.

    •The Media Access Control (MAC) is a set of rules for accessing high speed physical links and for transferring data frames from one computer to another in a network.

    •The Physical level deals mainly with actual transmission and reception of bits over the transmission medium.

    2.1.2 Major Components of LANs

    A LAN is made of the following main components:

    -Hardware:

    ◊ Computers

    ◊ Network interface card (NIC) linked to physical address

    ◊ Media or Cables (Unshielded twisted pair, Coaxial cable, Optical fiber, Air for wireless)

    ◊ Hub, Switches, repeaters

    -Access Methods: Rules that define how a computer puts data on and takes it from the network cable.

    -Software: Programs to access and / or to manage the network.

    2.1.3 Aspects of LAN architecture.

    These aspects include:

    -LAN’s physical topology: defines how the nodes of the network are physically connected

    -LAN’s logical topology: how data is transmitted between nodes

    -LAN’s MAC protocol: used for the physical identification of different devices within the network

    2.1.4 Ethernet

    Ethernet is a family of computer networking technologies commonly used in local area networks, metropolitan area networks and wide area networks. Ethernet cable is one of the most popular forms of network cable used in wired networks. They connect devices together within a local area network like PCs, routers and switches. A standard Ethernet network can transmit data at a rate up to 10 Megabits per second (10 Mbps). Ethernet uses CSMA/CD (Carrier Sense multiple Access with Collision Detection)

    2.1.5 Carrier Sense Multiple Access with Collision Detection (CSMA/CD)

    In a LAN, computers transmit data to each other. Normally, there is order to follow so that two computers can not send data at the same time while they are using the same route. When it happens that two computers send messages at the same time, there is what we call data collision. Therefore, a data collision occurs when two or more computers send data at the same time. When this happens, each computer stops data transmission and waits to resend it when the cable is free. Carrier Sense Multiple Access with Collision Detection (CSMA/CD) is a set of rules determining how network devices respond to a collision.

    How does the CSMA/CD work?

    Consider the following picture:

                              

    On the figure above, host A is trying to communicate with host B. Host A “senses” the wire and decides to send data. But, in the same time, host D sends its data to host C and the collision occurs. The sending devices (host A and host D) detect the collision and resend the data after a random period of time.

    When a collision occurs on an Ethernet LAN, the following happens:

    •A jam signal informs all devices that a collision occurred.

    A signal sent by a device on an Ethernet network to indicate that a collision has occurred on the network is called a jam signal.

    •The collision invokes a random back off algorithm (a set of rules which controls when each computer resend the data in order to assure that no more collision will happen again).

    •Each device on the Ethernet segment stops transmitting for a short time until the timers expire.

    •All hosts have equal priority to transmit after the timers have expired.

    Application activity 2.1:

    1. Realize a physical topology using devices like router, switches, Hubs, Ethernet cables and 4 computers available in your school computer lab as indicated in Fig. 2.2.

    2. Describe how does the CSMA/CD enable the communication over Ethernet?

    2.2 Cable Ethernet Standards

    Activity 2.2:

    Look around your school computer lab and answer the following question:Observe and describe the communication media (different types of Cables) available there.

    2.2.1 Definition of standard

    Standards provide guidelines to manufacturers, vendors, government agencies, and other service providers in guaranteeing national and international interoperability of data and telecommunications technology and processes. With Ethernet technologies, different types of standards have been so far used in networks.

    The different Ethernet technologies used in wired networks to connect computers are given in the following table. The choice of one or another type depends on the size of networks and the quantity of data to exchange.

    10BASE-F

    10BASE-F is a generic term for the family of 10 Mbit/s Ethernet standards using fiber optic cable. In 10BASE-F, the 10 represents its maximum throughput of 10 Mbit/s, BASE indicates its use of base band transmission, and F indicates that it relies on medium of fiber-optic cable. In fact, there are at least three different kinds of 10BASE-F. All require two strands of 62.5/125 μm multimode fiber.One strand is used for data transmission and one strand is used for reception, making 10BASE-F a full-duplex technology.

    The 10BASE-F variants include 10BASE-FL, 10BASE-FB and 10BASE-FP. Of these only 10BASE-FL experienced widespread use. All 10BASE-F variants deliver 10 Mbit/s over a fiber pair. These 10 Mbit/s standards have been largely replaced by faster Fast Ethernet, Gigabit Ethernet and 100 Gigabit Ethernet standards.

    10BASE-FL

    10BASE-FL is the most commonly used 10BASE-F specification of Ethernet over optical fiber. In 10BASE-FL, FL stands for fiber optic link. It replaces the original fiber-optic inter-repeater link (FOIRL) specification, but retains compatibility with FOIRL-based equipment. The maximum segment length supported is 2000 meters.When mixed with FOIRL equipment, maximum segment length is limited to FOIRL's 1000 meters.

    Today, 10BASE-FL is rarely used in networking and has been replaced by the family of Fast Ethernet, Gigabit Ethernet and 100 Gigabit Ethernet standards.

    10BASE-FB

    The 10BASE-FB (10BASE-FiberBackbone) is a network segment used to bridge Ethernet hubs. Due to the synchronous operation of 10BASE-FB, delays normally associated with Ethernet repeaters are reduced, thus allowing segment distances to be extended without compromising the collision detection mechanism. The maximum allowable segment length for 10BASE-FB is 2000 meters.

    10BASE-FP

    10BASE-FP calls for a non-powered signal coupler capable of linking up to 33 devices, with each segment being up to 500m in length. This formed a star-type network centered on the signal coupler. There are no devices known to have implemented this standard.

    2.2.1 Wireless network standards

    Wireless LANs (WLANs) use radio frequencies (RFs) that are radiated into the air from an antenna that creates radio waves.

    Because WLANs transmit over radio frequencies, they are regulated by the same types of laws used to govern things like AM/FM radios. It is the Federal Communications Commission (FCC) that regulates the use of wireless LAN devices, and the IEEE takes it from there and creates standards based on what frequencies the FCC releases for public use.

    The wireless standards like the Ethernet standards are applied in different situations. The table below clearly describes each type.

    2.2.3 Range, bandwidth and frequency

    One characteristic that measures network performance is bandwidth. The bandwidth reflects the range of frequencies we need. However, the term can be used in two different contexts with two different measuring values: bandwidth in hertz and bandwidth in bits per second.

    a. Bandwidth in Hertz

    Bandwidth in hertz is the range of frequencies contained in a composite signal or the range of frequencies a channel can pass. For example, we can say the bandwidth of a subscriber telephone line is 4 kHz.

    b.Bandwidth in Bits per Seconds

    The term bandwidth can also refer to the number of bits per second that a channel, a link, or even a network can transmit per second. For example, one can say the bandwidth of a Fast Ethernet network is a maximum of 100 Mbps. This means that this network can send 100 Megabits per second.

    2.2.3.1 Frequency and Network Range

    The higher the frequency of a wireless signal, the shorter its range. 2.4 GHz wireless networks therefore cover a significantly larger range than 5 GHz networks. In particular, signals of 5 GHz frequencies do not penetrate solid objects nearly as well as do 2.4 GHz signals, limiting their reach inside homes.

    Many older Wi-Fi devices do not contain 5 GHz radios and so must be connected to 2.4 GHz channels in any case.

    2.2.3.2 Range, Bandwidth and Frequency

    •The term ‘Bandwidth’ refers to the speed at which data is transferred over the wireless network (more bandwidth means faster downloading and uploading)

    •The term ‘Range’ refers to the maximum distance from the router at which the network can be received (the greater the range, the further you can be from the router and still be connected).

    •The term ‘Frequency’ refers to the number of waves that pass a fixed place in a given amount of time. So if the time it takes for a wave to pass is is 1/2 second, the frequency is 2 per second. If it takes 1/100 of an hour, the frequency is 100 per hour.

    Usually frequency is measured in the hertz unit, named in honor of the 19th-century German physicist Heinrich Rudolf Hertz. The hertz measurement, abbreviated Hz, is the number of waves that pass by per second. For example, an "A" note on a violin string vibrates at about 440 Hz (440 vibrations per second).

    2.2.3.3 Advantages and Disadvantages of the 2.4 GHz and the 5 GHz Wireless Networks



    2.2.3.4 Token ring

    Token ring or IEEE 802.5 is a network where all computers are connected in a circular fashion. The term token is used to describe a segment of information that is sent through that circle. When a computer on the network can decode that token, it receives data.

    A Multistation Access Unit (MSAU) is a hub or concentrator that connects a group of computers ("nodes" in network terminology) to a token ring local area network. For example, eight computers might be connected to an MSAU in one office and that MSAU would be connected to an MSAU in another office that served eight other computers. In turn, that MSAU could be connected to another MSAU in another office, which would be connected back to the first MSAU. Such a physical configuration is called a star topology. However, the logical configuration is a ring topology because every message passes through every computer one at a time, each passing it on to the next in a continuing circle.

    Application activity 2.2:

    Your school has classrooms, computer labs and the staff room located in three different buildings as indicated in the figure below:

    Questions:

    1. Which kind of technology can you propose to connect computers in the 3 buildings. Explain you choice.

    2. If you choose to install a wireless network within this school, in which building would you place the wireless device which serves the whole school? Explain.

    3. What type of Ethernet cable would you use if you are requested to interconnect those three buildings? Explain.

    2.3 Fiber Distributed Data Interface (FDDI)


    2.3.1 Definition

    The Fiber Distributed Data Interface (FDDI) is a standard developed by the American National Standards Institute (ANSI) for transmitting data on optical fiber cables. FDDI supports transmission rates of 100 megabits per second on token-passing networks.

    FDDI provides high-speed network backbones that can be used to connect and extend LANs.

    2.3.2 Advantages of FDDI

    The Fiber Distributed Data Interface allows the transmission of very large volumes of data over large distances. It provides high bandwidth.

    2.3.3 Disadvantages

    The Fiber Distributed Data Interface (FDDI) is an expensive technology to set up because the network devices require a special network card and also the required fiber optic cabling is expensive than twisted-pair cable. Because most Fiber Distributed Data Interface (FDDI) installations use a redundant second ring, more cabling is required.

    2.3.4 Fiber Optic cables

    A fiber optic cable is a glass or plastic strand that transmits information using light and is made up of one or more optical fibers enclosed together in a sheath or jacket. It has the following properties:

    •Not affected by electromagnetic or radio frequency interference.

    •All signals are converted to light pulses to enter the cable, and converted back into electrical signals when they leave it.

    •Signals are clearer, can go farther, and have greater bandwidth than with copper cable.

    •Signal can travel several miles or kilometers before the signal needs to be regenerated.

    •Usually more expensive to use than copper cabling and the connectors are more costly and harder to assemble.

    •Common connectors for fiber-optic networks are SC, ST, and LC. These three types of fiber optic connectors are half-duplex, which allows data to flow in only one direction.

    Therefore, two cables are needed.

    a. Types of fiber optic

    There are three types of fiber optic cable commonly used: single mode, multimode and plastic optical fiber (POF).

    1. Single-mode: Cable that has a very thin core. It is harder to make, uses lasers as a light source, and can transmit signals dozens of kilometers with ease.

    2. Multimode: Cable that has a thicker core than single-mode cable. It is easier to make, can use simpler light sources (LEDs), and works well over distances of a few kilometers or less.

    3. Plastic optical fiber (POF):Transparent glass or plastic fibers which allow light to be guided from one end to the other with minimal loss.

    The Fiber optic technologies are summarized in the following table.

    Application activity 2.3:

    4. Discuss the advantages and disadvantages of FDDI within a Local Area Network.

    5. Referring to the figure on learning activity 2.3, what type of fiber optic cable would you recommend for the core and distribution layers? Explain.

    2.4 Network devices

    Activity 2.4.

    In groups, look at the devices given below and answer the questions:

    6. Describe the role of each one within a Local Area Network.

    7. Explain how you can make a Local Area Network using the following devices?

    There are many networking devices: NIC cards, Repeaters, HUB, Bridges, Switches and Router

    2.4.1 Wireless LAN cards (Network adapters)

    Also called Network Interface Cards (NICs), they are connectivity devices enabling a desktop, server, printer, or other node to receive and transmit data over the network media

    a. Types of Wireless Network Interface Cards (NICs)

    NICs come in a variety of types depending on:

    -The access method (for example, Ethernet versus Token Ring)

    -Network transmission speed (for example, 100 Mbps versus 1 Gbps)

    -Connector interfaces (for example, RJ-45 versus SC)

    -Type of compatible motherboard or device (for example, PCI)

    -Manufacturer (popular NIC manufacturers include 3Com, Adaptec, D

    -Link, IBM, Intel,

    -Kingston, Linksys, and so on)

    b.Wireless NIC card installation and configuration

    -Refer to the card manufacturer's quick

    -start guide. Alternatively, you can also run the software installation program on the CD which comes with the PCI card and observe the steps to install it.

    -Shut down the PC.

    -Remove the cover.

    -Locate an available PCI slot and remove the corresponding slot cover from the back of the PC.

    -Carefully route the antenna through the open slot in the back of the PC, insert the card in the slot, and secure it. Replace the cover.

    -Power up the PC. It should recognize and enable the new hardware.

    c. Wireless NIC card Driver installation through the Device Manager

    Step 1: Right-click on Computer (or PC) to select Manage.

    Step 2: On the left, select Device Manager to bring it up on the right.

    Step 3: Right click on the unknown adapter to Update Driver Software.

    Step 4: Click to Search automatically for updated driver software.

    Step 5: Wait until the download process is successfully completed.

    Step 6: Click on Save Settings or OK to apply the change.Confirmation of a successful Driver installation is achieved when the model of your adapter is labeled and listed in the Network adapters group of the Device Manager.

    2.4.2 Routers and Access points

    A wireless router is a device that performs the functions of a router and also includes the functions of a wireless access point. It is used to provide access to the Internet or a private computer network. Routers operate at the Network layer (Layer 3) of the OSI Model.

    The Wireless access points (APs or WAPs) are networking devices that allow wireless Wi-Fi devices to connect to a wired network.

    2.4.3 Configuring a wireless router

    Step 1: Get to know your wireless router

    •A power input jack one.

    •One or more wired Ethernet jacks (often labeled 1, 2, 3, 4) for computers on your network which don't have wireless ability.

    •One Ethernet jack for your broadband connection, often labeled “WAN” or “Internet.”

    •A reset button. to

    Step 2: Connect your router a wired PC for initial setup

    Step 3: Open web browser and connect to wireless router administration INTERFACE

    To connect to your router, you need to know its default IP address and connect your browser to http://routeripaddress. For example, if you own a Linksys brand wireless router, its default IP address is 192.168.1.1, and therefore you open your browser to the URL http://192.168.1.1.

    Most wireless routers also require you to log in to access configuration pages. Your router includes a manual or a "quick setup" guide which details both its default IP address and default login.

    Step 4: Determine your broadband type

    There are three common broadband connection methods:

    •DHCP Dynamic IP: Basic network parameters are automatically assigned to your router by the broadband modem.

    •PPPoE: Requires you to supply a username and password provided to you by your ISP.

    •Static IP: Your broadband provider would have supplied you with a set of numeric addresses you need to connect to the network, as they are not assigned automatically.

    Step 5: Configure your broadband connection

    On this model, you clicked the "Setup" menu and "Basic setup" sub menu. Again, your model may differ, and newer models may include a guided wizard that takes you through these steps.

    Step 6: Configure your wireless network basics

    If your router is connected to broadband and it is working successfully, we can setup the wireless networking configuration. On our sample router we clicked the "Wireless" sub menu.

    Assign your wireless network a name, also known as Service Set Identifier (SSID). Choose a unique name in case there may be neighboring wireless routers nearby.

    Step 7: Configure your wireless security

    Most wireless network users will select one of four degrees of encryption security available in wireless hardware today.

    1. WEP: The oldest and least secure data encryption. All wireless gear supports WEP, though, it is useful when you need at least some kind of encryption to be compatible with older wireless hardware.

    2. WPA: A more secure upgrade to WEP. Designed so that many older devices which included only WEP can be upgraded to support WPA.

    3. WPA2: A significantly more secure upgrade to either WEP or WPA. Cannot upgrade older hardware to WPA2, but many new wireless devices support WPA2.

    Note: At each step you must click on the “save Settings” button before you proceed with the next step

    2.4.3.1 Router Operation Mode

    Many of the routers offers different operation modes that you can use.

    a. Wireless Router Mode

    In wireless router/ IP sharing mode, the router connects to the Internet via PPPoE, DHCP, PPTP, L2TP, or Static IP and shares the wireless network to LAN clients or devices. Select this mode if you are a first-time user or you are not currently using any wired/wireless routers.

    b.Repeater Mode

    In Repeater mode, your router wirelessly connects to an existing wireless network to extend the wireless coverage. You will generally use repeaters or wireless extenders

    when you have hard to reach places with your home Wi-Fi setup.

    c. Access Point (AP) Mode

    In Access Point (AP) mode, the router connects to a wireless router through an Ethernet cable to extend the wireless signal coverage to other network clients. This mode is best to be used in an office, hotel, and places where you only have wired network.

    d.Media Bridge or Client Mode

    With client mode or media bridge, it can connect to a wired device and works as a wireless adapter to receive wireless signal from your wireless network. The reason for this mode is that it can increase the speed of your wireless connection so that it matches the speed of the Ethernet connection.

    2.4.3.2 Default gateway

    A default gateway is used to allow devices in one network to communicate with devices in another network. If your computer, for example, is requesting an Internet webpage, the request first runs through your default gateway before exiting the local network to reach the Internet.An easier way to understand a default gateway might be to think of it as an intermediate device between the local network and the Internet.

    a. Configuring the default gateway on a wireless router

    Start packet tracer, add a wireless router and do the following:

    -Click on wireless router and go to GUI tab.

    -Set the Internet Connection type to Static IP.

    -Configure the IP addressing according to the figure below.

    -Scroll down and click on Save Settings.

    b.How to find your default gateway IP address

    You might need to know the IP address of the default gateway if there is a network problem or if you need to make changes to your router.

    -In Microsoft Windows, the IP address of a computer's default gateway can be accessed through Command Prompt with the ipconfig command, as well as through the Control Panel.

    -The netstat and ip route commands are used on macOS and Linux for finding the default gateway address.

    c. Configuring a default gateway on a desktop

    -Open the control panel-Click on Network and Internet

    -Click on Network and sharing center

    -Click on adapter settings

    -Right click on wi-fi and choose properties

    Key Unit Competency: To be able to identify computer network models, protocols and configure network devices

    Introductory Activity

    Look at figure 2.1 below and answer the following questions:

                                     

    1. Describe what you see.

    2. Are the above computers communicating? How and why?

    3. In which case the communication may not be possible?

    4. What type of network does the figure above represent?

    5. How the Computers A and B are connected?

    6. Is there any other way of connecting A, B and C

    2.1 LAN architecture

    Activity 2.1:

    Visit your school computer lab and look at the existing Network and answer the followings questions:

    1. Describe how computers are connected to the Network?

    2. Determine the type of the logical or physical arrangement of network devices (nodes) in that network.

    2.1.1 Definition of LAN Architecture?

    A Local Area Network (LAN) architecture is the overall design of a computers network that interconnects computers within a limited area such as a residence, school, laboratory, university campus of office building. The LAN architecture consists of three levels: Physical, Media Access Control (MAC) and Logical Link Control (LLC).

    •The LLC provides connection management, if needed.

    •The Media Access Control (MAC) is a set of rules for accessing high speed physical links and for transferring data frames from one computer to another in a network.

    •The Physical level deals mainly with actual transmission and reception of bits over the transmission medium.

    2.1.2 Major Components of LANs

    A LAN is made of the following main components:

    -Hardware:

    ◊ Computers

    ◊ Network interface card (NIC) linked to physical address

    ◊ Media or Cables (Unshielded twisted pair, Coaxial cable, Optical fiber, Air for wireless)

    ◊ Hub, Switches, repeaters

    -Access Methods: Rules that define how a computer puts data on and takes it from the network cable.

    -Software: Programs to access and / or to manage the network.

    2.1.3 Aspects of LAN architecture.

    These aspects include:

    -LAN’s physical topology: defines how the nodes of the network are physically connected

    -LAN’s logical topology: how data is transmitted between nodes

    -LAN’s MAC protocol: used for the physical identification of different devices within the network

    2.1.4 Ethernet

    Ethernet is a family of computer networking technologies commonly used in local area networks, metropolitan area networks and wide area networks. Ethernet cable is one of the most popular forms of network cable used in wired networks. They connect devices together within a local area network like PCs, routers and switches. A standard Ethernet network can transmit data at a rate up to 10 Megabits per second (10 Mbps). Ethernet uses CSMA/CD (Carrier Sense multiple Access with Collision Detection)

    2.1.5 Carrier Sense Multiple Access with Collision Detection (CSMA/CD)

    In a LAN, computers transmit data to each other. Normally, there is order to follow so that two computers can not send data at the same time while they are using the same route. When it happens that two computers send messages at the same time, there is what we call data collision. Therefore, a data collision occurs when two or more computers send data at the same time. When this happens, each computer stops data transmission and waits to resend it when the cable is free. Carrier Sense Multiple Access with Collision Detection (CSMA/CD) is a set of rules determining how network devices respond to a collision.

    How does the CSMA/CD work?

    Consider the following picture:

                              

    On the figure above, host A is trying to communicate with host B. Host A “senses” the wire and decides to send data. But, in the same time, host D sends its data to host C and the collision occurs. The sending devices (host A and host D) detect the collision and resend the data after a random period of time.

    When a collision occurs on an Ethernet LAN, the following happens:

    •A jam signal informs all devices that a collision occurred.

    A signal sent by a device on an Ethernet network to indicate that a collision has occurred on the network is called a jam signal.

    •The collision invokes a random back off algorithm (a set of rules which controls when each computer resend the data in order to assure that no more collision will happen again).

    •Each device on the Ethernet segment stops transmitting for a short time until the timers expire.

    •All hosts have equal priority to transmit after the timers have expired.

    Application activity 2.1:

    1. Realize a physical topology using devices like router, switches, Hubs, Ethernet cables and 4 computers available in your school computer lab as indicated in Fig. 2.2.

    2. Describe how does the CSMA/CD enable the communication over Ethernet?

    2.2 Cable Ethernet Standards

    Activity 2.2:

    Look around your school computer lab and answer the following question:Observe and describe the communication media (different types of Cables) available there.

    2.2.1 Definition of standard

    Standards provide guidelines to manufacturers, vendors, government agencies, and other service providers in guaranteeing national and international interoperability of data and telecommunications technology and processes. With Ethernet technologies, different types of standards have been so far used in networks.

    The different Ethernet technologies used in wired networks to connect computers are given in the following table. The choice of one or another type depends on the size of networks and the quantity of data to exchange.

    10BASE-F

    10BASE-F is a generic term for the family of 10 Mbit/s Ethernet standards using fiber optic cable. In 10BASE-F, the 10 represents its maximum throughput of 10 Mbit/s, BASE indicates its use of base band transmission, and F indicates that it relies on medium of fiber-optic cable. In fact, there are at least three different kinds of 10BASE-F. All require two strands of 62.5/125 μm multimode fiber.One strand is used for data transmission and one strand is used for reception, making 10BASE-F a full-duplex technology.

    The 10BASE-F variants include 10BASE-FL, 10BASE-FB and 10BASE-FP. Of these only 10BASE-FL experienced widespread use. All 10BASE-F variants deliver 10 Mbit/s over a fiber pair. These 10 Mbit/s standards have been largely replaced by faster Fast Ethernet, Gigabit Ethernet and 100 Gigabit Ethernet standards.

    10BASE-FL

    10BASE-FL is the most commonly used 10BASE-F specification of Ethernet over optical fiber. In 10BASE-FL, FL stands for fiber optic link. It replaces the original fiber-optic inter-repeater link (FOIRL) specification, but retains compatibility with FOIRL-based equipment. The maximum segment length supported is 2000 meters.When mixed with FOIRL equipment, maximum segment length is limited to FOIRL's 1000 meters.

    Today, 10BASE-FL is rarely used in networking and has been replaced by the family of Fast Ethernet, Gigabit Ethernet and 100 Gigabit Ethernet standards.

    10BASE-FB

    The 10BASE-FB (10BASE-FiberBackbone) is a network segment used to bridge Ethernet hubs. Due to the synchronous operation of 10BASE-FB, delays normally associated with Ethernet repeaters are reduced, thus allowing segment distances to be extended without compromising the collision detection mechanism. The maximum allowable segment length for 10BASE-FB is 2000 meters.

    10BASE-FP

    10BASE-FP calls for a non-powered signal coupler capable of linking up to 33 devices, with each segment being up to 500m in length. This formed a star-type network centered on the signal coupler. There are no devices known to have implemented this standard.

    2.2.1 Wireless network standards

    Wireless LANs (WLANs) use radio frequencies (RFs) that are radiated into the air from an antenna that creates radio waves.

    Because WLANs transmit over radio frequencies, they are regulated by the same types of laws used to govern things like AM/FM radios. It is the Federal Communications Commission (FCC) that regulates the use of wireless LAN devices, and the IEEE takes it from there and creates standards based on what frequencies the FCC releases for public use.

    The wireless standards like the Ethernet standards are applied in different situations. The table below clearly describes each type.

    2.2.3 Range, bandwidth and frequency

    One characteristic that measures network performance is bandwidth. The bandwidth reflects the range of frequencies we need. However, the term can be used in two different contexts with two different measuring values: bandwidth in hertz and bandwidth in bits per second.

    a. Bandwidth in Hertz

    Bandwidth in hertz is the range of frequencies contained in a composite signal or the range of frequencies a channel can pass. For example, we can say the bandwidth of a subscriber telephone line is 4 kHz.

    b.Bandwidth in Bits per Seconds

    The term bandwidth can also refer to the number of bits per second that a channel, a link, or even a network can transmit per second. For example, one can say the bandwidth of a Fast Ethernet network is a maximum of 100 Mbps. This means that this network can send 100 Megabits per second.

    2.2.3.1 Frequency and Network Range

    The higher the frequency of a wireless signal, the shorter its range. 2.4 GHz wireless networks therefore cover a significantly larger range than 5 GHz networks. In particular, signals of 5 GHz frequencies do not penetrate solid objects nearly as well as do 2.4 GHz signals, limiting their reach inside homes.

    Many older Wi-Fi devices do not contain 5 GHz radios and so must be connected to 2.4 GHz channels in any case.

    2.2.3.2 Range, Bandwidth and Frequency

    •The term ‘Bandwidth’ refers to the speed at which data is transferred over the wireless network (more bandwidth means faster downloading and uploading)

    •The term ‘Range’ refers to the maximum distance from the router at which the network can be received (the greater the range, the further you can be from the router and still be connected).

    •The term ‘Frequency’ refers to the number of waves that pass a fixed place in a given amount of time. So if the time it takes for a wave to pass is is 1/2 second, the frequency is 2 per second. If it takes 1/100 of an hour, the frequency is 100 per hour.

    Usually frequency is measured in the hertz unit, named in honor of the 19th-century German physicist Heinrich Rudolf Hertz. The hertz measurement, abbreviated Hz, is the number of waves that pass by per second. For example, an "A" note on a violin string vibrates at about 440 Hz (440 vibrations per second).

    2.2.3.3 Advantages and Disadvantages of the 2.4 GHz and the 5 GHz Wireless Networks



    2.2.3.4 Token ring

    Token ring or IEEE 802.5 is a network where all computers are connected in a circular fashion. The term token is used to describe a segment of information that is sent through that circle. When a computer on the network can decode that token, it receives data.

    A Multistation Access Unit (MSAU) is a hub or concentrator that connects a group of computers ("nodes" in network terminology) to a token ring local area network. For example, eight computers might be connected to an MSAU in one office and that MSAU would be connected to an MSAU in another office that served eight other computers. In turn, that MSAU could be connected to another MSAU in another office, which would be connected back to the first MSAU. Such a physical configuration is called a star topology. However, the logical configuration is a ring topology because every message passes through every computer one at a time, each passing it on to the next in a continuing circle.

    Application activity 2.2:

    Your school has classrooms, computer labs and the staff room located in three different buildings as indicated in the figure below:

    Questions:

    1. Which kind of technology can you propose to connect computers in the 3 buildings. Explain you choice.

    2. If you choose to install a wireless network within this school, in which building would you place the wireless device which serves the whole school? Explain.

    3. What type of Ethernet cable would you use if you are requested to interconnect those three buildings? Explain.

    2.3 Fiber Distributed Data Interface (FDDI)


    2.3.1 Definition

    The Fiber Distributed Data Interface (FDDI) is a standard developed by the American National Standards Institute (ANSI) for transmitting data on optical fiber cables. FDDI supports transmission rates of 100 megabits per second on token-passing networks.

    FDDI provides high-speed network backbones that can be used to connect and extend LANs.

    2.3.2 Advantages of FDDI

    The Fiber Distributed Data Interface allows the transmission of very large volumes of data over large distances. It provides high bandwidth.

    2.3.3 Disadvantages

    The Fiber Distributed Data Interface (FDDI) is an expensive technology to set up because the network devices require a special network card and also the required fiber optic cabling is expensive than twisted-pair cable. Because most Fiber Distributed Data Interface (FDDI) installations use a redundant second ring, more cabling is required.

    2.3.4 Fiber Optic cables

    A fiber optic cable is a glass or plastic strand that transmits information using light and is made up of one or more optical fibers enclosed together in a sheath or jacket. It has the following properties:

    •Not affected by electromagnetic or radio frequency interference.

    •All signals are converted to light pulses to enter the cable, and converted back into electrical signals when they leave it.

    •Signals are clearer, can go farther, and have greater bandwidth than with copper cable.

    •Signal can travel several miles or kilometers before the signal needs to be regenerated.

    •Usually more expensive to use than copper cabling and the connectors are more costly and harder to assemble.

    •Common connectors for fiber-optic networks are SC, ST, and LC. These three types of fiber optic connectors are half-duplex, which allows data to flow in only one direction.

    Therefore, two cables are needed.

    a. Types of fiber optic

    There are three types of fiber optic cable commonly used: single mode, multimode and plastic optical fiber (POF).

    1. Single-mode: Cable that has a very thin core. It is harder to make, uses lasers as a light source, and can transmit signals dozens of kilometers with ease.

    2. Multimode: Cable that has a thicker core than single-mode cable. It is easier to make, can use simpler light sources (LEDs), and works well over distances of a few kilometers or less.

    3. Plastic optical fiber (POF):Transparent glass or plastic fibers which allow light to be guided from one end to the other with minimal loss.

    The Fiber optic technologies are summarized in the following table.

    Application activity 2.3:

    4. Discuss the advantages and disadvantages of FDDI within a Local Area Network.

    5. Referring to the figure on learning activity 2.3, what type of fiber optic cable would you recommend for the core and distribution layers? Explain.

    2.4 Network devices

    Activity 2.4.

    In groups, look at the devices given below and answer the questions:

    6. Describe the role of each one within a Local Area Network.

    7. Explain how you can make a Local Area Network using the following devices?

    There are many networking devices: NIC cards, Repeaters, HUB, Bridges, Switches and Router

    2.4.1 Wireless LAN cards (Network adapters)

    Also called Network Interface Cards (NICs), they are connectivity devices enabling a desktop, server, printer, or other node to receive and transmit data over the network media

    a. Types of Wireless Network Interface Cards (NICs)

    NICs come in a variety of types depending on:

    -The access method (for example, Ethernet versus Token Ring)

    -Network transmission speed (for example, 100 Mbps versus 1 Gbps)

    -Connector interfaces (for example, RJ-45 versus SC)

    -Type of compatible motherboard or device (for example, PCI)

    -Manufacturer (popular NIC manufacturers include 3Com, Adaptec, D

    -Link, IBM, Intel,

    -Kingston, Linksys, and so on)

    b.Wireless NIC card installation and configuration

    -Refer to the card manufacturer's quick

    -start guide. Alternatively, you can also run the software installation program on the CD which comes with the PCI card and observe the steps to install it.

    -Shut down the PC.

    -Remove the cover.

    -Locate an available PCI slot and remove the corresponding slot cover from the back of the PC.

    -Carefully route the antenna through the open slot in the back of the PC, insert the card in the slot, and secure it. Replace the cover.

    -Power up the PC. It should recognize and enable the new hardware.

    c. Wireless NIC card Driver installation through the Device Manager

    Step 1: Right-click on Computer (or PC) to select Manage.

    Step 2: On the left, select Device Manager to bring it up on the right.

    Step 3: Right click on the unknown adapter to Update Driver Software.

    Step 4: Click to Search automatically for updated driver software.

    Step 5: Wait until the download process is successfully completed.

    Step 6: Click on Save Settings or OK to apply the change.Confirmation of a successful Driver installation is achieved when the model of your adapter is labeled and listed in the Network adapters group of the Device Manager.

    2.4.2 Routers and Access points

    A wireless router is a device that performs the functions of a router and also includes the functions of a wireless access point. It is used to provide access to the Internet or a private computer network. Routers operate at the Network layer (Layer 3) of the OSI Model.

    The Wireless access points (APs or WAPs) are networking devices that allow wireless Wi-Fi devices to connect to a wired network.

    2.4.3 Configuring a wireless router

    Step 1: Get to know your wireless router

    •A power input jack one.

    •One or more wired Ethernet jacks (often labeled 1, 2, 3, 4) for computers on your network which don't have wireless ability.

    •One Ethernet jack for your broadband connection, often labeled “WAN” or “Internet.”

    •A reset button. to

    Step 2: Connect your router a wired PC for initial setup

    Step 3: Open web browser and connect to wireless router administration INTERFACE

    To connect to your router, you need to know its default IP address and connect your browser to http://routeripaddress. For example, if you own a Linksys brand wireless router, its default IP address is 192.168.1.1, and therefore you open your browser to the URL http://192.168.1.1.

    Most wireless routers also require you to log in to access configuration pages. Your router includes a manual or a "quick setup" guide which details both its default IP address and default login.

    Step 4: Determine your broadband type

    There are three common broadband connection methods:

    •DHCP Dynamic IP: Basic network parameters are automatically assigned to your router by the broadband modem.

    •PPPoE: Requires you to supply a username and password provided to you by your ISP.

    •Static IP: Your broadband provider would have supplied you with a set of numeric addresses you need to connect to the network, as they are not assigned automatically.

    Step 5: Configure your broadband connection

    On this model, you clicked the "Setup" menu and "Basic setup" sub menu. Again, your model may differ, and newer models may include a guided wizard that takes you through these steps.

    Step 6: Configure your wireless network basics

    If your router is connected to broadband and it is working successfully, we can setup the wireless networking configuration. On our sample router we clicked the "Wireless" sub menu.

    Assign your wireless network a name, also known as Service Set Identifier (SSID). Choose a unique name in case there may be neighboring wireless routers nearby.

    Step 7: Configure your wireless security

    Most wireless network users will select one of four degrees of encryption security available in wireless hardware today.

    1. WEP: The oldest and least secure data encryption. All wireless gear supports WEP, though, it is useful when you need at least some kind of encryption to be compatible with older wireless hardware.

    2. WPA: A more secure upgrade to WEP. Designed so that many older devices which included only WEP can be upgraded to support WPA.

    3. WPA2: A significantly more secure upgrade to either WEP or WPA. Cannot upgrade older hardware to WPA2, but many new wireless devices support WPA2.

    Note: At each step you must click on the “save Settings” button before you proceed with the next step

    2.4.3.1 Router Operation Mode

    Many of the routers offers different operation modes that you can use.

    a. Wireless Router Mode

    In wireless router/ IP sharing mode, the router connects to the Internet via PPPoE, DHCP, PPTP, L2TP, or Static IP and shares the wireless network to LAN clients or devices. Select this mode if you are a first-time user or you are not currently using any wired/wireless routers.

    b.Repeater Mode

    In Repeater mode, your router wirelessly connects to an existing wireless network to extend the wireless coverage. You will generally use repeaters or wireless extenders

    when you have hard to reach places with your home Wi-Fi setup.

    c. Access Point (AP) Mode

    In Access Point (AP) mode, the router connects to a wireless router through an Ethernet cable to extend the wireless signal coverage to other network clients. This mode is best to be used in an office, hotel, and places where you only have wired network.

    d.Media Bridge or Client Mode

    With client mode or media bridge, it can connect to a wired device and works as a wireless adapter to receive wireless signal from your wireless network. The reason for this mode is that it can increase the speed of your wireless connection so that it matches the speed of the Ethernet connection.

    2.4.3.2 Default gateway

    A default gateway is used to allow devices in one network to communicate with devices in another network. If your computer, for example, is requesting an Internet webpage, the request first runs through your default gateway before exiting the local network to reach the Internet.An easier way to understand a default gateway might be to think of it as an intermediate device between the local network and the Internet.

    a. Configuring the default gateway on a wireless router

    Start packet tracer, add a wireless router and do the following:

    -Click on wireless router and go to GUI tab.

    -Set the Internet Connection type to Static IP.

    -Configure the IP addressing according to the figure below.

    -Scroll down and click on Save Settings.

    b.How to find your default gateway IP address

    You might need to know the IP address of the default gateway if there is a network problem or if you need to make changes to your router.

    -In Microsoft Windows, the IP address of a computer's default gateway can be accessed through Command Prompt with the ipconfig command, as well as through the Control Panel.

    -The netstat and ip route commands are used on macOS and Linux for finding the default gateway address.

    c. Configuring a default gateway on a desktop

    -Open the control panel-Click on Network and Internet

    -Click on Network and sharing center

    -Click on adapter settings

    -Right click on wi-fi and choose properties


    -Choose Internet Protocol Version 4 (TCP/IPv4) and click on properties

    -Enter IP address as follows and then click on OK:

                           

    2.4.4 Public and private IP2.

    4.4.1 Public IP addresses

    A public IP address is the one that your ISP (Internet Service Provider) provides to identify your home network to the outside world. It is an IP address that is unique throughout the entire Internet. A public IP address is worldwide unique, and can only be assigned to a unique device

    Depending on your service, you might have an IP address that never changes (a fixed or static IP address). But most ISPs provide an IP address that can change from time to time (a dynamic IP address)

    Example: Web and email servers directly accessible from the Internet use public IP addresses.

    2.4.4.2 Private IP addresses

    A private IP address provides unique identification for devices that are within your Local Area Network, such as your computer, your smartphones, and so on.If every device on every network had to have real routable public IP addresses, we would have run out of IP addresses to hand out years ago. Private IP addresses are used for the following reasons:

    -To create addresses that cannot be routed through the public Internet

    -To conserve public addresses

    Examples:

    -Computers, tablets and smartphones within an organization are usually assigned private IP addresses.

    -A network printer residing in your school computer lab is assigned a private address so that only users within computer lab can print to your local printer.

    -Notice that IP addresses, public or private, are assigned to devices according to network classes. The most used classes are A, B and C. They differ according to the number of networks and hence to the number of IP addresses in one network. From A to C, the number of possible networks increase while number of available IP addresses in a network reduces.


    2.4.5 Configuring a wireless Access Point

    The physical setup for a wireless access point is pretty simple: you take it out of the box, put it on a shelf or on top of a bookcase near a network jack and a power outlet, plug in the power cable, and plug in the network cable.

    To get to the configuration page for the access point, you need to know the access point’s IP address. Then, you just type that address into the address bar of a browser from any computer on the network.

    For example to configure TP-Link TL-WA701ND Access Point you will follow the following steps:

    Step 1: Power the TP-Link TL-WA701ND using the barrel jack or PoE (Power-over-Ethernet) injector, and connect a computer to the access point using an Ethernet cable (if using the PoE injector, connect the LAN port to your computer, and the POE port to the access point).

    Step 2: Ensure all wireless interfaces are disabled on the computer (such as WiFi and Bluetooth) and that DHCP is enabled on the Ethernet interface. Open a web browser and access the TL-WA701ND by entering 192.168.0.254 into the address bar.

    Step 3: Log in using username admin and password admin

    Step 4: The Quick Setup wizard will load in the browser. Click Next to start the configuration process.

    Step 5: Select Client from the list of operating modes. Click Next.

    Step 6: Click Survey to scan for a list of available wireless access points. Alternatively, skip to step 8 and manually enter information.

    Step 7: From the list of available WiFi networks, select the network to use by clicking Connect to the far right. Make sure the network has a good connection by checking the signal strength. The higher the number, the stronger the connection.

    Step 8: Once the Connect option is clicked, these fields will automatically fill in. Alternatively, enter the Wireless Name (SSID) and Wireless Security Mode and Wireless Password. The wireless security settings will need to be manually entered for any password protected WiFi network. Click Next.

    Step 9: The default values are typically fine for these settings. If needed, obtain the correct settings from the network administrator. Be sure to make a note/take a screenshot of the IP address set in this step, as it will replace the original fallback IP address. When the correct settings have been applied, click Next.

    Step 10: Make a note or take a screenshot of the applied settings if desired, then click Save.

    Step 11: The device will reboot. The configuration page will likely not load; try loading a web-page (e.g. http://www.irembo.gov.rw) while the TP-Link Access Point is connected to the computer to see if there is Internet connectivity.

    Step 12: Troubleshooting

    •The TP-Link TP-WA701ND does not have any LEDs illuminated

         * Ensure the access point has power either directly to the barrel jack on the back, or via the POE injector’s POE Ethernet port. The POE injector requires power via barrel jack.

          * Verify the ON/OFF button next to the access point’s Ethernet port is depressed in the ON position.

    •I cannot access the device configuration page.

           * The TP-Link WA701ND has a default fallback IP address of 192.168.0.254. To access the device configuration pages, connect a computer directly via an Ethernet cable, configure the computer to use an IPv4 address in the same range (for example, 192.168.0.100), open a web browser, and enter the fallback IP address of 192.168.0.254 in the address bar. If you changed the IP address on the Network Setting page during configuration step 9, use that IP address instead.

    •I cannot access the device at all (lost credentials, major configuration issue, etc)

         * The TP-Link TL-WA701ND has a recessed reset button located on the back of the device. This button is closest to the antenna, and a pin or paperclip is needed to press it. Hold the button down for 8+ seconds. All of the LEDs should turn off and back on; after this the initial configuration steps can be used to gain access. Note that this will reset all device settings to the factory default.

    2.4.5 How to connect to the Internet through your wireless access point?

    a. Connecting to Internet through the control panel

    -Open the windows control panel, and then click network and Internet.

    -The Network and Internet window appears.

    -Click network and sharing center.

    -The Network and Sharing Center window appears.

    -Click set up a new connection or network.


    -Set up a Connection or Network window appears.
    -Click Manually connect to a wireless network


    -Click Manually connect to a wireless network

    -Enter your wireless name in the Network name textbox, for example in our case we want to connect to “Wireless AP”

    -Choose WPA2

    -Personal for security type

    -Choose AES for encryption type

    -Type wireless key in the security key textbox

    -Click next


    2. In the list of networks, choose the network that you want to connect to, and then select Connect.

    3. Type the security key (often called the password).

    4. Follow additional instructions if there are any.

    2.4.7 Wireless Access Point vs Router

    The Wireless Access Points (AP) and routers play the similar role but they have some differences. They all connect different networks. A router often has an Access Point built-in, but a standalone Access Point can’t be a router. An AP can be compared to a modem which is limited in its functionality on managing multiple devices or controlling an entire network with many devices.Routers on the other hand can manage an entire home or small business giving network capability to many computers and devices simultaneously.

    2.4.7.1 Wireless Access Point Functions

    APs give wireless network ability to any device that only has a hard-wired connection. It is done by plugging in an Ethernet cable and the AP would then communicate with WiFi devices and giving them network access. .For example a printer that has no built-in wireless can have a access point added which will give it wireless ability.

    While current routers have built-in WiFi and play many roles including being an AP, many don’t use dedicated AP.APs are still used in many networks and they are used to help with WiFi dead spots and extending a wireless network.

    An AP can be added in locations that have bad wireless network ability and give good coverage throughout a home or business.

    2.4.7.2 Router Functions

    From the above section, a router is a network device that can transfer data wirelessly or wired. It forwards data packets to the desired device and control LAN (local Area Networks) or WAN (Wide Area Networks) networks

    2.4.7 SSID and encryption

    2.4.7.1 SSID and Wireless Networking

    An SSID (Service Set Identifier) is the primary name associated with an 802.11 Wireless Local Area Network (WLAN) including home networks and public hotspots. Client devices use this name to identify and join wireless networks.

    When you right click on the icon of wireless network in the Task Bar (Bottom Right of the computer’s screen), the displayed list of names of different networks are the SSID that are covered now or have been used in past.

    On home Wi-Fi networks, a broadband router or broadband modem stores the SSID but allows administrators to change it. Routers can broadcast this name to help wireless clients find the network. Router manufacturers set a default SSID for the Wi-Fi unit, such as Linksys, xfinitywifi, NETGEAR, dlink or just default. However, since the SSID can be changed, not all wireless networks have a standard name like that.

    2.4.7.2 Wireless fundamentals: Encryption and authentication

    Wireless encryption and authentication help users to make an educated decision on what type of security to implement into their wireless network. There exist different types of encryption and authentication. For example, CISCO Meraki is using the following:

    Application activity 2.4:

    A. Look around your school computer lab and do the following:

    •Uninstall and reinstall wireless adapters into your desktops

    •Switch on your computers and check whether wireless drivers are installed.

    •Using your computers, check for available wireless signal?

    •Login into your wireless router and change its SSID to “NetworkingLab”.

    •What is the IP address of your computer?

    •Discuss the advantages of protecting your wireless network with a password?

    B. Using one smart phone, setup a computer network made of your laptops. Describe how to connect to that network. What is the name of the network? Change that name and set up a new password.

    2.5 computer Network Protocols

    Activity 2.5:

    The school computer lab has 20 computers connected to the Local Area Network and Internet. Using his computer, the teacher wishes to get a copy of 40 MB document in all computers but he does not have any storage devices to facilitate the task. In groups, discuss possible ways to obtain this document in all computers in laboratory. Apply your proposed solutions.

    2.5.1 Definition

    A network protocol defines rules and conventions for communication between network devices. Network protocols include mechanisms for devices to identify and make connections with each other, as well as formatting rules that specify how data is packaged into messages sent and received.

    Network protocols are grouped such that each one relies on the protocols that underlie it sometimes referred to as a protocol stack. The key network protocols are the following:

    2.5.1 Most used protocols

    The most used protocols with their descriptions are given in the following table.

    Application activity 2.5:

    1. Discuss the role of protocols in computer communication?

    2. Search on Internet a free application called FileZilla Client and FileZilla Server using FTP to get access remotely to documents on another computer in the school computer lab. Copy to /from any document between the 2 computers.

    2.6 OSI model

    Activity 2.6:

    Fill in the blanks with the appropriate device between hub, switch and router.


    2.6.1 Definition

    Open System Interconnect (OSI) is an open standard for all communication systems. OSI model is established by International Standard Organization (ISO). It is a general-purpose model for discussing or describing how computers communicate with one another over a network. Its seven-layered approach to data transmission divides the many operations up into specific related groups of actions at each layer

    In the OSI model, data flows down the transmit layers, over the physical link, and then up through the receive layers. The transmitting computer software gives the data to be transmitted to the applications layer, where it is processed and passed from layer to layer down the stack with each layer performing its designated functions. The data is then transmitted over the physical layer of the network until the destination computer or another device receives it. At this point the data is passed up through the layers again, each layer performing its assigned operations until the data is used by the receiving computer’s software. The roles of OSI model layers are:

    a. The Application Layer

    The application layer enables the user, whether human or software, to access the network. It provides user interfaces and support for services such as domain name service (DNS), file transfer protocol (FTP), hypertext transfer protocol (HTTP), Internet message access protocol (IMAP), post office protocol (POP), simple mail transfer protocol (SMTP), Telenet, and terminal emulation. Devices used in this layer are Gateways, Firewalls, and all end devices like PC’s, Phones, and Servers.

    b.The Presentation Layer

    It presents data to the Application layer and is responsible for data translation and code formatting.

    The presentation layer is concerned with the syntax and semantics of the information exchanged between two systems.

    Specific responsibilities of the presentation layer include the following:

    •Translation

    •Encryption

    •Compression

    Devices which operate at this layer are Gateways, Firewalls and PC’s.

    c. The Session Layer

    The Session layer is responsible for setting up, managing, and then destroying down sessions between Presentation layer entities. This layer also provides dialogue control between devices, or nodes.

    It coordinates communication between systems and serves to organize their communication by offering three different modes of communication: simplex, half duplex, and full duplex.

    Specific responsibilities of the session layer include the following:

    •Dialog control

    •Synchronization

    The devices used at this layer are Gateways, Firewalls, and PC’s.

    d.The Transport Layer

    The Transport layer segments and reassembles data into a data stream. Services located in the transport layer segment and reassemble data from upper-layer applications and unite it onto the same data stream. They provide end-to-end data transport services and can establish a logical connection between the sending host and destination host on an internetwork. At this layer we find devices like Gateways and Firewalls.

    e. The Network Layer

    The Network layer manages device addressing, tracks the location of devices on the network, and determines the best way to move data, which means that the Network layer must transport traffic between devices that are not locally attached. Routers (layer 3 devices) are specified at the Network layer and provide the routing services within an Internetwork.

    The network layer is responsible for the delivery of individual packets from the source host to the destination host.

    Two activities are performed:

    •Logical addressing: IP addressing

    •Routing: Source to destination transmission between networks

    f. The Data Link Layer

    The Data Link layer formats the message into pieces, each called a data frame, and adds a customized header containing the hardware destination and source address. This added information forms a sort of capsule that surrounds the original message.

    To allow a host to send packets to individual hosts on a local network as well as transmit packets between routers, the Data Link layer uses hardware addressing.

    Switches and bridges work at the Data Link layer and filter the network using hardware (MAC) addresses.

    g.The Physical Layer

    Finally arriving at the bottom, the Physical layer does two things: It sends bits and receives bits. Bits come only in values of 1 or 0. The Physical layer communicates directly with the various types of actual communication media.

    The Physical layer specifies the electrical, mechanical, procedural, and functional requirements for activating, maintaining, and deactivating a physical link between end systems. This layer is also where you identify the interface between the data terminal equipment (DTE) and the data communication equipment (DCE). Devices like Hubs, Repeaters, Cables, and Fibers operate at this layer.

    Notice that the following network devices operate on all seven layers of the OSI model:

    -Network management stations (NMSs)

    -Web and application servers

    -Gateways (not default gateways)

    -Network hosts

    2.6.2 Advantages of using the OSI layered model

    1. It divides the network communication process into smaller and simpler components, thus aiding component development, design, and troubleshooting.

    2. It allows multiple-vendor development through standardization of network components.

    3. It encourages industry standardization by defining what functions occur at each layer of the model.

    4. It allows various types of network hardware and software to communicate.

    5. It prevents changes in one layer from affecting other layers, so it does not hamper hardware or software development.

    Application activity:

    1. Which layer of the OSI model creates a virtual link between hosts before transmitting data?

    2. What is the main reason of the creation of OSI model?

    3. Describe each one of the 7 layers of the OSI model.

    4. Which layer is responsible for converting data packets from the Data Link layer into electrical signals?

    5. At which layer is routing implemented, enabling connections and path selection between two end systems?

    6. Which layer defines how data is formatted, presented, encoded, and converted for use on the network?

    7. Which layer is responsible for creating, managing, and terminating sessions between applications?

    8. Search on Internet and propose the format of a packet sent between 2 computers through the

    2.7 TCP/IP model

    Learning activity 2.7:

    One teacher at your school wants to send a 50MB file to students’ emails but when he tries to attach it the email server rejects because of the size limit. It says that it cannot upload files larger than 20MB.

    1. What are other alternative to share this file?

    2.7.1 Introduction

    The TCP/IP protocol suite was developed prior to the OSI model. Therefore, the layers in the TCP/IP protocol suite do not exactly match those in the OSI model. TCP/IP model is the combination of TCP as well as IP models. This model ensures that data received is same as the data sent, and the data bytes are received in sequence. This model mainly defines how data should be sent (by sender) and received (by receiver). Most common examples of applications using this model include the email, media streaming, or World Wide Web (WWW). Presentation and session layers OSI model are not there in TCP/IP model.

    TCP/IP model comprises 4 layers that are as follows:

    1. Application Layer

    Application layer is the top most layer of four layers TCP/IP model. Application layer is present on the top of the Transport layer. Application layer defines TCP/IP application protocols and how host programs interface with Transport layer services to use the network.

    It groups the functions of OSI Application, Presentation and Session Layers. It includes protocols like:

    -The Hypertext Transfer Protocol (HTTP) is used to transfer files that make up the Web pages of the World Wide Web.

    -The File Transfer Protocol (FTP) is used for interactive file transfer.

    -The Simple Mail Transfer Protocol (SMTP) is used for the transfer of mail messages and attachments.

    -Telnet, a terminal emulation protocol, is used for logging on remotely to network hosts.

    2. Transport layer

    Transport Layer (also known as the Host-to-Host Transport layer) is the third layer of the four layer TCP/IP model. The position of the Transport layer is between Application layer and Internet layer. The purpose of Transport layer is to permit devices on the source and destination hosts to carry on a conversation. Transport layer defines the level of service and status of the connection used when transporting data. It is responsible for providing the Application layer with session and datagram communication services.

    The core protocols of the Transport layer are Transmission Control Protocol (TCP) and the User Datagram Protocol (UDP).

    -TCP provides a one-to-one, connection-oriented, reliable communications service. TCP is responsible for the establishment of a TCP connection, the sequencing and acknowledgment of packets sent, and the recovery of packets lost during transmission.

    -UDP provides a one-to-one or one-to-many, connectionless, unreliable communications service. UDP is used when the amount of data to be transferred is small (such as the data that would fit into a single packet).The Transport layer encompasses the responsibilities of the OSI Transport layer and some of the responsibilities of the OSI Session layer.

    3. Internet layer

    The Internet layer is responsible for addressing, packaging, and routing functions. The core protocols of the Internet layer are IP, ARP, ICMP, and IGMP.

    -The Internet Protocol (IP) is a routable protocol responsible for IP addressing, routing, and the fragmentation and reassembly of packets.

    -The Address Resolution Protocol (ARP) is responsible for the resolution of the Internet layer address to the Network Interface layer address such as a hardware address.

    -The Internet Control Message Protocol (ICMP) is responsible for providing diagnostic functions and reporting errors due to the unsuccessful delivery of IP packets.

    -The Internet Group Management Protocol (IGMP) is responsible for the management of IP multicast groups.The Internet layer is analogous to the Network layer of the OSI model.

    4. Network Access Layer

    This layer basically controls hardware devices and media that make up the network. Its tasks include routing of data, sending it over the network, verifying the data format, and converting the signs from analog to the digital format. TCP/IP can be used to connect differing network types. These include LAN technologies such as Ethernet and Token Ring and WAN technologies such as X.25 and Frame Relay.

    The Network Interface layer encompasses the Data Link and Physical layers of the OSI model.

    2.7.1 Summary of network models

    The 2 network models do realize the same job of sending data between different networks. By comparing OSI and TCP/IP models, there is a difference because the number of layers differs. However, some layers like application in TCP/IP do the job done by many layers in OSI models. For example Application layer and Network layer in TCP/IP combine the role of many layers.

    Application activity 2.7:

    1. Which of the following are layers in the TCP/IP model? (Choose three.)

    a. Application

    b. Session

    c. Transport

    d. Internet

    e. Data Link

    f. Physical

    2. What layer in the TCP/IP stack is equivalent to the Transport layer of the OSI model?

    a. Application

    b. Host-to-Host

    c. Internet

    d. Network Access

    3. Using a figure, describe TCP/IP and OSI network models with their associated protocols.

    4. Describe the purpose and basic operation of the protocols in the OSI and TCP models.

    2.7.1 Network switching

    Learning activity 2.8:

    Look at the following two network designs represented by figure A and B and answer questions:

    5. Describe what you see.

    6. What is the difference between these two designs?

    2.8.1 Definition

    Switching is a process to forward packets coming in from one port to a port leading towards the destination. When data comes on a port it is called ingress, and when data leaves a port or goes out it is called egress.

    A switched network consists of a series of interlinked nodes, called switches. In a switched network, some of these nodes are connected to the end systems (computers or telephones, for example). Others are used only for routing. The Figure below shows a switched network.

    The end systems (communicating devices) are labeled A, B, C, D, and so on, and the switches are labeled I, II, III, IV, and V. Each switch is connected to multiple links.

    The advantages of switches are as follows:

    -Switches increase available network bandwidth

    -Switches reduce the workload on individual computers

    -Switches increase network performance

    -Networks that include switches experience fewer frame collisions because switches create collision domains for each connection (a process called micro segmentation)

    -Switches connect directly to workstations.

    2.8.2 Switching methods

    The classification of switched networks is given by the figure below.

    2.8.2.1 Circuit-Switched Networks

    Circuit switching is a switching method in which a dedicated communication path in physical form between two stations within a network is established, maintained and terminated for each communication session. Applications which use circuit switching may have to go through three phases:

    •Establish a circuit

    •Transfer the data

    •Disconnect the circuit

    2.8.2.2 Packet Switched Networks

    In packet switched data networks all data to be transmitted is first broken down into smaller chunks called packets. The switching information is added in the header of each packet and transmitted independently.

    It is easier for intermediate networking devices to store small size packets and they do not take much resources either on carrier path or in the internal memory of switches.

    Packet switching can be done through the following technologies:

    g. Datagram networks

    Packets are treated independently and may take different routes. Datagram is better if numbers of packets are not very large.

    h. Virtual circuit networks

    In virtual circuit, a logical path is setup prior the transmission and therefore, no routing decision is to make which ensure that packet are forwarded more quickly than datagram. The logical path between destination and source also assure the sequencing of packet and better error control. However, virtual circuit is less reliable because Interruption in a switching node loses all circuit through that node.

    2.8.2.3 Message switching

    In message switching, if a station wishes to send a message to another station, it first adds the destination address to the message. Message switching does not establish a dedicated path between the two communicating devices i.e. no direct link is established between sender and receiver. Each message is treated as an independent unit.

    Consider a connection between the users (A and D) in the figure below (i.e. A and D) is represented by a series of links (AB, BC, and CD).

    For example, when an email message is sent from A to D, it first passes over a local connection (AB). It is then passed at some later time to C (via link BC), and from there to the destination (via link CD). At each message switch, the received message is stored, and a connection is subsequently made to deliver the message to the neighboring message switch.

    Application activity 2.8

    1. How does the message switching differ from circuit switching?

    2. Explain the technologies used in packet switching.

    END UNIT ASSESSMENT ACTIVITIES

    1. Your school has acquired 60 computers from the Rwanda Education Board (REB) and wishes to distribute them as follows:

    - Administration: 3 computers

    - Staff room: 7 computers

    - Computer lab for students in Ordinary level: 30 computers

    - Computer lab for students in Advanced level: 20 computers

    a. List and describe specifications of all materials needed to setup 2 wireless LANs within the school.

    b. Is it possible to secure those wireless networks?

    c. Indicate the type of wireless security to be used.

    2. Discuss the advantages of Fiber optic cables within a LAN.

    3. Why routers and switches do not operate at the same OSI reference model layer?

    4. What are the common steps in configuring both wireless router and access points?

    5. Is it possible to change the default gateway of your computer? Explain.

    6. When and how both public and private IP addresses are used within the same network?

    7. Describe the purpose and basic operation of the protocols in the OSI and TCP models.

    8. What are the advantages of using OSI layered model?

    9. Discuss the importance of switches within a LAN.

  • UNIT 3 :NETWORKING PROJECT

    UNIT 3: NETWORKING PROJECT
    Key Unit Competency:

    To be able to build a computer wired and wireless network

    INTRODUCTORY ACTIVITY

    Observe the following figure

    z

    Figure: 3.1: Networking tools
    Observe the figure above and answer the following questions
    1. List all the medium in the figure above
    2. Using two telephones or two laptops state different steps which can help sharing films via Bluetooth technology.
    3. Choose the different tools which can help in crossover Network cable making

    4. Describe all network tools available in the school computer Lab and in the figure 3.1

    3.1 Build Peer to Peer network
    Project I: Preparing Ethernet cables and devices
    P2P Project 3.1. A

    1) You are given 10 computers, UTP cables, RG-45, crimping tools, switch/ hub
    a. Arrange the tools and devices required to build P2P network
    b. Prepare cables required to build P2P network
    c. Test each cable if they are properly working.
    d. Connect cables to different devices.
    e. How will you dispose your useless materials?
    f. To avoid the loss of many materials which are costly, what measures would you
    take?
    Observation: If you have a green light, the connection is successful; if the
    color is Red, the connection is failed, please revise you cabling. If tried twice

    without success, please call the teacher/Lab technician.

    3.1.1 Tools required building P2P Network
    This practice has crucial important as when files are shared to the network will reduce
    the cost of printout papers as one document can be shared by many users who
    stands in the same P2P network or when sharing printer to the same P2P network,
    all users can enjoy printing without taking time in installing setup to all computers
    in the same work group.

    In order to build peer to peer network we need several equipment discussed in

    previous network classes, the maximum number of computer to build a P2P network
    is 10, transmission media needed is UTP cables. We use crossover to connect the
    same devices (hub to hub, computer to computer, router to router, and switch to
    switch) and straight through cable to connect different devices (Computer to switch,
    computer to hub). We use star topology where we need Switch/hub as the central
    devices. To build a P2P network of 2 computers we need only two computers and
    Ethernet cable (Strait through cable), for P2P network of more than 2 computers we
    need computers, Ethernet cables (Crossover and Straight through) and Switch/hub.

    3.1.2 Process to build P2P network

    We are going to build a peer to peer network for small office or home office.
    Step 1: Make sure all computers are turned off, organized and arranged.
    In this practice we need to arrange 10 computers which is the maximum number of
    computer allowed in P2P network.

    Step 2: Install central devices (Switch or hub).

                  r

    Step 3: Connect each end of the UTP CAT 6 straight through cables to connect

    computers to Switch/ Hub.

    When connecting devices, UTP CAT 6 straight through cable is required to connect
    the same devices (Computer to computer) and UTP CAT 6 crossover cable to connect
    different devices (Switch to computer), depending on number of devices we have in
    our practice, more Ethernet cables are need. There are some tools which are needed
    to make ethernet cables and different steps learned in the previous school (S5, Unit
    3 Introduction to networking) are needed.
    Tools used to make Ethernet cables: cat 6 cables, RJ 45 , RJ 45 crimping tool, scissors,
    Cable tester, drilling machine, hammer, screw driver, cable strripper, cable ducts

    Making straight through cable and Crossover cable:

    Step 1: Strip the cable jacket about 1.5 inch down from the end.
    Step 2: Spread the four pairs of twisted wire apart. For Cat 5e, you can use the pull
    string to strip the jacket farther down if you need to, then cut the pull string. Cat 6
    cables have a spine that will also need to be cut.
    Step 3: Untwist the wire pairs and neatly align them in the T568B orientation. Be
    sure not to untwist them any farther down the cable than where the jacket begins;
    we want to leave as much of the cable twisted as possible.
    Step 4: Cut the wires as straight as possible, about 0.5 inch above the end of the
    jacket.
    Step 5: Carefully insert the wires all the way into the modular connector, making
    sure that each wire passes through the appropriate guides inside the connector.
    Step 6: Push the connector inside the crimping tool and squeeze the crimper all the
    way down.
    Step 7: Repeat steps 1-6 for the other end of the cable.
    Step 8: To make sure you’ve successfully terminated each end of the cable, use a
    cable tester to test each pin.
    The following figure shows how you should keep each wire according to their colors

    for each type of cable (Crossover and straight through cables)

    f

    You should also use numbers of wires in case you are confused by colors. The following

    figure helps also to create the indicated cables.

    a. Straight Cable

    d

    After making Ethernet cable we need to make them tested using cable tester using packet
    tracer, we can connect devices using different cables not only packet tracer which can be

    used but also real computers depending on the resources/ computer lab.

    f

    Project II: Static IP address configuration
    P2P Project 3.1. B

    Using previous exercises P2P project 3.1.A, after arranging computers and connect
    them with different cables accordingly do the following:
    a. Define IP address scheme to be used
    b. Assign each computer a static IP address
    c. After assigning static IP address, test if they are connected using ping
    command.
    d. Identify the materials to be used for keeping devices clean.
    Observation: By pinging each computer, the observation will be on the replies, if IP
    address is assigned successfully, the packet sent will be equal to 4, received will be 4
    and the lost will be 0, else it is not assigned successfully else repeat the process. If failed

    twice call the teacher/Lab technician for guidance.

    Step 4: Define IP address scheme
    Listing IP addresses that will be used to different computers is an important step
    that will be helpful to define the same working group for being able to share files,
    folder, printers and network.

    In this project we use maximum 10 computers which are allowed to build P2P

    network, the IP addresses given to the PCs can be in the same network for being in

    the same work group to share resources such as folder, printer, files and network.

    The following are IP addresses we will use in this practice:
    PC1: 192.168.0.1, PC2: 192.168.0.2, PC3: 192.168.0.3, PC4: 192.168.0.4,
    PC5: 192.168.0.5, PC6: 192.168.0.6, PC7: 192.168.0.6, PC8: 192.168.0.8,
    PC9: 192.168.0.9, PC10: 192.168.0.10
    Step 5: Configure static IP address for each computer.
    Process1: In windows 10, go to search and type in Control panel and click on it
    Process2: Click the link “View network status and tasks” under the “Network

    and Internet” heading.

    m

    Process 3: Click the link on the left of the window labeled Change adapter settings.

    Process 3: change adaptor settings

    x

    Process 4: You might have more than one Internet connection listed in this
    window. If this is the case you will need to determine which one is your connection
    to the Internet. Once you have found it, right click on your network adapter and

    choose properties to open up the properties window of this internet connection.

    s

    Process 5: Find the option of Internet Protocol Version 4 (TCP/IPv4) and click on

    it. Then choose one option (Internet Protocol Version 4)

    d

    Process 6: Select “Use the following IP address” and enter the IP address,
    Subnet Mask, Default Gateway and DNS server. Click OK and close the Local Area

    Connection properties window.

    d

    When choosing “Use the following IP address” the IP must be configured as
    Static
    Procces7: As an example, assign one PC1 an IP address of 192.168.0.1 and use the
    subnet mask 255.255.255.0.

    For PC1

    c

    Procces 8: As an example, assign one PC2 an IP address of 192.168.0.2 and use the

    subnet mask 255.255.255.0.

    For PC2

    c

    Process 9: Do the same for other 8 PCs
    Step 6: Ping each computer to verify if they are connected
    Process 1: In windows 10, go to search and type in CMD then press Enter
    Process 2: Type Ping 192.162.0.1 (if you use a computer assigned with
    192.168.0.2 type in: ping 192.168.0.2) then press Enter button, the replies should

    be as indicated in the figure below

    v

    Figure 3.1.15: IP address Replies
    Step 7: Do the same as what you did on step 5 to all PCs
    Note: if you receive timeout message when attempting to ping your selected IP
    address, it is possible that the internet connection firewall is interfering, unplugged

    cables, mistake on IP configuration, etc

    z

    How to allow internet connection through the firewall?
    - Right click on My Network places, then select properties.
    - Right click local Area connection and select properties once again,
    - Click the advanced tab. Uncheck the box titled: protect my computer from
    the internet.
    - Click OK. Now, try typing the selected IP address again.
    Once you get two computers to communicate successfully together, you can now

    enjoy the benefit of files, printer, internet sharing.

    Project III: Sharing folder, files, printer and internet
    P2P Project 3.1.C

    Using previous exercises P2P project 3.1.B, after assigning each computer its own IP address,
    do the following
         a. Put all computers in the same work group network
         b. Share files, folder, printers and network.
         c. How to avoid piracy in networking.
    Observation: When files, folder, printer and internet are successfully to all permitted PCs,
    they should be seen and retrieved on other PCs and enjoying using them when you are
    in the same P2P network. The shared printer must print to every connected PC even if
    there is no setup installed. If fails to share repeat the process once else call to the teacher/

    Lab technician for guidance

    To share folder requires creating a home group/ workgroup, for the topic discussed
    in S5, creating home group/ work group is discussed, here we need to change a
    home group/ work group to be able to share files, folder, printer and network.

    The following are the steps to change the home group/ workgroup in Windows 10


    Step1:
    With the right mouse button click the Start icon and choose System. If you

    have a touch enabled device, click and hold the start button, then tap the System

    button.

    s

    Step2: Under “Computer name, domain, and workgroup settings” click on Change

    Settings

    v

    Step3: Under the tab “Computer Name” find the Change… button and click it

    v

    Step4: Under “Member Of” change the Workgroup name.

    d

              Figure 3.17: Select workgroup

    Step 5: Change the name from WORKGROUP to S6A

    s

    Step 6: Then click on OK, the system will prompt to reboot
    After rebooting the system, the new folder/files/printer or network will be added
    to the existing Home group following the steps below
    Step 1: Use the Windows key + E keyboard shortcut to open File Explorer
    Step 2: On the left pane, expand your computer’s libraries on Home Group
    Step 3: Right-click Documents and choose Properties
    Step 4: Click Properties.

    Step 5: Click Add.

    b

    Step 6: Select the folder you want to share and click Include folder.
    Step 7: Click Apply.
    Step 8: Click OK.
    Now the folder will be accessible by anyone who joined the Home Group when
    they browse the Documents folder

    3.2 Wireless Router installation and configuration

    Note: This configuration is for TP-Link wireless Router, for other types of wireless
    routers, consult manufacturer requirements and guidance.

    Project I: Wireless router connection and Setting

    P2P Project 3.1.C

    Using previous exercises P2P project 3.1.B, after assigning each computer its own IP
    address, do the following
    a. Put all computers in the same work group network
    b. Share files, folder, printers and network.
    c. How to avoid piracy in networking.
    Observation: When files, folder, printer and internet are successfully to all permitted PCs,
    they should be seen and retrieved on other PCs and enjoying using them when you are
    in the same P2P network. The shared printer must print to every connected PC even if
    there is no setup installed. If fails to share repeat the process once else call to the teacher/
    Lab technician for guidance
    This unit provides procedures for configuring the basic parameters of your router;
    it also describes the default configuration on startup.

    3.2.1 Default configuration

    When you first boot up the router, some basic configuration has already been
    performed for TP-Link wireless router. All of the LAN and WAN interfaces have been
    created, console and VTY (Virtual Teletype) ports are configured, and the inside
    interface for Network Address Translation has been assigned.

    3.2.2 Wireless Router configuration requirements

    For some routers web browsers are needed to configure them to the wireless
    router, others need Ethernet cables to be configured, they are also some which
    needs their catalog where there is written all process to configure them.
    To configure wireless Router the following materials are needed:
             • A wireless router,
             • A computer or laptop with wireless capabilities.
              • Two Ethernet cables.
    Step 1: Prepare router and switch on it.
    Step2: Connect router to the Laptop/PC with wireless capability
    Step3: Access Dashboard using default IP address and Password
    The different process will be applicable when prompting to the Dashboard using
    default IP and password.
    Process1: Open the web browser and key in the address bar the default IP address
    (192.168.0.1)
    Process2: The server asks for the default username and password. The user will

    then write in the form the default username and password

    c

    Process 3: Prompting default dashboard

    The figure below will be displayed after entering default username and password

    d

    Step4: Configure internet using information from the ISP
    When configuring internet using information from ISP, some processes are
    applicable

    Process1: Click on Network then select internet mode and click Save

    c

    Process2: Click on Quick Setup to start configuring internet using information

    from ISP where the selected internet mode is activated, then click Next.

    x

    Process3: On Wireless name rename the existing name of wireless and
    authenticate by set your own password for network protection.
    In this practice, we use
    Wireless name: senior 6a

    Password: Kigali12

    x

    Process 3: After renaming Wireless and authentication, click Finish to apply the

    change, and then move to set DHCP settings.

    m

    Project II: Wireless security configuration
    Wireless Router configuration Project 3.2.B
    Following what is done in Wireless Router 3.2.A
    a. Configure the wireless security
    b. Set DHCP settings?
    c. What measures would you take to keep data on router confidential?
    Observation: If wireless router is successfully configured it will be seen on each device
    which has capabilities to be connected to the wireless and using the name of the wireless
    and password connect to verify if it is working, if it is not working repeat configuration

    once if fails twice, call to the teacher for guidance.

    Step5: configure LAN and IP using DHCP
    Go to Wireless then Wireless Security and configure the wireless security. WPA/
    WPA2-Personal is recommended as the most secure option. Once configured,
    click Save.
    There are many wireless security protocols. Here is a basic list ranking the current
    Wi-Fi security methods available on any modern (post-2006) router, ordered from
    best to worst: WPA2 + AES, WPA + AES, WPA + TKIP/AES (TKIP is there as a fallback
    method), WPA + TKIP, WEP and Open Network (no security at all).

    Ideally, you will disable Wi-Fi Protected Setup (WPS) and set your router to WPA2

    + AES. Everything else on the list is less than ideal step down from that. Once you
    get to WEP, your security level is so low, it’s about as effective as a chain link fence.
    The fence exists simply to say “hey, this is my property” but anyone who actually

    wanted to go in could just climb right over it.

    m

    Note: If using a dual band router, repeat this process.
    Step 5:
    Go to DHCP→ DHCP Settings and select Disable the DHCP Server. Click

    on Save.

    m

    Step 6: Set the IP from ISP and go to the System Tools and select Reboot to reboot

    the device.

    m

    3.3 Building Client/Server network
    Project I: Creating and setting an FTP folder

    Client/server project 3.3.A

    1. You are given 2 computers and strait through cables
    a. Create an FTP Folder
    b. Configure IP address
    c. Configure an FTP Server
    d. When file is needed to be used by many people in the group, sometimes
    printing or sending to flash disk is needed which is costly and sometimes
    spread viruses. What measures would you take to avoid high cost and
    virus spreading?

    Observation: If the FTP folder is successfully created it will be available on server computer

    and start enjoying using it for the next session to create an FTP site. If it fails once,

    call to the teacher/lab technician for guidance.

    3.3.1 Creating an FTP client/server network
    With a home FTP server, you are able to upload and download files from anywhere
    to your PC, Similar to cloud storage but without the limitations.
    Setting up a File transfer protocol (FTP) server may sound complicated, but it’s
    actually quite easy to set up especially if using Windows 10.

    Step 1: Create a folder that your FTP users will be accessing on C: drive.

    v

    Step2: Press the “Windows key + R” on your keyboard to open the Run window,

    and type CMD, click OK to open the command prompt window.

    h

    Step3: Here type “ipconfig” and press enter, write down the IP address and the
    default gateway IP, because we are going to use it in the next steps. Here the

    following IP addresses will be used: 10.0.0.17 and 10.0.0.1

    n

    Step4: Then go to Control Panel -> Programs and Features.

    Step5: Navigate to Turn Windows features on or off on the top left.

    r

    Step 6: Select the check box, next to “Internet Information Services” also collapse
    it to check mark “FTP Server” and “FTP Extensibility”, then click the OK button and

    wait for the features to be added.

    r

    Project II: Creating and setting an FTP site
    Client/server project 3.3.B
    1. Following the steps client/project 3.3.A,
    a. Create an FTP site
    b. Create an Internet Information service site
    c. How can you prevent unauthorized user to access your data?
    Observation: If the FTP site is successfully created it will be available on server computer
    and start enjoying by sharing folder, printers and files to the same client/server network.

    If it fails repeat the process or consult the teacher for guidance.

    Step 7: Go to Control panel, Administrative tools,

    d

    Step 8: Choose Internet Information Services (IIS) Manager,
    Step 9: Expand the root and right click on Sites to create a new FTP Site, click on

    “add FTP Site…”

    f

    Step10: Give your FTP site a name, such as “AvoidErrors”, and browse for the folder
    we created initially. This will be the default location where files will be accessible on

    the server via FTP.

    d

    Step11: On the Binding and SSL Settings page, click on the drop down to select
    the IP Address of the computer, Select “No SSL”. If you do have an SSL certificate,

    you can choose either “Require SSL” or “Allow SSL”.

    u

    Note: Be sure to require SSL if you intend to make this FTP server accessible via the

    Internet.

    On the Authentication and Authorization Information screen, change
    “Authentication” option to “Basic” (requires that FTP users specify a login ID and
    password).

    Step13: Authorization section, select “Specified Users”, and Read & Write

    permissions. Alternatively, you can choose specific user accounts or a group, and
    limit permission to only Read or Write. Click Finish.

    Step13: Now your new IIS Site is properly created.

    u

    Step14: Create a local user account, and give to him/her permission on the FTP
    content directory:
    *If you prefer to use an already existing local computer user account instead of
    creating one you can skip the create user step and skip to open windows firewall
    ports.

    To allow additional users you must:

    1. Create a Windows 10 user.
    2. Create a new IIS Rule for the new user.
    3. Add the new user to the security settings of the FTP folder.
    Open firewall ports for FTP:

    Open Control Panel… (View by: “Small icons” recommended)… Windows

    Firewall… Select “Allow an app or feature through windows firewall” -> Change
    Settings button
    Select the checkbox next to “FTP Server” and at least one of the networks, and then

    click on OK.

    f

    Project III: Creating rules and sharing folders
    Client/server project 3.3.C

    1. Following the project 3.3.B,
    a. Enable FTP server port to be accessed from the LAN
    b. Select proper management
    c. Add the new user to the security settings of the FTP folder.
    d. How can you avoid unauthorized users to access your folder
    Observation: If the FTP client/server network is successfully configured the shared Folder,
    printer, files and internet will be seen among all computers on the same network of
    FTP server network, you can start enjoying using the shared files, folder and printers.

    Else repeat the process twice or call the teacher for guidance.

    Now the Windows 10 FTP Server is enabled to be accessed from the LAN.

    Step15:
    Once you have tested the FTP over the LAN than we are ready to access it

    via WAN. To allow FTP connection you must enable Port 21 in your router’s firewall

    to allow incoming connection via FTP port 21.

    w

    Process1: Select the proper Site in IIS Manager and on the right, navigate to FTP

    Authorization Rule.

    f

    Process2: Right click an empty space and select Add Allow Rule…

    w

    Process3: Here check mark specified users and write the user name. Make sure is

    first created in windows 10 and click OK.

    r

    To add the new user to the security settings of the FTP folder:

    Process4:
    Locate the folder that your FTP users will be accessing (example: C:\FTPFolder),
    right click the folder… select Properties -> Security tab… and add the
    user that was created in the previous step with appropriate permissions.
    r
    r
    End unit activity
    1. Lab activity: a given 5 computers with the following IP addresses:
    198.162.0.117, 198.162.0.114, 198.162.0.118, 198.162.0.119,
    198.162.0.116, the subnet mask is 255.255.255.0 assigned to all
    computers
    a. Create a P2P network
    b. Add a home group and share folders, music and files to all computers.
    2. You are given a server machine, three new laptop, three Ethernet
    cables, and the following IP addresses: 198.162.0.12 and 198.162.0.13,

    create an FTP client/ server network and share folder.

  • UNIT 4 :SQL AND DATABASE PROJECT

    UNIT 4: SQL AND DATABASE PROJECT

    Key Unit Competency:
    To be able to apply Structured Query Language in RDBMS

    and create a short database project

    Introductory activity:

    The school is requested to submit a report to the District indicating the data on
    students, teachers, rooms and courses.
    In groups, conduct a survey in your school and answer the following questions:
    i. How many students are in the schools?
    ii. How many student are in each class?
    iii. How many boys are in the school? How many boys are in each class?
    iv. How many are girls in the school? How girls are many in each class?
    v. What is the number of teachers in the schools?
    vi. How many teachers for Mathematics Subjects
    vii. How many rooms are there in the school?
    viii. How can you choose the courses that can be taught in the same
    room?
    ix. Show the number of students eligible to get National Identity card.
    x. How do you think the school does such reporting according to these
    criteria?
    a. Design the entities student, teacher, room and course
    b. Using the used RDBMS (MS Office Access) in the school computer
    lab, create the database of the school with the 3 tables representing
    the entities above.
    c. Respond again to the sub questions in question a) by querying your
    database. Are the answers the same? What is used to get the answer

    to each answer?

    Activity 4.1:
    4. Kabeza Company Ltd is running a business and wishes to manage
    transactions in computerized way. The database of business contains
    various entities including “Customers” (id, name, age, address, salary)
    and orders (id,date,customer_id, amount) which are given here below.
    Help your school to find a solution to get the following:
    i. The highly paid employee
    ii. The least paid employee
    iii. The oldest employee
    iv. The youngest employee
    v. To generate total amount of income at a given day.
    vi. To retrieve only the names and age of all employees
    vii. To retrieve the average income at a given day.

    “Customers” table:

    i. σage>=23 AND address≠ kigali(“Customers”)

    d

    Definition: relational algebra is the one whose operands are relations or variables

    that represent relations.

    4.1.1 Unary operations
    By definition a unary operation is an operator that uses only one operand (relation).
    In Relational algebra, the unary operations are selectionand projection

    4.1.1.a Selection operation

    Selection is a unary operation that selects records satisfying a given predicate
    (criteria). It selects a subset of records. The lowercase Greek letter sigma (σ) is used to
    denote selection. The selection condition appears as a subscript to σ. The argument

    relation is given in parenthesis following the σ.

    Select Operation Notation Model

    d

    Syntax: σ selection_condition (Relation)

    The selection condition or selection criterion can be any legally formed expression

    that involves:

    1. Constants (i.e, members of any attribute domain)

    2. Attributes names
    3. Arithmetic operators(+,*,/,-,%)
    4. Comparisons/Relational operators : a. in mathematicl algebra(=,≠,<,>,≤,≥)
    b.in relational algebra (=,<,<=,>,>=)
    5. Logical operators( And,Or, Not)
    Example of using selection operation within a relation: σ last_name=’Mugisha’
    (“Customers”) where the table “Customers” in learning activities 4.1 is considered.

    The result is a binary relation listing all the “Customers” with the name Mugisha.

    g

    result of σ name=’Mugisha’ (“Customers”)

    Application activity 4.1:
    Using the table “Customers” in learning activities 4.1, what is the result of the
    following statements?
             i. σ Salary>2000 (“Customers”)

             ii. σage>=23 AND address≠ kigali(“Customers”)

    4.1.1.b Projection operation
    Activity 4.2
    From the table “Customers”, display the following table with only two

    columns name and age

    r

    Result for π name,age (“Customers”)
    The PROJECT operation is another unary operation. This operation returns a set of
    tuples containing a subset of the attributes in the original relation. Thus, we state
    that the SELECT operation selects some rows and discards the others. The PROJECT
    operation, on the other hand, selects some columns of the relation and discards
    the other column. The PROJECT operation can be viewed as the vertical filter of the
    relation.

    The projection operation copies its arguments relation, but certain columns are left

    out. The projection operation lists the desired attributes to appear in the result as a
    subscript to π.
    Projection is unary operation denoted by the Greek letter pi (π).
    • Syntax: πattribute−list(r)
    • Eg: Π attribute_1, attribute_2,…, attribute_n(Relation)

    Project Operation Notation Model:

    r

    Notice that if the projection produces two identical rows, the duplicate rows must be
    removed since the relation is a set and it is not allowed to contain identical records.


    Eg:Example: Retrieve the suburbs that are stored in database

    d

    Application activity 4.2:
    What is the result of the following statements?
    i. π ID, Address (“Customers”)

    ii. π name,Salary(σSalary>5000) (“Customers”)

    4.1.2 Binary operations
    A binary operation is an operation that uses two operands (relations). In Relational
    algebra, the binary operations are Cartesian product, Union operator, Set Difference,

    Intersection, Theta-join and Natural Join.

    Activity 4.3:

    We have two relations, student and subject, as follows:

    e

    How can be generated the following table?

    f

    The Cartesian product of two relations, R1 and R2, is written in infix notation as
    R1xR2. To define the final relation scheme, we need to use fully qualified attribute
    names. Practically, it means we attach the name of the original relation in front of
    the attribute. This way we can distinguish R1.A from R2.A. If R1(A1,A2,…,An) and
    R2(A1,A2,…,An) are relations, then the Cartesian product R1xR2 is a relation with
    a scheme containing all fully qualified attribute names from R1 and R2:(R1.A1,…
    R1.An,R2.A1,….R2.An).

    The records of Cartesian product are formed by combining each possible pair

    of records: one from the R1 relation and another from the R2 relation. If there are
    n1 records in R1 and n2 records in R2, then there are n1*n2 records in their Cartesian

    product.

    The results of a Cartesian product is a relation whose scheme is a concatenation
    of student scheme and subject scheme. In this case, there are no identical attribute
    names. For that reason, we do not need to use the fully qualified attribute names.
    Student relation contained 2 records, subject relation 3, therefore, the result has 6
    records (2 times 3).

    Note that the Cartesian product contains no more information than its components

    contain together. However, the Cartesian product consumes much more memory
    than the two original relations consume together. These are two good reasons why
    Cartesian product should be de-emphasized, and used primarily for explanatory or

    conceptual purpose.

    Application activity 4.3:

    Carry out the following activity:

    c

    A. Union operator

    Activity 4.4:

    m

    What was the operation performed in order to get this table?

    q

    The result of the query are all the people (students and teachers) appearing in either
    or both of the two relations. Again, since relations are sets, duplicate values are
    dropped.

    The binary operation Union is denoted, as in set theory, by U. Union is intended

    to bring together all of the facts from its arguments, however, the relational union
    operator is intentionally not as general as the union operator in mathematics.

    We cannot allow for an example that shows union of a binary and a ternary relation,

    because the result of such union is not a relation. Formally, we must ensure that
    union is applied to two union compatible relations. Therefore, for a union operator

    R1UR2 to be legal,it is required that two conditions be held:

    1. The relations R1 and R2 are of the same arity. Which means, they have the
    same numbers of attributes.

    2. The domains of the ith attribute of R1 and ith attribute of R2 are the same.

    Application activity 4.4:

    Carry out the following activity:

    r

    B. Set Difference
    Activity 4.5:

    From the following table:

    e

    What is the operation performed in order to generate the following table?

    r

    The last fundamental operation we need to introduce is set difference. The set
    difference, denoted by - is a binary operator. To apply this operator to two relations,
    it is required for them to be union compatible. The result of the expression R1-R2,
    is a relation obtained by including all records from R1 that do not appear in R2. Of
    course, the resulting relation contains no duplicate records.
    Note that if the relations are union compatible, applying “set difference” to them is
    allowed.

    C. Intersection

    The first operation we add to relational algebra is intersection, which is a binary
    operation denoted by ∩ symbol. Intersection is not considered a fundamental
    operation because it can be easily expressed using a pair of set difference operations.
    Therefore, we require the input relations to union compatible.
    After applying the intersection operator, we obtain a relation containing only those
    records from r1 which also appear as records in r2.We do not need to eliminate
    duplicate rows because the resulting relation cannot contain any (since neither of
    the operands contain any).

    Practical example:

    Consider relations R and S

    n

    D. Join operations
    SQL Joins can be classified into Equi join and Non Equi join.
    1. SQL Equi joins
    It is a simple sql join condition which uses the equal sign as the comparison operator.
    Two types of equi joins are SQL Outer join and SQL Inner join.
    For example: You can get the information about a customer who purchased a
    product and the quantity of product.

    2. SQL Non equi joins

    Types of Joins:
    Join is a special form of cross product of two tables. It is a binary operation that
    allows combining certain selections and a Cartesian product into one operation.
    The The join operation forms a Cartesian product of its two arguments, performs a
    selection forcing equality on those attributes that appear in both relation schemas,
    and finally removes duplicate attributes. Following are the different types of joins:
    Theta Join, Equi Join, Semi Join, Natural Join and Outer Joins
    We will now discuss them one by one

    1. Theta Join:

    In theta join we apply the condition on input relation(s) and then only those selected
    rows are used in the cross product to be merged and included in the output. It means
    that in normal cross product all the rows of one relation are mapped/merged with
    all the rows of second relation, but here only selected rows of a relation are made
    cross product with second relation. It is denoted as RX S

    For example there are two relations of FACULTY and COURSE now first apply select

    operation on the FACULTY relation for selection certain specific rows then these
    rows will have a cross product with COURSE relation, so this is the difference in
    between cross product and theta join. From this example the difference between

    cross product and theta join becomes clear.

    m

    m

    2. Equi¬ Join:
    This is the most used type of join. In equi¬join rows are joined on the basis of values
    of a common attribute between the two relations. It means relations are joined on
    the basis of common attributes between them; which are meaningful. This means on
    the basis of primary key, which is a foreign key in another relation. Rows having the
    same value in the common attributes are joined. Common attributes appear twice in
    the output. It means that the attributes, which are common in both relations, appear
    twice, but only those rows, which are selected. Common attribute with the same
    name is qualified with the relation name in the output. It means that if primary and
    foreign keys of two relations are having the same names and if we take the equi ¬
    join of both then in the output relation the relation name will precede the attribute
    name. For Example, if we take the equi ¬ join of FACULTY and COURSE relations then

    the output would be

    m

    3. Natural Join:
    This is the most common and general form of join. If we simply say join, it means
    the natural join. It is same as equi¬ join but the difference is that in natural join,
    the common attribute appears only once. Now, it does not matter which common
    attribute should be part part of the output relation as the values in both are same.

    To join the tables use this symbol:x

    e

    4. Outer Join:
    This join condition returns all rows from both tables which satisfy the join condition
    along with rows which do not satisfy the join condition from one of the tables.
    The Outer Join has three forms:

    a. Left Outer Join:

    In a left outer join all the tuples of left relation remain part of the output. The tuples
    that have a matching tuple in the second relation do have the corresponding tuple
    from the second relation. However, for the tuples of the left relation, which do not
    have a matching record in the right tuple, have null values against the attributes of
    the right relation. Left outer join is the equi-join plus the non matching rows of the

    left side relation having null against the attributes of right side relation.

    The following example shows how Left Outer Join operation works:

    Consider the relation BOOK and relation STUDENT

    e

    e

    w

    b. Right Outer Join:
    In right outer join all the tuples of right relation remain part of the output relation,
    whereas on the left side the tuples, which do not match with the right relation,
    are left as null. It means that right outer join will always have all the tuples of right
    relation and those tuples of left relation which are not matched are left as Null. The
    following example shows how Right Outer Join operation works:

    Consider the Relation BOOK and Relation STUDENT

    e

    c. Full Outer Join:
    In outer join all the tuples of left and right relations are part of the output. It means
    that all those tuples of left relation which are not matched with right relation are
    left as null. Similarly all those tuples of right relation which are not matched with left
    relation are left as null.

    The following example shows how Right Outer Join operation works:


    Consider the relation BOOK and relation STUDENT

    m

    d. Division Operator
    Division identifies attribute values from a relation that are paired with all of the

    values from another relation.

    Application activity 4.5:
    1. Solve the following relational expressions for these relations:

    User

    y

    s


    a. πName(δ(Age>25)(User)) same as δName(δAge>25)(User)
    b. δ(Id>2∨Age!=31)(User)
    c. δ(User.OccupationId=Occupation.OccupationId)(User X Occupation)
    d. πName,Gender(δCityId=1User ⋈ City))
    2. With clear example, differentiate unary operators and binary operators.
    3. Use an example and explain projection and selection operations.

    4. Consider ABC database containing the following relations:

    Representative (number, surname, firstname, committee, county)
    Counties (code, name, region)
    Regions(code, name)
    Committees (number, name, president)
    Formulate the following queries in relational algebra, in domain calculus and
    in record calculus:
    • Find the number and surname of the Representatives from the country
    of Rwanda;
    • Find the name and surname of representatives;
    • Find the members of the finance committee;
    • Find the name, surname, country and region of election of the
    delegates of the finance committee;
    • Find the regions in which representatives having the same surname
    have been elected.

    5. Consider the DEFG database schema with the relations:

    Courses (number, faculty, coursetitle, tutornumber)
    Students (number, surname, firstname, faculty)
    Tutors (number. surname, firstname)
    Exams (studentnumber, coursenumber, grade, date)
    Studyplan( studentnumber, coursenumber, year)
    * Formulate in relational algebra the queries that produce:
    * The students who have gained an ‘A’ in at least one exam, showing, for
    each of them, the first name, surname and the date of the first of such
    occasions;
    * For every course in the engineering faculty, the students who passed
    the exam during the last session;
    * The students who passed all the exams required by their respective
    study plans;
    * For every course in the literature faculty, students who passed the
    exam with the highest grades;
    * The students whose study plans require them to attend lectures only
    in their own faculties;
    * First name and surname of the students who have taken an exam with

    a tutor having the same surname as the student.

    4.2 Structured Query language
    Activity 4.6:

    1. Consider the following relational schema of HIJK database;
    Departments (Dept_Code (integer), name (text) , Budget (number)) Employees (SSN
    (integer), first_name (text), last_name (text), Dept_Code (integer, foreign key) Write SQL
    query to:
    i. Create the above relations (tables)
    ii. Add at least ten records
    iii. Select the last name of all employees, without duplicates.
    iv. Select all the data of employees whose first name is "Peter" or "Diane".
    v. Retrieve the sum of all the departments’ budgets.
    vi. Retrieve all the data of employees whose last name begins with letter
    "U".
    vii. Show the number of employees in each department (you only need to
    show the department code and the number of employees).
    viii. Select the name and last name of employees working for departments
    with a budget greater than 6,000,000 (currency: Rwf)
    ix. Show the departments with a budget larger than the average budget of
    all the departments.
    x. Add a new department called "Quality Assurance", with a budget of
    4,000,000 (currency: Rwf) and departmental code 11. Add an employee
    called "KAMANA" in that department, with SSN 847-21-9811.
    xi. Reduce the budget of all departments by 10%.

    xii. Delete from the table all employees in the IT department (code 14).

    4.2.0. Introduction
    SQL which is an abbreviation for Structured Query Language, is a language to
    request data from a database, to add, update, or remove data within a database, or
    to manipulate the metadata of the database.
    Commonly used statements are grouped into the following categories:
    DML: Data Manipulation Language

    DDL: Data Definition Language

    DCL: Data Control Language
    4.2.1 Data Definition Language (DDL)
    a. To create a new database, the SQL query used is CREATE DATABASE
    The Syntax is:
    create database databasename;
    Always database name should be unique within the RDBMS. Example of a query to
    create a database called XYZLtd

    CREATE DATABASE XYZLtd; In MYSQL, it will look like the following:

    i

    Make sure that the user has admin privilege before creating any database.
    b. To display the list of all databases created, the SQL query is SHOW
    databases;
    Once a database is created, the user can check it in the list of databases as follows:
    Show databases;

    If the RDBMS used is MYSQL, the result will look like this:

    6

    c. Before using a database, the SQL command USE helps to select
    the name of the database.

    The SQL USE statement is used to select any existing database in the SQL schema.
    Syntax: The basic syntax of the USE statement is as shown below: Always the
    database name should be unique within the RDBMS. Now, if the user wants to work
    with the XYZLtd database, then he/shecan execute the following SQL command

    and start working with the School database.

    f

    d. After creating a database and entering in it, there is a need now
    to create table

    Creating a basic table involves naming the table and defining its columns and each
    column’s data type. The SQL CREATE TABLE statement is used to create a new table.

    the basic syntax of CREATE TABLE statement is as follows:

    create table table_name (column1 datatype, column2 datatype, column3 datatype,
    ..... columnn datatype,
    primary key( one or more columns ) );

    CREATE TABLE is the keyword telling the database system what you want to do. In

    this case, you want to create a new table. The unique name or identifier for the table
    follows the CREATE TABLE statement.

    Then in brackets comes the list defining each column in the table and what sort of

    data type it is. The syntax becomes clearer with an example below.

    Activity 4.7:

    The following SQL query creates a “Customers” table with ID as primary key and NOT
    NULL and thereafter, when the table was successfully created, the message “Query OK,

    o rows affected (0.48 sec)” is displayed. See below.

    c

    The user can verify if the table has been created successfully by looking at the
    message displayed by the SQL server, otherwise he/she can use DESC command as

    follows:

    g

    Now, “Customers” table is created and available in database. It can be used to store
    required information related to “Customers”.
    Notice that DESC is the same as DESCRIBE is some RDBMS.

    e. Create Table Using another Table:

    A copy of an existing table can be created using a combination of the CREATE TABLE
    statement and the SELECT statement.
    The new table has the same column definitions. All columns or specific columns can
    be selected.
    When you create a new table using existing table, new table would be populated
    using existing values in the old table.
    The basic syntax for creating a table from another table is as follows:


    create table new_table_name as like existing_table_name [ where ]

    Example:
    To create a table called SALARY having the same attributes like table “Customers”,
    write

    Create table salary like “Customers”;

    v

    The structure of SALARY is displayed in the following interface.

    d

    f. To remove a table from a database, use the SQL Command “DROP TABLE”.
    The SQL DROP TABLE statement is used to remove a table definition and all its data.
    Notice that the user has to be careful while using this command because once
    a table is deleted then all the information available in the table would also be lost
    forever.

    The Basic syntax of DROP TABLE statement is as follows:

    i

    Example:
    DROP TABLE “Customers”;
    To make sure that the table has been removed, check with

    DESC “Customers”; and the answer will be like the following:

    b

    “CUSTOMERS” Relation

    d

    g. To add, delete or modify columns in an existing table, use the SQL
    Command ALTER TABLE followed by either ADD or DROP or MODIFY.


    ALTER TABLE command can also be used to add and drop various constraints on an

    existing table.

    The basic syntax of ALTER TABLE to add a new column in an existing table is as
    follows:

            alter table table_name add column_name datatype;

    The basic syntax of ALTER TABLE to DROP COLUMN in an existing table is as follows:

                   alter table table_name drop column column_name;

    The basic syntax of ALTER TABLE to change the DATA TYPE of a column in a table is
    as follows:

           alter table table_name modify column column_name datatype;

    The basic syntax of ALTER TABLE to add a NOT NULL constraint to a column in a
    table is as follows:

           alter table table_name modify column_name datatype not null;

    The basic syntax of ALTER TABLE to ADD UNIQUE CONSTRAINT to a table is as
    follows:
       alter table table_name

       add constraint myuniqueconstraint unique(column1, column2...);

    The basic syntax of ALTER TABLE to ADD CHECK CONSTRAINT to a table is as follows:
            alter table table_name

            add constraint myuniqueconstraint check (condition);

    The basic syntax of ALTER TABLE to ADD PRIMARY KEY constraint to a table is as
    follows:
    alter table table_name add constraint myprimarykey primary key (column1,

    column2...);

    The basic syntax of ALTER TABLE to DROP CONSTRAINT from a table is as follows:

    alter table table_name drop constraint myuniqueconstraint;

    If you’re using MySQL, the code is as follows:

    alter table table_name drop index myuniqueconstraint;

    The basic syntax of ALTER TABLE to DROP PRIMARY KEY constraint from a table is
    as follows:

    alter table table_name drop constraint myprimarykey;

    If you’re using MySQL, the code is as follows:

    alter table table_name drop primary key;

    Application activity 4.7:
    You are given a flat database named “Library”, with a relation Book (ISBN (Text,
    primary key), title (text), author (text), pages (integer), and price (integer)) Create
    this database and relation, then insert at least five records.

    4.2.1.a SQL Constraints

    Activity 4.8:

    Consider “Customers” relation, perform the following tasks;
    i. Add new column “sex”
    ii. Change the datatype of salary to decimals
    iii. Add “Not null” constraint to age field (column)
    iv. Remove the column “sex”
    v. Drop “not null” constraint from age field.

    Constraints are the rules enforced on data columns of a table. These are used to limit

    the type of data that can go into a table. This ensures the accuracy and reliability of
    the data in the database.

    Constraints could be column level or table level. Column level constraints are applied

    only to one column whereas table level constraints are applied to the whole table.

    Following are commonly used constraints available in SQL. These constraints have

    already been discussed inSQL - RDBMS

    Following are commonly used constraints available in SQL:

    Not null constraint: ensures that a column cannot have null value.
    Default constraint: provides a default value for a column when none is
    specified.
    Unique constraint: ensures that all values in a column are different.
    Primary key: uniquely identified each rows/records in a database table.
    Foreign key: uniquely identified a row/record in any other database table.
    Check constraint: the check constraint ensures that all values in a column
    satisfy certain conditions.
    Index: use to create and retrieve data from the database very quickly.

    NOT NULL Constraint:

    By default, a column can hold NULL values. If the user does not want a column to
    have a NULL value, then he/she needs to define such constraint on this column
    specifying that NULL is now not allowed for that column. A NULL is not the same as
    no data, rather, it represents unknown data.

    Example:
    For example, the following SQL query creates a new table called “CUSTOMERS” and
    adds five columns, three of which, ID and NAME and AGE, specify not to accept

    NULLs:

    create table “Customers”(
    id int       not null,
    name varchar (20)     not null,
    age int                        not null,
    address char (25) ,
    salary        Int,
    primary key (id)

    );

    a. DEFAULT Constraint:
    The DEFAULT constraint provides a default value to a column when the INSERT INTO
    statement does not provide a specific value.

    Example:

    For example, the following SQL creates a new table called “CUSTOMERS” and adds
    five columns. Here, SALARY column is set to 5000 by default, so in case INSERT INTO
    statement does not provide a value for this column, then by default this column

    would be set to 5000.

    d

    If “Customers” table has already been created, then to add a DEFAULT constraint to

    SALARY column, write a statement similar to the following:

    \

    Drop “Default” Constraint:

    To drop a DEFAULT constraint, use the following SQL:

    d

    b. UNIQUE Constraint:
    The UNIQUE Constraint prevents two records from having identical values in a
    particular column. In the “Customers” table, for example, you might want to prevent
    two or more people from having identical age.

    Example:

    For example, the following SQL creates a new table called “CUSTOMERS” and adds
    five columns. Here, AGE column is set to UNIQUE, so that you cannot have two

    records with same age:

    d

    If “CUSTOMERS” table has already been created, then to add a UNIQUE constraint to

    AGE column, you would write a statement similar to the following

    c

    The user can also use the following syntax, which supports naming the constraint in

    multiple columns as well:

    m

    Drop a UNIQUE Constraint:

    To drop a UNIQUE constraint, use the following SQL:

    m

    If you are using MySQL, then you can use the following syntax

    m

    c. PRIMARY Key:
    A primary key is a field in a table which uniquely identifies each row/record in a
    database table. Primary keys must contain unique values. A primary key column
    cannot have NULL values.
    A table can have only one primary key, which may consist of single or multiple fields.
    When multiple fields are used as a primary key, they are called a composite key.
    If a table has a primary key defined on any field(s), then it is impossible to have two
    records having the same value of that field(s).
    Notice that these concepts can be used while creating database tables.

    The syntax to define ID attribute as a primary key in a “CUSTOMERS” table is:

    x

    To create a PRIMARY KEY constraint on the “ID” column when “CUSTOMERS” table
    already exists, use the following SQL syntax:

    Alter table customers add primary key (ID);

    Notice that to use the ALTER TABLE statement to add a primary key, the primary key
    column(s) must already have been declared to not contain NULL values (when the
    table was first created).

    For defining a PRIMARY KEY constraint on multiple columns, use the following SQL

    syntax:

    d

    To create a PRIMARY KEY constraint on the “ID” and “NAMES” columns when

    “CUSTOMERS” table already exists, use the following SQL syntax:

    x

    To delete the Primary Key constraints from the table,

    , use the Syntax:

    a

    d. FOREIGN Key:
    A foreign key is a key used to link two tables together. This is sometimes called a
    referencing key.

    The Foreign Key is a column or a combination of columns, whose values match a

    Primary Key in a different table.

    The relationship between 2 tables matches the Primary Key in one of the tables with
    a Foreign Key in the second table.

    If a table has a primary key defined on any field(s), then you cannot have two records

    having the same value of that field(s).

    Example:

    Consider the structure of the two tables as follows:

    “CUSTOMERS” table:

    m

    ORDERS table:

    d

    If ORDERS table has already been created, and the foreign key has not yet been, use

    the syntax for specifying a foreign key by altering a table.

    b

    Drop a FOREIGN KEY Constraint:

    To drop a FOREIGN KEY constraint, use the following SQL:

    y

    e. CHECK Constraint:
    The CHECK Constraint enables a condition to check the value being entered into a
    record. If the condition evaluates to true, the record violates the constraint and isn’t
    entered into the table.
    Example:
    For example, the following SQL creates a new table called “CUSTOMERS” and adds
    five columns. Here, we add a CHECK with AGE column, so that you cannot have any

    Customer below 18 years:

    t

    If “Customers” table has already been created, then to add a CHECK constraint to AGE

    column, you would write a statement similar to the following:

    t

    You can also use following syntax, which supports naming the constraint and

    multiple columns as well:

    g

    Drop a CHECK Constraint:
    To drop a CHECK constraint, use the following SQL. This syntax does not work with

    MySQL:

    m

    Dropping Constraints:
    Any constraint that you have defined can be dropped using the ALTER TABLE
    command with the DROP CONSTRAINT option.
    For example, to drop the primary key constraint in the EMPLOYEES table, you can

    use the following command:

    g

    Some implementations may provide shortcuts for dropping certain constraints. For
    example, to drop the primary key constraint for a table in Oracle, you can use the

    following command:

    v

    Some implementations allow you to disable constraints. Instead of permanently
    dropping a constraint from the database, you may want to temporarily disable the

    constraint, and then enable it later.

    Integrity Constraints:
    Integrity constraints are used to ensure accuracy and consistency of data in a
    relational database. Data integrity is handled in a relational database through the
    concept of referential integrity.
    There are many types of integrity constraints that play a role in referential integrity
    (RI). These constraints include Primary Key, Foreign Key, Unique Constraints and
    other constraints mentioned above.

    4.2.2 Data Manipulation Language (DML)

    A. Insert into command

    The SQL INSERT INTO Statement is used to add new rows of data into a table in the
    database.
    There are two basic syntaxes of INSERT INTO statement as follows:
    insert into table_name (column1, column2, column3,...columnn)] values
    (value1, value2, value3,...valuen);
    Here, column1, column2,..columnN are the names of the columns in the table into
    which you want to insert data.
    You may not need to specify the column(s) name in the SQL query if you are adding
    values for all the columns of the table. But make sure the order of the values is in the
    same order as the columns in the table. The SQL INSERT INTO syntax would be as

    follows:

    g

    Activity 4.9:
    Create/ insert six records in “Customers” table (relation). Use two possible

    ways to insert records (tuples) in a table:

    First method:

    e

    Second method:

    z

    All the above statements would produce the following records in “Customers” table:

    e

    B. Select statement
    The SELECT statement is used to select data from a database. The data returned is
    stored in a result table, called the result-set.

    SELECT Syntax

    r

    Here, column1, column2,... are the field names of the table you want to select data

    from. If you want to select all the fields available in the table, use the following syntax:

                 5

    SELECT * (Select all)
    The following SQL statement selects all the columns from the ““Customers”” table:

    SELECT * FROM “Customers”;

    4

    The SQL SELECT DISTINCT Statement
    The SELECT DISTINCT statement is used to return only distinct (different) values.
    Inside a table, a column often contains many duplicate values; and sometimes you
    only want to list the different (distinct) values. The SELECT DISTINCT statement is

    used to return only distinct (different) values.

    SELECT DISTINCT Syntax

    5

    SELECT Example
    The following SQL statement selects all (and duplicate) values from the “Address”
    column in the ““Customers”” table:
    Example
    SELECT Address FROM “Customers”;
    WHERE Clause Example
    The following SQL statement selects all the “Customers” from the address “Muhanga”,
    in the “Customers” table:
    Example
    SELECT * FROM “Customers”
    WHERE Address=’Muhanga’;

    The SQL AND, OR and NOT Operators

    The WHERE clause can be combined with AND, OR, and NOT operators. The AND,
    OR operators are used to filter records based on more than one condition: The AND
    operator displays a record if all the conditions separated by AND are TRUE. The OR
    operator displays a record if any of the conditions separated by OR is TRUE. The NOT

    operator displays a record if the condition(s) is NOT TRUE.

    t

    AND Example
    The following SQL statement selects all fields from ““Customers”” where address is
    “Nyamagabe” AND address is “Huye”:

    Example

    SELECT * FROM “Customers” WHERE Address=’Nyamagabe’ AND Address=’Huye’;

    OR Example

    The following SQL statement selects all fields from “Customers” where address is

    “Huye” OR “Nyamagabe”;

    f

    Example for NOT operator
    The following SQL statement selects all fields from “Customers” where address is NOT

    “Nyamagabe”:

    Example

    SELECT * FROM “Customers” WHERE NOT Address=’Nyamagabe’;

               y

    You can also combine the AND, OR and NOT operators.
    The following SQL statement selects all fields from ““Customers”” where address is
    “Nyamagabe” AND address must be “Huye” OR “Nyamagabe” (use parenthesis to form
    complex expressions):

    Example:

    SELECT * FROM “Customers” WHERE Address=’Nyamagabe’ AND (Address=’Huye’ OR

    Address=’Nyamagabe’);

    4

    The following SQL statement selects all fields from “Customers” where address is NOT
    “Nyamagabe” and NOT “GASABO”:

    Example:

    SELECT * FROM “Customers” WHERE NOT Address=”Nyamagabe” AND NOT Address=”GASABO”;

    Application activity 4.6:
    You are given a flat database named “Library”, with a relation Book (ISBN (Text, primary
    key), title (text), author (text), pages (integer), and price (integer))
    i. Create this database and relation, then insert at least five records.
    ii. Retrieve ISBN and price of books written by “Bigirumwami”
    iii. Retrieve books whose price is between 30,000 and 200,000 Rwf
    iv. Select title and pages of all books
    v. Show the books that have more than 300 pages or books that cost more
    than 4,000 Rwf
    vi. Retrieve books written by authors whose name is started by A, B or C.
    vii. Order the books ‘titles from A to Z.
    viii. Retrieve top three books.
    ix. Delete books which have less than 50 pages

    x. Delete books written by “Kagame”.

    C. Aggregate functions:
    1. SQL COUNT Function- The SQL COUNT aggregate function is used to
    count the number of rows in a database table.
    2. SQL MAX Function- The SQL MAX aggregate function allows us to select
    the highest (maximum) value for a certain column.
    3. SQL MIN Function- The SQL MIN aggregate function allows us to select
    the lowest (minimum) value for a certain column.
    4. SQL AVG Function- The SQL AVG aggregate function selects the average
    value for certain table column.
    5. SQL SUM Function- The SQL SUM aggregate function allows selecting the
    total for a numeric column.

    6. SQL COUNT Function

    SQL COUNT Function is the simplest function and very useful in counting the number

    of records, which are expected to be returned by a SELECT statement.

    e

    Similarly, if you want to count the number of records that meet a given criteria, it can

    be done as follows to count records whose salary is 2000:

    e

    Notice that all the SQL queries are case insensitive, so it does not make any
    difference if you write SALARY or salary in WHERE condition.

    SQL MAX Function

    SQL MAX function is used to find out the record with maximum value among a

    record set.

    f

    SQL MIN Function
    SQL MIN function is used to find out the record with minimum value among a record

    set.

    d

    You can use MIN Function along with MAX function to find out minimum value as

    well.

    s

    SQL AVG Function

    SQL AVG function is used to find out the average of a field in various records.

    y

    7. SQL SUM Function

    SQL SUM function is used to find out the sum of a field in various records.

    5

    Application activity 4.7:
    You are given a flat database named “Library”, with a relation Book (ISBN (Text,
    primary key), title (text), author (text), pages (integer), and price (integer))
    i. Create this database and relation, then insert at least five records.
    ii. Retrieve the amount to get when all books are sold.
    iii. Retrieve the most expensive book.
    iv. Select the least expensive book
    v. Show the total number of the books in book relation.

    vi. Find the average price of the books

    D. String Expressions
    SQL string functions are used primarily for string manipulation. The following table

    details the important string functions:

    r

    a. CONCAT (str1,str2,...)
    Returns the string that results from concatenating the arguments. May have one or
    more arguments. If all arguments are non-binary strings, the result is a non-binary
    string. If the arguments include any binary strings, the result is a binary string. A
    numeric argument is converted to its equivalent binary string form; if you want to

    avoid that, you can use an explicit type cast, as in this example:

    5

                  b. LEFT(str,len)

    Returns the leftmost len characters from the string str, or NULL if any argument is

    NULL.

    5

    c. LENGTH (str)
    Returns the length of the string str measured in bytes. A multi-byte character counts
    as multiple bytes. This means that for a string containing five two-byte characters,

    LENGTH( ) returns 10, whereas CHAR_LENGTH( ) returns 5.

    w

    d. LOWER(str)
    Returns the string str with all characters changed to lowercase according to the

    current character set mapping.

    4

    e. REVERSE(str)

    Returns the string str with the order of the characters reversed.

    r

    Application activity 4.8:
    You are given a flat database named “Library”, with a relation Book (ISBN (Text,
    primary key), title (text), author (text), pages (integer), and price (integer))
    i. Find the length of the title of the book which has 35050115-30 as ISBN
    ii. Reverse the name of the author who wrote the book “Imigenzo
    n’imiziririzo ya Kinyarwanda”.
    iii. Compare the names “Aloys” and “Alexis”.
    iv. Change “Ndi umunyarwanda” in upper case.
    v. Change “HELP EACH OTHER” in lower case

    E. SQL JOINS

    The SQL Joins clause is used to combine records from two or more tables in a
    database. A JOIN is a mean for combining fields from two tables by using values
    common to each.

    Consider “Customers” and orders tables as follows:

    u

    These two tables (relations) have the following records:

    m

    Now, let us join these two tables in our SELECT statement as follows:

    m

    This would produce the following result:

    m

    Here, it is noticeable that the join is performed in the WHERE clause. Several operators
    can be used to join tables, such as =, <, >, <>, <=, >=,! =, BETWEEN, LIKE, and NOT;
    they can all be used to join tables. However, the most common operator is the equal

    symbol.

    SQL Join Types:
    There are different types of joins available in SQL:
    Inner join: returns rows when there is a match in both tables.
    Left join: returns all rows from the left table, even if there are no matches in the right
    table.
    Right join: returns all rows from the right table, even if there are no matches in the
    left table.
    Full join: returns rows when there is a match in one of the tables.
    Self-join: is used to join a table to itself as if the table were two tables, temporarily
    renaming at least one table in the sql statement.
    Cartesian join: returns the Cartesian product of the sets of records from the two or

    more joined tables.

    1. INNER JOIN
    The most frequently used and important of the joins is the INNER JOIN. They are also
    referred to as an EQUIJOIN.

    The INNER JOIN creates a new result table by combining column values of two

    tables (table1 and table2) based upon the join-predicate. The query compares each
    row of table1 with each row of table2 to find all pairs of rows which satisfy the joinpredicate.
    When the join-predicate is satisfied, column values for each matched pair

    of rows of A and B are combined into a result row.

    The basic syntax of INNER JOIN is as follows:

    m

    m

    2. LEFT JOIN
    The SQL LEFT JOIN returns all rows from the left table, even if there are no matches
    in the right table. This means that if the ON clause matches 0 (zero) records in right
    table, the join will still return a row in the result, but with NULL in each column from
    right table.
    This means that a left join returns all the values from the left table, plus matched

    values from the right table or NULL in case of no matching join predicate.

    Syntax:

    The basic syntax of LEFT JOIN is as follows:

    m

    m

    3. RIGHT JOIN
    The SQL RIGHT JOIN returns all rows from the right table, even if there are no matches
    in the left table. This means that if the ON clause matches 0 (zero) records in left
    table, the join will still return a row in the result, but with NULL in each column from
    left table.
    This means that a right join returns all the values from the right table, plus matched
    values from the left table or NULL in case of no matching join predicate.

    The basic syntax of RIGHT JOIN is as follows:

    m

    m

    4. FULL JOIN
    The SQL FULL JOIN combines the results of both left and right outer joins.
    The joined table will contain all records from both tables, and fill in NULLs for missing
    matches on either side.

    The basic syntax of FULL JOIN is as follows:

    m

    m

    If your DBMS does not support FULL JOIN like MySQL does not support FULL JOIN,

    then you can use UNION ALL clause to combine two JOINS as follows:

    m

    5. SELF JOIN
    The SQL SELF JOIN is used to join a table to itself as if the table were two tables,
    temporarily renaming at least one table in the SQL statement.

    The basic syntax of SELF JOIN is as follows:

    y

    j

    6. CARTESIAN JOIN
    The CARTESIAN JOIN or CROSS JOIN returns the Cartesian product of the sets of
    records from the two or more joined tables. Thus, it equates to an inner join where
    the join-condition always evaluates to True or where the join condition is absent
    from the statement.


    The basic syntax of INNER JOIN is as follows:

    y

    y

    F. SQL Unions Clause
    The SQL UNION clause/operator is used to combine the results of two or more

    SELECT statementswithout returning any duplicate rows.

    To use UNION, each SELECT must have the same number of columns selected, the
    same number of column expressions, the same data type, and have them in the
    same order, but they do not have to be the same length.


    The basic syntax of UNION is as follows:

    r

    m

    1. The UNION ALL Clause:
    The UNION ALL operator is used to combine the results of two SELECT statements
    including duplicate rows. The same rules that apply to UNION apply to the UNION

    ALL operator.

    The basic syntax of UNION ALL is as follows:

    s

    t

    There are two other clauses (i.e., operators), which are very similar to UNION
    clause: SQLINTERSECT Clause: is used to combine two SELECT statements, but
    returns rows only from the first SELECT statement that are identical to a row in the
    second SELECT statement. SQLEXCEPT Clause: combines two SELECT statements and
    returns rows from the first SELECT statement that are not returned by the second
    SELECT statement.

    2. INTERSECT Clause

    The SQL INTERSECT clause/operator is used to combine two SELECT statements,
    but returns rows only from the first SELECT statement that are identical to a row in
    the second SELECT statement. This means INTERSECT returns only common rows
    returned by the two SELECT statements.

    Just as with the UNION operator, the same rules apply when using the INTERSECT

    operator. MySQL does not support INTERSECT operator


    The basic syntax of INTERSECT is as follows:

    r

    Example:

    5

    3. EXCEPT Clause
    The SQL EXCEPT clause/operator is used to combine two SELECT statements and
    returns rows from the first SELECT statement that are not returned by the second
    SELECT statement. This means EXCEPT returns only rows, which are not available in
    second SELECT statement.

    Just as with the UNION operator, the same rules apply when using the EXCEPT

    operator. MySQL does not support EXCEPT operator.


    The basic syntax of EXCEPT is as follows:

    e

    Example:

    4

    Select statement with Alias
    You can rename a table or a column temporarily by giving another name known as

    alias.

    The use of table aliases means to rename a table in a particular SQL statement. The
    renaming is a temporary change and the actual table name does not change in the
    database. The column aliases are used to rename a table’s columns for the purpose
    of a particular SQL query.


    The basic syntax of table alias is as follows:

    r

    The basic syntax of column alias is as follows:

    4

    t

    Following is the usage of column alias:

    t

    y

    G. SQL TRUNCATE TABLE
    The SQL TRUNCATE TABLE command is used to delete complete data from an
    existing table.
    You can also use DROP TABLE command to delete complete table but it would
    remove complete table structure form the database and you would need to recreate
    this table once again if you wish you store some data.

    The basic syntax of TRUNCATE TABLE is as follows:

    3

    Example:

    e

    H. SQL HAVING CLAUSE
    The HAVING clause enables you to specify conditions that filter which group results
    appear in the final results. The WHERE clause places conditions on the selected
    columns, whereas the HAVING clause places conditions on groups created by the
    GROUP BY clause.

    The following is the position of the HAVING clause in a query:

    3

    The HAVING clause must follow the GROUP BY clause in a query and must also
    precede the ORDER BY clause if used. The following is the syntax of the SELECT

    statement, including the HAVING clause:

    r

    Example:
    Following is the example, which would display record for which similar age count

    would be more than or equal to 2:

    l

    I . SQL SUB QUERIES
    A Subquery or Inner query or Nested query is a query within another SQL query and
    embedded within the WHERE clause.

    A subquery is used to return data that will be used in the main query as a condition

    to further restrict the data to be retrieved.

    Subqueries can be used with the SELECT, INSERT, UPDATE, and DELETE statements

    along with the operators like =, <, >, >=, <=, IN, BETWEEN etc.

    There are a few rules that subquery must follow:

    * Subquery must be enclosed within parentheses.
    * A subquery can have only one column in the SELECT clause, unless multiple
    columns are in the main query for the subquery to compare its selected
    columns.
    * Subqueries that return more than one row can only be used with multiple
    value operators, such as the IN operator.
    * A subquery cannot be immediately enclosed in a set function.
    * The BETWEEN operator cannot be used with a subquery; however, the
    BETWEEN operator can be used within the subquery.

    I.1. Subqueries with the SELECT Statement:

    Subqueries are most frequently used with the SELECT statement. The basic syntax

    is as follows:

    h

    f

    I.2. Subqueries with the INSERT Statement:
    Subqueries also can be used with INSERT statements. The INSERT statement uses the
    data returned from the subquery to insert into another table. The selected data in

    the subquery can be modified with any of the character, date or number functions.

    The basic syntax is as follows:

    n

    Example:
    Consider a table “CUSTOMERS”_BKP with similar structure as “CUSTOMERS” table.
    Now to copy complete “CUSTOMERS” table into “CUSTOMERS”_BKP, following is the

    syntax:

    b

    I.3. Subqueries with the UPDATE Statement:
    The subquery can be used in conjunction with the UPDATE statement. Either single
    or multiple columns in a table can be updated when using a subquery with the

    UPDATE statement.

    The basic syntax is as follows:

    d

    Example:
    Assuming, we have “CUSTOMERS”_BKP table available which is backup of
    “CUSTOMERS” table.

    Following example updates SALARY by 0.25 times in “CUSTOMERS” table for all the

    “Customers” whose AGE is greater than or equal to 27:

    n

    I.4. Subqueries with the DELETE Statement:
    The subquery can be used in conjunction with the DELETE statement like with any

    other statements mentioned above.

    The basic syntax is as follows:

    g

    Example:
    Assuming, we have “CUSTOMERS”_BKP table available which is backup of
    “CUSTOMERS” table.

    Following example deletes records from “CUSTOMERS” table for all the “Customers”

    whose AGE is greater than or equal to 27:

    v

    delete
    The SQL DELETE Query is used to delete the existing records from a table.

    You can use WHERE clause with DELETE query to delete selected rows, otherwise all

    the records would be deleted.


    The basic syntax of DELETE query with WHERE clause is as follows:

    m

    Following is an example, which would DELETE a customer, whose ID is 6:

    d

    If you want to DELETE all the records from “CUSTOMERS” table, you do not need to

    use WHERE clause and DELETE query would be as follows:

    m

    4.2.3 Data Control Language (DCL)

    SQL GRANT and REVOKE commands

    DCL commands are used to enforce database security in a multiple user database
    environment. Two types of DCL commands are GRANT and REVOKE. Only Database
    Administrator’s or owners of the database object can provide/remove privileges on

    a database object.

    a. SQL GRANT Command
    SQL GRANT is a command used to provide access or privileges on the database
    objects to the users.


    The Syntax for the GRANT command is:

    s

    * privilege_name is the access right or privilege granted to the user. Some of
    the access rights are ALL, EXECUTE, and SELECT.
    * object_name is the name of an database object like TABLE, VIEW, STORED
    PROC and SEQUENCE.
    * user_name is the name of the user to whom an access right is being granted.
    * Public is used to grant access rights to all users.
    * Roles are a set of privileges grouped together.
    * With grant option - allows a user to grant access rights to other users.


    For Example:
    GRANT SELECT ON employee TO user1; This command grants a SELECT permission
    on employee table to user1.You should use the WITH GRANT option carefully
    because for example if you GRANT SELECT privilege on employee table to user1
    using the WITH GRANT option, then user1 can GRANT SELECT privilege on employee
    table to another user, such as user2 etc. Later, if you REVOKE the SELECT privilege on

    employee from user1, still user2 will have SELECT privilege on employee table.

    b. SQL REVOKE Command:
    The REVOKE command removes user access rights or privileges to the database
    objects.

    The Syntax for the REVOKE command is:

    a

    For Example:
    REVOKE SELECT ON employee FROM user1;This command will REVOKE a SELECT
    privilege on employee table from user1.When you REVOKE SELECT privilege on a
    table from a user, the user will not be able to SELECT data from that table anymore.
    However, if the user has received SELECT privileges on that table from more than one
    users, he/she can SELECT from that table until everyone who granted the permission

    revokes it. You cannot REVOKE privileges if they were not initially granted by you.

    c. Privileges and Roles:
    Privileges: Privileges defines the access rights provided to a user on a database
    object. There are two types of privileges.
    1. System privileges - This allows the user to CREATE, ALTER, or DROP
    database objects.
    2. Object privileges - This allows the user to EXECUTE, SELECT, INSERT,
    UPDATE, or DELETE data from database objects to which the privileges

    apply.

    Application activity 4.9:
    You are given a flat database named “Library”, with a relation Book (ISBN (Text,
    primary key), title (text), author (text), pages (integer), and price (integer)).
    i. Create student and teacher users
    ii. Give student user “selection” abilities only
    iii. Give all rights to user teacher

    iv. Revoke selection rights to students

    Application activity 4.10:
    XYZ Ltd is a company that focusses on finding ICT and technology related
    solution to the citizens of Rwanda. It develops software and offers maintenance.
    It has many competitors in Rwanda and in East Africa, but XYZ Ltd tries to be a
    market winner in the region. One day, unknown person managed to have access
    and enter to the XYZ Ltd systems without company’s authorization. That person
    managed to change the passwords that the company used in its everyday
    activities.
    1. Discuss the challenges that the company should face
    2. Can granting privileges help in preventing such cases? If yes, show

    how. If no, explain

    4.3.Database security concept
    As computers need to be physically and logically protected, the database inside
    needs also to be secured. There are some principles linked to databases so that
    they can remain meaningful. Those principles are integrity, Availability, Privacy and
    Confidentiality.
    When dealing with a database belonging to an individual or an organization
    (company), Some actions are done to Backup and Concurrent control for the sake

    of security.

    Activity 4.10:
    1. What are the problems that a database can face in a computer?
    2. How the security of database can be done?
    3. Who is responsible of the security of a database?
    4. What are the consequences that can happen when database security is
    violated?
    5. Discuss security measure that can be taken to keep database secure.

    Data integrity refers to the overall completeness, accuracy and consistency of data.

    4.3.1. Integrity
    There are four types of integrity:
    »»Entity (or table) integrity requires that all rows in a table have a unique identifier,
    known as the primary key value. Whether the primary key value can be changed,
    or whether the whole row can be deleted, depends on the level of integrity
    required between the primary key and any other tables.

    »»Referential integrity
    ensures that the relationship between the primary key (in a

    referenced table) and the foreign key (in each of the referencing tables) is always
    maintained. The maintenance of this relationship means that:
    i. A row in a referenced table cannot be deleted, nor can the primary key be
    changed, if a foreign key refers to the row. For example, you cannot delete
    a customer that has placed one or more orders.
    ii. A row cannot be added to a referencing table if the foreign key does not
    match the primary key of an existing row in the referenced table. For
    example, you cannot create an order for a customer that does not exist.
    »»Domain (or column) integrity specifies the set of data values that are valid for
    a column and determines whether null values are allowed. Domain integrity is
    enforced by validity checking and by restricting the data type, format, or range of
    possible values allowed in a column.
    »»User-Defined integrity: Enforces some specific business rules that do not fall into
    entity, domain, or referential integrity.

    4.3.2. Availability

    Availability is the condition where in a given resource can be accessed by its
    consumers. So in terms of databases, availability means that if a database is available,
    the users of its data; that is, applications, “Customers”, and business users; can access
    it. Any condition that renders the resource inaccessible causes the opposite of
    availability: unavailability.

    Another perspective on defining availability is the percentage of time that a system

    can be used for productive work. The required availability of an application will vary
    from organization to organization, within an organization from system to system,

    and even from user to user.

    Database availability and database performance are terms that are often confused
    with one another, and indeed, there are similarities between the two. The major
    difference lies in the user’s ability to access the database. It is possible to access a
    database suffering from poor performance, but it is not possible to access a database
    that is unavailable. So, when does poor performance turn into unavailability?

    If performance suffers to such a great degree that the users of the database cannot

    perform their job, the database has become, for all intents and purposes, unavailable.
    Nonetheless, keep in mind that availability and performance are different and must
    be treated by the database administrator as separate issues; even though a severe
    performance problem is a potential availability problem.

    Availability comprises four distinct components, which, in combination, assure that

    systems are running and business can be conducted:

    Manageability: the ability to create and maintain an effective environment that

    delivers service to users
    Recoverability: the ability to reestablish service in the event of an error or
    component failure
    Reliability: the ability to deliver service at specified levels for a stated period
    Serviceability: the ability to determine the existence of problems, diagnose their
    cause(s), and repair the problems.

    All four of these “abilities” impact the overall availability of a system, database, or

    application.

    4.3.3. Data Privacy

    Privacy of information is extremely important in this digital age where everything is
    interconnected and can be accessed and used easily. The possibilities of our private
    information being extremely vulnerable are very real, which is why we require data
    privacy. We can describe the concept as:

    Data privacy,
    also known as information privacy, is the necessity to preserve and

    protect any personal information, collected by any organization, from being accessed
    by a third party. It is a part of Information Technology that helps an individual or an
    organization determine what data within a system can be shared with others and

    which should be restricted.

    What Type of data is included?
    Any personal data that could be sensitive or can be used maliciously by someone
    is included when considering data privacy. These data types include the following:
    • Online Privacy: This includes all personal data that is given out during
    online interactions. Most sites have a privacy policy regarding the use of
    the data shared by users or collected from users.
    • Financial Privacy: Any financial information shared online or offline is
    sensitive as it can be utilized to commit fraud.
    • Medical Privacy: Any details of medical treatment and history is privileged
    information and cannot be disclosed to a third party. There are very
    stringent laws regarding sharing of medical records.
    • Residential and geographic records: sharing of address online can be a
    potential risk and needs protection from unauthorized access.
    • Political Privacy: this has become a growing concern that political
    preferences should be privileged information.
    • Problems with providing Data Security
    • It is not an easy task to provide data security. Most organizations have
    problems in providing proper information privacy. These problems
    include:
    • Difficulty in understanding and defining what is sensitive data and what
    is not.
    • With data growing in volume by the day, most organizations struggle
    to create real-time masking facilities and security policies to efficiently
    protect all the data.
    • Difficulty to screen and review data from a central location with outmoded
    tools and bloated databases.

    Importance of Data Security

    * Data security is extremely important for any individual or organization, as
    theft of data, can cause huge monetary losses. Data security can help an
    organization by:
    • Preventing theft of data;
    • Preserving data integrity;
    • Containing a cost of compliance to data security requirements;

    • Protection of privacy.

    Legal provisions for Data Security
    The laws that govern data security vary across the world. Different countries and legal
    systems deal with it in their way. But most laws agree that personal data is shared
    and processed only for the purpose for which the information has been collected.
    In Rwanda we have RURA (Rwanda Utilities Regulatory Agency) that govern data

    security issues.

    4.3.4. Confidentiality
    Confidentiality refers to protecting information from being accessed by unauthorized
    parties. In other words, only the people who are authorized to do so can gain access
    to sensitive data. Imagine your bank records. You should be able to access them, of
    course, and employees at the bank who are helping you with a transaction should
    be able to access them, but no one else should.

    A failure to maintain confidentiality means that someone who shouldn’t have access

    has managed to get it, through intentional behavior or by accident. Such a failure of
    confidentiality, commonly known as a breach, typically cannot be remedied. Once
    the secret has been revealed, there’s no way to un-reveal it.

    If your bank records are posted on a public website, everyone can know your bank

    account number, balance, etc., and that information can’t be erased from their
    minds, papers, computers, and other places. Nearly all the major security incidents

    reported in the media today involve major losses of confidentiality.

    a. Backup
    In information technology, a backup, or the process of backing up, refers to the
    copying and archiving of computer data so it may be used to restore the original
    after a data loss event.

    A catastrophic failure is one where a stable, secondary storage device gets corrupt.

    With the storage device, all the valuable data that is stored inside is lost. We have
    two different strategies to recover data from such a catastrophic failure:


    * Remote backup – Here a backup copy of the database is stored at a remote
    location from where it can be restored in case of a catastrophe.
    * Alternatively, database backups can be taken on magnetic tapes and stored
    at a safer place. This backup can later be transferred onto a freshly installed
    database to bring it to the point of backup. Grown-up databases are too
    bulky to be frequently backed up. In such cases, we have techniques where
    we can restore a database just by looking at its logs.
    So, all that we need to do here is to take a backup of all the logs at frequent intervals
    of time. The database can be backed up once a week, and the logs being very small
    can be backed up every day or as frequently as possible.

    b. Remote access

    Individuals, small and big institutions/companies are using databases in their daily
    businesses. Most of the time institutions have agencies spread around the country,
    region or the world. Umwalimu SACCO is a saving and credit Cooperative that
    helps teachers to improve their lives by getting financial loans at low interests. This
    institution is having different agencies in different districts. The central agency is
    located at Kigali and host the main database of all members of Umwalimu SACCO
    in Rwanda. When a client goes to look for a service at an agency, the teller requests
    permissions from Kigali by identifying, authenticating him/her self so that the
    authorization can be granted to him/her. The whole network works in the mode of
    Cleint/Server. The fact of getting connection to the server from far is what we call
    “Remote Access”. Hence, the database is accessed remotely. This act requires some
    security measures because otherwise anybody can disturbs the system of working
    and hack the whole business system of Umwalimu SACCO.
    This institution needs then to set rules and regulations to manage the remote access
    to its information.

    c. Concurrent control

    Process of managing simultaneous operations on the database without having
    them interfere with one another.
    - Prevents interference when two or more users are accessing database
    simultaneously and at least one is updating data.
    - Although two transactions may be correct in themselves, interleaving of

    operations may produce an incorrect result.

    Three examples of potential problems caused by concurrency:
    • Lost update problem.
    • Uncommitted dependency problem.
    • Inconsistent analysis problem.

    Application activity 4.11:

    1. Discuss the advantages of data backup.
    2. Explain the advantages of remote access to big companies.
    3. Discuss the reasons why a database administrator must understand
    well the concept of privacy as used in database.
    4. Compare the concepts of integrity, availability and confidentiality.
    Discuss what should happen when they are violated (case by case).
    5. A secondary school has a Management Information System hosted on
    their own server located inside their compound. All the important data
    related to students, teachers, salaries, marks, library, etc. are in that
    server.
    One day, it happened that the Head Master finds that some students have the
    lists of their marks in all courses before deliberation. He investigated and found
    that the students did not get marks from any teachers or administrative/technical
    staff of the school.
    • In groups, discuss what should happened in the server of the school
    • Is data privacy assured in that server?
    • What are the measures that the school has to put in place to protect
    their data.

    4.3.5. Database threats

    Activity 4.11

    i. From what you have seen in computer security, what are the possible
    threats of computers?
    ii. What do you do in case you realize that there are threats for your
    computer system?

    iii. Can database be attacked by hackers?

    Threat is any situation or event, whether intentional or unintentional, that will
    adversely affect a system and consequently an organization.

    Threats to databases result in the loss or degradation of some or all of the following

    security goals: integrity, availability, and confidentiality.

    • Loss of integrity: Database integrity refers to the requirement that
    information be protected from improper modification. Modification of
    data includes creation, insertion, modification, changing the status of
    data, and deletion. Integrity is lost if unauthorized changes are made to
    the data by either intentional or accidental acts. If the loss of system or
    data integrity is not corrected, continued use of the contaminated system
    or corrupted data could result in inaccuracy, fraud, or erroneous decisions.
    • Loss of availability: Database availability refers to making objects
    available to a human user or a program to which they have a legitimate
    right.
    • Loss of confidentiality: Database confidentiality refers to the protection
    of data from unauthorized disclosure. The impact of unauthorized
    disclosure of confidential information can range from violation of the
    Data Privacy Act to the jeopardization of national security. Unauthorized,
    unanticipated, or unintentional disclosure could result in loss of public
    confidence, embarrassment, or legal action against the organization.

    In other words we can say that database threats can appear in form of unauthorized

    users, physical damage, and Data corruption.

    a. Database protection

    The protection of a database can be done through access control and data encryption

    Access control
    - Based on the granting and revoking of privileges. 
    - A privilege allows a user to create or access (that is read, write, or modify)
    some database object (such as a relation, view, and index) or to run certain
    DBMS utilities.
    -  Privileges are granted to users to accomplish the tasks required for their

    jobs.

    Data encryption
    - The encoding of the data by a special algorithm that renders the data
    - unreadable by any program without the decryption key.

    Application activity 4.12:

    You are tasked to design a database of your school. Discuss the steps you will
    follow to come up with genuine product by keeping in mind the data security

    and the protection of the database against the potential threats.

    4.3.6. Database planning and designing
    Activity 4.12

    1. Discuss the pillars of database design
    2. Why is database planning necessary?
    3. Discuss activities involved in database planning.

    Before planning, designing and managing a database, first it is created. Its creation
    goes through defined steps known as Database System Development Lifecycle.

    Those steps are:

    • Database planning
    • System definition
    • Requirements collection and analysis
    • Database design
    • DBMS selection (optional)
    • Prototyping (optional)
    • Implementation
    • Data conversion and loading
    • Testing

    • Operational maintenance

    A. Database Planning

     Management activities that allow stages of database system development
    lifecycle to be realized as efficiently and effectively as possible.
    ◊ Must be integrated with overall information system strategy of the
    organization.
    ◊ Database planning should also include development of standards that
    govern:
    • How data will be collected,
    • How the format should be specified,
    • What necessary documentation will be needed,
    • How design and implementation should proceed.

    Database Planning – Mission Statement:

    • Mission statement for the database project defines major aims of database
    application.
    • Those driving database project normally define the mission statement.
    • Mission statement helps clarify purpose of the database project and
    provides clearer path towards the efficient and effective creation of
    required database system.

    Database Planning – Mission Objectives:

    • Once mission statement is defined, mission objectives are defined.
    • Each objective should identify a particular task that the database must
    support.
    • May be accompanied by some additional information that specifies the
    work to be done, the resources with which to do it, and the money to pay

    for it all.

    System Definition
    • Database application may have one or more user views.
    • Identifying user views helps ensure that no major users of the database
    are forgotten when developing requirements for new system.
    • User views also help in development of complex database system allowing
    requirements to be broken down into manageable pieces.

    Requirements Collection and Analysis

    • Process of collecting and analysing information about the part of
    organization to be supported by the database system, and using this
    information to identify users’ requirements of new system.
    • Information is gathered for each major user view including:
    * a description of data used or generated;
    * details of how data is to be used/generated;
    * any additional requirements for new database system.
    * Information is analyzed to identify requirements to be included in new

    database system. Described in the requirements specification.

    • Another important activity is deciding how to manage the requirements

    for a database system with multiple user views.

    Three main approaches:
    • centralized approach;
    • view integration approach;
    • combination of both approaches(hybrid).

    Centralized approach

    • Requirements for each user view are merged into a single set of
    requirements.
    • A data model is created representing all user views during the database

    design stage.

    View integration approach
    * Requirements for each user view remain as separate lists.
    * Data models representing each user view are created and then merged
    later during the database design stage.
    ◊ Data model representing single user view (or a subset of all user views) is
    called a local data model.
    ◊ Each model includes diagrams and documentation describing requirements
    for one or more but not all user views of database.
    ◊ Local data models are then merged at a later stage during database design
    to produce a global data model, which represents all user views for the

    database.

    B. Database Design
    In every institution, there is a process of creating a design for a database that will
    support the enterprise’s mission statement and mission objectives for the required
    database system.
    The main approaches include:
    ◊ Top-down or Entity-Relationship Modelling

    ◊ Bottom-up or Normalisation

    Top-down approach
    * Starts with high-level entities and relationships with successive
    refinement to identify more detailed data model.

    * Suitable for complex databases.

    Bottom-up approach
    * Starts with a finite set of attributes and follows a set of rules to group
    attributes into relations that represent entities and relationships.

    * Suitable for small number of attributes.

    The main purposes of data modeling include:
    * to assist in understanding the meaning (semantics) of the data;
    * to facilitate communication about the information requirements.
    * Building data model requires answering questions about entities,

    relationships, and attributes.

    There are three phases of database design: Conceptual database design, Logical
    database design and Physical database design.

    Conceptual Database Design:
    * Process of constructing a model of the data used in an enterprise,
    independent of all physical considerations.
    * Data model is built using the information in users’ requirements
    specification.
    * Conceptual data model is source of information for logical design phase.

    Logical Database Design:

    * Process of constructing a model of the data used in an enterprise based
    on a specific data model (e.g. relational), but independent of a particular
    DBMS and other physical considerations.
    * Conceptual data model is refined and mapped on to a physical data
    model.

    Physical Database Design:

    * Process of producing a description of the database implementation on
    secondary storage.
    * Describes base relations, file organizations, and indexes used to achieve
    efficient access to data. Also describes any associated integrity constraints
    and security measures.

    * Tailored to a specific DBMS.

    DBMS Selection:
    The selection of an appropriate DBMS to support the database system follows these
    steps: define Terms of Reference of study; shortlist two or three products; evaluate

    products and Recommend selection and produce report.

    END UNIT ASSESSMENT
    Part I: Relational algebra and SQL statements
    1. 1. A company organizes its activities in projects. Products that are used in
    the projects are bought from suppliers. This is described in a database with
    the following schema: Projects(projNbr, name, city) Products(prodNbr,
    name, color) Suppliers(supplNbr, name, city) Deliveries(supplNbr, prodNbr,
    projNbr, number). Write relational algebra expressions that give the
    following information:
    a. All information about all projects.
    b. All information about all projects in Kigali.
    c. The supplier numbers of the suppliers that deliver to project number 123.
    d. The product numbers of products that are delivered by suppliers in Kigali.
    e. All pairs of product numbers such that at least one supplier delivers
    both products.
    4. The following relations keep track of airline flight information:
    Flights(flno: integer, from: string, to: string, distance: integer, departs: time,
    arrives: time, price: real) Aircraft(aid: integer, aname: string, cruisingrange: integer)
    Certified(eid: integer, aid: integer) Employees(eid: integer, ename: string, salary:
    integer)

    Note that the Employees relation describes pilots and other kinds of employees as

    well; every pilot is certified for some aircraft, and only pilots are certified to fly. Write
    each of the following queries in SQL.

    i. Find the names of aircraft such that all pilots certified to operate them have

    salaries more than $80,000.
    ii. For each pilot who is certified for more than three aircraft, find the eid and
    the maximum cruising range of the aircraft for which she or he is certified.
    iii. Find the names of pilots whose salary is less than the price of the cheapest
    route from Kigali to New York.
    iv. For all aircraft with cruising range over 1000 miles, find the name of the
    aircraft and the average salary of all pilots certified for this aircraft.
    v. Find the names of pilots certified for some Boeing aircraft.
    vi. Find the aids of all aircraft that can be used on routes from Kigali to London.
    vii. Identify the routes that can be piloted by every pilot who makes more than 
    $100,000.
    viii. Print the names of pilots who can operate planes with cruising range
    greater than 3000 Km but are not certified on any Boeing aircraft.
    ix. A customer wants to travel from Kigali to New York with no more than
    two changes of flight. List the choice of departure times from Kigali if the
    customer wants to arrive in New York by 6 p.m.
    x. Compute the difference between the average salary of a pilot and the
    average salary of all employees (including pilots).
    xi. Print the name and salary of every non pilot whose salary is more than the
    average salary for pilots.
    xii. Print the names of employees who are certified only on aircrafts with
    cruising range longer than 1000 Km.
    xiii. Print the names of employees who are certified only on aircrafts with
    cruising range longer than 1000 Km, but on at least two such aircrafts.
    xiv. Print the names of employees who are certified only on aircrafts with
    cruising range longer than 1000 Km and who are certified on some Boeing

    aircraft.

    Part II: Database projects
    1. Consider the following relations:
    Student (snum: integer, sname: string, major: string, level: string, age: integer)
    Class (name: string, meets at: string, room: string, fid: integer)
    Enrolled (snum: integer, cname: string)
    Faculty (fid: integer, fname: string, deptid: integer)
    The meaning of these relations is straightforward; for example, Enrolled has one
    record per student-class pair such that the student is enrolled in the class. Level is a
    two character code with 4 different values (example: Junior: A Level etc)
    Write the following queries in SQL. No duplicates should be printed in any of the
    answers.
    i. Find the names of all Juniors (level = A Level) who are enrolled in a class
    taught by Prof. Kwizera
    ii. ii. Find the names of all classes that either meet in room R128 or have five or
    more Students enrolled.
    iii. Find the names of all students who are enrolled in two classes that meet at
    the same time.
    iv. Find the names of faculty members who teach in every room in which some
    class is taught.
    v. Find the names of faculty members for whom the combined enrollment of

    the courses that they teach is less than five.

    2. The following relations keep track of airline flight information:
    Flights (no: integer, from: string, to: string, distance: integer, Departs: time, arrives:
    time, price: real) Aircraft (aid: integer, aname: string, cruisingrange: integer)
    Certified (eid: integer, aid: integer)
    Employees (eid: integer, ename: string, salary: integer)
    Note that the Employees relation describes pilots and other kinds of employees as
    well; every pilot is certified for some aircraft, and only pilots are certified to fly.
    Write each of the following queries in SQL:
    i. Find the names of aircraft such that all pilots certified to operate them have
    salaries more than Rwf.80, 000.
    ii. For each pilot who is certified for more than three aircrafts, find the eid and
    the maximum cruisingrange of the aircraft for which she or he is certified.
    iii. Find the names of pilots whose salary is less than the price of the cheapest
    route from Kigali to Nairobi.
    iv. For all aircraft with cruisingrange over 1000 Kms,. Find the name of the
    aircraft and the average salary of all pilots certified for this aircraft.
    v. Find the names of pilots certified for some Boeing aircraft.
    vi. Find the aids of all aircraft that can be used on routes from Rusizi to Kigali.


    3. Consider the following database of student enrollment in

    courses & books adopted for each course.

    STUDENT (regno: string, name: string, major: string, bdate:date)
    COURSE (course #:int, cname:string, dept:string)
    ENROLL ( regno:string, course#:int, sem:int, marks:int)
    BOOK _ ADOPTION (course# :int, sem:int, book-ISBN:int)
    TEXT (book-ISBN:int, book-title:string, publisher:string, author:string)
    i. Create the above tables by properly specifying the primary keys and the
    foreign keys.
    ii. Enter at least five records for each relation.
    iii. Demonstrate how you add a new text book to the database and make this
    book be adopted by some department.
    iv. Produce a list of text books (include Course #, Book-ISBN, Book-title) in the
    alphabetical order for courses offered by the ‘CS’ department that use more
    than two books.
    v. List any department that has all its adopted books published by a specific
    publisher.
    vi. Generate suitable reports.

    vii. Create suitable front end for querying and displaying the results.

    4. The following tables are maintained by a book dealer.
    AUTHOR (author-id:int, name:string, city:string, country:string)
    PUBLISHER (publisher-id:int, name:string, city:string, country:string)
    CATALOG (book-id:int, title:string, author-id:int, publisher-id:int, category-id:int,
    year:int, price:int)
    CATEGORY (category-id:int, description:string)
    ORDER-DETAILS (order-no:int, book-id:int, quantity:int)
    i. Create the above tables by properly specifying the primary keys and the
    foreign keys.
    ii. Enter at least five records for each relation.
    iii. Give the details of the authors who have 2 or more books in the catalog and
    the price of the books is greater than the average price of the books in the
    catalog and the year of publication is after 2000.
    iv. Find the author of the book which has maximum sales.
    v. Demonstrate how you increase the price of books published by a specific
    publisher by 10%.
    vi. Generate suitable reports.

    vii. Create suitable front end for querying and displaying the results.

    5. Consider the following database for a banking enterprise
    BRANCH(branch-name:string, branch-city:string, assets:real)
    ACCOUNT(accno:int, branch-name:string, balance:real)
    DEPOSITOR(customer-name:string, accno:int)
    CUSTOMER(customer-name:string, customer-street:string, customer-city:string)
    LOAN(loan-number:int, branch-name:string, amount:real)
    BORROWER(customer-name:string, loan-number:int)
    i. Create the above tables by properly specifying the primary keys and the
    foreign keys
    ii. Enter at least five records for each relation
    iii. Find all the “Customers” who have at least two accounts at the Main branch.
    iv. Find all the “Customers” who have an account at all the branches located in
    a specific city.
    v. Demonstrate how you delete all account records at every branch located
    in a specific city.
    vi. Generate suitable reports.
    vii. Create suitable front end for querying and displaying the results.
    6. XYZ high school’s database has the following information:
    i. Professors have an SSN, a name, an age, a rank, and a research specialty.
    Projects have a project number, a sponsor name (e.g., USAID), a starting
    date, an ending date, and a budget.
    ii. Graduate students have an SSN, a name, an age, and a degree program
    (e.g., Bachelor’s or Masters..).
    iii. Each project is managed by one professor (known as the project’s principal
    investigator).
    iv. Each project is worked on by one or more professors (known as the project’s
    co-investigators).
    v. Professors can manage and/or work on multiple projects.
    vi. Each project is worked on by one or more graduate students (known as the
    project’s research assistants).
    vii. When graduate students work on a project, a professor must supervise their
    work on the project.
    viii. Graduate students can work on multiple projects, in which case they
    will have a (potentially different) supervisor for each one.
    ix. Departments have a department number, a department name, and a main
    office.
    x. Departments have a professor (known as the chairman) who runs the
    department.
    xi. Professors work in one or more departments, and for each department that
    they work in, a time percentage is associated with their job.
    Graduate students have one major department in which they are working on their
    degree. Each graduate student has another, more senior graduate student (known
    as a student advisor) who advises him or her on what courses to take.
    i. Design and draw an ERD that captures the information about the XYZ High
    school. Use only the basic ER model here; that is, entities, relationships, and
    attributes. Be sure to indicate any key and participation constraints.
    ii. Use SQL statement to computerize the above ERD.
    iii. Use your favorite programming language and design front end to interact

    with your back end (database).

    Part III: Database security
    1. Explain the following terms as to database security:
    i. Threat
    ii. Availability
    iii. Confidentiality
    iv. Privacy
    v. Integrity
    2. Discuss the role of backup in database field.
    3. In your school, find and explain the possible security issues that can harm
    your database.
    4. Database security is a big concern. Discuss the measures you can adopt to

    keep your database secure.

  • UNIT 5: ARRAYS, FUNCTIONS AND PROCEDURES IN VISUAL BASIC

    Key Unit Competency: Use array, functions and procedures in Visual Basic program.

    Learning objectives:

    •Identify the importance of using array in the program

    •Identify the role of using each category of the function in the program

    •Give the syntax and step to write a function

    •Differentiate Inbuilt function from user-defined function and their usage

    •Design and write a Visual Basic program using an array

    •Design and write a Visual Basic Program using a user defined and in-built functions

    Introductory activity

    In one family, a mother has a culture of storing daily expenses for each day of the year; every day she records daily expenses which means 365 times per year.

    Answer the following questions:

    1. What do you think about 365 times of records per year in terms of programming data storage?

    2. What could be the best way of doing it much better?

    3. Design a VB interface which can allow her to input date/day, Expense designation, Amount, Submit, Update, Delete and Search buttons.

    4. Write a VB program using a functions and procedures which receives marks 35 students in S5 Computer Science and Mathematics where by marks in 10 subjects will be entered, the program should calculate and display the average and percentage got by each student and then generates all students whom their performance is above class average.

    Learning activity 5.1.

    Observe and analyze the two tables below and then answer on the following questions:

    Q1. Give the difference between the way data are stored in Table A and Table B

    Q2. Declare variables and keep the data for both Tables

    Q3.Discuss what would happen if you have to records 500 Students Names? Is it a challenge? If yes discuss what could be the best solution?

    An array is a list of variables with the same data type and name. When we work with a single item, we only need to use one variable. However, if we have a list of items which are of similar type to deal with, we need to declare an array of variables instead of using a variable for each item.

    By using an array, you can refer a list of values by the same name, and use a number that’s called an index or subscript to identify an individual element based on its position in the array. The indexes of an array range from zero to a number one less than the total number of elements in the array. When you use Visual Basic syntax to define the size of an array, you specify its highest index, not the total number of elements in the array.

    a. Dimension of an Array

    An array can be one dimensional or multidimensional. One dimensional array is like a list of items or a table that consists of one row of items or one column of items.

    Example: If we need to record one hundred names, it is difficulty to declare 100 different names; this is a waste of time and efforts. So, instead of declaring one hundred different variables, we need to declare only one array. We differentiate each item in the array by using subscript, the index value of each item, for example name(1), name(2),name(3) .......etc. , makes declaring variables more streamline.

    A two dimensional array is a table of items that made of rows and columns. The format for a one dimensional array is ArrayName(x), the format for a two dimensional array is ArrayName(x,y) and a three dimensional array is ArrayName(x,y,z) .

    Example: A two dimensional array can be considered as a table, which will have x number of rows and y number of columns. Normally it is sufficient to use one dimensional and two dimensional arrays, you only need to use higher dimensional arrays if you need to deal with more complex problems.

    b.Declaring Arrays

    We can use Public or Dim statement to declare an array just as the way we declare a single variable. The Public statement declares an array that can be used throughout an application while the Dim statement declares an array that could be used only in a local procedure.

    b.1. The general format to declare a one dimensional array

    Dim arrayName(subs) as dataType where subs indicates the last subscript in the array.

    Example

    Dim StudName(5) as String

    The above statement will declare an array that consists of 5 elements starting from

    StudName (1) to StudName (5).

    Application activity 5.1

    1. Differentiate a one dimensional array from a two dimensional array

    2. Declare both one dimensional and two dimensional arrays which can keep 100

    5.1.2. Arrays initialization and accessing elements of an array

    Learning activity 5.2.

    1. Write a VB program that initializes your First name, Last name, age, combination and level of studies

    2. Having an array Num already initialized with 10 numbers calculate the sum of the first and last element.

    3. Discuss the way you think elements of an array can be accessed?

    When creating an array, each item of the series is referred to as a member of the array. Once the array variable has been declared, each one of its members is initialized with a 0 value. Most, if not all of the time, you will need to change the value of each member to a value of your choice. This is referred to as initializing the array. To initialize an array, you can access each one of its members and assign it a desired but appropriate value.

    In mathematics, if you create a series of values as X1, X2, X3, X4, and X5, each member of this series can be identified by its subscript number. In this case the subscripts are 1, 2, 3, 4, and 5. This subscript number is also called an index. In the case of an array also, each member can be referred to by an incremental number called an index. A VB array is zero-based which means that the first member of the series has an index of 0, the second has an index of 1. In math, the series would be represented as X0, X1, X2, X3, and X4. In VB, the index of a member of an array is written in its own parentheses. This is the notation you would use to locate each member. One of the actions you can take would consist of assigning it a value or array initialization. Below are some examples:

    a. General syntax of array initializationa.

    1. One dimensional array initialization


    b. Accessing elements of an array

    Once an array has been initialized, that is, once it holds the necessary values, you can access and use these values. The main technique used to use an array consists of accessing each member or the necessary member based on its index. Remember that an array is zero-based.

    You can access the first member using an index of 0. The second member has an index of 1, and so on. Here are some examples: print function or list box can be used to access elements of an array.

    Note: The two ways for accessing elements of an array are not excessive.

    Syntax:

    1. [Print][variableName] or

    2. [ListName.AddItem] [VariableName]

    You can give an example like this: Suppose an array declared as Dim Marks(4) As Double

    a. With one dimensional array

    Program example 1

    Program example 2

    b.With two dimensional array

    Program example 1

    Program example 2

    5.1.3. Entering and displaying arrays elements

    Learning activity 5.3.

    1. Discuss different types of controls and functions that can be used to input and to display elements of an array

    The program accepts data entry through an input box and displays the entries in the form itself. As you can see, this program will only allow a user to enter student’s marks each time a user clicks on the start button.

    a. Entering arrays elements

    Examples

    b.Displaying arrays elements

    An array student Marks has been already initialized, the next step will be to display its elements using a loop.

    .Application activities 5.3.

    1. Write a VB program which allow a user to Input 10 elements of an array and then displays even and odd numbers separately.

    5.2. FUNCTIONS IN VB

    Functions are “self-contained” modules of code that accomplish a specific task. Functions usually “take in” data, process it, and “return” a result. Once a function is written, it can be used over and over and over again. Functions can be “called” from the inside of other functions.

    You can define a function in a module, class, or structure. It is public by default, which means you can call it from anywhere in your application that has access to the module, class, or structure in which you defined it.

    A function can take arguments, such as constants, variables, or expressions, which are passed to it by the calling code.

    There are two basic types of functions. Built-in functions and user defined ones. The built-in functions are part of the Visual Basic language. There are various mathematical, string or conversion functions.

    5.2. 1 Built-in Functions

    Learning activity 5.4.

    1. Discuss the importance of using functions use in programming context

    The built-in functions are functions which are automatically declared by the compiler, and associated with a built-in function identifier.

    a. MsgBox ( ) Function

    The objective of MsgBox is to produce a pop-up message box that prompt the user to click on a command button before he /she can continues. This format is as follows:

    yourMsg = MsgBox(Prompt, Style Value, Title)

    The first argument, Prompt, will display the message in the message box. The Style Value will determine what type of command buttons appear on the message box, please refer Table1 for types of command button displayed. The Title argument will display the title of the message board.

    We can use named constant in place of integers for the second argument to make

    the programs more readable. In fact, VB6 will automatically shows up a list of names constant where you can select one of them.

    Example 1: yourMsg=MsgBox( “Click OK to Proceed”, 1, “Startup Menu”) and yourMsg=Msg(“Click OK to Proceed”. vbOkCancel,”Startup Menu”) are the same. Your Msg is a variable that holds values that are returned by the MsgBox ( ) function. The values are determined by the type of buttons being clicked by the users. It has to be declared as Integer data type in the procedure or in the general declaration section. Table 2 shows the values, the corresponding named constant and buttons.

    Table 2: Return Values and Command Buttons


    The codes for the test button see Figure 4

    Private Sub Test_Click()

    Dim testmsg As Integer


    When the user clicks on the test button, the image like the one shown in Figure 4 will appear. As the user clicks on the OK button, the message “Testing successful” will be displayed and when he/she clicks on the Cancel button, the message “Testing fail” will be displayed.

    a.3. Icon besides the message

    To make the message box looks more sophisticated, you can add an icon besides the message. There are four types of icons available in VB as shown in Table 5.

    Example 3

    You draw the same Interface as in example 2 but modify the codes as follows:

    b.The InputBox( ) Function

    An InputBox( ) function displays a message box where the user can enter a value or a message in the form of text. The format is myMessage=InputBox(Prompt, Title, default_text, x-position, y-position)

    myMessage is a variant data type but typically it is declared as string, which accept the message input by the users. The arguments are explained as follows:

    •Prompt: The message displayed normally as a question asked.

    •Title: The title of the Input Box.

    •Default-text: The default text that appears in the input field where users can use it as his intended input or he may change to the message he wish to key in.

    •X-position and y-position: the position or the coordinate of the input box. Below is an example of an input box

    The procedure for the OK button

    When a user click the OK button, the input box as shown in Figure 7 will appear. After user entering the message and click OK, the message will be displayed on the caption, if he/she clicks Cancel, “No message” will be displayed.

    c. String function

    In this lesson, we will learn how to use some of the string manipulation function such as Len, Right, Left, Mid, Trim, Ltrim, Rtrim, Ucase, Lcase, Instr, Val, Str ,Chr and Asc.

    c. 1 The Len Function

    The length function returns an integer value which is the length of a phrase or a sentence, including the empty spaces.

    The above example will produce the output 1, 4, 8. The reason why the last value is 8 is because z# is a double precision number and so it is allocated more memory spaces.

    c. 2 The Right Function

    The Right function extracts the right portion of a phrase. The format is

    Right (“Phrase”, n)

    Where n is the starting position from the right of the phrase where the portion of the phrase is going to be extracted.

    Example

    Right(“Visual Basic”, 4) = asic

    c. 3 The Left Function

    The Left$ function extract the left portion of a phrase. The format is

    Left(“Phrase”, n)

    Where n is the starting position from the left of the phase where the portion of the phrase is going to be extracted.

    Example

    Left (“Visual Basic”, 4) = Visu

    c. 4 The Ltrim Function

    The Ltrim function trims the empty spaces of the left portion of the phrase. The format is

    Ltrim(“Phrase”)

    Example

    Ltrim (“Visual Basic”, 4)= Visual basic

    c. 5 The Rtrim Function

    The Rtrim function trims the empty spaces of the right portion of the phrase. The format is

    Rtrim(“Phrase”)

    Example:

    Rtrim (“Visual Basic”, 4) = Visual basic

    c. 6 The Trim function

    The Trim function trims the empty spaces on both side of the phrase. The format is

    Trim(“Phrase”)

    Example

    Trim (“ Visual Basic ”) = Visual basic

    c. 7 The Mid Function

    The Mid function extracts a substring from the original phrase or string. It takes the following format:

    Mid(phrase, position, n)

    Where position is the starting position of the phrase from which the extraction process will start and n is the number of characters to be extracted.

    Example

    Mid(“Visual Basic”, 3, 6) = ual Bas

    c. 8 The InStr function

    The InStr function looks for a phrase that is embedded within the original phrase and returns the starting position of the embedded phrase. The format is

    Instr (n, original phase, embedded phrase)

    Where n is the position where the Instr function will begin to look for the embedded phrase.

    Example

    Instr(1, “Visual Basic”,” Basic”)=8

    c. 9 The Ucase and the Lcase functions

    The Ucase function converts all the characters of a string to capital letters. On the other hand, the Lcase function converts all the characters of a string to small letters.

    Example

    Ucase(“Visual Basic”) =VISUAL BASICLcase(“Visual Basic”) =visual basic

    c. 10 The Str and Val functions

    The Str is the function that converts a number to a string while the Val function converts a string to a number. The two functions are important when we need to perform mathematical operations.

    c. 11 The Chr and the Asc functions

    The Chr function returns the string that corresponds to an ASCII code while the Ascfunction converts an ASCII character or symbol to the corresponding ASCII code. ASCII stands for “American Standard Code for Information Interchange”. Altogether there are 255 ASCII codes and as many ASCII characters. Some of the characters may not be displayed as they may represent some actions such as the pressing of a key or produce a beep sound.

    The format of the Chr function is:

    Chr(charcode)

    and the format of the Asc function is:

    Asc(Character)

    The following are some examples:

    Chr(65)=A, Chr(122)=z, Chr(37)=% , Asc(“B”)=66, Asc(“&”)=38

    d.The mathematical functions

    The mathematical functions are very useful and important in programming because very often we need to deal with mathematical concepts in programming such as chance and probability, variables, mathematical logics, calculations, coordinates, time intervals and etc. The common mathematical functions in Visual Basic are Rnd, Sqr, Int, Abs, Exp, Log, Sin, Cos, Tan , Atn, Fix and Round.

    a. Int is the function that converts a number into an integer by truncating its decimal part and the resulting integer is the largest integer that is smaller than the number. For example, Int(2.4)=2, Int(4.8)=4, Int(-4.6)= -5, Int(0.032)=0 and so on.

    b. Sqr is the function that computes the square root of a number. For example, Sqr(4)=2, Sqr(9)=2 and etc.

    c. Abs is the function that returns the absolute value of a number. So Abs(-8) = 8 and Abs(8)= 8.

    d. Exp of a number x is the value of ex.

    For example, Exp(1)=e1 = 2.7182818284590

    e. Fix and Int are the same if the number is a positive number as both truncate the decimal part of the number and return an integer. However, when the number is negative, it will return the smallest integer that is larger than the number.

    For example, Fix(-6.34)= -6 while Int(-6.34)=-7.

    f. Round is the function that rounds up a number to a certain number of decimal places. The Format is Round (n, m) which means to round a number n to m decimal places.

    Example

    Round (7.2567, 2) =7.26

    g. Log is the function that returns the natural Logarithm of a number.

    Example

    Log (10)= 2.302585

    h. Sin is the function that returns the natural Sinus of a number.

    Example

    Sin(10)= -0,544021

    i. Tan is the function that returns the natural Tangent of a number.

    For example

    Tan(10)= 0,6403608

    j. Cos is the function that returns the natural Cosinus of a number.

    Example

    Cos(10)= -0,839071

    e. Graphical methods (Line, Circle and Rectangle)

    e. 1 Drawing lines

    The Line method lets you draw lines in Visual Basic 6. You need to specify the starting point and the finishing point of the line in the argument. You may also specify the color of the line. This is optional, though.

    i. A simple line

    The following code example shows how to draw a simple line using the Line method.

                                      

    ii. A line with drawing styles Form’s Draw

    Style property lets you draw lines using a particular style. The constant values of the DrawStyle property are 0 (vbSolid), 1 (vbDash), 2 (vbDot), 3 (vbDashDot, 4 (vbDashDotDot), 5 (vbTransparent) and 6 (vbInsideSolid). The default value is 0, vbSolid. You may use the numeric constant or the symbolic constant such as vbSolid, vbDash etc to change drawing styles in your code.

    NOTE: The Draw Style property does not work if the value of Draw Width is other than 1.

    Example