Query 1. Modify data. Replace the text. Your First Name, Your Last Name with your first...
Fantastic news! We've Found the answer you've been seeking!
Question:
Transcribed Image Text:
Query 1. Modify data. Replace the text. Your First Name, Your Last Name with your first name and your last name in the DML file. Aside: Last Name is the Family Name or Surname. Replace 0 in Balance field with another number. Replace the other five entries in the Customer_T table with your own data. Write the names of your colleagues sitting besides you in the lab. Do not skip this step. After modifying the script run it. Type and run each of the statements to verify that the tables are created successfully. SELECT FROM Customer_T; SELECT FROM Invoice_T; SELECT FROM Product_T; SELECT * FROM Invoice_Line_T; Query 2. List data in sorted order. List all customers (last name and then first name), sorted by last name. Hint: Use the clause ORDER BY. Query 3. List year of invoice number. List only invoice numbers and invoice dates (4-digit year) from the Invoice T table. Tip: Use the EXTRACT function. Query 4. List Data, identify correct table. List Invoice Number, Product. Code, and Line Price. Identify the table that has these columns? Query 5. List Data. Associate common terms with database field ames. List all product code and product description. Choose the appropriate table to complete the query. Query 6. Filter rows. List customer names (last and first name) and city for those customers who do not live in Ottawa. Hint: Use WHERE clause. Query 7. Use String functions. The city name is stored as Ottawa. Modify the query to list the same number of rows when the condition is written as OTTAWA. Tip: Use the appropriate string function shown in figure 2.2 on page 23, Query 8. Reverse Engineer the database. Reverse Engineer the Inventory database using a data modeling tool. Rearrange the entities and relationships. Insert a text box with your name, section number, student number, course code, and course name. Query 8. Reverse Engineer the database. Reverse Engineer the Inventory database using a data modeling tool. Rearrange the entities and relationships. Insert a text box with your name, section number, student number, course code, and course name. Query 9. Determine identifying and non-identifying entities. Determine the identifying and non-identifying relationships. Note your answers, you will need them for the quiz. Refer to the documentation on identifying and non-identifying relationships. Query 10. Determine the associative entity. Which one of the four entities is an associative entity? Give a reason. 5. SQL CONSTRAINTS Notes on Relationships An identifying relationship is represented by a solid line; a row in a child table can be uniquely identified only if an entry exists in the parent table. For example, in table Invoice_Line_T an entry can be identified only if an Invoice_Number exists in the Invoice_T table. Aside: The prime key Invoice_Number in the Invoice T Table is part of the prime key in the Invoice_Line_T table. A non-identifying relationship is represented by a broken (dashed) line. In a non-identifying relationship, an entry in a child table can be uniquely identified without a corresponding entry in the parent table. For example, an entry in the Invoice T table can be uniquely identified by Invoice_Number alone, it does not need an entry from the customer table. Similarly an entry in the Invoice_Line_T table can be uniquely identified by a combination of Invoice_Number and Invoice_Line it does not depend on an entry in the Product T table. 66 4 cust_id cust_fname cust_Iname cust_phone cust_address cust_city cust prov cust_postcode customer t Character(4) Character varying(30) Character varying (30) Character varying (15) Character varying (20) Character varying(15) Character(2) Character(6) cust_balance Numeric(9,2) + invoice_number cust_id invoice date I I I fk cust id invoice_t NN (PK) NN NN Date NN NN NN Character(6) NN (PK) Character(4) NN (FK) fkl invoice line prod_code prod_description prod_indate prod_qoh prod_min prod_price prod discount product_t Character(5) Character varying(60) Date Integer Integer Numeric(5,2) Integer invoice_line_t 4 invoice_number Character(6) invoice_line prod_code line_units line_price NN (PK) NN Figure 5.4: Inventory Database NN NN NN fk2 invoice line NN (PFK) Integer NN (PK) Character(5) NN (FK) Integer NN Numeric(5,2) NN Requirements Create a database called Inventory in postgres. First load and run Inventory-DDL, the DDL script will create tables. Load the Inventory-DML script. Locate the following line, INSERT INTO Customer_T VALUES ( C002 , Your First Name , Your Last Name , 613-727-4723 , 1385 Woodroffe Ave , Ottawa , ON , K2G1V8¹,0); A note on CONSTRAINTS. You can run the DML.sql file only once, the constraints will prevent you from running it again. If you need to run the DML the second time, first run the DDL. This will drop all existing tables and recreate them. Now you will be able to run your modified DML.sql. Query 1. Modify data. Replace the text. Your First Name, Your Last Name with your first name and your last name in the DML file. Aside: Last Name is the Family Name or Surname. Replace 0 in Balance field with another number. Replace the other five entries in the Customer_T table with your own data. Write the names of your colleagues sitting besides you in the lab. Do not skip this step. After modifying the script run it. Type and run each of the statements to verify that the tables are created successfully. SELECT FROM Customer_T; SELECT FROM Invoice_T; SELECT FROM Product_T; SELECT * FROM Invoice_Line_T; Query 2. List data in sorted order. List all customers (last name and then first name), sorted by last name. Hint: Use the clause ORDER BY. Query 3. List year of invoice number. List only invoice numbers and invoice dates (4-digit year) from the Invoice T table. Tip: Use the EXTRACT function. Query 4. List Data, identify correct table. List Invoice Number, Product. Code, and Line Price. Identify the table that has these columns? Query 5. List Data. Associate common terms with database field ames. List all product code and product description. Choose the appropriate table to complete the query. Query 6. Filter rows. List customer names (last and first name) and city for those customers who do not live in Ottawa. Hint: Use WHERE clause. Query 7. Use String functions. The city name is stored as Ottawa. Modify the query to list the same number of rows when the condition is written as OTTAWA. Tip: Use the appropriate string function shown in figure 2.2 on page 23, Query 8. Reverse Engineer the database. Reverse Engineer the Inventory database using a data modeling tool. Rearrange the entities and relationships. Insert a text box with your name, section number, student number, course code, and course name. Query 8. Reverse Engineer the database. Reverse Engineer the Inventory database using a data modeling tool. Rearrange the entities and relationships. Insert a text box with your name, section number, student number, course code, and course name. Query 9. Determine identifying and non-identifying entities. Determine the identifying and non-identifying relationships. Note your answers, you will need them for the quiz. Refer to the documentation on identifying and non-identifying relationships. Query 10. Determine the associative entity. Which one of the four entities is an associative entity? Give a reason. 5. SQL CONSTRAINTS Notes on Relationships An identifying relationship is represented by a solid line; a row in a child table can be uniquely identified only if an entry exists in the parent table. For example, in table Invoice_Line_T an entry can be identified only if an Invoice_Number exists in the Invoice_T table. Aside: The prime key Invoice_Number in the Invoice T Table is part of the prime key in the Invoice_Line_T table. A non-identifying relationship is represented by a broken (dashed) line. In a non-identifying relationship, an entry in a child table can be uniquely identified without a corresponding entry in the parent table. For example, an entry in the Invoice T table can be uniquely identified by Invoice_Number alone, it does not need an entry from the customer table. Similarly an entry in the Invoice_Line_T table can be uniquely identified by a combination of Invoice_Number and Invoice_Line it does not depend on an entry in the Product T table. 66 4 cust_id cust_fname cust_Iname cust_phone cust_address cust_city cust prov cust_postcode customer t Character(4) Character varying(30) Character varying (30) Character varying (15) Character varying (20) Character varying(15) Character(2) Character(6) cust_balance Numeric(9,2) + invoice_number cust_id invoice date I I I fk cust id invoice_t NN (PK) NN NN Date NN NN NN Character(6) NN (PK) Character(4) NN (FK) fkl invoice line prod_code prod_description prod_indate prod_qoh prod_min prod_price prod discount product_t Character(5) Character varying(60) Date Integer Integer Numeric(5,2) Integer invoice_line_t 4 invoice_number Character(6) invoice_line prod_code line_units line_price NN (PK) NN Figure 5.4: Inventory Database NN NN NN fk2 invoice line NN (PFK) Integer NN (PK) Character(5) NN (FK) Integer NN Numeric(5,2) NN Requirements Create a database called Inventory in postgres. First load and run Inventory-DDL, the DDL script will create tables. Load the Inventory-DML script. Locate the following line, INSERT INTO Customer_T VALUES ( C002 , Your First Name , Your Last Name , 613-727-4723 , 1385 Woodroffe Ave , Ottawa , ON , K2G1V8¹,0); A note on CONSTRAINTS. You can run the DML.sql file only once, the constraints will prevent you from running it again. If you need to run the DML the second time, first run the DDL. This will drop all existing tables and recreate them. Now you will be able to run your modified DML.sql.
Expert Answer:
Answer rating: 100% (QA)
Answer Query1 UPDATE custmert SET custfnameJohn custlnameSmith custphone9856321548 custaddressKottar... View the full answer
Related Book For
Database Systems Design Implementation and Management
ISBN: 978-1285196145
11th edition
Authors: Carlos Coronel, Steven Morris
Posted Date:
Students also viewed these databases questions
-
Another database associated with this text and found in Wiley PLUS is the Consumer Food database. There is a dichotomous variable in this database, and that is whether a family lives in a metro area...
-
Create a SQL query to satisfy each of the tasks below. Save each SQL query using the name associated with the task in the table below You must create an sql code for each tasks and ensure when...
-
Name three ways that null values in a database can be interpreted. Why is this problematic?
-
Show that in the limit ??x ?? 0 and ??t ?? 0, the difference Equation (3.12) is equivalent to the differential Equation (2.5). GIVENThe difference equation for one-dimensional transient...
-
Peptides often have functional groups other than free amino groups at the N terminus and other than carboxyl groups at the C terminus. (a) A tetrapeptide is hydrolyzed by heating with 6 M HCl, and...
-
What is the quiet period?
-
Aprinted circuit board is produced by passing through a sequence of three steps. The scrap rates for steps one through three are 5 percent, 3 percent, and 3 percent, respectively. If 10,000 good...
-
The 2012 pro-forma income statement for Grover Company is as follows (ignore taxes): Required: 1. Compute how many units must be sold to break even. 2. Compute the increase (decrease) in profit under...
-
Discuss the importance of embedded systems in the industry. Discuss one application enabled with embedded systems.
-
What is the level of measurement for each of the following variables? a. Student IQ (intelligence quotient) ratings. b. Distances students travel to class. c. Student scores on the first statistics...
-
A History of Anthropology by Thomas Hylland Eriksen & Finn Sivert Nielsen According to chapter one what is Anthropology? What is the aim and methodology of this field? Describe the historical...
-
Your firms bank has offered you two options for short-term financing in the amount of $450,000. The first option is a committed line of credit with a commitment fee of 0.4% (EAR) and an interest rate...
-
Starware Software was founded last year to develop software for gaming applications. The founder initially invested $1,000,000 and received 12 million shares of stock. Starware now needs to raise a...
-
Foster Enterprises stock is trading for $43 per share and there are currently 18 million shares outstanding. It would like to raise $105 million. If its underwriter charges 6% of gross proceeds, a....
-
The Needy Corporation borrowed $10,500 from Bank Ease. According to the terms of the loan, Needy must pay the bank $458 in interest every three months for the three-year life of the loan, with the...
-
If Berkshire Hathaways A shares are trading at $80,000, what split ratio would it need to bring its stock price down to $40?
-
Use Euler's method to find y-values of the solution for the given values of x and Ax, if the curve of the solution passes through the given point. Check the results against known values by solving...
-
Respond to the ethical judgments required based on the following scenarios. Scenario 1. Assume you have collected a sample using MUS and that you have evaluated that sample to calculate a total...
-
What are the three basic components of the ODBC architecture?
-
Use MS Excel to connect to the Ch02_InsureCo MS Access database using ODBC, and retrieve all of the CUSTOMERs.
-
What trade-offs are involved in building highly distributed data environments?
-
Consider a charged relativistic particle of charge \(q\) and mass \(m\) moving in a cylindrically symmetric magnetic field with \(\mathrm{B}^{\varphi}=0\). (a) Show that this general setup can be...
-
For the previous problem, find the angular speed by which the particle spins about the magnetic field in terms of the radius of the circular orbit \(ho\) and other constants in the problem. Data from...
-
A cyclotron is made of sheet metal in the form of an empty tuna-fish can, set on a table with a flat-side down and then sliced from above through its center into two D-shaped pieces. The two "Dees"...
Study smarter with the SolutionInn App