Activity: DB design, integrity constraints, and SQL
(no submission)Purpose:
- Reinforce (some) database concepts
- Analyze integrity constraints
- Practice writing and interpreting queries (SQL)
- Get ready to work on homework assignment and course project
- Prepare for exam 1
You may make a copy of a worksheet and complete this activity, or simply type your answers in any text editor.
You may work alone or with 3-4 other students in this course (max size=5).
Consider the Santa_Workshop database. Refer to the Santa's workshop database scenario.
Complete/Answer the following questions
- Convert the Santa’s workshop E-R diagram (above) into schema statements. Identify primary keys and foreign keys.
- If toy_id is a key for the Toy relation, could it be a key for the Built_in relation?
Write SQL to solve the following problems
- Create all tables represented by the schema statements in question 1
- Find the badges and names of all elves who work in a workshop named "NorthStar"
- Find the badges and names of all elves who work at the "NorthStar" or "EastStar" workshops
- Find the number of elves each supervisor supervises. Display the supervisors’ badges along with the number of elves.
- Consider all elves who work in the "NorthStar" workshop. Find the badges of their supervisors.
- List the badges of all supervisors who supervise at least 3 elves.
- Find the elves who work in the same workshop as their supervisors. Display the elves' badges and their supervisors' badges.
- Find the information of all toys built in "NorthStar" or "EastStar" workshops
- Find the information of all toys built in "NorthStar" and "EastStar" workshops
- Find the information of all toys built in "NorthStar" but not in "EastStar" workshops
- Consider all elves who work in the "NorthStar" workshop. Find their supervisors' badges and names
- Find the number of evaluations each rater has rated. List the names of the raters along with the number evaluations.
Copyright © 2024 Upsorn Praphamontripong
Released under the
CC-BY-NC-SA 4.0 license.