(NMVPM’s)

NUTAN MAHARASHTRA INSTITUTE OF ENGINEERING AND

TECHNOLOGY (NMIET)
Under Administrative Support - (PCET)
DEPARTMENT OF COMPUTER SCIENCE AND ENGINEERING(ARTIFICIAL INTELLIGENCE)

Practical 1

Practical 1 Title - Introduction to Database Creation

Lab Objective Setup and create database using SQL/PostgreSQL

Practical Execution/ Timely Submission Attendance Overall Total Signature

Performance /Viva (5) Journal/Record (5) Understanding/ (20) of Faculty
Maintenance (5) Behavior(5)

1 Mrs Sarita Charkha, NMIET,Talegaon

 (NMVPM’s)
NUTAN MAHARASHTRA INSTITUTE OF ENGINEERING AND
TECHNOLOGY (NMIET)
Under Administrative Support - (PCET)
DEPARTMENT OF COMPUTER SCIENCE AND ENGINEERING(ARTIFICIAL INTELLIGENCE)

Aim: To introduce the fundamentals of database creation and design.

Objective: To set up and create a database using SQL, and implement entities and relationships using appropriate
commands.

Problem Statement:- Design and implement a relational database by defining the following entities and
their attributes, and establish the required relationships among them:

Entities

 Create a Student table with attributes:

StudentID, Name, Email, Age, Address
 Create a Course table with attributes:
CourseID, CourseName, Credits, InstructorID
 Create an Instructor table with attributes:
InstructorID, Name, Email, Department
 Create an Enrollment table with attributes:
EnrollmentID, StudentID, CourseID, EnrollmentDate

Relationships

 A student can enroll in multiple courses.

 A course can have multiple students.
 An instructor teaches one or more courses.

Outcomes: Upon completion of this experiment, the student should be able to:

1. Create database entities and define their attributes using various DDL commands.
2. Establish and represent entity–relationship mappings using appropriate SQL constraints and
commands.

Theory:
A database is an organized collection of related data. In relational database systems like MySQL/PostgreSQL, data
is stored in tables (relations) consisting of rows and columns.
2 Mrs Sarita Charkha, NMIET,Talegaon
 (NMVPM’s)
NUTAN MAHARASHTRA INSTITUTE OF ENGINEERING AND
TECHNOLOGY (NMIET)
Under Administrative Support - (PCET)
DEPARTMENT OF COMPUTER SCIENCE AND ENGINEERING(ARTIFICIAL INTELLIGENCE)

Each table represents an entity, and relationships between entities are implemented using primary keys and
foreign keys.
SQL (Structured Query Language) is used to create databases, tables, insert data, and retrieve information.
Introduction to Entity and Relationship:
Entity:
An entity is a real-world object or concept that has an independent existence and can be uniquely identified. It
represents a set of similar objects about which data is stored in a database.
Example: Student, Course, Instructor.
Relationship:
A relationship is an association or logical connection between two or more entities that defines how the entities
are related to each other in the real world.
Example: A student enrolls in a course.
Entity Relationship Model (ER Model)

The entity-relationship data model perceives the real world as consisting of basic objects, called
Entities and relationships among these objects. It was developed to facilitate database design by
allowing specification of an enterprise schema which represents the overall logical structure of a
data base.

Main Features of ER-MODEL:

 Entity relationship model is a high level conceptual model
 It allows us to describe the data involved in a real world enterprise in terms of objects and their
relationships.
 It is widely used to develop an initial design of a database.
 It provides a set of useful concepts that make it convenient for a developer to move from a basic
 set of information to a detailed and description of information that can be easily implemented in
a database system
 It describes data as a collection of entities, relationships and attributes.

Basic Concepts:
The E-R data model employs three basic notions :
Entity sets
Relationship sets
Attributes.

3 Mrs Sarita Charkha, NMIET,Talegaon

 (NMVPM’s)
NUTAN MAHARASHTRA INSTITUTE OF ENGINEERING AND
TECHNOLOGY (NMIET)
Under Administrative Support - (PCET)
DEPARTMENT OF COMPUTER SCIENCE AND ENGINEERING(ARTIFICIAL INTELLIGENCE)

Entity Sets:
An entity is a “thing” or “object” in the real world that is distinguishable from all other objects.
Forexample, each person in an enterprise is an entity. An entity has a set properties and the values for
some set of properties may uniquely identify an entity. BOOK is entity and its properties (called as
attributes) bookcode, booktitle, price etc.

An entity set is a set of entities of the same type that share the same properties, or attributes. The set of all
persons who are customers at a given bank.

Attributes:
An entity is represented by a set of attributes. Attributes are descriptive properties possessed by
each member of an entity set.
Customer is an entity and its attributes are customerid, custmername, custaddress etc.

An attribute as used in the E-R model, can be characterized by the following attribute types.

a) Simple and Composite Attribute:

Simple attributes are the attributes which can’t be divided into sub parts, e.g. customerid, empno
Composite attributes are the attributes which can be divided into subparts, e.g. name consisting of
first name, middle name, last name and address consisting of city, pincode, state.

b) Single-Valued and Multi-Valued Attribute:

The attribute having unique value is single –valued attribute, e.g. empno, customerid, regdno etc.
The attribute having more than one value is multi-valued attribute, eg: phone-no, dependent name,
vehicle.

c) Derived Attribute:
The values for this type of attribute can be derived from the values of existing attributes, e.g. age
which can be derived from currentdate – birthdate and experience_in_year can be calculated as
currentdate-joindate.

d) NULL Valued Attribute:

The attribute value which is not known to user is called NULL valued attribute.

Relationship Sets:
A relationship is an association among several entities. A relationship set is a set of relationships of the
same type. Formally, it is a mathematical relation on n>=2 entity sets. If E1, E2…En are entity sets, then
a relationship set R is a subset of {(e1,e2,…en) | e1Є E1, e2 Є E2.., en Є En}
4 Mrs Sarita Charkha, NMIET,Talegaon
 (NMVPM’s)
NUTAN MAHARASHTRA INSTITUTE OF ENGINEERING AND
TECHNOLOGY (NMIET)
Under Administrative Support - (PCET)
DEPARTMENT OF COMPUTER SCIENCE AND ENGINEERING(ARTIFICIAL INTELLIGENCE)

where (e1,e2,…en) is a relationship.

Consider the two entity sets customer and loan. We define the relationship set borrow to denote the
association between customers and the bank loans that the customers have.

Types of Keys:

Super Key:
A super key is a set of one or more attributes that taken collectively, allow us to identify uniquely an
entity in the entity set. For example , customer-id, (cname, customer-id), (cname, telno)

Candidate Key:
In a relation R, a candidate key for R is a subset of the set of attributes of R, which have the
following properties:
1. Uniqueness: No two distinct tuples in R have the same values for the candidate key
2. Irreducible: No proper subset of the candidate key has the uniqueness property that is
the candidate key. Eg: (cname, telno)

Primary Key:
The primary key is the candidate key that is chosen by the database designer as the principal means of
identifying entities within an entity set. The remaining candidate keys if any, are called Alternate Key.

The overall logical structure of a database using ER-model graphically with the help of an ERdiagram.

Introduction to DDL commands

DDL (Data Definition Language)-The SQL DDL provides command for defining relation schemas,
deleting relations and modifying relation schema.
Data Definition Language
The SQL DDL allows specification of not only a set of relations but also information about each relation,
including-Schema for each [Link] domain of values associated with each attribute.
The integrity constraints. The set of indices to be maintained for each relation. the security and
authorization information for each relation. The physical storage structure of each relation on disk

Domain types (DataTypes) in SQL-

The SQL standard supports a variety of built in domain types, including-
•Char (n)- A fixed length character length string with user specified length .
•Varchar (n)- A variable character length string with user specified maximum length n.
•Int- An integer.
5 Mrs Sarita Charkha, NMIET,Talegaon
 (NMVPM’s)
NUTAN MAHARASHTRA INSTITUTE OF ENGINEERING AND
TECHNOLOGY (NMIET)
Under Administrative Support - (PCET)
DEPARTMENT OF COMPUTER SCIENCE AND ENGINEERING(ARTIFICIAL INTELLIGENCE)

•Small integer- A small integer.

•Numeric (p, d)-A Fixed point number with user defined precision.
•Real, double precision- Floating point and double precision floating point numbers with machine
dependent precision.
•Float (n)- A floating point number, with precision of at least n digits.
•Date- A calendar date containing a (four digit) year, month and day of the month.
•Time- The time of day, in hours, minutes and seconds Eg. Time ’09:30:00’.
•Number- Number is used to store numbers (fixed or floating point)

Data Definition in SQL

Creating Tables
Syntax:-
create table<table name>
(colume_name 1 datatype size(),
colume_name 2 datatype size(),
.
colume_name n datatype size());

e.g. create table student with the following fields(name, roll, class, branch)

create table student

(name char(20), Roll number(5), Class char(10), Branch char(15));

Restrictions for creating a table:

[Link] names and column names must begin with a letter.

[Link] names and column names can be 1 to 30 characters long.

[Link] names must contain only the characters A-Z,a-z,0-9,underscore_,$ and #

[Link] name should not be same as the name of another database object.

[Link] name must not be an ORACLE reserved word.

[Link] names should not be duplicate within a table definition.

6 Mrs Sarita Charkha, NMIET,Talegaon

 (NMVPM’s)
NUTAN MAHARASHTRA INSTITUTE OF ENGINEERING AND
TECHNOLOGY (NMIET)
Under Administrative Support - (PCET)
DEPARTMENT OF COMPUTER SCIENCE AND ENGINEERING(ARTIFICIAL INTELLIGENCE)

Describe commands
To view the structure of the table created use the DESCRIBE [Link] command displays the
column names and datatypes

Syntax:-
Desc[ribe]<table_name>

e.g desc student

Alteration of TABLE:-

Alter table command

Syntax:-
Case1:-

Alter table <table_name>

Add( colume_name 1 datatype size(),
colume_name 2 datatype size(),
- ---- .
colume_name n datatype size());

Case2:-

Alter table <table_name>

Modify(colume_name 1 datatype size(),
colume_name 2 datatype size(),
-----
colume_name n datatype size());

After you create a table,you may need to change the table structures because you need to have a
column definition needs to be changed. Alter table statement can be used for this purpose. You can
add columns to a table using the alter table statement with the ADD clause.
E.g. Suppose you want to add enroll_no in the student table then we write

Alter table student

Add(enroll_no number(10));
You can modify existing column in a table by using the alter table statement with modify clause.

7 Mrs Sarita Charkha, NMIET,Talegaon

 (NMVPM’s)
NUTAN MAHARASHTRA INSTITUTE OF ENGINEERING AND
TECHNOLOGY (NMIET)
Under Administrative Support - (PCET)
DEPARTMENT OF COMPUTER SCIENCE AND ENGINEERING(ARTIFICIAL INTELLIGENCE)

E.g. Suppose you want to modify or chang the size of previously defined field name in the student
table then we write
Alter table student
modify(name char(25));

Dropping a column from a table

Syntax :

ALTER TABLE <Tablename> DROP COLUMN <ColumnName> ;

Drop table command

Syntax:-
Drop table <table_name>

Drop table command removes the definitions of an oracle [Link] you drop a table ,the database
loses all the data in the table and all the indexes associated with it.

e.g drop table student;

Truncate table command

Syntax:-
Truncate table<table_name>

The truncate table statement is used to remove all rows from a table and to release the storage space
used by the table.

[Link] table student;

Rename table command

Syntax:-
Rename<oldtable_name> to<newtable_name>

Rename statement is used to rename a table,view,sequence,or synonym.

8 Mrs Sarita Charkha, NMIET,Talegaon

 (NMVPM’s)
NUTAN MAHARASHTRA INSTITUTE OF ENGINEERING AND
TECHNOLOGY (NMIET)
Under Administrative Support - (PCET)
DEPARTMENT OF COMPUTER SCIENCE AND ENGINEERING(ARTIFICIAL INTELLIGENCE)

e.g. Rename student to stud;

Create a Student table

CREATE TABLE Student (
StudentID INT PRIMARY KEY,
Name VARCHAR(50),
Email VARCHAR(50),
Age INT,
Address VARCHAR(100)
);

Create Instructor Table

CREATE TABLE Instructor (
InstructorID INT PRIMARY KEY,
Name VARCHAR(50),
Email VARCHAR(50),
Department VARCHAR(50)
);

Create Course Table

CREATE TABLE Course (

CourseID INT PRIMARY KEY,
CourseName VARCHAR(50),
Credits INT,
InstructorID INT,
FOREIGN KEY (InstructorID) REFERENCES Instructor(InstructorID)
);

Create Enrollment Table

CREATE TABLE Enrollment (

EnrollmentID INT PRIMARY KEY,
StudentID INT,
CourseID INT,
9 Mrs Sarita Charkha, NMIET,Talegaon
 (NMVPM’s)
NUTAN MAHARASHTRA INSTITUTE OF ENGINEERING AND
TECHNOLOGY (NMIET)
Under Administrative Support - (PCET)
DEPARTMENT OF COMPUTER SCIENCE AND ENGINEERING(ARTIFICIAL INTELLIGENCE)

EnrollmentDate DATE,
FOREIGN KEY (StudentID) REFERENCES Student(StudentID),
FOREIGN KEY (CourseID) REFERENCES Course(CourseID)
);

CONCLUSION:-

ASSIGNMENT QUESSTION:

OUTPUT:

10 Mrs Sarita Charkha, NMIET,Talegaon