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

  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 a text file called yourstudentid

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

  Create a comparison document

Create a Comparison Document

  Pretend that you are on the boards

Pretend that you are on the boards of the American National Standards Institute (ANSI) and the International Organization for Standards (ISO), two of the organizations who standardized SQL

  Select statement to return one row

Write a SELECT statement that returns one row for each general ledger account number which contains three columns.

  Give me a list of all the proposed prices

Give me a list of all the proposed prices for the next year from the l_foods table.  Sort your results by proposed_price.  (Note that proposed price, is price + price_increase.)

  Sql script-creating the database and inserting data

Understanding the SQL script, creating the database and inserting data and display all product information for products that contain the string ‘saw' in their description.

  Relational schema of a firm database - write a sql query

Display the vendor ID and vendor name of the items whose item quantity is the largest and display the item ID and item quantity of all items whoseitem quantity is less than the average quantity. Also display the average quantity

  Prior to the adoption of sql as a standard

Prior to the adoption of SQL as a standard by the American National Standards Institute (ANSI), each database management system (DBMS) had its own unique command language and syntax. SQL was adopted as an ANSI standard in 1986. The International S..

  Calculate days between ordering and shipping

Calculate the Tax on an Order, Calculate the Total Shopper Spending - Develop a SELECT statement using the BB_SHOPPER table to produce a list of each shopper in the database and his or her respective totals.

  Decomposition for r = (a,b,c,d,e )

Give a good decomposition for R = (A,B,C,D,E ) with the following functional dependencies:

  Statement to display the sku, sku_description

write an sql statement to display the SKU, SKU_Description, WareHouse ID, and QuantityOnHand for all products having a QuantityOnOrder equal to 0. Sort the results in descending order by WareHouse ID and in ascending order by SKU

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