Answered step by step
Verified Expert Solution
Link Copied!

Question

1 Approved Answer

In SQL 1) Modify the employee table created in lab 3, add columns and constraints as specified below. Then create the other tables with storage

In SQL 1) Modify the employee table created in lab 3, add columns and constraints as specified below. Then create the other tables with storage parameters, specify INITIAL extent size 65536 NEXT extent size 65536 Percent increase (PCTINCREASE) size 0 MINimum extents 1 Maximum extents 123 ); table space users. TABLE NAME COLUMN NAME DATATYPE NN PK ______________________________________________________ Employee emp_num number yes yes emp_fname varchar2(50) yes emp_lname varchar2(50) yes birth_dt date yes item item_num number yes yes item_descrip varchar2(50) yes STATE STATE_CD varchar2(2) yes yes STATE_NAME varchar2(50) yes STORE STORE_NUM NUMBER yes yes STATE_CD character(2) yes STORE_ITEM STORE_NUM NUMBER(2) yes yes ITEM_NUM NUMBER(2) yes yes 2) Create a primary key constraint for each table using a index, specify tablespace user_index and storage clauses. 3) Create foreign key constraints where needed. 4) Create a sequences for each table with a system assigned pk 5) Create table comments for each table . 6) Create column comments for each table column . 7) alter the employee table to include the column last_updt_dt with datatype date . 8) Create a spool file to be mailed to me. sqlplus> spool lab4.spo 9) select the table comments and column comments in your schema, limit the column length listed to 30 characters. 10) Select the table names of the tables in your schema 11) select the table name and index name of the indexes in your schema 12) select the sequence names of sequences in your schema 13) select the constraint_name of the primary key constraints in your schema. Hint : CONSTRAINT_TYPE='P' . 14) select the constraint_name of the Foreign key constraints in your schema. Hint : CONSTRAINT_TYPE='R' . 15) exit sqlplus 16) mail the spooled listing to taylorrg please include your name in the subject mail -s 'lab4 from yourname ' taylorrg@buffalostate.edu < lab4.spo A) Data Definition Language (DDL) to create a table (segment) named employee including storage parameters. Create table employee (emp_num number(11) not Null, emp_Lname varchar2(50), birth_dt date) tablespace users storage (INITIAL 65536 NEXT 65536 PCTINCREASE 0 MINEXTENTS 1 MAXEXTENTS 123 ); B) DDL that creates a table comment for the employee table. comment on table EMPLOYEE is ' EMPLOYEE PERSONAL INFO '; c) DDL that creates a column comment for emp_num in employee table. COMMENT ON COLUMN EMPLOYEE.EMP_NUM IS ' Employee number PK '; D) DDL that creates a primary key constraint with user named index including storage parameters. ALTER TABLE employee ADD CONSTRAINT pk_emp_num PRIMARY KEY (emp_num) USING INDEX Tablespace user_index storage ( INITIAL 65536 NEXT 65536 PCTINCREASE 0 MINEXTENTS 1 MAXEXTENTS 120 ); e) DDL that creates a foreign key reference. ALTER TABLE store ADD CONSTRAINT FK_store_statecd FOREIGN KEY (state_cd) REFERENCES state (state_cd) ; f) DDL that creates a sequence that will later be used for a system assigned primary key for the employee table . CREATE SEQUENCE emp_num_seq INCREMENT BY 1 START WITH 1 MINVALUE 0 MAXVALUE 99999999999 NOCACHE; g) DDL that alters the employee table to include a new column last_updt_dt of datatype date. Alter table employee add (last_updt_dt date);

Step by Step Solution

There are 3 Steps involved in it

Step: 1

blur-text-image

Get Instant Access to Expert-Tailored Solutions

See step-by-step solutions with expert insights and AI powered tools for academic success

Step: 2

blur-text-image

Step: 3

blur-text-image

Ace Your Homework with AI

Get the answers you need in no time with our AI-driven, step-by-step assistance

Get Started

Recommended Textbook for

The Database Experts Guide To SQL

Authors: Frank Lusardi

1st Edition

0070390029, 978-0070390027

More Books

Students also viewed these Databases questions

Question

Does it have at least one-inch margins?

Answered: 1 week ago

Question

Does it highlight your accomplishments rather than your duties?

Answered: 1 week ago