Database Design and
Applications(SSZ G518)
2nd Semester 2016-17
Assignment
Date: 10-03-2017
Please read the following Instructions carefully.
(1) You can do this assignment on
some RDBMS of your choice, excepting MS Access.
(Ex: Oracle, MySQL, SQL Server,
IBM DB2, Post GRESQL etc.)
(2) Table names:
If student ID is : 2015HT12205 and name is : Mohan Krishna
Then that student has to name his
tables as follows.
For example, if it is EMP table,
it should be created as
EMP_Mohan_12205
You are required to follow the
same naming convention for all tables.
(2) The final doc which is to be
uploaded will be names as : Assignment_ Mohan_12205.
(3) Make sure that the final
Document is in word doc format. Max
file size is: 10MB.
Do not upload PDF. It will not be evaluated.
(4) The final document will
contain only screenshots of SQL interface.
(5) Look at the sample submission
content available at the end of this
documentation to get an idea about how the final submission doc should look
like.
Table Creation and Data insertion:
(i)
Create following Tables with required
constraints (as mentioned) using SQL DDL.
Student
|
sid
(int)
PK
|
sname VC(10)
|
sbranch
Char(5)
|
sage (int)
(not greater than 25)
|
|
1001
|
Kamal
|
IT
|
18
|
|
1004
|
Yogesh
|
IT
|
17
|
|
1011
|
Mani
|
CSE
|
20
|
|
1022
|
Farooq
|
EEE
|
21
|
|
1027
|
Girija
|
CSE
|
24
|
|
1030
|
Mousmi
|
CSE
|
22
|
|
1036
|
Rajanish
|
EEE
|
21
|
|
1052
|
Praneet
|
EEE
|
21
|
|
1059
|
Joseph
|
CSE
|
17
|
|
1063
|
Lahiri
|
IT
|
21
|
Course
|
cid (int) PK
|
Cname
(chars 4 and unique)
|
units(int)
|
|
10
|
OS
|
3
|
|
20
|
DBMS
|
4
|
|
30
|
CNW
|
5
|
|
40
|
PHY
|
3
|
StudentCourse
|
stid PK1 and FK to sid
of student
|
cno PK2 and FK to cid of course
|
|
1001
|
20
|
|
1001
|
30
|
|
1004
|
10
|
|
1004
|
20
|
|
1004
|
30
|
|
1004
|
40
|
|
1027
|
20
|
|
1027
|
30
|
|
1036
|
10
|
|
1036
|
30
|
|
1036
|
20
|
|
1059
|
10
|
|
1063
|
20
|
Take the screenshot of the successful statement and put it
in word doc.
(ii)
Execute insert commands to insert data in to the
tables. (no screenshots needed)
(iii)
Execute
commends Select * from <table>
for all tables and put the screenshots in to the file.
(iv)
Insert
following tuple into Student table and take the screenshot of the response and
put into the doc.
<1052, Kumar, EEE, 24>
As the sid 1052 already exists in
the table it should not be allowed. (PK Constraint)
(v)
Insert
following tuple into StudentCourse table
and take the screenshot of the response and put into the doc.
<1089, 30> As the
sid 1089
does not exist in the Student
table , it should not be allowed. (FK constraint)
(vi)
Insert
following tuple into Student table and
take the screenshot of the response and put into the doc.
<1070, Rajender, IT, 270>
As the age > 25 it should not be allowed. (Check constraint)
SQL
Queries:
Now Write SQL statements for the following data retrieval needs,
execute statements and take screenshot that includes both the SQL statement and
Result together and put the image into the file.
Q1: Get branch name and number of
students in each branch.
Q2. Get sid and sname for
those whose age is > 20 and in CSE branch.
Q3. Get sid and sname for those whose name starts
with ‘M’.
Q4. Get sid, sname and number of courses
registered for those who have at least one course registration.
Q5. Get sid,
sname for those who have not registered for any course.
Q6. Get cid, and
name for courses registered by all
students from EEE.
Q7. Get sid and
sname for those registered for at least 3 courses.
Q8. Get sid and
sname for those whose name has 6 characters.
Q9. Get sid and
sname for those who have not registered for any 5 credit course.
Q10. Get sid,
sname for those registered for all 3
credit courses.
Q11. Get sid and
sname for those registered for the course ‘OS’.
Q12. Get cid and
cname for thoses not registered by any CSE student.
Evaluation
Pattern:
Table creation: 2.5 Mark
Constraints: 0.5 Marks
Data insertion: 1 Mark
Each Query: 0.5 Marks.
Total 10 Marks.
(Note: Manual evaluation; partial
marking is done depending on the merit of the work.)
Prof.R Gururaj
Course IC for SSZG518
Sample
submission content (to be put in the final submission MS Word doc):
Assignment Screenshots:
Table creation and data insertion:
Data display after insertion in each table:
Query Execution:Q1:
Get eid, ename for those whose age is > 40.
Important: Query and
result should be together otherwise marks will not be awarded.
Note: After taking the screenshot you may paste the image in
MS Paint and select the required portion
to paste back into the final submission doc (word) .
Final Submission need to be Word document NOT PDF.
No comments:
Post a Comment