For all tasks below, you are MUST write SQL code in the SQL query view in...
Fantastic news! We've Found the answer you've been seeking!
Question:
Transcribed Image Text:
For all tasks below, you are MUST write SQL code in the SQL query view in MYSQL. Examine the following two relations which are part of a database for a company selling horse accessories. Product Product No (PK) P001 P002 P003 P004 P005 Warehouse Warehouse No (PK) W010 W020 W015 W040 W025 Description Riding Hat Jodhpurs Back Support Riding Boots Jacket City London Cambridge York Bedford Nottingham Price £50 £20 £60 £100 £150 Address 10 New Road 23 Hills Road 456 Long Road 45 Old Street 56 Bar Hill Assumptions: 1. The products sold by the company are stored in Warehouses. 2. Each warehouse can store many products. 3. Each product can be stored in many warehouses. NOTE: The codes to create the above tables are given in the HorseDB.sg! file. Download the file and execute it. You will need the Product and Warehouse tables before you create the Stock table: Task 1: Create a Stock table which links the product table with the warehouse table and contains the attributes given in the table below. You are expected to give each attribute a relevant name and data type, and to correctly define the primary key and foreign keys: Stock Attribute Attribute 1 Attribute 2 Attribute 3 Attribute 4 Attribute 5 Attribute 6 Description Code used to uniquely identify the Stock. Link to the Product Table. Links to the Warehouse Table. Stock quantity. Aisle Number where stock located in warehouse. Bin Number where stock is located in aisle. Data Type String-5 characters String-5 characters String-5 characters Integer String-5 characters Integer Task 2: Populate the table with at least 5 rows of data. Make sure the data is realistic and the Entity and Referential Integrity Constraint are not violated. You will be marked on the following GOALS: These GOALs are all individual and worth 1 mark. 19.1. The correct use of the CREATE TABLE statement. 19.2. Relevant attribute names. 19.3 Relevant domain types. 19.4. Correctly defined Primary Key. 19.5 Correctly define the Foreign Keys. 19.6. The correct use of the INSERT INTO statement. 19.7. The correct use of the VALUES statement. 19.8. Relevant data inserted into table. 19.9. Demonstrate that the Entity Integrity constraint is not violated. 19.10. Demonstrate that the Referential Integrity constraint is not violated. For these GOALS, the entry into your portfolio MUST contain the SQL code and a screenshot of the resultant table after the code is run. For all tasks below, you are MUST write SQL code in the SQL query view in MYSQL. Examine the following two relations which are part of a database for a company selling horse accessories. Product Product No (PK) P001 P002 P003 P004 P005 Warehouse Warehouse No (PK) W010 W020 W015 W040 W025 Description Riding Hat Jodhpurs Back Support Riding Boots Jacket City London Cambridge York Bedford Nottingham Price £50 £20 £60 £100 £150 Address 10 New Road 23 Hills Road 456 Long Road 45 Old Street 56 Bar Hill Assumptions: 1. The products sold by the company are stored in Warehouses. 2. Each warehouse can store many products. 3. Each product can be stored in many warehouses. NOTE: The codes to create the above tables are given in the HorseDB.sg! file. Download the file and execute it. You will need the Product and Warehouse tables before you create the Stock table: Task 1: Create a Stock table which links the product table with the warehouse table and contains the attributes given in the table below. You are expected to give each attribute a relevant name and data type, and to correctly define the primary key and foreign keys: Stock Attribute Attribute 1 Attribute 2 Attribute 3 Attribute 4 Attribute 5 Attribute 6 Description Code used to uniquely identify the Stock. Link to the Product Table. Links to the Warehouse Table. Stock quantity. Aisle Number where stock located in warehouse. Bin Number where stock is located in aisle. Data Type String-5 characters String-5 characters String-5 characters Integer String-5 characters Integer Task 2: Populate the table with at least 5 rows of data. Make sure the data is realistic and the Entity and Referential Integrity Constraint are not violated. You will be marked on the following GOALS: These GOALs are all individual and worth 1 mark. 19.1. The correct use of the CREATE TABLE statement. 19.2. Relevant attribute names. 19.3 Relevant domain types. 19.4. Correctly defined Primary Key. 19.5 Correctly define the Foreign Keys. 19.6. The correct use of the INSERT INTO statement. 19.7. The correct use of the VALUES statement. 19.8. Relevant data inserted into table. 19.9. Demonstrate that the Entity Integrity constraint is not violated. 19.10. Demonstrate that the Referential Integrity constraint is not violated. For these GOALS, the entry into your portfolio MUST contain the SQL code and a screenshot of the resultant table after the code is run.
Expert Answer:
Related Book For
Posted Date:
Students also viewed these databases questions
-
Refer to Exhibit 34-2. The U.S. demand and supply for a good are shown. Under a policy of free trade, the world price is PW. At this price, what quantity of this good do U.S. consumers buy from U.S....
-
Planning is one of the most important management functions in any business. A front office managers first step in planning should involve determine the departments goals. Planning also includes...
-
The Crazy Eddie fraud may appear smaller and gentler than the massive billion-dollar frauds exposed in recent times, such as Bernie Madoffs Ponzi scheme, frauds in the subprime mortgage market, the...
-
When they hear the name Credit Suisse, my grandchildren will likely have to google (or whichever search engine exists then) these two words. For most of my contemporaries here in Switzerland, Credit...
-
In order to determine the mass moment of inertia of a flywheel of radius 1.5 ft, a 20-lb block is attached to a wire that is wrapped around the flywheel. The block is released and is observed to fall...
-
In 2020 hornby used innovation and made changes to its promotional methods to make the company profitable. Evaluate these two options and recommend which one was likely to have been more effective at...
-
These data consist of the 503 daily returns for the calendar years 2005 and 2006 of the S\&P value-weighted index. (The data file contains additional years - this exercise uses only 2005 and 2006...
-
The beginning inventory and data on purchases and sales for a three-month period are shown in Problem 7-1B. Instructions 1. Record the inventory, purchases, and cost of merchandise sold data in a...
-
You placed $6,599 in a savings account today that earns an annual interest rate of 3 percent compounded annually. How much you will have in this account at the end of 2 years?
-
Asbat Pharmaceuticals (Asbat) is a leading pharmaceutical company that has been in existence for 22 years. Asbat has a calendar year-end and is audited annually. Asbat only operates in the United...
-
You deposit $350 into the bank account at the end of each year (ordinary annuity), 4 years in a row. The interest rate on the deposit is 4% per annum, accrued annually. A) Calculate the Future Value...
-
You purchased 10 ASD June 45 puts and the company declares a 10% stock dividend. How are the terms of the option contract then changed?
-
Does China benefit from a weak currency?Does Us benefit from a strong currency? Why? Please explain
-
ABC Inc. curently has a share price of $44. If they are paying a dividend of $1.13, with a cum dividend date of November 1, would would you expect their share price to be on November 2nd? Please give...
-
4) Champagne, Inc., had revenues of $13 million, cash operating expenses of $9 million, and depreciation and amortization of $2 million during 2008. The firm purchased $650,000 of equipment during...
-
In November 2020 you entered into four May 2021 long futures contracts for crude oil (1,000 barrels per contract) for $65.00 per barrel. If the futures price was $70.00 per barrel on December 31,...
-
Item 5 Part A What dissolved species are present in a solution of KCN? Express your answers as ion separated by a comma. Submit Part B Give Up What dissolved species are present in a solution of...
-
How does the organizational structure of an MNC influence its strategy implementation?
-
Why is the study of database technology important?
-
What utility is used to manage ODBC?
-
What tool(s) can be used to create a script?
-
Net profit is calculated in the (A) Trading account (B) Profit and loss account (C) Trial balance (D) Statement of financial position
-
To find the value of closing inventory at the end of a period we (A) Do this by physically counting the inventory (i.e. stocktaking) (B) Look in the inventory account (C) Deduct opening inventory...
-
Gross profit is (A) Excess of sales over cost of goods sold (B) Sales less purchases (C) Cost of goods sold + opening inventory (D) Net profit less expenses of the period
Study smarter with the SolutionInn App