Assignment
1. Review the 3NF you have designed 3NF in assignment 4-1 for The Gill Art Gallery. Make sure your design can store all the information listed in the form below. Update the design if necessary (Remember to review the comment for suggested changes if you have).
(Note, Artist information should be separated into three attributes: artist_id, artist_fname and artist_lname)
The Gill Art Gallery wishes to maintain data on their customers, artists and paintings. They may have several paintings by each artist in the gallery at one time. Here is the information that is needed to generate the Gallery Customer History Form:
|
Gallery Customer History Form
|
|
|
|
|
|
|
Customer Name
|
|
|
|
| |
|
|
|
|
Kelly, John
|
Phone
|
(614) 284-6783
|
|
| |
|
|
|
|
3255 Broad St.
|
|
|
|
|
Columbus, OH
|
|
|
|
|
43215-1234
|
|
|
|
| |
|
|
|
|
Purchases Made
|
|
|
|
|
|
|
|
|
|
Artist
|
Title
|
Purchase Date
|
Sales Price
|
|
10 - Carol Channing
|
Laugh with Teeth
|
2/13/16
|
4670
|
|
13 - Dennis Frings
|
South toward Emerald Sea
|
7/21/15
|
4500
|
|
10 - Carol Channing
|
At the Movies
|
8/14/15
|
6950
|
|
13 - Dennis Frings
|
Face of Mountain in Snow
|
3/19/13
|
5900
|
2. Generate the DDL to create the entities, attributes, constraints, primary keys and foreign keys you identified in 3NF and name the script name as gallery.sql. TheDept_team.sqlscript includes the example of create table, primary key, foreign key and not null constraint for your reference.
1) For all columns with VARCHAR2 data type, you need to define length of each one. For example VARCHAR2(35) for last name and first name columns, VARCHAR2(255) for CUSTOMER_STREET.
2) For CUSTOMER_STATE column, if you only store the abbreviation of state in US, you can choose CHAR(2); if you want to store the state name, you can use varchar2(40).
3. Add the drop table cascade constraints; command BEFORE creating any table in your gallery.sqlfile or in the worksheet area so that this file can be rerun when necessary. You will get table does not exist the first time. Ignore it. Refer to Dept_team.sql for drop table example.
4. Run gallery.sqlto generate tables in the database for Gill Art Gallery.
5. Insert data into tables. Create insert statements to insert data provided in Gallery Customer History Forminto related tables. Feel free to insert more data if you like after inserting all the information provided in the Gallery Customer History Form.
6. Write a SELECT query to display the customer John Kelly's information in Customer table and copy the output into Work document. Note: The output must be the output from the query. I will test it using your query.
7. Write a SELECT query to display the purchases made by John Kelly including the painting title, artist name, sale price and purchase date of each painting, and copy the output into Word document.
8. Write a SELECT query to display the total amount of money that John Kelly paid for all paintings he bought. (hint: use SUM function for total amount)
9. Write a query to update the sales price of "Laugh with Teeth" painted by Carol Channing to $5400. Then write a query to display the sales price of this painting after the update. Copy the output into Word document.
10. Submit your assignment in Word document with all SQL scripts and all output results.
Note: Copy both SQL statement and output result in every step into Word document with step number. Points will be deducted if any of them is missed. For each step, the output should follow each SQL statement.Refer to Example1.doc attached in 7-1:Lab2.
• Note: Lab Expectations - These apply to all SQL Labs for this class.
1) Always include set echo on;SQL command at the beginning of every SQL script(.sql) file you create or before inputting any SQL commands directly into the worksheet area in SQL Developer (along with the set serveroutput on;command right after it).
2) Include commit; command for INSERT/UPDATE/DELETE to ensure the changes is stored permanently in the database.
a. When writing your SQL query commands and gathering the output from them (to be put into a Word document), you must be sure to include the step #, the SQL command(s), and it corresponding output(s), in that order in the Word document.
b. Insert will append the data into existing table. If you want to remove the data in the table and then insert the new data, use delete from ; command BEFORE doing any inserts into tables in your .sql file or in the worksheet area.
c. Use the comment feature in SQL Developer in your .sql script files and in your worksheet view to include any comments. These are input into your file by surrounding your comments with a /* ... */. An example is: /* this is a comment */ Also see it used in the .sql file hyperlink in #1 above. These comments should be used to designate which commands and output apply to which steps.
d. Points will be deducted if any of the expectations are not met.
Attachment:- Assignment-Lab-SQL.rar