INFS22007903 Assignment 5 Solution

The assignment is to create a supermarket database for our CS Store example. The database will be called CS_Store. Most of the tables are what you would expect from the videos on SQL (with a similar, but not always identical set of attributes). The twist compared to the slides (otherwise, if you could just follow the slides without thinking, it would be a bit too easy) is that we will also want to support logisitics. I.e. moving wares around the different shops.
As an aside, each question from 2-6 asks you to create a view, that you should be sure only has each line once (i.e. use DISTINCT! – that said, GROUP BY will automatically give you DISTINCT, even without specifying it, because of how GROUP BY works) and specifies how to sort it – it is necessary for how we grade it. Do make sure you do the latter, since it would be sad for you to lose points for not doing something relatively easy like that! Doing it this way ensures that each question has a unique correct output of the queries on both the test data at the end as well as the hidden set of test data (but there are multiple ways of doing all the queries) and grading will consists of checking that you get the right outputs (there are too many of you to do this by hand and it would lead to errors in grading if I did).
Format
The assignment should be done in .sql format (i.e. the output format from MySQLs workbench) – it is really just a basic text file with the SQL commands written in it and you could do it by writing the file directly, if you wish – I would suggest not to, but you could.
And each line should contain only the following:
1. CREATE TABLE statements for question 1 (8 in total)
Make sure that you can run the full file through MySQL when using the CS_Store database (starting with an empty CS_Store database) and after having done so, the CS_Store database should contain the tables and views required from the questions you solved (and perhaps some more views if you feel it would be convenient). This means that you should remove any statement that causes errors before handing in the assignment, because MySQL stops when it encounters an error (meaning that the last statements are not executed)! If you do not, you risk getting a far lower grade than otherwise (because the part of your hand-in after the first error will not be graded)...
Do not do the following: Any of the following should not be done:
• End by removing the database (i.e. DROP DATABASE CS_Store; or similar). It would be the same as handing in an empty file.
• Create comments like “------------". MySQL workbench will accept it, but the command line version of MySQL does not, which is what is used to check...
• Swap the columns in the created tables. Since the insert command does not state which columns they insert into, you will put the information in the wrong column and then get hard to understand issues when you solve the questions.

Question 1) (worth 16 points – 2 point for each table)
Make the following set of tables.
◼ Customers(birth_day, first_name, last_name, c_id)
◼ Employees(birth_day, first_name, last_name, e_id)
◼ Locations(address, l_id)
◼ Items(price_for_each, name)
◼ ItemsInTransactions(name*, t_id*, amount)
Question 2) (worth 15 points – 5 point for getting the right output on the test data and another 10 for the hidden data – see the beginning for more detail!)
SELECT * FROM DeniseTransactions;
when run on the CS_Store database (after inserting the test data at the end) should be:
number_of_transactions
2

Question 3) (worth 15 points – 5 point for getting the right output on the test data and another 10 for the hidden data – see the beginning for more detail!)
The view should be such that the output of
SELECT * FROM PeopleInShop;
when run on the CS_Store database (after inserting the test data at the end) should be:
birth_day first_name last_name
1990-07-03 Anita Taylor
1991-02-19 Finn Wilson
1998-08-12 Denise Davies
Question 4) (worth 15 points – 5 point for getting the right output on the test data and another for the hidden data – see the beginning for more detail!)
Find the value of each distinct transaction made. More precisely, you are asked to create a view TransactionValue, with t_id, value with the value being the sum of the values of the items (i.e. price for each of items times the amount of that item in the transaction) in the transaction, sorted by t_id ascending.
As an example, consider transaction 1 in the test data. It involves (according to ItemsInTransactions) 5 Garlic, 8 Bread, 1 Chicken and 1 Rice. The price of (from Items) of Garlic is 25, Bread is 200, Chicken is 450 and Rice is 200.
5 ⋅ 25 + 8 ⋅ 200 + 1 ⋅ 450 + 1 ⋅ 200 = 2125
The view should be such that the output of
SELECT * FROM TransactionValue;
when run on the CS_Store database (after inserting the test data at the end) should be:
t_id value
1 2375
2 2750
3 650
4 12175
5 2450
6 5300

Question 5) (worth 24 points – 5 point for getting the right output on the test data and another 19 points for the hidden data – see the beginning for more detail!)
HINTS: Try to make intermediate/extra views, one for how the item count change on specific dates and one for the mentioned dates. You can then make a combined answer from those.
The expected output is given at the end (but before the test data), since it is long and would distract.
Question 6) (worth 15 points – 5 point for getting the right output on the test data and another 10 for the hidden data – see the beginning for more detail!)
We want to know for each location whether it always had a feasible (i.e. non-negative) amount of each item. More precisely, create a view FeasibleLocations with l_id, feasible, where feasible should be 1 if the amount at all times in l_id for each item (according to ItemsOnDateAndLocation) were non-negative and otherwise be 0. Sort it by l_id ascending.
HINT: Use ItemsOnDateAndLocation in a subquery with Locations on the outside.
The view should be such that the output of
SELECT * FROM FeasibleLocations;
when run on the CS_Store database (after inserting the test data at the end) should be:
l_id feasible
1 1
2 0
3 0
Question 5 output)
The view you create in question 5 should be such that the output of
SELECT * FROM ItemsOnDateAndLocation;
when run on the CS_Store database (after inserting the test data at the end) should be:




Test data
-- Data about customers
INSERT INTO Customers VALUES('1983-02-11','Jamie','Johnson',1);
INSERT INTO Customers VALUES('1995-10-26','Birgit','Doe',2);
INSERT INTO Customers VALUES('1991-05-15','Finn','Smith',3);
INSERT INTO Customers VALUES('1990-07-03','Anita','Taylor',4);

-- Data about employees
INSERT INTO Employees VALUES('1964-12-01','Carla','Brown',1);
INSERT INTO Employees VALUES('1984-03-14','Bryan','Williams',2);
INSERT INTO Employees VALUES('1991-02-19','Finn','Wilson',3);
INSERT INTO Employees VALUES('1998-08-12','Denise','Davies',4);

-- Data about locations
INSERT INTO Locations VALUES('Park Road 7',1);
INSERT INTO Locations VALUES('Hill Street 2',2);
INSERT INTO Locations VALUES('Duckinfield Street 5',3);

-- Data about transactions

-- Data about items
INSERT INTO Items VALUES(200,'Bread');
INSERT INTO Items VALUES(100,'Lemonade');
INSERT INTO Items VALUES(100,'Banana');
INSERT INTO Items VALUES(200,'Rice');
INSERT INTO Items VALUES(150,'Grape');
INSERT INTO Items VALUES(450,'Chicken');
INSERT INTO Items VALUES(25,'Garlic');

-- Data about items brought into shop

-- Data about movement of items

-- Data about items in transactions
INSERT INTO ItemsInTransactions VALUES('Garlic',1,5);
INSERT INTO ItemsInTransactions VALUES('Bread',1,8);
INSERT INTO ItemsInTransactions VALUES('Chicken',1,1);
INSERT INTO ItemsInTransactions VALUES('Rice',1,1);
INSERT INTO ItemsInTransactions VALUES('Banana',2,3);
INSERT INTO ItemsInTransactions VALUES('Chicken',2,5);
INSERT INTO ItemsInTransactions VALUES('Rice',2,1);
INSERT INTO ItemsInTransactions VALUES('Rice',3,1);
INSERT INTO ItemsInTransactions VALUES('Chicken',3,1);
INSERT INTO ItemsInTransactions VALUES('Garlic',4,7);
INSERT INTO ItemsInTransactions VALUES('Grape',4,80);
INSERT INTO ItemsInTransactions VALUES('Lemonade',5,17);
INSERT INTO ItemsInTransactions VALUES('Grape',5,5);
INSERT INTO ItemsInTransactions VALUES('Lemonade',6,17);
INSERT INTO ItemsInTransactions VALUES('Grape',6,24);