AU Wk 5 Back End Communication Systems & Open Database Connectivity Essay

Discussion 

Prior to beginning work on this discussion, read Chapter 14 in your text book as well as the JDBC Tutorial (Links to an external site.) and JDBC Introduction (Links to an external site.) online resources, and review any relevant information in this week’s lecture.

When developing an application, several different database management system (DBMS) access solutions are available including JDBC and ODBC. JDBC stands for Java database connectivity created by the Oracle Corporation. This technology is an Application Programming Interface (API) for the Java programming language that defines how a client may access a database. It provides methods for interacting with the database including issuing queries and updates in a database. On the other hand, ODBC stands for Open Database Connectivity and is a standard programming language middleware API for accessing DBMSs.

In your initial post, compare and contrast the differences between ODBC and JDBC technologies including their individual strengths and weaknesses. Describe the reasons a developer might select one technology versus the other.

JDBC//ODBC are used for connecting to back-end DBMS. Identify and explain other technologies that would allow user applications to communicate with the back-end database. Describe the advantages and disadvantages of these additional technologies, and describe how they compare with JDBC and ODBC.

Your initial post should be a minimum of 250 words.

——————————————– Weekly Lecture —————————

Week Five Lecture

Entity Relationship Diagram

With the business rules defined, tables, and attributes identified, we need to discuss another important concept known as Entity Relationship Diagram. An Entity Relationship Diagram (ERD) is a visual representation of different data using conventions that describe how these data are related to each other (Coronel & Morris, 2015). The creation of an ERD involves three major components which are entities, attributes, and relationships.

Relationships can have their own attributes that further describe how the entities are related to each other. One aspect of a relationship is cardinality, or the number of instances of an entity that can, or must, participate in the relationship. Minimum cardinality is the minimum number of entity instances that must participate in a relationship and is designated with O (optional) and M (mandatory) (Harrington, 2012). Maximum cardinality is the number of entity instances that may participate in the relationship and is designated with 0, 1, and N for many. Be sure to review your text for more details on cardinality and relationships.

Once the developer and the users have determined the essentials of the solution, an ERD is developed. Tables are then created to match the ERD using Structure Query Language (SQL). SQL is used to create the fields and insert the records that need to populate the tables that reflect the ERD.

Structured Query Language (SQL)

SQL is a data definition and manipulation language that is used with most relational databases and relational DBMSs (“Basic SQL statements: DDL and DM”,n.d. ) in the world today. It is accepted by the American National Standards Institute (ANSI) and the International Standards Organization (ISO). SQL has evolved over the years and is still acquiring more and more features. SQL 92 is a standard and has different levels of conformance, such as entry and intermediate. It is widely supported at various levels. SQL 1999 (ANSI and ISO) is the current standard.

E. F. Codd, who was an IBM scientist, published a paper in 1970 that introduced the concept of relational DBs. He worked at the IBM Research Labs in San Jose, California. IBM gets the credit for inventing SQL (called SEQUEL back then), but the Oracle Corporation, previously known as Relational Software, implemented the first commercial version of SQL. There are three types of SQL statements and clauses, data definition (DDL), data control (DCL), and data manipulation (DML).

Data Definition Language (DDL)
DDL is used to create and modify the structure of DB objects in a DB. DDL statements are compiled by the DBMS and are used to create tables, indexes, stored procedures, and other constructs. They can also be used to destroy (delete) tables, indexes, and so on. DDL statements are stored in the data dictionary. Finally, a data dictionary contains metadata, which is data about the data (Harrington, 2012).

Data Control Language (DCL)
DCL is used to create roles, permissions, and referential integrity as well as securing a database by controlling access to it. DCL consists of statements that control security and concurrent access to table data. DCL is the enforcement for the database rules set up in the DBMS. It allows management of resources and protects data integrity. It also controls transaction processing, logs any errors that occur, monitors the DBMS performance, and is an integral part of disaster recovery if records or data are lost.

Data Manipulation Language (DML)
DML is used to retrieve, store, modify, delete, insert, and update data in a DB. DML includes statements used to retrieve, update, modify, or delete from a DB. There are two types of DML: procedural and nonprocedural. Procedural DML allows the user to specify what data is needed and how to get it. This type of DML is typically what programmers use when developing DB procedures such as automated data transformation and other programmatic data processing. Nonprocedural DML allows the user to specify the data that is needed, but not how to get it. This is typically the form of DML that is used by an end user of a DB to query data. The terms DML and query language are synonymous.

Critical Database Developer Skills
An important aspect of all this is the database developer. The developer plays a critical role in this activity and needs to bring good skills to the process. These are some of the required skills that a good developer needs for this role:

• Excellent attention to detail.
• Good communication skills because he or she will need good questioning techniques in order to gather relevant information from users.
• Good critical thinking skills are essential because structured, logical thinking is the key to organizing a well-formed DB.
• The ability to “make rrder out of chaos.” Rarely will you will find that customers have an organized system in place that can be computerized without creating a logical structure. 
• The ability to organize by essential properties and be able to determine which properties or fields logically go with which entities or tables.

If you enjoy working with data, people, and processes, and enjoy solving process problems, you may enjoy being a database developer.

References

Basic SQL statements: DDL and DML (Links to an external site.). (n.d.). Retrieved from http://www.tomjewett.com/dbdesign/dbdesign.php?pag…

Coronel, C., & Morris, S. (2015). Database systems: Design, implementation, & management (11th ed.). Stamford, CT: Cengage Learning.

Harrington, L. J. (2012).  Relational Database Design Clearly Explained )2nd ed.). The Morgan Kaufmann Series in Data Management Systems.