2. SQL programming Assume the following Logical Design of a database (a relation

Question

2. SQL programming Assume the following Logical Design of a database (a relational database schema) is given. It describes customers (schema CUSTOMER) who can place orders (schema SALES ORDER) for one of their accounts (schema ACCOUNT) Attribute cuId in schema SALES ORDER specifies the customer who placed the order. Each order involves products (schema INVOLVES PRODUCT). The details of products are described in schema PRODUCT CUSTOMER (culd,fName, 1Name,city,postcode,street,phone) primary key culd ACCOUNT (sortCode accNo, address,creditLimit,customer, type,) primary key (sortCode,accNo) foreign key customer REFERENCES CUSTOMER(cuId) PRODUCT (prodNo, price,cost,description) primary key prodNo SALES_ORDER (orderNo,orderDate, deliveryDate,cuId,sortCode,accNo) rimary key orderNo foreign key culd REFERENCES CUSTOMER (cuId) foreign key (sortCode,accNo) REFERENCES ACCOUNT (sortCode, accNo) INVOLVES_PRODUCT(orderNo,prodNo, status, quantity) primary key (orderNo, prodNo) foreign key orderNo REFERENCES SALES ORDER (orderNo) foreign key prodNo REFERENCES PRODUCT (prodNo) For this question all SQL code you provide must be MySQL 5.1 compliant. If not mentioned otherwise, use attribute names as column headings. All queries must consist of one statement only but may contain nested queries You can assume that tables for all schemas but ACCOUNT exist. (a) Delete the ACCOUNT table [2 marks] (b) Create a new ACCOUNT table according to the schema above, assuming that table CUSTOMER has a primary key of type CHAR (10). Implement the . sortCode is a positive integer number not larger than 4,294,967,295; . address is a string of variable length not longer than 60 characters . creditLimit should be a decimal number with two decimal places following requirements accNo is a positive integer number not larger than 16,777,215; and should always have a value; after and six before the decimal point and can be negative;

Explanation / Answer

a> DROP TABLE PRODUCT;

b> CREATE TABLE PRODUCT(CUSTOMER VARCHAR(10), SORTCODE INT, ACCNO MEDIUMINT, ADDRESS VARCHAR(60), CREDITLIMIT NUMERIC(8,2), PRIMARY KEY(CUSTOMER))

c> ALTER TABLE PRODUCT ADD SUPPLIER_ID VARCHAR(10);

d> UPDATE PRODUCT SET price = (price*price) WHERE prodNo='1860'

Related Questions

Navigate

• Browse (All)
• Browse 2
• Subjects

---

---

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