Scribd
Upload
11 views3 pages

DBMS - Lab Practical Assignment #3

The document outlines an experiment focused on implementing data constraints in Oracle within a Microsoft Windows environment. It details the creation of tables for Department, Employee, and Candidate with specific constraints, as well as instructions for creating schemas and modifying them. The document serves as a practical assignment for a Database Management System lab course in Computer Engineering and Applications.

Uploaded by

ay188039
Copyright
© All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as PDF, TXT or read online on Scribd
11 views3 pages

DBMS - Lab Practical Assignment #3

The document outlines an experiment focused on implementing data constraints in Oracle within a Microsoft Windows environment. It details the creation of tables for Department, Employee, and Candidate with specific constraints, as well as instructions for creating schemas and modifying them. The document serves as a practical assignment for a Database Management System lab course in Computer Engineering and Applications.

Uploaded by

ay188039
Copyright
© All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as PDF, TXT or read online on Scribd

Experiment No.

Environment: Microsoft Windows


Tools/ Language: Oracle

OBJECTIVE: To implement the restrictions on the structure of the table.


Theory:
Data constraints: Besides the column name, column length and column data type,
there are other parameters i.e. other data constraints that can be passed by the DBA
at check creation time. The constraints can either be placed at column level or at the
table level.
i. Column Level Constraints: If the constraints are defined along with the
column definition,
definiti it is called a column level constraint.
ii. Table Level Constraints: If the data constraint attached to a specified
column in a table reference the contents of another column in the table then
the user will have to use table level constraints.

List of mostt used Constraint


NOT NULL
DEFAULT
UNIQUE
CHECK
PRIMARY KEY
FOREIGN KEY
Practical Assignment - 3

Department:Computer Engineering & Applications


Course: BCA
Subject:Database Management System Lab (BCAC-0816)
Year: 1ST Semester: 2ND

1. Create the following tables and specify constraints at the time of creation.

Department
Column Data Size Constraint
Name Type
Deptno number 3 primary key
Dname varchar2 20 Unique
Location varchar2 20 not null, department are located in Delhi, Pune,
Agra

Employee
Column Data Size Constraint
Name Type
Empno varchar2 5 primary key, should start with ‘E’
Ename varchar2 20 Unique
Designation varchar2 20 not null
Salary number 10 default 25000, must lie between 15000 and
50000
DOB date not null
Dno number 3 foreign key (references department)

Candidate
Column Name Data type Size Constraints
Candidate_ID Number 6 Primary key of the table
Candidate_Name Varchar2 20 Not Null
Candidate_Email Varchar2 30 Unique, Must have ‘@’ followed by ‘.’ in
between the email
Candidate_Dept Number 2 Default ‘HR’
Manager_ID Varchar2 30 It can take only those values which are
present in Candidate_ID column
2. Create the schemas as specified above without specifying any constraints.

College (cName: varchar2(10),c state: varchar2(10), enrollment: int)


Student (sID: int, sName: varchar2(10), CGPA:
number(2,1),marks:int) Apply (sID: int, cName: varchar2(10), major:
varchar2(20))
(i) Add cName as Primary key in College.
(ii) Add sID as Primarykey in Student.
(iii) Add sID, cName, major as Primary key in Apply.
(iv) Add a new column decision in the Apply table keeping a constraint of not
null for this column with data type varchar2(3).
(v) Change data type of decision in Apply to char(1).
(vi) Drop foreign key on column name cName from Apply table.
(vii) Remove column CGHS from Student table.
(viii) Drop primary key from College
(ix) Modify foreign key on sID in Apply table to foreign key on delete set null.
(x) Rename column enrollment to enroll in College Table.

You might also like