networking & dbms

Network Topology

how devices in a computer network are connected

node

any device or point in the network crucial for transmitting and receiving data

link

connection between two nodes

types of topology

mesh, star, bus, ring, tree

mesh topology

every node has a dedicated link in every other node

Pros and Cons of mesh topology

pros: redundant links result in fault tolerance, easy fault identification and isolation
cons: each node must have an interface for every other node, limited number of I/O ports on each computer

star topology

every node is connected to a central server

Pros and Cons of star topology

pros: easy to install and wire
cons: more expensive cabling

bus topology

single link connects all nodes in the network and data is broadcast simultaneously

Pros and Cons of bus topology

pros: easy to implement and extend
cons: not scalable and expensive

Ring topology

nodes are situated in a circular fashion and data is transmitted in a unidirectional loop

pros and cons of ring topology

pros: fault tolerance builds into design, data packets move at higher speeds
cons: expensive and limited scalability

tree topology

hierarchical tree structure with parent nodes and children nodes

pros and cons of tree topology

pros: scalable, high security, reliable
cons: more expensive due to amount of cables

transmission modes

transferring of data between two devices

types of transmission modes

simplex, half duplex, full duplex mode

simplex mode

communication is a one way street

pros and cons for simplex mode

pros: cost-effective and reliable, used when feedback or response isn’t required
cons: no way to confirm whether transmitted data has been received correctly

half duplex mode

two nodes communicate bidirectionally but only one can receive and transmit at a time, similar to a walkie-talkie

pros and cons of half duplex mode

pros: more efficient mode of communication than simplex mode
cons: delay between transmission and reception, less reliable than full duplex and more expensive than simplex

full duplex mode

half duplex mode but can transmit and receive simultaneously

pros and cons of full duplex

pros: ideal for real time applications and provides highest level of reliability and security due to not needing to check for correctedness in data transmitted
cons: expensive, complex, and not needed for certain applications

ip address

numerical label assigned to a node that uses the Internet Protocol for communication; two types ipv4 and ipv6

difference between ipv4 and ipv6

ipv4 uses two 32 bit integers expressed in decimal notation for the network address and host address
ipv6 uses 128 bit addresses; other improvements include improved security (data authentication, encryption), simplified header format, better mobile device support, stronger and more reliable support for QoS features which aid in higher traffic and audio/video quality on pages

subnetting

a bigger network split into smaller ones to maintain security, scalability, reduce traffic for some structures, and maintain order and efficiency

disadvantages of subnetting

wasting ip addresses (two per subnet), costly due to internal network devices required to implement

OSI Model

Open system interconnection: 7 layer architecture that collaboratively works to transmit data globally

TCP/IP Model

Transmission Control Protocol/Internet Protocol which is a 4 layer architecture that defines how data is transmitted over networks; achieves it by dividing the data into packets at the sender’s end, then recombining them at the receiver’s end

Network Access Layer

generating data and requesting connections; error prevention and framing

Internet/Network Layer

responsible for routing packets of data from one device to another across a network

IP Protocol

Transport Layer

responsible for reliable data transfer between applications on different devices on network; segmentation, error detection and correction, and flow control

TCP and UDP are transport layer protocols

Application Layer

responsible for end-to-end communication and error-free delivery of data


HTTP/HTTPS, SSH are the application layer protocols

TCP

provides reliable, connection-oriented data transfer between two devices; guarantees a delivery of data in the right order and handles error correction and retransmission

UDP

offers connectionless, efficient data transfer; prioritizes speed over reliability

types of database keys

candidate, primary, alternate, super, foreign, composite

candidate key

uniquely identifies a row in a table without relying on other attributes; can have multiple in a table

primary key

chosen from the candidate keys as the main identifier for a table; only one primary key per table

alternate key

candidate keys that aren’t primary keys

super. key

set of attributes that uniquely identifies a row in a table

foreign key

links a table to another table, establishing a relationship; references the primary key of the other table

normalization

concept in database design that aims to organize data effectively to minimize redundancy and improve data integrity and maintainability through dividing the database into smaller, well-structured tables based on specific rules and principles (forms)

normal forms

series of guidelines to help achieve normalization, getting progressively stricter and reducing even more redundancy

pros and cons of normalization

pros: reduced data redundancy, improved data integrity, enhanced data retrieval and manipulation, simplified data administration
cons: increased complexity and potentially slower performance

1NF

eliminates repeating groups of values within a table; each cell should contain a single atomic value

2NF

no partial dependencies, every non-key attribute must depend on the entire primary key, not just a part of it

3NF

no transitive dependencies; no non-key attribute depends on another non-key attribute, only on the primary key

4NF

addresses multi-valued dependencies that cannot be resolved with 3NF

5NF

deals with higher order dependencies but rarely used in practice

dirty read problem

transaction reads uncommitted data written by another transaction which leads to inconsistencies and inaccurate results
scenario: transaction A starts and updates a data item (e.g. changes a customer’s balance) → transaction A doesn’t commit the change yet → transaction B reads the updated data item from transaction A, even though it’s not finalized → transaction A encounters an error and is rolled back, undoing its changes
problem: transaction B has “dirty data”- it read information that wasn’t final and now doesn’t exist which results in inconsistencies and errors
prevention: various concurrency control protocols to prevent dirty reads and ensure data consistency

dirty read prevention tactics

locking- locks are placed on data being modified by a transaction , preventing other transactions from reading or writing until the lock is released

transaction logging-changes are recorded in a log before being applied to in a database; in case of rollbacks, changes are undone based on the log

transaction

set of operations (read, write, update, delete) that are used for performing a set of logical work

attribute dependency

describes the relationship between two columns and how the values in one or more attributes affects the values in another
e.g. the employee id number would determine the employee name, address, email, etc. thus those other attributes are dependent on the id

conflict serializability & view serializability

concepts in dbms related to correctness of schedules (sequence of read and write operations) in concurrent transaction processing

conflict serializable

a schedule is so if it can be transformed into a serial schedule (a schedule where transactions are executed one after another without overlapping) by swapping non-conflicting operations

two operations are conflicting if they belong to two different transactions, at least one of them is a write, and they access the same data item

ACID properties

properties followed to maintain data consistency before and after a transaction

Atomicity (ACID)

entire transaction takes place at once or doesn’t happen at all

consistency (ACID)

database must be consistent before and after the transaction

Isolation (ACID)

multiple transactions occur independently without interference

durability (acid)

changes of a successful transaction occurs even if the system failure occurs

indexing

creating additional data structures that act as efficient references to the actual data stored in tables; significantly improve data retrieval performance by enabling faster locating of specific rows based on certain criteria

b-tree index

most common and versatile type of indexing method, organizes data in a tree like structure, suitable for various data types and query patterns

hash index

faster lookups for exact value matches, uses a hashing function to directly map values to row locations, not ideal for range queries or sorting as hash values themselves aren’t ordered

full-text index

used for searching text-based columns, stores word occurrences and their locations within the text

covering index

stores all columns needed for a specific query which eliminates the need to access the actual table data, offering exceptional performance on frequently used queries

composite index

combines multiple columns in a single index and useful for queries that involve conditions on multiple columns