Question - 1
a) Briefly explain the following terms.
i. Primary Key, Composite Key and Foreign Key
A primary key is defined as a key or database column which uniquely identifies each row in a database table.
A composite key is a set of more than one key that, together, uniquely identifies each record.
Foreign key is a column(s) of a table that points to the primary key of another table. They act as a cross-reference between tables.
ii. File Operations & Ordered File
OPEN: Readies the file for access, and associates a pointerthat will refer to a current file record at each point in time.
FIND (or LOCATE): Searches for the first file record that satisfies acertain condition, and makes it the current file record.
FINDNEXT: Searches for the next file record (from the current record) that satisfies a certain condition, and makes it the current file record.
READ (or GET): Reads the current file record into a program variable.
INSERT: Inserts a new record into the file, and makes it thecurrent file record.
RESET: Sets the file pointer of an open file to the beginning of the file.
DELETE: Deletes the current record and (eventually) updates the file on disk to reflect the deletion.
Ordered File:
- Also called a sequential file.
- File records are kept sorted by the values of an ordering field.
- Insertion is expensive: records must be inserted in the correct order.
- Reading the records in order of the ordering field is quite efficient
iii. DDL & DML
Data Definition Language, which deals with database schema and descriptions, of how the data should reside in the database.
Ex:-
CREATE - to create database and its objects like (table, index, views, store procedure, function, and triggers)
ALTER - alters the structure of the existing database
DROP - delete objects from the database
Data Manipulation Language which deals with data manipulation and includes most common SQL statements such SELECT, INSERT, UPDATE, DELETE etc,
b) Write two advantages of DBMS
- Controlling Data Redundancy
- Sharing of Data
- Data Consistency
- Integration of Data
- Integration Constraints
- Data Security
- Report Writers
- Control Over Concurrency
- Backup and Recovery Procedures
- Data Independence
Question - 2
Consider the following information:
- In an educational institute, there are several departments and students belong to one of them.
- Each department has a unique department number, a name, a location, phone number and is headed by a professor.
- Professors have a unique employee Id, name, phone number.
- We like to keep track of the following details regarding students: name, unique roll number, sex, date of birth, age and one or more email addresses.
- Students have a local address consisting of the hostel name and the room number. They also have home address consisting of house number, street, city and PIN. It is assumed that all students reside in the hostels.
- Course taught in a semester of the year is called a section. There can be several sections of the same course in a semester; these are identified by the section number.
- Each section is taught by a different professor and has its own timings and a room to meet. Students enroll for several sections in a semester. Each course has a name, number of credits and the department that offers it.
- A course may have other courses as pre-requisites i.e, courses to be completed before it can be enrolled in.
- Professors also undertake research projects. These are sponsored by funding agencies and have a specific start date, end date and amount of money given. More than one professor can be involved in a project. Also a professor may be simultaneously working on several projects. A project has a unique projectId
Draw an ER diagram for this system.
a) Define the term functional dependency.
Functional dependency describes the relationship between attributes in a relation. For example if A and B are attributes of relation R, B is functionally dependent on A (denoted A -> B) if each value of A is associated with exactly one value of B.
b) Normalize the following table to 1NF, 2NF and 3NF
2NF
CourseGrade (StudentNo, CourseNo, Grade)
CourseInstructor (CourseNo, CourseName, InstructorNo, InstructorName, InstructorLocation)
3NF
CourseGrade (StudentNo, CourseNo, Grade)
Course (CourseNo, CourseName, InstructorNo)
Instructor (InstructorNo, InstructorName, InstructorLocation)
Question - 4
Write SQL statements to perform the following.
a) Consider the following schema:
Student_Info (Student No: Integer; Name: varchar (20), Major: char(4); GPA: float)
i. Create the table for schema.
Create table Student_Info (StudentNo int(3),Name varchar(20),Major char(4),GPA double(3,2),primary key(studentNo));
ii. Insert the following information:
insert into student_info values (100,”kamal”,”COS”,3.6);
insert into student_info values (101,”Raja”,”AMA”,3.2);
III. Update Kamal's GPA to 3.7
update student_info set gpa=3.7 where studentNo=100;
IV. Add a column address (i.e. address: varchar (30) to the Student_info table).
alter table student_info add address varchar(30);
V. Change the data type of address column into varchar (50).
alter table student_info modify address varchar(50);
VI. Write the SQL statement to delete student_Info table from the Database.
drop table student_info;
b) Consider the following schemas: Write SQL statements to perform the following.
Emp_Information (Eid, name, salary, dept_no, address)
Department (Dept_no, dname, building, mgr_no)
i. Print all employee names.
select name from Emp_Information;
ii. Print names of employees working for 'Administration' (i.e. dname) department.
select e.name from Emp_Information e, Department dwhere e.dept_no=d.Dept_no and d.dname=”Administration”;
iii. Print names of employees working for 'Administration' (i.e. dname) department and getting a salary >Rs. 20,000
select e.name from Emp_Information e, Department dwhere e.dept_no=d.Dept_no and d.dname=”administration” and e.salary>20000;
Question - 5
a) Convert the following ER diagram (figure 1) to relation schema.
Student (student_Id, first_name, last_name, date_of_birth)
Subject (subject_code, title)
Register (student_Id, subject_code, date)
Lectuer (Lecturer_Id,Subject_code)
b) Write Relational algebra expressions to the followings.
I.Retrieves details of all actors above the age of 45.
Ο age>45(Actor)
II.Retrieves all distinct film titles.
π title (Film)
III.Retrieves all distinct titles of films directed by a British director.
π title (Film Ο nationality='British' (Director))
IV.Retrieves all distinct titles of films that were released before 2000.
π title (Οyear<2000(Film))
V.Retrieves details of all films released between 2000 and 2010
Ο year ≥ 2000(Film) ∩ Ο year ≤ 2010(Film)
Question - 6
a) Write two characteristics of DBMS
Self-Describing Nature of the Database System: A database system contains not only the database itself but also a complete definition or descriptions the database structure and constraints.
Data Independence : the structure of the data file is stored in the DBMS catalog separately from the access program.
Data Abstraction : DBMS provides users with conceptual representation of data that does not include many of the details of how the data is stored.
Sharing of data and multi-user transaction processing: Allowing a set of concurrent users to retrieve and update the database. Concurrency control with the DBMS guarantees that each transaction is correctly executed or completely aborted.
b) Explain three schema architecture in brief
Internal schema at the internal level to describe physical storage structures and access paths. The internal schema is also known as a physical schema. It uses the physical data model. It is used to define that how the data will be stored in a block.
Conceptual schema at the conceptual level to describe the structure and constraints for the whole database for a community of users.
The conceptual schema describes the structure of the whole database.
The conceptual level describes what data are to be stored in the database and also describes what relationship exists among those data.
In the conceptual level, internal details such as an implementation of the data structure are hidden.
External schemas at the external level to describe the various user views.
Each view schema describes the database part that a particular user group is interested and hides the remaining database from that user group.
The view schema describes the end user interaction with database systems.
c) Write the short notes for the following terms
i. Logical data independence
The logical data independence is the key feature of database management system for maintaining data integrity and for maintaining overall effectiveness of data usage.
ii. Attribute
A property or description of an entity. Eg: A department employee entity could have attributes describing the employee’s name, salary, and years of service.
iii. Constraints
Constraints enforce limits to the data or type of data that can be inserted/updated/deleted from a table. The whole purpose of constraints is to maintain the data integrity during an update/delete/insert into a table.
Ex:
NOT NULL
UNIQUE
DEFAULT
CHECK
Key Constraints – PRIMARY KEY, FOREIGN KEY
No comments:
Post a Comment