SQL

DCC Software is a company that creates a wide range of software products for home computers. Its products include image, video and sound editing software.

DCC Software makes significant use of software libraries.

Explain the benefits of using software libraries to create software.

Once DCC Software has written a piece of software, they use translators to translate the programming code.

DCC Software is considering the benefits of both an interpreter and a compiler.

State two benefits of using an interpreter.

State two benefits of using a compiler.

DCC software uses a database table called TblSoftware to store details about all of their products.

Write an SQL statement to insert this new product into TblSoftware:

Write an SQL statement to return the Name of products that have a Price that is less than the Price of the product with the Name "DCCDraw".

You should use a nested SELECT statement in your answer.

A team of programmers create a robot that will be used in a factory. The robot will be able to do the work of multiple humans.

The programmers discuss whether to write the instructions for the robot in assembly language or a high-level language.

Describe two differences between assembly language and high-level languages.

The robot uses a multi-core processor. The programmers assume that this means that the robot will execute programs more quickly than using a single core processor.

Give one reason why this assumption can sometimes be true.

Explain why this assumption is not always true.

* The robot stores data internally and also communicates with other robots and users via a wireless network which is password protected. All data for these tasks is secured using either encryption or hashing.

Compare the robot’s use of encryption and hashing for storing and communicating data.

You should include the following in your answer:

• the different types of encryption that could be used and how this would secure data

• how hashing could be used to secure data and which data would be suitable

• why encryption and hashing are used by the robot for stored data and communications.

The robot provides a web-based interface for users. The home screen webpage for this interface is shown in Fig. 4.

Write CSS code that could be used in an external stylesheet to format all text using the <h1> tag as white with a red background.

The robot’s web interface uses images that show the robot in action. These photographs have been taken using a digital camera.

The programmers do not want other people to download and use these images.

State the name of one relevant piece of legislation and describe how this would protect these images.

For other areas of the web interface, the programmers need to use images that they have not created themselves.

Give two ways that they could make sure these images are used legally.

Details of all users that have accessed the robot are stored in a database table called TblAccessLog. This table stores the username and user type of each user. When a user accesses the robot, the current date is added to the DateAccessed field for that user.

A selection of the data from this table is shown here. Username is the key field.

Write an SQL statement to delete all records from the table TblAccessLog for users who have a UserType of "NotNeeded".

State two requirements for a database to be in First Normal Form (1NF).

Explain why the structure of TblAccessLog means that this database is not in First Normal Form (1NF).

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.)

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