Homework Help is Here – Start Your Trial Now!
Engineering
Computer Science
Write SQL code for the following design schema of three tables. The tables should have the following properties: sales table has a primary key on SalesNumber and it is set to AUTO-INCREMENT. products table has a primary key on number and it is set to AUTO-INCREMENT, they is an unique index on prodid salesdetails table has a primary key on number and it is set to AUTO-INCREMENT, they is an index on prodid.  They is a one-to-many relationship between the salesdetails.SalesNumber and sales.SalesNumber fields.  There is a one-to-many relationship between the products.prodid and salesdetails.prodid fields.  There is to be a delete constraint on the product.prodid field a record in the product table should not be allowed to be deleted if they exist a matching prodid in the salesdetails table.  Primary and foreign keys should be implemented in the appropriate tables.  Referential Integrity should be enforce at the database level, where appropriate for example, if a sales record is deleted all associated records in the saledetails table should be deleted as well, etc.

Write SQL code for the following design schema of three tables. The tables should have the following properties: sales table has a primary key on SalesNumber and it is set to AUTO-INCREMENT. products table has a primary key on number and it is set to AUTO-INCREMENT, they is an unique index on prodid salesdetails table has a primary key on number and it is set to AUTO-INCREMENT, they is an index on prodid.  They is a one-to-many relationship between the salesdetails.SalesNumber and sales.SalesNumber fields.  There is a one-to-many relationship between the products.prodid and salesdetails.prodid fields.  There is to be a delete constraint on the product.prodid field a record in the product table should not be allowed to be deleted if they exist a matching prodid in the salesdetails table.  Primary and foreign keys should be implemented in the appropriate tables.  Referential Integrity should be enforce at the database level, where appropriate for example, if a sales record is deleted all associated records in the saledetails table should be deleted as well, etc.

Programming Logic & Design Comprehensive
Programming Logic & Design Comprehensive
9th Edition
ISBN:9781337669405
Author:FARRELL
Publisher:FARRELL
Chapter8: Advanced Data Handling Concepts
Section: Chapter Questions
Problem 17RQ
See similar textbooks
icon
Related questions
Question
100%

Write SQL code for the following design schema of three tables. The tables should have the following properties:

sales table has a primary key on SalesNumber and it is set to AUTO-INCREMENT.
products table has a primary key on number and it is set to AUTO-INCREMENT, they is an unique index on prodid
salesdetails table has a primary key on number and it is set to AUTO-INCREMENT, they is an index on prodid. 
They is a one-to-many relationship between the salesdetails.SalesNumber and
sales.SalesNumber fields.
 There is a one-to-many relationship between the products.prodid and salesdetails.prodid fields.
 There is to be a delete constraint on the product.prodid field a record in the product table should not be allowed to be deleted if they exist a matching prodid in the salesdetails table.
 Primary and foreign keys should be implemented in the appropriate tables.
 Referential Integrity should be enforce at the database level, where appropriate for example, if a sales record is deleted all associated records in the saledetails table should be deleted as well, etc. 

swen2005 sales SalesNumber: int(10) SalesDate : date # Sales Total : decimal(7,2) V swen2005 salesdetails number: int(10) # SalesNumber : int(10) Ⓒ prodid: varchar(20) # price : decimal(7,2) # qty: int(10) swen2005 products number : int(11) prodid: varchar(20) Ⓒ prodname : varchar(30) # price : decimal(10,2) #onhand int(11)
Transcribed Image Text:swen2005 sales SalesNumber: int(10) SalesDate : date # Sales Total : decimal(7,2) V swen2005 salesdetails number: int(10) # SalesNumber : int(10) Ⓒ prodid: varchar(20) # price : decimal(7,2) # qty: int(10) swen2005 products number : int(11) prodid: varchar(20) Ⓒ prodname : varchar(30) # price : decimal(10,2) #onhand int(11)
Expert Solution
steps

Step by step

Solved in 4 steps

SEE SOLUTIONCheck out a sample Q&A here
Blurred answer
Knowledge Booster
SQL Query
Learn more about
Need a deep-dive on the concept behind this application? Look no further. Learn more about this topic, computer-science and related others by exploring similar questions and additional content below.
Similar questions
  • SEE MORE QUESTIONS
Recommended textbooks for you
Programming Logic & Design Comprehensive
Programming Logic & Design Comprehensive
Computer Science
ISBN:
9781337669405
Author:
FARRELL
Publisher:
Cengage
Enhanced Discovering Computers 2017 (Shelly Cashm…
Enhanced Discovering Computers 2017 (Shelly Cashm…
Computer Science
ISBN:
9781305657458
Author:
Misty E. Vermaat, Susan L. Sebok, Steven M. Freund, Mark Frydenberg, Jennifer T. Campbell
Publisher:
Cengage Learning
Np Ms Office 365/Excel 2016 I Ntermed
Np Ms Office 365/Excel 2016 I Ntermed
Computer Science
ISBN:
9781337508841
Author:
Carey
Publisher:
Cengage
SEE MORE TEXTBOOKS