Introductory Class in Oracle DB development using Oracle SQL Developer. No fancy coding styles. Only need simple basic. I will forward a guidance document to help your expert.
Assignment 1.2 READ this First: This is an Introductory college course, so I do not need fancy coding style. Just follow the examples in Lab 4 Explanation document which is identical to the assignments in this document. NOTE: Commands on different version of Oracle SQL Developer MAY not be compatible to Oracle SQL Developer v20.0.0. I am using Oracle18C Express Edition with Oracle SQL Developer v20.0. Your commands may run on your platform but it may not run at all on mine. I have to rerun your code on my environment. So, either complete the assignment on the same platform as mine OR follow exactly the same commands as in Lab3 Explanation document on your platform. If those commands don’t work on your platform, then, provide the screenshot of your work and then provide alternative command on a notepad which I can use to rerun in my environment. Screenshot both command and results so we know your commands work and how the subsequent results look bcoz I have to rerun your command on my environment. Example of a screenshot below: Each screenshot comes directly after the question i.e. Assignment Question followed by Evidence of answer. Yes, paste screenshot directly onto this Lab4 Submission document. Read below to have understanding the topics covered in this Lab 4 so that your command quality and level of difficulty complies with the below. Overview of the Lab Modern relational DBMS natively support a procedural language in addition to the declarative SQL language. Standard programming constructs are supported in the procedural language, including if conditionals, loops, variables, and reusable logic, lists, and other extended datatypes. The procedural languages also support the ability to embed and use the results of SQL queries. Combining the procedural language with SQL is powerful and allows you to solve problems that cannot be addressed with SQL alone. From a technical perspective, together, we will learn: · how to create and use sequences. · how to create reusable stored procedures. · how to save calculated and database values into variables, and make use of the variables. · how to implement full transactions in stored procedures. · how to create triggers to perform intra-table and cross-table validations. · how to store a history of a column using a table and a trigger. · how transaction schedules, locks, and multiversioning works with transaction concurrency. Lab 4 Explanations Reminder As a reminder, it is important to read through the Lab 4 Explanation document to successfully complete this lab, available in the assignment inbox alongside this lab. The explanation document illustrates how to correctly execute each SQL construct step-by-step, and explains important theoretical and practical details. Other Reminders · The examples in this lab will execute in modern versions of Oracle, Microsoft SQL Server, and PostgreSQL as is. · The screenshots in this lab display execution of SQL in the default SQL clients supported in the course – Oracle SQL Developer, SQL Server Management Studio, and pgAdmin – but your screenshots may vary somewhat as different version of these clients are released. · Don’t forget to commit your changes if you work on the lab in different sittings, using the “COMMIT” command, so that you do not lose your work. For diagrams or DB design chart use any freeware such as https://www.lucidchart.com/pages/ As long as there is an Option to choose Entity Relationship such as Crow’s Foot (Standard Option) or UML (Class Diagram option). You may CHOOSE your preferred DB design charting app but do show entity, entity relationship (1:1, 1:M, M:N, etc), attributes, etc …. Section One – Stored Procedures Section Background Modern relational DBMS natively support a procedural language in addition to the declarative SQL language. Standard programming constructs are supported in the procedural language, including if conditionals, loops, variables, and reusable logic. These constructs greatly enhance the native capabilities of the DBMS. The procedural languages also support the ability to embed and use the results of SQL queries. The combination of the programming constructs provided by the procedural language, and the data retrieval and manipulation capabilities provided by the SQL engine, is powerful and useful. Database texts and DBMS documentation commonly refers to the fusion of the procedural language and the declarative SQL language as a whole within the DBMS. Oracle’s implementation is named Procedural Language/Structured Query Language, and is more commonly referred to as PL/SQL, while SQL Server’s implementation is named Transact-SQL, and is more commonly referred to as T-SQL. PostgreSQL supports multiple procedural languages including PL/pgSQL which is the one used in this lab. For more information on the languages supported, reference the postgresql.org documentation. SQL predates the procedural constructs in both Oracle and SQL Server, and therefore documentation for both DBMS refer to the procedural language as an extension to the SQL language. This idea can become confusing because database texts and documentation also refer to the entire unit, for example PL/SQL and T-SQL, as a vendor-specific extension to the SQL language. It is important for us to avoid this confusion by recognizing that there are two distinct languages within a relational DBMS – declarative and procedural – and that both are treated very differently within a DBMS in concept and in implementation. In concept, we use the SQL declarative language to tell the database what data we want without accompanying instruction on how to obtain the data we want, but we use the procedural language to perform imperative logic that explicitly instructs the database on how to perform specific logic. The SQL declarative language is handled in part by a SQL query optimizer, which is a substantive component of the DBMS that determines how the database will perform the query, while the procedural language is not in any way handled by the query optimizer. In short, the execution of each of the two languages in a DBMS follows two separate paths within the DBMS. Modern relational DBMS support the creation and use of persistent stored modules, namely, stored procedures and triggers, which are widely used to perform operations critical to modern information systems. A stored procedure contains logic that is executed when a transaction invokes the name of the stored procedure. A trigger contains logic that is automatically executed by the DBMS when the condition associated with the trigger occurs. Not surprisingly stored procedures and triggers can be defined in both PL/SQL, T-SQL and PL/pgSQL. This lab helps teach you how to intelligently define and use both types of persistent stored modules. This lab provides separate subsections for SQL Server, Oracle, and PostgreSQL, because there are some significant differences between the DBMS procedural language implementations. The syntax for the procedural language differs between Oracle, SQL Server, and PostgreSQL which unfortunately means that we cannot use the same procedural code across all DBMS. We must write procedural code in the syntax specific to the DBMS, unlike ANSI SQL which oftentimes can be executed in many DBMS with no modifications. The procedural language in T-SQL is documented as a container for the declarative SQL language, which means that procedural code can be written with or without using the underlying SQL engine. It is just the opposite in PL/SQL, because the declarative SQL language is documented as a container for the procedural language in PL/SQL, which means that procedural code executes within a defined block in the context of the SQL engine. PL/pgSQL is similar to Oracle’s PL/SQL in that the procedural code executes in blocks and these blocks are literal strings defined by the use of Dollar quotations ($$). Please be careful to complete only the subsections corresponding to your chosen DBMS. You will be working with the following schema in this section, which is a greatly simplified social networking schema. It tracks the people who join the social network, as well as their posts and the “likes” on their posts. The Person table contains a primary key, the person’s first and last name, and the person’s username that they use to login to the social networking website. The Post table contains a primary key, a foreign key to the Person that made the post, a shortened content field containing the text of the post, a created_on date, and a summary of the content which is the first 12 characters followed by “…”. For example, if the content is “Check out my new pictures.”, then the summary would be “Check out my...”. The Likes table contains a primary key, a foreign key to the Person that likes the Post, a foreign key to the Post, and a date on which the Post was liked. In this first section, you will work with stored procedures on this schema, which offer many significant benefits. Reusability is one significant benefit. The logic contained in a stored procedure can be executed repeatedly, so that each developer need not reinvent the same logic each time it is needed. Another significant benefit is division of responsibility. An expert in a particular area of the database can develop and thoroughly test reusable logic, so that others can execute what has been written without the need to understand the internals of that database area. Stored procedures can be used to support structural independence. Direct access to underlying tables can be entirely removed, requiring that all data access for the tables occur through the gateway of stored procedures. If the underlying tables change, the logic of the stored procedures can be rewritten without changing the way the stored procedures are invoked, thereby avoiding application rewrites. Enhanced security accompanies this type of structural independence, because all access can be carefully controlled through the stored procedures. Follow the steps in this section to learn how to create and use stored procedures. You will also learn to work with sequences in this section, which are the preferred means of generating synthetic primary keys for each of your tables. As a reminder, for each step that requires SQL, make sure to capture a screenshot of the command and the results of its execution. Section Steps 1. Create the tables in the social networking schema, including all of their columns, datatypes, and constraints. Create sequences for each table; these will be used to generate the primary and foreign key values in Step #2. 2. Populate the tables with data, ensuring that there are at least 5 people, at least 8 posts, and at least 4 likes. Make sure to use sequences to generate the primary and foreign key values. Most of the fields are self-explanatory. As far as the “content” field in Post, make them whatever you like, such as “Take a look at these new pics” or “Just arrived in the Bahamas”, and set the summary as the first 12 characters of the content, followed by “…”. 3. Create a stored procedure named “add_zana_sage” which has no parameters