Nothing Special   »   [go: up one dir, main page]
Scribd Logo
Upload

Welcome to Scribd!

  • 4 views

    Exp 2

    Uploaded by

    mango0000007007

    Copyright:

    © All Rights Reserved
    Download as PDF, TXT or read online from Scribd

    Exp 2

    Uploaded by

    mango0000007007
    4 views7 pages

    Original Title

    Exp2

    Copyright

    © © All Rights Reserved

    Available Formats

    PDF, TXT or read online from Scribd

    Did you find this document useful?

    Is this content inappropriate?

    Copyright:

    © All Rights Reserved
    Download as PDF, TXT or read online from Scribd
    Download as pdf or txt
    4 views7 pages

    Exp 2

    Uploaded by

    mango0000007007

    Copyright:

    © All Rights Reserved
    Download as PDF, TXT or read online from Scribd
    Download as pdf or txt
    of 7

    Practical Assignment 2

    NAME: Piyush Kumar

    ROLL NO: 14

    BRANCH & SEC: BTECH CF (CSF)

    YEAR: 1st

    SEMESTER: 2nd

    SUBJECT: Database Management System Lab


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

    mysql> create table department (deptno int (3), dname varchar (20) unique,
    location varchar (20) not null check (location in ('Delhi', 'Agra',' Pune')),
    primary key(deptno));
    mysql> create table employee (empno varchar (5) check (empno like 'e%')
    primary key, ename varchar (20) unique, designation varchar (20) not null,
    salary int (10) check (salary between 15000 and 50000) default 25000, dob
    date not null, dno int (3), foreign key(dno) references department (deptno));

    mysql> CREATE TABLE candidate (Candidate_ID INT (6), Candidate_Name VARCHAR


    (20) NOT NULL, Candidate_Email VARCHAR (30) UNIQUE CHECK (Candidate_Email
    LIKE '%@%.%'), Candidate_Dept VARCHAR(2) DEFAULT 'HR', Manager_ID INT(6),
    PRIMARY KEY (Candidate_ID), FOREIGN KEY (Manager_ID) REFERENCES
    candidate(Candidate_ID) );
    2. Create the schemas as specified above without specifying any
    constraints.
    1. Add cName as Primary key in college. mysql>
    alter table college add primary key(cName);

    2. Add sID as Primary key in Student. mysql>


    alter table student add primary key(SID);

    3. Add sID, cName, major as Primary key in


    Apply. mysql> alter table apply add primary
    key(SID, cName, major);

    4.Make sID in Apply foreign key referring table student and cName
    referring table college.
    mysql> alter table apply add foreign key(SID) references student(SID), add
    foreign key (cName) references college(cName);
    5.Increase data type size of major from 20 to 25.
    mysql> alter table apply modify major varchar(25);

    6.Add a new column decision in the Apply table keeping a constraint of


    not null for this column with data type varchar2(3). mysql> alter table
    apply add decision varchar(3) not null;

    7. Change data type of decision in Apply to char (1).


    mysql> alter table apply modify decision char(1);

    8. Drop foreign key on column name cName from Apply table. mysql>
    alter table apply drop foreign key apply_ibfk_2;
    9. Remove column sizeHS from Student table.
    mysql> alter table student drop column sizeHS;

    10. Drop primary key from college. mysql> alter table college drop
    primary key;

    11. Make cname, major unique pairwise such as Stanford CS, Stanford EE.
    mysql> alter table apply add constraint unique_pair_apply unique
    (cName, major);

    12. Add cName as Foreign Key in Apply table referring table College using
    on delete cascade.
    mysql> alter table college add primary key(cName); mysql>
    alter table apply add Foreign key(cName) references
    college(cName) on delete cascade;

    13. Modify foreign key on sID in Apply table to foreign key on delete set
    null.
    mysql> alter table apply drop foreign key apply_ibfk_1;
    mysql> alter table apply add foreign key(sID) references student(sid) on
    delete set null;

    14. Rename column enrollment to enroll in College Table. mysql> alter


    table college rename column enrollment to enroll;

    You might also like