Brainscape's Knowledge GenomeTM

Browse over 1 million classes created by top students, professors, publishers, and experts.


See full index

A Level Comp Sci Paper 1 > 1.3.2 Databases > Flashcards

1.3.2 Databases Flashcards

1
Q

What is a Database

A
  • An organised collection of data
  • Databases are permanent, organised structures that hold vast amounts of data
How well did you know this?
1
Not at all
2
3
4
5
Perfectly
2
Q

Organising data into a database allows for easy:

A
  • Adding
  • Modification
  • Deletion
  • Searching
How well did you know this?
1
Not at all
2
3
4
5
Perfectly
3
Q

What are the benefits of using electronic databases

A
  • Easier to retrieve, add, delete, update and modify data
  • Easier to back up and make copies
  • Can be accessed by multiple people at the same time and from different locations.
How well did you know this?
1
Not at all
2
3
4
5
Perfectly
4
Q

What are the different ways of capturing data

A
  • Optical Character Recognition (OCR)
  • Optical Mark Recognition (OMR)
  • Barcodes and QR codes.
How well did you know this?
1
Not at all
2
3
4
5
Perfectly
5
Q

What is Optical Character Recognition

A
  • Converts paper-based hardcopies into editable, digital versions of the same document.
  • It reads text by interpreting the shape of the letters and puts the equivalent ASCII value in a digital file.
How well did you know this?
1
Not at all
2
3
4
5
Perfectly
6
Q

What is Optical Mark Recognition (OMR)

A
  • Used for Multiple choice tests and collecting results of surveys.
  • The user shades the box and the OMR scanner uses a light and places results in a database
  • This reduces human error.
How well did you know this?
1
Not at all
2
3
4
5
Perfectly
7
Q
  1. What is an entity/file
  2. What is a row/tuple
  3. What is a column/attribute
A
  1. The data stored in the table
  2. The table contains records
  3. The table contains fields.
How well did you know this?
1
Not at all
2
3
4
5
Perfectly
8
Q

What is a Flat file database and an example of their use

A
  • It contains only a single table
  • Simple, quick to set up and store small amounts of data
  • Can be ued for storing contact details
  • Can be inefficient as they can develop data redundancy.
How well did you know this?
1
Not at all
2
3
4
5
Perfectly
9
Q

How do we solve the issues with flat file database

A
  • We need to structure the data differently by creating a relational database.
  • Split the information into multiple tables.
How well did you know this?
1
Not at all
2
3
4
5
Perfectly
10
Q

What is a Relational Database

A
  • Made of smaller tables linked together using primary and foreign keys.
  • This will remove redundancy but can be complex to create and maintain
How well did you know this?
1
Not at all
2
3
4
5
Perfectly
11
Q

What is data redundancy

A
  • Simply means when lots of data is replicated
  • This increases the amount of storage space required
How well did you know this?
1
Not at all
2
3
4
5
Perfectly
12
Q

Exam Question: Describe what is meant by a Primary Key

A
  • A field that has a unique value / a unique identifier (1) for every record in that table (1)
  • Usually ID numbers
  • Using the primary key - can link 2 tables together
How well did you know this?
1
Not at all
2
3
4
5
Perfectly
13
Q

What is a foreign key

A
  • Is any field in a table which is the primary key in another table.
  • E.G Teacher ID is foreign in Students table but primary key in Staff table
How well did you know this?
1
Not at all
2
3
4
5
Perfectly
14
Q

What is a secondary key

A
  • Is an additional key which can be used to search for a record in a database. It doesnt need to be unique to each record.
  • Hard to remember student ID use surname instead - this is a secondary key.
How well did you know this?
1
Not at all
2
3
4
5
Perfectly
15
Q

What is Indexing

A
  • An index is a data structure used to shorten the length of time it takes to search a database
  • The index provides the position of each record according to its primary key.
  • It maintains an index of primary keys
How well did you know this?
1
Not at all
2
3
4
5
Perfectly
16
Q

What is Normalisation

A
  • Is the formal process of converting a flat-file database into a relational database.
  • Each method is labelled according to the nature of their arrangement.
How well did you know this?
1
Not at all
2
3
4
5
Perfectly
17
Q

What are the 3 different types of database normalisation

A
  • First Normal Form (1NF)
  • Second Normal Form (2NF)
  • Third Normal Form (3NF)
How well did you know this?
1
Not at all
2
3
4
5
Perfectly
18
Q

What are the different types of relationships

A
  • 1:1 (one:one)
  • 1:M (one to many)
  • M:M (Many to many)
  • We can use entity relationship diagrams.
How well did you know this?
1
Not at all
2
3
4
5
Perfectly
19
Q

Exam Question: Describe why Film Title is not a suitable Primary Key

Describe why film title would make a suitable secondary key

A
  1. A primary key must have a unique value for each record. However it is possible for 2 films to have the same name
  2. A secondary key is indexed allowing for faster searching and users are more likely to search by film.
How well did you know this?
1
Not at all
2
3
4
5
Perfectly
20
Q

What are the 4 ways of data and how to handle it

A
  • Capturing: How do we get the database in the first place?
  • Selecting: How do we query the data and retrieve it?
  • Managing: How do we manage, manipulate, add, edit and delete data?
  • Exchanging: How do we exchange the data with other people/systems
How well did you know this?
1
Not at all
2
3
4
5
Perfectly
21
Q

What do we use to Select data in a Database

A
  • Structured Query Language
  • Fast and efficient retrieval, deletion and manipulation of data using commands: SELECT, FROM, WHERE
How well did you know this?
1
Not at all
2
3
4
5
Perfectly
22
Q

How do you manage data in a database

A
  • We need to be able to modify a database once it has been set up.
  • Add new data, edit/modify data, delete data.
  • Can achieve this via a database manipulation language (DML) like SQL using commands like; UPDATE, DELETE, INSERT
How well did you know this?
1
Not at all
2
3
4
5
Perfectly
23
Q

What criteria do you need to consider when exchanging data

A
  • The common formats used for exchanging data
  • Manual methods of data exchange
  • Automatic methods of data exchange.
24
Q

Name some manual ways of exchanging data

A
  • Memory stick, Optical media, Removable Hard Disk, Email
25
Q

What is a live connection and name an example

A
  • 1 Live connection is known as Electronic Data Interchange (EDI). Exchanges data automatically Exam boards and schools.
  • EDI significantly increases the speed of data transmission and the efficiency of processes for users.
26
Q

What are the 5 rules to get first normal form

A
  1. All field names must be unique
  2. Values in fields should be from the same domain
  3. Values in fields should be atomic
  4. No 2 records can be identical
  5. Each table needs a primary key
27
Q

What are the 2 rules to convert a table from first normal form to second normal form

A
  1. The data is already in second normal form
  2. Any partial dependencies have been removed (each table should serve its own purpose)
28
Q

What is a dependency and partial dependency and how should you fix it

A
  • A dependency is a field that every other field depends on.
  • A partial dependency means 1 or more fields depend on only part of the primary key
  • This arises when a composite key is used
  • Can split the table and create a relationship between them.
29
Q

How do you fix many:many relationships

A
  1. Creating a linking table
  2. Assigning the primary keys from the 2 initial tables as the composite key for the new linking table
  3. Flipping the M:M crows - feet relationship to become 2 separate 1:M relationships joined by the new table.
30
Q

What are the 2 rules to get from second normal form to third normal form

A
  1. The data is already in 2NF
  2. Any transistive dependencies have been removed. (Another way to say this is that we need to remove any non-key dependencies)
31
Q

What is a non-key dependency

A
  • A non-key dependency is where 1 value of a field is determined by the value of another field that is not part of the primary key. (Remove repeating data)
32
Q

What is a query

A
  • A database search is a query
  • A query may return one or more fields from 1 or more tables
33
Q

What is SQL

A
  • Structured query language
  • Allows for fast and efficient retrieval, deletion and manipulation of data held in relational databases using a simple set of commands
34
Q

What does the full scope of SQL allow us to do

A
  1. Query data
  2. Manipulate data
  3. Define data
  4. Control data access
35
Q

What does the SELECT command do in SQL

A
  • Returns a specified piece of data from a database. It can return whole rows and columns or the data from a single field.
  • E.g SELECT *
  • FROM World
  • WHERE name LIKE “A%”
36
Q

What does the INSERT command do in SQL

A
  • SQL lets you add records using the INSERT Command.
  • E.G INSERT INTO Students (StudentID, StudentName, DOB, Gender)
  • VALUES (“ST9”, “Adam”, “29/01/2001”, “M”, “OX69TG”)
37
Q

What does the DELETE command do in SQL and how do you remove tables

A
  1. Deleting data from a database
  • E.G) DELETE FROM Students
  • WHERE Studentname = “Joseph”

2) DROP

38
Q

What does the UPDATE Command do in SQL

A
  • Used to edit or modify data in a database
  • E.G UPDATE Students
  • SET Studentname = “Joseph”
  • WHERE StudentID = “ST6”
39
Q

Question) Construct an SQL Statement that outputs the Student Surname, Student Forename, Teacher and Room from the Tutors table for all students in the students table who have an entry code of “E”.

A

SELECT Student.Surname, Student.Forename, Tutors.Teacher, Tutors.Room

FROM Students

JOIN Tutor Group

ON Students.TutorGroup = Tutors.TutorGroup

WHERE Students.Entry = “E”

40
Q

What is a multi-user database

A
  • Databases often need to support multiple simultaneous users.
  • Users can be given different access rights to a database:
    • Some will be able to query/run reports
    • Others will be able to add/modify records
    • A select few may be allowed to delete records.
41
Q

What is Data Integrity

A
  • The Database Management System (DBMS) ensures that the data stored in the database remains consistent
  • The process of maintaining the consistency of the database is known as data integrity
  • A key technique of ensuring data integrity in a relational database is known as referential integrity.
42
Q

What is referential integrity

A
  • Referential integrity refers to the accuracy and consistency of data within a relationship. (Ensure that links between tables remain constant)
  • Making sure each foreign key refers to an existing record and a primary key in another table.
  • Records with a foreign key that dont link back to the primary key are sometimes called orphan records
43
Q

What is record locking

A
  • Record locking is used to ensure that the data itself is correct
  • Record locking ensures that 2 people cant try and update the same piece of data at the same time and override each others changes.
44
Q

Name 1 way to maintain referential integrity

A
  • Enforce a cascade delete restraint on the primary key relationship between the tables.
  • If we delete the record from 1 table any associated rows from another table would be deleted.
45
Q

What is Transaction Processing

A
  • Means taking multiple smaller operations and grouping them together in a single transaction operation.
  • Any information processing that is divided into individual, indivisble operations called transactions.
  • Each transaction must suceed or fail as a complete unit - it can never be only partially complete.
46
Q

All relational databases have certain base functionality, referred to using the acronym CRUD:

A
  • Create - INSERT/CREATE
  • Read - SELECT
  • Update - UPDATE
  • Delete - DELETE.
47
Q

What are the ACID rules and what do they do

A
  • To ensure data integrity, transaction processing in all database management systems (DBMS) must conform to a set of rules.
    • Atomicity
    • Consistency
    • Isolation
    • Durability
48
Q

What is Atomicity

A
  • A change to a database is either completely performed or not at all. Each part of the transaction is successful or not.
  • Half competed change must not be saved back to the database
49
Q

What is Consistency

A
  • All parts of the transaction must be in line with the database rules. E.g Dont create duplicate keys.
  • Must retain the overall state of the database.
50
Q

What is Isolation

A
  • A transaction must not be interrupted by another transaction
  • Other users or processes cant access the data until completed complete.
  • The DBMS enforces record locking - leaving them in a read only state
51
Q

What is Durability

A
  • Once a change has been made to a database, it nust not be lost due to a system failure
  • Durability is achieved by making sure the DBMS writes the effects of transactions immediately back to permanent secondary storage.
52
Q

Exam Question: ) Discuss the suitability of flat files and relational databases for use by a family at home and for use in a large mail order company.

A

Flat files

  • Limited amount of data
  • Limited technical expertise available in family
  • Data format difficult to change
  • Security not a major issue for family compared with company

Relational

  • Saves space / reduces data duplication / redundant data
  • Improves data consistency / integrity
  • Easy to change data format
  • Improves security / easy to control access to data
  • Easy to add data Easy to link to other applications
53
Q

Exam Question: Explain the use of a primary key as a foreign key

A
  • Primary key in one table…
  • …used as an attribute / foreign key in another
  • Provides a link between tables
  • Represents many-one relationship
54
Q

Exam Question: Discuss how the bank can ensure accuracy and the importance of it - knowledge (9)

A
  • Atomic; They should either succeed or fail but never partially succeed.
  • Consistent: The transaction should only change the database according to the rules of the database
  • Isolated: Each transaction shouldn’t affect/overwrite other transactions concurrently being processed.
  • Durable: Once a transaction has been started it is remains no matter what happens
  • Record locking
  • Referential integrity. Changes to data in one table must take into account data in linked tables.
  • Data should have redundancy
  • Security measures
  • Data entered should be validated
55
Q

Exam Question: Discuss how the bank can ensure accuracy and the importance of it - application (9)

A
  • Ensuring the accuracy of transactions will be partly down to the DBMS and partly down to the code accessing the DBMS.
  • Referential Integrity is often enforced by the database management system.
  • Redundancy can be provided in a number of ways. This could be a RAID setup or mirroring servers.
  • Bank may use validation and verification when data is input.
  • Security procedures may include firewall, enforcement of sensible passwords and enforced user access rights.
  • Validation may include range checks
  • Verification may include double entry
56
Q

Exam Question: Explain what is meant by the term “referential integrity” and how could this be potentially broken?

A
  • 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)

A Level Comp Sci Paper 1 (16 decks)

Brainscape helps you reach your goals faster, through stronger study habits.
© 2024 Bold Learning Solutions. Terms and Conditions