Information Systems MIS 3306 Assignment
FaizLakhani
MIS3306Module8-2Exercise1.docxMIS 3306 Database Management Systems
Module 8-2 Exercise
Required for Grading (Type Your Name Below):
I am (type your full name) and I complete this assignment following the UHD academic integrity policy.
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.
· 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.>>
|
·
You are asked to rebuild the SalesCo database. The ERD of SalesCo is shown below. The ERD incorporates the column names and the data types.
|
Symbol |
Meaning |
|
|
Primary key |
|
|
Foreign key |
|
|
Column (Not null) |
|
|
Column (Could be null) |
|
|
Atribute (CUS_CODE) and its data type (INT(11)) |
Note: The primary key for the LINE table is (INV_NUMBER, LINE_NUMBER), where INV_NUMBER is part of the PK and is also an FK.
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 |
|
|
|
|
|
|
CUS_CODE |
|
|
|
|
|
|
INV_DATE |
|
|
|
|
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:
|
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:
|
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:
|
PART II: Make and Use the Database.
5. Open MySQL Workbench and click the local instance. You will see the Workbench environment blow.
6. Create a database and use the database.
a. Click File New Query Tab, or click
to open a new query.
b. type the following code in the SQL query editor. Replace the database name with your first name and last name. This is required for grading.
c. Highlight the codes and click
to execute the selected portion.
d. From the SCHEMAS list, click
to refresh. You should see the schema “DB_YourFNameLName”. MySQL uses the term schema to refer to a user’s view of a database.
e. To use the database, type the following code in the SQL query editor after the creating database syntax. Highlight the codes and click
to execute the selected statement.
f. After refreshing the schemas, you should see your database (schema) in bold, which means it is in use. All the later SQL statements will be sent to and executed here. Use the Snipping or Grab app to capture your database like the image below (you may find a separate document for capturing instructions). Paste it as your answer. (2 points)
|
Answer here: The image should clearly show your database name, which should be DB_YourFNameLName.
<<Paste your image here>>
|
7. Save the script.
a. Click File Save Script
b. Save the script file as “DB_YourFirstNameLastName_CREATE_TABLE.sql”
PART III: Create Tables.
8.
To create the VENDOR table, type the following code in the SQL query editor. You must create the VENDOR table before the PRODUCT table because the PRODUCT table contains a foreign key (V_CODE) that refers to the V_CODE in VENDOR.
· For better readability: The code is not case sensitive. However, please follow the naming convention for table names and column names. Use TAB, Space, or Enter/Return on keyboard to align the statement.
· To create a table, use the syntax below. Define the columns and constraints within the parentheses.
· CREATE TABLE tablename ();
· To end a a SQL statement, use semicolon (;). Therefore, these whole thing from line 6-15 is one statement for creating the VENDOR table.
· To define a column, the format is below. Use space or tab to separate.
· ColumnName DataType Constraint
· To separate a column/constraint, use a comma (,). For the last column or constraint in the table, do not put a comma at the end of the last column/constraint.
· To define a primary key, the format is below.
· CONSTRAINT ConstraintName PRIMARY KEY (ColumnName)
· Normally you define the constraint after defining the columns.
· Choose the proper constraint name that make sense (such as VENDOR_PK, indicating it as the PK in the VENDOR table).
· The constraint name should be unique in the database.
· The column name you specified in this constraint must exist in the same table.
· This is the syntax for MySQL. There exist minor differences between DBMSs.
9. Highlight only the codes for creating the VENDOR table and click
to execute the selected statement. From the SCHEMAS list, click
to refresh. You should see the VENDOR table below your schema.
10.
To create the PRODUCT table, type the following code after the VENDOR table statement. Highlight only the codes for creating the PRODUCT table and click
to execute the selected statement. From the SCHEMAS list, click
to refresh. You should see the PRODUCT table below your schema.
· 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: ________ · The foreign key is the ________ column. · The original primary key (that the foreign key refers to) is the ________’s ________ 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, ________ (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, ________ (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)
|
Answer here:
|
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:
|
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.
The value range for the INV_DATE must be: (2 points)
|
Answer here:
|
15.
To create the LINE table, type the following code after the INVOICE table statement. Highlight only the codes for creating the LINE table and click
to execute the selected statement. From the SCHEMAS list, click
to refresh. You should see the LINE table below your schema.
· Replace the ??????(??????) with a proper table and column name.
16. Review the table. From your schema, click the LINE table, right click, and choose “Table Inspector”. You should be able to see the configurations of the LINE table like the image below.
Click on the “Foreign keys” tab, what are the two foreign keys? What are the tables and columns that they refer to? (2 points)
|
Answer here:
|
17. After refreshing the schemas, use the Snipping or Grab app to capture your database 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 image should clearly show your name as the database name and list the five tables that you created.
<<Paste your image here>>
|
PART III: Alter Tables.
18.
To change the data type of PRODUCT’s P_INDATE column, type the following code after the create-table statements. Highlight only the codes for altering table and click
to execute the selected statement. From the SCHEMAS list, click
to refresh.
19. From your schema, click the PRODUCT table, right click, and choose “Table Inspector”. Click on the Columns tab. You should be able to see the data type for P_INDATE has been changed like the image below.
20.
To add a column into the PRODUCT table, type the following code after the statement above. Highlight only the codes for altering table and click
to execute the selected statement. From the SCHEMAS list, click
to refresh.
21. From your schema, click the PRODUCT table, right click, and choose “Table Inspector”. Click on the Columns tab. Use the Snipping or Grab app to capture your database 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 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.
PART IV: 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>>
|
28. To insert data into the PRODUCT table, type the following code after the statement above. Highlight only the codes for altering table and click
to execute the selected statement.
· Must replace the ????? with a proper value.
What value can “?????” be? Why? (2 points)
|
Answer here: Hint: One of the integrity rules. Name it or explain it.
|
29. From your schema, click the PRODUCT 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, the image should clearly show the V_CODE with a proper value.
<<Paste your image here>>
|
30. Save the query.
a. Click File Save Script
b. Save the script file as “DB_YourFirstNameLastName_INSERT_DATA.sql”
Congrats! End of Exercise!
16
