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