User Account

All Pages

Provide a comprehensive record of using sql

Assignment Help PL-SQL Programming
Reference no: EM13310546

1. Learning Objectives: The purpose of Project 3 is to enable the students to gain extensive experience with using SQL to design and query databases. After completing Project 3, a student is expected to become competent and proficient in using SQL as a DDL (Data Definition Language) and DML (Data Manipulation Language).

For the 2 databases (DBs) you implemented in Project 2, (using MySQL or SQLite), perform the following sets of activities.

2. (): Perform the following SQL Security related actions, for each of the 2 databases:

a. Create Privileges for 5 sets of concrete users via AUTHORIZATION clauses, by creating Schemas.
b. Grant Privileges to 5 sets of users, on various database elements, to enable users to be able to perform SELECT, INSERT, etc.
c. Revoke Privileges for some of the users who earlier were granted privileges

3. Incorporate 3 - 5 Triggers in each of both of your DBs.

4. Subject the designs of your two DBs to a critical analysis, using the language and terminology of Normalization Theory. In particular, provide cogent arguments that all the relational tables in your designs meet the requirements of the normal forms, from 1NF (1st Normal Form), thru 2NF (2nd Normal Form) to 3NF (3rd Normal Form). If necessary, revise your DB design and implementation to meet the xNF requirements.

5. Extend both of your DBs to incorporate Constraints of various types. Include concrete examples for each of the following constraints and manipulations.
a. Non-NULL constraints.
b. On attributes: Attribute-based CHECK constraints.
c. On tuples: Tuple-based CHECK constraints.
d. Altering constraints on Tables

6. Extend both of your DBs to incorporate Assertions.

7. Extend both of your DBS to incorporate Views and Indexes: Give concrete examples.
a. Views: Declaring, Renaming, Removal, Modifying, Updating, Materializing.

b. Indexes: Declaring.

8. Provide a comprehensive record of using SQL (MySQL) to query and manipulate the databases you have created. Your record should include concrete examples of queries and manipulations on both DBs. (See also Quiz 3).
a. DB modification via Insertion, Deletion, Updates.
b. SELECT-FROM-WHERE queries, emphasizing varieties of SELECT clauses; FROM clauses; WHERE clauses
c. Queries emphasizing ORDER BY clauses
d. Queries emphasizing GROUP BY clauses
e. Queries emphasizing HAVING clauses
f. Queries emphasizing Sub-queries
g. Queries emphasizing use 2 or more tables in a query.
h. Queries emphasizing Set Operations, Join Expressions, Returning Unique records
i. Queries emphasizing Aggregation Operations

9. Document and present your design as a report in the following form:
Title
Author
Date, Revision Number
Problem to Solve
SQL Uses. (Provide details in an Appendix)
Discussion
Summary & Conclusions
Acknowledgments, (if any)
References, (if any)
Appendix:
Constraints
Views
Indexes
Assertions
Triggers
Security Implementation
Normalization Analysis

 

 

 

Reference no: EM13310546

Questions Cloud

What is the potential drop across the tube : A 20cm long hollow tube of inner diameter 2.8nm, outer diameter 3.0mm is connected to a battery. what is the potential drop across the tube
Compute the energy required to raise the temperature : Calculate the energy required to raise the temperature of 160.0 g Ag from 273 K to 295 K. Please provide answer in J
Determine sketch beam and show neutral axis : A rectangular beam 4 inches wide and 12 inches height must sustain a maximum bending stress of 12,500 Lbs/in^2. A) Sketch beam and show neutral axis. B) Solve for the maximum bending moment.
Evaluate the energy required to heat of ethane gas : Calculate the energy required to heat 1.30 kg of ethane gas (C2H6) from 27.0°C to 78.0°C first under conditions of constant volume and then at a constant pressure of 2.00 atm
Provide a comprehensive record of using sql : Learning Objectives: The purpose of Project 3 is to enable the students to gain extensive experience with using SQL to design and query databases.
What is the change in length in the unloaded direction : A brass cube (E=100 GPa, v = 0.25(Poisson's ratio)) is 100 mm long per side. A compressive force of 100kN is applied to one side, while a tension force of 200 kN is applied to a second side.
What is the electric force on the top left charge : Starting from the top left corner and moving clockwise the charges are -10 uC, -10 uC, 5 uC, and 5 uC. What is the electric force on the top left charge due to all the other charges
Determine what fraction of the cyanide is in the cn-form : The sodium cyanide solution ([NaCN]-total = 0.01 M) is kept at pH 10.4. The pH is kept high to keep the cyanide in the CN- form. If CN protonates by the reverse of the HCN dissociation reaction,
Describe what mass of carbon dioxide is produced : What mass of carbon dioxide is produced from the complete combustion of 7.00×10?3g of methane

Reviews

Write a Review

PL-SQL Programming Questions & Answers

  Write a script that declares a variable

Write a script that declares a variable and sets it to the count of all products in the Products table. If the count is greater than or equal to 7, the script should display a message that says,

  Write pl-sql procedures and functions

Write PL/SQL procedures and functions to populate and query that database

  Write pl-sql block that displays last name

Write PL/SQL block which displays last name and salary of following people. Each of these can be done separately as PL/SQL, first to test and get output lines.

  Design new transaction by sql statements

Code SQL statements to update view as follows: change spelling of Mark Tobey to Mark Toby. Design new Transaction for Mark Toby. Suppose you have essential transaction, work, and customer data.

  Create report to display scr courses in alphabetical order

Create the detail report which will display all SCR courses in alphabetical order, with a course name and instructor name in the group header.

  Display all the lastnames from the customers table

Display all the LastNames from the Customers table and any associated OrdersIDs from the Orders Table. Show the LastName even if they do not have any associated orders. Order the results by LastName in ascending order.

  Create a text file called yourstudentid

Create a text file called YourStudentId-Create.sql (format xxxxxxx-Create.sql) for example 2225991- Create.sql that will.

  Write a perl subroutine for temperature conversion

Write a perl subroutine for temperature conversion named ' convert_temp '. It should be able to handle both Fahrenheit to Celsius conversions as well as Celsius to Fahrenheit conversions.

  F does not logically imply

Given R = (J,K,L ) and the set F of FDs J -> K and L -> K , show that F does NOT logically

  Write sql statement to add new record to part table

Write SQL statement which creates the stored procedure which adds new record to the Part table, and returns value of newly created PartID PK value in out parameter.

  Convert requirement to logic and then to sql

Prepare the SQL query for each of given requirements. Convert requirement to logic, then to SQL. Illustrate the names of all products which have been sold.

  Write an update statement that modifies the row

Write an UPDATE statement that modifies the row you just added to the Categories table. This statement should change the ProductName column to "Woodwinds", and it should use the CategoryID column to identify the row.

Free Assignment Quote

Assured A++ Grade

Get guaranteed satisfaction & time on delivery in every assignment order you paid with us! We ensure premium quality solution document along with free turntin report!

Submit Assignment

Academics

Major Subjects

Majors

Get In Touch

whatsapp: +1-415-670-9521

Phone: +1-415-670-9521

Email: [email protected]

TERMS & POLICIES

HELP & SUPPORT

All rights reserved! Copyrights ©2019-2020 ExpertsMind IT Educational Pvt Ltd