PostgreSQL Flashcards
What is PostgreSQL also commonly referred to as?
a) PostSQL
b) Postgres
c) PostRelational
d) PGSQL
b) Postgres
What is the primary function of a database schema?
a) To store the entire database data
b) To define constraints for organizing and storing data
c) To run queries efficiently
d) To manage user permissions
b) To define constraints for organizing and storing data
What is the purpose of the id column in the university table?
a) To store the university’s name
b) To serve as the primary key
c) To manage foreign key relationships
d) To store timestamp data
b) To serve as the primary key
Which of the following is an example of a surrogate key?
a) A university name
b) A uniquely generated numeric ID
c) A natural column like ‘email’
d) A timestamp value
b) A uniquely generated numeric ID
What type of relationship exists between the nasa_group table and the astronauts table?
a) One-to-many
b) Many-to-one
c) Many-to-many
d) One-to-one
b) Many-to-one
Which SQL statement is used to insert data into a table?
a) ADD
b) INSERT
c) UPDATE
d) INCLUDE
b) INSERT
What does the FOREIGN KEY constraint ensure in a relational database?
a) It prevents duplicate data entries
b) It ensures data integrity by referencing another table’s column
c) It acts as a unique identifier for a table
d) It improves query performance
b) It ensures data integrity by referencing another table’s column
In the missions table, what is the purpose of the start_date and end_date columns?
a) To track mission durations using the timestamp data type
b) To count the number of missions
c) To define relationships between tables
d) To store integer values of mission durations
a) To track mission durations using the timestamp data type
What is the primary purpose of the astronaut_missions table?
a) To store duplicate mission data
b) To create a bridge table for many-to-many relationships
c) To hold all data about astronauts
d) To store primary keys for both tables
b) To create a bridge table for many-to-many relationships
What is the first normal form (1NF) in database normalization?
a) Eliminating redundant data
b) Eliminating partial dependencies
c) Ensuring schema is in 3NF
d) Adding foreign key constraints
a) Eliminating redundant data
What is one benefit of storing university data in its own table?
a) It increases the size of the database
b) It reduces redundancy and errors
c) It simplifies the astronaut data
d) It creates duplicate data
b) It reduces redundancy and errors
Which SQL keyword is used to combine rows from two or more tables based on a related column?
a) SELECT
b) JOIN
c) UNION
d) INTERSECT
b) JOIN
What is the result of a SELECT query without a WHERE clause on a large database?
a) Improved query performance
b) Unrestricted retrieval of all data
c) Data filtering based on conditions
d) Reduced query execution time
b) Unrestricted retrieval of all data
Why is it important to use a WHERE clause in SQL queries?
a) To improve JOIN syntax
b) To specify the output table
c) To restrict the amount of data returned
d) To eliminate foreign keys
c) To restrict the amount of data returned
In SQL, what does the LIMIT clause do?
a) Limits the number of database connections
b) Restricts the number of rows returned in a result set
c) Filters rows based on conditions
d) Specifies the columns to be displayed
b) Restricts the number of rows returned in a result set
What is the function of an alias in SQL queries?
a) To delete duplicate rows
b) To rename tables or columns for easier referencing
c) To enforce primary key constraints
d) To modify the database schema
b) To rename tables or columns for easier referencing
What kind of key is used in the astronauts table for name?
a) Surrogate key
b) Natural key
c) Foreign key
d) Composite key
b) Natural key
What would happen if a row in the astronauts table references a non-existent university_id in the university table?
a) The row would be added without error
b) An error would occur due to foreign key constraints
c) The data would be ignored
d) The university_id would be updated automatically
b) An error would occur due to foreign key constraints
What does the INNER JOIN keyword do in a SQL query?
a) Returns all rows from both tables
b) Returns rows where there is a match in both tables
c) Returns only unique rows from a table
d) Returns rows from one table only
b) Returns rows where there is a match in both tables
Which column type is best for storing precise date and time information?
a) VARCHAR
b) TIMESTAMP
c) INT
d) DATE
b) TIMESTAMP
What is the process of creating a database schema called?
a) Data aggregation
b) Normalization
c) Denormalization
d) Data analysis
b) Normalization
In database terms, what is a ‘transitive dependency’?
a) A column depending on a non-key column
b) A column depending on a primary key
c) A foreign key constraint violation
d) An example of a surrogate key
a) A column depending on a non-key column
What is one drawback of having a natural key as the primary key?
a) It eliminates the need for JOINs
b) It may require updating in multiple tables if the value changes
c) It adds complexity to surrogate key creation
d) It simplifies query optimization
b) It may require updating in multiple tables if the value changes
Why might database administrators denormalize a schema?
a) To save disk space
b) To eliminate JOIN operations and improve performance
c) To enforce all foreign key constraints
d) To achieve third normal form (3NF)
b) To eliminate JOIN operations and improve performance
What SQL statement would you use to view all rows and columns of the university table?
a) SELECT * FROM university;
b) DISPLAY university;
c) SHOW university;
d) VIEW university;
a) SELECT * FROM university;
What is the purpose of the PostgreSQL dependency added to the pom.xml file?
a) To enable connection pooling in Java
b) To allow Maven to access the PostgreSQL JDBC driver
c) To provide an ORM framework for PostgreSQL
d) To define the schema of the database
b) To allow Maven to access the PostgreSQL JDBC driver
What is the main role of the PostgresConn class?
a) To manage user authentication
b) To handle connections to the PostgreSQL database
c) To define the database schema
d) To execute queries directly
b) To handle connections to the PostgreSQL database
In the PostgresConn class, which method is used to establish a connection to the database?
a) connectToDatabase
b) connectToPostgres
c) initializeConnection
d) establishDatabaseConnection
b) connectToPostgres
What is the primary responsibility of the AstronautPostgresDAL class?
a) To manage the user interface for the application
b) To provide a data access layer for interacting with the database
c) To initialize the database schema
d) To configure environment variables for the database
b) To provide a data access layer for interacting with the database
Which of the following is used to ensure a secure database query in the AstronautPostgresDAL class?
a) Prepared statements
b) Raw SQL queries
c) String concatenation
d) Hardcoded queries
a) Prepared statements
What is a key advantage of using a data access layer (DAL)?
a) It improves the graphical interface of the application
b) It abstracts database operations from business logic
c) It eliminates the need for database connections
d) It generates random queries for the database
b) It abstracts database operations from business logic
In the PostgresConn class, what does the closePostgresConnection method do?
a) Closes the Java application
b) Releases the database connection back to the connection pool
c) Resets the database schema
d) Deletes the connection object
b) Releases the database connection back to the connection pool
Which library is imported to handle SQL exceptions in the code?
a) java.util.Exception
b) java.sql.SQLException
c) java.io.IOException
d) java.lang.RuntimeException
b) java.sql.SQLException
In the AstronautPostgresDAL class, what type of object does the getGeminiRoster method return?
a) An ArrayList of integers
b) A List of strings
c) A HashMap of key-value pairs
d) A single string
b) A List of strings
What does the getGeminiRoster method use to limit the number of rows returned by the query?
a) A WHERE clause
b) A LIMIT clause
c) An ORDER BY clause
d) A GROUP BY clause
b) A LIMIT clause
How does the GeminiAstronautsRDBMS class obtain database credentials?
a) From a configuration file
b) From Java constants
c) From environment variables
d) From the main method arguments
c) From environment variables
What is the result of running the GeminiAstronautsRDBMS code without defining environment variables?
a) The application retrieves default credentials
b) The application fails with a NullPointerException
c) The database connection is automatically established
d) An SQL injection attack occurs
b) The application fails with a NullPointerException
What is the role of the missionStatement in the getMissionAstronauts method?
a) To define a mission object in Java
b) To prepare and execute a parameterized SQL query
c) To perform error handling for SQL queries
d) To generate a random mission name
b) To prepare and execute a parameterized SQL query
What does the getMissionAstronauts method use to ensure unique identifiers in the query result set?
a) INNER JOIN clauses
b) The AS keyword to create column aliases
c) GROUP BY clauses
d) DISTINCT keywords
b) The AS keyword to create column aliases
In the GeminiAstronautsRDBMS class, what is the purpose of using a Set for random missions?
a) To store duplicate mission numbers
b) To prevent duplicate mission numbers
c) To sort mission numbers in ascending order
d) To map missions to astronaut names
b) To prevent duplicate mission numbers
What is the main function of the getMissionAstronauts method?
a) To generate a random set of astronaut missions
b) To query the database for astronaut and mission details
c) To initialize the database schema for missions
d) To update the mission status in the database
b) To query the database for astronaut and mission details
Which method is used to bind a parameter value to a prepared statement in Java?
a) setParameterValue
b) bindValue
c) setString
d) setQueryParameter
c) setString
What does the code postgres.getConn().createStatement() do in the getGeminiRoster method?
a) Initializes the Postgres connection
b) Executes a database update
c) Creates a SQL statement object for querying
d) Closes the database connection
c) Creates a SQL statement object for querying
What happens if duplicate random numbers are generated for missions in the GeminiAstronautsRDBMS class?
a) The duplicates are added to the Set
b) The duplicates overwrite existing values
c) The duplicates are ignored by the Set
d) The application throws an exception
c) The duplicates are ignored by the Set
Why is it important to close database connections after use?
a) To speed up query execution
b) To free up resources in the database connection pool
c) To reset the database schema
d) To prevent SQL injection attacks
b) To free up resources in the database connection pool
What is the role of the Random object in the GeminiAstronautsRDBMS class?
a) To generate database queries
b) To simulate astronaut data
c) To generate random mission numbers
d) To handle exceptions
c) To generate random mission numbers
What is the expected output of the GeminiAstronautsRDBMS program if all configurations are correct?
a) A list of random mission names
b) An error message about missing credentials
c) The full roster of Gemini astronauts and mission details
d) A list of missions without astronaut names
c) The full roster of Gemini astronauts and mission details
What is the purpose of the StringBuilder object in the GeminiAstronautsRDBMS class?
a) To parse mission data from the database
b) To concatenate strings for mission names
c) To store a list of astronaut names
d) To handle SQL exceptions
b) To concatenate strings for mission names
Which database table handles the many-to-many relationship between astronauts and missions?
a) astronauts
b) missions
c) astronaut_missions
d) mission_details
c) astronaut_missions
What happens when the getMissionAstronauts method encounters an SQL error?
a) It logs the error and rethrows it
b) It returns an empty list and prints the error stack trace
c) It terminates the application
d) It retries the query automatically
b) It returns an empty list and prints the error stack trace
