WEEK - 2
EXPERIMENT – 2
RELATIONAL MODEL
AIM: To Represent all the entities (Strong, Weak) in tabular fashion. Represent relationships in a tabular fashion.
1. Relation model for college management system.
Student: student(S_ID : INTEGER,S_NAME : STRING, ADDRESS:STRING)
Column Name DataType Constraints Type of Attributes
S_ID INTEGER Single value
S_NAME VARCHAR(20) Multi value
D.O.B DATE
AGE INT Single value
ADDRESS VARCHAR (255) Multi value
SCHEMA:
Mysql>create table student(Student_Id Varchar(10),First_Name char(10),Last_Name char(10) ,DOB
date,Age int,phone_number varchar(30),city char(10),state char(10),pincode int);
Mysql>desc student;
FACULTY:
Column Name DataType Constraints Type of
Attributes
FACULTY_ID VARCHAR(10) Single value
FACULTY_NAME CHAR(10) Multi value
DEPARTMENT CHAR(10) Single value
PHONE NO VARCHAR(30) Multi value
SALARY INT Single value
SCHEMA:
mysql> create table faculty(Faculty_id varchar(10),First_Name char(10) ,Last_Name char(10) ,Department
varchar(10),phone_no varchar(30),salary int);
Mysql>desc faculty;
COURSE:
Column Name DataType Constraints Type of
Attributes
COURSE_ID VARCHAR(10) Single value
COURSE_NAME CHAR(20) Single value
DEPARTMENT CHAR(20) Single value
SCHEMA:
mysql> create table course(course_id varchar(10),course_name varchar(10) not
null,Department varchar(20));
mysql>desc course;
DEPARTMENT:
Column Name DataType Constraints Type of
Attributes
DEPARTMENT_ID VARCHAR(10) Single value
DEPARTMENT_NAME CHAR(20) Single value
SCHEMA:
mysql> create table Department(Department_Id varchar(10),Department_Name char(20));
mysql>desc Department;
SUBJECT:
Column Name DataType Constraints Type of Attributes
SUBJECT_ID VARCHAR(10) Single value
SUBJECT_NAME CHAR(20) Single value
SCHEMA:
mysql> create table Subject(Subject_Id varchar(10),Subject_Name char(20));
EXAMS:
Column Name DataType Constraints Type of
Attributes
EXAM_CODE VARCHAR(10) Single value
ROOM_NO INTEGER Single value
TIME TIME Single value
DATE DATE
SCHEMA:
mysql> create table Exams(Exam_code varchar(10),Room_no int ,Time time, Date date);
mysql> desc Exam;
HOSTEL:
Column Name DataType Constraints Type of
Attributes
HOSTEL_ID VARCHAR(10) Single value
HOSTEL_NAME CHAR(20) Single value
NO_OF_SEATS INT Single value
SCHEMA:
mysql> create table Hostel(Hostel_Id varchar(10),Hostel_Name char(20) ,No_of_seats int);
mysql> desc Hostel;
2.Relational model for BUS Reservation system.
1. Bus: Bus(BusNo: String, Source: String, Destination: String, CoachType: String)
ColumnName Datatype Constraints Type of Attributes
BusNo Varchar(10) Single-value
Source Varchar(20) Single-value
Destination Varchar(20) Simple
CoachType Varchar(10) Simple
Mysql>create table Bus(BusNo varchar(10),source varchar(20),Destination
varchar(20),coachType varchar(10),primary key(BusNo));
Mysql>desc Bus;
Ticket:
Ticket(TicketNo: string, DOJ: date, Address:string,ContactNo: string, BusNo:String, SeatNo :Integer, Source:
String, Destination: String)
ColumnName Datatype Constraints Type of Attributes
TicketNo Varchar(20) Single-valued
DOJ Date Single-valued
Address Varchar(20) Composite
ContactNo Integer Multi-valued
BusNo Varchar(10) Single-valued
SeatNo Integer Simple
Source Varchar(10) Simple
Destination Varchar(10) Simple
Mysql> create table ticket(ticketno varchar(20), doj date,address varchar(20),contactno int, busno
varchar(20),seatno int,source varchar(10),destination varchar(10));
Mysql>desc Ticket;
Passenger:
Passenger(PassportID: String, TicketNo:string,Name: String, ContactNo:string,Age: integer, Sex: character,
Address: String);
Type of
ColumnName Datatype Constraints
Attributes
PassportID Varchar(15) Single-valued
TicketNo Varchar(20) Single-valued
Name Varchar(20) Composite
ContactNo Varchar(20) Multi-valued
Age Integer Single-valued
Sex character Simple
Address Varchar(20) Composite
Mysql> Create table passenger(passportID varchar(15) ,TicketNo varchar(15),Name varchar(15),ContactNo
varchar(20),Age integer, sex char(2),address varchar(20));
Mysql> desc passenger;
Reservation:
Reservation(PNRNo: String, DOJ: Date, NoofSeats: integer , Address: String ,ContactNo:
String, , BusNo: String,SeatNo:Integer)
ColumnName Datatype Constraints Type of Attributes
PNRNo Varchar(20) Single-valued
DOJ Date Single-valued
No_of_Seats Integer Simple
Address Varchar(20) Composite
ContactNo Varchar(10) Multi-valued
BusNo Varchar(10) Single-valued
SeatNo Integer Simple
Mysql> Create table Reservation(PNRNo varchar(20),DOJ date,NoofSeats integer,Address varchar(20),ContactNo
varchar(20),BusNo varchar(20),SeatNo integer);
Mysql> desc reservation;
Cancellation:
Cancellation (PNRNo: String,DOJ: Date, SeatNo: integer,ContactNo: String,Status: String)
ColumnName Datatype Constraints Type of Attributes
PNRNo Varchar(10) Single-valued
DOJ Date Single-valued
SeatNo Integer Simple
ContactNo Varchar(15) Multi-valued
Status Varchar(10) Simple
Mysql> create table cancellation(PNRNo varchar(10),DOJ date,SeatNo integer, ContactNo
varchar(15),Status varchar(10));
Mysql> desc cancellation;
3.Relational model for Banking system.
Bank:
Bank (Name: String, Code string, Address string)
ColumnName Datatype Constraints Type of Attributes
Name char(10) Single-valued
code varchar(15) Single-valued
Address Varchar(100) Multi-valued
mysql> create table Bank(Name char(10),Code varchar(15),Address varchar(100));
mysql> desc Bank;
Branch:
Branch (Branch_Id: String, Name string, Address string)
ColumnName Datatype Constraints Type of Attributes
Branch_Id varchar(10) Single-valued
Name char(15) Single-valued
Address Varchar(100) Multi-valued
mysql> create table Branch(Branch_Id varchar(10),Name varchar(15),Address varchar(100));
mysql> desc Branch;
Loan (Loan_Id: String, Loan_Type string, Address string)
ColumnName Datatype Constraints Type of Attributes
Loan_Id varchar(10) Single-valued
Loan_Type varchar(15) Single-valued
Address Varchar(100) Multi-valued
mysql> create table Loan(Loan_Id varchar(10),Loan_Type varchar(15),Address varchar(100));
mysql> desc Loan;
Account (Account_No integer, Account_Type string, Balance float)
ColumnName Datatype Constraints Type of Attributes
Account_No Integer Single-valued
Account_Type varchar(15) Single-valued
Balance Float Single
mysql> create table Account(Account_No int,Account_Type varchar(15),Balance float);
mysql> desc Account;
Customer:
Customer( Customer_Id varchar(10),Name: String, Phone_No string, Address string)
ColumnName Datatype Constraints Type of Attributes
Customer_Id Varchar(10) Single-values
Name char(20) Single-valued
Phone_No varchar(30) Multi-valued
Address Varchar(100) Multi-valued
mysql> create table Customer(Customer_Id varchar(10),Name char(20),Phone_No varchar(30),Address
varchar(100));
mysql> desc Customer;
