using SQL to design and query databases

profileSuperClass
 (Not rated)
 (Not rated)
Chat

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. (): Perform the following SQL Security related actions, for each of the 2 databases: Create Privileges for 5 sets of concrete users via AUTHORIZATION clauses, by creating Schemas. Grant Privileges to 5 sets of users, on various database elements, to enable users to be able to perform SELECT, INSERT, etc. Revoke Privileges for some of the users who earlier were granted privileges Incorporate 3 - 5 Triggers in each of both of your DBs. 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. Extend both of your DBs to incorporate Constraints of various types. Include concrete examples for each of the following constraints and manipulations. Non-NULL constraints. On attributes: Attribute-based CHECK constraints. On tuples: Tuple-based CHECK constraints. Altering constraints on Tables Extend both of your DBs to incorporate Assertions. Extend both of your DBS to incorporate Views and Indexes: Give concrete examples. Views: Declaring, Renaming, Removal, Modifying, Updating, Materializing. Indexes: Declaring. 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...

  • 8 years ago