Birla
Institute of Technology & Science, Pilani
Work-Integrated
Learning Programmes Division
First
Semester 2016-2017
Mid-Semester
Test
(EC-2
Regular)
Course No. : SS ZG518
Course Title : DATABASE DESIGN AND
APPLICATIONS
Nature of Exam : Closed Book
Weightage : 35%
Duration : 2 Hours
Date
of Exam : 25/09/2016 (AN)
No
of pages: 2
No
of questions: 4
Note:
1. Please
follow all the Instructions to Candidates given on the cover page of the
answer book.
2. All
parts of a question should be answered consecutively. Each answer should start
from a fresh page.
3. Assumptions
made if any, should be stated clearly at the beginning of your answer.
Q.1 (a)
Mention
the 3 basic components of E-R model [1.5]
E-R Model components
1)
Entity
2)
Attribute
3)
Relationship
Q.1 (b)
Mention
the 5 attributes types
[2.5]
Attribute Type
1)
Simple
2)
Derived Attribute
3)
Complex Attribute
4)
Composite Attribute
5)
Multi-valued attribute
Q.1 (c)
Mention
the 3 entity sets and explain them. [3]
An entity
set is a set of entities of the same type.
Student: Set of BITS
students
Faculty: Set of BITS
faculties
Course: Set of BITS
courses
Q.2 (a)
Create a relation(table) EMPLOYEE with
Primary key emp_no [4]
emp_no fixed length 11 characters
emp_name variable length of maximum 30
characters
designation fixed length 10 characters’
salary number (integer)
dept_no number (integer) with the value constraint 1 to 20
CREATE TABLE EMPLOYEE
(
emp_no CHAR(11),
emp_name VARCHAR(30),
designation CHAR(10),
salary INT,
dept_no INT CONSTRAINT CHK_DEPT CHECK (dept_no >= 1 AND dept_no <= 10),
CONSTRAINT PK_EMPLOYEE PRIMARY KEY CLUSTERED (emp_no)
)
Q.2 (b)
Create
a relation(table) ORDER with primary key (order_no, cust_no, item_code)
order_no number(integer)
cust_no number(integer)
item_code fixed
length 10 characters
order_date date
quantity number(integer)
by using the
following relations(tables):
customer(cust_no,
cust_name, address)
item(item_code,
item_desc) [7]
CREATE TABLE ORDER
(
order_no INT,
cust_no INT
REFERENCES customer.cust_no(cust_no),
item_code CHAR(10)
REFERENCES item.item_code(item_code),
order_date date,
quantity INT,
CONSTRAINT PK_ORDER PRIMARY KEY CLUSTERED (order_no, cust_no,
item_code)
)
Q.3.
Write
the SQL commands(statements) for the given relation below
employee(emp_no,
emp_name, designation, salary, dept_no)
(a)
Create
a view CLERK_V containing the emp_name, designation and salary of employees for
designation ‘CLERK’.
CREATE VIEW CLERK_V
AS
SELECT emp_name, salary
FROM employee
WHERE designation = ‘CLERK’
(b)
Update
the salary of all employees whose designation is ‘SALESMAN’ working in the
dept_no 20 with the salary 50000. [4 + 3 = 7]
UPDATE employee
SET salary = 50000
WHERE designation = ‘SALESMAN’
AND dept_no = 20
Q.4.
student(st_id,
name, birth_date, gender)
course(course_no,
title, units)
st_course(st_id,
course_no, sem, year, section_no, grade)
Note:
All the Computer Science courses start with CS,
Electrical
Engineering courses with EE,
Mechanical Engineering courses by ME
(a)
Write an SQL statement to list all the
students with st_id, st_name whose name contains KUMAR
SELECT st_id, name
FROM student
WHERE name LIKE ‘%KUMAR%’
(b)
Write an SQL statement to list all the
Computer Science, Electrical Engineering
courses only with st_id, course_no, sem, year, section_no, grade, by
arranging the course_no, section_no, st_id
in the ascending order.
SELECT s.st_id,
sc.course_no, sc.sem, sc.year, sc.section_no, sc.grade
FROM student s INNER JOIN
st_course sc ON s.st_id = sc.st_id
INNER JOIN course c ON sc.course_no = c.course_no
WHERE c.title LIKE ‘CS%’
OR c.title LIKE ‘EE%’
ORDER BY course_no ASC,
section_no ASC, st_id ASC
(c)
Write an SQL statement to list all students
with st_id, name, course_no, who have taken more than 6 Computer Science
courses. [2 + 3 + 5 = 10]
SELECT s.st_id, s.name
FROM student s INNER JOIN
st_course sc ON s.st_id = sc.st_id
INNER
JOIN course c ON sc.course_no = c.course_no
WHERE c.title LIKE ‘CS%’
GROUP BY s.st_id, s.name
HAVING COUNT(sc.course_no)
>6
*********
No comments:
Post a Comment