Question

This assignment your tasks is to convert the E-R diagram to a relational model and then to SQL. The mapping process is straightforward. Three components of the conversion process are Specify schema of relation itself Specify primary key on the relation Specify any foreign key references to other relations 1. 2. 3. dateBorn mName birthPlace IName dateDied fName IName address - oCountry ID mName ttlAmt Customers N Artists M Favors fName artStyle ID N Creates Like M startedDate theme title wide endedDate year name Exhibition N height size Artworks Display description style price Statue Painting weight material type material Sculpture weight material Task 1 (70 points): Write SQL statements to create the corresponding relations and capture as many of the constraints as possible. If cannot capture some constraints, explain why. Make sure that the data type and size for each attribute are appropriate. For example, a numerical data type is a better choice than a string data type for the attribute ttlAmt.

Answer 1

drop table Artists cascade constraints;
drop table customers cascade constraints;
drop table ARTWORKS cascade constraints;
drop table EXHIBITION cascade constraints;
drop table PAINTING cascade constraints;
drop table SCULPTURE cascade constraints;
drop table STATUE cascade constraints;

CREATE TABLE ARTISTS(ART_ID CHAR(3) PRIMARY KEY,
                   FNAME VARCHAR(15) NOT NULL,
                   MNAME VARCHAR(10),
                   LNAME VARCHAR(15) NOT NULL,
                   BIRTHPLACE VARCHAR(15),
                   DATEBORN DATE,
                   DATEDIED DATE,
                   OCOUNTRY VARCHAR(15),
                   ARTSTYLE VARCHAR(15) NOT NULL);
                  
CREATE TABLE CUSTOMERS(CUST_ID CHAR(3) PRIMARY KEY,
                   FNAME VARCHAR(15) NOT NULL,
                   MNAME VARCHAR(10),
                   LNAME VARCHAR(15) NOT NULL,
                   ADDRESS VARCHAR(15) NOT NULL,
                   TTAMOUNT INT NOT NULL,
                   ART_ID CHAR(3),
                   FOREIGN KEY(ART_ID) REFERENCES ARTISTS(ART_ID));
                  
CREATE TABLE ARTWORKS(ART_WO_ID CHAR(4) PRIMARY KEY,
                   TITLE VARCHAR(15)NOT NULL,
                   YEAR INT NOT NULL,
                   WIDE INT NOT NULL,
                   HEIGHT INT NOT NULL,
                   DESCRIPTION VARCHAR(15),
                   PRICE INT NOT NULL,
                   ART_ID CHAR(3),
                   FOREIGN KEY(ART_ID) REFERENCES ARTISTS(ART_ID));
                     
CREATE TABLE EXHIBITION(NAME VARCHAR(15) PRIMARY KEY,
                       THEME VARCHAR(15) NOT NULL,
                       STARTDATE DATE NOT NULL,
                       ENDDATE DATE NOT NULL,
                       ART_WO_ID CHAR(4),
                       FOREIGN KEY(ART_WO_ID) REFERENCES ARTWORKS(ART_WO_ID));
                      
CREATE TABLE PAINTING(TYPE VARCHAR(15) NOT NULL,
                   MATERIAL VARCHAR(15),
                   ART_WO_ID CHAR(4) ,
                   FOREIGN KEY(ART_WO_ID) REFERENCES ARTWORKS(ART_WO_ID));
                  
CREATE TABLE SCULPTURE(WEIGHT INT,
                   MATERIAL VARCHAR(15),
                   ART_WO_ID CHAR(4) ,
                   FOREIGN KEY(ART_WO_ID) REFERENCES ARTWORKS(ART_WO_ID));
                  
CREATE TABLE STATUE(WEIGHT INT,
                   MATERIAL VARCHAR(15),
                   ART_WO_ID CHAR(4) ,
                   FOREIGN KEY(ART_WO_ID) REFERENCES ARTWORKS(ART_WO_ID));
                  
                      
DESC ARTISTS;
DESC CUSTOMERS;
DESC ARTWORKS;
DESC EXHIBITION;
DESC PAINTING;
DESC SCULPTURE;
DESC STATUE;

ARTISTS Artists may or may not be contain middle name and hence middle name can contain null value.
Similarly DOB,datedied and birthpalce may or nay not be exists for a specified artists and hence these three
fields may contain null values.

CUSTOMERS customer may or may not be contain middle name and hence middle name can contain null value.

ARTWORK some authors want to describe the ART and some do not want to describe hence decription field ma contain null values.

PAINTING - Material used for painting may contain null values.

SCULPTURE,STATUE if Artists want to mention weight and material of SCULPTURE,STATUE then they mention other it contains null values.

SQL> SQL> DESC ARTISTS Name Nu11? Type NOT NULL CHAR(3) ART_ID FNAME NOT NULL UARCHAR2(15) UARCHAR2(10) NOT NULL UARCHAR2(15) UARCHAR2(15) MNAME LNAME BIRTHPLACE DATEBORN DATE DATEDIED DATE UARCHAR2(15) NOT NULL UARCHAR2 (15) OCOUNTRY ARTSTYLE SQL> DESC CUSTOMERS; Nul1? Name Type CUST_ID FNAME NOT NULL CHAR(3) NOT NULL UARCHAR215) UARCHAR2(10) NOT NULL UARCHAR2 (15) NOT NULL UARCHAR215) NOT NULL NUMBER( 38) CHAR(3) MNAME LNAME ADDRESS TTAMOUNT ART ID SQL> DESC ARTWORKS; Name Nul1? Type ART WO_ID TITLE NOT NULL CHAR(4) NOT NULL UARCHAR215) NOT NULL NUMBER ( 38) NOT NULL NUMBER ( 38) NOT NULL NUMBER (38) UARCHAR2(15) NOT NULL NUMBER(38) CHAR(3) YEAR WIDE НЕI GHT DESCRIPTION PRICE ART ID SQL> DESC EXHIBITION; Name Nul1? Type NAME NOT NULL UARCHAR2(15) NOT NULL UARCHAR2(15 THEME STARTDATE NOT NULL DATE ENDDATE NOT NULL DATE CHAR() ART_WO_ID

SQL> DESC PAINTING; Мane Nul1? Type ТУРЕ NOT NULL UARCHAR2(15) UARCHAR2(15) CHAR(4) МАTERIAL ART_WO_ID SQL> DESC SCULPTURE; Name Nul1? Type NUMBER (38) VARCHAR2(15) CHAR(4) WEIGHT MATERIAL ART_WO_ID SQL> DESC STATUE; Type Name Nul1? NUMBER (38) UARCHAR2(15) CHAR (4) WEIGHT MATERIAL ART_WO_ID SQL>

ARUN'S- ADENO N 3978 CATE ARTISTS ARI-ID ENAME MNAME LNAME BIRTHPLACE DATEBORN DATEDIEO OC OUNTRY ARTSTYLE FUSTOMERS CuS7-1D ENNAM EMNAME 3WUNT ADDRESS TTAMOaT ART-10 ARTWDRKS ART.WO_ID TITLE YEAR WIDE HEL&HT DESCRIPTION PRICE ART-TD EXHIBITION NAME THEME START DATE ENDDDATE AR7 NO JD PALNTING TYPE MATERIAL ART-WO-JD SCULPTURE WEIG HT MATERIAL fiRT_alO-D STATUE 1AE GHT MA7ERIAL ARI ulo TD