Database – Normalisation

All the examples will use this simple table for explanation.
Screenshot 2018-10-18 at 10.59.59 PM
Attribute is column, and tuple is row. In this table the attributes are from A to F.

Functional dependency
Screenshot 2018-10-18 at 11.03.44 PM
A is determinant, B is dependant.
B is functional dependent on A.

Screenshot 2018-10-18 at 11.05.35 PM
A and B are composite key which functionally determines C.

Partially functional dependency
Screenshot 2018-10-18 at 11.09.59 PM
A and B are candidate keys, but C is dependent only to A. This is partially functional dependent.

Transitive functional dependency
Screenshot 2018-10-18 at 11.21.02 PM

F is dependent on D, but D is not part of candidate key.

Screenshot 2018-10-18 at 11.21.53 PM
E and F dependent on D, but D is not part of candidate key.

Why normalisation?
Normalisation is to remove duplicates. Duplicates or redundant values in a database will create problems. These problems are:
Update anomaly, deletion anomaly, and insert anomaly.

1NF
To qualify for 1NF these are the requirements:
a. Attributes must be unique.
b. Values in tuple must be atomic i.e. no multiple values, only one value.
c. Values in tuple must be the same domain. Example if the attribute is date, the value must be date related.

2NF
2NF must have the qualification of 1NF and in addition must not have attributes that are partially functional dependent.
Example of 2NF


A and B determine C, A determine B, no partially dependent.

3NF
3NF must be qualified for 2NF, and must not have attribute(s) is/are dependent on another non-key attribute(s) i.e. no transitive dependency

Advertisements
Posted in Database | Tagged , | Leave a comment

[python]Email script

#!/usr/bin/env python
import smtplib
import sys
from email.mime.text import MIMEText

lineitems = sys.stdin.readlines()
SERVER = "obfuscated.email.server"
FROM = "tufin-noreply@obfuscated.com"
TO = "admin@ofuscated.com"
CC = "wonghonbing@obfuscated.com"

SUBJECT = "Test - Tufin securechange auto step stuck"
TEXT = "Dear admin,\r\
This message is generated for your attention that a step in tufin secure change \ is stucked.\r \
Manual intervention by you is required.\r\
On next version this message will add the tufin ticket number.\r\
This is a test message please IGNORE.\r\
%s " % (lineitem for line in lineitems)

message = MIMEText(TEXT)
message["From"] = FROM
message["To"] = TO
message["Subject"] = SUBJECT
message["Cc"] = CC
server = smtplib.SMTP(SERVER)
server.sendmail(FROM, TO, message.as_string())
server.quit()

The above code snippet is an email script that is triggered when Tufin fails an automated step. The challenge now is to include a ticket number to the email.

Posted in General stuffs | Leave a comment

Database: count number of rows in a table

QL> select count(*) from cyrus.customer;

  COUNT(*)
----------
	11

SQL> 

This is to count the number of rows in a table. Cyrus is the owner and customer is the table.

Posted in Database | Tagged | Leave a comment

Database: Check all tables from a known owner

This is an oracle 12c query to find out all tables that are owned by “cyrus”.

select owner,table_name from all_tables where owner='CYRUS';

the contents in the row is case sensitive, in this example the content in row “owner” is CYRUS not Cyrus or cyrus.

another way if you do not know the case of the content use this:

select owner,table_name from all_tables where upper(owner)=upper('cyrus');

another way is to convert the owner name to lower case

select owner,table_name from all_tables where lower(owner)='cyrus';

Posted in Database | Tagged , | Leave a comment

ICT research method – what is ICT research

What is research
Research is a creation of new knowledge.

Research outcomes

  • A new and improved product
  • A deeper understanding of a research topic
  • A new or improved model and theory to compliment existing research
  • A new or improved technique to solve certain research problems.

Aims of research

  • Observe and describe a research problem
  • Predict
  • Identify the cause of a problem
  • To explain a research problem

What is ICT research
It is the creation of new knowledge in any ICT areas.
examples:

  • New and more efficient routing protocol
  • How to better secure gratuitous ARP used in virtual router routing protocol

What is research project
Project is a sequence of activities/tasks to create new knowledge within specific timeline, within budget, according to specification and with resources.

Phases of research
The following in sequence:

  • Literature review
  • Define research question/problem
  • Research design
  • Collect data
  • Data analysis
  • Report finding

Research approaches

  • Survey – research data collected from population of interest
  • Design and creation – research focus on developing new ICT product
  • Experiment – research to find causal relationship between selected variables
  • Action research = research focus to address practical issues
  • Case study research – research focus on one or more instances of things of interest (case)
Posted in ICT research method | Leave a comment

[tufin]adding generic routes

Tufin securetrack is incapable of detecting routes that use policy based routing, a possible solution to include routes of policy based routing is to add generic routes. This can be found in this https://forum.tufin.com/support/kc/latest/index.htm?toc.htm?10626.htm

you need to create a csv file in tufin ST, then use the <code>/usr/local/st/topology_generic_routes -m 5 -i your_file_in.csv</code>

update the topology with the generic network, this is quicker than pressing the synchronize button in the network gui in Tufin ST.

<code>/usr/local/st/topology_graph_builder</code>

To check if generic network is added to the firewall, eg if firewall id is 5.
<code>/usr/local/st/topology_generic_routes -m 5 -p</code>

Has to be version 17.3 onwards, anything earlier than this does not have this script.

Posted in General stuffs | Tagged , | Leave a comment

[python]Comparing missing ip addresses between original inventory with object group

An inventory list is kept to record hostnames and ip addresses, this inventory list (inventory.xlsx) is currently maintained by automation.

For every virtual machine created, the hostname and its corresponding ip address are recorded in the list, automation will update the ip address into the existing object group in the firewall.

However there is a report that there are certain ip addresses not included in the interesting_group, you need to write a script to leverage Tufin SecureTrack to find out what are the missing ip addresses in the existing group as compared with the inventory list.

In the inventory list there are ip addresses which belong to subnet 192.168.1.0/24, this whole subnet is not the network protected by firewall hence skip during collection.

The script needs to be cautious to skip recording items from interesting_group.

This is a code written in haste, hence not structured, there are repeated items which worth to create functions.

import xml.etree.ElementTree as ET
import requests
from openpyxl import load_workbook
from netaddr import IPNetwork, IPAddress


NSMAP = {'xsi': 'http://www.w3.org/2001/XMLSchema-instance'}
tufin_st_xml = requests.session()
tufin_st_xml.verify = False
tufin_st_xml.headers.update({'Content-type': 'application/xml'})
tufin_st_xml.auth = requests.auth.HTTPBasicAuth('admin', 'password')

ip_collections = []
group_collections = []
base_list = []
row = 2
column = 7
ip_from_inventory = []
filtered_ip = []
filtered_ip_collections = []
missing_ip = []
try:
    response = tufin_st_xml.get('https://secure_track_address/securetrack/api/devices/1/network_objects?type=group&show_members=true&name=interesting_group')
    xml_response = ET.fromstring(response.text)
    for xml_item in xml_response.findall('.//ip'):
        ip_collections.append(xml_item.text)
    for xml_type in xml_response.findall("./network_object[@xsi:type='networkObjectGroupDTO']", NSMAP):
        if 'interesting_group' not in xml_type.find('display_name').text:
            group_collections.append(xml_type.find('display_name').text)
except Exception as e:
    print(e)
    exit(1)

if group_collections:
    for group in group_collections:
        print(group)
        try:
            response = tufin_st_xml.get('https://secure_track_address/securetrack/api/devices/1/network_objects?type=group&show_members=true&name={}'.format(group))
            xml_response = ET.fromstring(response.text)
            for xml_item in xml_response.findall('.//ip'):
                ip_collections.append(xml_item.text)
        except Exception as e:
            print(e)
            exit(1)

for ip_item in ip_collections:
    if IPAddress(ip_item) not in IPNetwork('192.168.1.0/24').iter_hosts():
        filtered_ip_collections.append(ip_item)
wb = load_workbook('inventory.xlsx')
while wb['Sheet1'].cell(row, column).value is not None:
    ip_from_inventory.append(wb['Sheet1'].cell(row, column).value)
    row += 1

for ip_item in ip_from_inventory:
    if IPAddress(ip_item) not in IPNetwork('192.168.1.0/24').iter_hosts():
        filtered_ip.append(ip_item)

missing_ip = set(filtered_ip).difference(filtered_ip_collections)
print(missing_ip)
Posted in Python, Scripting | Tagged , , , , , | Leave a comment