Module 8.4- Database Operations & Access

Question 1

Working With a Database: Basic Operations

Answer 1

• at the core of database management are the CRUD operations, which stand for Create, Read, Update, and Delete
• these are the fundamental tasks you’ll use to interact with the data stored in a database
• Create (C)- adding new records to a database e.g. adding a new customer to a customer table
• Read (R)- querying the database to retrieve data; might search for all orders placed in the last month or look up a customer’s contact details
• Update (U)- modifying existing data in the database, such as changing a customer’s address or updating an order status
• Delete (D)- removing records from database when they are no longer needed

Question 2

Querying a Database

Answer 2

• querying is how you ask a database for information
• this is done using SQL (Structured Query Language) for relational databases
• e.g. in photos
• updating data allows you to modify existing records
• e.g. in photos
• e.g. in a sales database, if a customer changes their address, you’d perform an UPDATE to ensure their profile information stays current

Question 3

Database Location & Access

Answer 3

• databases can be hosted locally (on-premises) or in the cloud, and where the database is located affects how you access and manage it
• each setup comes with its own advantages and challenges

Question 4

Local Databases

Answer 4

• a local database is hosted on a server or machine within your organisation
• this setup gives you direct control over the database, including security, backups, and updates
• often used by small businesses or organisations that need fast access to data and have internal IT infrastructure

Question 5

Local Database Pros

Answer 5

• faster access when operating on the same local network
• more control over security and data privacy

Question 6

Local Database Cons

Answer 6

• requires internal IT resources to manage the server, perform updates, and handle backups
• limited by physical location-remote access can be slow or require a VPN

Question 7

Cloud Database

Answer 7

• hosted on a remote server, typically managed by a cloud provider such as Amazon Web Services (AWS), Microsoft Azure, or Google Cloud
• offer greater flexibility and scalability, making them a popular choice for businesses that need to access data from multiple locations or scale their operations quickly

Question 8

Cloud Database Pros

Answer 8

• accessible from anywhere with an internet connection, making it ideal for remote work or distributed teams
• scalable- resources can be increased or decreased based on demand without investing in physical infrastructure
• managed services- cloud providers often handle maintenance, backups, and updates

Question 9

Cloud Database Cons

Answer 9

• Internet connectivity is required, which can be a limitation in areas with poor internet access
• data security and compliance need to be managed carefully, especially for sensitive information
• e.g. a company with employees across different regions might choose a cloud database so that everyone can access the same data regardless of location- however, a small business with all operations in a single office might prefer a local database for faster access

Question 10

Why is Data Validation important?

Answer 10

• data validation is a critical process that ensures the data entering your database is accurate, correctly formatted, and useful
• proper data validation is essential to maintain data integrity, prevent errors, and ensure the quality of information stored in a database
• ensures that only correct, relevant, and properly formatted data is saved in the database
• if invalid data is allowed into the system, it can cause issues down the line, from reporting errors to system failures
• e.g. trying to save text data in a field meant for numbers, or entering dates in the wrong format, can result in errors that affect everything from queries to business decisions

Question 11

Different Levels of Validation

Answer 11

• Client-side validation- this happens on the user’s end (in the browser, app, etc.) before the data even reaches the server or database
• Application-level validation- the backend application e.g. server code performs additional validation to catch any incorrect data that slipped through client-side checks
• Database-level validation- finally, the database itself uses constraints and data types to ensure that only valid data is stored, even if earlier checks failed

Question 12

Client-Side & Application-Level Validation

Answer 12

• these early stages of validation occur before the data even touches the database, ensuring that the data is formatted correctly and meets certain criteria before it’s sent for storage

Question 13

Common Scenarios for Client-Side & Application Validatio

Answer 13

• Format Checks- making sure that phone numbers, email addresses, or dates follow a specific format
• e.g. a phone number might need to match the pattern (XXX) XXX-XXXX
• Field Type Checks- ensuring that the user enters the correct data type
• e.g. trying to submit a word or letter in a field meant for numbers, like an age or quantity field, should trigger an error
• Mandatory Fields- ensuring that required fields are not left empty
• e.g. ensuring the “name” or “email” fields are completed before submission
• e.g. when filling out an online registration form, a user should receive an error message if they type their name into the “age” field or input an invalid date into a birthdate field
• client-side and application-level validation not only enhances user experience by providing immediate feedback, but also reduces the load on the server by preventing invalid data from ever reaching the backend

Question 14

Database-Level Validation

Answer 14

• even with proper client-side and application-level validation, errors can still occur- that’s where database-level validation comes in
• this is the last line of defense to ensure that only valid data is saved, and it’s especially important because it ensures that data integrity is preserved even if earlier validations are bypassed or fail
• Data Types- each field in the database is assigned a specific data type, such as INT, VARCHAR, or DATETIME
• the data type ensures that only the correct kind of data is stored in that field
• e.g. a DATETIME field will reject any attempt to insert invalid dates (like February 30th) or non-date values (like a name)

Question 15

Database-Level Validation (part 2)

Answer 15

• Constraints- databases use constraints to enforce rules on data being inserted
• NOT NULL- ensures that a field cannot be left empty
• UNIQUE- ensures that no duplicate values are entered in a field (useful for things like email addresses or usernames)
• CHECK- adds custom rules to ensure that data follows specific criteria, like ensuring that an age is a positive integer
• e.g. if someone tries to enter the text “abc” into a field meant for numbers (such as a price or quantity field), the database would reject the data due to a mismatch with the field’s INT data type
• similarly, entering a date like “2024-13-01” would be invalid for a DATETIME field, as the month value exceeds 12

Question 16

Why a Multi-Layer Validation Approach is Essential

Answer 16

• using multiple layers of validation-client-side, application-level, and database-level-ensures comprehensive protection against data errors
• client-side validation provides immediate feedback to users, improving the user experience and reducing errors early
• application-level validation adds a second layer of error-checking and can handle more complex rules or business logic
• database-level validation is the ultimate gatekeeper, preventing invalid data from being stored in the database even if the previous layers failed

Question 17

Common Case Examples

Answer 17

• Invalid Date Formats- a user might try to input “30/02/2023” into a DATETIME field
• without proper validation, this could cause errors or lead to incorrect data being stored
• the database would reject this because it’s not a valid date
• Incorrect Data Types- in a field meant to store a quantity (an integer), if someone tries to enter text, such as “five,” the application should flag this as an error before it even reaches the database
• if it somehow slips through, the INT data type on the field will ensure the database rejects the invalid input
• Mandatory Fields- when entering customer information, some fields (like a customer ID or email address) might be mandatory
• if a record is attempted with these fields left blank, NOT NULL constraints in the database will ensure the data is not saved without those required details

Question 18

Ensuring User Feedback

Answer 18

• lastly, whenever validation fails, whether on the client-side, application-side, or database-side, it’s important to give clear and helpful feedback to the user
• e.g. instead of displaying a vague error like “Invalid Input,” the system should provide a specific message such as “Please enter a valid phone number in the format (XXX) XXX-XXXX” or “Date must be in the format MM/DD/YYYY”
• this not only helps the user understand what went wrong but also guides them to correct the error, improving the overall user experience