Activity: DB design, integrity constraints, and SQL

(no submission)
Purpose:

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.

image showing Santa workshop ER

Complete/Answer the following questions

  1. Convert the Santa’s workshop E-R diagram (above) into schema statements. Identify primary keys and foreign keys. 
    Elf(badge, name, wname)   
          –- FK: wname references Workshop(wname)
is_supervised(badge, sbadge)   
          –- PK: badge, an elf can have at most one supervisor, 
      a supervisor can supervise many elves
          –- FK: badge references Elf(badge)
          –- FK: sbadge references Elf(badge)
Workshop(wname, location)
Toy(toy_id, toy_name)
Built_in(toy_id, wname)
          –- FK: toy_id references Toy(toy_id)
          –- FK: wname references Workshop(wname)
Evaluation(badge, rater, rate_date, rating)
          -- FK: badge references Elf(badge)
          -- FK: rater references Elf(badge)
          -- assume rating is of type INT
  2. If toy_id is a key for the Toy relation, could it be a key for the Built_in relation? 
    No. Just because it is a key in one relation doesn't mean it is in another. 
Being a key is relative to the relation. In this scenario, we want to  
be able to keep track of multiple workshops that build one toy 
(and also multiple toys that are built in one workshop).

Write SQL to solve the following problems

  1. Create all tables represented by the schema statements in question 1 
    CREATE TABLE Workshop(wname VARCHAR(255), 
                    location VARCHAR(255),
                    PRIMARY KEY (wname));
CREATE TABLE Toy(toy_id VARCHAR(30), 
                    toy_name VARCHAR(255),
                    PRIMARY KEY (toy_id));
CREATE TABLE Built_in(toy_id VARCHAR(30), 
                    wname VARCHAR(255),
                    PRIMARY KEY (toy_id, wname),
                    FOREIGN KEY (toy_id) references Toy(toy_id),
                    FOREIGN KEY (wname) references Workshop(wname));
CREATE TABLE Elf (badge VARCHAR(10), 
                    name VARCHAR(60), 
                    wname VARCHAR(255),
                    PRIMARY KEY (badge),                 
                    FOREIGN KEY (wname) REFERENCES Workshop(wname));
CREATE TABLE is_supervised (badge VARCHAR(10), 
                    sbadge VARCHAR(10),
                    PRIMARY KEY (badge),
                    FOREIGN KEY (badge) REFERENCES Elf(badge));
CREATE TABLE Evaluation(badge VARCHAR(10), 
                    rater VARCHAR(10), 
                    rate_date DATE,
                    rating INT,
                    PRIMARY KEY (badge, rater, rate_date),
                    FOREIGN KEY (rater) REFERENCES Elf(badge)); 

-- (note the order of creation - due to foreign keys)

-- To test, inserts some data

INSERT INTO Workshop VALUES ('NorthStar', '123 North St.');
INSERT INTO Workshop VALUES ('EastStar', '123 East St.');
INSERT INTO Workshop VALUES ('WestStar', '123 West St.');
INSERT INTO Workshop VALUES ('SouthStar', '123 South St.');

INSERT INTO Toy VALUES ('111', 'Car');
INSERT INTO Toy VALUES ('222', 'Book');
INSERT INTO Toy VALUES ('333', 'Doll');
INSERT INTO Toy VALUES ('444', 'Ball');
INSERT INTO Toy VALUES ('555', 'Bike');
INSERT INTO Toy VALUES ('666', 'Board game');

INSERT INTO Built_in VALUES ('111', 'NorthStar');
INSERT INTO Built_in VALUES ('222', 'SouthStar');
INSERT INTO Built_in VALUES ('222', 'WestStar');
INSERT INTO Built_in VALUES ('333', 'NorthStar');
INSERT INTO Built_in VALUES ('333', 'EastStar');
INSERT INTO Built_in VALUES ('444', 'EastStar');
INSERT INTO Built_in VALUES ('444', 'WestStar');
INSERT INTO Built_in VALUES ('444', 'NorthStar');
INSERT INTO Built_in VALUES ('555', 'EastStar');
INSERT INTO Built_in VALUES ('666', 'WestStar');

INSERT INTO Elf VALUES ('1111111111', 'Humpty', 'NorthStar');
INSERT INTO Elf VALUES ('1111111112', 'Dumpty', 'NorthStar');
INSERT INTO Elf VALUES ('1111111113', 'Wacky', 'EastStar');
INSERT INTO Elf VALUES ('1111111114', 'Duh', 'NorthStar');
INSERT INTO Elf VALUES ('1111111115', 'Huh', 'SouthStar');
INSERT INTO Elf VALUES ('1111111116', 'Duhhuh', 'SouthStar');
INSERT INTO Elf VALUES ('1111111117', 'Mickey', 'SouthStar');
INSERT INTO Elf VALUES ('1111111118', 'Minnie', 'WestStar');
INSERT INTO Elf VALUES ('1111111119', 'Goofy', 'SouthStar');
INSERT INTO Elf VALUES ('1111111120', 'Donald', 'SouthStar');
INSERT INTO Elf VALUES ('1111111121', 'Daisy', 'EastStar');
INSERT INTO Elf VALUES ('1111111122', 'Pluto', 'EastStar');

-- INSERT INTO is_supervised VALUES ('1111111111', '');
INSERT INTO is_supervised VALUES ('1111111112', '1111111111');
INSERT INTO is_supervised VALUES ('1111111113', '1111111111');
INSERT INTO is_supervised VALUES ('1111111114', '1111111112');
INSERT INTO is_supervised VALUES ('1111111115', '1111111112');
INSERT INTO is_supervised VALUES ('1111111116', '1111111114');
INSERT INTO is_supervised VALUES ('1111111117', '1111111114');
INSERT INTO is_supervised VALUES ('1111111118', '1111111114');
INSERT INTO is_supervised VALUES ('1111111119', '1111111116');
INSERT INTO is_supervised VALUES ('1111111120', '1111111114');
INSERT INTO is_supervised VALUES ('1111111121', '1111111115');
INSERT INTO is_supervised VALUES ('1111111122', '1111111116');

INSERT INTO Evaluation VALUES ('1111111112', '1111111111', '20221030', 3);
INSERT INTO Evaluation VALUES ('1111111113', '1111111111', '20221030', 4);
INSERT INTO Evaluation VALUES ('1111111114', '1111111112', '20221010', 2);
INSERT INTO Evaluation VALUES ('1111111115', '1111111112', '20221028', 3);
INSERT INTO Evaluation VALUES ('1111111114', '1111111112', '20221030', 3);
INSERT INTO Evaluation VALUES ('1111111116', '1111111114', '20221030', 1);
INSERT INTO Evaluation VALUES ('1111111117', '1111111114', '20221030', 2);
INSERT INTO Evaluation VALUES ('1111111118', '1111111114', '20221020', 3);
INSERT INTO Evaluation VALUES ('1111111118', '1111111114', '20221030', 4);
INSERT INTO Evaluation VALUES ('1111111119', '1111111116', '20220930', 3);
INSERT INTO Evaluation VALUES ('1111111119', '1111111116', '20221030', 3);
INSERT INTO Evaluation VALUES ('1111111120', '1111111114', '20220930', 3);
INSERT INTO Evaluation VALUES ('1111111120', '1111111114', '20220830', 3);
INSERT INTO Evaluation VALUES ('1111111120', '1111111114', '20220730', 4);
INSERT INTO Evaluation VALUES ('1111111120', '1111111114', '20221030', 4);
INSERT INTO Evaluation VALUES ('1111111121', '1111111115', '20220830', 2);
INSERT INTO Evaluation VALUES ('1111111122', '1111111116', '20220830', 3);
INSERT INTO Evaluation VALUES ('1111111122', '1111111116', '20220930', 4);
INSERT INTO Evaluation VALUES ('1111111122', '1111111116', '20221030', 4);
  2. Find the badges and names of all elves who work in a workshop named "NorthStar" 
    SELECT badge, name
FROM Elf
WHERE wname='NorthStar';
  3. Find the badges and names of all elves who work at the "NorthStar" or "EastStar" workshops 
    SELECT badge, name
FROM   Elf
WHERE  wname='NorthStar' OR wname='EastStar';
  4. Find the number of elves each supervisor supervises. Display the supervisors’ badges along with the number of elves. 
    SELECT sbadge, COUNT(badge) AS count_badge
FROM is_supervised
GROUP BY sbadge;
  5. Consider all elves who work in the "NorthStar" workshop. Find the badges of their supervisors. 
    SELECT DISTINCT sbadge
FROM Elf, is_supervised
WHERE wname='NorthStar' AND Elf.badge=is_supervised.badge;

-- or

SELECT DISTINCT sbadge
FROM Elf NATURAL JOIN is_supervised
WHERE wname='NorthStar';
  6. List the badges of all supervisors who supervise at least 3 elves. 
    SELECT   sbadge
FROM     is_supervised
GROUP BY sbadge
HAVING   COUNT(badge) >= 3;
  7. Find the elves who work in the same workshop as their supervisors. Display the elves' badges and their supervisors' badges. 
    SELECT E1.badge, E2.badge
FROM   Elf E1, Elf E2, is_supervised 
WHERE  E1.badge=is_supervised.badge AND E2.badge=is_supervised.sbadge
AND    E1.wname=E2.wname;

-- E1 for Elf, E2 for supervisor
  8. Find the information of all toys built in "NorthStar" or "EastStar" workshops 
    SELECT DISTINCT toy_id, toy_name
FROM   Toy NATURAL JOIN Built_in
WHERE  wname='NorthStar' OR wname='EastStar';

-- Another example

(SELECT toy_id, toy_name
 FROM   Toy NATURAL JOIN Built_in
 WHERE  wname='NorthStar')
UNION  
(SELECT toy_id, toy_name  
 FROM   Toy NATURAL JOIN Built_in
 WHERE  wname='EastStar');
  9. Find the information of all toys built in "NorthStar" and "EastStar" workshops 
    (SELECT toy_id, toy_name
 FROM   Toy NATURAL JOIN Built_in
 WHERE  wname='NorthStar')
INTERSECT 
(SELECT toy_id, toy_name
 FROM   Toy NATURAL JOIN Built_in
 WHERE  wname='EastStar');

-- or

SELECT toy_id, toy_name
FROM   Toy NATURAL JOIN Built_in
WHERE  wname='NorthStar'
AND    toy_id IN (SELECT toy_id
                  FROM   Toy NATURAL JOIN Built_in
                  WHERE  wname='EastStar');
  10. Find the information of all toys built in "NorthStar" but not in "EastStar" workshops 
    (SELECT toy_id, toy_name
 FROM   Toy NATURAL JOIN Built_in
 WHERE  wname='NorthStar')
EXCEPT
(SELECT toy_id, toy_name
 FROM   Toy NATURAL JOIN Built_in
 WHERE  wname='EastStar');

-- or

SELECT toy_id, toy_name
FROM   Toy NATURAL JOIN Built_in
WHERE  wname='NorthStar'
AND    toy_id NOT IN (SELECT toy_id
                      FROM   Toy NATURAL JOIN Built_in
                      WHERE  wname='EastStar');
  11. Consider all elves who work in the "NorthStar" workshop. Find their supervisors' badges and names 
    WITH sbadge_of_NorthStarElf AS (SELECT is_supervised.sbadge
                                FROM   Elf NATURAL JOIN is_supervised
                                WHERE  Elf.wname='NorthStar')
SELECT sbadge_of_NorthStarElf.sbadge, Elf.name
FROM   sbadge_of_NorthStarElf, Elf
WHERE  sbadge_of_NorthStarElf.sbadge = Elf.badge;
  12. Find the number of evaluations each rater has rated. List the names of the raters along with the number evaluations. 
    WITH rater_count AS (SELECT   rater, COUNT(*) AS count_eval
                     FROM     Evaluation
                     GROUP BY rater)
SELECT name, count_eval
FROM   Elf, rater_count
WHERE  badge = rater;

Copyright © 2024 Upsorn Praphamontripong

Released under the Creative Commons License CC-BY-NC-SA 4.0 license.

Last updated 2024-02-25 16:52