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.)

Data tables REGION C T STORE S1 S2 S3 T111 T222 T333 SALESTRANSACTION T444 T555 tid regionid storeid CUSTOMER 1-2-333 2-3-444 3-4-555 1-2-333 2-3-444 1-2-333 3-4-555 2-3-444 customerid 60600 60605 35400 customerid Tina Tony Pam Chicagoland Tristate storezip S1 S2 S3 S3 S3 storeid customername regionname C regionid 2013-01-01 2013-01-01 2013-01-02 2013-01-02 2013-01-02 60137 60611 35401 tdate customerzip