MIS3306 Module 8-2 Exercise-1

MIS 3306 Database Management Systems Module 8-2 Exercise Required for Grading (Type Your Name Below): I am Jordan Nicholson (type your full name) and I complete this assignment following the UHD academic integrity policy. (5 points) Learning objectives  Create a database and select data types  Use SQL to create a table  To add, modify, and remove columns and contraints  Use SQL to insert, update, and delete rows of data Use SQL to create database views Read Before Starting this Assignment:  You must complete Exercise 8-1 to do this exercise.  Keep in mind that the database server will NOT keep a copy of your SQL codes. The database server simply executes the commands that you send from your client (Workbench). Therefore, in Workbench, please save your SQL codes as SQL script files (*.sql) . The *.sql files will become your template for later exercises or a backup. o Common practice is to place all create-table commands in one *.sql file and all insert- data commands in another *.sql file.  Your answer is required when you see the red answer box like the box below. Answer here: <<This is an example. Answer whenever you see this.>> 1

 2

PART I: Make the Data Dictionary (A.K.A. Metadata Table). 1. Refer to the ERD and complete the data dictionary below. Fill in the blanks for the INVOICE table. (4 points) TABLE COLUMN TYPE REQUIRED PK or FK FK REFERENCED TABLE PRODUCT P_CODE VARCHAR(10) Y PK P_DESCRIPT VARCHAR(35) Y P_INDATE DATETIME Y P_QOH SMALLINT(6) Y P_MIN SMALLINT(6) Y P_PRICE DECIMAL(8,2) Y P_DICSOUNT DECIMAL(5,2) Y V_CODE INT(11) FK VENDOR(V_CODE) VENDOR V_CODE INT(11) Y PK V_NAME VARCHAR(30) Y V_CONTACT VARCHAR(50) Y V_AREACODE CHAR(3) Y V_PHONE CHAR(8) Y V_STATE CHAR(2) Y V_ORDER CHAR(1) Y INVOICE INV_NUMBER Int(11) Y PK CUS_CODE Int(11) Y FK Customer INV_DATE Date Y Note: PK will always be required but FK may be not. 2. In the INVOICE table, what is the foreign key? Also, which table and column does the foreign key refer to? (2 points) Answer here: In table Invoice Foreign key = Cus_code 3. Refer to the Table 8.1 in the textbook and MySQL data types ( https://www.w3schools.com/sql/sql_datatypes.asp ). Explain the difference between CHAR and VARCHAR. (2 points) Answer here: char data type has a fixed length and takes some fixed amount of space but Varchar which has variable length and the space occupied varies according to the allocation. Char(9) holds the 9 bytes regardless of your char input having less number of bytes. Varchar(9) the bytesallocated depends on your input. 4

4. Refer to MySQL data types ( https://www.w3schools.com/sql/sql_datatypes.asp ). For the DECIMAL(8,2) datatype, what does the “8” mean and what does the “2” mean? (2 points) Answer here: 8 is the maximum number of digits allowed in the decimal input. 2 is the number of digits allowed to the right after the Decimal point. 5

Answer here: The image should clearly show your database name, which should be DB_YourFNameLName. 7. Save the script. a. Click File à Save Script b. Save the script file as “DB_YourFirstNameLastName_CREATE_TABLE.sql” 7

8

 To define a foreign key , the format is below. 11. In the SQL code for the foreign key constraint, it writes: “ CONSTRAINT PRODUCT_FK FOREIGN KEY (V_CODE) REFERENCES VENDOR(V_CODE) ON UPDATE CASCADE ON DELETE NO ACTION ”. This means: (6 points) Answer here:  The constraint name is: _PRODUCT_FK_______  The foreign key is the _____V_CODE___ column.  The original primary key (that the foreign key refers to) is the _VENDORS ______ (table)’s ___V CODE_____ column.  “ON UPDATE CASCADE” means: If a PRODUCT(V_CODE) value exists and you command to change the same value in the VENDOR’s V_CODE, _ _CASCADE______ (what will happen?).  “ON DELETE NO ACTION” means: If a PRODUCT(V_CODE) value exists and you command to delete the same value in the VENDOR’s V_CODE, ___NO ACTION_____ (what will happen?). 12. Now, you are going to create the CUSTOMER, INVOICE, and LINE table. Among the three tables, the first table to be created should be CUSTOMER, the second table to be created should be INVOICE, and the third table to be created should be LINE. Explain why the table should be created in this sequence. (2 points) 10

Answer here: A customer can place many invoices but one invoice can be related to only one customer Same is the case between INVOICE and LINE table. 13. To create the CUSTOMER table , type the following code after the PRODUCT table statement. Highlight only the codes for creating the CUSTOMER table and click to execute the selected statement. From the SCHEMAS list, click to refresh. You should see the CUSTOMER table below your schema.  Replace the ?????? with a proper column name.  Value constraint : the DEFAULT and CHECK set the value constraints.  To define a candidate key , use the UNIQUE constraint, as shown in line 41. The default value for the CUS_AREACODE is: (2 points) Answer here: 615 14. To create the INVOICE table , type the following code after the CUSTOMER table statement. Highlight only the codes for creating the INVOICE table and click to execute the selected statement. From the SCHEMAS list, click to refresh. You should see the INVOICE table below your schema.  Replace the ??????(??????) with a proper table and column name so that it refers to the CUSTOMER’s CUS_CODE. 11

Answer here: To receive grades, your image should clearly show your name as the database name and list the five tables that you created. 13

14

Answer here: To receive grades, your image should clearly reflect the change in data type and the new column. <<Paste your image here>> 22. To drop a column form the INVOICE table , type the following code after the statement above. Highlight only the codes for altering table and click to execute the selected statement. Paste output message. After you drop the INV_TOTAL column, from the Action Output, find the output about the command, right click, and choose “Copy Row”. Paste the output message as your answer (not the screen capture). (2 points) Answer here: Directly paste the text output message here. 23. Save the script. a. Click File à Save Script b. Save the script file as “DB_YourFirstNameLastName_CREATE_TABLE.sql” c. You may now close the query tab. 16

PART V: Add Table Rows (Insert Data). 24. Open a new query. 25. To insert data into the VENDOR table, type the following code after the statement above. Highlight only the codes for altering table and click to execute the selected statement.  The format is: INSERT INTO tablename VALUES (value1, value2, value3, ….., valueN)  The values must match the column order in the table. Each column in the table needs a value.  Alternatively, you may insert the same data using the following codes. This method allows you to specify the columns (and column sequence) that you want to insert data into.  When inserting data, the data types of the values must match the data types in the table.  For INT or DECIMAL, just type the value, without quotation marks (‘ ’).  For CHAR or VARCHAR, must type values within quotation marks (‘ ’). From the inserted data, why is 615 enclosed within single quotation marks while 21225 is not? (2 points) Answer here: 26. Write and execute a SQL command to insert the following record into the VENDOR table. Remember to use Your Name. The data types should fit how they are defined in the table. Column V_CODE V_Name V_CONTACT V_AREACODE V_PHONE V_STATE V_ORDER Value 21226 Your FName LName UHD 713 221-8000 TX Y 27. From your schema, click the VENDOR table, right click, and choose “Select Rows – Limit 1000”. You will see a result like the image below. Use the Snipping or Grab app to capture your result like the image below (you may find a separate document for capturing instructions). Paste it as your answer. (2 points) Answer here: To receive grades, your data should clearly show your name. <<Paste your image here>> 17