Question: Consider the ZAGI database from the textbook. The ER diagram and relational schema are on page
Question:
Question:
Consider the ZAGI database from the textbook. The ER diagram and relational schema are on page 77 in Figure 3.32. Sample data is on page 77 in Figure 3.33. (Use the SQL to create the database)
Database:
Here is the SQL To Create the database:
customer -
CREATE TABLE `customer` ( `customerid` char(7) NOT NULL, `customername` varchar(15) NOT NULL, `customerzip` char(5) NOT NULL, PRIMARY KEY (`customerid`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
region -
CREATE TABLE `region` ( `regionid` char(1) NOT NULL, `regionname` varchar(25) NOT NULL, PRIMARY KEY (`regionid`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
store -
CREATE TABLE `store` ( `storeid` varchar(3) NOT NULL, `storezip` char(5) NOT NULL, `regionid` char(1) NOT NULL, PRIMARY KEY (`storeid`), KEY `regionid` (`regionid`), CONSTRAINT `store_ibfk_1` FOREIGN KEY (`regionid`) REFERENCES `region` (`regionid`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
salestransaction -
CREATE TABLE `salestransaction` ( `tid` varchar(8) NOT NULL, `customerid` char(7) NOT NULL, `storeid` varchar(3) NOT NULL, `tdate` date NOT NULL, PRIMARY KEY (`tid`), KEY `customerid` (`customerid`), KEY `storeid` (`storeid`), CONSTRAINT `salestransaction_ibfk_1` FOREIGN KEY (`customerid`) REFERENCES `customer` (`customerid`), CONSTRAINT `salestransaction_ibfk_2` FOREIGN KEY (`storeid`) REFERENCES `store` (`storeid`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
ER Diagram:
Relational DB schema:
1. For this question, Consider the following SQL query:
SELECT CustomerName, RegionName
FROM region R, store S, salestransaction T, customer C
WHERE C.CustomerID = T.CustomerID
AND T.StoreID = S.StoreID
AND S.RegionID = R.RegionID
ORDER BY CustomerName
Using the data from the textbook, what is the result of the query? (Write the result in table format, complete with column headings.) (Hint: There will be between 3 and 5 records.)
2. Explain the meaning of the query in the previous question (the one that starts SELECT CustomerName, RegionName) in your own words.
3. Write an SQL query that joins the PRODUCT and CATEGORY tables and displays only ProductName, ProductPrice, and CategoryName.
4. Explain the meaning of the query in the previous question (the one that joins PRODUCT and CATEGORY) in your own words.
5. Write an SQL query that displays, for a given store's ID number (e.g. S2), for all the transactions that occurred at that store, the date of the transaction and a list of the IDs of products sold in that transaction with the number of items of that product.
6. Suppose the DBMS that is storing the ZAGI database has a username/password authentication system with a user account for each store manager. Explain the steps that a DB administrator might take to allow access to the data described in the previous question only to the store's manager. (Each store manager can only see the information for transactions that occurred at their store.)
Modern Systems Analysis And Design
ISBN: 9780134204925
8th Edition
Authors: Joseph Valacich, Joey George