uni

University stuff
git clone git://git.margiolis.net/uni.git
Log | Files | Refs | README | LICENSE

commit 2c673a109715ba290bdff05322c69f1b726e3c21
parent 60ca8f39798dc55d848faaa024536a49cc3102ec
Author: Christos Margiolis <christos@margiolis.net>
Date:   Tue, 11 Jan 2022 21:56:58 +0200

sql assignment done, python one is so bad

Diffstat:
Apython_ai/team.py | 150+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
Dsql_databases2/ex1.sql | 83-------------------------------------------------------------------------------
Asql_databases2/insurance.db | 0
Asql_databases2/insurance.sql | 151++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
4 files changed, 301 insertions(+), 83 deletions(-)

diff --git a/python_ai/team.py b/python_ai/team.py @@ -0,0 +1,150 @@ +CAPACITY = 10 +FLOORS_TOTAL = 5 #total number of FLOORS_TOTAL + +#WORLD OF PROBLEM:elevator capacity, floors, residents +#CONSTRAINTS: +#1.The elevator has maximum capacity of 10 +#2.The total number of FLOORS_TOTAL is 5 +#3.In the beginning the elevator is empty in floor 0 +#4.Each floor has a specific number of residents in the beginning + +# function to point where the elevator goes next +def go_to_floor(state, floor): + # if the elevator position is less than its capacity and the elevator + # is not on the ground then the new state enters the list of states + if floor == 0: + new_state = list(state).copy() + new_state[0] = 0 + new_state[FLOORS_TOTAL] = 0 + return tuple(new_state) + if state[FLOORS_TOTAL] < CAPACITY and state[floor] > 0: + new_state = list(state).copy() + if state[floor] > CAPACITY - state[FLOORS_TOTAL]: + # we exclude the last floor + new_state[floor] = state[floor] + state[FLOORS_TOTAL] - CAPACITY + new_state[0] = floor + new_state[FLOORS_TOTAL] = CAPACITY + else: + new_state[floor] = 0 + new_state[0] = floor + new_state[FLOORS_TOTAL] = state[floor] + state[FLOORS_TOTAL] + return tuple(new_state) + else: + return None + +# state: ( elevator position={0,1,2,3,4}, floor1, floor2, floor3, floor4, elevator ) +# initial: ( 0, 12, 3, 7, 8, 0 ) +# final: ( 0, 0, 0, 0, 0, 0 ) +def bfs(initial_state, final_state): + visited = {} + frontier = [initial_state] + + while len(frontier) > 0: + # print(frontier) + state = frontier.pop(0) + print(state) + # if the elevator has reached the final state all previously visited + # states are printed + if state == final_state: + print(f"\n\nBFS: states visited = {len(visited)}") + return final_state + visited[state] = 1 + #the state must be immutable in order to become key to the dict + # print(state) + + # anakalypsh paidiwn + current_floor = state[0] + current_capacity = state[-1] + + if current_capacity == CAPACITY or current_floor == FLOORS_TOTAL - 1: + new_state = go_to_floor(state, 0) + #putting the new state to the frontier + if new_state not in visited: + frontier.append(new_state) + continue + + return_to_0 = True + + for floor in range(1, FLOORS_TOTAL): + # in case the elevator is over the ground and the floor + # (in frontier) is over the current position of the elevator go up + if floor <= current_floor: + continue + # in case the floor (in frontier) is not the last and the elevator + # position is on the ground go up + elif floor < FLOORS_TOTAL - 1 and state[floor] == 0: + continue + # in case the last floor is visited,the next floor in frontier is 4 and + # the elevator is not in 0 the new state is added to the list + elif (floor == FLOORS_TOTAL - 1 and state[FLOORS_TOTAL - 1] == 0 and current_floor != 0 and return_to_0): + new_state = go_to_floor(state, 0) + # in case the elevator carries less than 10 people and the next floor + # in frontier is over the current floor the elevator goes up + elif current_capacity < CAPACITY and floor > current_floor: + return_to_0 = False + new_state = go_to_floor(state, floor) + if new_state is None: + continue + # in case there is no new state continue + else: + continue + + # BFS: bazw paidia (new states) sto TELOS tou frontier + if new_state not in visited: + frontier.append(new_state) + return None + +def heuristic(state): + return sum(state) + +def bestfs(initial_state, final_state): + visited = {} + state = initial_state + + while state != final_state: + print(state) + # initialize available states + avail_states = [] + current_floor = state[0] + current_capacity = state[-1] + + # test all states + for floor in range(1, FLOORS_TOTAL): + if floor == current_floor: + continue + new_state = go_to_floor(state, floor) + if new_state == None: + continue + # only add states not already visited + if new_state not in visited: + avail_states.append(new_state) + # mark state as visited + visited[new_state] = 1 + + # sort combinations according to heuristic value + avail_states.sort(key=heuristic) + + if avail_states: + # get best path + state = avail_states.pop(0) + avail_states.clear() + + if current_capacity == CAPACITY or current_floor == FLOORS_TOTAL - 1: + state = go_to_floor(state, 0) + + print(f"\n\nBestFS: states visited: {len(visited)}") + return final_state + +def main(): + initial = (0, 12, 3, 7, 8, 0) + final = (0, 0, 0, 0, 0, 0) + method = "BestFS" + + if method == "BFS": + state = bfs(initial, final) + elif method == "BestFS": + state = bestfs(initial, final) + print(state) + +if __name__ == "__main__": + main() diff --git a/sql_databases2/ex1.sql b/sql_databases2/ex1.sql @@ -1,83 +0,0 @@ --- sqlite3 -- -PRAGMA foreign_keys = ON; -.schema -.header on -.mode column - --- Part 1 -- -CREATE TABLE INSURANCE_COVERS ( - COVER_NO INT(2) NOT NULL, - DESC VARCHAR, - YEARLY_COST INT(2) NOT NULL, - MIN_VALID_MONTHS INT(2) NOT NULL, - PRIMARY KEY(COVER_NO) -); - -CREATE TABLE CUSTOMERS ( - FULL_NAME VARCHAR, - ADDRESS VARCHAR, - TEL VARCHAR, - VAT_NO VARCHAR, - TAX_OFFICE VARCHAR, - PRIMARY KEY(VAT_NO) -); - -CREATE TABLE CONTRACTS ( - COVER_NO INT(2) NOT NULL, - VAT_NO VARCHAR, - START_DATE DATE, - EXPIRE_DATE DATE, - FOREIGN KEY(VAT_NO) REFERENCES CUSTOMERS(VAT_NO), - FOREIGN KEY(COVER_NO) REFERENCES INSURANCE_COVERS(COVER_NO) -); - --- Part 2 -- -INSERT INTO INSURANCE_COVERS VALUES - (1, "Health Insurance", 200, 12), - (2, "Critical Illness Cover", 100, 12), - (3, "Home Insurance", 150, 24), - (4, "Car Insurance", 120, 12); - -INSERT INTO CUSTOMERS VALUES - ("Foo Bar", "Nice Street 4", "2101234567", "123456789", "Peristeri"), - ("Bob Baz", "Bad Avenue", "2113216549", "321654987", "Acharnes"); - -INSERT INTO CONTRACTS VALUES - (1, "123456789", "2021-03-02", "2022-03-02"), - (2, "123456789", "2020-12-01", "2021-12-01"), - (3, "321654987", "2020-05-10", "2022-05-10"), - (3, "123456789", "2020-07-11", "2022-07-11"); - --- Part 4 -- -CREATE VIEW info AS - SELECT FULL_NAME "Customer name", - DESC AS "Coverage", - VAT_NO as "VAT number", - START_DATE AS "Start date", - EXPIRE_DATE AS "Expiration date" - FROM CUSTOMERS - INNER JOIN CONTRACTS USING (VAT_NO), - INSURANCE_COVERS USING(COVER_NO) - ORDER BY FULL_NAME; - -CREATE VIEW updatable_info AS - SELECT FULL_NAME, VAT_NO, ADDRESS, TEL, TAX_OFFICE - FROM CUSTOMERS - ORDER BY FULL_NAME; - --- TODO: make trigger -- - --- Part 5 -- -SELECT INSURANCE_COVERS.DESC AS "Contract", - COUNT(CONTRACTS.COVER_NO) AS "Number of contracts" - FROM CONTRACTS - INNER JOIN INSURANCE_COVERS USING(COVER_NO) - GROUP BY CONTRACTS.COVER_NO; - -SELECT FULL_NAME AS "Customer", - SUM(YEARLY_COST) AS "Total contract cost" - FROM CONTRACTS - INNER JOIN INSURANCE_COVERS USING (COVER_NO) - INNER JOIN CUSTOMERS USING (VAT_NO) - GROUP BY VAT_NO - ORDER BY SUM(YEARLY_COST) DESC; diff --git a/sql_databases2/insurance.db b/sql_databases2/insurance.db Binary files differ. diff --git a/sql_databases2/insurance.sql b/sql_databases2/insurance.sql @@ -0,0 +1,151 @@ +-- -- sqlite3 +PRAGMA foreign_keys = ON; +.schema +.header on +.mode column + +-- Part 1 +CREATE TABLE insurance_covers ( + cover_no INT(2) NOT NULL, + desc VARCHAR, + yearly_cost INT(2) NOT NULL, + min_valid_months INT(2) NOT NULL, + PRIMARY KEY(cover_no) +); + +CREATE TABLE customers ( + full_name VARCHAR, + address VARCHAR, + tel VARCHAR, + vat_no VARCHAR, + tax_office VARCHAR, + PRIMARY KEY(vat_no) +); + +CREATE TABLE contracts ( + cover_no INT(2) NOT NULL, + vat_no VARCHAR, + start_date DATE, + expire_date DATE, + FOREIGN KEY(vat_no) REFERENCES customers(vat_no), + FOREIGN KEY(cover_no) REFERENCES insurance_covers(cover_no) +); + +-- Part 2 +INSERT INTO insurance_covers VALUES + (1, "Health Insurance", 200, 12), + (2, "Critical Illness Cover", 100, 12), + (3, "Home Insurance", 150, 24), + (4, "Car Insurance", 120, 12); + +INSERT INTO customers VALUES + ("Foo Bar", "Nice Street 4", "2101234567", "123456789", "Peristeri"), + ("Bob Baz", "Bad Avenue", "2113216549", "321654987", "Acharnes"); + +INSERT INTO contracts VALUES + (1, "123456789", "2021-03-02", "2022-03-02"), + (2, "123456789", "2020-12-01", "2021-12-01"), + (3, "321654987", "2020-05-10", "2022-05-10"), + (3, "123456789", "2020-07-11", "2022-07-11"); + +-- Part 3: diagram.png + +-- Part 4 +CREATE VIEW info AS + SELECT full_name "Customer name", + desc AS "Coverage", + vat_no as "VAT number", + start_date AS "Start date", + expire_date AS "Expiration date" + FROM customers + INNER JOIN contracts USING (vat_no), + insurance_covers USING(cover_no) + ORDER BY full_name; + +CREATE VIEW updatable_info AS + SELECT full_name, vat_no, address, tel, tax_office + FROM customers + ORDER BY full_name; + +SELECT * from info; + +-- Part 5 +SELECT insurance_covers.desc AS "Contract", + count(contracts.cover_no) AS "Number of contracts" + FROM contracts + INNER JOIN insurance_covers USING(cover_no) + GROUP BY contracts.cover_no; + +-- Make this a view so we can make things easier later. +CREATE VIEW total_cost_info AS + SELECT full_name AS "Customer", + sum(yearly_cost) AS "Total contract cost" + FROM contracts + INNER JOIN insurance_covers USING (cover_no) + INNER JOIN customers USING (vat_no) + GROUP BY vat_no + ORDER BY sum(yearly_cost) desc; + +SELECT * from total_cost_info; + +-- Part 6 +CREATE TRIGGER cost_of_contracts AFTER INSERT ON insurance_covers +BEGIN + UPDATE customers SET total = (SELECT sum(yearly_cost) FROM insurance_covers) + new.yearly_cost + WHERE customers.vat_no = new.vat_no; +END; + +INSERT INTO contracts VALUES (4, "123456789", "2022-05-10", "2023-05-10"); +SELECT * from total_cost_info; + +-- Part 7 (SQLite doesn't have CREATE FUNCTION so this part is commented out) +-- DELIMETER // +-- CREATE FUNCTION contract_duration(start DATE, end DATE) +-- RETURNS INT +-- DETERMINISTIC +-- BEGIN +-- RETURN datediff(end, start); +-- END // +-- DELIMETER ; +-- +-- Test function. +-- SELECT contract_duration( +-- SELECT start_date FROM contracts WHERE vat_no = "123456789", +-- SELECT expire_date FROM contracts WHERE vat_no = "123456789" +-- ); + + +-- Part 8 (SQLite doesn't have procedures either) +-- DELIMETER // +-- CREATE PROCEDURE active_contracts(vat_no_arg VARCHAR, date_arg DATE) +-- BEGIN +-- DECLARE start_row INT DEFAULT 0; +-- DECLARE end_row INT DEFAULT 0; +-- DECLARE p_vat_no VARCHAR, +-- DECLARE p_cover_no INT, +-- DECLARE p_start_date DATE, +-- DECLARE p_end_date DATE, +-- DECLARE contr_count INT DEFAULT 0; +-- DECLARE COST INT; +-- DECLARE pending_cost INT DEFAULT 0; +-- DECLARE contr_ptr CURSOR FOR SELECT * FROM contracts; +-- +-- SET start_row := 0; +-- SELECT count(*) FROM contracts INTO end_row; +-- OPEN contr_ptr; +-- WHILE (start_row < end_row) DO +-- FETCH contr_ptr INTO p_vat_no, p_cover_no, p_start_date, p_end_date; +-- SELECT yearly_cost FROM insurance_covers WHERE cover_no = p_cover_no INTO cost; +-- +-- IF (p_vat_no = vat_no_arg AND date_arg >= p_start_date AND date_arg <= p_end_date) THEN +-- SET contr_count := contr_count + 1; +-- SET pending_cost := pending_cost + cost; +-- END IF; +-- SET start_row := start_row + 1; +-- END WHILE; +-- SELECT contr_count AS "Active contracts", pending_cost/12 AS "Contract cost per month"; +-- END // +-- DELIMETER ; +-- +-- Test procedure. +-- CALL active_contracts("123456789", "2021-10-10");