Chapter 8 - Databases

Question 1

What are the limitations of using a file based approach for the storage and retrieval of data?

Answer 1

• data redundancy as the same data is stored in multiple files, leading to duplication
• data inconsistency when one file is updated but other aren’t, leading to unreliable data; additionally it is difficult to update data since changes must be made in multiple areas, increasing time and risk of error
• lack of data integrity since it’s harder to ensure data is consistent and accurate over multiple files
• poor security as there is limited control over who can access and modify different files
• limited scalability as it is not suitable for large volumes of data or complex data relationships
• no central control and bad data management since each application manages its own data
• hard to manage relationships as cannot easily link related data across files

Question 2

Describe the features of a relational database that address the limitations of a file based approach

Answer 2

• organises data into multiple tables instead of a single table
• uses keys to connect related data which reduces data redundancy, makes efficient use of storage, and is easier to maintain

Question 3

entity

Answer 3

a real world object or concept that data is stored about

Question 4

table

Answer 4

a collection of data about an entity, organised in rows and columns

Question 5

record/tuple

Answer 5

a single row in a table representing one instance of an entity

Question 6

field/attribute

Answer 6

a single column in a table, storing one piece of data about the entity

Question 7

primary key

Answer 7

a unique identifier for each record in a table

Question 8

candidate key

Answer 8

a field or a combination of fields that could be used as a primary key

Question 9

secondary key

Answer 9

a key used for searching and sorting which is not necessarily unique

Question 10

foreign key

Answer 10

a filed that links to a primary key in another table to create relationships

Question 11

relationships

Answer 11

a logical connection between two tables/entities

Question 12

one-to-one

Answer 12

one record in a table relates to one record in another table

Question 13

one-to-many

Answer 13

one record in a table relates to many records in another table

Question 14

many-to-many

Answer 14

records in one table relate to multiple records in another

Question 15

referential integrity

Answer 15

ensures foreign keys match a primary key in the related table to prevent broken links

Question 16

indexing

Answer 16

a technique to speed up searching in a table by creating an ordered list of key fields

Question 17

first normal form

Answer 17

• contains atomic values
• no repeating groups
• has unique column names
• has a primary key

Question 18

second normal form

Answer 18

• fulfil all 1nf requirements
• have a compound primary key
• have full functional dependency (all non-prime attributes must be fully dependent on the primary key)
• have no partial dependencies (separate tables should be created for partially dependent attributes)

Question 19

third normal form

Answer 19

• fulfil all 2nf requirements
• have no transitive dependencies (non prime attributes must not rely on other non-prime attributes)
• have separate tables for attributes with transitive dependencies which are linked with a foreign key

Question 20

what is a DBMS?

Answer 20

software that is used to create, manage, and interact with databases

Question 21

describe the features of a DBMS that address the limitations of a file-based approach

Answer 21

• eliminates data redundancy by centralising data
• ensures data consistency by updating a single source of truth
• has good data management by utilising a data dictionary that defines all data elements across the system (field name, data type, table name)
• uses data modelling tools to define entities and relationships
• uses logical schema to show how data is organised and is used independently of how it is physically stored
• high data security by utilising user accounts, access rights, and permissions
• has automated backup and recovery tools to reduce the risk of data loss

Question 22

what is the purpose of the developer interface DBMS tool?

Answer 22

• allows developers to write database queries using SQL
• more flexible than using query-by-example tools
• allows the developer to create more complex and precise queries to search, update, or manage data

Question 23

what is the purpose of the query processor DBMS tool?

Answer 23

• responsible for handling and executing SQL queries
• consists of:
• DDL interpreter: interprets DDL commands and updates data dictionary
• DML compiler: compiles DML statements into low level instructions and optimises the query
• query evaluation engine: executes the compiled instructions to retrieve or manipulate the actual data in the database