Question

Task 2 [40 points Given an ER diagram (as shown below), write SQL statements to create the corresponding relations and capture as many of the constraints as possible. If you cannot capture some constraints, explain why. d_name p_id title salary d id budget manages phone Professor Department Building works_in Dependent Child name age Task 3 [20 points). Suppose that we have a ternary relationship R between entity sets A, B, and C such that (i) A has a key constraint; and (ii) C has a key constraint and total participation. These are the only constraints. A has attributes al and a2, with al being the key; B has attributes b1 and 52, with b1 being the key; and Chas attributes cl and c2, with cl being the key. R has no descriptive attributes. All of the above-mentioned attributes are to be stored as Strings of 10 characters. Write SQL statements that create tables corresponding to this information so as to capture as many of the constraints as possible. If you cannot capture some constraint, explain

CAN YOU PLEASE EXPLAIN IN TASK 3 - HOW DO WE REPRESENT TOTAL PARTICIPATION AND KEY CONSTRAINTS IN SQL CODE. PLEASE PROVIDE SOLUTION AND DETAILED EXPLANATION.

I WOULD REALLY APPRECIATE IF YOU CAN HELP ME WITH THE ABOVE 2 QUESTIONS.

I WILL DEFINITELY RATE!!

Thank you.

Answer 1

(A.) FIrst we will Create the table from the given ER diagram , Each entities will have there own different tables every attributes become the column of the table, with this concept there will be three tables:

(1) First table will be the PROFESSOR table in which Professor is the strong entity and hence each tuples of the Professor table can be uniquely identified by primary key which is P id (as this attribute is represented by underline in the ER diagram).Also there are two relation between table professor and department table, one is professor works in department and other is department manages by professor, for first relation (works in) we need foreign key to uniquely identify the Department in Professor table on join. therefore there is the need of KEY CONSTRAINTS in the Professor table ,also P id is the primary key of the table therefore value of P id cannot be null hence we will add NOT NULL constraints in the table construction.SQL query will be :-

CREATE TABLE Professor (
P id int (10)NOT NULL,
title varchar(5),
    Phone int(10), Salary int(10), Building varchar(50),
    PRIMARY KEY (P id),
    FOREIGN KEY (D id) REFERENCES Department(D id)
);

(2) Second table will be the DEPARTMENT table in which Department is the strong entity and hence each tuples of the department table can be uniquely identifies by primary key which is D id (as this attribute is represented by underline in the ER diagram).Also there are two relation between table department and professor table, one is professor works in department and other is department manages by professor, for second relation (manages) we need foreign key to uniquely identify the professor in department table on join. therefore there is the need of KEY CONSTRAINTS in the Department table ,also D id is the primary key of the table therefore value of D id cannot be null hence we will add NOT NULL constraints in the table construction.

SQL query will be :-

CREATE TABLE Department (
D id int (10)NOT NULL,
D name varchar(25),
budget int(10),
    PRIMARY KEY (D id),
    FOREIGN KEY (P id) REFERENCES Professor(P id)
);

(3) Third table will be the CHILD table in which Child is the weak entity as there is no primary key which is able to uniquely distinguish each tuples ,name can be a key but it is not able to distinguish all the tuples, therefore we need any foreign key to uniquely identify the tuples of the child table .Also there is a relation between table department and child table, which is child has professor as dependent ,there is the need of REFERENTIAL INTEGRITY  CONSTRAINTS in the Child table during the table construction.

SQL query will be :-

CREATE TABLE Child (
Name varchar(25),
Age int(2),
    FOREIGN KEY (P id) REFERENCES Professor(P id)
);

(B.) Now if we talk about the second part of the question clearly here also we have to make three tables A,B,C which shares the ternary relationship which means each table is connected to other table with one same relationshipwhich are following:-

(1.) The table A contains a1 and a2 as attributes which are of string types of length 10 and attribute a1 is the primary key and table A has the key constraints, with these points we can create table by using SQL query as follows:-

CREATE TABLE A (
a1 varchar(10)NOT NULL,
a2 varchar(10),
    PRIMARY KEY (a1),
    FOREIGN KEY (b1) REFERENCES B(b1),   FOREIGN KEY (c1) REFERENCES C(c1)
);

(2.) The table B contains b1 and b2 as attributes which are of string types of length 10 and attribute b1 is the primary key and table B has the key constraints, with these points we can create table by using SQL query as follows:-

CREATE TABLE B (
b1 varchar(10)NOT NULL,
b2 varchar(10),   
    PRIMARY KEY (b1),
    FOREIGN KEY (a1) REFERENCES A(a1),   FOREIGN KEY (c1) REFERENCES C(c1)
);

(3.) The table C contains c1 and c2 as attributes which are of string types of length 10 and attribute c1 is the primary key and table C has the key constraints and total participation which can be represented by using foreign key in query, with these points we can create table by using SQL query as follows:-

CREATE TABLE C (
c1 varchar(10)NOT NULL,
c2 varchar(10),
    PRIMARY KEY (c1),
    FOREIGN KEY (a1) REFERENCES A(a1),   FOREIGN KEY (b1) REFERENCES B(b1)
);