Database Management System(3130703) GTU Paper Solution winter 2021

Date:21-02-2022
Time:10:30 AM TO 01:00 PM
Total Marks:70

Q.1
(a) Explain three levels of data abstraction.

Answer:

Three level abstraction(architecture).

- It is also called ANSI/SPARC architecture or three schema architecture.

- This framework is used to describe the structure of specific database system.

- It breaks database into three different categories.

1. Physical/ internal level
2. Logical / conceptual level
3. External level / view level

1. Physical/ internal level :

- It has an physical schema which describe the physical storage structure of the database - It uses the physical data model. It is used to define that how the data will be stored in block - It is used to describe complex low level data structure - It concerns with following activities

storage space allocation
access path
Data compression and encryption technique
optimization of internal structure
representation of stored fields

2. Logical / conceptual level :

- It has logical schema which describe the design of a database at the conceptual level
- It describes the structure of whole database
- Also, what data are to be stored and what relationships exist among those data.
- Internal details such as implementation of the data structure are hidden
- Programmers and database administrators works at this level.

3. External level / view level :
- It has an external scheme I reach contains several schemas known as sub schemas
- Desh sab schema is used to describe the different view of the database
- This view schema describe and user interaction with database systems

Q.1
(b) Describe Data Definition Language and Data Manipulation Language.

Answer:

DDL

-Data Definition Language
-DDL actually consists of the SQL commands that can be used to define the database schema.
-List Of DDL commands.
(i) Create:

- Create command is used to create table or create database.

 Syntax: 
CREATE TABLE <table_name>(
    column1 datatype, 
    column2 datatype, 
    ...
);

Example:

 CRAETE TABLE STUDENT(
    name varchar2(15), 
    enroll_no number(12), 
    phone_no number(10)
);

(ii) Drop:

- Drop command is used to drop(delete) a table or database.

- Syntax:

DROP TABLE <table_name>;

- Example:

DROP TABLE student;

(iii) Truncate:

- Truncate command is used to delete all data inside the table, but not delete the table.

- Syntax:

TRUNCATE TABLE <table_name>;

- Example:

TRUNCATE TABLE student;

(iv) Alter:

- Alter command is used to add, delete or modify columns in the existing table and also used to add and drop constrains on an existing table.

- Syntax:

ALTER TABLE <table_name> ADD column_name datatype;

- Example:

ALTER TABLE student ADD marks number(3,2);

DML

-Data manipulation language.
-Dml consists of the set of commands that deals with manipulation of data present in the database.
-List of DML commands

(i) Insert:

- Insert command is used to insert data into the table.

- Syntax:

INSERT INTO <table_name> (column1, column2, column3) VALUES (value1, value2, value3);

- Example:

INSERT INTO student (name, enrollno, marks) VALUES ('Anil', 222333444555, 99.99);

(ii) Update:
- Update command is used to modify data in the table.
- Syntax:
UPDATE <table_name> SET column1=value1, column2=value2, ... WHERE condition;- Example:
UPDATE student SET name='Sunil' WHERE name='Anil';

(iii) Delete:
- Delete command is used to delete existing record in table using specific condition.
- Syntax: 
DELETE FROM <table_name> WHERE condition;- Example:
DELETE FROM student WHERE marks=99.99;

Q.1
(c) What is integrity constraint? Explain primary key, reference key and check constraint with SQL syntax.

Answer:

Integrity constraint:

- Integrity constraints are rules that are to be applied on database columns to ensure the validity of data.
For Example -
i. Phone number must consist of 10 digits.
ii. Every employee ID must start with a letter.
- Every time data is entered into the particular column, it is evaluated against the constraint and only if the result comes out to be true then the data is inserted into the column.
- Integrity constraints ensure that the data insertion, deletion, updation have to be performed in such a way that data integrity is not affected.
- The integrity constraint thus helps in maintaining the quality of a database by managing the consistency among the data present in the database.
- Integrity constraints are extremely necessary for any database management system.

Primary Key:

- The primary key is candidate key chosen by the database designer to identify the tuple in relation uniquely
For example - consider the following representation of a primary key in the student table.

Student
Enroll no	Name	Marks	Phone no
101	Anil	90	99999XXXXX
102	Sunil	75	45678XXXXX
103	Suhani	84	54851XXXXX
104	Sanjay	89	12456XXXXX

In the above table enroll no is primary key because with the help of enroll no we can identify each student uniquely.
Rules for primary key:
- The primary key must have one or more attributes.
- There is only one primary key in the relation.
- The value of the primary key attribute cannot be null.
- The value of the primary key attribute does not get changed.

Reference key( foreign key):

- foreign key is a single attribute or collection of attributes in a table that refer to the primary key of another table.
- The table containing the primary key is called the parent table and the table containing foreign key is called the child table.

Student
Enroll no	Name	Marks	Phone no
101	Anil	90	99999XXXXX
102	Sunil	75	45678XXXXX
103	Suhani	84	54851XXXXX
104	Sanjay	89	12456XXXXX

Course
Enroll no	CourseID	CourseName
101	C07	Computer
102	C17	Electrical
103	C41	Robotics
104	C16	IT

From the above example, we can say that two tables are linked for instance, we could easily find out that the student Anil has opted for a computer course.

Check constraint:

- Constraint is used to limit the value range that can be placed In in a column.
- If you define a check constraint on a column it will allow only certain values for this column.

- If you define a check constraint on the table it can limit the value in certain columns based on value in the other columns in the row.
- SQL syntax of CHECK constraint:

 CREATE TABLE Persons (
    ID int NOT NULL,
    LastName varchar(255) NOT NULL,
    FirstName varchar(255),
    Age int CHECK (Age>=18)
);

Next Question

Database Management System(3130703) GTU Paper Solution winter 2021