MOD 4

• As a general rule, each data item must be stored in only one place in the database (except for foreign keys).
• types of relationships:

  ◼

  one-to-oneone-to-manymany-to-many

  ◼

  allows the analyst to specify the foreign keys in the schema
• Prototypes depict the user interfaces (UIs) that will be used to access the database.
• True: The physical database can be directly created based on the relational schema
• The Data Definition Language (DDL) component of SQL is used to create or update the database structure
• True: Relational model is a logical model or implementation model
• INNODB supports the relational model’s integrity features.
• Use VARCHAR for strings with lengths that are not fixed. Check the documentation for more on data typesin MySQL.
• True: A primary key must have unique values, but it does not need to contain all columns from the table.
• integrity constraints in the relational model involve entity integrity and referential integrity.
• Entity integrity: PRIMARY KEY, UNIQUE KEY (for alternate keys) and NOT NULL
• Referential integrity: FOREIGN KEY with FK rules
• The ALTER TABLE statement is used to modify / add / drop columns or add / drop constraints
• DDL Statements: DROP TABLE, TRUNCATE TABLE, RENAME, ALTER TABLE
• Data Manipulation Language: INSEET, UPDATE, DELETE
• Cartesian Product : The most “primitive” operation to combine rows from multiple tables
• TRUE: The WHEREclause can be added to the Cartesian product in order to provide more useful information
• INNER JOIN : allows data to be retrieved from multiple tables (not necessarily distinct) such that only rows that satisfy the join condition
• The most frequently used join condition: equality between a column in the left table and a column on the right table (usually PK-FK)
• Joins based on equality are also referred to as equijoins
• Table alias is used to qualify column names with table names, particularly when table names are lengthy
• Table alias is Useful when a join operation involves columns from the same table (self-join).
• If he foreign keys and primary keys in the schema have the same column names, use NATURAL JOIN
• The USING clause can be used instead of the ON clause for equijoins that involve the same column names.
• TRUE: Join operations may involve more that two tables.
• LEFT / RIGHT [OUTER] JOIN

  It returns the result of the inner join, as well as the rows from the left/right source table that do not have a match (via the join condition) in the other source table.
• ODBC (Open Database Connectivity).

  • One of the original standard APIs for database access (Microsoft); was the basis of later data access standards/technologies (including JDBC); supported by SQL and noSQL products
• OLEDB (Object Linking and Embedding, Database)

  API provided by Microsoft to access not only relational databases but also non-relational data such as spreadsheets and object databases; can only be used by applications that run on Windows
• TRUE: One database can be accessed by multiple applications, possibly developed in different platforms
• TRUE: One application can access multiple databases / data sources in possibly various formats
• Driver manager CONNECTS to the DBMS, providing the application with access the database
• DriverManager


  Class used to establish a database connection
• Connection


  Object used to manage connections and creating (SQL) statements
• Statement


  Object used to execute a static SQL statement
• PreparedStatement


  Object used to execute a parameterized / dynamic SQL statements (derived from Statement)
• CallableStatement


  Object used to execute / invoke stored (database) procedures or functions
• ResultSet


  Table of data that is generated / returned after a query is executed; enables access to the rows and columns of the query result
• JDBC core API (JDBC 4.3): java.sql

  Date ( Class): SQL Date object (different from the java.util.Date class)

  SQLException: main exception object for information on database access errors

  SQLType (Interface): object used to identify a SQL type or vendor-specific type

  Array, BLOB, CLOB, DatabaseMetaData and others
• @varname : this variable can be used in a query so that its value can be changed as needed.
• Parameterized SQL statements / Dynamic SQL

  • The usage of variables provide flexibility that can be utilized in application code.
  • Another approach is the PREPARE keyword: