Web Based Information Systems Flashcards
What is a SQL Schema? How would you write one?
A schema is a diagram of a database that showcases the tables, and their relationships
What is a one to many or many to many relationship?
In a database schema, the connections between tables specify the relationships. These are represented like 1* or 1.1 or .
What is the generic structure of a SQL query?
SELECT (item1, item2, item3)
FROM table
WHERE condition;
Remember always end a query with ;
How would you select all items from a table?
SELECT * FROM table;
How would you delete all items from a table?
DELETE FROM table;
How would you update a table?
UPDATE table
SET value1 = ‘Hello World’ WHERE id=’2’
How would you select a specific item from a table to delete?
DELETE FROM table WHERE item1 =’Hello World’;
How would you order results in ascending or descending order?
SELECT * FROM table
ORDER BY item1 ASC
SELECT * FROM table
ORDER BY item1 DESC
How would you order results alphabetically, In both directions?
SELECT * FROM table
ORDER BY item1;
SELECT * FROM table
ORDER BY item1 DESC;
What is a Primary Key?
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;
What is a Composite Key?
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);
What are the main datatypes in SQL?
- 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)
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).
- Write a query that returns the name of the products with in-stock values exceeding the outstanding debt to the supplier.
- Write a query that prints the money owed per supplier.
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;
What does JOIN mean in SQL?
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
What does it mean to use variables in SQL, how do they work and how would you use them?
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.
Define SUM and why would you use in in SQL?
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;
What is the definition of Eventual Consistency?
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.
Why is Eventual Consistency important?
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.
Define what a conceptual model is
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
What is a recursive relationship?
A relationship between a table and it’s elements, so table Person will have a 0.* relationship with personID
Give a one to one relationship example
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.
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
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)
What is the convention for writing relational database schemas?
We underline PRIMARY KEYS and FOREIGN KEYS, but we also might colour the FOREIGN KEYS to differentiate.
Name some characteristics of a relational data model
– 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
What are integrity constraints?
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.
What is a reflexive relationship?
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.
What are nested select statements? Why would you use them?
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’);
What is Normalisation?
A formal method for designing relational schemas, based on the concept of functional dependancy
What is 1NF?
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.
What is a functional dependancy?
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
How are functional dependancies shown in a diagram?
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.
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.
Borrower (BorrowerID, bName)
Book (ISBN, Title, LibClassif, LibFloor)
BookAuthors (ISBN, Author)
Loan (BorrowerID, ISBN, LoanDate)
What is 2NF?
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.
Consider the 1NF relation:
STUDENT_MODULE (
StudentId (PK),
StudentName,
ModuleCode (PK),
ModuleTitle,
SchoolCode,
SchoolName,
Mark
)
Reduce to the 2NF
STUDENT_MODULE(StudentId, ModuleCode, Mark)
STUDENT(StudentId, StudentName)
MODULE(ModuleCode, SchoolCode, SchoolName, ModuleTitle)
What is 3NF?
A relation is in 3NF if it is in 2NF and all attributes
are determined by the primary key
– i.e. no transitive dependencies
Reduce this to 3NF
STUDENT_MODULE(StudentId (PK), ModuleCode (PK), Mark)
STUDENT (StudentId (PK), StudentName)
MODULE(ModuleCode (PK), SchoolCode, SchoolName, ModuleTitle)
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.
What are Indices in DBMS?
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
What is the SQL command to create an index?
CREATE INDEX indexName ON Table (value);
What is a Transaction?
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.
What are the requirements for a Transaction?
The database is required to be in a consistent state before and after a
transaction.
What is the purpose of a Transaction?
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.
Provide the structure of a Transaction in SQL
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;
What does ROLLBACK do?
It will reverse all actions up the last start of transaction.
What is dirty read?
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;
What is lost update?
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;
What is Inconsistent analysis?
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
What is serialisability?
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,
Define the ACID properties
- 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.
Define Locking
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
Define Timestamping
Transactions are ordered by start time, if a conflict occurs the latest is aborted, does not scale.
What are Optimistic methods?
No checks are performed until the Transaction is completed and the differences are resolved when detected.
What are some problems with locking?
- 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.
What causes a Deadlock?
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; …
List some possible failures for a database
- 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.
What is reliability achieved by?
– Minimising
Frequency of failures
Side-effects
– Maximising
Ability to recover
List failure types for databases, with causes and fixes
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
What should a recovery mechanism do?
– 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
What information is stored in a DBMS log file?
– 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
Define the Transaction processing model
- 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
What is the Buffer Manager?
Controls the buffer pool
– Pages fetched from disk as required
– Pages removed when no longer required
– Typically LRU – Least Recently Used
What are the different types of Log-based recovery?
- 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
What is the undo/redo algorithm?
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.
What is PHP?
An open source server-side scripting language
What is 3 tier Client-server architecture?
- CLIENT:
- sends requests to web server
- renders page returned by server - SERVER:
- Receives request from client
- Executes program
- Returns page to client - DATABASE
- Executes queries and returns results to program
How is PHP code executed? What is the syntax?
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:
<?php
echo “Hello World”
?>
How do you declare a variable in PHP?
with $
$x = 1;
$y = 2;
$sum = $x + $y;
How do you add PHP variables to HTML?
Echo a string by concatenating the values together/
<html>
<body>
<h1>Mixing up PHP and HTML</h1>
</body>
</html>
How do you declare a function in PHP?
function doStuff ($a, $b) {
return $a + $b;
}
// Call function
echo doStuff(5, 6)
How do you initialise an array and index items?
$myArr = array(‘1’, ‘2’, ‘3’);
echo $myArr[0];
echo $myArr[1];
echo $myArr[2];
What is an associative array?
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
How do you write if/else in PHP?
if ($variable == “value”) {
echo “do something here”;
}
else if ($variable == “something else”) {
echo “an alternative”;
}
else {
echo “last resort”;
}
What is the syntax for writing for loops in PHP?
for ($i = 0; TRUE; $i++) {
if ( $i % 2 == 0 ) {
echo $i;
}
if ( $i >= 10 ) {
break;
}
}
How would you iterate through an array and print all elements in PHP?
$arr = array (10, 39, 45);
for ($i = 0; $i < count($arr); $i++) {
echo $a[i] . “ “ ;
}
What is a foreach?
$arr = array(10, 39, 45);
foreach ($arr as $value)
echo $value . “ “ ;
How might you print keys of an associative array?
$arr = array(“brand” => ”ford”, “model” => ”focus”);
foreach ($arr as $key => $value)
echo $key . “ = “ . $value . “<br></br>“;
How would you import external PHP files?
<?php
echo “<p id='myhead'>Simple example - “ . date(‘Y’)</p>”;
?>
Then in the main file
<html><head>…</head>
<body>
<h1>Some page with an header</h1>
<p>Some content.</p>
</body>
</html>
What are the two ways of calling PHP scripts from an HTML form?
POST and GET.
<form>
<input></input>
<input></input>
<input></input>
</form>
How would you use the data given from GET or POST in a PHP script?
Use inbuilt $_GET or $_POST to retrieve the data.
$_GET[‘firstName’] and $_GET[‘age’]
echo ‘<p> Thank you, ‘.$_POST[‘firstname’].’!</p>’
What is a Cookie?
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.
What data does a cookie store?
User information, username, specific setting (new user or not), unique identifier etc
How would you set up a cookie in PHP?
setcookie(‘uniqueID’,’Ali12’, time() + 1200);
We need the time, as cookies are supposed to be temporary.
How would you delete a cookie?
set a negative time, this ensures that the cookie is useless.
setcookie(‘uniqueID’,’Ali12’, time() - 1200);
What is a superglobal variable?
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
What is a global variable?
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()
What is a session?
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.
How do you set up a session?
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.
<?php
// Start the session
session_start();
?>
<!DOCTYPE html>
<html>
<body>
</body>
</html>
How would you use a cookie in a login system?
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’] . “ ?”;
Can PHP be object oriented?
Yes, we sometimes need to call functions on objects.
To do this, we use an arrow ->
$object->function();
How would you define a class in PHP?
<?php
class Fruit {
// Properties
public $name;
public $colour;
// Methods
function set_name($name) {
$this->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 “<br></br>”;
echo $banana->get_name();
?>
How would you connect to a database in PHP?
You need to define the:
- host
- database name
- user
- password
Then attempt a connection using PDO and call setAttribute() after.
<?php
$host = ‘dragon.ukc.ac.uk’;
$dbname = ‘lb514’;
$user = ‘lb514’;
$pwd = ‘xxx’;
try {
$conn = new PDO(“mysql:host=$host;dbname=$dbname”, $user, $pwd);
$conn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
echo ‘Connected to ‘.$dbname;
$conn = null;
} catch (PDOException $e) {
echo “PDOException: “.$e->getMessage();
}
?>
What is server side validation?
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()…
What are some useful functions to protect against SQL injection?
htmlspecialchars() will convert any characters to their ascii values.
strip_tags() removes html tags
addslashes() adds slash in front of each quote
