Database-management-DBMS Lab test 1 Solved

1. Consider that we want to design a database that will store details of paintings in an art museum. The following information is given.   

Each painting is identified by a painting_id. We need to maintain the painting_name, painting_medium (paper, canvas and board) and completion_date for each painting. For some painting, completion_date may not be known. We also need to maintain data about the painters. Each painter is identified by a painter_id. Other attributes for painter are painter_name and date_of_birth. We also need to maintain details of different painting generations. Each such generation is identified by a generation_of_painting. Examples of possible values of generation_of_painting are Renaissance, Victorian, Impressionistic and Cubic, etc. For each such generation, there is a start_date and an end_date (which might be null for an ongoing generation). These denote from which date that painting generation started and when it ended. Each painting belongs to one and only one generation.

The museum keeps track of the location of each painting in the museum building. floor_no (integer) and room_no (integer) and capacity (in terms of number of paintings it can keep) have to be maintained for this purpose. The same room no. can repeat in different floors, i.e., there might be Room no. 3 in Floor 1 and also Room no. 3 in Floor 4. Each room can keep more than one painting but can have paintings of up to 3 painters. Paintings of the same painter can be located in different rooms in different floors of the building.  

(a)   Draw an Entity-relationship (E-R) diagram on a piece of paper clearly identifying all the entity sets (both strong and weak), relationship sets, cardinalities, participations and attributes (including the type of attribute like key attribute, multi-valued attribute, derived attribute, etc.). Strictly use the notation that uses rectangular boxes to denote entities with attributes inside the rectangles, diamonds to represent relations and cardinality is represented using the format l..h to denote the minimum and maximum cardinality as shown in the diagram given at the end of the question paper. Clearly state any reasonable assumption that you make.           [15 Marks]

(b)   From the ER-Diagram, derive the tables (See the naming convention below) using correct rules for relational model generation from ER model. Draw the database schema on a piece of paper. Clearly mark the columns, primary keys and foreign keys for each table. This forms your database schema. There should not be any redundant table in the database schema. All your table names must start with your <Roll_No> without the first two digits (For example, if your roll number is 18CS10001, your table names must start with CS10001). This should be followed by a random string (Between 2-3 character long) to complete the table name and not derived from the entity/relationship name. We will figure out which table corresponds to which entity/relationship in the ER Diagram. You need not worry about the same.  
(c)   Along with the database schema, write a query statement that would require an SQL SELECT statement which has to access all your tables. You do NOT need to write the SQL.  

(d)   Write appropriate CREATE table SQLs to create all your tables in the database (each table name in the format mentioned above). You must specify all appropriate data types, keys and constraints. We will test your remaining answers using these CREATE table statements. Hence, these should be valid SQL statements.       

(e)   Write a SELECT statement that retrieves all the painters along with the details of their paintings and corresponding painting generations where the total number of paintings of the painter is at least 2.  

Write INSERT statements to insert appropriate rows in the tables that would require execution of the above SQL statement and satisfy the requirements mentioned below. Show the results of execution of the SQL on your dataset using MySQl/Oracle. There should be at least 3 distinct painters returned as a result of the query. Output result format: (painter_id, painter_name, date_of_birth, painting_id, painting_name, painting_medium, completion_date, generation_of_painting, start_date, end_date).  

(f)    Write a SELECT statement that gives details of the locations of various paintings which have been completed (i.e., they have a valid completion_date) that belong to generations which have started but not yet ended (i.e., end_date is NULL).  

Delete all rows inserted for Question (e) and insert appropriate rows in the tables that would require execution of the above SQL statement and satisfy the requirements mentioned below. Show the results of execution of the SQL on this dataset using MySQL/Oracle. There should be at least 2 distinct physical museum rooms (i.e., 2 distinct floor_no, room_no combinations) and at least 3 distinct generations in your result set. Output result format: (floor_no, room_no, painter_id, painter_name, painting_id, painting_name,

          completion_date. generation_of_painting, start_date).                                                              

Submit the following through appropriate Moodle links:  

i.                    Scanned copies of the handwritten ER-Diagram (Problem a), handwritten Database Schema (Problem

b) and the handwritten problem statement (Problem c). At the top of each page your roll number and name should be written. The maximum total number of such pages can be 6. Formats accepted are: jpeg, png and pdf. No Zip file is allowed. Submit the files against the link for Questions a_b_c

ii.                  A text file (not handwritten) containing all the CREATE table statements for Question d. At the top of the file, your roll number and name should be written. Submit it against the link for Question d.

iii.                A text file containing the INSERT statements, the SELECT statement and the Query results (exported in text format from MySQL/Oracle) for Question e. At the end of this file, you should also write DELETE statements to delete all the rows from all the tables that were populated by the INSERT statements. We will use these statements to test the next question (Question f). At the top of the file, your roll number and name should be written. Submit the text file against the link for Question e.

iv.                A text file containing the INSERT statements, the SELECT statement and the Query results (exported in text format from MySQL/Oracle) for Question f. At the end of this file, you should also write DELETE statements to delete all the rows from all the tables that were populated by the INSERT statements.  We will use these statements to test the previous question (Question e). At the top of the file, your roll number and name should be written. Submit the text file against the link for Question f.

(All your file names should be distinct and they must start with LT1_<Roll_no>)