DEPARTMENT OF COMPUTER SCIENCE & APPLICATIONS

KURUKSHETRA UNIVERSITY, KURUKSHETRA

Session: 2023-24
Part A - Introduction
Subject COMPUTER SCIENCE
Semester III
Name of the Course Data Management
Course Code B23-SEC-302
Course Type: SEC
(CC/MCC/MDC/CC-
M/DSEC/VOC/DSE/PC/AEC/
VAC)
Level of the course (As per An-
nexure-I
Pre-requisite for the course (if
any)
Course Learning Outcomes(CLO): After completing this course, the learner will be able to:
1. Describe major components of DBMS and their func-
tions
2. Model an application‟s data requirements using con-
ceptual modelling tools like ER diagrams and de-
sign database schemas based on the conceptual
model.
3. Write queries in relational algebra / SQL
4. Normalize a given database schema to avoid data
anomalies and data redundancy.
____________________________________________
5*. to implement the concepts of databases using SQL.
Credits Theory Practical Total
2 1 3
Contact Hours 2 2 4
Max. Marks:75(50(T)+25(P)) Time: 3 Hrs.(T), 3Hrs.(P)
Internal Assessment Marks:20(15(T)+5(P))
End Term Exam Marks: 55(35(T)+20(P))
Part B- Contents of the Course
Instructions for Paper- Setter
Examiner will set a total of nine questions. Out of which first question will be compulsory.
Remaining eight questions will be set from four unit selecting two questions from each unit.
Examination will be of three-hour duration. All questions will carry equal marks. First question
will comprise of short answer type questions covering entire syllabus.
Candidate will have to attempt five questions in all, selecting one question from each unit. First

30
question will be compulsory.
Practicum will be evaluated by an external and an internal examiner. Examination will be of
three-hour duration.
Unit Topics Contact
Hours
I Database Management System – Introduction and Purpose, Database 6
Architectures: Centralised, Client-Server, Parallel, Distributed, Web
based system: Web architecture (2 tier, 3 tier, N-tier Architecture)
Database Storage Structures: Introduction, Indexing, Hashing, Data
Dictionary.
II Data Models: Introduction to various data models, Cardinality Ratio 6
& Relationships, Representation of entities, attributes, relationship
attributes, relationship set, Generalization, aggregation, Structure of
relational Database and different types of keys, Codd‟s rules and
Relational data model
III Relational Database design: Basic System Development Life Cycle, 6
Database Design – ER to Relational, Functional dependencies,
Normalization, Normal forms based on primary keys (1NF, 2NF,
3NF)
IV SQL queries: SQL data definition, data types, specifying constraints, 6
Queries for retrieval, insertion, deletion, updation, introduction to
views.
V* Practicum: 25
Create and use the following database schema to answer the given
queries.
EMPLOYEE Schema:
Field Type NULL KEY DEFAULT
Eno Char(3) NO PRI NIL
Ename Varchar(50) NO NIL
Job_type Varchar(50) NO NIL
Manager Char(3) Yes FK NIL
Hire_date Date NO NIL
Dno Integer YES FK NIL
Commission Decimal(10,2) YES NIL
Salary Decimal(7,2) NO NIL
DEPARTMENT Schema:
Field Type NULL KEY DEFAULT
Dno Integer No PRI NULL
Dname Varchar(50) Yes NULL
Location Varchar(50) Yes New Delhi
Query List
1. Query to display Employee Name, Job, Hire Date, Employee
Number; for each employee with the Employee Number appearing
first.
2. Query to display unique Jobs from the Employee Table.
3. Query to display the Employee Name concatenated by a Job
separated by a comma.

31
4. Query to display all the data from the Employee Table. Separate
each Column by a comma and name the said column as
THE_OUTPUT.
5. Query to display the Employee Name and Salary of all the
employees earning more than $2850.
6. Query to display Employee Name and Department Number for the
Employee No= 7900.
7. Query to display Employee Name and Salary for all employees
whose salary is not in the range of $1500 and $2850.
8. Query to display Employee Name and Department No. of all the
employees in Dept 10 and Dept 30 in the alphabetical order by name.
9. Query to display Name and Hire Date of every Employee who
was hired in 1981.
10. Query to display Name and Job of all employees who don‟t have
a current Manager.
11. Query to display the Name, Salary and Commission for all the
employees who earn commission.
12. Sort the data in descending order of Salary and Commission.
13. Query to display Name of all the employees where the third letter
of their name is „A‟.
14. Query to display Name of all employees either have two „R‟s or
have two „A‟s in their name and are either in Dept No = 30 or their
Manger‟s Employee No = 7788.
15. Query to display Name, Salary and Commission for all
employees whose Commission amount is 14 greater than their Salary
increased by 5%.
16. Query to display the Current Date.
17. Query to display Name, Hire Date and Salary Review Date
which is the 1st Monday after six months of employment.
18. Query to display Name and calculate the number of months
between today and the date each employee was hired.
19. Query to display the following for each employee earns <
Salary> monthly but wants < 3 * Current Salary >. Label the Column
as Dream Salary.
20. Query to display Name with the 1st letter capitalized and all
other letter lower case and length of their name of all the employees
whose name starts with „J‟, ‟A‟ and „M‟.
21. Query to display Name, Hire Date and Day of the week on which
the employee started.
22. Query to display Name, Department Name and Department No
for all the employees.
23. Query to display Unique Listing of all Jobs that are in
Department # 30. 24. Query to display Name, Dept Name of all
employees who have an „A‟ in their name.
25. Query to display Name, Job, Department No. And Department
Name for all the employees working at the Dallas location.
26. Query to display Name and Employee no. Along with their
Manger‟s Name and the Manager‟s employee no; along with the
Employees‟ Name who do not have a Manager.
27. Query to display Name, Dept No. And Salary of any employee
whose department No. and salary matches both the department no.
And the salary of any employee who earns a commission.

32
 28. Query to display Name and Salaries represented by asterisks,
where each asterisk (*) signifies $100.
29. Query to display the Highest, Lowest, Sum and Average Salaries
of all the employees
30. Query to display the number of employees performing the same
Job type functions.
31. Query to display the no. of managers without listing their names.
32. Query to display the Department Name, Location Name, No. of
Employees and the average salary for all employees in that
department. 33. Query to display Name and Hire Date for all
employees in the same dept. as Blake.
34. Query to display the Employee No. And Name for all employees
who earn more than the average salary.
35. Query to display Employee Number and Name for all employees
who work in a department with any employee whose name contains
a „T‟.
36. Query to display the names and salaries of all employees who
report to King.
37. Query to display the department no, name and job for all
employees in the Sales department
Suggested Evaluation Methods
Internal Assessment: End Term Ex-
➢ Theory amination:
 Class Participation: 4 A three hour
 Seminar/presentation/assignment/quiz/class test etc.: 4 exam for both
 Mid-Term Exam: 7 theory and
practicum.
➢ Practicum
 Class Participation: 2
 Seminar/Demonstration/Viva-voce/Lab records etc.: 3
 Mid-Term Exam: NA

Part C-Learning Resources

*Applicable for courses having practical component.

33