SQL Help Use the file to create the initial database and tables. Require Using C

Question

SQL Help

Use the file to create the initial database and tables.

Require

Using Create

1. Create a table that implements the many-to-many relationship between titles and authors. Include a field to track the royalty share the author receives for a title (must be between 0 and 100%).

Using Alter

Modify the Titles table:

2. Add a field indicating whether there is a contract with an author for this book. This field should default to true.

3. Add a field for the amount of advance payment made to author(s); advance cannot be a negative number; an advance cannot be made if there is not a contract.

4. Add a field for Projected Sales; projected sales cannot be negative or zero.

5. Add a field for Notes.

Expand the Database

Add a table to identify the Sales of books to stores.

6. Add a text field to identify each store by four characters; this is required.

7. Add a field to store up to 20 characters to capture the Purchase Order number; this is required.

8. Add a field to capture the date of the sale, defaulting to today.

Add a table for Sales Details; this is a join table between Sales and Titles.

9. Add fields to serve as foreign keys as appropriate.

10. Make the combination of foreign key values the primary key.

11. Add a field to track the quantity ordered; this is required and must be greater than zero.

12. Add a field to track the quantity shipped; this is required and must be greater than zero.

13. Add a field to track the shipping date; date must be on or before today and after January 1, 1998.

create database DDLPractice use DDLPractice create table authors au id char (11) not null primary key, au_lname varchar (40) not null, au_fname varchar(20) not null, phone char(12) null, address varchar (40) null, city varchar(20) null, au_state char(2) null, zip char(5) null create table publishers pub_id char (4) not null primary key, pub_name varchar (40) null, address varchar (40) null, city varchar(20) null, pub_state char (2) null create table titles title_id char(6) not null primary key, title varchar (80) not null, title_type char (12) nul1, pub_id char (4) null references publishers (pub_id) price money null, pubdate smalldatetime null

Explanation / Answer

==================royalty_share table====================

This query is used to create royalty_share percentage table for author. This table has

many-to-many relation with both author and titles table. Both table are joined with foreign key

au_id and title_id respectively.

use DDLPractice;

create table royalty_share(

share_id char(4) not null primary key,

royalty_share_percentage int null,

title_id char(11) null

REFERENCES titles(title_id),

au_id char(11) null

REFERENCES authors(au_id)

);

=============================titles table====================================

This is query is used to alter titles table to add is_contract, advance_amount, projected_sales

and notes columns.

use DDLPractice;

alter table titles

add column is_contract bit default 0,

add column advance_amount decimal(20,2) CHECK(advance_amount > 0),

add column projected_sales decimal(20,2) CHECK(projected_sales > 0),

add column notes varchar(80) null;

=========================sales table========================================

This query is used to sale of book from the store. This will store store name, order_number and sale_date of the book.

use DDLPractice;

create table sales (

sales_id char(12) not null primary key,

store char(4) not null,

order_number varchar(20) not null,

sale_date datatime default CURRENT_TIMESTAMP

);

===========================sales_details table=======================================

This query is used to store sales_details. This table has been joined with sales and titles table with sales_id &

title_id foreign key respectively.

use DDLPractice;

create table sales_details(

s_detail_id char(12) not null primary key,

sales_id char(12) null

REFERENCES sales(sales_id),

title_id char(11) null

REFERENCES titles(title_id),

quantity_ordered int check(quantity_ordered >0),

quantity_shipped int check(quantity_shipped >0),

shipping_date DATETIME check(shipping_date BETWEEN (DATEADD(yyyy, -2, CURRENT_TIMESTAMP)) and (CURRENT_TIMESTAMP))

);

=======================================================================================================

Note: Help us improve our solution quality.Please rate this solution by pressing like button and if you have any queries please comment in the comment section

Related Questions

Navigate

• Browse (All)
• Browse S
• Subjects

---

---

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.