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
*********
Awesome...
ReplyDeleteSurvival8: Bits Wilp Software Development For Portable Devices 2017-H2 Handout >>>>> Download Now
Delete>>>>> Download Full
Survival8: Bits Wilp Software Development For Portable Devices 2017-H2 Handout >>>>> Download LINK
>>>>> Download Now
Survival8: Bits Wilp Software Development For Portable Devices 2017-H2 Handout >>>>> Download Full
>>>>> Download LINK Ye
i tried mailing you , but it is not going through. Can you please share the answers to this question paper if you have.
ReplyDeleteThanks
Mail me at ashishjainblogger@gmail.com
DeleteMention the subject(s) for which you want help.
Thanks for putting up the content in such organized manner.Great Help. Thumbs UP !!
ReplyDeleteRecorded lecture links are not working please help
ReplyDeleteIs there a group/whatsapp group for Mtech in computing systems and Infrastructure?
ReplyDeleteVery useful .. Thank you very much
ReplyDeleteAre you Looking for Smart Device Development? QuantaEd Solutions is the Best Smart Device Development Company, We design and develop smart devices that suit the healthcare requirements. For any inquiry call us we will provide all kind of assistance. For more details visit- https://quantaedsolutions.com
ReplyDeleteThis post is so interactive and informative.keep updating more information...
ReplyDeleteSoftware Testing Courses in Mumbai
Software Testing Training in Ahmedabad
Software Testing Courses in Kochi
Software Testing Courses in Trivandrum
Software Testing Courses in Kolkata
Thanks for the blog article.Thanks Again. Keep writing.
ReplyDeletejava online training hyderabad
java online training in india
Thanks for the blog article.Much thanks again. Fantastic.
ReplyDeleteonline training in java
online training on java
AI & ML in Dubai
ReplyDeletehttps://www.nsreem.com/ourservices/ai-ml/
Artificial intelligence is very widespread today. In at least certainly considered one among its various forms has had an impact on all major industries in the world today, NSREEM is #1 AI & ML Service Provider in Dubai
1634348519669-9
Thank you for giving valuable information about software for portable device, we can also develop custom software from pixabulous design.
ReplyDeleteNice Blog!!!
ReplyDeleteServiceNow Training
ServiceNow Online Training in Hyderabad
This article explains in a clear manner. Nice way of explaining. Thanks for sharing. cloud engineering services
ReplyDeleteI really liked your blog post.Much thanks again. Awesome.
ReplyDeletejava online training
java training
Data Science Training In Noida
ReplyDeleteData Science course In Noida
WILP is a set of educational programs designed in such a way that they can be easily integrated into your work life. Earlier, only highly developed nations like the US and Europe were indoctrinating WILPs but now the WILP in India have also gained a lot of popularity.
ReplyDeleteCandidates who wish to take the BITSAT should begin studying as soon as possible. Due to the high level of competition, it is critical to follow the best BITSAT 2022 preparation tips recommended by professionals. This blog post contains BITSAT 2022 study suggestions as well as exam pattern and syllabus information. Continue reading to get answers to all of your questions. To know more information visit @ SSSi Online Tutoring Services.
ReplyDeleteSurvival8: Bits Wilp Software Development For Portable Devices 2017-H2 Handout >>>>> Download Now
ReplyDelete>>>>> Download Full
Survival8: Bits Wilp Software Development For Portable Devices 2017-H2 Handout >>>>> Download LINK
>>>>> Download Now
Survival8: Bits Wilp Software Development For Portable Devices 2017-H2 Handout >>>>> Download Full
>>>>> Download LINK bO
"Thanks for sharing this informative blog on Best Software Development company in chennai,Software Development Company in chennai,
ReplyDeleteBest Software Development company in india,
Top software development company in chennai,
Software Development Company in india"
The BITS Pilani Admission Process is designed to select the brightest minds for its world-class programs. With its independent entrance exam, BITSAT, and direct admission opportunities for board toppers, the institute ensures that only the most deserving candidates secure a place.
ReplyDelete