/* IVY TECH COMMUNITY COLLEGE DBMS130 M02 Hands-On Lab Assignment Constructing the Database */ /* (5 points) After completing your SQL below, upload your Script and Run it until all errors are resolved. I WILL GRADE this script directly in APEX. You will be adding DB Objects to the HOL DB that were not added in the HOL M01 scripts. Use the HOL Small Company ERD and HOL Small Company Details xlsx to complete the steps listed below. It is important to name the tables and columns exactly as they are shown in the ERD/xlsx. Failure to do so will cause future assignment to fail. */ /* (5 points) Comments 1. At the beginning of your script, write single-line comments for your name, the name of the assignment, and the date. 2. At the beginning of your script, write a multi-line comment that describes the issues you had with this assignment. */ /* (30 points) Creating Tables After the 5 DROP TABLE statements, complete the 5 CREATE TABLE statements below to create the tables listed below. Make sure you choose the appropriate data types. Include NOT NULL and UNIQUE constraints. DO NOT include key constraints (i.e., primary keys, foreign keys) in your statements. The table names are: (6 points) HOL_CUSTOMERS (6) HOL_BOOKS (6) HOL_BOOK_CATEGORY (6) HOL_ORDERS (6) HOL_ORDER_ITEMS

*/ DROP TABLE HOL_ORDER_ITEMS; DROP TABLE HOL_ORDERS; DROP TABLE HOL_CUSTOMERS; DROP TABLE HOL_BOOKS; DROP TABLE HOL_BOOK_CATEGORY; CREATE TABLE HOL_BOOK_CATEGORY ( ) ; CREATE TABLE HOL_BOOKS ( ) ; CREATE TABLE HOL_CUSTOMERS ( ) ; CREATE TABLE HOL_ORDERS ( ) ; CREATE TABLE HOL_ORDER_ITEMS ( ) ; /* (20 points) Creating Constraints */ -- 1. (5 points) Write ALTER TABLE statements to add PRIMARY KEY constraints -- to the tables you created above. -- 2. (5) Write ALTER TABLE statements to add FOREIGN KEY constraints -- to the tables you created above.

-- 3. (5) Write an ALTER TABLE statement to add a foreign key constraint -- for the relationship between -- the HOL_EMPLOYEES table (created in M01 HOL) -- and -- the HOL_ORDERS table created above. -- Which column represents an "Employee" on the order in HOL_ORDERS? -- 4. (5) Write an ALTER TABLE statement to add -- a unique constraint on the ORDER_ITEMS table -- that includes both Order_Number and ISBN.

DBMS 130: HOL Small Company ERD \begin{tabular}{|l|} \hline Legend \\ \hline Tables provided \\ \hline Tables you complete \\ \hline \end{tabular} \begin{tabular}{|l|l|} \hline PK & PRIMARY KEY \\ \hline FK & FOREIGN KEY \\ \hline NN & NOT NULL \\ \hlineU & UNIQUE \\ \hline \end{tabular} \begin{tabular}{|l|l|l|} \hline \multicolumn{3}{|c|}{ HOL_REGIONS } \\ \hline PK & Region_ID & INTEGER \\ \hline & Region_Name & VARCHAR2(250) \\ \hline \end{tabular} \begin{tabular}{|l|l|l|} \hline \multicolumn{2}{|c|}{ HOL_COUNTRIES } \\ \hline PK & Country_Code & CHAR(2) \\ \hline & Country_Name & VARCHAR2(250) \\ \hline FK & Region_ID & INTEGER \\ \hline \end{tabular} \begin{tabular}{|l|l|l|} \hline \multicolumn{2}{|c|}{ HOL_EMPLOYEES } \\ \hline PK & Employee_ID & INTEGER \\ \hline & First_Name & VARCHAR2(250) \\ \hline & Middle_Name & VARCHAR2(250) \\ \hline NN & Last_Name & VARCHAR2 (250) \\ \hline & Email & VARCHAR2(250) \\ \hline & Phone & VARCHAR2(50) \\ \hline NN & Hire_Date & DATE \\ \hline & Current_Salary & NUMBER(12,2) \\ \hline & Commision_Pct & NUMBER(2,2) \\ \hline & Bonus & NUMBER(12,2) \\ \hline FK & Job_ID & INTEGER \\ \hline FK & Manager_ID & INTEGER \\ \hline FK & Department_ID & INTEGER \\ \hline \end{tabular} \begin{tabular}{|l|l|l|} \hline \multicolumn{2}{|c|}{ HOL_LOCATIONS } \\ \hline PK & Location_ID & INTEGER \\ \hline & Street & VARCHAR2(250) \\ \hline & City & VARCHAR2(250) \\ \hline & State_Province & CHAR(2) \\ \hline & Postal_Code & VARCHAR2(50) \\ \hline FK & Country_ID & INTEGER \\ \hline \end{tabular} \begin{tabular}{|l|l|l|} \hline \multicolumn{2}{|c|}{ HOL_DEPARTMENTS } \\ \hline PK & Department_ID & INTEGER \\ \hline & Department_Name & VARCHAR2(250) \\ \hline FK & Manager_ID & INTEGER \\ \hline FK & Location_ID & INTEGER \\ \hline \end{tabular} \begin{tabular}{|l|l|l|} \hline \multicolumn{2}{|c|}{ HOL_CUSTOMERS } \\ \hline PK & Customer_ID & INTEGER \\ \hline & First_Name & VARCHAR2(250) \\ \hline & Middle_Name & VARCHAR2(250) \\ \hline & Last_Name & VARCHAR2(250) \\ \hline U & Email & VARCHAR2(250) \\ \hline & Phone & VARCHAR2(50) \\ \hline & Birth_Date & DATE \\ \hline & Gender & VARCHAR2(50) \\ \hline FK & Location_ID & INTEGER \\ \hline \end{tabular} \begin{tabular}{|l|l|l|} \hline \multicolumn{3}{|c|}{ HOL_JOBS } \\ \hline PK & Job_ID & INTEGER \\ \hline NN & Job_Title & VARCHAR2(250) \\ \hline & Max_Salary & NUMBER(12,2) \\ \hline & Min_Salary & NUMBER (12,2) \\ \hline \end{tabular} \begin{tabular}{|l|l|l|} \hline \multicolumn{2}{|c|}{ HOL_JOB_HISTORY } \\ \hline PK & Job_History_ID & IDENTITY \\ \hline FK & Employee_ID & INTEGER \\ \hline & Start_Date & DATE \\ \hline & End_Date & DATE \\ \hline FK & Job_ID & INTEGER \\ \hline FK & Department ID & INTEGER \\ \hline \end{tabular} \begin{tabular}{|l|l|l|} \hline \multicolumn{2}{|c|}{ HOL_ORDERS } \\ \hline PK & Order_Number & INTEGER \\ \hline & Order_Date & DATE \\ \hline & Order_Total & NUMBER(18,2) \\ \hline FK & Customer_ID & INTEGER \\ \hline FK & Sales_Person_ID & INTEGER \\ \hline \end{tabular} \begin{tabular}{|l|l|l|} \hline \multicolumn{2}{|c|}{ HOL_BOOKS } \\ \hline PK & ISBN & INTEGER \\ \hline NN & Book_Title & VARCHAR2(250) \\ \hline & Book_Description & VARCHAR2(4000) \\ \hline & Book_Price & NUMBER(12,2) \\ \hline & Book_Reviews & INTEGER \\ \hline & User_Rating & NUMBER(4,2) \\ \hline FK & Book_Category_ID & INTEGER \\ \hline \end{tabular} \begin{tabular}{|l|l|l|} \hline \multicolumn{2}{|c|}{ HOL_ORDER_ITEMS } \\ \hline PK & Order_Item_ID & INTEGER \\ \hline & Unit_Price & NUMBER (12,2) \\ \hline & Quantity & INTEGER \\ \hline FK & Order_Number & INTEGER \\ \hline FK & ISBN & INTEGER \\ \hline \end{tabular} \begin{tabular}{|l|l|l|} \hline \multicolumn{3}{|c|}{ HOL_BOOK_CATEGORY } \\ \hline PK & Book_Category_ID & INTEGER \\ \hline NN & Book_Category_Name & VARCHAR2(250) \\ \hline & Book_Category_Description & VARCHAR2\{4000) \\ \hline \end{tabular}