11/18/22, 8:39 AM Topic: Unit 5: Team Problem Solving Activity - Unit 5 Group 3
This is a graded discussion: 40 points possible
due Nov 20
3 19
Unit 5: Team Problem Solving Activity - Unit 5 Group 3
From CIS622
Introduction
A fluent professional in this field can easily recognize
concepts across different coding languages. The python
pandas library works a lot like SQL, which you are building a
proficiency in. For this activity, you will practice your SQL
and then research why someone might used python over
SQL for a similar task.
Directions
1. As a group build the code below in SQL:
1. Create two new tables that are related with a primary
and foreign key.
2. Add data to both tables.
3. Join the two tables.
4. Two additional actions of your groups choosing.
2. You can do the task above in python as well. Research
why you would chose python over SQL. What
advantages does python provide that SQL does not?
Each team is randomly assigned a leader. The leader is
responsible for compiling the final post with help from the
team (aka, don't leave the leader to do all the work. How you
divide the work is up to your team). If a leader is failing to
perform their duties, please work amongst yourself to
appoint a new leader.
Due Dates
Participate in the discussion at least three different days
https://canvas.park.edu/groups/26889/discussion_topics/1512468 1/13
11/18/22, 8:39 AM Topic: Unit 5: Team Problem Solving Activity - Unit 5 Group 3
this week. by 11:59 p.m. Saturday CT.
The leader must post your final summary to the Unit 5
Show-and-Tell
(https://canvas.park.edu/courses/71584/discussion_topics/
1496618) by 8:00 p.m. Sunday, CT.
Search entries or author
Unread
Subscribed
Reply
(https:// Pavankalyan Nunna (https://canvas.park.edu/groups/26889/users/100552)
Tuesday
Hi Team,
This week we are tasked to code in SQL. We need to create tables and that are mapped with
foreign and primary keys. So on what data we can create the tables. Does anyone have
anything specific in mind or we can just go with random data. In my view since it is only two
tables we need to embed all the information in those two. So what about US states and
population in each state?
Reply
(http Michael Brown (https://canvas.park.edu/groups/26889/users/100174)
Tuesday
Pavankalyan,
I don’t know what Lauren means by “additional actions of our choosing”, but to do this I
have an idea. Why not do three tables: one with our names and an ID, an event table that
is event ID and a date we had our favorite meal and the ID of what we had, then a third
table which is a list of our favorite meals. And an ID for each. We set up our relationships
and then build 2 queries: the most common meal we’ve eaten and meals we’ve not tried.
So my dataset could be
tblPerson: 1, Michael, Brown
https://canvas.park.edu/groups/26889/discussion_topics/1512468 2/13
11/18/22, 8:39 AM Topic: Unit 5: Team Problem Solving Activity - Unit 5 Group 3
tblEvent: 1, 11/4/2022, 2
tblMeal: 2, Macaroni and Cheese
If you all give me a list of 5 foods each and 2 dates, I will code the SQL and post the
scripts here for your review.
Pavankalyan, please have a look at the requirements and let me know what you think of
my take.
Does this work for everyone?
Reply
(http Pavankalyan Nunna (https://canvas.park.edu/groups/26889/users/100552)
Tuesday
Hi Brown,
I think the idea of two additional actions can be adding of more tables as well but not
sure.
The idea which you have proposed is good. So in our tables we are going to have
primary keys for all tables such as person_id, event_id, and meal_id. Am I correct?
List of foods
tblPerson: 1, Pavan
tblEvent: 1, 11/11/2022, 3
tblMeal: 4, Pista
tblPerson: 2, Pavan
tblEvent: 2, 11/9/2022, 2
tblMeal: 3, Cheese and Burger
tblPerson: 3, Pavan
tblEvent: 3, 9/9/2022, 5
tblMeal: 7, Classic sandwich
https://canvas.park.edu/groups/26889/discussion_topics/1512468 3/13
11/18/22, 8:39 AM Topic: Unit 5: Team Problem Solving Activity - Unit 5 Group 3
tblPerson: 4, Kalyan
tblEvent: 1, 12/9/2022, 1
tblMeal: 3, Spicy Italian
tblPerson: 5, Kalyan
tblEvent: 4, 11/5/2022, 6
tblMeal: 2, Hamburger
Reply
(http Michael Brown (https://canvas.park.edu/groups/26889/users/100174)
Tuesday
Pavankalyan,
You’re spot on with the keys! I will code this tomorrow evening and post my
script!
Reply
(http Pavankalyan Nunna (https://canvas.park.edu/groups/26889/users/100552)
Wednesday
Hi Brown,
That's Awesome!
Reply
(http Siva Sai Teja Chandu (https://canvas.park.edu/groups/26889/users/104608)
Tuesday
Hi Brown,
I agree with you idea and implementation, Sure we will go with the above three tables
tblPerson:3,Siva, Sai
tblEvent:3,11/05/2022,7
https://canvas.park.edu/groups/26889/discussion_topics/1512468 4/13
11/18/22, 8:39 AM Topic: Unit 5: Team Problem Solving Activity - Unit 5 Group 3
tblMeal:7,Chicken taco, grilled chicken
tblPerson:3,Siva, Sai
tblEvent:3,11/10/2022,8
tblMeal:8,Indian Biriyani, Tandoori
tblPerson:3,Siva, Sai
tblEvent:3,11/10/2022,9
tblMeal:9,Pizza ,Softdrink
Please let me know if you need anything more
Reply
(https:// Ravi Teja MacHarla (https://canvas.park.edu/groups/26889/users/100314)
Wednesday
Hi Team ,
Can you please have a look on it
CREATE SCHEMA `tree_sales` ;
CREATE TABLE `trees` (
`Tree_ID` int NOT NULL,
`Latin_Name` varchar(45) DEFAULT NULL,
`Price` decimal(5,2) DEFAULT NULL,
PRIMARY KEY (`Tree_ID`)
)
INSERT INTO `tree_sales`.`trees` (`Tree_ID`, `Latin_Name`, `Price`) VALUES
('100', 'Pinus', '12.50');
INSERT INTO `tree_sales`.`trees` (`Tree_ID`, `Latin_Name`, `Price`) VALUES
('101', 'Malus', '11.3');
INSERT INTO `tree_sales`.`trees` (`Tree_ID`, `Latin_Name`, `Price`) VALUES
('102', 'Indica', '10.40');
INSERT INTO `tree_sales`.`trees` (`Tree_ID`, `Latin_Name`, `Price`) VALUES
('103', 'Calyptranthes', '3.30');
https://canvas.park.edu/groups/26889/discussion_topics/1512468 5/13
11/18/22, 8:39 AM Topic: Unit 5: Team Problem Solving Activity - Unit 5 Group 3
Thanks
Reply
(http Michael Brown (https://canvas.park.edu/groups/26889/users/100174)
Yesterday
Ravi,
I wouldn't use the DECIMAL field type if I could use FLOAT. What happens if the price for
an item is $1000? Also, if you default values to NULL, that means you can have an entry
with a PK but no other data. Is that something you would allow>
Otherwise, the script is sound.
Michael
Reply
(http Ravi Teja MacHarla (https://canvas.park.edu/groups/26889/users/100314)
8:47am
Hi Michael ,
Decimal Is preferable in this case since the price is represented in dollars and cents.
In situation whereby the price is $1000,
We have made our database in a way that the output will be rendered as $1000.00
Thanks
Ravi
Reply
(https:// Michael Brown (https://canvas.park.edu/groups/26889/users/100174)
Yesterday
So, starting on the scripts - here is the one for our own people
-- Table: public.Person
https://canvas.park.edu/groups/26889/discussion_topics/1512468 6/13
11/18/22, 8:39 AM Topic: Unit 5: Team Problem Solving Activity - Unit 5 Group 3
-- DROP TABLE IF EXISTS public."Person";
CREATE TABLE IF NOT EXISTS public."Person"
"PersonID" integer NOT NULL,
"FirstName" character varying(20)[] COLLATE pg_catalog."default" NOT NULL,
"LastName" character varying(40)[] COLLATE pg_catalog."default" NOT NULL,
CONSTRAINT "Person_pkey" PRIMARY KEY ("PersonID")
TABLESPACE pg_default;
ALTER TABLE IF EXISTS public."Person"
OWNER to postgres;
COMMENT ON TABLE public."Person"
IS 'Contains user information';
This is the SQL pgAdmin generates. I will post the others as I write them.
Reply
(http Michael Brown (https://canvas.park.edu/groups/26889/users/100174)
Yesterday
Event tabl
CREATE TABLE public."MealDate"
"MealID" integer NOT NULL,
"PersonID" integer NOT NULL,
"MealDate" date NOT NULL,
PRIMARY KEY ("MealID"),
CONSTRAINT "PersonID" FOREIGN KEY ("PersonID")
REFERENCES public."Person" ("PersonID") MATCH SIMPLE
ON UPDATE NO ACTION
ON DELETE NO ACTION
NOT VALID
);
https://canvas.park.edu/groups/26889/discussion_topics/1512468 7/13
11/18/22, 8:39 AM Topic: Unit 5: Team Problem Solving Activity - Unit 5 Group 3
ALTER TABLE IF EXISTS public."MealDate"
OWNER to postgres;
COMMENT ON TABLE public."MealDate"
IS 'These are the dates we ate a meal';
Reply
(http Michael Brown (https://canvas.park.edu/groups/26889/users/100174)
Yesterday
Last one - what we ate.
CREATE TABLE public."Meal"
"FoodID" integer NOT NULL,
"MealID" integer NOT NULL,
"FoodDescription" character varying(50)[] NOT NULL,
CONSTRAINT "MealID" FOREIGN KEY ("MealID")
REFERENCES public."MealDate" ("MealID") MATCH SIMPLE
ON UPDATE NO ACTION
ON DELETE NO ACTION
NOT VALID
);
ALTER TABLE IF EXISTS public."Meal"
OWNER to postgres;
COMMENT ON TABLE public."Meal"
IS 'This is what we ate';
Reply
(http Michael Brown (https://canvas.park.edu/groups/26889/users/100174)
Yesterday
A final word on create scripts: if creating in pgAdmin's GUI, make sure you do not
select a VARCHAR field with brackets. That creates an array field.
To correct, the script would look something like this
https://canvas.park.edu/groups/26889/discussion_topics/1512468 8/13
11/18/22, 8:39 AM Topic: Unit 5: Team Problem Solving Activity - Unit 5 Group 3
ALTER TABLE IF EXISTS public."Person" DROP COLUMN IF EXISTS
"FirstName";
ALTER TABLE IF EXISTS public."Person" DROP COLUMN IF EXISTS
"LastName";
ALTER TABLE IF EXISTS public."Person"
ADD COLUMN "FirstName" character varying(20) NOT NULL;
ALTER TABLE IF EXISTS public."Person"
ADD COLUMN "LastName" character varying(40) NOT NULL;
I will be back later with insert statements. At this point, I have tables coded and
"joined", in that we've created foreign keys that bind tables together. When we
get to writing queries, we're going to look at JOIN statements in the script.
Reply
(http Siva Sai Teja Chandu (https://canvas.park.edu/groups/26889/users/104608)
Yesterday
Hi Michael,
I have shared the details as per our discussion,
Table Person:
PersonID,Firstname ,lastname
Eg:
table Person: 3,Siva, Sai
Table2:(Meal Date)
Meal Date
Person ID,
MealID,
Eg:
tbl Event: 3,7,11/05/2022
https://canvas.park.edu/groups/26889/discussion_topics/1512468 9/13
11/18/22, 8:39 AM Topic: Unit 5: Team Problem Solving Activity - Unit 5 Group 3
Table3:Meal
FoodID
MealID
FoodDescription
Table Meal :
9,7,Pizza Softdrink
As per your table description and details I have applied this
Reply
(http Pavankalyan Nunna (https://canvas.park.edu/groups/26889/users/100552)
Yesterday
Is this table we will use to store a person's information with the primary key as primary_id?
I have a few doubts about what COLLATE and TABLESPACE represent in the code. I am
new to SQL pgAdmin so never mind asking you this.
Reply
(http Michael Brown (https://canvas.park.edu/groups/26889/users/100174)
Yesterday
It is, and if I were using a terminal, I wouldn't use COLLATE or TABLESPACE. It
might have something to do with the array it wanted me to do. It's moot now - I
https://canvas.park.edu/groups/26889/discussion_topics/1512468 10/13
11/18/22, 8:39 AM Topic: Unit 5: Team Problem Solving Activity - Unit 5 Group 3
dripped the columns and added them back (correctly this time).
Reply
(https:// Jayasree Vadlamudi (https://canvas.park.edu/groups/26889/users/102103)
Yesterday
Hello Team
Post 1
Here I have created two tables using the sql. Here the tables are realted to the food ordering
system. Here I have connected both tables using customer_ID. We can see that in customer
table it is Primary key and in transaction table it is foregin key.
CREATE TABLE [dbo].[customer](
[cust_ID] [int] NOT NULL,
[name] [varchar](50) NULL,
[contact_num] [int] NULL,
[address] [varchar](500) NULL,
CONSTRAINT [PK_customer] PRIMARY KEY CLUSTERED
[cust_ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY =
OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON,
OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
CREATE TABLE [dbo].[Transaction](
[order_ID] [int] NOT NULL,
[cust_ID] [int] NOT NULL,
[date] [date] NULL,
[food_ID] [int] NULL,
[supply_ID] [int] NULL,
https://canvas.park.edu/groups/26889/discussion_topics/1512468 11/13
11/18/22, 8:39 AM Topic: Unit 5: Team Problem Solving Activity - Unit 5 Group 3
CONSTRAINT [PK_Transaction] PRIMARY KEY CLUSTERED
[order_ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY =
OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON,
OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
Adding relationship between tables can be done using ALTER command.
ALTER TABLE [dbo].[customer] WITH CHECK ADD CONSTRAINT [FK_customer_customer]
FOREIGN KEY([cust_ID])
REFERENCES [dbo].[customer] ([cust_ID])
ALTER TABLE [dbo].[customer] WITH CHECK ADD CONSTRAINT
[FK_customer_customer1] FOREIGN KEY([cust_ID])
REFERENCES [dbo].[customer] ([cust_ID])
References
Wade. (2020). Skills wanted: Sql, Java, Python, and AWS top employers’ wish lists - [Careers].
IEEE Spectrum, 57(1), 59–59. https://doi.org/10.1109/mspec.2020.8946316
(https://doi.org/10.1109/mspec.2020.8946316)
Reply
(https:// Ravi Teja MacHarla (https://canvas.park.edu/groups/26889/users/100314)
Yesterday
Hello guys,
I am working on tables and added data in it, with primary and foreign keys.
Reply
https://canvas.park.edu/groups/26889/discussion_topics/1512468 12/13
11/18/22, 8:39 AM Topic: Unit 5: Team Problem Solving Activity - Unit 5 Group 3
(https:// Michael Brown (https://canvas.park.edu/groups/26889/users/100174)
9:02am
Everyone,
We now have three people working towards SQL. Can we stick to what I am putting out
there? This way, when we go to post our results Sunday, we have only one database to talk
about.
If someone is looking to code the Python version of what I am coding in SQL, or wants to take
the data set and put it into a csv file for load, I could use help there.
Ravi/Jayasree, is this something the two of you can work on?
Reply
https://canvas.park.edu/groups/26889/discussion_topics/1512468 13/13
