17/10/2022
The conceptual design for this database includes three tables:
Product(manufacturer_ID, model_nbr, group_ID, type_code)
PC(code, model, cpu_speed, ram_size, hd_size, price)
Printer(code, manufacturer_ID, model, ptr_type, price)
The Product table contains data on the manufacturer, model number, the ID number of the product group ('PC', 'Laptop', or 'Printer') and the code number for the type of device (ink jet, laser, desktop, portable, hand-held, etc.).
It is assumed that model numbers in the Product table are unique for all makers and product types.
Each personal computer in the PC table is uniquely identified by a serialized code and its model number (foreign key referring to the Product table), processor speed (in MHz) in the cpu_speed field, RAM size in Mb, hard disk drive capacity (in Gb), and its current sale price.
Each printer in the Printer table is identified by a serialized code number and its model number and manufacturer. Attributes for the printer type ('Laser', 'Jet', or 'Matrix'), and current sale price are also included in the Printer table.
Identify the primary and foreign keys, if any, in these tables.
Write the T-SQL statements to create these tables in the “stock” schema and the “hardgoods” database.
If there are any flaws in the design of the database tables, explain the issue to be corrected and include the correction in your code statements.
Write T-SQL queries to list:
the PCs and Printers (on separate queries) by product and manufacturer
all a manufacturer’s products (printer and PCs) in stock
all items in stock by sale price descending.
You are responsible for only the T-SQL statements for the above. You are not required to construct the database and process your statements. However, if you do (to test the statements), include a capture of the results for a few extra points.