10.1.3.1/20 10.1.2.1/24 R3 10.1.5.1/24 R1 SP1 AS1 le1 10.1.0.1/24 10.2.0.1/24 RB 10.2.3.1/24, 10.1.4.1/24 R10 SP2 AS2...

Transcribed Image Text:

10.1.3.1/20 10.1.2.1/24 R3 10.1.5.1/24 R1 SP1 AS1 le1 10.1.0.1/24 10.2.0.1/24 RB 10.2.3.1/24, 10.1.4.1/24 R10 SP2 AS2 102 192.168.55.1/24 R9 10.2.1.1/24 101 lo1 10.3.1.1/24] 10.3.0.1/24 Internet RS RA SP3 AS3 101 2.1/24 R7 RG lot 10.3.3.1/2 R11 10.3.5.1/24 Project: Tools rental store Section A - Setup Python for database connection Project overview The project includes two phases: o Phase #1: Database creation, which includes O Database creation O o Table creation o Table population o Data management: Queries (select), Insert, Delete Submission: Text files with extension *.sql with SQL statements, all required screenshots Phase #2: Python program to manage database Submission: Python script, demo by providing all required screenshots Phase #1 - Database conceptual design Data requirements The following data has to be managed for the tool rental store: customer information, tools information, rental information. This will require 5 tables. public.Customers OFname OLname varchar(10) varchar(10) varchar(20) integer Phone_number O Membership_date date Customer_id integer Customers_pk constraint Postal Code pubic.Tools O Title O Rental duration Tool id Tools_pk tools: varchar(15) Integer integer constraint Entities with attributes pk pk rentals pk> pk o Title o customers o o Postal code o Phone number o Membership date Tools_has_many_many_Tools_has_many_Jobs Tools_has_many_Rentals Name: first, last Customers_has_many_Rentals o Rental date p Return date Toolid Tools integer Job id Jobs integer public.Tools Jobs Tools_fk Jobs_fk Tools Jobs_pk constraint constraint constraint O Rental date Return_date Rental_id pk fk >> pk fk pubic. Rentals date date Jobs_has_many_many_Tools_has_many_Jobs pk fk fk integer Customer_id_Customers integer Toolid Tools integer Rentals pk Customers fk Tools_fk constraint constraint constraint pk f fk pk> fk fk public. Jobs Job_id integer Job_name varchar(20) Jobs_pk constraint pk Jobs that can require a given tool: to be modeled as a separate entity. For example, tool name - snake, job - plumming. O Rental duration in days pk 3. Service Provider 1 (SP1) Requirements (30 marks) a. Network 10.1.4.0/24 is a private network within SP1 and must not be advertised to any router that is not part of Service Provider 1. This network must be advertised within the IBGP domain of SP1 but must not be advertised to any other Service Provider. SP1 would prefer a simple solution that didn't require amending the configuration of more than one router. Partial marks will be awarded for implementing a solution that spans multiple routers. (10 marks) b. SP1 does not want any traffic destined for the 10.1.5.0.0/24 and 10.1.4.0/24 networks to travel over the direct link between R1 and R2. Such traffic must be routed through R3 to keep the direct link between R1 and R2 free. (10 marks) c. SP1 would like to reduce the number of routes it advertises to SP2 and SP3. Make sure that SP1 advertises a summary route in place of 10.1.0.0/24, 10.1.1.0/24, 10.1.2.0/24 and 10.1.3.0/24 to Service Providers 2 and 3. Select the most precise summary route you can build. The component subnets must be advertised to all routers within SP1 but only the summary routes should be advertised to SP2 and SP3 (10 marks) 1. Service Provider 2 (SP2) Requirements (16 marks) a. R10 has an uplink to a large Tier 1 Service Provider. This is represented in the simulation by lo2. SP2 needs R10 to advertise a default route to both R8 and R9. However, the default route should be withdrawn if the 192.168.55.0/24 network is unreachable. (8 marks) b. SP2 would like all traffic destined for the 10.1.5.0/24 network of SP1 to be routed through SP3 via R8 over its direct link to R4. If this path is unavailable then the traffic should be routed over the alternative path to SP3 via the R9 to R6 link. If SP2 has no valid path to the 10.1.5.0/24 subnet through SP3, it may use any available alternative path. (8 marks) 2. Service Provider 3 (SP3) Requirements (24 marks) a. SP3 is expected to grow rapidly in the near future and is concerned that a full mesh of IBGP neighbour relationships will be unsustainable. SP3 would like to make use of Route Reflectors to reduce the overall number of iBGP neighbour relationships required within the SP3 network. Design and implement a solution that makes use of one or more Route Reflectors. Your design should: 1. Eliminate any single points of failure caused by the deployment of Route Reflectors. 2. Reduce the number of IBGP neighbour relationships within SP3 3. Prevent any potential routing loops from occurring and reduce the Control Plane overhead as much as possible for all iBGP routers within SP3 (12 marks) b. SP3 would like to make use of the default route advertised by SP2. Default traffic should exit SP3 via: 1. The direct link between R4 and R8. 2. If the link between R4 and R8 is down then default traffic should exit SP3 via the direct link between R6 and R9. 3. If this second link is also down then default traffic should exit SP3 via any available path through SP1. (12 marks) 4. Documentation (30 marks) Answer the following questions about your implementation. You are encouraged to include code snippets to support your answers where appropriate. There is no word count limit. If a solution is only partially working then explain what works and what doesn't work. A partially operational solution may be awarded more marks if it is clear that you are aware that it is not working as required. Identify any sections that you have not attempted. Please note that marks may be awarded for providing a valid strategy to an incomplete section. a. Explain how you deployed a default route (1a). Your answer should explain how you linked the default route advertisement to the presence of a viable route to the 192.168.55.0/24 network. (5 marks) b. Document your solution to (2a). Your answer should include the following: i. Operation of a Route Reflector and its role in simplifying an IBGP network. (3 marks) ii. Issues that may arise from the use of Route Reflectors and strategies that may be used to mitigate these issues. (3 marks) iii. Justification for your Route Reflector deployment strategy. (4 marks) c. Explain how you prevented subnet 10.1.4.0/24 from being advertised to SP2 and SP3 (3a). There are several ways to solve this problem. Suggest one other strategy and provide justification for the solution you implemented. (6 marks) d. Document your solution to (3b). Your answer should include the following: i. An overview of the strategy used to solve this problem. ii. A brief review of the tests used to verify that your solution worked (5 marks) e. Provide command listings for the BGP configuration of each router and a fully labelled topology diagram that shows the IP addresses and port labels of each interface (you may include the same diagram you submitted as part of Portfolio Element 1, though you are advised to improve it if it was awarded a low mark). (4 marks) Why 5 tables? Relationship between tools and jobs is many-to-many. Use "associative pattern" (ERD lecture) to resolve many-to-many relationships. Out of tools and jobs we will have 3 tables: tools, jobs, and tool_jobs. Useful concepts Recall good naming conventions: it is good idea to name your primary key (PK) as "id". If some other table references this PK - give it name "tablename_id". For example, tool_id in table tool_rentals would reference PK id in table tools. Auto-incrementing (surrogate) primary key: To facilitate primary key management use the serial "type" instead of the integer data type when declaring a primary key: it is equivalent to an unsigned integer, not null, and autoincrementing; it is based on the setup of a sequence. Another advantage of the "surrogate" key is that your primary key will be made up only of one attribute, otherwise, for example, in table that describes tool rentals you will have to create a composite (key made up of several attributes) primary key made up of tool_id (foreign key to attribute id of tools table) and customer_id (foreign key to attribute id of customers table). O Example: CREATE TABLE tools ( tool_id serial PRIMARY KEY, tool_name varchar (200) not null unique, rental_days smallint not null ); Foreign key reference: Note that foreign key that references SERIAL primary key in the related table should be declared with integer data type. Inserting tuples with primary keys: If you are using serial in your INSERT statement you do not need to specify value corresponding to attribute declared with SERIAL, the attribute will obtain value automatically based on the next available sequence number fieldN) o Syntax: INSERT INTO table (fieldl, field2, VALUES (valuel, value2, valueN); o Example: INSERT INTO tools (tool_name, rental_days) VALUES ('hammer', 14) Inserting tuples with foreign keys: The foreign key needs to have the same value as the primary key. In order to automate the process (let the DB system look up the value of the primary key) use an SQL query that will retrieve the primary key from the parent table. o Syntax: INSERT INTO table (fieldl, field2_foreign_key) VALUES ('value', (SELECT field_primary_key FROM table WHERE field = 'value'); 10.1.3.1/20 10.1.2.1/24 R3 10.1.5.1/24 R1 SP1 AS1 le1 10.1.0.1/24 10.2.0.1/24 RB 10.2.3.1/24, 10.1.4.1/24 R10 SP2 AS2 102 192.168.55.1/24 R9 10.2.1.1/24 101 lo1 10.3.1.1/24] 10.3.0.1/24 Internet RS RA SP3 AS3 101 2.1/24 R7 RG lot 10.3.3.1/2 R11 10.3.5.1/24 Project: Tools rental store Section A - Setup Python for database connection Project overview The project includes two phases: o Phase #1: Database creation, which includes O Database creation O o Table creation o Table population o Data management: Queries (select), Insert, Delete Submission: Text files with extension *.sql with SQL statements, all required screenshots Phase #2: Python program to manage database Submission: Python script, demo by providing all required screenshots Phase #1 - Database conceptual design Data requirements The following data has to be managed for the tool rental store: customer information, tools information, rental information. This will require 5 tables. public.Customers OFname OLname varchar(10) varchar(10) varchar(20) integer Phone_number O Membership_date date Customer_id integer Customers_pk constraint Postal Code pubic.Tools O Title O Rental duration Tool id Tools_pk tools: varchar(15) Integer integer constraint Entities with attributes pk pk rentals pk> pk o Title o customers o o Postal code o Phone number o Membership date Tools_has_many_many_Tools_has_many_Jobs Tools_has_many_Rentals Name: first, last Customers_has_many_Rentals o Rental date p Return date Toolid Tools integer Job id Jobs integer public.Tools Jobs Tools_fk Jobs_fk Tools Jobs_pk constraint constraint constraint O Rental date Return_date Rental_id pk fk >> pk fk pubic. Rentals date date Jobs_has_many_many_Tools_has_many_Jobs pk fk fk integer Customer_id_Customers integer Toolid Tools integer Rentals pk Customers fk Tools_fk constraint constraint constraint pk f fk pk> fk fk public. Jobs Job_id integer Job_name varchar(20) Jobs_pk constraint pk Jobs that can require a given tool: to be modeled as a separate entity. For example, tool name - snake, job - plumming. O Rental duration in days pk 3. Service Provider 1 (SP1) Requirements (30 marks) a. Network 10.1.4.0/24 is a private network within SP1 and must not be advertised to any router that is not part of Service Provider 1. This network must be advertised within the IBGP domain of SP1 but must not be advertised to any other Service Provider. SP1 would prefer a simple solution that didn't require amending the configuration of more than one router. Partial marks will be awarded for implementing a solution that spans multiple routers. (10 marks) b. SP1 does not want any traffic destined for the 10.1.5.0.0/24 and 10.1.4.0/24 networks to travel over the direct link between R1 and R2. Such traffic must be routed through R3 to keep the direct link between R1 and R2 free. (10 marks) c. SP1 would like to reduce the number of routes it advertises to SP2 and SP3. Make sure that SP1 advertises a summary route in place of 10.1.0.0/24, 10.1.1.0/24, 10.1.2.0/24 and 10.1.3.0/24 to Service Providers 2 and 3. Select the most precise summary route you can build. The component subnets must be advertised to all routers within SP1 but only the summary routes should be advertised to SP2 and SP3 (10 marks) 1. Service Provider 2 (SP2) Requirements (16 marks) a. R10 has an uplink to a large Tier 1 Service Provider. This is represented in the simulation by lo2. SP2 needs R10 to advertise a default route to both R8 and R9. However, the default route should be withdrawn if the 192.168.55.0/24 network is unreachable. (8 marks) b. SP2 would like all traffic destined for the 10.1.5.0/24 network of SP1 to be routed through SP3 via R8 over its direct link to R4. If this path is unavailable then the traffic should be routed over the alternative path to SP3 via the R9 to R6 link. If SP2 has no valid path to the 10.1.5.0/24 subnet through SP3, it may use any available alternative path. (8 marks) 2. Service Provider 3 (SP3) Requirements (24 marks) a. SP3 is expected to grow rapidly in the near future and is concerned that a full mesh of IBGP neighbour relationships will be unsustainable. SP3 would like to make use of Route Reflectors to reduce the overall number of iBGP neighbour relationships required within the SP3 network. Design and implement a solution that makes use of one or more Route Reflectors. Your design should: 1. Eliminate any single points of failure caused by the deployment of Route Reflectors. 2. Reduce the number of IBGP neighbour relationships within SP3 3. Prevent any potential routing loops from occurring and reduce the Control Plane overhead as much as possible for all iBGP routers within SP3 (12 marks) b. SP3 would like to make use of the default route advertised by SP2. Default traffic should exit SP3 via: 1. The direct link between R4 and R8. 2. If the link between R4 and R8 is down then default traffic should exit SP3 via the direct link between R6 and R9. 3. If this second link is also down then default traffic should exit SP3 via any available path through SP1. (12 marks) 4. Documentation (30 marks) Answer the following questions about your implementation. You are encouraged to include code snippets to support your answers where appropriate. There is no word count limit. If a solution is only partially working then explain what works and what doesn't work. A partially operational solution may be awarded more marks if it is clear that you are aware that it is not working as required. Identify any sections that you have not attempted. Please note that marks may be awarded for providing a valid strategy to an incomplete section. a. Explain how you deployed a default route (1a). Your answer should explain how you linked the default route advertisement to the presence of a viable route to the 192.168.55.0/24 network. (5 marks) b. Document your solution to (2a). Your answer should include the following: i. Operation of a Route Reflector and its role in simplifying an IBGP network. (3 marks) ii. Issues that may arise from the use of Route Reflectors and strategies that may be used to mitigate these issues. (3 marks) iii. Justification for your Route Reflector deployment strategy. (4 marks) c. Explain how you prevented subnet 10.1.4.0/24 from being advertised to SP2 and SP3 (3a). There are several ways to solve this problem. Suggest one other strategy and provide justification for the solution you implemented. (6 marks) d. Document your solution to (3b). Your answer should include the following: i. An overview of the strategy used to solve this problem. ii. A brief review of the tests used to verify that your solution worked (5 marks) e. Provide command listings for the BGP configuration of each router and a fully labelled topology diagram that shows the IP addresses and port labels of each interface (you may include the same diagram you submitted as part of Portfolio Element 1, though you are advised to improve it if it was awarded a low mark). (4 marks) Why 5 tables? Relationship between tools and jobs is many-to-many. Use "associative pattern" (ERD lecture) to resolve many-to-many relationships. Out of tools and jobs we will have 3 tables: tools, jobs, and tool_jobs. Useful concepts Recall good naming conventions: it is good idea to name your primary key (PK) as "id". If some other table references this PK - give it name "tablename_id". For example, tool_id in table tool_rentals would reference PK id in table tools. Auto-incrementing (surrogate) primary key: To facilitate primary key management use the serial "type" instead of the integer data type when declaring a primary key: it is equivalent to an unsigned integer, not null, and autoincrementing; it is based on the setup of a sequence. Another advantage of the "surrogate" key is that your primary key will be made up only of one attribute, otherwise, for example, in table that describes tool rentals you will have to create a composite (key made up of several attributes) primary key made up of tool_id (foreign key to attribute id of tools table) and customer_id (foreign key to attribute id of customers table). O Example: CREATE TABLE tools ( tool_id serial PRIMARY KEY, tool_name varchar (200) not null unique, rental_days smallint not null ); Foreign key reference: Note that foreign key that references SERIAL primary key in the related table should be declared with integer data type. Inserting tuples with primary keys: If you are using serial in your INSERT statement you do not need to specify value corresponding to attribute declared with SERIAL, the attribute will obtain value automatically based on the next available sequence number fieldN) o Syntax: INSERT INTO table (fieldl, field2, VALUES (valuel, value2, valueN); o Example: INSERT INTO tools (tool_name, rental_days) VALUES ('hammer', 14) Inserting tuples with foreign keys: The foreign key needs to have the same value as the primary key. In order to automate the process (let the DB system look up the value of the primary key) use an SQL query that will retrieve the primary key from the parent table. o Syntax: INSERT INTO table (fieldl, field2_foreign_key) VALUES ('value', (SELECT field_primary_key FROM table WHERE field = 'value');