DBMS lab report Sample- Bikram Adhikari

                                               A PROJECT






prepared by : Bikram Adhikari     Submitted To : Karna pun 




(page 2)


First of all we would like thank our computer facilitator Mr. Anil Lamichhane for giving us this golden opportunity to write a report on database system. We also thank the college management for providing such opportunity to develop our presentation skill. Furthermore we are really glad to perform this task.


DBMS-Database Management System

DDL-Database Definition Language

DML-Database Manipulation Language

DBA-Database Administrator

ER-Entity Relation

OODM-Object-Oriented Data Model

EER-Extended Entity Relation Model

SQL-Structured Query Language


1. Introduction to database

1.1 Basic concept………………………………………………………………………..1

1.2 Database and data management system……………………………………………..

1.3 database instance and schema…………………………………………….................

1.4 Data abstraction………………………………………………………………….

1.5 Data languages………………………………………………………………………

1.6 Transaction management…………………………………………………………....

1.7 Database administrative……………………………………………………………..

1.8 Database models…………………………………………………………………….

2. Entity relationship model

2.1 Entity Relationship Model

2.2 Entity relationship diagram

2.3 Components of ER model

2.4 Keys

2.5 Extended ER Model

3. Structured Query Language

3.1 Introduction………………………………………………………………..


The University Management System is concerned with the proper management of a University. It deals with the university staff, course offering, students admitted, credit hours, and many more. We have prepared by our group and it was quiet entertaining. We have sort out about the university. We have done research through field visit to the university. It helped us to find out about university, its origin, course offerings and many more.


The main purpose of this report is to find out about the university management system. The university management system provides easy access to the authorized users. This system also help to manage the data of university. The university may conatain professor, student, course offering, credit hours. This can be managed through this system.


Database Management system

Database manipulation language

Database control Language

Data Definition language




University Management System is that software which helps administrator, teachers, and students with their activities and keeps them up to date with their data. This software aims to integrate all the modules and allows different sections heads to have access to their specific departments only. All the sections are grouped into single central zone, which is the main administration. It helps to manage all students, teacher and other faculty members database and all other activities. The software helps you with the daily routine of university management and reduces the paper work to zero. Not only this , the software saves your time, helps in proper utilization of time and most important, helps you to have complete control in your university.




A database is an organized collection of logically related data that contains information relevant to an enterprises . The database is also called the repository or container for collection of data files. For example ,university database maintains information about students course offering and grades in university.


A database management system (DBMS) is the set of programs that is used to store , retrieve and manipulate the data in convenient and efficient way. Main goal of database management system (DBMS) is to hide underlying complexities of database management from user and  provide easy interface to them . Some of the common examples of DBMS software are Oracle, Sybase, Microsoft SQL, MySQL  etc.

Database Instance and Schema

The overall structure of the database is called database schema. In the relational model , the schema  for a relation specifies its name , the name of each field , and the type of each field . For example, employee information in a company database may be stored in a relation with following schema

Employee( Eid: string, Ename :string, Address: string, , salary:integer)

The collection of information stored in the database at a particular moment is called an instance of the database. It is the actual content of the database at a particular point in time. Database instance changes frequently with every insertion deletion and update operation performed in data stored in a database.


Data abstraction is the process of providing easy interface to users by hiding underlying complexities of data management from users. Database system provides users with an abstract  view of the data . Data abstraction is provided in database management systems by using three level schema (ANSI/SPARC) architecture.


Transaction is a collection of operations that form a single logical unit of work. For example , a transfer of fund from a checking account to  saving account is a single operation from the customer's point of view; however it consists of several operations. A database system must ensure proper execution of transactions despite failures either the entire transaction should execute or none of it does.

To ensure integrity of the data , we require that database transactions must satisfy ACID properties, where A stands for acidity, C stands for consistency, I stands for isolation, D stands for durability.


Database administrative (DBA) is a person who has the central control over both data and application programs. The responsibilities of DBA vary depending upon the job description  and corporate and organization policies . Some of the responsibilities of DBA are given here.

1. Schema Definition and Modification: It is the responsibility of the DBA to create the database schema by executing a set of data definition statement in DLL . The DBA also carries out changes to the schema according to the changing needs of the organization.

2. Security enforcement and administration:  DBA is responsible for establishing and monitoring the security the security of the database system. It involves adding and removing users, auditing , checking for security problems etc.

3. Performance tuning: DBA is responsible for analyzing the data stored in the database , and studying its performance and efficiency in order to effectively use indexes, parallel query execution etc.

4. Database design: DBA works with the development team during the database design stage due to which many potential problems that can arise later can be avoided.

5. Physical organization modification : DBA is responsible for carrying out the modifications in the physical organization of the database for better performance.

6. Data backup and maintenance : DBA is responsible for taking the database backup periodically in order to recover from any hardware or software failure. other routine maintenance checks that are carried out by the DBA are checking data storage  and ensuring the availability of free disk space for normal operations , upgrading disk space as and when required, etc.


As discussed earlier , the main objectives of database system is to highlight only the essential 

features and to hide the storage and data organization details from the user. This is known as data abstraction. A database model provides the necessary means to achieve data abstraction. Database model or simply a data model is an abstract model that describes how the data is represented and used. A data model consists of a set of data structures and conceptual tools that is used to describe the  structure (data types, relationships, and constraints) of a database.

There are different database models which are  used to design and develop the database of the of the organization.

 a. Hierarchical Model

b. Network Model

c. Entity Relationship Model

d. Relational Model

 e. Object Oriented Model

f. Object Relational Model

a. Hierarchical Data Model

the hierarchical data model is the oldest data model. It is the record based representational or implementational data model. In the hierarchical model , different  records are inter related through hierarchical or tree like structures. The root may have any number of dependents may have any number  of lower level dependents. A parent record can have several children , but a child can have only one paren. Therefore it represents only one to one and one to many relationship.

b Network Model

Network data model is an extension of hierarchical database structure. It is also record based  representational or impelementational data model. Network model is more flexible than hierarchical data model. It describes data and relations between data by using graph rather than tree like structure. Thus unlike network data model is able to represent many to many relationships also.

c. Entity Relationship Model

The entity relationship data model is based on a perception of real world that consists of a collection of  basic objects called entities and relationship among these objects. In ER model  a database can be modeled as a collection of entities, and  relationship among these entities. It is one of the conceptual data model and describes the information used by an organization in a way that is independent of any implementation level issues and details 

d. Relational Model

 The relational data model was developed by E.F C odd in 1970. It is also representational or impelemenatational data model. In the relational data model all the data is maintained in the form of tables consisting of rows and columns. Each row represents an entity and a column represents an attribute of the entity. The relationship between the two tables is implemented through a common attribute in a tables and not by physical links or pointers. This makes the querying easier in a relational database system than in the hierarchical or network database systems.



The entity relationship model is the most popular conceptual model used for designing a database. It was originally proposed by Dr. Peter Chen in 1976. The ER model views the real world as a set of basic objects , their characteristics and associations among these objects. The entities , attributes and relationship are basic constructs of an ER model.

The information gathered from the user forms the basic for designing the ER model. The nouns in the requirements specification document are represented as the entities , the additional nouns that describe the nouns corresponding to entities form the attributes and the verbs become the relationship among various entities. 


Once the entity types, relationships types, and their corresponding attributes have been identified, the next step is to graphically represent these components using entity-relationship diagram. An ER diagram is specialized graphical tool that demonstrates the interrelationships among various entities of a database. It is used to represent the overall logical structure of the database. While designing ER diagram , the emphasis is on the schema of the database and not on the instances. This is because the schema of the database is changed rarely ; however, the instance in the entity and relationship sets change frequently. Thus ER diagrams are more useful in designing the database.


Entity and entity sets

An entity is an object or a concept that is identified by the enterprises as having an independent existence. For example, each student in a class is an entity. An entity usually has an attribute whose value is distinct for each individual entity in the collection. An entity set is asset of entities of the same type that share the same properties, or attributes. For example, set of all student in a class is an entity set. Set of all employees is another example of entity set.


Attributes are the information that explains the properties of an entity. For example a customer entity can have customer id, customer name, customer street, and customer city as attributes. Attributes are the descriptive properties possessed by all members of an entity set. Each entity may have its own value for each attribute. For each attribute, there is a set of permitted values, called the domain or value set of that attribute. Attributes are represented by oval with attribute names inside as given example below.

Relationship and relationship set

Association between two or more entities is called relationship. For example, "Arjun  teaches Elina". Here teaches is the association between entities 'Arjun' and ' Elina'. A relationship set is a set of relationships of the same type. In another way, we can say that association between two entity sets is called relationship set. Formally, it is a mathematical relation on n=>2 entities sets.

Here is an example of relationship


Set of one or more attributes whose values are distinct for each individual entity in the entity set is called key, and its value can be used to identify each entity uniquely. For example , the name attribute is a key of the entity set Company because no two companies are allowed to have the same name. There are different types of keys which are as follow:

a. Super key

b. Candidate key

c. Primary key

d. Composite key

e. Foreign key

These keys are further more described below:

a. Super key: A super key of an entity set is a set of one or more attributes whose values uniquely determine each entity in the entity set. If K is a super key and any superset of K is also super key. For example student-id attribute of the entity set student is sufficient to distinguish one student entity from another. Thus the student-id is a super key.

b. Candidate key: A candidate key of an entity set is minimal super key. That is super key whose proper subset is no longer super key is called candidate key. For example, student-id is candidate key of the entity set student but set of attributes{roll no, name, program, semester, section} is not candidate key of the entity set student because it has proper subset which is also key.

c. Primary key: A primary key is a candidate key that is chosen by the database designer as primary means of uniquely identifying entities within an entity set. There may exist several candidate keys, one of the candidate keys is selected to be primary key. For example, entity set student have two candidate keys : student-id and { roll number , program , semester, section}. If the database design choose student-id for the purpose of uniquely identifying entities within entity set thenit becomes primary key. Primary key must satisfy following conditions:

  • It cannot be null

  • It cannot be duplicated

c. Composite key: A primary key contains more than one attribute then it is called composite key. For example database designer choose student-id as primary key then it is not composite key but if database designer choose {roll number, program, semester, section } as primary key then it is also composite key.

d. Foreign key: A foreign key is an attribute or combination of attributes that is used to establish and enforce relationship between relations. A set of attributes that references primary key of another table is called foreign key. For example, if a student enrolls in program then program id(primary key of the relation) can be used as foreign key in student relation.


SQL (Structured query language ) is a database language designed for retrieval and management of data stored in relational database management system (RDBMS), database schema creation and modification , and database object access control management. It is non-procedural query language. This means we need to specify only what information is required, rather than hoe to get it. SQL is originally designed as a declarative query and data manipulation language, variations SQL  have been created by SQL database management system (DBMS) vendors  that add procedural constructs, control-of-flow statements, user-defined data types and various other language extensions. With the release of SQL : 1999 standard , many such extensions were formally adopted as part of the SQL language.


The SQL modification statements make changes to database data in tables and columns. There are three database modification statements:

a. INSERT statement : It is used to add tuples into database relations

b. UPDATE statement: It is used to modify attribute values in relations

c. DELETE  statement : It is used to remove tuples from relations.

These are furthermore described below:

a. INSERT Statement: The INSERT statement is used to add one or more tuples into database relations. It has two formats:

INSERT INTO table-1[(column list )] VALUES(value list)


 INSERT INTO table-1[column list)](query specification)

b. UPDATE statement: The UPDATE statement is used to modified column values in selected table rows. It has the following general format:

UPDATE table-1 set list [WHERE predicate]

The optimal WHERE clause has the same format as the SELECT  statement. The WHERE clause choose which table rows to update. If it is missing , all rows are in table-1 are updated. The set list contains assignments of new values for selected columns. The SET Clause expressions and WHERE Clause predicate can conatain subqueries, but the subqueries cannot reference table-1.

c. DELETE Statement: The DELETE statement is used to remove selected rows from table. It has the following general format:

DELETE FROM table-1[WHERE predicate]

The optional WHERE clause has the same format as in the select statement.  The WHERE clause chooses which table rows to delete. 


The SQL standard supports a variety of built in domain types. Data type specifications included in SQL are as follow:

a. Char (length) or character(length): A fixed length character string with user-specified length.

b. Varchar(length): A variable length character string with user specified maximum length.

c. int or integer: represents a whole number. It is a finite subset of integers whose range is machine dependent.

d. Float: these are approximate numeric values that have fixed precision but floating decimal point. they have machine dependent precision.

1. CREATE Statement: The different structures that are created by DDL are tables, views, sequences, Triggers, Indexes, etc.


         CREATE TABLE (table name)

          (                          <column1> <data type>[not null][unique][<intigrety                                                                                      


                                <column2><data type>[not null][unique][<integrity                                                                                                           constraints>]


ALTER Statement

It allows us to modify a given table. The structure of given table can be changed by adding  new column in existing table, by deleting some columns from an existing table, or by modifying some column definitions given table.

Adding new column


ALTER TABLE<table name>

ADD (<column name> <data type>);

Removing column


ALTER TABLE<table name>

DROP (column name);

Modifying  column definition 


ALTER ABLE<table name>

MODIFY(<column name><data type>);

DROP Statement

It allows us to remove an existing table, view or other database object from the database.


DROP TABLE <table name>


The University Management system have following  data entries

a. Course(number , title, credit hour, syllabus and requisition)

 b. course offering( course number, year, semester, section , number, instructor, timing and classroom)

c. Student(s-id,  name, and program)

d. Professor( Id, name , department and title)








This is only for educational purpose.

Related Posts

Post a Comment