NEP-SYBCS -DBMS-I Assignment-2

 Assignment no.2 Data Definition Query (Create simple tables with referential integrity constraint).

Set A

Create tables for the information given below by giving appropriate integrity constraints as specified.

1. Create the following tables:

Table Name :-Property

Columns Column Name         Column Data Type         Constraints

1                 P-number             integer                             Primary key

2                 Description          varchar (50)                     Not null

3                 Area                     char(10)

Table Name:- Owner

Columns Column Name         Column Data Type         Constraints

1             Owner-name             varchar(50)                     Primary key

2             Address                     varchar (50)

3             Phone no                    integer

Relationship - A one-many relationships between owner and property. Define reference keys accordingly.

Queries:-

CREATE TABLE Owner (

    "Owner-name" VARCHAR(50) PRIMARY KEY,

    Address VARCHAR(50),

    "Phone no" INTEGER

);

CREATE TABLE Property (

    "P-number" INTEGER PRIMARY KEY,

    Description VARCHAR(50) NOT NULL,

    Area CHAR(10),

    "Owner-name" VARCHAR(50),

    FOREIGN KEY ("Owner-name") REFERENCES Owner("Owner-name")

);

2. Create the following tables:

Table Name:- Hospital

Columns Column Name         Column Data Type     Constraints

1             Hno                             integer                     Primary key

2             Name                         varchar (50)              Not null

3             City                             char(10)

Table Name:- Doctor

Columns         Column Name         Column Data Type         Constraints

1                     Dno                             integer                         Primary key

2                     Dname                         varchar (50)

3                     City                             char(10)

Relationship - A many-many relationships between hospital and doctor.

Query:-

CREATE TABLE Hospital (

    Hno INTEGER PRIMARY KEY,

    Name VARCHAR(50) NOT NULL,

    City CHAR(10)

);

CREATE TABLE Doctor (

    Dno INTEGER PRIMARY KEY,

    Dname VARCHAR(50),

    City CHAR(10)

);

CREATE TABLE Hospital_Doctor (

    Hno INTEGER,

    Dno INTEGER,

    PRIMARY KEY (Hno, Dno),

    FOREIGN KEY (Hno) REFERENCES Hospital(Hno),

    FOREIGN KEY (Dno) REFERENCES Doctor(Dno)

);

3. Create the following tables:

Table Name:- Patient

Columns    Column Name         Column Data Type         Constraints

1                 Pno                             integer                         Primary key

2                 Name                         varchar (50)                  Not null

3                 Address                      varchar(50)

Table Name:- Bed

Columns     Column Name         Column Data Type     Constraints

1                 Bedno                         integer                             Primary key

2                 Roomno                     integer                             Primary key

3                 Description                 varchar(50)

Relationship - a one–to-one relationship between Patient & Bed.

Query:-

CREATE TABLE Bed (

    Bedno INTEGER,

    Roomno INTEGER,

    Description VARCHAR(50),

    PRIMARY KEY (Bedno, Roomno)

);

CREATE TABLE Patient (

    Pno INTEGER PRIMARY KEY,

    Name VARCHAR(50) NOT NULL,

    Address VARCHAR(50),

    Bedno INTEGER,

    Roomno INTEGER UNIQUE,

    FOREIGN KEY (Bedno, Roomno) REFERENCES Bed(Bedno, Roomno)

);

Set B

1. Create the following tables:

Table Name:- Student

Columns     Column Name     Column Data Type         Constraints

1                 Sno                         integer                                 Primary key

2                 Name                     varchar (50)                         Not null

3                 Address                  varchar(50)

4                 Class                     varchar(10)

Table Name Teacher

Columns Column Name Column Data Type Constraints

1                 Tno                     integer                     Primary key

2                 Tname                integer                     Primary key

3                 Qualification     varchar(50)

4                 TotalExperience     float

5                 Salary                 float                         Should be greater than 0

Relationship - A Many–to-Many relationships between Student and Teacher with descriptive

attribute Marks.

Query:-

CREATE TABLE Student (

    Sno INTEGER PRIMARY KEY,

    Name VARCHAR(50) NOT NULL,

    Address VARCHAR(50),

    Class VARCHAR(10)

);

CREATE TABLE Teacher (

    Tno INTEGER PRIMARY KEY,

    Tname VARCHAR(50),

    Qualification VARCHAR(50),

    TotalExperience FLOAT,

    Salary FLOAT CHECK (Salary > 0)

);

CREATE TABLE Student_Teacher (

    Sno INTEGER,

    Tno INTEGER,

    Marks INTEGER,

    PRIMARY KEY (Sno, Tno),

    FOREIGN KEY (Sno) REFERENCES Student(Sno),

    FOREIGN KEY (Tno) REFERENCES Teacher(Tno)

);

2. Create the following tables:

Table Name Project

Columns       Column Name         Column Data Type         Constraints

1                     Pno                             integer                             Primary key

2                     Pname                         varchar (30)                     Not null

3                     Ptype                         char(20)

4                     Duration                     integer

Table Name Employee

Columns     Column Name         Column Data Type         Constraints

1                     Eno                             integer                         Primary key

2                     Ename                         varchar(20)

3                     Qualification               varchar(50)

4                     Join_Date                     date

5                     Salary                         float                     Should be greater than 0

Relationship -A Many–to-Many relationships between Project and Employee with descriptive

attribute Start date(date), no_of_hours_worked(integer).

Query:-

CREATE TABLE Project (

    Pno INTEGER PRIMARY KEY,

    Pname VARCHAR(30) NOT NULL,

    Ptype CHAR(20),

    Duration INTEGER

);

CREATE TABLE Employee (

    Eno INTEGER PRIMARY KEY,

    Ename VARCHAR(20),

    Qualification VARCHAR(50),

    Join_Date DATE,

    Salary FLOAT CHECK (Salary > 0)

);

CREATE TABLE Project_Employee (

    Pno INTEGER,

    Eno INTEGER,

    Start_Date DATE,

    No_of_Hours_Worked INTEGER,

    PRIMARY KEY (Pno, Eno),

    FOREIGN KEY (Pno) REFERENCES Project(Pno),

    FOREIGN KEY (Eno) REFERENCES Employee(Eno)

);