Relational databases and keys

Orla is developing a program that will allow candidates to complete quizzes for a range of subjects. Each quiz has three questions and each one is multiple choice with three possible answers.

Orla is considering using a flat file database to store data about candidates, and the subjects and quizzes they sit.

Explain the limitations of using flat file databases.

In order to determine what tables were required, Orla used database normalisation.

State two requirements for a database to be in Second Normal Form (2NF).

State two requirements for a database to be in Third Normal Form (3NF).

Orla wants a web based version of the quiz. She develops a function called checkAnswers to calculate the score of each quiz on the client side using JavaScript.

Next to each candidate answer is an empty HTML element. When the user presses submit, the function, checkAnswers, will check if each answer is correct. If so, it will add the word "Correct" into the empty HTML element.

The function, checkAnswers takes three parameters:

• responses – an array of the IDs of the HTML elements that contains the user’s responses.

• feedbacks – an array of the IDs of the HTML elements that the text "Correct" is to be written to if the candidate gets the question right. The array will be the same length as responses.

• answers – an array of the correct answer for each of the questions. The array will be the same length as responses.

The function returns the final score out of three for the quiz. It loops through each question to calculate if the candidate got it correct and processes it appropriately if they did.

Complete the function checkAnswers, using pseudocode or program code.

7* Rosa has recently opened a new leisure centre. Currently she has 150 members, however, she is estimating that this will increase to around 10 000 members.

Rosa will store members’ details in a database. She will also store gym class bookings and appointments with fitness instructors.

Rosa is considering using either a flat file database or a relational database.

Discuss the benefits and drawbacks of flat file databases and relational databases and which one would be the most suitable for Rosa.

You should include the following in your answer:

• the features of flat file databases and relational databases

• the benefits and drawbacks of each type of database

• which type of database would be the most suitable for Rosa.

A video streaming service uses a relational database. An extract of the data from two tables from this database is shown in Fig. 2.

Membership contains data about current memberships that customers hold and package contains data about different streaming packages available.

State what is meant by the term ‘primary key’.

Identify the foreign key used in the database and the table name where this is a foreign key.

Identify the data type of the CostPerMonth(£) field.

Give the name of the field that could be stored using a Boolean data type.

The Adverts field indicates if customers will be shown adverts. true indicates that customers will be shown adverts, and false indicates that adverts are not shown.

Write Structured Query Language (SQL) to return the Username and FirstName fields for all customers who see adverts.

When new customers join the streaming service, their name, email address and contact details are captured so that they can be entered into the database.

Identify one method of capturing a new customer’s personal data, describing why this method is suitable.

Sometimes the company may need to move or backup its data they hold about customers.

Identify two methods of exchanging data with other computer systems.

The database supports ACID transactions. ACID stands for Atomicity, Consistency, Isolation and Durability.

Describe what is meant by a transaction being durable.

Give one way that durability can be achieved for a completed transaction.

Explain how record locking can be used to ensure that the ACID principle of isolation is achieved when carrying out multiple transactions.

Give one disadvantage of using record locking.

The Copyright Designs and Patents Act 1988 applies to all videos that are streamed.

Explain how this act applies to the videos.

All videos that are streamed are compressed. Customers have the option to choose from watching the videos with lossy compression or lossless compression.

Explain how this choice will impact the customer.

A program is written using an object-oriented programming paradigm and uses a class called video to organise the videos that are streamed to customers.

The class video has these attributes:

• name

• number of views

• star rating.

The constructor method will set the name attribute to the name that is passed in as a parameter. The constructor will also initially set the number of views to 0 and the star rating to 3.

Write program code or pseudocode to declare the class video and initialise the required attributes as private.

You should include both the attribute definitions and the constructor method in your answer.

A public method called updateviews() will update the number of views after a video has been viewed. This method is defined inside the video class.

Write program code or pseudocode for the method updateviews() to increase the number of views by one.

OCR Insurance uses a computer system to calculate the price that customers pay for car insurance.

The computer system contains a CPU, GPU, RAM and ROM.

State two factors that affect the performance of a CPU.

Explain the difference between RAM and ROM, including how these are used by the computer system.

Describe one non-graphical use OCR Insurance may have for a GPU.

The CPU uses pipelining to improve efficiency.

Explain what is meant by the term ‘pipelining’.

* OCR Insurance’s computer system uses secondary storage across the company in servers, client machines and for backup purposes.

For each of these, discuss whether magnetic storage or solid state storage would be most suitable, taking into account the advantages and disadvantages of both.

Customers’ details are stored in the flat file database table Customer. An extract of the table is shown below.

State what is meant by the term ‘primary key’, identifying the primary key in the table above.

Write the SQL statement that would show only the CustomerID and Surname fields for customers with the Title “Miss” or “Mrs”.

Describe one problem that would arise with the flat file database structure if a customer wanted to insure more than one car at the same time.

Describe how the flat file database structure could be altered to efficiently allow each customer to insure multiple cars at the same time. (You may assume each car is insured to only one customer.)

A hotel uses a computer system to keep track of room bookings. The hotel staff are able to query a database to discover which rooms are booked or which rooms are free.

The hotel’s computer network uses a client-server model.

Describe what is meant by the term ‘client-server’ in this context.

Give two advantages of client-server compared to peer-to-peer.

The hotel’s network uses multiple switches.

Explain the purpose of a network switch.

* The hotel is concerned about the security of its computer network.

Discuss the threats which potentially exist to the hotel’s computer network and how these threats could be eliminated or reduced.

The hotel stores data about rooms, customers and bookings in a database. Each customer can book multiple rooms and each room can be booked multiple times.

Draw an Entity Relationship Diagram for this database.

Define what is meant by the term ‘foreign key’, giving one example of where a foreign key would be used in the hotel booking database.

Describe two different ways that hashing could be used in this database.

The hotel booking database enforces referential integrity.

Explain what is meant by the term ‘referential integrity’ and how this could potentially be broken.

Describe what the SQL statement below does.

SELECT FlightNumber FROM Flight WHERE DestinationCode='JFK'

The airport cancels all its flights to Heathrow on 4^th July 2018.

The SQL statement below shows all the data for flights going to Halifax. Rewrite it so it instead removes all flights to Heathrow on 4^th July 2018.

SELECT * FROM Flight WHERE DestinationName='Halifax'

Tables often have primary and secondary keys.

State why DestinationCode would not be a suitable primary key for the Flight table.

State why DestinationCode would be a suitable secondary key for the Flight table.

The airline wishes to ensure the database is normalised.

Describe why the database can be considered to be in First Normal Form.

Describe why the database can be considered to be in Second Normal Form.

Describe why the database can not be considered to be in Third Normal form.

The airport wishes to allow airlines to be able to access the data it has on flights via the internet.

Describe one format or method the airport could use to provide the data to the airlines so they can use it in their own applications.

A web forum stores all its content in a database.

The forum stores details of its users in the table called Users. An extract of Users is shown

Describe what is meant by the term ‘primary key’, giving an example from the table above.

The user’s password is passed to a function that generates a hash and the result is stored in passwordHash.

Describe what is meant by the term ‘hash’.

Describe one advantage to storing the password as a hash.

Write an SQL statement to get just the passwordHash and locked values of the user Apollo.

Sometimes users can have their accounts locked if they behave inappropriately. When this is the case the locked field is set to 1 rather than 0.

Write an SQL statement that locks the account of the user Hades

The function checkAccess takes in the password the user has entered (givenPassword along with the password hash (passwordHash) and locked value (locked).

passwordHash and locked have already been extracted from the database before being passed to the function. It should return the value true if a user should be allowed access to a system and false if they aren’t.

Your function should make use of the pre-written function hash() which takes in a string and returns the hash of that string.

e.g.

hash("Hello") returns f7ff9e8b7bb2e09b70935a5d785e0cc5d9d0abf0

Complete the function checkAccess.

function checkAccess(givenPassword, passwordHash, locked)

endfunction