Web Based Information Systems

Question 1

What is a SQL Schema? How would you write one?

Answer 1

A schema is a diagram of a database that showcases the tables, and their relationships

Question 2

What is a one to many or many to many relationship?

Answer 2

In a database schema, the connections between tables specify the relationships. These are represented like 1* or 1.1 or .

Question 3

What is the generic structure of a SQL query?

Answer 3

SELECT (item1, item2, item3)
FROM table
WHERE condition;

Remember always end a query with ;

Question 4

How would you select all items from a table?

Answer 4

SELECT * FROM table;

Question 5

How would you delete all items from a table?

Answer 5

DELETE FROM table;

Question 6

How would you update a table?

Answer 6

UPDATE table
SET value1 = ‘Hello World’ WHERE id=’2’

Question 7

How would you select a specific item from a table to delete?

Answer 7

DELETE FROM table WHERE item1 =’Hello World’;

Question 8

How would you order results in ascending or descending order?

Answer 8

SELECT * FROM table
ORDER BY item1 ASC

SELECT * FROM table
ORDER BY item1 DESC

Question 9

How would you order results alphabetically, In both directions?

Answer 9

SELECT * FROM table
ORDER BY item1;

SELECT * FROM table
ORDER BY item1 DESC;

Question 10

What is a Primary Key?

Answer 10

A key that can be used to uniquely identify an element from a given table.

Here we are setting a primary key to be an id that is auto incrementing.

id INT NOT NULL AUTO_INCREMENT;
PRIMARY KEY id;

Question 11

What is a Composite Key?

Answer 11

A composite key is made up of more than one field to uniquely identify an item from a given table.

…
name VARCHAR(50),
surname VARCHAR(50),
PRIMARY KEY (name, surname);

Question 12

What are the main datatypes in SQL?

Answer 12

• VARCHAR (variable string length)
• INT (Integer)
• FLOAT (Floating point number)
• DECIMAL(10, 2) (Creates a fixed decimal point number)
• BOOL (Boolean, 0 is false)
• TEXT (Holds a large text block)
• CHAR (Fixed length string)

Question 13

Consider the business problem of a supermarket tracking products. Buyers require the following information on every product to process procurement and to support marketing: product name, in-stock, price (the price per unit), supplier ID and outstanding (amounting owed to the supplier). Sketch out a SQL schema with the information above. You need to clearly identify the entity (or entities), attributes, data types and key (or keys).

1. Write a query that returns the name of the products with in-stock values exceeding the outstanding debt to the supplier.
2. Write a query that prints the money owed per supplier.

Answer 13

We need to break down the question first.
- product
- in-stock
- price
- supplier ID
- outstanding

So we need to first need to define our tables.
1. Product
2. Supplier

CREATE TABLE Supplier (
sID INT UNIQUE NOT NULL AUTO_INCREMENT,
name VARCHAR(100) NOT NULL,
outstanding DECIMAL(10, 2) NOT NULL,
PRIMARY KEY(sID)
);

CREATE TABLE Product (
pID INT UNIQUE NOT NULL AUTO_INCREMENT,
name VARCHAR(50) NOT NULL,
price DECIMAL(10, 2) NOT NULL,
in_stock INT NOT NULL,
PRIMARY KEY (pID),
FOREIGN KEY (sID) REFERENCES Supplier(sID) ON DELETE CASCADE
);

SELECT p.name
FROM Product p
JOIN Supplier s ON p.sID = s.sID
WHERE p.in_stock > s.outstanding

SELECT s.sID s.name, SUM(p.price * p.in_stock) AS total_money_owed
FROM Supplier s
JOIN Product p ON s.sID = p.sID
GROUP BY s.sID, s.name;

Question 14

What does JOIN mean in SQL?

Answer 14

We are able to connect multiple tables together using the JOIN method, depending on if the tables both share a key and foreign key.

SELECT p.name
FROM Product p
JOIN Supplier s ON p.sID = s.sID

Question 15

What does it mean to use variables in SQL, how do they work and how would you use them?

Answer 15

If we have a table of Product and Supplier and we have a query, then we can simplify the query by using a variable name to represent a generic value from a table.

SELECT s.SID, s.name, SUM(p.price * p.in_stock) AS total_money_owed

This is taking two values of s and p, to represent the supplier and product, then these values were used to calculate the sum of the price times the amount in stock.

Question 16

Define SUM and why would you use in in SQL?

Answer 16

SUM will return the total sum of a numeric column.

SELECT a.aID, SUM(a.value) + SUM(b.value) AS total_value
FROM ATable a
JOIN BTable b ON a.aID = b.bID
GROUP BY a.aID;

Question 17

What is the definition of Eventual Consistency?

Answer 17

Eventual consistency is a property of distributed systems that ensures, given enough time and the absence of new updates, all replicas of a dataset will eventually converge to the same value. However, temporary inconsistencies may occur while updates propagate across nodes.

Question 18

Why is Eventual Consistency important?

Answer 18

Scalability: It allows distributed databases to scale across multiple nodes while maintaining availability.

High Availability: Ensures the system remains operational even when some nodes experience network delays or failures.

Performance Optimisation: Unlike strong consistency, which requires synchronisation across all nodes, eventual consistency reduces latency and improves throughput.

Question 19

Define what a conceptual model is

Answer 19

A model that showcases a database as a relationship based model, focusing on the relations.

• Entities become relations, often multiple entities merge into one.
• Relationships become FOREIGN KEYS
• Many to many relationships need to be resolved

Question 20

What is a recursive relationship?

Answer 20

A relationship between a table and it’s elements, so table Person will have a 0.* relationship with personID

Question 21

Give a one to one relationship example

Answer 21

A table of People and Department, where the Person is a manager and their ID, which is a PRIMARY KEY is a FOREIGN KEY in the Departments table.

Question 22

Identify the PRIMARY KEY and any potential FOREIGN KEYS

Date | Time | Room_Booked | Person_Booking
23/06/2015 | 10:00 | CC03 | pjr
23/06/2015 | 12:00 | CC03 | ec34
27/07/2015 | 10:00 | CC01 | pjr
23/06/2015 | 10:00 | CC01 | pjr

Answer 22

If we look, the Date and Time and Room_booked fields are unique in combination, which could make up the PRIMARY KEY.

The FOREIGN KEYS probably come from the Room_booked and Person_booking.

PRIMARY KEY (Date, Time, Room_Booked)
FOREIGN KEY (Room_booked)
FOREIGN KEY (Person_booking)

Question 23

What is the convention for writing relational database schemas?

Answer 23

We underline PRIMARY KEYS and FOREIGN KEYS, but we also might colour the FOREIGN KEYS to differentiate.

Question 24

Name some characteristics of a relational data model

Answer 24

– each relation in a model has a distinct name (object identity)
– each attribute in a relation has a distinct name
– all values of an attribute are drawn from the same domain
– attribute values must be atomic
– ordering of attributes in a relation is of no significance
– ordering of elements in a relation is of no significance
– elements in a relation must be distinct; primary key (unique row
identifier) must not have a null value
* primary key may be composite
– non-key attributes may have null values

Question 25

What are integrity constraints?

Answer 25

Rules to help ensure that the database does not have invalid values and configurations - By definition all rows in a relation are distinct, so PRIMARY KEYS should have distinct values. - No PRIMARY KEY should have a null value. - The value of a FOREIGN KEY must be the value of an existing PRIMARY KEY or NULL.

Question 27

What is a reflexive relationship?

Answer 27

Reflexive Relation is a mathematical or set-theoretical relation in which every element is related to itself, meaning that for every element ‘x’ in the set, the pair (x, x) is a part of the relation. In database management, a reflexive relationship, also known as a recursive or self-referential relationship, occurs when an entity relates to itself, often used to model hierarchical structures or relationships within the same table.

Question 28

What are nested select statements? Why would you use them?

Answer 28

Nesting select statements are useful as they allow us to make targeted queries. SELECT DISTINCT city FROM Customer WHERE cID IN ( SELECT cID FROM Ordering WHERE pID = 'p03');

Question 29

What is Normalisation?

Answer 29

A formal method for designing relational schemas, based on the concept of functional dependancy

Question 30

What is 1NF?

Answer 30

A relation is in 1NF if all attributes are atomic. Atomic means that it is a simple indivisible piece of data that cannot be broken down any further.

Question 31

What is a functional dependancy?

Answer 31

Functional dependency in DBMS is an important concept that describes the relationship between attributes (columns) in a table. It shows that the value of one attribute determines the other. x -> y if x implies the value for y

Question 32

How are functional dependancies shown in a diagram?

Answer 32

They are drawn as boxes with arrows linking to their dependancies. To quickly decipher a dependancy refer to the ABC method, where we assign A or B to the values and then mark their dependancies with C.

Question 33

If we have diagram of the following table: LIBRARY(BorrowerID, ISBN, bName, LoanDate, Authors, Title, LibClassif, LibFloor) Identify the relationships and refine to the 1NF.

Answer 33

Borrower (BorrowerID, bName) Book (ISBN, Title, LibClassif, LibFloor) BookAuthors (ISBN, Author) Loan (BorrowerID, ISBN, LoanDate)

Question 34

What is 2NF?

Answer 34

A relation is 2NF if it is in 1NF and no non-prime attribute is functionally dependant on any proper subset of the primary key. A non prime attribute is an attribute that is not part of the primary key.

Question 35

Consider the 1NF relation: STUDENT_MODULE ( StudentId (PK), StudentName, ModuleCode (PK), ModuleTitle, SchoolCode, SchoolName, Mark ) Reduce to the 2NF

Answer 35

STUDENT_MODULE(StudentId, ModuleCode, Mark) STUDENT(StudentId, StudentName) MODULE(ModuleCode, SchoolCode, SchoolName, ModuleTitle)

Question 36

What is 3NF?

Answer 36

A relation is in 3NF if it is in 2NF and all attributes are determined by the primary key – i.e. no transitive dependencies

Question 37

Reduce this to 3NF STUDENT_MODULE(StudentId (PK), ModuleCode (PK), Mark) STUDENT (StudentId (PK), StudentName) MODULE(ModuleCode (PK), SchoolCode, SchoolName, ModuleTitle)

Answer 37

STUDENT_MODULE(StudentId (PK), ModuleCode (PK), Mark) STUDENT (StudentId (PK), StudentName) MODULE(ModuleCode (PK), SchoolCode, ModuleTitle) SCHOOL(SchoolCode (PK), SchoolName) We have removed all dependancies from MODULE, by creating a new table SCHOOL, which houses SchoolCode and SchoolName with SchoolCode being the PRIMARY KEY.

Question 38

What are Indices in DBMS?

Answer 38

Indices are a key concept in DBMS allowing a significant speedup of DB operation – An index is a data structure that allows speed up of queries by enabling fast lookup of records – An index is typically created as a separate file – A sparse index has an index record only for some of the search key values – A dense index, which is more common, has an index record for every search key value in the file - Primary Keys are indexed by default in most DBMS

Question 39

What is the SQL command to create an index?

Answer 39

CREATE INDEX indexName ON Table (value);

Question 40

What is a Transaction?

Answer 40

A transaction is an action, or series of actions, carried out by a user or application which accesses or changes the contents of a database.

Question 41

What are the requirements for a Transaction?

Answer 41

The database is required to be in a consistent state before and after a transaction.

Question 42

What is the purpose of a Transaction?

Answer 42

A transaction in SQL is a sequence of one or more SQL statements executed as a single unit of work. These statements could be performing operations like INSERT, UPDATE, or DELETE. The main idea behind a transaction is that all changes made within it should either be completely successful or fully undone in case of failure. In the case of an error, a rollback ensures that no partial changes are saved.

Question 43

Provide the structure of a Transaction in SQL

Answer 43

BEGIN TRANSACTION; -- Deduct $150 from Account A UPDATE Accounts SET Balance = Balance - 150 WHERE AccountID = 'A'; -- Add $150 to Account B UPDATE Accounts SET Balance = Balance + 150 WHERE AccountID = 'B'; -- Commit the transaction if both operations succeed COMMIT; Commands start with BEGIN TRANSACTION; and end with COMMIT;

Question 44

What does ROLLBACK do?

Answer 44

It will reverse all actions up the last start of transaction.

Question 45

What is dirty read?

Answer 45

When a transaction is aborted, but intermediate state is visible to other transactions. -- This updates t and sets x to x + 100 for id 01 T1: UPDATE t SET x=x+100 WHERE id = 01; ROLLBACK; -- This is attempting to access 01 and will read x + 100 T2: SELECT * FROM t WHERE id = 01;

Question 46

What is lost update?

Answer 46

When two transactions update the same item. T1: UPDATE t SET x=x+100 WHERE id = 01; T2: UPDATE t SET x=x-50 WHERE id = 01;

Question 47

What is Inconsistent analysis?

Answer 47

It can occur when one transaction is performing an aggregate operation whilst another is updating. T1: SELECT SUM(x) FROM t; T2: UPDATE t SET x=x+50 WHERE id = 01; hint: aggregate means caclulation

Question 48

What is serialisability?

Answer 48

Two Transactions are serialisable if, when interleaved, they produce the same result as if they were executed in a serial manner. A good concurrency control system attempts to schedule transactions so that concurrent transactions are serialisable,

Question 49

Define the ACID properties

Answer 49

- ATOMIC: set of actions that form a transaction are indivisible. - CONSISTENT: Transaction always results in a consistent database after operation, if it was in a consistent state before. - ISOLATED: Transactions must be independent of each other (serialisable). - DURABLE: The effects of a committed transaction must be permanent.

Question 50

Define Locking

Answer 50

When data items are locked during access to deny access to other transactions. - Before each granule may be accessed, a lock is requested. - Read lock is a shared lock - Write lock is an exclusive lock

Question 51

Define Timestamping

Answer 51

Transactions are ordered by start time, if a conflict occurs the latest is aborted, does not scale.

Question 52

What are Optimistic methods?

Answer 52

No checks are performed until the Transaction is completed and the differences are resolved when detected.

Question 53

What are some problems with locking?

Answer 53

- Deadlock, a circular waiting condition, caused be two or more transactions, each waiting for another to release its lock on a granule. - Livelock, Transaction waits indefinitely for a lock despite the state of the transactions changing.

Question 54

What causes a Deadlock?

Answer 54

If two transactions need to access the same resource, then neither transaction can complete. T1: read x; set x=x+100; read y; … T2: read y; set y=y-1, read x; …

Question 55

List some possible failures for a database

Answer 55

- System crashes - loss of main memory - Hardware failures - loss of database storage - Natural disaster - destruction of database system - Human errors - corruption or destruction of data, hardware or software.

Question 56

What is reliability achieved by?

Answer 56

– Minimising Frequency of failures Side-effects – Maximising Ability to recover

Question 57

List failure types for databases, with causes and fixes

Answer 57

TRANSACTION FAILURE Cause: deadlock Fix: transaction rollback and restart SYSTEM FAILURE Cause: power failure Fix: System should recover to consistent state on restart COMMUNICATION FAILURE Cause: network outage Fix: This must be handled in distributive systems MEDIA FAILURE Cause: Disk failure Fix: Recover from backup

Question 58

What should a recovery mechanism do?

Answer 58

– Ensure transactions are either completely executed or not at all - Atomicity – Ensure updates from committed transactions are permanently stored - Durability – Guarantee database Consistency after recovery – Have minimal impact on normal processing

Question 59

What information is stored in a DBMS log file?

Answer 59

– Transaction records, e.g. Transaction ID Type of log record - Transaction start, commit, abort records Data affected by insert, update or delete actions Data page ID Before-image , i.e. data page before the action After-image , i.e. data page after the action - Checkpoint records A point where all changes made to the database are on the disk

Question 60

Define the Transaction processing model

Answer 60

- Pages needed to process transactions are read from disk into memory buffers - Buffer pages are updated - Records of updates are written to log - On commit, log records flushed to disk before releasing locks

Question 61

What is the Buffer Manager?

Answer 61

Controls the buffer pool – Pages fetched from disk as required – Pages removed when no longer required – Typically LRU – Least Recently Used

Question 62

What are the different types of Log-based recovery?

Answer 62

* Normal recovery - After normal shutdown – Start from last log record (a checkpoint written at shutdown) * Warm recovery - After system failure – Revert to last checkpoint in log – Apply committed transactions – Remove effects of un-committed transactions * Cold recovery - After media failure – Restore from backup/dump – Apply log records to reach latest consistent state

Question 63

What is the undo/redo algorithm?

Answer 63

An algorithm that performs the following: 1. Creates two empty lists, undo/redo 2. Starts reading the log at last checkpoint 3. Puts all active transactions on the undo list 4. Read log forwards - If BEGIN TRANSACTION found - add to undo - If COMMIT found, remove transaction from undo, add transaction to redo 5. Uses before-images to undo all on undo list, uses after-images to redo all on the redo list.

Question 64

What is PHP?

Answer 64

An open source server-side scripting language

Question 65

What is 3 tier Client-server architecture?

Answer 65

1. CLIENT: - sends requests to web server - renders page returned by server 2. SERVER: - Receives request from client - Executes program - Returns page to client 3. DATABASE - Executes queries and returns results to program

Question 66

How is PHP code executed? What is the syntax?

Answer 66

PHP code is usually embedded within an HTML page and the code is executed on the server before the page is delivered to user. The syntax is:

Question 67

How do you declare a variable in PHP?

Answer 67

with $ $x = 1; $y = 2; $sum = $x + $y;

Question 68

How do you add PHP variables to HTML?

Answer 68

Echo a string by concatenating the values together/

Mixing up PHP and HTML

hello ".$myvariable."

" ?>

Question 69

How do you declare a function in PHP?

Answer 69

function doStuff ($a, $b) { return $a + $b; } // Call function echo doStuff(5, 6)

Question 70

How do you initialise an array and index items?

Answer 70

$myArr = array('1', '2', '3'); echo $myArr[0]; echo $myArr[1]; echo $myArr[2];

Question 71

What is an associative array?

Answer 71

An array that associates a value with another. $postage = array ( 'First Class' => ‘1’, 'Second Class' => ‘2’, 'Next Day' => ‘3’ ); echo $postage[‘First Class’]; // prints 1

Question 72

How do you write if/else in PHP?

Answer 72

if ($variable == “value”) { echo “do something here”; } else if ($variable == “something else”) { echo “an alternative”; } else { echo “last resort”; }

Question 73

What is the syntax for writing for loops in PHP?

Answer 73

for ($i = 0; TRUE; $i++) { if ( $i % 2 == 0 ) { echo $i; } if ( $i >= 10 ) { break; } }

Question 74

How would you iterate through an array and print all elements in PHP?

Answer 74

$arr = array (10, 39, 45); for ($i = 0; $i < count($arr); $i++) { echo $a[i] . “ “ ; }

Question 75

What is a foreach?

Answer 75

$arr = array(10, 39, 45); foreach ($arr as $value) echo $value . “ “ ;

Question 76

How might you print keys of an associative array?

Answer 76

$arr = array(“brand” => ”ford”, “model” => ”focus”); foreach ($arr as $key => $value) echo $key . “ = “ . $value . “
“;

Question 77

How would you import external PHP files?

Answer 77

Simple example - " . date('Y')

"; ?> Then in the main file …

Some page with an header

Some content.

Question 78

What are the two ways of calling PHP scripts from an HTML form?

Answer 78

POST and GET.

Question 79

How would you use the data given from GET or POST in a PHP script?

Answer 79

Use inbuilt $_GET or $_POST to retrieve the data. $_GET['firstName'] and $_GET['age'] echo '

Thank you, '.$_POST['firstname'].'!

'

Question 80

What is a Cookie?

Answer 80

A piece of small information, that is sent by the server to the client with HTTP, it is then stored on the user's computer and sent back to the server with subsequent requests.

Question 81

What data does a cookie store?

Answer 81

User information, username, specific setting (new user or not), unique identifier etc

Question 82

How would you set up a cookie in PHP?

Answer 82

setcookie('uniqueID','Ali12', time() + 1200); We need the time, as cookies are supposed to be temporary.

Question 83

How would you delete a cookie?

Answer 83

set a negative time, this ensures that the cookie is useless. setcookie('uniqueID','Ali12', time() - 1200);

Question 84

What is a superglobal variable?

Answer 84

Some predefined variables in PHP are "superglobals", which means that they are always accessible, regardless of scope - and you can access them from any function, class or file without having to do anything special. The PHP superglobal variables are: $GLOBALS $_SERVER $_REQUEST $_POST $_GET $_FILES $_ENV $_COOKIE $_SESSION

Question 85

What is a global variable?

Answer 85

By using the global syntax, you can access variables from outside the scope of a function or script as they are global. $x = 75; function myfunction() { echo $x; } myfunction()

Question 86

What is a session?

Answer 86

When you work with an application, you open it, do some changes, and then you close it. This is much like a Session. The computer knows who you are. It knows when you start the application and when you end. But on the internet there is one problem: the web server does not know who you are or what you do, because the HTTP address doesn't maintain state. Session variables solve this problem by storing user information to be used across multiple pages (e.g. username, favorite color, etc). By default, session variables last until the user closes the browser.

Question 87

How do you set up a session?

Answer 87

Begin with session_start(), then assign information to the $_SESSION superglobal, that is saved into a file in the server and linked to the session ID.

Question 88

How would you use a cookie in a login system?

Answer 88

We start a session, which generates an ID, then we can assign new session values to the values received from the login. CLIENT $_POST[‘username’] = ‘Alice’ $_POST[‘password’] = ‘123’ $_POST[‘color’] = ‘blue’ $_POST[‘food’] = ‘cookies’ SERVER session_start(); $_SESSION[‘name’]=$_POST[‘username’] $_SESSION[‘color’]=$_POST[‘color’] $_SESSION[‘food’]= $_POST[‘food’] echo “Hi $_SESSION[‘name’], would you like some “; echo $_SESSION[‘color’] . “ “ . $_POST[‘food’] . “ ?”;

Question 89

Can PHP be object oriented?

Answer 89

Yes, we sometimes need to call functions on objects. To do this, we use an arrow -> $object->function();

Question 90

How would you define a class in PHP?

Answer 90

name = $name; } function get_name() { return $this->name; } } $apple = new Fruit(); $banana = new Fruit(); $apple->set_name('Apple'); $banana->set_name('Banana'); echo $apple->get_name(); echo "
"; echo $banana->get_name(); ?>

Question 91

How would you connect to a database in PHP?

Answer 91

You need to define the: - host - database name - user - password Then attempt a connection using PDO and call setAttribute() after. setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION); echo 'Connected to '.$dbname; $conn = null; } catch (PDOException $e) { echo "PDOException: ".$e->getMessage(); } ?>

Question 92

What is server side validation?

Answer 92

Where the server will check incoming values and assess them for being valid inputs. isset() will check that a variable is set and not null isempty() will check that a variable is set and not null or not empty is_string(), is_int(), is_numeric()...

Question 93

What are some useful functions to protect against SQL injection?

Answer 93

htmlspecialchars() will convert any characters to their ascii values. strip_tags() removes html tags addslashes() adds slash in front of each quote