The purpose of this assignment is to allow you to demonstrate your skills associated with using SQL. This is an assignment that you may complete and turn in with one other person. All database components should be developed in conjunction with the Majestic Airwaves project or with the database problem that you developed and described associated with the ERM Assignment. All components should be completed and turned in using SQL (the problem should not be completed using MS Access). Read the entire assignment before you start working in SQL. For each query or statement that you portray, show the SQL statement, the results of the SQL statement, and a caption describing what requirement in this assignment the statement is fulfilling.
Complete the following exercises:
1. Create Tables (20%): Pratt Chapter 2, Create tables associated with the data model. Add a small number of records to each table. It is not necessary to create primary keys and foreign keys unless instructed to do so below.
Include each of the following in at least one of each of the single and multi-table queries:
a) Design a query so that “alias” titles are used to replace all of the field names with appropriate descriptive titles (i.e., use the "AS" operator).
b) Construct a constraint so that results include records between some upper and lower bound in a numeric field (e.g., donations between $50 and $100).
c) Design a query so that the user is prompted to enter a constraint or parameter used in the query.
d) Create a query that involves a calculation within a numeric field (e.g., sum all donations).
e) List output in descending order based on a particular field (e.g., descending order based on donor name).
f) Concatenate two or more columns
g) Use nested queries.
2. Single Table Queries (25%): Chapter 3. Create at least 5 separate single table queries.
3. Multi-Table Queries (30%): Chapter 4. Create 5 separate multi-table queries. Make sure that at least one query involves three tables and a second query involves three or more tables.
4. Updating Data and Tables (10%): Chapter 5. After you make each update, run a query to view the results after the change. Also, you are welcome to use ROLLBACK to remove the changes that you have made when you are done.
a) Demonstrate that you can update a single record, a range of records, and all records in a table.
b) Create a new table from a table that includes a selection and projection of a source table. For example, select a subset of columns and rows from the donor table.
c) Add a new column to an existing table. Set a default value for this field when you add it to the table.
d) After adding a new column, change the value of one record for this new field.
e) Change the width of one of the columns in a table of your choice.
5. Data Administration (15%): Chapter 6. After you make each update, run a query to view the results after the change.
a) Create and define a view involving one table. This view should involve a projection only.
b) Create and define a view involving two or more tables. This view should involve a projection and selection.
c) Write SQL commands to obtain the following information from the system catalog (you will only be able to execute the query in Oracle, you will receive an error message because you do not have the rights to carry out this command).
a. List every table containing a column of your choice.
b. List the name of every view that you own.
d) Write SQL commands to specify the following integrity constraints.
a. A column can only accept particular values (e.g., donation amount greater than zero).
b. A column can only accept a particular group of entries (e.g., donation type is within some group of descriptors).
e) Write SQL commands to add foreign and primary keys for three or more of your tables. If you had previously created the tables with primary keys, you can drop the primary keys [alter table tablename drop primary key]
What to Turn in: A print out of the SQL commands that you executed in this assignment (you do not need to show the commands used to create tables or insert data). However, include a printout of the data in each table and the table fields (i.e., use the SELECT command to show table contents and use the DESCRIBE command to show the table fields). Show all of the commands that you typed in for the queries and other commands. Also include, where appropriate, any output that is generated when the SQL commands are executed. Each section of the project should be clearly labeled (e.g., place the letter designating the query that you are showing before the commands and output for the query). The printouts should be collated and logically organized (e.g., place the printouts of the commands for creating and inserting the tables in proximity to the printouts showing the records in the tables) and stapled with a cover sheet that includes your name, the date, and the assignment number. You will probably find that a word processor will help you to edit and format your commands (I prefer that you use a Courier font or another fixed space font so that columns are aligned properly).