Consider the following SQL DDL statements: CREATE TABLE DEPT ( did INTEGER, dname VARCHAR(20), PRIMARY KEY(did));...

Consider the following SQL DDL statements:
CREATE TABLE DEPT ( did INTEGER,
dname VARCHAR(20),
PRIMARY KEY(did));

CREATE TABLE EMP( eid INTEGER,
name VARCHAR(20),
did INTEGER,
PRIMARY KEY(eid),
FOREIGN KEY(did) REFERENCES DEPT);

In the database created by above statements, which of the following operations may cause violation
of referential integrity constraints?

Question 1 options:

	UPDATE on DEPT
	INSERT into DEPT
	DELETE on EMP
	Both DELETE on EMP and INSERT into DEPT

Expert Solution

Solution

DELETE on EMP

Explanation
In the DEPT table there are no referential integrity that means no foreign key
if you insert any values or update values it will not cause any violation
for e.g

as you can see we are inserting 2 new records and updating existing records in the DEPT violation there are no violation

--

but deleting a record cause referential integrity violation because

FOREIGN KEY(did) REFERENCES DEPT);

did is the foreign key

---

all the best

venereology answered 1 year ago

Consider the following table definitions create table node( node_id integer primary key, node_color varchar(10)); create table...

Consider the following table definitions create table node( node_id integer primary key, node_color varchar(10)); create table edge( edge_id integer primary key, origin_id integer, destination_id integer, foreign key (origin_id) references node(node_id), foreign key (destination_id) references node(node_id)); What is the result of the following query? select node_id, node_color, destination_id from node, edge; An inner join of the tables node and edge that lists origin node_id and node_color together with the node_id of the destination node for all those nodes that have outgoing...

create table node( node_id integer primary key, node_color varchar(10)); create table edge( edge_id integer primary key,...

create table node( node_id integer primary key, node_color varchar(10)); create table edge( edge_id integer primary key, origin_id integer, destination_id integer, foreign key (origin_id) references node(node_id), foreign key (destination_id) references node(node_id)); write an SQL query that lists all those nodes that have edges with a destination node that has color 'red'.

CREATE TABLE Branch ( branchNo VARCHAR(4), address VARCHAR(50), city VARCHAR(30), state VARCHAR(2), phone VARCHAR(20), PRIMARY KEY...

CREATE TABLE Branch ( branchNo VARCHAR(4), address VARCHAR(50), city VARCHAR(30), state VARCHAR(2), phone VARCHAR(20), PRIMARY KEY (branchNo)); INSERT INTO Branch VALUES('B001','366 Tiger Ln','Los Angeles','CA','213-539-8600'); INSERT INTO Branch VALUES('B002','18 Harrison Rd','New Haven','CT','203-444-1818'); INSERT INTO Branch VALUES('B003','55 Waydell St','Essex','NJ','201-700-7007'); INSERT INTO Branch VALUES('B004','22 Canal St','New York','NY','212-055-9000'); INSERT INTO Branch VALUES('B005','1725 Roosevelt Ave','Queens','NY','718-963-8100'); INSERT INTO Branch VALUES('B006','1471 Jerrold Ave','Philadelphia','PA','267-222-5252'); CREATE TABLE Staff ( staffNo VARCHAR(4), fName VARCHAR(20), lName VARCHAR(20), position VARCHAR(20), sex VARCHAR(1), age INTEGER, salary NUMBER(8,2), phone VARCHAR(20), address VARCHAR(50), city VARCHAR(20),...

This is the database CREATE TABLE AIRCRAFT ( AC_NUMBER varchar(5) primary key, MOD_CODE varchar(10), AC_TTAF double,...

This is the database CREATE TABLE AIRCRAFT ( AC_NUMBER varchar(5) primary key, MOD_CODE varchar(10), AC_TTAF double, AC_TTEL double, AC_TTER double ); INSERT INTO AIRCRAFT VALUES('1484P','PA23-250',1833.1,1833.1,101.8); INSERT INTO AIRCRAFT VALUES('2289L','DC-90A',4243.8,768.9,1123.4); INSERT INTO AIRCRAFT VALUES('2778V','MA23-350',7992.9,1513.1,789.5); INSERT INTO AIRCRAFT VALUES('4278Y','PA31-950',2147.3,622.1,243.2); /* -- */ CREATE TABLE CHARTER ( CHAR_TRIP int primary key, CHAR_DATE date, AC_NUMBER varchar(5), CHAR_DESTINATION varchar(3), CHAR_DISTANCE double, CHAR_HOURS_FLOWN double, CHAR_HOURS_WAIT double, CHAR_TOT_CHG double, CHAR_OIL_QTS int, CUS_CODE int, foreign key (AC_NUMBER) references AIRCRAFT(AC_NUMBER) ); INSERT INTO CHARTER VALUES(10001,'2008-02-05','2289L','ATL',936,5.1,2.2,354.1,1,10011); INSERT INTO CHARTER VALUES(10002,'2008-02-05','2778V','BNA',320,1.6,0,72.6,0,10016);...

1. Use SQL to create a polyinstantiated table including a primary key and a unique constraint...

1. Use SQL to create a polyinstantiated table including a primary key and a unique constraint 2.Use SQL to insert multiple records for each security classification with the same ID. You must have 4 classifications. 3.Use SQL to create 4 schemas, one for each security classification 4.Use SQL to create a view in each schema that restricts the records to those belonging to a particular security classification and restricts the columns to only those columns that have relevant data. 5.Select...

USE SQL CREATE TABLE IF NOT EXISTS football_games ( visitor_name VARCHAR(30), /* Name of the visiting...

USE SQL CREATE TABLE IF NOT EXISTS football_games ( visitor_name VARCHAR(30), /* Name of the visiting team */ home_score SMALLINT NOT NULL, /* Final score of the game for the Buffs */ visitor_score SMALLINT NOT NULL,/* Final score of the game for the visiting team */ game_date DATE NOT NULL, /* Date of the game */ players INT[] NOT NULL, /* This array consists of the football player ids (basically a foreign key to the football_player.id) */ PRIMARY KEY(visitor_name, game_date)...

CREATE TABLE youtubevideos( url VARCHAR(150), title VARCHAR(50), description VARCHAR(200), comid INTEGER NOT NULL, postuserVARCHAR(50) NOT NULL,...

CREATE TABLE youtubevideos( url VARCHAR(150), title VARCHAR(50), description VARCHAR(200), comid INTEGER NOT NULL, postuserVARCHAR(50) NOT NULL, postdate DATE, PRIMARY KEY (email), FOREIGN KEY (comid) REFERENCES Comedians(comid), FOREIGN KEY (postuser) REFERENCES Users(email)); CREATE TABLE Users( email VARCHAR(50), password VARCHAR(50), firstname VARCHAR(50), lastname VARCHAR(50), gender CHAR(1), age INTEGER, PRIMARY KEY (email)); CREATE TABLE Comedians( comid INTEGER, firstname VARCHAR(50), lastname VARCHAR(50), birthday DATE, VARCHAR(50), PRIMARY KEY(comid)); CREATE TABLE Reviews( reviewid INTEGER NOT NULL AUTO_INCREMENT, remark VARCHAR(100), rating CHAR(1), //P.F.G.E author VARCHAR(50) NOT NULL,...

Create table, create primary and foreign key constraints. Create index on the table to satisfy a...

Create table, create primary and foreign key constraints. Create index on the table to satisfy a query with aggregate functions.

-- Creating table ProjDept: create table ProjDept ( ProjDeptID NUMBER(10) primary key, ProjDeptName varchar2(20), OfficeLocation varchar2(20),...

-- Creating table ProjDept: create table ProjDept ( ProjDeptID NUMBER(10) primary key, ProjDeptName varchar2(20), OfficeLocation varchar2(20), PhoneNumber varchar2(20) ); INSERT INTO ProjDept (ProjDeptID, ProjDeptName, OfficeLocation, PhoneNumber) VALUES (1001, 'Accounting','ITCC01-400','888-285-8100'); (2001, 'Human Resources','ITCC01-200','888-285-8100'); (3001, 'Marketing','ITCC02-300','888-285-8100'); (4001, 'Information Techn','ITCC02-100','888-285-8100'); (5001, 'Legal','ITCC01-100','888-285-8100'); -- Creating table Employee: create table Employee( EmployeeID NUMBER(10) primary key, FirstName varchar2(20), LastName varchar2(20), ProjDeptID NUMBER(10), PhoneNumber varchar2(20) ); INSERT INTO Employee (EmployeeID, FirstName, LastName, ProjDeptID, PhoneNumber, Email) VALUES (10, 'Mark','Columbus',1001, '888-285-8101', '[email protected]'); (29, 'Elvin','Wahl', 2001, '888-285-8201', '[email protected]'); (38, 'Taylor','Noel',...

Create a table book_store with columns Book_id VARCHAR(255) NOT NULL, Book_Name VARCHAR(255) NOT NULL, Book_genre VARCHAR(255)...

Create a table book_store with columns Book_id VARCHAR(255) NOT NULL, Book_Name VARCHAR(255) NOT NULL, Book_genre VARCHAR(255) NOT NULL, Status VARCHAR(255) NOT NULL, PRIMARY KEY (Book_id) Create a table book with columns Book_id VARCHAR(255) NOT NULL, Book_Name VARCHAR(255) NOT NULL, Book_release integer, Book_price integer , Publisher Varchar(10), Book_genre VARCHAR(255) NOT NULL, PRIMARY KEY (Book_id) CREATE TABLE price_logs with columns id INT(11) NOT NULL AUTO_INCREMENT, Book_id VARCHAR(255) NOT NULL, Old_Book_price DOUBLE NOT NULL, New_Book_price DOUBLE NOT NULL, updated_at TIMESTAMP NOT NULL DEFAULT...

Question