Databases

Outline the use of a query in a database [2]

  • A query provides a virtual representation of the database, shows the relationship between different attributes

  • A query searches/provides a filter view of the database based on the criteria set in the query.

  • A query helps to manipulate data in a database, using INSERT DELETE UPDATE statement

  • A query is used to aggregate and summarize data, using functions such as SUM() AVG() COUNT() and enabling grouping and sorting of data

  • A query can alter the schema and structure of the database, through the modification of fields and data types of relations or by creating and or altering the relationship between tables or relations

Outline why an integer is an appropriate data type for the PayGrade field [2]

  • Pay grade values are whole numbers

  • Use of an integer reduces size of field

  • Allows listing and sorting of staff in order of the PayGrades

  • Allows calculation and mathematical functions

Explain two ways in which database administrators can ensure the privacy of the hospital staff’s data [6] (method, use, application)

  • The separation of sensitive data into a separate tables ensures minimum number of people have access to sensitive data, and with only the use of unique identifiers of the staff a a reference point

  • The use of different levels of access means that the minimum number of people have access to this data, eg. password protected role table to restrict access

  • The encryption of stored data in the database (or spec columns in tables), with the process of scrambling or converting data in cipher text that can only be decoded using a key ensures only people with access to the key can access the data

  • The obfuscation, anonymisation and masking of data removes or transforms personally identifiable information in the data, for example, suppresing certain characters in staff pay grade

  • Database views can be used to limit data access by exposing only specific columns or rows from a table, for eample, a view can be created for a department manager to see the data of staff in their department only

Describe the difference between a conceptual schema and a logic schema [2]

  • Conceptual schemas are a high level, lesser detailed representation of a database and is sometimes described as the what model. It involves identification of entities and high level relationships between them

  • Logic schemas are more detailed than a conceptual schema and shows details such as field names, and is sometimes described as the how model. It is developed from the conceptual schema

Explain the importance of a data definition language in implementing a data model [2] (expl, eg)

  • A DDL is used to specify the schema of a database

  • A DDL allows you to define the tables, fields and set datatypes such as CREATE TABLE

  • A DDL allows you to speify keys and create relationships

  • A DDL allows you to modify schema sych as DROP and ALTER

Explain why data modelling is used during the development of a database [4] (expl,eg*2)

  • DM helps to identify the entities and tables in the database and if is incorrect, wont support the purpose of the db

  • The attributes of the tables should be necessary and sufficient for the purpose to avoid inefficiencies in the operation of the db

  • The keys for accessing the tables need to be identified to ensure easy user access to data

  • The relationships between tables are identified to enable the user to perform complex queries across several tables

  • The normalization during data modelling reduces data duplication which reduces anomalies and saving storage space

  • DMs enable the db structure to be understood by other stakeholders, aalysts and programmers which allows ease of db management

Explain why both data validation and data verification are required to ensure the correctness of the data within a database [3] (definition*2, link)

  • Data validation is an automated process that ensure input meets data entry rules

  • Data verification is the process of checking data to ensure input is intended

  • Both techniques provide the optimal solution, with data validation’s application to fields where values can be easily determined, then the use of data verification for the checking where data validation is not possible or appropriate

Outline how data integrity is maintained during database transactions [2]

  • Integrity is maintained by no changes being made until the transaction is complete, if the transaction cannot be completed then it is rolled back to the original state, appending to the atomicity of either all changes are made or no changes are made.

  • Consistency encures a transaction does not put the db into an invald state, with rules and integrity constraints