General Study Pt2.

Question 1

Phantom Phenomena

Answer 1

Any transaction processing related commands need to be translated into basic and explicit reads and writes on database objects.

Question 2

Sharing Phenomena

Answer 2

Running concurrent transaction through different isolation levels:

Serializable Read - Not possible Dirty Read/Inconsistent Read/Phantom Read

Repeatable Read - Not possible Inconsistent Read/Phantom Read

Read Committed - Possible Inconsistent Read/Phantom Read

Read Uncommitted - Possible Dirty Read/Inconsistent Read/Phantom Read

Question 3

Wait for Graph

Answer 3

Draw a graph of the processes and what processes are waiting for what resources. If there are cycles then there is a deadlock.

Question 4

Two Phase Locking (2PL)

Answer 4

2PL is a concurrency control mechanism used to manage locks, it is in charge of assigning locks and unlocks. It has two phases, growing and shrinking. During the growing phase it hordes locks, and during the shrinking phase it releases locks. This is a strict phase, after a lock is released, no more locks may be attained.

Question 5

Conflict Serializable

Answer 5

A serializable schedule is conflict serializable if it can be transferred to a serial schedule by swapping non-conflicting operations.

Question 6

Serial Schedules

Answer 6

A serial schedule executes transactions in their totality. It is correct. It doesn’t allow interleaving.

Question 7

Recoverable Schedules

Answer 7

it is recoverable, no need to roll back. If no transaction T in S commits until all transactions Ti that have written an item that T reads have committed.(all changes are made before reading)

Question 8

Cascading Rollback:

Answer 8

Abort a transaction, To avoid this, make sure that every transaction reads only transactions that were written by committed transactions.

Question 9

Strict Schedule:

Answer 9

On top of cascading rollbacks, writes delayed until all transactions previously written are either committed or aborted.

Question 10

List of common ports at…

Answer 10

Can find list of common ports at /etc/services on UNIX.

Question 11

IP

Answer 11

Internet Protocol is part of OS. Address in dotted decimal.

Question 12

IP: TCP Specification in OS

Answer 12

Allocate local resources -> Define local and remote endpoints -> initiate communication -> send and receive data.

Question 13

Socket

Answer 13

API to TCP. Created on UNIX as a set of OS calls:
Communication connection point which can be named and addressed
Data Structure
Set of API functions

Question 14

COM/OLE

Answer 14

COM (Component Object Model) and OLE (Object Linking and Embedding) are technologies developed by Microsoft for creating modular and extensible software components. In the context of distributed databases, COM/OLE can be used to facilitate communication and interaction between different software components or applications.

Question 15

How does a JAVA RMI work

Answer 15

It does as so:
1. Server sets up service and registers objects
2. Client looks for objects in registry
3. Client retrieves object stub
4. As client uses stub, communicates with server

Question 16

Interoperability

Answer 16

Interoperability refers to the ability to be able to run a process or referencing a process on another platform or address space.

Question 17

Producer Consumer

Answer 17

Producers generate tasks or data and consumers process them. Communicate asynchronously through a buffer, preventing resource conflicts.

Question 18

Master Slave

Answer 18

Master slave is a model in which there is one node which is a master and a number of nodes which are the slaves. The master nodes distributes tasks amongst the slave nodes, in the form of parallel programming.

Question 19

Creating Parallel Program

Answer 19

1) First we want to break down our task into sub tasks
2) We want to assign each of these sub-tasks to a process
3) We then want to orchestrate synchronization, communication and data access
4) Finally map processes to processors

Question 20

Technology needed to design Physical DDB

Answer 20

DB Connection (FDW, stored procedures…)
Triggers
Views
Replication

Question 21

Perils of Replication

Answer 21

Security
Consistency
Performance
Recovery

Question 22

2PC

Answer 22

Standards for two phase commit. Allows programmers to commit the entire transaction in one request with this request spanning over various systems and networks.

Question 23

Procedural Replication

Answer 23

Proc. Replication queues calls to procedure and then delivers to all relevant sites. Conflict handlers required.

Question 24

Multimaster Replication

Answer 24

Used if there is need to maintain a table at numerous sites with updates being reflected at all sites. Not good for massive data updates.

Uses synchronous for online data and asynchronous with queue for eventually delivered. Needs conflict resolution in async.

Question 25

Updatable Remote Views

Answer 25

If local changes to a remote view are required, we have to send these changes to the master site which builds the remote view.

Question 26

Remote Read Only Views

Answer 26

Copy part of a database into local database, used when connection dependency is high.

Question 27

In logical replication, a publisher can:

Answer 27

• Broadcast its updates
• Can be a number of subscribers to the same broadcast
• Can control what updates to show
• Response time and latency at subscriber are OK.

Question 28

Logical Replication

Answer 28

Can publish row changes over one or more persistent tables in a source database to a number of interested parties through a subscription. Source DB must declare a named object as a publication.

Question 29

Client Set Up

Answer 29

1. Install a client software on a client machine
2. Check client machine can ping servers
3. Check current assigned IP is in range
4. Add a connection

Question 30

Server Set Up

Answer 30

1. Install postgreSQL server
   1.1 Adjust start-up files
2. Restart PostgreSQL server
3. Extract the following details
   3.1 Server Ip and listening port
   3.2 DB name
   3.3 DB username
4. Check local instance

Question 31

PGSQL Shell \q

Answer 31

\q Remark quit shell

Question 32

PGSQL Shell \I

Answer 32

\l Remark list of databases …

Question 33

PGSQL Shell \c

Answer 33

\c scott Remark … connect to database scott

Question 34

How do you create a table

Answer 34

CREATE TABLE name

Question 35

How do you create a view

Answer 35

CREATE OR REPLACE VIEW name
AS SELECT * FROM scott.emp
WHERE emp.no = 10;

Question 36

Input text Postgres in SQL

Answer 36

COPY table_name FROM ‘/path/to/input/file.csv’ WITH CSV HEADER;

Question 37

Output text postgres in SQL

Answer 37

COPY table_name TO ‘/path/to/output/file.csv’ WITH CSV HEADER;

Question 38

Foreign Data Wrapper enable functionality

Answer 38

CREATE EXTENSION postgres_fdw;

Question 39

Create FDW server

Answer 39

CREATE SERVER fdw_database_server FOREIGN DATA WRAPPER postgres_fdw OPTIONS ( host ‘127.0.0.1’, port ‘5432’, dbname ‘dm’ );

Question 40

Create DBlink Extension SQL

Answer 40

CREATE EXTENSION dblink;

Question 41

Get DBLink connections SQL

Answer 41

SELECT dblink_get_connections();

Question 42

Disconnect DBlink SQL

Answer 42

SELECT dblink_disconnect(‘’con2tastrade’);

Question 43

Copy data from other DB using DBLink SQL

Answer 43

SELECT * FROM dblink( ‘port=5432 dbname=server_b user=postgres password=?’, ‘select * from test.t1’) AS t1 (a int, b varchar(50));

Question 44

Design Quirk

Answer 44

In Postgres, all databases are isolated from each other, even if they are on the same server.

Question 45

State of Play

Answer 45

Use a middleman to connect components and apps to ease communications.

Question 46

Speedup

Answer 46

Measures Decrease in Response Time when adding more distributed nodes. Linear speedup is ideal.

Question 47

Scale Up

Answer 47

Measures how long response time can be maintained when adding more workload and nodes. Ideally remains the same.

Question 48

Size Up

Answer 48

Measures the proportionality of response time to increase of workload

Question 49

Design Considerations

Answer 49

3 Dimensions of design considerations:

Level of Sharing: We can choose between no sharing, data sharing or data+program sharing.

Behaviour of Access Patterns: Static or Dynamic

Level of Knowledge on Access Pattern Behaviour

Question 50

Components of DDBMS

Answer 50

• User Processor
  
  • UI: I/O of end users
  • Semantic Data Controller: Integrity
  • constraints & authorizations
  • Global Query Optimizer & Decomposer
  • Distributed Execution Monitor
• Data Processor
  
  • Local Query Optimizer
  • Local Recovery Manager
  • Run Time Support Processor

Question 51

DBMS Architectural Model

Answer 51

Distribution of Data: Each node is a fully fledged DBMS

Autonomy of Data: DBMS manages DBMSs that run in total isolation.

Heterogeneity: DDB is made up of DBMS’s that vary in data modelling, query languages etc.