Question

Database Systems

Lab Exercises

1. Create a table Faculty based on the following chart:

Column	Data type	Constraints
Faculty_Id	Number (6)	Primary Key => faculty_pk
Last_Name	Varchar2(15)	Not NULL
First_Name	Varchar2(15)	Not NULL
Dept	Char(3)

Save the SQL statement as ex1.sql. Confirm and validate the creation of the new table.

2. Create a table Dept based on the following chart:

Column	Data type	Constraints
Dept_Code	Char (3)	Primary Key => dept_pk
Dept_Name	Varchar2(20)	Not NULL

Save the SQL statement as ex2.sql. Confirm and validate the creation of the new table.

3. Add a new column Location to Dept table which has data type Char(7). Save the SQL statement as exsql. Confirm and validate the modification of the table.
4. Add a new FOREIGN KEY constraint (named faculty_dept_fk) to column Dept on Faculty table that refers to column Dept_Code on Dept table. Add ON DELETE CASCADE option. Save the SQL statement as exsql. Confirm and validate the modification of the table.

5. Increase Last_Name column to 25 characters long. Save the SQL statement as exsql. Confirm and validate the modification of the table

use this to draw https://www.diagrameditor.com/

Answer 1

Ex1.sql:

CREATE TABLE Faculty(
    Faculty_Id            Number (6) NOT NULL,
    Last_Name   Varchar2(15) NOT NULL,
    First_Name   Varchar2(15) NOT NULL,
    Dept   Char(3),
    CONSTRAINT Faculty_pk PRIMARY KEY(Faculty_Id)
    );

Ex2.sql;

CREATE TABLE Dept(
    Dept_Code            Char(3) NOT NULL,
    Dept_Name   Varchar2(20) NOT NULL,
    CONSTRAINT Dept_pk PRIMARY KEY(Dept_Code)
    );

Deccription of the created table:

    select *
  from user_tab_columns

Exsql.sql;

Add location:

 Alter table Dept
  ADD Location Char(7);

    select *
  from user_tab_columns

exsql.sql

Alter table Faculty
ADD Constraint Faculty_Dept_fk FOREIGN KEY(Dept) REFERENCES Dept(Dept_Code)on delete cascade;

exsql.sql

SQL Server / MS Access:

ALTER TABLE Faculty
ALTER COLUMN last_name Varchar2(25);

My SQL / Oracle (prior version 10G):

ALTER TABLE Faculty
MODIFY COLUMN last_name Varchar2(25);

Oracle 10G and later:

ALTER TABLE Faculty
MODIFY last_name Varchar2(25);

    select *
  from user_tab_columns

Since you have not mentioned the diagram that is needed, I have drawn a basic diagram .Hope that helps!