Brainscape's Knowledge GenomeTM

Browse over 1 million classes created by top students, professors, publishers, and experts.


See full index

Databases > JDBC_SQLINJ_AUT > Flashcards

JDBC_SQLINJ_AUT Flashcards

1
Q

What is JDBC short for?

A

JDBC = Java DataBase Connectivity

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
2
Q

Functions of JDBC?

A

JDBC is Java’s call-level interface to SQL
DBMS’s.
– A library with operations that give full access
to relational databases, including:
• Creating, dropping or altering tables, views, etc.
• Modifying data in tables
• Querying tables for information

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
3
Q

Explain JDBC

A

JDBC Objects
• JDBC is a library that provides a set of classes
and methods for the user:
– DriverManager
• Handles connections to different DBMS. Implementation
specific.
– Connection
• Represents a connection to a specific database.
– Statement, PreparedStatement
• Represents an SQL statement or query.
– ResultSet
• Manages the result of an SQL query.

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
4
Q

Explain statement object.

A
How well did you know this?
1
Not at all
2
3
4
5
Perfectly
5
Q

2 fundamental methods of statement objects?

A
How well did you know this?
1
Not at all
2
3
4
5
Perfectly
6
Q

Exceptions in JDBC?

A
How well did you know this?
1
Not at all
2
3
4
5
Perfectly
7
Q

How to Execute Queries.

A
How well did you know this?
1
Not at all
2
3
4
5
Perfectly
8
Q

Which commando holds the results of an SQL query?

A
How well did you know this?
1
Not at all
2
3
4
5
Perfectly
9
Q

Important note on ResultsSet.

A

ResultSet is not a result set!
• Remember a ResultSet is more like a
cursor than an actual set – it is an
interface to the rows in the actual result
set.
• A Statement object can have one result
at a time. If the same Statement is used
again for a new query, any previous
ResultSet for that Statement will no
longer work!

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
10
Q
A
How well did you know this?
1
Not at all
2
3
4
5
Perfectly
11
Q

If we need information from more than one table,
there are two different programming patterns for
doing so:

A

Two approaches
• If we need information from more than one table,
there are two different programming patterns for
doing so:
– Joining tables in SQL
• Join all the tables that we want the information from in a
single query (like we would in SQL), get one large result set
back, and use a ResultSet to iterate through this data.
– Use nested queries in Java
• Do a simple query on a single table, iterate through the
result, and for each resulting row issue a new query to the
database (like in the example on the previous page, but
without the error).

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
12
Q

Example of Joining in SQL.

A
How well did you know this?
1
Not at all
2
3
4
5
Perfectly
13
Q

Example using nested queries in JAVA.

A
How well did you know this?
1
Not at all
2
3
4
5
Perfectly
14
Q

Comparison of Joining in SQL and Nested Queries in JAVA

A

Comparison
• Joining in SQL
– Requires only a single query.
– Everything done in the DBMS, which is good at
optimising.
• Nested queries
– Many queries to send to the DBMS
• communications/network overhead
• compile and optimise many similar queries
– Logic done in Java, which means optimisations must
be done by hand.
– Limits what can be done by the DBMS optimiser.

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
15
Q

What does Push complexity to DBMS means?

A
How well did you know this?
1
Not at all
2
3
4
5
Perfectly
16
Q

Explain Dynamically Generated Queries.

A
17
Q

What are a Naive Approach to dynamically generated queries?

A
18
Q

Explain SQL injections.

A
19
Q

What is Prepared Statement used for?

A
20
Q

Why are Prepared Statement Superios? (3 reasons)

A
  1. Security
  2. Performance

3 Easier to read/write.

21
Q

Explain

PreparedStatement is superior:
Reason 1 – Security

A
22
Q

Explain PreparedStatement is superior:
Reason 2 – Performance.

A
23
Q

Explain

PreparedStatement is superior:
Reason 3 – easier to read/write

A
24
Q

How to prevent SQL injection?

A
25
Q

Summary JDBC

A
26
Q

Explain Database authorization.

A
27
Q

Explain difference between a database vs file system.

A

Database vs file system
• A (UNIX) file system has:
– Privileges on files.
– Three different privileges: read, write, execute
– Three levels of access: owner, group, all
• A database has:
– Privileges on schema elements (tables, views,
triggers, etc.)
– Nine different privileges.
– Any number of levels of access – each user can be
given different access.

28
Q

Explain privileges on relations

A

Privileges on relations
• SELECT (attributes) ON table
– Allows the user to select data from the specified table.
– Can be parametrized on attributes, meaning the user
may only see certain attributes of the table.
• INSERT (attributes) ON table
– Allows the user to insert tuples into the table.
– Can be parametrized on attributes, meaning the user
may only supply values for certain attributes of the
table. Other attributes are then set to NULL.

DELETE ON table
– Allows the user to delete tuples from the
table.
– Cannot be parametrized on attributes.
• UPDATE (attributes) ON table
– Allows the user to update data in the table.
– Parametrizing means the user may only
update values of certain attributes.

REFERENCES (attributes) ON table
– Allows the user to create a foreign reference to
(attributes of) that table.
• TRIGGER ON table
– Allows the user to create triggers for events on that
table.
• EXECUTE ON procedure
– Allows the user to execute the procedure or function,
and use it in declarations.
• USAGE, UNDER, TRUNCATE, CREATE, ALL,
…

29
Q
A
30
Q

Explain Execute and Trigger.

A

EXECUTE and TRIGGER
• When writing a trigger, the body may perform
selections and modifications.
– The user who writes the trigger must have all the
necessary privileges to perform those operations,
plus the TRIGGER privilege.
– The user that sets off the trigger needs only the
privilege to perform the triggering event (e.g. an
insertion). Everything that happens in the trigger is
considered done by its creator.
• The same thing goes for procedures and
functions – it is the privileges of the creator that
decides what operations may be performed, and
the user needs only EXECUTE.

31
Q

Explain Granting Privileges.

A

Granting privileges
• You have all possible privileges on
elements that you have created.
• You may grant privileges to other users on
those elements.
– A user is referred to by an authorization ID,
which is typically a user name.
– There is a special authorization ID, public
– Granting a privilege to public makes it
available to all users.

32
Q

How to write a GRANT statement.

A
33
Q

Explain GRANT option.

A
34
Q

Explain Revoking Privileges.

A
35
Q

Explain Grant diagrams.

A

Grant diagrams
• Nodes = user + privilege + option
– Option is either owner, WITH GRANT
OPTION, or neither.
– UPDATE ON T, UPDATE(a) ON T,
UPDATE(b) ON T and UPDATE ON T WITH
GRANT OPTION all live in different nodes.
• Edge X → Y means that node X was used
to grant Y.

36
Q

Explain Manipulating Edges.

A

Manipulating edges
• If A grants P to B, we draw an edge from AP* (or
AP**) to BP(* if with grant option).
• Revoking a privilege means deleting the edge
corresponding to the privilege.
• Fundamental rule: User U has privilege P as
long as there is a path from XP** to either UP,
UP* or UP**, where X is the owner of P.
– Note that X could be U, in which case the path is 0
steps.

37
Q

Summarize main points concerning Authorization.

A

Summary Authorization
• Privileges in SQL
– SELECT, INSERT, DELETE, UPDATE,
REFERENCE, TRIGGER, EXECUTE …
• Granting and revoking privileges
– Authentication IDs, public
– WITH GRANT OPTION
• Grant diagrams

38
Q
A

Databases (7 decks)

Brainscape helps you reach your goals faster, through stronger study habits.
© 2024 Bold Learning Solutions. Terms and Conditions