Option A - Databases Flashcards
What is a database?
A database is a system that allows users to store data in a structured way using tables and fields.
It also provides _ means for us to _ the data.
various,access
Data vs Information
Data:1. Raw facts2. Unprocessed3. No context4. Just text and numbersInformation:1. Data+context2. Processed and structured3. Interpreted by the user4. Data is given meaning
Database vs spreadsheet
Spreadsheets are used to manipulate data(using mathematical operations) while databases are used to store data. Databases also have relationships betwen tables and allow the user to make queries to find specific data
Database vs informtion system
Databases contain data that is used by information systems. Informations systems are complete systems that present data as information.
Why are databases needed?
- Data organisation* Data integrity* Data security* Scalability* Perfomance* Data sharing
Elaborate on data organisation.| As part of the question: Why are databases needed?
Help organise data in a structured manner, Allow us to store, retrieve and manipulate data
Elaborate on data integrity.| As part of the question: Why are databases needed?
- provide mechanisms to ensure data is accurate and consistent * Needed when many users/applications access the same data
Elaborate on data security.| As part of the question: Why are databases needed?
- Allow administrators(authorized) to control and access data* this protects sensetive data from unauthorized users
Elaborate on scalabilitity.| As part of the question: Why are databases needed?
can be scaled by organisations who need to handle large amounts of data
Elaborate on perfomance.| As part of the question: Why are databases needed?
- optimized, which allows users to access and manipulate data quickly and efficiently
Elaborate on data sharing.| As part of the question: Why are databases needed?
- they enable sharing which allows collaborations across different systems and applications
What are database transactions?
Logical unit of work consisting of one or many operations that are executed together as a single atomic unit.
Transactions ensure that either _ operations are _ or _ are _ at all.
all,completed,none,executed
Transactions ensure that a _ remains in a _ _ even in the event of _ or _ _.
database,consistent,state,errors,system,failures
Database states refer to the _ of a database at any given _.
condition,time.
States are used to keep track of _ made to the database.
changes
Maintaining _ across _ maintains _.
consistancy,states,integrity.
What are database updates?
updates refer to changes made to the database.
_ operations maintain _ and integrity.
update, consistency
Why are locking mechanisms necessary?
They prevent conflict and inconsistencies.
_ transactions are conducted _.
concurrent,sequentially
Using _ created at _ allows changes to be _
logs,commit,reverted
What are the ACID properties of a database transaction?
Atomicity, consistency,isolation,durability
Explain atomacity.| As part of : What are the ACID properties of a database transactions?
- Atomic: all transactions are executed sucessfully or none are executed at all.* It ensures consistency
Explain consistency.| As part of : What are the ACID properties of a database transactions?
- Ensures a consistent state of the database is maintained before and after a transaction is executed
Explain isolation.| As part of : What are the ACID properties of a database transactions?
- transactions are isolated(changes made by one transaction are not visible to the other until completion)
Explain durability.| As part of : What are the ACID properties of a database transactions?
- Once changes are made they are permanant despite errors or system failure
Define concurrency.
The ability of users to access data simultaneously.
Define concurrency control.
The process of managing concurrent access to maintain consistency and integrity. Involves implementing mechanisms to prevent conflict.
What are the techniques for managing concurrency?
Locking, time-stamping, MVCC
What do locking mechanisms ensure?| As part of: What are the techniques for managing concurrency?
Only one user can make a change at a time. They do this by placing a lock which can not be acessed or modified until it is released.
What is time stamping?| As part of: What are the techniques for managing concurrency?
Assigning a unique timestamp to each transaction. If simultaneous attempts are made to access data, time stamps are used to determine priority.
What is MVCC?
Creating multiple versions of data or a record to allow users/applications to modify simultaneously. Users/applications see a version of the data that reflects the state of the database at the time.| MVCC-multi-version concurrency control.
What are the functions databases need to be perfomed on them?
Query functions and update functions
What are query functions?| As part of: functions databases need to be perfomed on databases
- Used to retrieve specific data or a subset of data which meets a specific criteria* SELECT in SQL and FIND in NoSQL* Allow analysis and reporting
What are update functions?| As part of: functions databases need to be perfomed on them
- Used to modify data in the database* UPDATE and DELETE in SQL and save() and remove() in NoSQL* Essential for maintaining accuracy and integrity of data - allow users to correct errors, update data and remove obsolete data
What is data validation?
Checking whether data entered into a system matches predefined rules and constraints. Ensures that the data entered is correct.
What is data verification?
Process of checking whether data in the database is accurate, complete and consistent. Ensures that data is a true representation of its original source.
Data verification is perfomed on a _ basis.
periodic
Tools for data verification include _ checks, _ tools or a combination of both.
manual, automated
What is a DBMS?
A DBMS is used to store, manage and retrieve data in a structured and organised manner.
What is the purpose of a DBMS?
Provide a centralised, controlled and efficient environment for managing data. This enables organisations to access large amounts of data in a consistent and organised way.
What are the benefits of using a DBMS?
- Data organisation and management* Data security and privacy* Data consistency and integrity* Data sharing and collaboration* Data analysis and reporting* Data backup and recovery
How can a DMBS be used to promote data security?
Authentication and access control, data validation, locking, encryption, audit trail, backup and recovery,data masking(masks sensetive data with ficticious data)
Define the term schema.| Long answer
- Schema refers to the logical structure of the database
- defines organisations and relationships amoung data elements or objects
- defines the type of data that can be stored
- relationships between different types of data
- constraints or rules given
- (blueprint for the database)
What is conceptual schema?
- High level representation of the the overall logical structure of the database
- Provides an overall view of data, abstraction, storage and processing
- Defines entities attributes and relations
What is the physical schema?
- Describes how data is physically stored in the database system. This includes:* File organisation* Indexing methods* Storage structures* Access paths* Deals with low level implementation aspects: optimizing perfomance and storage efficiency
What is the logical schema?
- More detailed version of the conceptual schema* Describes how data will be represented and organised in the database. Focuses on:* Data types* Keys* Constraints* Provides the blueprints for database design
What is a data dictionary?
Collection of metadata which provides information about data in database| Also known as metadata repository or data catelog
The data dictionary serves as a _ source for database _, _, and _ . It provides a _ way to document the _ and _ of a database.
reference,administrators,developers,users,standardised,structure,content
What does a data dictionary include?
- Data elements and descriptions
- Table and relationship descriptions
- Business rules and constraints
- Data access permissions
- Database management information
Elaborate on data element descriptions.
As part of: What does a data dictionary include?
data type, length and format of elements
Define the term table.
Collection of related data in rows and columns. Used to store data in a database and named after the data they contain.
Define the term record.
A collection of data that represents a single entity in a table. Record=row.
Define the term field.
A field is a single piece of data stored in a record. It represents a specific attribute or characteristic of the record. Field=column.
Define the term primary key.
A field or combinations of fields which uniquely identify each record.
Define the term secondary key.
A field which is not the primary key but can be used to query data in the table.
Define the term candidate key.
A field which can be used as the primary key but is not currently in use for that purpose.
Define the term Composite primary key.
A primary key that consists of two or more fields in a table.
What does the candidate key ensure?
It ensures no two records are identical.
What is a composite key used for?
Used when a single key is not sufficient to uniquely identify each record.
What is the join operation?
A join operations combines multiple tables into a single table based on a related field.
What is a join operation used for?
Data analysis and reporting.
What issues can redundant data cause?
- Data inconsistency* Issues with data integrity* Storage costs * Maintainence costs* Security risks
Explain data incosistency issues.| As part of: What issues can redundant data cause?
If there are repetitions in data copies of the same data may become incosistent.
Explain data integrity issues.| As part of: What issues can redundant data cause?
Redudant data can compromise data integrity mkaing it more difficult to maintain accuracy and completeness of the data.
What is difficult to ensure when redundant data is present?| As part of: What issues can redundant data cause?
It is hard to ensure that all copies of data are updated consistently and accurately.
Explain storage space issues.| As part of: What issues can redundant data cause?
Redundant data takes up more storage in the database which increases costs and reduces perfomance
Explain maintenance cost issues.| As part of: What issues can redundant data cause?
Redundant data can increase the cost of maintaining and updating the database. More effort is required to keep all copies up to date.
Explain security risk issues.| As part of: What issues can redundant data cause?
Redundant data increases the number of attack points for malicious actors. If it is not secured it can be easily accessed and manipulated by unauthorized users.
What is referential integrity?
A concept in a normalised database that ensures that the relationship between tables are maintained and that the data is accurate and consistent across tables. It does this preventing invalid data from being entered into the database.
What are the different datatypes?
INT,FLOAT,DECIMAL,NUMERIC,CHAR,VARCHAR,NVARCHAR,DATE,TIME,DATETIME,TIMESTAMP and BLOB
How can a query provide a view of a database?
- Selecting fields: users can select fields they want to see* Filtering data: data can be filtered based on specific criteria* Sorting data: queries can sort data in a specific way* Grouping data: queries can group data based on specific criteria* Calculating data: queries can make calculations using data based on specific criteria
Compare the complexity of simple queries and complex queries.
- Simple queries only involve a single table and a small number of fields* Complex queries involve multiple tables, complex operations and advanced functions
Compare the purpose of simple queries and complex queries.
- Simple queries retrieve specific data from a database* Complex queries perform advanced data manipulation and analysis
Compare the perfomance of simple queries and complex queries.
- Simple queries are usually faster and more efficient than complex queries* Complex queries are slower and resource intensive, especially if they involve large amounts of data or complex calculations
Compare the ease of use of simple queries and complex queries.
- Simple queries are easier to understand* Complex queries require advanced technical skills and knowledge
What are the different methods to create a query?
GUIs, query languages, stored procedures, data access layers, ORM and web based interfaces.
Describe GUIs. As part of: What are the different methods to create a query?
Queries can be created using a visual interface. Users can select tables and fields, add filters and sorting criteria and build complex queries using drag and drop options
Describe query languages. As part of: What are the different methods to create a query?
provide syntax for creating queries
Describe stored procedures. As part of: What are the different methods to create a query?
Set of precompiled SQL statements that can be executed on a database. Perform specific tasks that meet specific criteria
Describe data access layers. As part of: What are the different methods to create a query?
provide a set of methods to retrieve data
Describe ORM. As part of: What are the different methods to create a query?
map tables to object oriented code
Describe web based interface. As part of: What are the different methods to create a query?
Used to create and execute queries from a browser
What are the roles of a database administrator?
Design and implmentation, maintenance and perfomance tuning, data security, user management, data modelling and architecture, monitoring and troubleshooting and training and support.
Elaborate on design and implementation.
As part of: What are the roles of a database administrator?
Responsible for designing and implementing :
* Architecture
* Physical storage
* Organisation
* logical relationships
* Security and access controls
Elaborate on data security.
As part of: What are the roles of a database administrator?
Using security measures such as encryption, access control rights to ensure security and privacy of databases.
Elaborate on user management.
As part of: What are the roles of a database administrator?
Responsible for managing user accounts and permissions ensuring appropriate access control mechanisms are in place to ensure security and privacy of data.
Elaborate on data modelling and architecture.
As part of: What are the roles of a database administrator?
Responsible for defining data models and architecture that support an organisations requirements(goals and objectives).
Elaborate on monitoring and troubleshooting.
As part of: What are the roles of a database administrator?
Responsible for monitoring databases and troubleshooting any issues that arise.
Elaborate on training and support.
As part of: What are the roles of a database administrator?
Responsible for providing training and support to other stakeholders to help them effectively understand the data and use the database.
What are the methods of data recovery?
- System log
- Deffered update
- Mirroring
Deffered updates _ _ and reduce the _ of _.
improve perfomance, risk, incosistencies
What happens in a deffered update?
Changes made to a database are not immediately written to a disk but held in a memory until commit. During commit all changes are written in a single batch. This reduces overhead risk and improves perfomance. But data may not be available for other transactions which may lead to concurrency issues.
What happens during mirroring?
The database is duplicated on a seperate server. The copy can be used to recover the database. This method provides high availablity and fast recovery times but it can be complex to maintain.
How can personal data be secured through technological methods?
- Encryption
- Access controls
- Secure data storage: Data should be stored in a secure location such as a surveillance room with cameras. Data backups should be stored offsite.
- Regular audits
How can personal data be secured through human methods?
- Employee training
- Access control
- Background checks
- Privacy notices and policies
Elaborate on employee training.
Employees should be trained on the importance of data privacy as well as company policies and procedures for protecting data.
1.
Elaborate on background checks.
Employees should undergo background checks to ensure they have a trustworthy background.
Elaborate on privacy policies and notices.
Policies should be made available to everyone. They should state the organisations privacy practices and procedures.
Elaborate on table and relationship descriptions.
As part of: What does a data dictionary include?
Helps users understand the structure and organisation of the database
Elaborate on business rules and constraints.
As part of: What does a data dictionary include?
Data validation rules, default values and other constraints
E
Elaborate on Data access permissions.
As part of: What does a data dictionary include?
Ensures data is accessed and used appropriately by users
Elaborate on database management information.
As part of: What does a data dictionary include?
the version of the software, server configuration and other details
