Database Design for Art Galleries: Schema & SQL Queries

Although you always wanted to be an artist, you ended up being an expert on databases because you love to cook data and you somehow confused database with data baste. Your old love is still there, however, so you set up a database company, ArtBase, that builds a product for art galleries. The core of this product is a database with a schema that captures all the information that galleries need to maintain.

ArtBase Database Schema

Artists

Artists, their names (which are unique), birthplaces, age, and style of art:

artist(name, birthplace, age, style_of_art)

Artwork

For each piece of artwork, the artist, the year it was made, its unique title, its type of art (e.g., painting, lithograph, sculpture, photograph), and its price must be stored:

artwork(title, artist_name, year_created, type_of_art, price)

Art Groups

Pieces of artwork are also classified into groups of various kinds. Each group is identified by a name that describes the group:

art_group(group_name, description)

Artwork-Group Relationship

artwork_group(title, group_name)

Customers

Customer information and preferences are captured in the customer and customer_preferences tables:

• customer(name, address, total_spent)
• customer_preferences(customer_name, artist_name, group_name)

SQL Query Examples

Sailors-Boats-Reserves DB

Consider the Sailors-Boats-Reserves DB:

S (sid, sname, rating, age)
B (bid, bname, color)
R (sid, bid, date)

1. Find the colors of boats reserved by Albert.
2. Find all IDs of sailors who have a rating of at least 8 or have reserved boat 103.
3. Find the names of sailors who have not reserved a red boat.
4. Find the IDs of sailors with age over 20 who have not reserved a red boat.
5. Find the names of sailors who have reserved at least two boats.
6. Find the names of sailors who have reserved all boats.
7. Find the names of sailors who have reserved all boats called BigBoat.
8. Find the IDs of sailors whose rating is better than some sailor called Bob.
9. Find the IDs of sailors whose rating is better than every sailor called Bob.
10. Find the IDs of sailors with the highest rating.
11. Find the name and age of the oldest sailor.

Suppliers-Parts-Catalog Schema

Consider the following schema:

• Suppliers(sid: integer, sname: string, address: string)
• Parts(pid: integer, pname: string, color: string)
• Catalog(sid: integer, pid: integer, cost: real)

1. Find the names of parts for which there is some supplier.

   SELECT DISTINCT P.pname FROM Parts P, Catalog C WHERE P.pid = C.pid

2. Find the names of parts supplied by Acme Widget Suppliers and no one else.

   SELECT P.pname FROM Parts P, Catalog C, Suppliers S WHERE P.pid = C.pid AND C.sid = S.sid AND S.sname = 'Acme Widget Suppliers' AND NOT EXISTS ( SELECT * FROM Catalog as C1, Suppliers as S1 WHERE P.pid = C1.pid AND C1.sid = S1.sid AND S1.sname != 'Acme Widget Suppliers' )

3. For each part, find the name of the supplier who charges the most for that part.

   SELECT P.id, S.sname FROM Parts P, Suppliers S, Catalog C WHERE C.pid = P.id AND C.sid=S.id AND C.cost = (SELECT MAX(C1.cost) FROM Catalog C1 WHERE C1.pid=P.id);

4. Find the ids of suppliers who supply a red part and a green part.

   SELECT DISTINCT C.sid FROM Catalog C, Parts P WHERE C.pid = P.pid AND P.color = 'Red' INTERSECT SELECT DISTINCT C1.sid FROM Catalog C1, Parts P1 WHERE C1.pid = P1.pid AND P1.color = 'Green'

5. Find the ids of suppliers who supply a red part or a green part.

   SELECT DISTINCT C.sid FROM Catalog C, Parts P WHERE C.pid = P.pid AND P.color = 'Red' UNION SELECT DISTINCT C1.sid FROM Catalog C1, Parts P1 WHERE C1.pid = P1.pid AND P1.color = 'Green'

Employee-Works-Department Schema

Consider the following relational schema. An employee can work in more than one department; the pct_time field of the Works relation shows the percentage of time that a given employee works in a given department.

• Emp(eid: integer, ename: string, age: integer, salary: real)
• Works(eid: integer, did: integer, pct_time: integer)
• Dept(did: integer, dname: string, budget: real, managerid: integer)

1. Print the names and ages of each employee who works in both the Hardware department and the Software department.

   SELECT E.ename, E.age FROM Emp E, Works W1, Works W2, Dept D1, Dept D2 WHERE E.eid = W1.eid AND W1.did = D1.did AND D1.dname = 'Hardware' AND E.eid = W2.eid AND W2.did = D2.did AND D2.dname = 'Software'

2. Print the name of each employee whose salary exceeds the budget of all of the departments that he or she works in.

   SELECT E.ename FROM Emp E WHERE E.salary > ALL (SELECT D.budget FROM Dept D, Works W WHERE E.eid = W.eid AND D.did = W.did)

3. Find the names of managers who manage the departments with the largest budgets.

   SELECT E.ename FROM Emp E WHERE E.eid IN (SELECT D.managerid FROM Dept D WHERE D.budget >= ALL (SELECT D1.budget FROM Dept D1))

University Schema

• Department(ID, name, address) – short name D
• Faculty(ID, deptID, firstName, lastName, joinYear) – short name F
• Course(ID, deptID, name, numOfCredits) – short name C
• Student(ID, deptID, firstName, LastName, joinYear) – short name S
• Teaching(FacultyID, courseID, Year) – short name T
• Registration(studentID, courseID, Year, grade) – short name R

1. Report faculty members who have joined after 2005. Sort them descending by their last names.

   SELECT * FROM Faculty WHERE joinYear > 2005 ORDER BY lastName DESC;

2. Report faculty members (Ids and names) who have taught courses outside their departments (that is, the course and the faculty belong to different departments).

   SELECT F.ID, F.firstName, F.lastName FROM Faculty F JOIN Teaching T ON F.ID = T.FacultyID JOIN Course C ON T.courseID = C.ID WHERE F.deptID != C.deptID;

3. Report the department DIs that have more than 20 faculty members and offer more than 10 courses with numOfCredits = 3.

   SELECT D.ID FROM Department D JOIN Faculty F ON D.ID = F.deptID JOIN Course C ON D.ID = C.deptID WHERE C.numOfCredits = 3 GROUP BY D.ID HAVING COUNT(DISTINCT F.ID) > 20 AND COUNT(DISTINCT C.ID) > 10;

4. Report the distinct course IDs that have been taught in 3 consecutive years between 2000 and 2010 (inclusive).

   SELECT DISTINCT T1.courseID FROM Teaching T1 JOIN Teaching T2 ON T1.courseID = T2.courseID AND T1.Year = T2.Year - 1 JOIN Teaching T3 ON T1.courseID = T3.courseID AND T1.Year = T3.Year - 2 WHERE T1.Year BETWEEN 2000 AND 2008 AND T2.Year BETWEEN 2000 AND 2009 AND T3.Year BETWEEN 2000 AND 2010;

5. Report the course IDs that have more than 50 registered students in year 2010.

   SELECT courseID FROM Registration WHERE Year = 2010 GROUP BY courseID HAVING COUNT(DISTINCT studentID) > 50;

6. Update the join year for faculty ID = 12345 to be 2007.

   UPDATE Faculty SET joinYear = 2007 WHERE ID = 12345;

7. Delete records from the Registration table for year 2000 and that have courses belong to department ID ‘CS’.

   DELETE FROM Registration WHERE Year = 2000 AND courseID IN ( SELECT ID FROM Course WHERE deptID = 'CS' );

8. Report the years that have either more than 50 courses or less than 5 courses taught by faculty. Report the year and the count of courses.

   SELECT T.Year, COUNT(DISTINCT T.courseID) AS courseCount FROM Teaching T GROUP BY T.Year HAVING COUNT(DISTINCT T.courseID) > 50 OR COUNT(DISTINCT T.courseID) < 5;

9. Report the student names (first and last) registered in the last offering of course ID = ‘CS3431’

   SELECT S.firstName, S.lastName FROM Student S JOIN Registration R ON S.ID = R.studentID WHERE R.courseID = 'CS3431' AND R.Year = ( SELECT MAX(Year) FROM Registration WHERE courseID = 'CS3431' );

10. Report student IDs who have at least 3 A’s (grades) in year 2010.

    SELECT R.studentID FROM Registration R WHERE R.Year = 2010 AND R.grade = 'A' GROUP BY R.studentID HAVING COUNT(R.grade) >= 3;