-- Schema definition
create table Customer (
cid smallint not null,
name varchar(20),
city varchar(15),
constraint customer_pk
primary key (cid)
);
create table Club (
club varchar(15) not null,
desc varchar(50),
constraint club_pk
primary key (club)
);
create table Member (
club varchar(15) not null,
cid smallint not null,
constraint member_pk
primary key (club, cid),
constraint mem_fk_club
foreign key (club) references Club,
constraint mem_fk_cust
foreign key (cid) references Customer
);
create table Category (
cat varchar(10) not null,
constraint category_pk
primary key (cat)
);
create table Book (
title varchar(25) not null,
year smallint not null,
language varchar(10),
cat varchar(10) not null,
weight smallint not null,
constraint book_pk
primary key (title, year),
constraint book_fk_cat
foreign key (cat) references Category,
constraint book_weight
check (weight > 0)
);
create table Offer (
club varchar(15) not null,
title varchar(25) not null,
year smallint not null,
price decimal(5,2) not null,
constraint off_pk
primary key (club, title, year),
constraint off_fk_club
foreign key (club) references Club,
constraint off_fk_book
foreign key (title, year) references Book,
constraint off_price
check (price > 0)
);
create table Purchase (
cid smallint not null,
club varchar(15) not null,
title varchar(25) not null,
year smallint not null,
when timestamp not null,
qnty smallint not null,
constraint pur_pk
primary key (cid, club, title, year, when),
constraint pur_fk_offer
foreign key (club, title, year) references Offer,
constraint pur_fk_mem
foreign key (club, cid) references Member,
constraint pur_qnty
check (qnty > 0)
);
-- A prettier way to view the PURCHASE table.
create view Pretty_Purchase as
(select cast (cid as decimal(2)) as cid,
title,
club,
year,
cast (when as date) as day,
cast (when as time) as time
from purchase);
create table Shipping (
weight smallint not null,
cost decimal(5,2) not null,
constraint shipping_pk
primary key (weight),
constraint ship_uni_cost
unique (cost)
);
Based on the snapshot pasted and the 2 questions mentioned under that, we can answer them as follows:
1. CREATE TABLE CREDIT
(
cid smallint not null, title varchar(25) not null, year smallint not null, when timestamp not null, old_club varchar(15) not null, new_club varchar(15) not null, done timestamp not null
);
2.After Trigger
CREATE OR REPLACE TRIGGER ClubChange
AFTER UPDATE OF CLUB ON PURCHASE
FOR EACH ROW ENABLE
BEGIN
INSERT INTO CREDIT VALUES(:OLD.CID, :OLD.TITLE, :OLD.YEAR, :OLD.WHEN, :OLD.CLUB, :NEW.CLUB, SYSDATE);
END;
/
Explanation: Triggers are named blocks that can execute whenever a condition is met. Here, we are executing an insert based on update on a particular column.
The syntax of trigger: first line defining the creation with name, second line defining when the trigger is to run (After an update is done on column CLUB of PURCHASE table), third line stating that it has to be in Enabled state, and execute for each row.
Here, during insertion you will see :OLD , :NEW predictes being used. Basically, the pseudorecord is present in a trigger, so, the whole old record updated is available to use (predefined) in :OLD clause, and the whole new record is available to us using the predefined :NEW clause, through which we can access the older / newer values.
-- Schema definition create table Customer ( cid smallint not null, name varchar(20), ...
Database Management 6. [5] Create the following table: CREATE TABLE customer ( cust_name VARCHAR(30) NOT NULL, address VARCHAR(60), UNIQUE (cust name, address)); A. Run the following inserts and explain why both work and how will you prevent it INSERT INTO customer VALUES ('Alex Doe', NULL); INSERT INTO customer VALUES ('Alex Doe', NULL); 7. [5] Modify the following table definition to ensure that all employees have a minimum wage of $10 CREATE TABLE employee ( empId INT PRIMARY KEY, empName VARCHAR(40)...
Using the table below, Write 2 simple/short TRIGGER STATEMENTS (WITH GOOD BUSINESS VALUE), After writing the trigger statements, please comment on what the TRIGGER IS DOING. CREATE TABLE STUDENT_TBL ( STU_ID NUMBER (8) NULL, F_NAME VARCHAR (20) NULL, L_NAME VARCHAR (30) NULL, STU_MAJOR VARCHAR (20) NULL, STU_ADDR VARCHAR (50) NULL, STU_EMAIL VARCHAR (30) NULL, CONSTRAINT PKSTU primary key (STU_ID) ); DESCRIBE STUDENT_TBL; CREATE TABLE ENROLL_TBL ( ENR_ID NUMBER (8), ENR_DATE DATE (10)...
Utilize the JigSaw SQL file below to create a Star Schema diagram. Remember, to create a Star Schema from a normalized data model, you will need to denormalize the data model into fact and dimension tables. The diagram should contain all of the facts and dimension tables necessary to integrate the JigSaw operational database into a data warehouse. Write a brief paper describing the challenges you experienced in completing this assignment. -- CREATE DATABASE js; CREATE TABLE buy_methods ( buy_code...
Given the schema, write a query and subquery to do the following: CREATE TABLE Majors major VARCHAR(12), description VARCHAR, PRIMARY KEY (major) ); CREATE TABLE Course ( courseMajor VARCHAR(12), courseNo VARCHAR(6), credits INTEGER NOT NULL, enroll_limit INTEGER, PRIMARY KEY(courseNo, courseMajor), FOREIGN KEY (courseMajor) REFERENCES Majors (major) CREATE TABLE Tracks trackMajor VARCHAR(12), trackCode VARCHAR(10), title VARCHAR, PRIMARY KEY(trackMajor, trackCode), FOREIGN KEY (trackMajor) REFERENCES Majors(major) CREATE TABLE Student ( SID CHAR(8), sName VARCHAR(30), studentMajor VARCHAR(12), trackCode VARCHAR(10), PRIMARY KEY(SID), FOREIGN KEY (studentMajor,...
Create a stored procedure that allows a user to select a bat’s manufacturer and (optionally) serial number using a stored procedure. The output should display all of the players who use the bat’s manufacturer. If the serial number is also provided, only display the players who use that bat’s manufacturer and serial number. Make sure you use a CREATE PROCEDURE call and insert this procedure into the existing database. Submit a document that includes: 1. Commented code for the stored...
Hello, Does anyone have any input on how to fragment this table in visual studio? CREATE TABLE [dbo].[Stock] ( [itemNo] INT NOT NULL , [store] VARCHAR(50) NOT NULL, [qtyOnHand] INT NULL, [qtyHand] INT NULL, [qtyOnOrder] INT NULL, [reorderPoint] INT NULL, CONSTRAINT [FK_Stock_Item] FOREIGN KEY ([itemNo]) REFERENCES [Item]([itemNo]), CONSTRAINT [FK_Stock_Store] FOREIGN KEY ([store]) REFERENCES [Store]([storeName]), CONSTRAINT [PK_Stock] PRIMARY KEY ([itemNo], [store]), The other tables are: CREATE TABLE [dbo].[Item] ( [itemNo] INT NOT NULL PRIMARY KEY, [itemName] VARCHAR(50) NULL, [supplier] VARCHAR(50) NULL,...
please answer 56789 CREATE TABLE ALLDRINKS( /* All legal drinks */ DRINK VARCHAR(30) NOT NULL, /* Drink name */ CONSTRAINT DRINKNAME_PKEY PRIMARY KEY(DRINK) ); CREATE TABLE DRINKERS ( /* All drinkers */ DRINKER VARCHAR(30) NOT NULL, CONSTRAINT DRINKERS_PKEY PRIMARY KEY (DRINKER)); CREATE TABLE LOCATED( /* Pubs have locations */ PUB VARCHAR(30) NOT NULL, /* Pub name */ STREET VARCHAR(30) NOT NULL, /* Street name */ BLDG_NO DECIMAL(4) NOT NULL, /* Building number */...
create table candidate ( cand_id varchar(12) primary key, -- cand_id name varchar(40) -- cand_nm ); create table contributor ( contbr_id integer primary key, name varchar(40), -- contbr_nm city varchar(40), -- contbr_city state varchar(40), -- contbr_st zip varchar(20), -- contbr_zip employer varchar(60), -- contbr_employer occupation varchar(40) -- contbr_occupation ); create table contribution ( contb_id integer primary key, cand_id varchar(12), --...
You will create the following 4 triggers: - trgEmployee: Will be placed on the Employee table and listens for Inserts, Deletes, and Updates - trgJob: Will be placed on the Job table and listens for Inserts, Deletes, and Updates - trgProject: Will be placed on the Project table that contains the projectId and projectName. - trgActivity: Will be placed on the Activity table that contains the activityId and activityName. Again, each trigger will write to its respective audit table: trgProject...
Using SQL and the following info Question 3: Find the people who do not have Viper Certification but are still assigned to Viper class ship Find the fname, lname, and ship_instance_id for all people who do not have Viper certification but are assigned to at least one instance of a Viper class ship (this includes all variants of Viper class ships). Return a row for every ship/person combination. Order your results by fname in ascending order. Use the BSG database...