Question

Flag Create a database for PAINTER and PAINTING entities/tables; Decide on your own what will be the attributes of PAINTER and PAINTING tables; Insert at least 5 records on each table Deliverables: Screenshot of PAINTER and PAINTING table structures using the describe command Screenshot of PAINTER and PAINTING table records/entries using select command.

Answer 1

solution:

Step 1: Analysing given entities and adding attributes:

Primary key is bold.

The system will have below entities:

1. PAINTER: This entity is required to store the details of a painter who makes the paintings. It can have below attributes:

PAINTER(PAINTER_ID, NAME, ADDRESS, COUNTRY_OF_ORIGON, CONTACT_NO)

2. PAINTING: This is the entity which stores the details of a paining with below attributes:

PAINING(PAINTING_ID, NAME, COMPLETED_ON, THEME, DESCRIPTION, COST)

Step 2: Relation between the entities:

A PAINTING is painted by one PAINTER, thus PAINTING table will have a foreign key reference from PAINTER table as below:

PAINTING (PAINTING_ID, NAME, COMPLETED_ON, THEME, DESCRIPTION, COST, PAINTED_BY)

Step 3: SQL to create tables:

Please find below the SQL to create database for the given tables:

---------------------------------------------

create database PAINT_AND_PAINTER; /*name of the database */

Use PAINT_AND_PAINTER;

CREATE TABLE PAINTER(PAINTER_ID INT PRIMARY KEY, NAME VARCHAR(100), ADDRESS VARCHAR(100), COUNTRY_OF_ORIGON VARCHAR(50), CONTACT_NO VARCHAR(12));

CREATE TABLE PAINTING (PAINTING_ID VARCHAR(10) PRIMARY KEY, NAME VARCHAR(100), COMPLETED_ON DATE, THEME VARCHAR(10), DESCRIPTION VARCHAR(100), COST double, PAINTED_BY int,

Foreign key (PAINTED_BY) references PAINTER(PAINTER_ID));

---------------------------------------------

Step 4: Insert records in the tables:

Please find below the insert query to insert the data in above tables:

INSERT INTO PAINTER values (1, 'Joe painter', 'ADD, Street 1, City1' , 'UK', '111-222-333');

INSERT INTO PAINTER values (2, 'Nick painter', 'ADD2, Street 2, City2' , 'LA', '222-333-444');

INSERT INTO PAINTER values (3, 'Liza painter', 'ADD3, Street 3, City3' , 'CA', '333-555-666');

INSERT INTO PAINTER values (4, 'Gray painter', 'ADD4, Street 4, City4' , 'US', '666-777-888');

INSERT INTO PAINTER values (5, 'Blue painter', 'ADD5, Street 5, City5' , 'IND', '000-999-888');

INSERT INTO PAINTING values('P1', 'Lonely Paint1', '1990-12-12', 'NATURE', 'DESCRPIOTN NATURE', 15687, 1);

INSERT INTO PAINTING values('P2', 'Happy Paint1', '1980-07-12', 'FAMILY', 'DESCRPIOTN FAMILY', 2605, 2);

INSERT INTO PAINTING values('P3', 'Simple Paint1', '1995-12-02', 'NATURE', 'DESCRPIOTN NATURE', 1150, 3);

INSERT INTO PAINTING values('P4', 'Art Paint1', '1997-05-09', 'PROFESSION', 'DESCRPIOTN PROFESSION', 5620, 5);

INSERT INTO PAINTING values('P5', 'Arling Paint1', '2010-12-01', 'NATURE', 'DESCRPIOTN NATURE', 159, 4);

Step 5: PAINTER and PAINTING table structures using the describe command

Command:

Describe PAINTER;

Describe PAINTING;

Step 5: PAINTER and PAINTING table records/entries using select command

Command:

Select* from PAINTER;

Select* from PAINTING;

please give me thumb up