Question

National Artists Association (NAA) maintains a small database to track painters, paintings, and galleries. A painting is created by a particular artist and then exhibited in a particular gallery. A gallery can exhibit many paintings, but each painting can be exhibited in only one gallery. Similarly, a painting is created by a single painter, but each painter can create many paintings. Create a database implementation of the statements above using SQL.

Answer 1

Following are the table in the database :

1. PAINTING (which has painting details for particular artist and particular gallery)
2. LOCATION (which has location details of the gallery)
3. GALLERY (which has gallery details)
4. ARTIST (which has artist details)
5. EXHIBITION (which has exhibition details)

We have following constraint type :

1. Primary key

2. Foreign key

3. Check condition

4. NOT NULL

5. UNIQUE

Constraints and datatypes for the table are given below:

Table : PAINTING

Column_name	Data Type	Contraint_type
PAINTING_ID	INT	PRIMARY KEY
TITLE	VARCHAR(50)	NOT NULL
PAINTING_COST	INT	NOT NULL (CHECK PAINTING_COST >0 )
ARTIST_ID	INT	FOREIGN KEY (references from ARTIST table)
GALLERY_ID	INT	FOREIGN KEY (references from GALLERY table)

Table : LOCATION

Column_name	Data Type	Contraint_type
LOCATION_ID	INT	PRIMARY KEY
LOCATION_NAME	VARCHAR(50)	NOT NULL

Table : GALLERY

Column_name	Data Type	Contraint_type
GALLERY_ID	INT	PRIMARY KEY
GALLERY_NAME	VARCHAR(50)	NOT NULL
LOCATION_ID	INT	FOREIGN KEY (references from LOCATION table)
EXHIBITION_ID	INT	FOREIGN KEY (references from EXHIBITION table)

Table : ARTIST

Column_name	Data Type	Contraint_type
ARTIST_ID	INT	PRIMARY KEY
ARTIST_NAME	VARCHAR(50)	NOT NULL
ADDRESS	VARCHAR(50)	NOT NULL
PHONE_NO	INT	NOT NULL, CHECK(PHONE_NO > 0)
EMAIL_ID	VARCHAR(50)	NOT NULL,UNIQUE,CHECK(EMAIL_ID LIKE ‘%_@__%.__%’)

Table : EXHIBITION

Column_name	Data Type	Contraint_type
EXHIBITION_ID	INT	PRIMARY KEY
EXHIBITION_START_DATE	DATE	NOT NULL
EXHIBITION_END_DATE	DATE	NOT NULL

ER DIAGRAM :

In order to create the above tables in database following are the SQL statements :

TABLE : LOCATION

CREATE TABLE LOCATION (LOCATION_ID INT PRIMARY KEY, LOCATION_NAME VARCHAR(50) NOT NULL)

TABLE : EXHIBITION

CREATE TABLE EXHIBITION(EXHIBITION_ID INT PRIMARY KEY, EXHIBITION_START_DATE DATE NOT NULL, EXHIBITION_END_DATE DATE NOT NULL)

TABLE : GALLERY

CREATE TABLE GALLERY (GALLERY_ID INT PRIMARY KEY, GALLERY_NAME VARCHAR(50) NOT NULL, FOREIGN KEY (LOCATION_ID) REFERENCES LOCATION (LOCATION_ID), FOREIGN KEY(EXHIBITION_ID) REFERENCES EXHIBITION(EXHIBITION_ID))

TABLE : ARTIST

CREATE TABLE ARTIST (ARTIST_ID INT PRIMARY KEY, ARTIST_NAME VARCHAR(50) NOT NULL,ADDRESS VARCHAR(50) NOT NULL,PHONE_NO INT NOT NULL,EMAIL_ID VARCHAR(50) NOT NULL, CHECK(PHONE_NO > 0), CHECK(EMAIL_ID LIKE ‘%_@__%.__%’),UNIQUE(EMAIL_ID))

TABLE : PAINTING

CREATE TABLE PAINTING (PAINTING_ID INT PRIMARY KEY, TITLE VARCHAR(50) NOT NULL,PAINTING_COST INT CHECK(PAINTING_COST > 0), ARTIST_ID INT, GALLERY_ID INT,   FOREIGN KEY (ARTIST_ID) REFERENCES ARTIST(ARTIST_ID), FOREIGN KEY (GALLERY_ID) REFERENCES GALLERY(GALLERY_ID))