H446 Revision

1.3.2

Databases

Exam questions: 6 questions, 88 marks

Question 1

9 marks

1

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.
Mark scheme 1

Tick each point your answer makes. Your mark is worked out from your ticks, and you can change it.

Mark Band 3-High Level (7-9 marks)
The candidate demonstrates a thorough knowledge and understanding of databases. The material is generally accurate and detailed.

The candidate is able to apply their knowledge and
understanding directly and consistently to the context provided. Evidence/examples will be explicitly relevant to the explanation.

There is a well-developed line of reasoning which is clear and logically structured. The information presented is relevant and substantiated.

A mark band 3 answer will cover benefits and disadvantages of both flat file and relational databases. The expansions will relate to Rosa’s needs and will go on to evaluate why a relational database is most appropriate.

Mark Band 2-Mid Level (4-6 marks)
The candidate demonstrates reasonable knowledge and understanding of databases; the material is generally accurate but at times underdeveloped.

The candidate is able to apply their knowledge and
understanding directly to the context provided although one

or two opportunities are missed. Evidence/examples are for the most part implicitly relevant to the explanation.

There is a line of reasoning presented with some structure. The information presented is in the most part relevant and supported by some evidence.

A mark band 2 answer will cover features of both flat file and relational databases and will expand the points, relating these to benefits and drawbacks and size of Rosa’s data set although these may not be balanced. There will be an attempt to recommend which type is suitable.

Mark Band 1-Low Level (1-3 marks)
The candidate demonstrates a basic knowledge of databases; the material is basic and contains some inaccuracies. The candidate makes a limited attempt to apply acquired knowledge and understanding to the context provided.

The candidate provides nothing more than an unsupported assertion.

The information is basic and communicated in an
unstructured way. The information is supported by limited evidence and the relationship to the evidence may not be clear.

A mark band 1 answer will contain some basic, relevant points related to features of flat file and/or relational databases. It may not be linked to Rosa’s specific needs.

0 mark
No attempt to answer the question or response is not worthy of credit.

Guidance

Points may include but aren’t limited to:

AO1 Knowledge and Understanding

A flat file database stores data in a single table, often in a plain text file. Usually, each line will hold a single record and delimiters such as commas are used to separate this into different fields.

Benefits:

  • They are easy to set up as only one table is needed
  • All records are stored in one place

Drawbacks:

  • Tables can contain lots of redundant data which increases the amount of storage space needed
  • Searching the database can be slower as there is more data to search
  • The database can be more difficult to manage and possibly expand in the future

A relational database stores data in multiple different tables. These are linked together using relationships created by primary and foreign keys.

Benefits:

  • There is less duplication of information so less redundant data which can reduce the file size
  • The database is easier to manage and easier to expand in the future
  • Searching the database may be faster as there is less data / not all tables need to be searched
  • It can be more secure as different tables means different people could have different access

Drawbacks:

  • They are generally more difficult to set up and need more technical knowledge to do things like normalisation.
  • Data is spread out between multiple tables. Some tables may be link tables which only display key fields / references
  • They are more expensive as you will need a DBMS rather than just a basic file

AO2 Application

  • Given that Rosa currently has 150 members, a flat file database will be suitable
  • However, as the business expands, using a flat file database may prove problematic
  • She could end up with a lot of redundant data as each time a new booking/appointment is made, it may store repeated data.
  • The more data Rosa has, the more likely there could be inconsistencies if data is edited/updated and her data would lose integrity
  • A relational database would be more efficient.
  • Membership details can be stored once and then their membership ID number can then be used to make a booking
  • A relational database will also allow her to expand even further in the future as additional tables can be added. However, the database will need to be normalised to ensure data is consistent, in the same format and that dependencies are reduced

AO3 Evaluation
The most suitable database for Rosa would be a relational database. Although this is more complex and the data needs to be normalised, it will store less repeated data when her company expands. It will also allow the database to be easily maintained and expanded in the future and will be easier to keep data secure, which is important.

Examiner’s Comments

There were some really good responses to this question. More successful candidates were able to explain the features of both types of database as well as giving relevant benefits and drawbacks of each which they then applied to the scenario well and gave a recommendation. Some candidates gave good descriptions of the features of each type but were unable to apply their knowledge to

the scenario other than saying the membership was going to increase. There were a number of candidates who confused a database with a table stating that a relational database was a number of related databases and not a number of related tables.

[An examiner's marking symbol, not shown here.]

Misconception

The main misconception was to confuse database with table although some candidates also confused the different keys used in a relational database and what they were used for.

Exemplar 3

[Image redrawn from the original mark scheme by an AI model (Gemma 4 12B). It may contain mistakes.]

A candidate's handwritten answer:

A flat file database is a type of database that only contains one table. This means that all data is stored in "tables", headed by "headers" and fields. It doesn't require a primary key or to be normalised. A relational database uses multiple tables linked together using foreign keys, which could create a many relationship between different tables in the database.

A flat file database has many advantages. Firstly, it is relatively simple & easy to understand. This is because all records are stored in tables, so all the information is in one place. However, there are drawbacks. If it is a large number - e.g. both multiple cities and approximately, extra records would be wanted. This would lead to a large amount of redundant data being included such as a number [unclear] appearing twice. This would increase the amount of space the database takes up. In other words, it would be difficult to search the database.

A relational database also has many benefits. When a relational database has been normalised, it becomes very efficient & search and easier to use. It will do so well as through the process of normalisation, large amounts of redundant data would be deleted, reducing the [unclear] space "saved" for

[unclear] the database. For example, by storing "cities" for approximately [unclear] number for "country", and "cities" for "country details", the membership details [unclear] could be written and again [unclear] for approximately [unclear] "country", saving [unclear] space.

Overall, a relational database would be much more suitable for large databases. A flat file database is [unclear] for [unclear] e.g. with 100 records, if the first 10,000 records in the [unclear] online [unclear] will be [unclear] difficult to locate specific records when searching, as there would be far too much redundant data, [unclear], and the file size would be [unclear] large if extra details are included each time an appointment is made. However, if [unclear] want to use a relational database, there would be much less redundant data, [unclear] [unclear] for the file size and [unclear] [unclear] for each record, as [unclear] [unclear] [unclear] will be used in a relational database. [unclear] [unclear] [unclear] [unclear] will be used in a relational database. [unclear] [unclear] [unclear] [unclear] [unclear] [unclear] [unclear] [unclear] [unclear] [unclear] [unclear] [unclear] [unclear] [unclear] [unclear] [unclear] [unclear] [unclear] [unclear] [unclear] [unclear] [unclear] [unclear] [unclear] [unclear] [unclear] [unclear] [unclear] [unclear] [unclear] [unclear] [unclear] [unclear] [unclear] [unclear] [unclear] [unclear] [unclear] [unclear] [unclear] [unclear] [unclear] [unclear] [unclear] [unclear] [unclear] [unclear] [unclear] [unclear] [unclear] [unclear] [unclear] [unclear] [unclear] [unclear] [unclear] [unclear] [unclear] [unclear] [unclear] [unclear] [unclear] [unclear] [unclear] [unclear] [unclear] [unclear] [unclear] [unclear] [unclear] [unclear] [unclear] [unclear] [unclear] [unclear] [unclear] [unclear] [unclear] [unclear] [unclear] [unclear] [unclear] [unclear] [unclear] [unclear] [unclear] [unclear] [unclear] [unclear] [unclear] [unclear] [unclear] [unclear] [unclear] [unclear] [unclear] [unclear] [unclear] [unclear] [unclear] [unclear] [unclear] [unclear] [unclear] [unclear] [unclear] [unclear] [unclear] [unclear] [unclear] [unclear] [unclear] [unclear] [unclear] [unclear] [unclear] [unclear] [unclear] [unclear] [unclear] [unclear] [unclear] [unclear] [unclear] [unclear] [unclear] [unclear] [unclear] [unclear] [unclear] [unclear] [unclear] [unclear] [unclear] [unclear] [unclear] [unclear] [unclear] [unclear] [unclear] [unclear] [

The candidate has shown good application to the scenario as well as benefits and drawbacks of both database types. They have made a valid
recommendation which is justified.

out of 9

Question 2

8 marks

2(a)

State two benefits of using a relational database instead of a flat file database.

1

2

Mark scheme 2(a)

Tick each point your answer makes. Your mark is worked out from your ticks, and you can change it.

  • Relational data allows for less redundancy of data/less repeated data

  • Relational databases improve the consistency of data

  • Relational databases allow for complex queries and/or searches to be performed

Guidance

Examiner’s Comments

This question was generally answered well, although

some candidates did not use the correct terminology and therefore could not be given all the available marks.

out of 2

2(b)

OCRConfectionery is a sweet manufacturing company.

They want to use a relational database to store details of the orders their customers make.

One customer can order as many different products as they like in the same order. A customer can also place as many orders as they like.

One product can be ordered multiple times in the same order or ordered by multiple different customers.

Complete the entity relationship diagram to show the relationships between the Product, Customer and Order entities.

[Diagram redrawn from the original paper by an AI model (Gemma 4 12B). It may contain mistakes.]

erDiagram
    Customer
    Product
    Order
Mark scheme 2(b)

Tick each point your answer makes. Your mark is worked out from your ticks, and you can change it.

1 mark for each bullet point to max 2 marks:

  • One customer to many orders

  • Many orders to many products / One order to many orderLines and many orderLines to one product

Solution 1:

[Image redrawn from the original mark scheme by an AI model (Gemma 4 12B). It may contain mistakes.]

erDiagram
    Customer ||--o{ Order : " "
    Order }o--|| Product : " "

Solution 2:

[Image redrawn from the original mark scheme by an AI model (Gemma 4 12B). It may contain mistakes.]

erDiagram
    Customer ||--o{ Order : " "
    Order ||--|{ OrderLine : " "
    OrderLine }|--|{ Product : " "

Guidance

Ignore any relationship between customer and product.

Accept any suitable alternative name for the OrderLine entity.

Additional guidance:
For MP2, candidates may have avoided the use of many-to-many relationships, due to the information in part c). Therefore, allow one product to many orders.

Examiner’s Comments

This question required candidates to correctly draw the relationships between the entities as explained in the question.

Candidates were expected to indicate the relationships. Some candidates used either words or other symbols to indicate the relationships, therefore not achieving either of the available marks.

Some candidates used a link entity to avoid a many to many relationship between product and order or indicated a relationship of one product to many orders. Either of these responses were acceptable.

[An examiner's marking symbol, not shown here.]

Assessment for learning

Appendices 5c in the specification shows the symbols used for entities and their relationships. Students need to be taught these to use in the exam.

out of 2

2(c)

The order table has these fields.

OrderID
OrderDate
OrderAmount
CustomerID
ProductID

2(c)(i)

State the difference between a primary key and a foreign key.

Mark scheme 2(c)(i)

Tick each point your answer makes. Your mark is worked out from your ticks, and you can change it.

  • A primary key will only appear once in a table/is a unique identifier

  • A foreign key may appear multiple times a table/may not be unique

Guidance

Accept entity for table

Examiner’s Comments

Many candidates were able to state what a primary key is, but did not state the difference between this and a foreign key and therefore did not achieve both marks.

out of 2

2(c)(ii)

State one foreign key in the order table.

Mark scheme 2(c)(ii)

  • CustomerID

  • ProductID

Guidance

Correct answer only

Examiner’s Comments

This question was generally answered well, although some candidates lost marks for mis-spelling the foreign key or adding spaces, both of which are not permitted.

2(c)(iii)

State why CustomerID would not make a suitable primary key in the order table.

Mark scheme 2(c)(iii)

Tick each point your answer makes. Your mark is worked out from your ticks, and you can change it.

There may be duplicate values.

Guidance

Examiner’s Comments

This question was generally answered well with many candidates gaining the mark.

out of 1

Question 3

6 marks

3

Details of all users that have accessed a 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.
UsernameUserTypeDateAccessed
Mrphy003User08/05/21, 07/06/21, 08/06/21
Lwis076Admin17/04/21, 19/07/21
Bbby412NotNeeded01/06/21, 02/07/21, 14/07/21
TblAccessLog

3(i)

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

Mark scheme 3(i)

Tick each point your answer makes. Your mark is worked out from your ticks, and you can change it.

  • DELETE FROM TblAccessLog

  • WHERE UserType = "NotNeeded"

Guidance

Do not accept DELETE * or inclusion of field names

Need quotation on MP2

For field and table names, case must match - only penalise once and FT

Do not award MP2 if == is used instead of =

Examiner’s Comments

Many candidates gained the mark for the WHERE statement but less gained a mark for the DELETE statement.

out of 2

3(ii)

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

1

2

Mark scheme 3(ii)

Tick each point your answer makes. Your mark is worked out from your ticks, and you can change it.

  • Each attribute name is unique

  • Primary key identified

  • No repeated attributes

  • All data in attributes must be atomic (cannot befurther split up) / by example

Guidance

Do not accept repeated data / data redundancy (higher than 1NF) unless specified that this is within one field

Allow fields/properties as alternative to attribute

Examiner’s Comments

There were some excellent responses to this question and most candidates were able to gain at least one mark.

out of 2

3(iii)

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

Mark scheme 3(iii)

Tick each point your answer makes. Your mark is worked out from your ticks, and you can change it.

  • DateAccessed…

  • …has non-atomic data / data can be split up (into separate dates)

Guidance

Examiner’s Comments

Very well answered and most candidates could identify the DateAccessed field as being where the problem lay.

out of 2

Question 4

22 marks

4(a)

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

4(a)(i)

Describe what is meant by a transaction being durable.

Mark scheme 4(a)(i)

Tick each point your answer makes. Your mark is worked out from your ticks, and you can change it.

  • (Committed) data/transaction is not lost…

  • …in case of power / system failure

out of 2

4(a)(ii)

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

Mark scheme 4(a)(ii)

Tick each point your answer makes. Your mark is worked out from your ticks, and you can change it.

  • Completed transactions stored in secondary storage / data not stored long-term in RAM/cache

out of 1

4(a)(iii)

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

Mark scheme 4(a)(iii)

Tick each point your answer makes. Your mark is worked out from your ticks, and you can change it.

  • The outcome of concurrent transactions is the same as if transactions were completed sequentially.

  • Record locking allows one user/process to access/modify record level data at any one time

  • So data that is being used elsewhere cannot be modified / data that is being modified elsewhere cannot be used

Guidance

Allow reference to lost updates/dirty reads/phantom reads for BP3.

Examiner’s Comments
For candidates with a good understanding of ACID, these questions were well answered. Unfortunately, some had only a vague knowledge or confused it with referential integrity. Some answers were unclear. Some candidates talked about locking the entire database when record locking rather than just the relevant records.

out of 3

4(a)(iv)

Give one disadvantage of using record locking.

Mark scheme 4(a)(iv)

Tick each point your answer makes. Your mark is worked out from your ticks, and you can change it.

  • Can cause delays (as users wait for access)

  • Can cause deadlock

Guidance

Examiner’s Comments
Many candidates were given a mark for deadlock or longer wait times. Those candidates given a mark in Question 2 (d) (iv) tended to be those who has gained marks in Question 2 (d) (iii).

out of 1

4(b)

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.

[Diagram redrawn from the original paper by an AI model (Gemma 4 12B). It may contain mistakes.]

UsernameFirstNameStartDatePackageType
User001Amaya08/05/2016Premium
User002Amit06/06/2019Basic
User003Tom17/08/2019Free
User004Kareem08/08/2017Basic
User005Sarah25/03/2020Premium

Membership

PackageTypeCostPerMonth(£)Adverts
Premium12.99false
Basic7.99true
Free0.00true

Package

Fig. 2

4(b)(i)

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

Mark scheme 4(b)(i)

Tick each point your answer makes. Your mark is worked out from your ticks, and you can change it.

  • Field that is unique/does not repeat

out of 1

4(b)(ii)

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

Foreign Key

Table Name

Mark scheme 4(b)(ii)

  • Foreign Key: PackageType

  • Table Name: Membership

Guidance

Must be spelled correctly

Examiner’s Comments
Many candidates gained 1 mark for the foreign key and most gained both marks, although some candidates gave ‘package’ as the table where it is a primary key rather than the membership table where it is the foreign key.

4(b)(iii)

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

Mark scheme 4(b)(iii)

  • Float / Floating Point / Real

Guidance

Allow currency/double/single/decimal

4(b)(iv)

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

Mark scheme 4(b)(iv)

  • Adverts

Guidance

CAO

4(c)

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.

Mark scheme 4(c)

Tick each point your answer makes. Your mark is worked out from your ticks, and you can change it.

  • Username and FirstName fields (and no others) selected correctly using SELECT keyword

  • Membership / both tables correctly selected using FROM keyword

  • Tables joined using correct JOIN / INNER JOIN keywords / Tables joined using correct WHERE clause

  • Fields use table identifiers before them

  • WHERE clause used to correctly show only records where Adverts

= true

Guidance

For full marks, a fully correct working answer must be provided. Candidates can join tables in either of two valid ways (using JOIN or WHERE). Note that JOIN is given in the specification but INNER JOIN is also equally acceptable.

BP1 is the same for either method
For BP2, candidates can either choose just the Membership table or both the Membership and Packagetable
BP3 credited for correct JOIN / INNER JOIN or correct use of WHERE clause to join tables. Do not credit if FROM clause incorrect for this method
BP4 credited if candidates have used table identifiers before the field name (i.e they have used
Membership.PackageType and not just

PackageType)

BP5 will require use of AND if WHERE is used to join tables.

Spellings of all field names, table names and keywords must be accurate but only penalise once.

Example one using JOIN keyword

SELECT Username, Firstname
FROM Membership
JOIN Package on
Membership.PackageType=Package.PackageType
WHERE Adverts = true

Example two using WHERE clause

SELECT Username, Firstname
FROM Membership, Package
WHERE Membership.PackageType =
Package.PackageType
AND Adverts = true

Examiner’s Comments
Many candidates were able to gain some marks. The question refers to the Adverts field which is in the package table and states that the data shown in the tables is only an extract from the tables. For full marks on this question, candidates were expected to attempt to

join the two tables to access the Username and Firstname from the membership table, and the Adverts from the package table.

out of 5

4(d)

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.

4(d)(i)

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

Method

Suitability

Mark scheme 4(d)(i)

Tick each point your answer makes. Your mark is worked out from your ticks, and you can change it.

  • Form / web form

  • Can use validation to check for common errors…

  • Can check for duplicate values

  • Data can be entered direct into the database / limited manual processing

  • Can be done from remote locations

Guidance

One mark maximum for identification of method Two marks for discussion of suitability

Accept other valid methods of capturing data. Do NOT accept OCR/OMR/barcodes/QR codes

If the method is incorrect, don’t read on

Examiner’s Comments
Few candidates were able to gain full marks on this question as many overcomplicated it or could not give a relevant data capture method. When they did give form as a relevant answer, they often had unclear suitability.

Exemplar 2

[Image redrawn from the original mark scheme by an AI model (Gemma 4 12B). It may contain mistakes.]

A candidate's handwritten answer:

[unclear] Electronic form filled by customer.

[unclear] A electronic form can be processed automatically and details [unclear] customer details automatically added to the database. A electronic form is [unclear] accessible for the customer as it can be filled at home.

The candidate has given a valid method and has given clear and correct suitability by describing that the details could be automatically added to the database and can be filled in from home, which would be a remote location. The candidate gained the full 3 marks.

out of 3

4(d)(ii)

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.

1

2

Mark scheme 4(d)(ii)

Tick each point your answer makes. Your mark is worked out from your ticks, and you can change it.

e.g.

  • CSV

  • JSON

  • XML

  • SQL

  • APIs

  • EDI

  • RSS

  • SOAP

Guidance

Examiner’s Comments
Few candidates gained full marks on this question although there were a range of relevant responses they could have given.

out of 2

Question 5

13 marks

5

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

CustomerID

Surname

Title

Phone

CarReg

JJ178

James

Mr

(0121) 343223

DY51 KKY

HG876

Habbick

Miss

(01782)
659234

PG62 CRG

EV343

Elise

Mrs

(07834)
123998

HN59 GFR

PG127

Pleston

Mr

(07432)
234543

JB67 DSF

5(i)

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

Mark scheme 5(i)

Tick each point your answer makes. Your mark is worked out from your ticks, and you can change it.

  • Field with a unique value

  • CustomerID

out of 2

5(ii)

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

Mark scheme 5(ii)

Tick each point your answer makes. Your mark is worked out from your ticks, and you can change it.

  • SELECT CustomerID, Surname

  • FROM Customer

  • WHERE Title="Miss"

  • OR Title = "Mrs"

Guidance

1 mark per bullet point.
Data in fields must be in speech marks/apostrophes Allow speech mark/apostrophe FT for BP 2 and 4 Ignore colons/semicolons

out of 4

5(iii)

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.

Mark scheme 5(iii)

Tick each point your answer makes. Your mark is worked out from your ticks, and you can change it.

  • Only one customer entry allowed (because of key field)

  • …so would not be able to add second entry

  • Customer data already present/would be repeated

  • ….resulting in redundant data/wasted space

  • …resulting in inconsistencies should changes be made

Guidance

Mark in pairs.

out of 2

5(iv)

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

Mark scheme 5(iv)

Tick each point your answer makes. Your mark is worked out from your ticks, and you can change it.

  • Add in second table

  • …for the cars / splitting up cars/customers

  • The primary key of customer is used as a field in the car/vehicle table

  • … as a foreign key of cars

  • Create one to many relationship. Accept many to one.

out of 5

Question 6

30 marks

6(a)

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.

6(a)(i)

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

Mark scheme 6(a)(i)

Tick each point your answer makes. Your mark is worked out from your ticks, and you can change it.

  • Client computers connect to server

  • Server provides access to a resource/service

  • In this case hotel staff use client computers to connect to database on server (or other sensible example).

out of 3

6(a)(ii)

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

1

2

Mark scheme 6(a)(ii)

Tick each point your answer makes. Your mark is worked out from your ticks, and you can change it.

e.g.

  • only one point of failure

  • easier to manage users/access

  • Easier to backup

  • Easier to keep data secure.

  • Technicians can more easily remotely install / monitor.

out of 2

6(b)

The hotel’s network uses multiple switches.

Explain the purpose of a network switch.

Mark scheme 6(b)

Tick each point your answer makes. Your mark is worked out from your ticks, and you can change it.

  • Joins computers/devices together on a LAN

  • Receives packets/data

  • Recipient’s address is given in packet header/it uses the mac address

  • Send packets/data

  • Out the correct port /to the specific computer device

out of 3

6(c)

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

Mark scheme 6(c)

Tick each point your answer makes. Your mark is worked out from your ticks, and you can change it.

Mark Band 3–High Level (7-9 marks)
The candidate demonstrates a thorough knowledge and understanding of network security. The material is generally accurate and detailed.

The candidate is able to apply their knowledge and
understanding directly and consistently to the context provided. Evidence/examples will be explicitly relevant to the explanation.

The candidate provides a thorough discussion which is well balanced. Evaluative comments are consistently relevant and well-considered.

There is a well-developed line of reasoning which is clear and logically structured. The information presented is relevant and substantiated.

Mark Band 2-Mid Level (4-6 marks)

The candidate demonstrates reasonable knowledge and understanding of network security; the material is generally accurate but at times underdeveloped.

The candidate is able to apply their knowledge and
understanding directly to the context provided although one or two opportunities are missed. Evidence/examples are for the most part implicitly relevant to the explanation.

The candidate provides a sound discussion, the majority of which is focused. Evaluative comments are for the most part appropriate, although one or two opportunities for development are missed.

There is a line of reasoning presented with some structure. The information presented is in the most part relevant and supported by some evidence.

Mark Band 1-Low Level (1-3 marks)
The candidate demonstrates a basic knowledge of network security; the material is basic and contains some
inaccuracies. The candidate makes a limited attempt to apply acquired knowledge and understanding to the context provided.
The candidate provides a limited discussion which is narrow in focus. Judgments if made are weak and unsubstantiated. The information is basic and communicated in an
unstructured way. The information is supported by limited evidence and the relationship to the evidence may

not be clear.

0 marks
No attempt to answer the question or response is not worthy of credit.

Guidance

AO1
Malware and viruses are software that can have a negative impact on computer systems
Spyware and keyloggers can record information entered and send back to a third party
Phishing attacks attempt to steal data by fraudulently appearing as legitimate emails asking for secure information
Denial of Service Attacks can overload a computer system with traffic and effectively disable access for legitimate users

AO2
Hotel’s systems could be disrupted by DDOS attacks so no external bookings able to be made.
Phishing and spyware attacks may compromise visitor security and result in financial loss
Malware, viruses could destroy hotel data
Theft of customer data would be an issue under Data Protection Act / GDPR for which the hotel could be prosecuted

AO3
Education for staff and customers is important to deal with recognising and dealing with threats
Up to date software, limitations of use of devices such as USB sticks and restricted access to wireless networks can all limit risks.
Use of Firewall to restrict traffic entering and leaving the network.

Should be balanced against customer experience; will customers return if they have no access to It facilities?

out of 9

6(d)

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.

6(d)(i)

Draw an Entity Relationship Diagram for this database.

Mark scheme 6(d)(i)

Tick each point your answer makes. Your mark is worked out from your ticks, and you can change it.

  • Customer, Room and Booking entities, must be singular

  • Customer joined to Booking and Room joined to booking and no other links

  • Customer to Booking relationship indicated as one- many -Room to Booking relationship indicated as one- many

Guidance

[Image redrawn from the original mark scheme by an AI model (Gemma 4 12B). It may contain mistakes.]

erDiagram
    Customer ||--o{ Booking : ""
    Room ||--o{ Booking : ""
out of 4

6(d)(ii)

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.

Definition

Example

Mark scheme 6(d)(ii)

Tick each point your answer makes. Your mark is worked out from your ticks, and you can change it.

  • A field that links to a (primary) key in a second table

  • Example : Customer ID / RoomID…

  • … in Booking table

out of 3

6(d)(iii)

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

1

2

Mark scheme 6(d)(iii)

Tick each point your answer makes. Your mark is worked out from your ticks, and you can change it.

  • Hashing for security

  • …e.g. hash passwords in database

  • …to make sure they cannot be read if they are stolen

  • Hashing for direct access

  • …e.g. Customer/Room/Booking records can be quickly accessed

  • …by using hash of index as address

out of 4

6(e)

The hotel booking database enforces referential integrity.

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

END OF QUESTION PAPER

Mark scheme 6(e)

Tick each point your answer makes. Your mark is worked out from your ticks, and you can change it.

  • Database/relationships are consistent / each foreign key links to an existing/valid primary key

  • Suitable example of being broken (e.g. if primary key is deleted/updated, foreign keys are no longer valid /

changes should be cascaded)

Guidance

Accept example that is not related to the database given (as this is an AO1 question)

out of 2