!

DBMS FILE

Submitted By:

Pulkit Chhabra
2K17/CO/248
COE - A4 (G1)

Submitted To:
Mr. Manoj Sethi
Index
S. No. Aim Page No. Done on Signature
EXPERIMENT-1
AIM:
To obtain in-depth knowledge of various database management system softwares, their advantages and
disadvantages and the operating system they work on.

PROBLEM STATEMENT:
Compare five database management systems and explain one of them in detail

DBMS DEFINITION:
A database-management system (DBMS) is a computer-software application that interacts with end-users,
other applications, and the database itself to capture and analyse data. A general-purpose DBMS allows the
definition, creation, querying, update, and administration of databases.

Following are some of the DBMS Softwares:

1. MySQL
MySQL is regarded as the most popular open source database that has ease of use and lowest total cost of
ownership compared to other database management systems. Available in four tiers of increasing cost (basic,
silver, gold and platinum), it provides high performance and scalable online transaction processing (OLTP)
capabilities and multi-terabyte data warehousing applications. The real engine is called MySQL Enterprise
Server. Some of the special features of MySQL Enterprise 5.1 server include partitioning that helps manage
very large databases and Event Scheduler to help create and schedule jobs.

FEATURES
• MySQL follows a client /server architecture. There is a database server (MySQL) and
arbitrarily many clients (application programs), which communicate with the server; that is,
they query data, save changes, etc. 


• MySQL is compatible to run on many operating systems like Windows, Linux, many
varieties of UNIX (such as Sun, Solaris, AIX, and DEC UNIX), OS/2, FreeBSD, and others.
MySQL also provides a facility that the clients can run on the same computer as the server
or on another computer (communication via a local network or the Internet). 


• MySQL uses Triggers, Stored procedures and views which allows the developer to give a
higher productivity. 


Their Advantages:

MySQL is easy to learn and use. MySQL is very easy to install, and thanks to a bevy of third-party
tools that can be added to the database, setting up an implementation is a relatively simple task.
MySQL is free to use and you can download it from MySQL official website.

MySQL can handle almost any amount of data, up to as much as 50 million rows or more. The
default file size limit is about 4 GB. However, you can increase this number to a theoretical limit of
8 TB of data.

MySQL is faster, more reliable and cheaper because of its unique storage engine architecture.

Support is readily available whenever necessary.

Their Disadvantages:

MySQL does not support a very large database size as efficiently.


MySQL doesn't support SQL check constraints.

MySQL is accused that it doesn't have a good developing and debugging tool compared to paid
databases.

2. SQLite

It is a relational database management system contained in a C programming library. In contrast to

many other database management systems, SQLite is not a c database engine. Rather, it is
embedded into the end program.Unlike server database management systems, the SQLite is an
integral part of the program.

Features

• SQLite is popular choice for the database engine in cellphones, PDAs, MP3 players, set- top
boxes, and other electronic gadgets. SQLite has a small code footprint, makes efficient use
of memory, disk space, and disk bandwidth, is highly reliable, and requires no maintenance
from a Database Administrator. 


• Because it requires no configuration and stores information in ordinary disk files, SQLite is
a popular choice as the database to back small to medium-sized websites. 


• SQLite is often used as a surrogate for an enterprise RDBMS for demonstration purposes or
for testing. SQLite is fast and requires no setup, which takes a lot of the hassle out of testing
and which makes demos perky and easy to launch. 

Their Advantages: 

• SQLite is a very light weighted database so, it is easy to use it as an embedded software with
devices like televisions, Mobile phones, cameras, home electronic devices, etc. 


• Reading and writing operations are very fast for SQLite database. It is almost 35% faster
than File system. 


• SQLite is very easy to learn. You don’t need to install and configure it. Just download
SQLite libraries in your computer and it is ready for creating the database. 

Their Disadvantages: 


• SQLite is used to handle low to medium traffic HTTP requests. 


• Database size is restricted to 2GB in most cases. 


3. ORACLE 

Oracle Database (commonly referred to as Oracle RDBMS or simply as Oracle) is

a multimodel base management system produced and marketed by Oracle Corporation. 

It is a database commonly used for running online transaction processing (OLTP), data
warehousing (DW) and mixed (OLTP & DW) database workloads. The latest generation,
Oracle Database 18c, is available on-prem, on-Cloud, or in a hybrid-Cloud environment. 18c
may also be deployed on Oracle Engineered Systems (e.g. Exdata) on-prem, 

on Oracle (public) cloud or (private) Cloud at Customer. At Openworld 2017 in San
Francisco, Executive chairman of the board and Larry Elison ,CTO announced the next
database generation, Oracle Autonomous Database. 

The Oracle Corporation 

Oracle Corporation is the largest software company in the field of database business. Its
relational database was the first to support SQL which has since become the industry
standard.

Oracle database is one of the most trusted and widely used relational database engines. The
biggest rival of Oracle database is Microsoft's SQL Server. 

Features 


• Oracle DB runs on most major platforms, including Windows, UNIX, Linux and Mac OS. 

• Its architecture is split between the logical and the physical. This structure means that for 

large-scale distributed computing, also known as grid computing, the data location is
irrelevant and transparent to the user, allowing for a more modular physical structure that
can be added to and altered without affecting the activity of the database, its data or users. 


• The sharing of resources in this way allows for very flexible data networks whose capacity
can be adjusted up or down to suit demand, without degradation of service. 


• It also allows for a robust system to be devised as there is no single point at which a failure
can bring down the database, as the networked schema of the storage resources means that
any failure would be local only. 


Their Advantages:

• Oracle fully supports all industry standards and provides full support to developers. 


• Oracle supports online backup and recovery and has a wealth of development tools, 

covering various stages of the development cycle. 


• Oracle supports large databases, data type support numbers and characters, and it 

provides data to support object-oriented database storage. 


• An Oracle database has the ability to manage multiple databases using a two-phase 

commit protocol. 


• Oracle supports cursors, which helps to make programming easier. It also performs each 

transaction separately, and the result of each transaction is invisible to other transactions
until it is complete, which increases the security of data. 

Their Disadvantages: 

• One major disadvantage of Oracle database is its complexity. 


• Using Oracle is not ideal if the users lack the technical ability and know-how needed to 

work with Oracle databases. 


• It is also not ideal to use Oracle if an organization or individual is looking for an easy-to- 

use database with basic features. 


• It is not ideal for small or mid-sized companies where small databases are needed. In 

such cases, using MySQL is more cost effective. 

4. Microsoft Access 

Microsoft Access is a Database Management System (DBMS) from Microsoft that combines
the relational Microsoft Jet Database Engine with a graphical user interface and software
development tools. It is a member of the Microsoft Office suite of applications, included in
the professional and higher editions. 

Features 


• Microsoft Access is just one part of Microsoft’s overall data management product strategy. 


• Like relational databases, Microsoft Access also allows you to link related information
easily. For example, customer and order data. However, Access 2013 also complements
other database products because it has several powerful connectivity features. 


• Access calls anything that can have a name an object. Within an Access desktop database,
the main objects are tables, queries, forms, reports, macros, data macros, and modules. 


• With the release of Access 2007, the database file format changed from the previous ".mdb"
to ".accdb". This new format supports more complex data types, but unfortunately is not
compatible with prior versions of the Access software. 

Their Advantages: 

• Access is a go-to choice for users who plan to develop software using .NET; linking to
Access database. Its graphical user interface also offers easy functionality and set up. 


• Microsoft Access is hundreds of dollars more economical than other larger systems;
offering the same functions and usage. 


• Access works well with many of the developing software programs based in Windows. It
also can be used in the front-end as back-end tables with products like Microsoft SQL
Server and non-Microsoft products like Oracle and Sybase. 

Their Disadvantages: 


• SQL for MS Access is not as robust as MS SQL Server or Oracle, to just name a few. 


• Microsoft Access is useful for individual departments or small-to-medium business sectors.

Any sector whose usage goes beyond 2 GB will hit a wall and discover limitations. 


• Technical limit is 255 concurrent users, but real world limit is 10 to 80 (depending on type
of application). 

5) IBM DB2 

DB2 is a database product from IBM. It is a Relational Database Management System
(RDBMS). DB2 is designed to store, analyze and retrieve the data efficiently. DB2 product
is extended with the support of Object-Oriented features and non- relational structures with
XML. 

Features 


• Initially, IBM had developed DB2 product for their specific platform. Since year 1990, it decided
to develop a Universal Database (UDB) DB2 Server,

which can run on any authoritative operating systems such as Linux, UNIX, and Windows.

It is designed for mid-size to large-size business organizations. Platform - Linux, UNIX, and
Windows. Table partitioning High Availability Disaster Recovery (HARD) Materialized Query
Table (MQTs) Multidimensional Clustering (MDC) Connection concentrator Pure XML Backup
compression Homogeneous Federations.

It is designed for Workgroup or mid-size business organizations. Using this WSE you can work
with - High Availability Disaster Recovery (HARD) Online Reorganization Pure XML Web Service
Federation support DB2 Homogeneous Federations Homogeneous SQL replication Backup
compression.

It provides all the capabilities of DB2 at zero charge. It can run on any physical or virtual systems
with any size of configuration.

Their Advantages:

Powerful structured query: DB2 has a more powerful Structured Query Language (SQL) dialect
than Microsoft's SQL offering. DB2 has features such as object tables, before triggers, Java method
support, multiple user-defined functions and support for arrays.

Multiple platform support: IBM produces versions of DB2 that run on all available platforms, rather
than just Windows-based platforms. Included in the DB2 list of supported platforms are AIX, HP-
UX, Linux and Sun.

Self tuning memory management: Database tuning is part art and part science, and normally takes a
significant amount of a database administrator's time. Part of this configuration job entails
configuring memory for the various workloads to achieve the best performance.

Their Disadvantages:

It's hard to find good DB2 DBAs quickly for a new project unlike Oracle or SQL- Server DBAs.
Up-skilling Oracle DBAs to DB2 is easy considering the amount of materials and tutorials available
in the internet for DB2.

There are lots of great as well as less efficient monitoring Tools in DB2, but nowhere it's mentioned
which one is recommended by IBM.

Conclusion:

We have learned about various database management systems in detail with their features,
advantages and disadvantages.
EXPERIMENT -2
Aim: To learn about modeling tools like MetaEdit which help in designing models. Problem
Statement: Explain MetaEdit software and its functions.

MetaEdit

MetaEdit is an environment for creating and using Domain specific Modeling languages.

It offers full modeling tool support for your language. Your whole team can immediately start to
edit designs as graphical diagrams, as matrices or as tables, switching between views according to
your needs.

There are two main versions of MetaEdit:

• MetaEdit Workbench, including tools for designing and using modeling languages. 


• MetaEdit Modeler, including tools for using modeling languages. 


Normally, MetaEdit Workbench is used by a few key developers to design a domain- 

specific modeling language for their project. Then, this modeling language is used to 

develop final products using MetaEdit Modeler.

You can browse designs with filters, apply components, link your models to other designs, 

and check your models with various pre-defined or user-defined reports. You can publish the
designs in your domain-specific modeling language to the web or word processors, or
generate full code for your products. 

These three elements together formed a metamodel file, which configured MetaEdit to
support that modeling language. 


• MetaEdit 1.0 was released as shareware in 1993. 


• MetaEdit 1.1 was released as commercial software in 1993. 


• MetaEdit 1.2 was released in 1995. 


The original MetaEdit was limited to supporting one modeling language at a time, one user
at a time, and one representational paradigm - graphical diagrams. MetaEdit was designed to
extend this to multiple integrated modeling languages, multiple simultaneous users, and
multiple representational paradigms - diagrams, matrices and tables. 

• MetaEdit 2.0, the first version of MetaEdit, was released by MetaCase in 1995 for Windows.

• MetaEdit 2.5 was released in 1996, adding full multi-user facilities and support for Solaris
and HP-UX. 


• MetaEdit 3.0 was released in 1999, with support for Linux and significant new functionality
added in three Service Releases over the next few years. 


• MetaEdit 4.0 was released in 2004, with new Diagram and Symbol Editors, support for
ports, and interoperability via SOAP and XML. Two Service Releases adding new
functionality and support for Mac OS X. 


• MetaEdit 4.5 was released in 2006, adding graphical metamodeling and improving the
metamodel, symbol and generator definition facilities and Diagram Editor. 

Functions

• The MetaEdit toolset thus includes generic CASE behavior for objects and
relationships, including a Diagram Editor, Object and Graph Browsers, and property
dialogs. The DSM developer need only specify his modeling language: e.g. creating
a new object type, giving it a name and choosing which property types it has. 


• . It allows you to define your object and relationship symbols, or reuse existing
symbols. There is no need for any hand coding, nor is any CASE tool code
generated. The MetaEdit editors simply follow the defined language in a similar way
to how Word follows its templates. 


• MetaEdit also includes XML import and export, an API for data and control access
to MetaEdit functions, and a generic code generator. The code generator uses a DSL
that allows the DSM developer to specify how to walk through models and output
their contents along with other text. This makes defining code generators simple,
with one line of a code generator definition corresponding to several lines in the
scripting languages sometimes used for this purpose. 

EXPERIMENT 3

OBJECTIVE: To implement the concepts of Entity-Relationship Diagrams (ERD)

PROBLEM STATEMENT: Design an ER Diagram for a Match-Player Management

System

DESCRIPTION:

Assume we have the following application that models soccer teams, the games
they play and the players in each team. In the design, we want to capture the following —

1. We have a set of teams, each team has an ID (Unique Identifier), Name, Main Stadium,
and to which City this team belongs.

2. Each team has many players and each player belongs to one team. Each player has a
Number (Unique Identifier), Name, Date of Birth, Start Year and Shirt Number that he
uses.

3. Teams play matches, inn each match there is a Host Team and a Guest Team. The
match takes place in the stadium of the host team.

4. For each match, we need to keep track of the following —

(i) The date on which the game is played

(ii) The final result of the match

(iii) The player participated in the match. For each player how many goals he
scored, whether or not he took yellow card & whether or not he took a red card

(iv) During the match one player may substitute another player. We want to
capture this substitution and the time at which it took place

5. Each match has exactly three Referees. For each referee, we have an ID (Unique
Identifier), Name, Date of Birth, Years of Experience,. One referee is the main referee
and the other two are assistant referees.
OUTPUT:

CONCLUSION: Through this experiment, we learnt how to use an ERD Software to make
an ER Diagram and how to interpret a question and turn it into an ER
Diagram.

EXPERIMENT 4

OBJECTIVE: To acquire knowledge of SQL, its merits & demerits and learn about its
commands.

PROBLEM STATEMENT: Explain SQL, its characteristics and commands.

EXPLANATION:

Structured Query Language (SQL):-

SQL is a standard language for storing, manipulating and retrieving data from
databases. It is a domain-specific language which is used in programming and is
designed for managing data held in a relational database management system, or for
stream processing in a relational data stream management system.

It is multi-paradigm, declarative, and is designed by Donald D. Chamberlin

Raymond F. Boyce at IBM and developed by the International Organization for
Standardization (ISO) first in 1974. It is a cross-platform software and has the filename
extension of .sql.

Characteristics of SQL:

• It is an ANSI and ISO standard computer language used for creating and manipulating
databases.

• It allows users to create, update, delete and retrieve from databases

• It can execute queries against the database
• It is used to describe the data, create and drop the database and table, to create a
view, stored procedure, function inn a database

• With the help of SQL language, the users can make different views of database
structure and databases for the different users

• It works with database programs like DB2, Oracle RDBMS, MS Access, Sybase, MS
SQL Server, etc.

• It is very easy and simple to learn

Advantages of SQL:

There are numerous advantages of SQL, some of them being—

• No coding needed: It is easy to manage the database systems without any need to
write its code by using the standard SQL

• High speed: The users can quickly and efficiently retrieve a large amount of records
from a database

• Well defined standards: There are no standards adhered by the non-SQL databases
• Portability: SQL can be used in the programs in PCs, servers, laptops, and even some
of the mobile phones

• Interactive Language: This domain language can be used for communicating with the
databases and receive answers to the complex questions in seconds

• Multiple data views: With the help of SQL language, the users can make different
views of database structure and databases for the different users

Disadvantages of SQL:

Along with some benefits, the Structured query language also has some certain
disadvantages —

• Difficult Interface: It has a complex interface that makes it difficult for some users to
access it

• Partial control: The programmers who use SQL don’t have full control over the
database because of the hidden business rules

• Implementation: Some of the databases go to the proprietary extensions to standard

SQL for ensuring the vendor lock-in

• Cost: The operating cost of some SQL versions make it difficult for some
programmers to access it

Types of SQL Commands:

SQL statements are categorised into four different types of statements :-

1. Data Manipulation Language (DML)

2. Data Definition Language (DDL)

3. Data Control Language (DCL)

4. Transaction Control Language (TCL)

1. Data Manipulation Language (DML)

In DML, there are four different SQL Statements —

1. SELECT - SELECT Statements is used to select the collection of records

from the table, which is based on some condition.

Eg - SELECT * FROM Student {Gets all the records of student

table}

2. INSERT - INSERT Statement is used to insert the set of values into the
table.

Eg - INSERT INTO Student (Rank, StudentName, Marks)

VALUES (1, ‘Kumar’, 450)

3. UPDATE - UPDATE Statement is used to update the existing values in the

table, which is based on some condition.

Eg - UPDATE Student set StudentName = ‘Manoj’

WHERE StudentName = ‘Kumar’

{The query given above will update the StudentName

from Manoj to Kumar where student name Kumar}

4. DELETE - DELETE Statement is used to delete the existing record in the

table, which is based on some condition.

Eg - DELETE FROM Student WHERE StudentName = ‘Manoj’

{The query given above will delete record which has

StudentName Manoj}

2. Data Definition Language (DDL)

In DDL, there are three different SQL Statements —

1. CREATE - CREATE Statement is used to create the new table in an

existing database.

Eg - CREATE TABLE Student (Rank Int, StudentName

varchar(50), Marks float)

2. ALTER - ALTER Statement can add a column, modify a column, drop a

column, rename a column or rename a table.

Eg - ALTER TABLE Student ADD (StudentAddress varchar(100))

3. DROP - SQL DROP TABLE Statement is used to remove a table definition

and all the data, indexes, triggers, constraints and permission
specifications for the table.

Eg - DROP Student

3. Data Control Language (DCL)

In DCL, it defines the control over the data in the database. We have two different
commands. These are —

1. GRANT - GRANT is allowed to do the specified user to the specified

tasks.

Syntax -

GRANT privilege_name

ON Object_name

TO {user_name | PUBLIC | role_name}

[WITH GRANT OPTIO};

2. REVOKE - It is used to cancel previously granted or denied permissions.

Syntax -

REVOKE privilege_name

ON object_name

FROM {user_name | PUBLIC | role_name}

4. Transaction Control Language (TCL)

In TCL, the commands are used to manage the transactions in the database.
These are the used to manage the changes made by DML statements. It also
allows the statements to be grouped together into logical transactions.

These are —

1. COMMIT - COMMIT command is used to permanently save any

transaction into the database.

Syntax -

COMMIT;

2. ROLLBACK - ROLLBACK command is used to restore the database for

the last committed state. It is also used to save point to
jump to the save point.

Syntax -

ROLLBACK TO savepoint_name;

3. SAVEPOINT - SAVEPOINT command is used to temporarily save a

transaction, so that you can roll back to that point
whenever necessary.

Syntax -

SAVEPOINT savepoint_name;

CONCLUSION: Here, we learned about SQL and the various commands that we can use
to perform different operations.
EXPERIMENT 5

OBJECTIVE: To acquire knowledge about performing queries on single table in SQL.

PROBLEM STATEMENT: Create a table ‘Student’ with attributes — Roll Number, Name,
Date of Birth, Phone Number, Address and Course. Also,
perform operations using INSERT, SELECT and WHERE
commands.

EXPLANATION: The different commands used in this experiment are —

1. CREATE - it is used to create a table.

2. INSERT - it is used to insert entries into a table. This command lets the
user insert entries into all attributes or just particularly chosen ones.

3. SELECT - it is used to display entries from the table. It lets the user
display all entries (SELECT * FROM Student) or particular entries
(SELECT Name, Address FROM Student).

4. WHERE - it is used to filter entries according to a condition set by this

command.

OUTPUT:

Creation of Table —
Insertion of Entries

Displaying Entries based on Conditions

CONCLUSION: Here we learnt how to create a table and perform operations on it like
displaying an entry based on a condition on one of the attributes.

EXPERIMENT 6

OBJECTIVE: To get a better grasp of SQL by learning about new commands

PROBLEM STATEMENT: Use the following commands on existing tables —

1. ALL

2. ALTER TABLE

3. ANY

4. EXISTS

5. GROUP BY

6. FULL JOIN

7. LEFT JOIN

8. RIGHT JOIN

9. ORDER BY

10. UPDATE

OUTPUT WITH EXPLANATION:

1. ALL Operator

ALL operator is used to select all tuples of SELECT statement. It is also used to
compare a value to every value in another value set or result from a subquery.

Here, it selected all the values in Student Table where CourseCode is ‘ECEUG’ and
displayed the Address column only.

2. ALTER TABLE Command

ALTER TABLE is used to add, delete (drop) or modify columns in the existing table.

ALTER TABLE - ADD

ADD is used to add columns into the existing table.

Here, Date of Birth column was added into the table and all the values are set to NULL by
default.

ALTER TABLE - DROP COLUMN

DROP COLUMN is used to drop column in a table.

Here, we deleted the PhoneNo column (as compared to above table).

ALTER TABLE - MODIFY

It is used to modify the existing columns in a table.

This command modified the Address column’s size to 100 bytes.

3. ANY Operator

ANY compares a value to each value in a list or results from a query and evaluates
to true if the result of an inner query contains at least one row.

This operator displayed the records in the Course table that have Duration = ‘2 years’.

4. EXISTS Condition

The EXISTS Condition inn SQL is used to check whether the result of a correlated
nested query is empty or not.

This helped us fetch the name of the students whose CourseCode matched with the
CourseCode in the Course table.

5. GROUP BY Statement

The GROUP BY Statement in SQL is used to arrange identical data into groups
with the help of some functions i.e if a particular group has same values in different rows
then it will arrange these rows in a group. It is used the SELECT Statement.

For the table Student, we grouped the data into the Subjects and Year and also displayed
their count.

6. LEFT JOIN Statement

The LEFT JOIN statement gives all rows in A, plus any common rows in B. If a
record in A doesn’t exist in B, it will return NULL for that row.

Here, since the last four tuples do not exist in the table Joining, their values for the
attributes Name, CourseID and Subject are NULL.

7. RIGHT JOIN Statement

The RIGHT JOIN statement gives all rows in table B, plus any common rows in A. If
a record in B doesn’t exist in A, it will return NULL for that row.

Here, the last three tuples have NULL values since those don’t exist in the table Students.

8. FULL JOIN

MySQL does not support FULL JOIN, so we have to combine RIGHT JOIN,
UNION, LEFT JOIN to get an equivalent. It gives the results of A union B. It returns all
records from both tables. Those columns which exist only in one table will contain NULL
in the opposite table.

Here, the values that don’t exist in the other table have been given NULL values.

9. ORDER BY Clause

The ORDER BY Clause is used for sorting the result set. It allows us to sort a result
set by a single or multiple columns, in ascending or descending order.

The first table shows the table being arranged in Ascending order (Default sorting) by
Names and in the second table they are arranged in Descending order (Done by the
keyword DESC) by Name.

10. UPDATE Statement

The UPDATE Command is used to modify rows in a table. It can be used to update
a single field or multiple fields at the same time.

In this, we changed all the Subjects that were ‘Mathematics’ to ‘Science’.

CONCLUSION: Through this experiment, we learnt the various Commands and

Conditions that can be performed in SQL, on Single and Multiple Tables.

EXPERIMENT 7

OBJECTIVE: To get an insight of operations on multiple tables.

PROBLEM STATEMENT: Perform Queries on multiple tables, that is, a table ‘Course’
with the following attributes — Course Code (unique identifier), Description, Duration,
Level; and another table ‘Student’ with the attributes — Roll Number, Name, Address,
Phone number, Course (Foreign Key related to Course Code of ‘Course’).

Following queries are to be performed —

1. The total number of Under Graduate Students and Post Graduate Students

2. List of Students in UG and PG Courses

3. List of Students and their course Description for Students of Software Engineering
Department

4. List of Students in a 2 year course from Software Engineering Department

EXPLANATION: The different commands used in this experiment are —

1. CREATE - it is used to create a table.

2. INSERT - it is used to insert entries into a table. This command lets the
user insert entries into all attributes or just particularly chosen ones.

3. SELECT - it is used to display entries from the table. It lets the user
display all entries (SELECT * FROM Student) or particular entries
(SELECT Name, Address FROM Student).

4. WHERE - it is used to filter entries according to a condition set by this

command.

5. REFERENCES - it is used to create a reference for one attributes to an

attribute in another table.

6. COUNT(*) - it is used to either count the total number of entries or some

entries based on a condition.

7. INNER JOIN - it selects records that have matching values in two related
tables.

OUTPUT:

Creation of ‘Student’ Table using REFERENCES command to relate both tables

Insertion and Display of ‘Student’ Table

Insertion and Display of ‘Course’ Table

Count of Under Graduate Students and Post Graduate Students (respectively)

List of Students in UG and PG Courses (respectively)

List of Students and their course Description for Students of Software Engineering
Department

List of Students in a 2 year course from Software Engineering Department

CONCLUSION: In this experiment we learnt how to relate two tables and create a Foreign
Key referenced to an attribute of another table and display entries from
both the tables together as one table.

PROBLEM STATEMENT FOR UNIVERSITY

MANAGEMENT SYSTEM
1. Database maintains information about all the departments of each
college in the university like name, contact no, address
2. Maintains info about all departments of each college like
Department name, contact no, Hod
3. Maintains info about all the courses being offered in each
department like course no, course title, year.
4. Maintains info about all the facilities for each college like name,
designation, qualification, address, contact no.
5. Maintains info about all students like name, year, contact no.,
address.
6. Maintains info about the progress report of student like year, grade,
Rank

ENTITY RELATIONSHIP DIAGRAM

DESCRIPTION
ASSUMPTIONS:
1. A college can have more than one contact numbers.
2. A student will be having a progress report for each passed year.
3. A lecturer can teach more than one course and also a course can be
taught by one or more lecturers.

INDENTIFICATION OF ENTITIES:
1. College (college_id, college_name, contact_no, address)
2. Department (dept_id, dept_name, contact_no, hod_no)
3. Courses (courses_id, course_title, year)
4. Faculty (fac_id, fac_name, designation, qualification,
contact_no, address)
5. Student (std_id, std_name, year, admission_date)
6. Progress_report (report_id, year, grade, rank)

INDENTIFICATION OF ENTITIES:
1. College: Department (1: N)
2. Department: Faculty (1: N)
3. Faculty: Courses (M: N)
4. Department: Courses (1: N)
5. Department: Student (1: N)
6. Student: Progress_report (1: N)

ENTITY RELATIONSHIP DIAGRAM FOR UNIVERSITY DATABASE

1. COLLEGE

2. CLG_CONTACT
3.DEPARTMENT

4. FACULTY

5. COURSES
6. STUDENT

7. PROGRESS_REPORT
8. TEACHES

SQL AGGREGATE FUNCTIONS

1. AVG ():

2. COUNT ():
3. SUM ()

4. MAX ():

5. MIN ():



DATE FUNCTIONS

1) SYSTEM DATE

2) SYSTEM DATE AND TIME

PERFORMING JOIN ON THE DEFINED UNIVERSITY DATABASE

1. List details of all the students who are allotted to some department along with
their department details. (inner join)
2. List all the faculty members of the university along with the courses id they
teach, if they teach any course. (left join)

3. List faculty id, faculty name, course id and course name for all the faculty
members and courses even if the faculty teaches no course or course is taught by
none faculty. (full join)
4. List name and id of faculty with the department name whose hod they are, if
they are. (right join)

DISCUSSION:

• We discussed how performing different joins, on defined University

Database, data can be retrieved in different ways showing relation
between tables.
• The data could be retrieved from more than one table which gave
view maintaining proper relation between tables.
• We saw that how different joins create different kind of views.
• We implemented the use of aggregate functions in the tables.
• We implemented date functions which enables to get system date and
time.

CONCLUSIONS:

• The database for University is implemented successfully.

• The implementation of INNER JOIN, OUTER JOIN (Full join, right join,
left join) query to retrieve data from tables is successful.
• The implementation of aggregate functions on the database is
completed.