Question

. Please write the SQL statements required to create the following relations, including appropriate versions of all primary and foreign key integrity constraints. (10 points)

Books (isbn:CHAR(10), title: CHAR(20), author: CHAR(80), qty_in_stock: INTEGER, price: REAL, year_published: INTEGER)

Customers (cid:INTEGER, cname: CHAR(80), address: CHAR(200))

Orders (ordernum:INTEGER, cid:INTEGER, order_date: DATE, cardnum:CHAR(16))

Orderlists (ordernum:INTEGER, isbn:CHAR(10), qty:INTEGER, ship_date:DATE)

Answer 1

create database book_store;
use book store;
 

create table Books(isbn CHAR(10),title CHAR(20),author CHAR(80),qty_in_stock int,price REAL,year_published int);

insert into Books values('aaaaa','ABC','ggh',10,10000,2011)

alter table Books add primary key(isbn);

desc Books;



create table customers(cid int,cname VARCHAR(80),address VARCHAR(200));

insert into customers values(1,'Alex','xahsdsad');

alter table customers add primary key(cid);

desc customers;




create table orders(ordernum int,cid int,order_date DATE,card_num VARCHAR(16));

insert into orders values(444,1970,11/06/20,'xxxxxxxxx')

alter table orders add primary key(ordernum);

desc orders;


create table orderlist(ordernum int,isbn CHAR(10),qty int,ship_date DATE);

insert into orderlist values(444,'aaaaa'10,12/06/20);

alter table orderlist add foreign key(ordernum) references ordernum(cid);





select * from table_name;


example: select * from table Books;

 

Hello student you can insert more records in the table as you required , if you have any query related to this sql query please feel free to contact me .

Thankyou.