SS2 DATA PROCESSING NOTE FIRST TERM (2019-2020 SESSION)

SCHEME OF WORK

WEEK	TOPIC
1	Writing of scheme of work
2	COMPUTER ETHICS ·         Definition of Computer ethics ·         Definition of computer room management ethics ·         Computer ethics issues ·         Responsible ways of using the Computer ·         Ways of misusing the computer
3	SAFETY MEASURES ·         Meaning of safety measures ·         Safety measures to be taken when using computer
4-5	DATA MODEL ·         Definition of Data model ·         Types of data model
6-7	DATA MODELLING ·         Definition of Data modelling ·         Approaches to data model ·         Data modelling using MS Access
8-9	NORMAL FORM ·         Definition of Normalization ·         Goals of normalization ·         Types of normal forms ·         Problems with first normal form

TOPIC ONE: COMPUTER ETHICS

Computer ethics is a term used to describe the principles of right and wrong that are acceptable when using computer system and related technologies.

Computer room management ethics can be defined as the appropriate way of handling and taking good care of computer and associated resources within the computer room.

Computer ethic issues

1.       Plagiarism – presenting someone’s work (hard copy, softcopy) as your own.

2.      Copyright violation: violation of the legal rights to an original work (e.g. software, ebook etc.) is a common ethical dilemma in our society.

3.  Privacy infringement: having unauthorized access to data of an individual is a privacy infringement which is liable to jail term.

4.    Netiquette:  Netiquette is the short for “internet etiquette”. It is the set of social rules about the proper and polite way to communicate when using the internet. Some of the common guidelines here are:

          a.       Email – when using email, if you don’t know the source of the email, make sure you:   

• Don’t reply to the email              
• Don’t click the (malicious) attachment
• Don’t click the dangerous links in the email that could download malware to your system.

        b.      Don’t click on any link or online ad (advertisement) that you are not sure of the source

        c.       Do not reveal your sensitive information online

        d.      Don’t just download any free software if you are not sure of the security of the website

        e.      Keep your username and password to yourself; don’t be deceived to sharing this vital                           information (not even to your friend)

        f.        Think twice before posting that message because whatever you post online stays online and                 can affect your personal or professional life in the future

         g.       Install antivirus protection before going online, it’s important for your online safety.

Responsible ways of using the computer

• Installing antivirus on the computer and regularly update it
• Checking and respond to emails promptly and politely
• Avoid using the computer to send anonymous emails/messages
• Take regular backups of files/data

Ways of misusing the computer

1. Hacking – using computer knowledge to have unauthorized access to files and information
2. Pornography- using computer or internet to watch or distribute pictures and videos that are sexually explicit.
3. Fraud – using the internet to fraudulently swindle unsuspecting victims of money or valuables e.g. yahoo yahoo
4. Spreading malicious software into networked computer
5. Using the computer to send discriminatory, harassing and threatening messages

Assignment: List 10 Computer room rules and regulations.

TOPIC TWO: SAFETY MEASURES

Safety measures are the precautions or actions taken to ensure protection from or not being exposed to, the risk of illness, or injury from the use of computer and related equipment within a computer environment.

The following are some of the safety measures/guidelines:

1. There should be appropriate ventilation in the computer room
2. Use surge protector to protect your computer against power surge and spikes
3. Unauthorized access to computer should be restricted by putting passwords on computer or by encrypting the files
4. Using anti-glare screen protector to cover the monitor in order to reduce strain to the eyes
5. Ensure good working posture when using the computer
6. Ensure you install and regularly update antivirus software to guard against malware
7. Make sure you regularly backup your files on external storage media
8. Avoid repairing computer components or devices esp. CRT monitor to avoid electrocution.

Computer related health problems and solutions

1.       Eye strain and vision problem – continuous staring at the monitor without taking breaks can cause eyestrain which weakens the sight.

Precautions/solution

• Use anti-glare glasses to cover the monitor
• Take regular short breaks from sitting in front of the computer

2. Low back pain – this is as a result of wrong sitting posture adopted when you use the computer. The risk increases when this happens regularly over a long period.

Precaution/solution

1.    Use a proper chair that support your back and lower back
2.  Sit up straight. Do not slouch
3.   Regular exercises at least five times a week to strengthen your back muscle
4.   Using a lumbosacral belt as a precautionary  and preventive measure will help prevent this problem

3. Carpal tunnel syndrome – regular typing and use of mouse over prolonged periods is a major risk factor of the risk called Carpal tunnel syndrome. Tingling and numbness, pain and loss of strength of the affect wrist, fingers and hands are symptoms.

Precautions/solution

1. Exercises for the wrist will help and take regular breaks
2. Do not hold the mouse tightly.

4. Cervical spondylosis (neck and shoulder problem)  - reading from the monitor screen or typing over a prolonged periods regularly is a risk factor that can over time cause cervical spondylosis

Precaution/solution

1. Wear a cervical collar during your computing hours, this will keep the neck and shoulder muscles strong
2. Neck and shoulder exercise two to three times a day will keep the neck and shoulder muscles strong

TOPIC THREE: DATA MODEL

A model is like a blueprint of a more complex real-world object or event.

A data model is a picture or description which shows how data is to be arranged to serve a specific purpose.

A data model defines how data is connected to each other and how they are processed and stored inside the system.

A data model documents and organizes data, how it is stored and accessed, and the relationships among different parts of data.

TYPES OF DATA MODEL

1. Flat file model: A flat data is a type of database that stores data in a single table. Flat file databases are generally in plain-text form, where each line holds only one record. The fields in the record are separated using delimiters such as tabs and commas

 2. Hierarchical model: In this model, data is organised into a tree like structure. The data is stored as records which are connected to one another through links. In a hierarchical model, each child record has only one parent, whereas each parent record can have one or more children. At the top of hierarchy there is only one entity which is called Root. In order to retrieve data from a hierarchical database the whole tree needs to be traversed starting from the root node.

    

3. Network model: In the network model, data records are organised in a graph, in which some records can be accessed through several paths. This model organizes data using two fundamental constructs, called records and sets. Records contain fields, and sets define one-to-many relationships between records: one owner, many members.

        

4. Relational model: In this model, data record is organised in two-dimensional tables called relations. The tables or relations are related to each other. Each table is made up of rows and columns and a table stores records about a particular subject. Relational data models are used in IBM’s DB2, Informix, Oracle, Sybase, Paradox, FoxBase, Teradata).

   

5. Entity-relationship model: this model is based on the notion of real world entities and relationships among them. ER model is based on:

• Entities and their attributes
• Relationships among entities

In ER modeling, the structure for a database is portrayed as a diagram called an entity-relationship diagram (or ER diagram). An example is shown below

TOPIC FOUR: DATA MODELLING

Data modelling is the process of creating a data model for an information system by applying certain formal techniques

APPROACHES TO DATA MODEL

The three approaches to data model are:

1. Conceptual data model
2. Logical data model
3. Physical data model

Conceptual data model: This is the first stage of database design. A conceptual data model identifies the highest-level relationships between different entities. In a conceptual data model, the following steps are taken:

1. Specify the important entities and the relationships among them
2. No attribute is specified
3. No primary key is specified

Logical data model: This is the second stage in database design. A logical data model describes the data in as much details as possible, without regard to how they will be physically implemented in the database. In a logical data model, the following steps are taken:

1. Specify primary keys for all entities
2. Find the relationships among different entities
3. Find all attributes for each entity
4. Resolve many-to-many relationships
5. Normalization

Physical data model: Physical data model is the transformation of the logical database into the actual database. Physical data model represents how the model will be built in the database. A physical database model shows all table structures, including column name, column data type, column constraints, primary key, foreign key and relationships between tables. The steps for physical data model design are as follows:

1. Convert entities into tables
2. Convert relationships into foreign keys
3. Convert attribute into columns
4. Modify the physical data model based on physical constraints/requirements.

CREATING LIBRARY DATABASE

The following table information will be used to create a Library database:

• Author (AuID, AuName, AuPhone, Address,)
• Book (ISBN, title, PubName, Category, AuID, Price)
• Publisher (PubID, PubName, PubPhone, AuID, ISBN)

To create the “library” database, follow the following steps:

• Click on the Start button on the Taskbar
• Point to All Programs
• Select Microsoft Office
• Click Microsoft Access 2010
• Click on Microsoft Office button/File tab
• Click New
• Click the New Blank Database icon
• Type “Library” as the database name in the File Name box
• Click Create. Microsoft Access window will be displayed.

Table

A table is the first and the most important object to be added to a database. It stores data about a particular subject such as Authors or Books. It consists of records and fields. Each record contains data about one instance of the table subject, such as a particular author. Each field contains data about one aspect of the table subject, such as AuthorName, AuthorPhoneNumber, etc. A field is also commonly called a column or an attribute. A record consists of field values.

Field property

Field property applies to a particular field in a table and defines one of the field’s characteristics or an aspect of the field’s behavior.

Data types

A field data type indicates the kind of data that field stores or accepts.  If the field name holds numeric value, the data type will be Number. Some of the data types include text, autonumber, number, memo, date/time, currency, Yes/No, Attachment, etc. Refer to your SS1 3rd term note for the explanation.

Table relationships

Although each table stores data about a different subject, tables in a database store data about subjects that are related to each other. Before storing data about different subjects in separate tables, you need a way to link them together so that you can easily combine related data from those separate tables. To connect the data stored in different tables, you create relationships. A relationship is a logical connection between two tables that specify fields that the tables have in common.

Key : A key is a minimal set of attributes whose values uniquely identify an entity in the set. A key consists of one field, but may consist of more than one field (i.e. Composite key).

Primary key: A primary key consist of one or more fields that uniquely identify each record that you store in the table. Consider the Student table; the AdmissionNumber field is the primary key of the Student table.

Foreign key: A foreign key contains values that correspond to values in the primary field of another table. For example, consider the table information below:

• Author (AuID, AuName, AuPhone, Address,)
• Book (ISBN, title, PubName, Category, AuID, Price)

In the Author’s table information above, AuID field is the primary key but a look at the Book’s table information, AuID field is also part of its attributes. Here AuID field is a foreign key of the Books table. The similarity of values between key fields forms the basis of a table relationship.

Differences between primary and foreign key

Primary key	Foreign key
The field used for primary key cannot be empty (null)	Field can contain a null value
A table cannot have more than one primary key value	A table can have more than one foreign key value
Primary key uniquely identify rows in a table	Foreign key create relationship between two tables
The primary key for each table is stored in an index	Index is not automatically created for a foreign key

Creating Book table

When already on the Microsoft Access window

1. Click the Create tab, then click Table.
2. Click View, then select Design View.
3. In the window that shows, type Books in the Table name box and click ok.
4. On the first row of the Field Name title, type ISBN for the first field name of the Books table to replace the highlighted ID, click inside Data type and select Text for the ISBN field
5. Click the second row, type Title for the second field name and select Text for its data type.
6. Click the third row, type AuID for the third field name, and select Text as the data type.
7. Click the fourth row, type Price for the fourth field name, and select Text as the data type.
8. Save your work, select View and choose Datasheet View to take you to the datasheet view where you can insert your records.

Creating the Authors and Publishers Tables

To add other tables, do the following:

1. Click the Create tab on the menu bar, select Table, another table will be added to the database.
2. Follow step 4 to 8 above to define the table fields and the data type.
3. Follow the process to create the Authors and Publishers tables.

Forms

A form is another database object that provides a convenient way to enter and view records in a table.

Queries

A query is a database object that enables you to locate records that match specified criteria by providing a way for you to ask a question about the information stored in a database table.

Report

A report is a database object that allows you to organize, summarize, and print all or a portion of the data in a database.

TOPIC FIVE: NORMAL FORM

Normalization is the set of guidelines used to optimally design a database to reduce redundant data. Normalization is a database design technique which organizes tables in a manner that reduces redundancy and dependency of data.

A database that is not normalized may include data that is contained in one or more different tables for no apparent reasons. Normalization is achieved by applying a number of tests called normal forms to tables.

Goals of normalization

The goals of normalization are:

1. Eliminating data redundancy i.e. duplication of data
2. To minimize or avoid modification issues
3. To simplify queries

Normal form

Normal form is the way of measuring the level, or depth, to which a database has been normalized. The most common normal forms are:

1. First normal form (1NF)
2. Second normal form (2NF)
3. Third normal form (3NF)

Other normal forms are Boyce /Codd normal form (BCNF), 4^th normal form and 5^th normal form.

Note: Each subsequent normal form depends on the normalization steps taken in the previous normal form. The 3NF is sufficient for most typical database applications.

First normal form (1NF)

A database table is said to be in 1NF if:

• It contains no repeating or duplicate fields/columns (i.e. each column name should be unique)
• No data in a columns is multi-valued  (i.e. each data field is single value)
• Each row of data has a unique identifier (or Primary Key)
• The attribute domain remains the same (i.e. age value cannot be in the name column)

For example, consider the un-normalized table below:

Item	Colour	Price	Tax
T-shirt	Red, Blue	12.00	0.60
Polo	Red, Yellow	12.00	0.60
T-shirt	Red, Blue	12.00	0.60
Sweatshirt	Blue, Black	25.00	1.25
Pant	White	6.00	0.30

         Table1: Un-normalized table

The table above is not in a first normal form because:

• Multiple items in color field  (i.e. red and blue , red and yellow etc)
• Duplicate records/no particular primary key (i.e. row1 and row3)

So, how do you convert the table above into 1NF?

• Delete one of the duplicate records (i.e. row1 or row3)
• Expand the remaining rows such that each column has a single value 

The resulting table now in 1NF is shown below.

Item	Colour	Price	Tax
T-shirt	Red	12.00	0.60
T-shirt	Blue	12.00	0.60
Polo	Red	12.00	0.60
Polo	Yellow	12.00	0.60
Sweatshirt	Blue	25.00	1.25
Sweatshirt	Black	25.00	1.25
Pant	White	6.00	0.30

      Table 2: 1NF table

Problems with tables in first normal form (1NF)

Insert anomalies: An Insert Anomaly occurs when certain attributes cannot be inserted into the database without the presence of other attributes. Suppose a new item has just been bought and is to be added into the table and you do not know the tax applicable, it will be difficult to enter a few item of information and not all, thereby leading to Insertion Anomaly.

Delete anomalies: A Delete Anomaly exists when certain attributes are lost because of the deletion of other attributes. If the White colour of the item Pant is no more in stock, and we try to delete ‘white’ from the colour column, then we will be forced to remove the item Pant, the price and the tax as well since the entire row will be deleted

Update anomalies: An Update Anomaly exists when one or more instances of duplicated data are updated, but not all. For example, if the tax applicable to the price 12.0 changed, then we will have to update all the rows where there is 0.60, else data will become inconsistent i.e. there will be different tax value for the price 12.0 in different rows.

Second normal form (2NF)

A database table is in a 2NF if and only if:

1. It is in 1NF and
2. Every non-key attribute is fully functionally dependent on the primary key i.e. there should be no partial dependency in the table

Consider the Table 2 above, the non-key attributes are colour, price and tax. The attribute item is the primary key. The price and the tax are functionally dependent on the item and not on the colour i.e. the item determines the price and not the colour.

So what do you do to normalize into 2NF?

Decompose the 1NF table and set up a new relation (table) for each partial key with its dependent attributes. Make sure to keep a table with the original primary key and any attributes that are fully functionally dependent on it.

Having decomposed the above table, the tables below are now in 2NF

Third normal form (3NF)

The third normal form's objective is to remove data in a table that is not dependent on the primary key.

A database table is said to be in 3NF if:

• It is in a 2NF
• All non-key field depend only on the primary key – no transitive dependency (i.e. Eliminate all fields that do not depend on the primary key by moving them into a separate table)

Tables 2NF(b) is not in third normal form because Tax depends on price, not item.

To normalize to 3NF, decompose the table and set up a new table that includes the non-key attribute(s) i.e. price, that functionally determine(s) other non-key attribute(s) i.e. tax

The tables below are now in 3NF.