POTD 2: SQL — Candy Inventory
Due 13-Feb-2024, 12pm/noon ESTPurpose:
- Transition from ER → schema → fine tuning → SQL
- Be familiar with database queries
- Practice writing and interpreting SQL
- Verify that you have access to the database server and the database server works properly
- Get ready to work on a homework assignment and course project
Part 1: Get started
You have ~5 minutes to complete the following tasks.- Team with 5-6 of your neighbors
- Get to know your team. Using the two-breath rule, introduce yourself, share your name, major(s), the year you are in.
- Please send one of the team members to grab a hand full of candies. Your team will create table(s) to maintain your candy inventory. Please don't eat yet. You may have your candies after class.
Part 2: (small scale) DB design
You have ~15 minutes to complete the following tasks.Imagine, you are designing a small (partial) candy inventory.
Consider your candy inventory.
-
Discuss in your team. Come up with a partial E-R diagram
(1-2 entity sets, and a relationship set if applicable) to help you
manage your candy inventory.
- Although we are imitating an inventory scenario, due to the time limitation, we will focus on managing some parts of the candy inventory.
- You should consider only the information necessary to be maintained in your candy database.
- Transform the E-R diagram into table(s).
- List all functional dependencies (FDs) that are applicable.
- Decompose your table(s) using either 3NF or BCNF. For each table, if it is already in the chosen normal form, discuss in your team and make a note to show that it is already in the chosen normal form.
Part 3: (small scale) DB implementation
You have ~20 minutes to complete the following tasks.- Write SQL to create your candy database
- Write SQL to create the table(s) you designed in part 2. Be sure to include constraints such as primary key(s) and foreign key(s).
- [Consider your candy inventory, get all information to be maintained in your database] — Write SQL to insert data into your table(s)
- Write SQL to retrieve data from your database.
- To help you practice, you should come up with some (simple) questions and then write SQL to answer the questions.
- Try to keep your questions simple (for now). We will discuss additional operations (such as aggregation function and join) and you will solve more complicated queries later.
- [Exchange a few (or some, or a lot of) candies with another team (or as many teams as you'd prefer)] — Write SQL to update data in your database. For example, you may need to update the quantity available in your inventory. You may need to insert a new item into your inventory. You may need to delete the entire information about some candies if you no longer wish to maintain them in your inventory.
Part 4: Final step
You have ~5 minutes to complete the following tasks.- [Imagine your company has decided to close the inventory. Each team member will take some candies home. Be sure that no candy is left in the classroom] — Write SQL to remove all information from your table(s).
- Write SQL to drop your table(s)
- Write SQL to drop your database
What to submit
- Part 2:
- Your (partial) E-R diagram
- A list of functional dependencies (FDs) applicable to your database scenario
- A list of your decomposed tables, written in schema statements
- Part 3: Your SQL — all SQL queries you wrote for this POTD
- Create a database (if you do not use the phpMyAdmin feature)
- Create the table(s)
- Insert data into the table(s)
- Retrieve data from your database
- Update data in your database
- Part 4: Your SQL — all SQL queries you wrote for this POTD
- Delete data from the table(s)
- Drop the table(s) (if you do not use the phpMyAdmin feature)
- Drop your database (if you do not use the phpMyAdmin feature)
Grading rubric
[Total: 10 points]: Done (or provide evidence of your attempt, full or reasonable effort)- (5 points) — Providing evidence of your attempt, minimal effort
(-2.5 points) for 24 hours late (submitted after 13-Feb-2024 12pm EST, by 14-Feb-2024 12pm EST)
(-5 points) for 48 hours late (submitted after 14-Feb-2024 12pm EST, by 15-Feb-2024 12pm EST)
Submission
- [optional] Take a selfie (or picture) of your team and submit it with your POTD
- Include all team member names and computingIDs in the report.
- You may do one of the following:
- take picture(s) of your POTD, or
- save your POTD as a .txt, .sql, or .pdf file — No Word document.
- Each team submits only one copy.
- Upload your report as a PDF to POTD 2 on Gradescope. Make sure you connect your partner to your group on Gradescope so that everyone receives credit.
- Please verify that your POTD is accessible. Making your submission available to instructors and course staff is your responsibility; if we cannot access or open your file(s), we have to assign a zero grade. Be sure to test access to your file(s) before the due date.
Copyright © 2024 Upsorn Praphamontripong
Released under the
CC-BY-NC-SA 4.0 license.
Last updated 2024-02-07 20:04