1.3.2 Databases

Question 1

What is a Database

Answer 1

• An organised collection of data
• Databases are permanent, organised structures that hold vast amounts of data

Question 2

Organising data into a database allows for easy:

Answer 2

• Adding
• Modification
• Deletion
• Searching

Question 3

What are the benefits of using electronic databases

Answer 3

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

Question 4

What are the different ways of capturing data

Answer 4

• Optical Character Recognition (OCR)
• Optical Mark Recognition (OMR)
• Barcodes and QR codes.

Question 5

What is Optical Character Recognition

Answer 5

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

Question 6

What is Optical Mark Recognition (OMR)

Answer 6

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

Question 7

1. What is an entity/file
2. What is a row/tuple
3. What is a column/attribute

Answer 7

1. The data stored in the table
2. The table contains records
3. The table contains fields.

Question 8

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

Answer 8

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

Question 9

How do we solve the issues with flat file database

Answer 9

• We need to structure the data differently by creating a relational database.
• Split the information into multiple tables.

Question 10

What is a Relational Database

Answer 10

• Made of smaller tables linked together using primary and foreign keys.
• This will remove redundancy but can be complex to create and maintain

Question 11

What is data redundancy

Answer 11

• Simply means when lots of data is replicated
• This increases the amount of storage space required

Question 12

Exam Question: Describe what is meant by a Primary Key

Answer 12

• 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

Question 13

What is a foreign key

Answer 13

• 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

Question 14

What is a secondary key

Answer 14

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

Question 15

What is Indexing

Answer 15

• 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

Question 16

What is Normalisation

Answer 16

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

Question 17

What are the 3 different types of database normalisation

Answer 17

• First Normal Form (1NF)
• Second Normal Form (2NF)
• Third Normal Form (3NF)

Question 18

What are the different types of relationships

Answer 18

• 1:1 (one:one)
• 1:M (one to many)
• M:M (Many to many)
• We can use entity relationship diagrams.

Question 19

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

Describe why film title would make a suitable secondary key

Answer 19

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.

Question 20

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

Answer 20

• 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

Question 21

What do we use to Select data in a Database

Answer 21

• Structured Query Language
• Fast and efficient retrieval, deletion and manipulation of data using commands: SELECT, FROM, WHERE

Question 22

How do you manage data in a database

Answer 22

• 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

Question 23

What criteria do you need to consider when exchanging data

Answer 23

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

Question 24

Name some manual ways of exchanging data

Answer 24

• Memory stick, Optical media, Removable Hard Disk, Email

Question 25

What is a live connection and name an example

Answer 25

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

Question 26

What are the 5 rules to get first normal form

Answer 26

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

Question 27

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

Answer 27

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

Question 28

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

Answer 28

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

Question 29

How do you fix many:many relationships

Answer 29

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.

Question 30

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

Answer 30

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)

Question 31

What is a non-key dependency

Answer 31

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

Question 32

What is a query

Answer 32

* A database search is a query * A query may return one or more fields from 1 or more tables

Question 33

What is SQL

Answer 33

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

Question 34

What does the full scope of SQL allow us to do

Answer 34

1. Query data 2. Manipulate data 3. Define data 4. Control data access

Question 35

What does the SELECT command do in SQL

Answer 35

* 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%"

Question 36

What does the INSERT command do in SQL

Answer 36

* 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")

Question 37

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

Answer 37

1. Deleting data from a database * E.G) DELETE FROM Students * WHERE Studentname = "Joseph" 2) DROP

Question 38

What does the UPDATE Command do in SQL

Answer 38

* Used to edit or modify data in a database * E.G UPDATE Students * SET Studentname = "Joseph" * WHERE StudentID = "ST6"

Question 39

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

Answer 39

SELECT Student.Surname, Student.Forename, Tutors.Teacher, Tutors.Room FROM Students JOIN Tutor Group ON Students.TutorGroup = Tutors.TutorGroup WHERE Students.Entry = "E"

Question 40

What is a multi-user database

Answer 40

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

Question 41

What is Data Integrity

Answer 41

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

Question 42

What is referential integrity

Answer 42

* 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

Question 43

What is record locking

Answer 43

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

Question 44

Name 1 way to maintain referential integrity

Answer 44

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

Question 45

What is Transaction Processing

Answer 45

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

Question 46

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

Answer 46

* Create - INSERT/CREATE * Read - SELECT * Update - UPDATE * Delete - DELETE.

Question 47

What are the ACID rules and what do they do

Answer 47

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

Question 48

What is Atomicity

Answer 48

* 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

Question 49

What is Consistency

Answer 49

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

Question 50

What is Isolation

Answer 50

* 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

Question 51

What is Durability

Answer 51

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

Question 52

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.

Answer 52

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

Question 53

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

Answer 53

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

Question 54

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

Answer 54

* 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

Question 55

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

Answer 55

* 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

Question 56

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

Answer 56

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