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

  Create a database model

Create a database model and Submit the table creation statements for the Database Model.

  Write pl-sql procedures and functions

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

  Sql questions

Write a query to display using the employees table the EMPLOYEE_ID, FIRST_NAME, LAST_NAME and HIRE_DATE of every employee who was hired after to 1 January, 1995.

  Run the lab_03_01.sql script

Run the lab_03_01.sql script in the attached file to create the SAL_HISTORY table. Display the structure of the SAL_HISTORY table.

  Write sql queries

Write a query to display the last name, department number, and salary of any employee whose department number and salary both match the department number and salary of any employee who earns a commission.

  Explaining sql insert statement to insert new row in cds

Write down a SQL insert statement to insert new row in "CDS" table.

  Write down name of actors in ascending order

Write down actors (or actress, your choice, but not both) who have won at least two (2) Academy Awards for best actor/actress. Provide the actor name, movie title & year. Order the result by actor name."

  What is an sql injection attack

What is an SQL injection attack? Explain how it works, and what precautions must be taken to prevent SQL injection attacks.What are two advantages of encrypting data stored in the database?

  Determine resonant frequency in series rlc resonant circuit

Given the series RLC resonant circuit in the figure, operating at variable frequency, determine: The resonant frequency ω o ,  The circuit’s quality factor Q , The cut-off frequencies, f 1  & f 2  and the bandwidth BW

  Query that uses cube operator to return lineitemsum

Write summary query which uses CUBE operator to return LineItemSum (which is the sum of InvoiceLineItemAmount) group by Account(an alias for AccountDesciption).

  Query to show customers were missing for existing orders

As DBA, your manager called a meeting and asked why there are so many orders for customers that don't exist in the customer table. Write query which would shows which customers were missing for existing orders. Use a join or a subquery.

  Sql query into a relational algebra statement

Turn this SQL query into a relational algebra statement? SELECT Request.reqfor, Ordering.invamt, Ordering.invnbr, Ordering.invdat

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