Week 4 Flashcards
(189 cards)
1
Q
What is Amazon RDS?
A
Amazon Relational Database is a web service that makes it easier to set up, operate, and scale a relational database within AWS cloud.
It provides cost efficient, resizable capacity for an industry standard relational database and manages common database administration tasks.
2
Q
Why use RDS?
A
automates expensive and time consuming tasks such as managing backups, software patching, automatic failure detection, and recovery
3
Q
What is SQL?
A
Structured Query Language.
A domain-specific language for working with certain databases called relational databases.
4
Q
What is a relational database?
A
a type of database that stores information in tables - that is, the data is stored in rows and columns.
5
Q
What is DDL?
A
Data Definition Language. These statements are utilized to define the database schema or skeleton. It is how we implement the design structure.
6
Q
Main keywords in DDL?
A
CREATE.
ALTER
DROP
TRUNCATE
7
Q
What does the CREATE keyword do? Which sub language is it used in?
A
It creates new objects or tables.
DDL
8
Q
What does the ALTER keyword do? Which sub language is it used in?
A
It modifies existing objects or tables.
DDL
9
Q
What does the DROP keyword do? Which sub language is it used in?
A
It deletes existing objects or tables.
DDL
10
Q
What does the TRUNCATE keyword do? Which sub language is it used in?
A
It deletes all the data existing within a table leaving the skeleton of the table only.
DDL
11
Q
Can changes be made to DDL operations?
A
No. DDL operations are permanent.
12
Q
What is DML?
A
Data Manipulation Language statements are used to perform CRUD operations on the actual data. Operations are normally performed by row in a relational database.
13
Q
What are the DML keywords?
A
INSERT.
UPDATE.
DELETE.
SELECT.
14
Q
What does the INSERT keyword do? Which sub language is it used in?
A
It inserts a new row into a table.
DML
15
Q
What does the UPDATE keyword do? Which sub language is it used in?
A
It updates one or more rows column values of a table that match a specific WHERE clause.
DML
16
Q
What does the DELETE keyword do? Which sub language is it used in?
A
It deleetes one or more rows of a table that match a specific WHERE clause.
DML
17
Q
What does the SELECT keyword do? Which sub language is it used in?
A
It obtains one or more rows of a table that match a specific WHERE clause. In ORACLE databases this is considered DML. This is how we perform queries in a database.
DML
18
Q
What is DQL?
A
Data Query Language. A sub language where only the SELECT statement exists.
19
Q
Four different DQL Clauses?
A
GROUP BY
HAVING
WHERE
ORDER BY
20
Q
Explain the GROUP BY clause. Which sub language is it used in?
A
This will combine all rows by a column specified in a query and perform any aggregate functions which are stated.
DQL
21
Q
Explain the HAVING clause. Which sub language is it used in?
A
This clause will pass another filter similar to the WHERE clause after everything has been filtered and grouped.
DQL
22
Q
A SQL error will be thrown if you try to perform the ______ clause in a WHERE clause.
A
HAVING
23
Q
What are Scalar Functions?
A
They operate on individual values and will perform some operation per row, and can be used in the SELECT or WHERE clause.
24
Q
What are Aggregate Functions?
A
Aggregate functions operate on multiple values (multiple rows). These functions are used to combine (aggregate) the values existing in one column.
25
What does MAX(COLUMN) return? Which sub language is it used in?
It returns the max value on a column.
DQL
26
What does MIN(COLUMN) return? Which sub language is it used in?
It returns the minimum value on a column.
DQL
27
What does AVG(COLUMN) return? Which sub language is it used in?
It returns the average value of the column.
DQL
28
What does SUM(COLUMN) return? Which sub language is it used in?
It returns the sum of the column.
| DQL
29
What does COUNT(COLUMN) return? Which sub language is it used in?
It returns the count of elements in a column.
DQL
30
Which clause can Aggregate functions be used within?
The SELECT clause.
31
What is DCL?
Data Control Language statements are used to manage the security and control of database systems.
32
What does the GRANT keyword do? Which sub language is it used in?
Grants any permission to an existing user.
DCL
33
What does the REVOKE keyword do? Which sub language is it used in?
Revokes any permissions of an existing user.
DCL
34
What is the TCL?
Transaction Control Language. These statements are utilized to manage transactions within a relational database.
35
What does the COMMIT keyword do? Which sub language is it used in?
Any DML operations that were executed before the statements will be persisted permanently.
TCL
36
What does the ROLLBACK keyword do? Which sub language is it used in?
Any DML operations between two COMMIT statments will be completely erased.
TCL
37
What does the SAVEPOINT keyword do? Which sub language is it used in?
Utilized to ROLLBACK to a specific point in time.
TCL
38
What is OWASP?
The Open Web Application Security Project is an international non-profit organization dedicated to Web Application Security.
39
What is insufficient logging and monitoring?
OWASP critical security issue.
Insufficient logging and monitoring is when you are missing security critical information logs or have a lack of proper log format, context, storage, security, and timely response to detect an incident or breach.
40
Define SELECT SQL Command.
Extracts data from a database.
41
Define UPDATE SQL Command.
Updates data in a database.
42
Define DELETE SQL Command.
Deletes data from a database.
43
Define INSERT INTO SQL Command.
Inserts new data into a database.
44
Define CREATE DATABASE SQL Command.
Creates new database.
45
Define ALTER DATABASE SQL Command.
Modifies a database.
46
Define CREATE TABLE SQL Command.
Creates a new table.
47
Define ALTER TABLE SQL Command.
Modifies a table.
48
Define DROP TABLE SQL Command.
Deletes a table.
49
Define CREATE INDEX SQL Command.
Creates an index (search key)
50
Define DROP INDEX SQL Command.
Deletes an index.
51
When should the producer produce data?
When the queue is not full.
52
When should the consumer consume data?
When the queue is not empty.
53
How can we solve the producer-consumer problem?
We can use the wait() and notify() methods to communicate between producer and consumer threads.
The wait() method to pause the producer or consumer thread depending on the queue size.
The notify() method sends a notification to the waiting thread.
54
What does I/O refer to?
The nature of how data is accessed, either by reading it form a resource (input) or writing it to a resource (output).
55
In Java, what are the two categories file I/O can fall into?
Streams.
Reader/Writer.
56
Explain Streams category.
Streams category is one of two categories relating to file I/O. Streams are for reading or writing bytes.
57
Explain Reader/Writer categories.
Reader/Writer category is one of two categories relating to file I/O. Reader/Writer are for reading or writing characters.
58
What does FileInputStream file I/O class do?
Reads raw bytes from a file.
59
What does FileOutputStream file I/O class do?
Writes raw bytes to a file.
60
What does FileReader file I/O class do?
Reads characters from a file.
61
What does FileWriter file I/O class do?
Writes characters to a file.
62
What does BufferedReader file I/O class do? What does it need?
Reads a file line by line, needs an instance of a FileReader with a path to the resource to be read in the constructor.
63
What does BufferedWriter file I/O class do? What does it need?
Writes to a file line by line, needs an instance of a FileWriter with a path to the destination file in the contructor.
64
What does Scanner file I/O class do?
Reads from an InputStream, useful methods for character reading.
65
What should you always close with when using I/O classes to read and write? Why?
.close()
This prevents exceptions from being thrown later, memory leaks, and system over utilization of unused resources.
66
What is Serialization?
The process of writing the state of an object to a byte stream; the reverse is called de-serialization.
67
What must an object do to be serialized?
It must implement the Serializable interface.
68
What is the point of a Marker interface?
The point is to provide metadata to the compiler.
69
How do you serialize an object?
To serialize an Object, you need a FileOutput Stream instance inside the constructor of an ObjectOutputStream, passing in the file path of where oyou want the Object to be serialized call the ObjectOutputStream.writeObject(yourObject) method.
70
How do you De-serialize an object?
To deserialize an Object, you need a FileInputStream instance inside the constructor of an ObjectInputStream, passing in the file path of where the serialized object is Call the ObjectInputStream.readObject() method, casting it to a bean of your type
71
What is a Singleton pattern ? Why is it useful?
A design pattern which allows the creation of an object in memory only once in an application and can be shared across multiple classes. It can be useful for services in an application, or other resources like a connection or thread pool.
72
The _______ is not created until it is used, often referred to as lazy instantiation.
Singleton
73
Which pattern gives the programmer complete control over instantiation?
Singleton Pattern.
74
How do you fetch the correct instance in a Singleton Pattern.
getInstance()
75
What allows coordination across a system due to the use of only 1 instance?
Singleton Pattern.
76
Two drawbacks to a Singleton Pattern?
Harder to work with in mulithreading.
Different components can be given too much control over other components.
77
To make a class into a Singleton, use:
private static variable of the class type.
private constructor - to prevent arbitrary object creation.
public static getInstance() method, which will either instantiate the object or return the instance in memory.
78
What is a factory pattern?
A design pattern which creates objects in which the precise type may not be known until runtime.
79
Which would pattern would you run if you don't know the exact types needed before running a code?
Factory pattern.
80
Which pattern would you run if you want to hide the creational logic, which prevents end user creating things that they shouldn't.
Factory pattern.
81
Which pattern would you use if you need an easy way to extend internal components.
Factory pattern.
82
Single responsibility is upheld by putting all of the construction code in a single function is a benefit of what?
Factory pattern.
83
To make a factory pattern...
Create the abstract data type
Create several classes that inherit the abstract data type (the objects whose instantiation details may not be known until runtime)
Set up a static method whose return type is the abstract data type in (1), which will return the concrete instance
84
What is JDBC?
Java Database Connectivity is a relatively low-level API used to write java code that interacts with relational databases via SQL.
85
Where are the JDBC classes and interfaces located?
java. sql package
| javax. sql package
86
What is the JDBC DriverManager class used for?
To make a connection a database driver.
87
What is the JDBC DataSource interface used for?
For retrieving connections. It's an alternative to DriverManager.
88
What is the JDBC Connection interface used for?
It represents a physical connection with a database.
89
What is the JDBC SQLException class?
A general exception thrown when something goes wrong when accessing the database.
90
What is the JDBC Statement interface?
Used for executing static SQL statements.
91
What is the JDBC PreparedStatement interface?
Represents pre-compiled SQL statements.
92
What is the JDBC CallableStatement interface?
Used to execute stored procedures.
93
What is the JDBC ResultSet interface?
Represents data returned from the interface.
94
What are the steps to interact with the database through Java?
1. Register the JDBC Driver.
2. Open a connection using: Database URL, Username, and Password.
3. Execute some SQL statement using either: Statement, PreparedStatement, or CallableStatement.
4. Retrieve the results that are returned in a ResultSet object.
95
An interface that represents a session between java application and database.
java.sql.Connection
96
Which class can we use to get a Connection to the database, given that we have the JDBC URL, username, and password?
DriverManager class.
97
By default, when a connection is created it is in __________.
auto-commit mode.
98
What is the JDBC String?
It is the database URL which is an address pointing to the database to be used.
99
What is the Postgres JDBC String URL?
org.postresql.Driver
100
What is DAO Design Pattern?
A way to logically separate the code that accesses the database into Data Access Objects.
101
To use the DAO Design Pattern, you must....
define an interface which declares methods through which the database will be queried.
102
The Statement and PreparedStatement interfaces have additional methods for sending SQL, including:
.execute()
.executeUpdate()
103
What does .execute() do ?
for any kind of SQL statement, returns a Boolean.
104
What does .executeUpdate() do?
for DML statements, returns an int which is the number of rows affected.
105
Results from a SQL query are returned as a _________.
ResultSet
106
How do you create an instance of the CallableStatement interface?
By using the prepareCall() method on a connection object.
107
What is a database schema?
A former structure of data defined in a relational database.
108
How can we visualize the schema of relational databases?
through Entity Relationship Diagrams, or ERD's.
109
What must you do when defining the properties of an entity in the database (i.e the columns)?
You must specify the data type to store.
110
What is the function of integrity constraints on specific columns in our database when defining tables?
They allow us to enforce the schema by ensuring consistency and integrity of the data in the table.
111
List all of the integrity constraints.
```
PRIMARY KEY
FOREIGN KEY
NOT NULL
UNIQUE
CHECK
DEFAULT
AUTO INCREMENT
```
112
What is a primary key constraint?
A constraint that uniquely identifies a record in a table.
113
A primary key is inherently composed of which two constraints?
UNIQUE
| NULL
114
What must be provided when inserting a record into a table, unless the RDBMS system is generating it automatically behind the scenes?
A primary key.
115
What is a foreign key constraint?
A constraint that signifies that a column represents a reference to the primary key of another table.
116
What is a not null constraint?
A constraint that enforces that all records must have a field for the column on which this constraint is applied.
117
We know that every person has a social security number. If we want to store the SSN, what would we consider placing on that field in our users table? Why?
not null constraint.
It prevents users of the database from leaving the table in an inconsistent or invalid state. ( similar to unique constraint)
118
What is a check constraint?
Provides a way of performing validation on values before records are entered into the table.
119
If I wanted to ensure that a bank account can never have a negative balance, which constraint would we set? Write out code example.
check.
CHECK (balance >= 0)
120
What is a default constraint?
A constraint that allows setting default values on columns for records that are inserted into the table.
121
What does Auto-increment constraint allow?
It allows a unique number to be generated automatically when a new record is inserted into a table.
122
What are candidate keys?
A scenario in which there could be multiple columns that together create a primary key to uniquely identify rows.
123
What do we call it once we identify the actual combination of columns to use as the primary key?
Composite key.
124
What is referential integrity?
This is making sure that our data remains in a consistent state throughout the database when we create table relationships.
125
What are orphan records? When does it happen? How can we prevent this?
These are records whose foreign keys do not point to an existing record in the other table.
It happens when we break referential integrity.
One way is by using a setting called CASCADE DELETE.
126
What happens when we enable CASCADE DELETE?
When we enable this, deleting a record in the table will also cascade that operation and delete any records in tables that reference the that record via foreign keys.
127
What is a one-to-one relationship?
This means that each entity in the table only relates to a single entity in the other table.
128
If we are modeling a school, where each classroom has a single projector in it, which table relationship would we want to have between Classroom and Projector? Which key would we provide the Classroom and Projector tables? Call the keys projector_id and classroom_id. Last, what should we add to enforce this? Why?
one-to-one relationship.
We would provide the foreign key projector_id to the Classroom table.
We would provide the foreign key classroom_id to the projector table.
To enforce the one to one aspect, we should apply a unique constraint on the foreign key columns. Otherwise, a use could add another projector record with the same classroom_id as an existing record which would break our one-to-one relationship.
129
What is a one to many, or many to one, relationship?
This is where one entity can belong to, own, or otherwise relate to multiple other entities.
130
A student has many books. Which table relationship best represents this? Where do we add the foreign key?
One to many or many to one.
You would add a foreign key to the "many" side of the relationship (books).
131
What is a many to many relationship?
This implies a one-to-many relationship in both directions on the entities.
132
If we are creating a connection between Students and Teachers in a database, which relationship would this likely represent? Explain how you would do this (use table called Class)
Many-to-many.
You would use a junction table. We would create a Class table which contains two foreign keys - one referring to Teacher table's primary key and one referring to the Student table's primary key.
133
What do we need to create to connect two tables with a many-to-many relationship.
junction table (aka bridge table)
134
What is PL/SQL?
This stands for the Procedural Language extension to SQL and it is a complete programming language which also allows SQL statements.
135
What is a sequence?
An object which holds a numeric number that starts from a certain point and it also contains a max. It increments by a specific amount every time NEXTVAL is called.
136
What can be combined with Triggers to auto increment primary key columns?
Sequence.
137
What is a Trigger?
A block of code that executes when a specific event happens. These events can be INSERT, UPDATE, or DELETE statements and they can happen AFTER or BEFORE.
138
What can be used to loop programmatically on the output of a SELECT statement (similar to iterators in Java)?
Cursor.
139
What is a stored procedure?
PL/SQL code that can be executed in certain ways and has some properties.
140
Stored Procedures don't return anything. T/F?
True.
141
What may or may not contain IN (by value) and OUT (by reference) parameters?
Stored Procedure.
142
What allows any DML statements within it? What does it mean can be created?
Stored Procedures.
This means transactions can be created in a stored procedure.
143
What can call other procedures and functions?
Stored procedures.
144
What is a User Defined Function?
These are like stored procedures but have some other restrictions or abilities.
145
What are the restrictions and abilities of User Defined Functions?
- They must return something.
- Cursors are allowed.
- It should be a single value.
- They may or may not contain IN parameters (by default)
- Only SELECT statements are allowed.
- Functions can only call other functions.
- They can be used in any DML statement.
-
146
To call a function, you have to use a ________.
DML statement.
147
Use _______ if you are not selecting from a specific table.
FROM DUAL
148
_______ is a dummy table which returns anything you throw at it.
DUAL
149
What are Types?
Similar to classes, they can be used as your own data types for columns. This is a high level technique.
150
What is a VARRAY?
Arrays of any Oracle data type or your own type, they can be used for columns.
151
What is a Nested Table?
Infinite arrays, they can be seen as tables in columns. They have to be of a specific data type.
152
Two main types of Indexes?
Clustered - index that alters the order in which the records are physically stored on a disk. Only one clustered index can be created on a given table.
NonClustered - index that specify a logical ordering of rows but do not affect the physical ordering, so there may be more than one non-clustered index in a table.
153
Explain the WHERE clause. Which sub language is it used in?
The WHERE clause is used to filter records. It is used to extract only those records that fulfill a specified condition.
DQL
154
Explain the ORDER BY clause. What is the default for this clause? Which sub language is it used in?
The ORDER BY clause is used to sort the result-set in ascending or descending order. The default is ascending. If you want to sort descending, use the DESC keyword.
DQL.
155
What are Joins?
Used to combine two or more tables. Joins are a database technique used in SELECT statements. Normally performed comparing primary keys to foreign keys.
156
What is an INNER JOIN?
Most common used type of join, returns rows only if the columns specified in the join clause match.
157
What is FULL OUTER JOIN?
Returns all records when there is a match in either left or right table.
158
What is LEFT OUTER JOIN?
Returns all records from the left table, and the matched records from the right table.
159
What is RIGHT OUTER JOIN?
Returns all records from the right table, and the matched records from the left table.
160
What is a CallableStatement Interface?
used to execute stored procedures. You can create by calling the prepareCall() method on a connection object.
161
What is Normalization?
An optimization process of structuring a relational database in a way that reduces redundancy of data and improves data integrity and consistency.
162
Name and explain the first three normal forms.
1NF - must have a primary key, no repeating groups, and atomic columns
2NF - must already be in 1NF, plus have no partial dependencies
3NF - must already be in 2NF, plus have no transitive dependencies
163
The first normal form enforces that a table must:
Have a primary key.
| Have each column be as granular as possible.
164
To be in second normal form, a table must also:
Not have columns that are dependent on only one part of the key. If there are no composite primary keys, you are automatically in 2NF.
165
To get to the third normal form, a table must also:
Not have transitive dependencies. This mean if column C relates to column B which relates to column A which is the primary key, this is not in 3NF because C is related to the primary key but indirectly (transitive dependency).
166
What is a sub-query?
A nested query. It is a query within another SQL query and embedded within the WHERE clause. It is used to return data that will be used in the main query as a condition to further restrict the data to be retrieved.
167
What does a properties file do?
Stores information as key value pairs, each on their own line; the file has a .properties extension.
168
What is Mockito?
A Java framework used for mocking and unit testing Java applications. Internally, it uses the Reflection API.
Mockito simplifies the process of testing by creating mock objects and avoiding external dependencies. It is used with other testing frameworks like JUnit or TestNG.
169
What is Mockito Spy?
A spy is like a partial mock, which will track the interactions with the object like a mock. Additionally, it allows us to call all the normal methods of the object. Whenever we call a method of the spy object, the real method will be invoked.
170
What are the 4 properties of ACID?
Atomicity.
Consistency.
Isolation.
Durability.
171
What is Atomicity?
Either all of the transactions will execute successfully or none of them will.
172
What is Consistency?
Constraints are enforced for every committed transaction. That indicates that all Keys, Data types, Checks, and Triggers are successful and no constraint violation is triggered.
173
What is Isolation?
Guarantees that concurrently running transactions should not affect each other.
174
What is Durability?
Guarantees all changes are recorded in the database once complete. This protects against a system crash or power outage.
175
What are the 4 Isolation levels?
Serializable.
Repeatable Reads.
Read Committed.
Read Uncommitted.
176
List some attributes of the Serializable Iso level.
Allowed in Oracle.
Read/Write locks.
Applies range locks even in the WHERE clauses of a select statement.
Phantom reads can't happen because of this.
Table that is being read can't be modified until the reading is done (no INSERTS, no UPDATES, no DELETES).
177
List some attributes of the Repeatable Reads Iso level.
Not used often.
Read/Write locks.
Doesn't provide range locks, that means phantom reads can happen.
Doesn't lock the whole SELECT statement, nor INSERTS, nor UPDATES, nor DELETS.
178
List some attributes of the Read Committed Iso level.
Oracle default.
Write only locks.
Only data that is committed will be seen by other transactions.
Dirty reads can't happen, but Phantom reads can.
This is why it is recommended to not perform very long transactions.
179
List some attributes of the Read Uncommitted Iso level.
A disaster.
Dirty reads are normal, any transaction can see any uncommitted data.
Very inconsistent.
180
What is a Dirty Read?
Reading data that is uncommitted.
181
What is a non-repeatable read?
when a row is read twice in a transaction and the values are different.
182
What is a Phantom Read?
Reading data that is being added or modified by a running transaction.
183
What are Set Operators? How are they different from Joins?
Set operators combine the rows of different result sets, and perform some kind of (set) operation on two different queries.
Joins combine columns of two tables.
184
Explain UNION and UNION [ALL] set operators.
UNION does not keep duplicates.
| UNION ALL does keep duplicates.
185
What does the INTERSECT Set Operator do?
Returns records in common between the queries.
186
What does the MINUS Set Operator do?
Removes from the first result set any rows that appear in the second result set and returns what remains.
187
What does the EXCEPT Set Operator do?
Same as MINUS, but for SQLServer instead of Oracle.
188
What is AWS S3?
A program that's built to store, protect, and retrieve data from "buckets" at any time from anywhere on an y device.
189
How does AWS S3 work?
Focuses on two key components. Buckets and objects that work together to create the storage system.
The S3 environment is a flat structure --- a user creates a bucket; the bucket stores objects in the cloud.
