Midterm Review Flashcards
What is the difference between a Simple and Prepared JDBC statement?
A Simple JDBC statement is executed directly without pre-compiling, which makes it vulnerable to SQL injection.
A PreparedStatement, on the other hand, is precompiled, allowing for parameterized queries, which enhances security and performance.
What is SQL Injection and how can we prevent it using JDBC?
SQL Injection is a security vulnerability where an attacker can manipulate SQL queries by injecting malicious input. To prevent it in JDBC, use PreparedStatement or CallableStatement with parameterized queries to avoid direct concatenation of user inputs.
What is a foreign key?
A foreign key is a column or a set of columns in a table that uniquely identifies a row in another table. It is used to establish and enforce a link between the data in two tables.
What is normalization?
Normalization is the process of organizing data to reduce redundancy and improve data integrity by dividing a database into two or more tables and defining relationships between them.
What is referential integrity?
Referential integrity is a property of a relational database that ensures relationships between tables remain consistent. For example, a foreign key in one table must match a primary key in another table.
What is multiplicity?
Multiplicity defines the number of instances of one entity that can or must be associated with each instance of another entity in a relationship, such as one-to-many or many-to-many.
Describe what a join is
It is an SQL operation that combines rows from two or more tables based on a related column.
Explain the different types of joins we can create.
- INNER JOIN: Returns rows with matching values in both tables.
- LEFT JOIN (or LEFT OUTER JOIN): Returns all rows from the left table and matched rows from the right table.
- RIGHT JOIN (or RIGHT OUTER JOIN): Returns all rows from the right table and matched rows from the left table.
- FULL JOIN (or FULL OUTER JOIN): Returns rows when there is a match in one of the tables.
- CROSS JOIN: Returns the Cartesian product of both tables.
What is the difference between a join and a set operation?
Joins combine columns from multiple tables based on a related column, while set operations (such as UNION, INTERSECT, and EXCEPT) combine rows from multiple queries. Joins merge data horizontally, while set operations merge data vertically.
What is a view and why is it useful?
A view is a virtual table created by a query on one or more tables. It is useful for simplifying complex queries, presenting data in a specific format, and enhancing security by restricting access to certain data.
What is HTTP? Why is it important to know about?
HTTP (Hypertext Transfer Protocol) is a protocol used for transferring hypertext requests and information on the web. It is important to understand HTTP because it is the foundation of data communication on the web.
What are common HTTP verbs used when a client application is making a request?
Common HTTP verbs include:
* GET: Retrieve data from the server.
* POST: Send data to the server.
* PUT: Update data on the server.
* DELETE: Remove data from the server.
* PATCH: Apply partial modifications to data.
What are some common HTTP status codes that can be included in a response?
Common HTTP status codes include:
* 200 OK: Request succeeded.
* 201 Created: Resource created successfully.
* 204 No Content: Request succeeded, no content to return.
* 400 Bad Request: Client-side error.
* 401 Unauthorized: Authentication required.
* 404 Not Found: Resource not found.
* 500 Internal Server Error: Server-side error.
What is a RDBMS?
An RDBMS (Relational Database Management System) is a database management system that stores data in a structured format, using tables that can be related to each other based on data values.
What is SQL and why would we use this language?
SQL (Structured Query Language) is a language used for managing and manipulating relational databases. It allows for querying, updating, and managing data, which is essential for interacting with relational databases.
What are the SQL sublanguages and their purpose?
SQL sublanguages include:
* DML (Data Manipulation Language): For querying and modifying data (e.g., SELECT, INSERT, UPDATE, DELETE).
* DDL (Data Definition Language): For defining and modifying database structures (e.g., CREATE, ALTER, DROP).
* DCL (Data Control Language): For controlling access to data (e.g., GRANT, REVOKE).
* TCL (Transaction Control Language): For managing transactions (e.g., COMMIT, ROLLBACK).
Describe relational database tables.
Relational database tables are structured collections of data organized into rows and columns. Each row represents a record, and each column represents an attribute of the record. Tables are related to each other through keys.
What are constraints and can you describe a few constraints?
Constraints are rules enforced on columns to ensure data integrity. Examples include:
* PRIMARY KEY: Uniquely identifies each record.
* FOREIGN KEY: Enforces a link between tables.
* UNIQUE: Ensures all values in a column are unique.
* NOT NULL: Ensures a column cannot have NULL values.
What are some operators that can be used in SQL?
SQL operators include:
* =, <, >, <=, >=, <> (or !=): Comparison operators.
* AND, OR, NOT: Logical operators.
* BETWEEN, LIKE, IN: Specialized operators for matching and range queries.
Why would I use the WHERE clause?
The WHERE clause is used to filter records based on specified conditions, allowing you to retrieve only the rows that meet certain criteria.
What is the JDBC API and the benefits of using it?
The JDBC (Java Database Connectivity) API is a Java-based API for connecting and executing queries on a database. Benefits include standardization of database access, ease of use, and integration with Java applications.
What is the DAO Design Pattern and why should we use it?
The DAO (Data Access Object) design pattern provides an abstract interface for interacting with a database, separating data access logic from business logic. It promotes code modularity, maintainability, and testability.
What information would you need in order to successfully connect to a database?
To connect to a database, you need:
* JDBC URL: The database location and protocol.
* Username: The database user account.
* Password: The password for the user account.
* Driver: The JDBC driver for the specific database.
Describe abstraction and how would you use it in a project?
Abstraction involves hiding the complex implementation details and showing only the necessary features. In a project, use abstraction to define abstract classes or interfaces to represent generalized concepts and hide complexity.