B.Com ( Tax Procedures & Practices) III Year
SYLLABUS
DATABASE MANAGEMENT SYSTEM
(This paper is offered in lieu of Business Communication offered to
B.Com.(General))
Paper : Max.Marks : 70T+30P=100
P.P.W :4
UNIT I
and Foreign Key.
: Data and information – Limitations of Manual Data Processing – Advantages of
databases- DBMS- Functions of DBA- Elements pf DBMS : DDL,DML, Entities,
Sets and attributes. Data Base Tables : Keys- Primary, Secondary, Composite
UNIT
II : Relations Data Bases Entity Relationship Types – 1:1, M:N, strong and Weak
entities, Recursive Data Base Design, Normalization, First, Second, Third,
BCNF, Fourth, Class Diagrams and Entity Relation tables.
UNIT III : Creating Tables , Modifying Table Structures – Data Entity – Edit – Delete
Importing – Exporting table using MS Access.
UNIT IV : Queries : QBE –Select Queries – Grouping – Parameters – Data Formatting,
queries based on multiple sources – Cross Tab Queries – Action Queries –
Make Table Queries – Append – Delete and Update Queries using MS
Access.
UNIT V : Forms and Reports : Forms – functions and uses – creating, Modifying labels
List Boxes, Dialog Boxes. Reports: Creating – Modifying reports – Creating
Reports with Report Graphics – Label Output format – Form Letters. (Using
MS-Access)
PRESCRIBED TEXT BOOK:
1. Peter Rob, Elie Semaan Databases Design, Development, & Deployment using
Microsoft Access, TATA MCGRAW – HILL EDITION
REFERENCE BOOK:
1. Fred R. McFadden, Modern Database Management, Addison Wesley B.COM III YEAR PRACTICAL EXAMINATION
(TAX PROCEDURES & PRACTICES)
DATABASE MANAGEMENT SYSTEM
1. Create a table named STUDENT with the following structure and set the validation
rules white creating the structure:
FieldName DataType Validation Rules/Constraints
Student# AutoNumber Primary Key
Name Text(20)
Qualification Text(20) MCA or MSC(CS)orBEorME
Date of Birth Date Between 1
st
January 1980 and 31
st
March
1985
Enter data in the above table and do the following:
a) Display the names of students whose names begin with ‘S’.
b) Display the details of students whose qualification is ‘MCA’.
c) Display the details of students whose age is greater than 24.
2. Create an EMPLOYEE table with the following details:
Employee# Name Basic DA HRA GrossPay
Assume all constraints, enter the data and calculate DA,HRA and Gross Pay.
3. Create a table called StudentDetails by importing the details of students from
MS-EXCEL Worksheet.
4. Create the CATALOG table with the following fields:
CATALOG
ISBN Title Author
Name
Publisher Year Price
Create queries as per the following specifications;
a) Get the details of all the books whose price is between 1000 and 1500.
b) Get the details of all the books whose price is between 1000 and 1500 in the
descending order of the prices.
c) Get the details of all the books whose stock level is zero.
5. Create a table named PERSON with Name and Age as attributes. Enter the data for
10 rows.
a) Modify the structure of the table to add a new column “Address”.
b) Make the name and address as the Primary Key for this table.
6. Create two tables EMPLOYEE and DEPARTMENT with the following details:
EMPLOYEE
DEPARTMENT
DeptNo Name Location
a) Make Employee # and DeptNo the primary keys for EMPOLYEE and
DEPARTMENT tables respectively.
b) Make DeptNo of Employees tables, the foreign key for the DEPARTMENT tables.
c) Display the details of Employees, Whose salary is greater than rs.5000.
d) Display the names of places, where different employees are working.
Employee# Name Job HireDate Salary DeptNo 7. Create the CATALOG table with the following fields:
CATALOG
Create queries as per the following specifications:
a) Get the details of all the books whose year of publishing is 2005.
b) Get the details of all the books whose year of publishing is 2002 or 2005.
c) Get the details of all the books whose year of publishing is between 2000 and
2005.
8. Create the table EMPLOYEE with the following details:
EMPLOYEE
Enter details of 10 employees and create queries as per the following specifications:
a) Modifying the Job of all employees to ’MANAGER’.
b) Enhance the salary of all employees by 10%.
c) Delete the details of all employees, Whose salary is less then Rs.5000/-
9. Illustrate the use of Append Query.
10. Consider the following tables:
EMPLOYEES
ISBN Title Author
Name
Publisher Year Price
Employee# Name Job Hire Date Salary DeptNo
Employee# Name Job Hire Date Salary DeptNo
DEPARTMENT
Enter relevant data and Create queries as per the following specification:
a) Display name and location of the department, where ALLEN is working.
b) Display number and name of all the employees, who earn more than 2600 in
alphabetical order by name?
c) Write a query that will accept a given job title and displays all rows according to
that title?
11. Consider the following tables:
EMPLOYEES
DEPARTMENT
a) Write a query to duplicate the EMPLOYEE table?
b) Calculate the department number where more then one clerk is working?
c) Find the department number where more than one clerk is working?
DeptNo Name Location
Employee# Name Job Hire Date Salary DeptNo
DeptNo Name Location
12. Create a database named Recruitment, with the tables Application, Interview and
Recruitment .The structure of the tables is given below:
Application
FieldName Data Type Validation Rules/Constraints
Cand-Id AutoNumber Primary Key
Name Text(20)
Qualification Text(20) MCA or Msc(cs)or BE or ME
Appl-Date Date Between May 15* to June 30
th
2005
Date of Birth Date Between 1
st
January 1980 And 31
st
March 1985
Percentage Number Between 70 and 100
Interview
FieldName Data Type Validation Rules/Constraints
Intv-id AutoNumber
Cand-id AutoNumber Foreign key to Cand-Id of
Application table
Intv-Marks Number Between 1 and 100
Recruitment
FieldName Data Type Validation Rules/Constraints
Recruit_Id Text (7)
Intv_id Auto Number
Join_Date Date >Current Date +1 month AND <
Current Date +2 month
Post_recruited Text
Set the validation rules, while creating the table and enter data into it.
13. Using the tables from Question 12, perform the following operations:
a) Create a simple query named Interview List to select only the cand_Id from the
Applications table with the candidates having above 70% and with the qualifica
-tion as MCA or Msc (cs).
b) From the Interview Table, select only the Intv_Id having Intv_Marks of more than
70, who are to be recruited for posts.
c) Create a look up field for Post _Recruited having the values Software Engineer,
System Engineer, Support Incharge, Senior Manager.
14. Using the tables from question 12, Build a form with the details of candidates
selected for Interview. The details are to be automatically update to the Interview
table.
15. Using the tables from Question 12,build a form with the details of candidates who
have been recruit to various posts, using the information from the Recruitment table.
16. Create the CATALOG table with the following fields:
CATALOG
Create queries as per the following specifications:
a) Find the name of all the books that start with the letter ‘C’.
b) Find the details of the books with the title “TechnoTrends”.
ISBN Title Author
Name
Publisher Year Price 17. Illustrate the use of Make-tables Query.
18. Create the CATALOG table with the following fields:
CATALOG
Create queries as per the following specifications:
a) Find the price of the book “Rapid Application Development:.
b) Change the price of the book “software Engineering” to Rs. 500/-.
c) Delete the record for the book named “The Internet Applications”.
19. Generate a Report based on the table Stud_Details, Marks_Details, Group_Mast_
Details, and place it in the Students Report, using the Report Wizard.
STUD_DETAILS
GROUP_MAST_DETAILS
Marks_Details
ISBN Title Author
Name
Publisher Year Price
Stud_Id Name DOB Address Group_Id
GroupId Group_Name
Stud_Id PCS BE MM OA TOTAL
No comments:
Post a Comment