Q.1.List and describe briefly all the possible applications of a database management system in any IGNOU’s Regional Centre?
A.1. DataBase Management System
A Database management system is a computerized record-keeping system. It is a repository or a container for collection of computerized data files. The overall purpose of DBMS is to allow he users to define, store, retrieve and update the information contained in the database on demand. Information can be anything that is of significance to an individual or organization.
All the possible applications of a Database Management System in any IGNOU'S Regional Center :-
(i) Database is used to store students’ details in a place
We know Database is used for storing Data and Information.In IGNOU'S Regional Center all data and Information related to student , staff and employees are stored from where we can access data and Information , update and also Delete the Data and Information.
(ii) Easy Query processing and management
From DBMS we can easily generate Query and we can easily manage it.And we can easily access Query in our database.
For Ex:SELECT * FROM Students;
where SELECT is a Query Statement ,
(*) All information can access through *(astrik sign)
And, student is tha name of Table.
(iii) Easy to understand and user friendly
The language we use in DBMS is easily understandable. Query Statements like SELECT, DELETE & UPDATE is very easy for users.Because we use a simple english words or Statements.
(iv) Store Students marks evaluation details
We easily get our Evaluatiom marks details that is stored in DBMS of Regional Center applications that is easily available for Enrolled Students.
(v) Store Employee details.
If we want to Know the details about the any of the Employee then we can easily get from the DBMS that is already stored in DBMS .To know which Employee is responsible for the which work that is assigned by Administrator.
(vi) Store final year project Submission.
In IGNOU programmed application the students have to create Project on any topics and the project is Submitted by Students in Regional Center.If student want to see the details of submitted project , they get from there.
(vii) Easy to implement Security and integrity of data
DBMS has four methods to maintain data integrity: normalizing data, defining business rules, providing referential integrity and data validation. Data normalization is a process where unnecessary dependencies and repeating groups of data are eliminated. Business rules for data access control data handling and sharing between applications. Referential integrity is used to protect data from corruption. Data validation uses multiple processes to validate data in the application.
(viii) Fast access of information
In DBMS, all data of an organization is integrated into a single database file. The data is recorded in only one place in the database and it is not duplicated.Thorough which we can access of Information fast.
(ix) Data sharing in other department of IGNOU.
In DBMS, data can be shared by authorized users of the organization. The database administrator manages the data and gives rights to users to access the data. Many users can be authorized to access the same piece of information simultaneously. The remote users can also share same data. Similarly, the data of same database can be shared between different application programs.
(x) Better data accuracy
By controlling the data redundancy, the data consistency is obtained. If a data item appears only once, any update to its value has to be performed only once and the updated value is immediately available to all users. If the DBMS has controlled redundancy, the database system enforces consistency and accurate.
Q.2.Identify all the associated entities for a Regional Centre Management System, their corresponding attributes, relationships and cardinality and design an Entity-Relationship (ER) diagram for it.
A.2.
Entity:- An entity can be a real-world object, either animate or inanimate, that can be easily identifiable. For example, in a school database, students, teachers, classes, and courses offered can be considered as entities. All these entities have some attributes or properties that give them their identity.
An entity set is a collection of similar types of entities. An entity set may contain entities with attribute sharing similar values. For example, a Students set may contain all the students of a school; likewise a Teachers set may contain all the teachers of a school from all faculties. Entity sets need not be disjoint.
Attributes
Entities are represented by means of their properties, called attributes. All attributes have values. For example, a student entity may have name, class, and age as attributes.
There exists a domain or range of values that can be assigned to attributes. For example, a student's name cannot be a numeric value. It has to be alphabetic. A student's age cannot be negative, etc.
Relationship
The association among entities is called a relationship. For example, an employee works_at a department, a student enrolls in a course. Here, Works_at and Enrolls are called relationships.
Cardinality
Defines the number of entities in one entity set, which can be associated with the number of entities of other set via relationship set.
- One-to-one − One entity from entity set A can be associated with at most one entity of entity set B and vice versa.
- One-to-many − One entity from entity set A can be associated with more than one entities of entity set B however an entity from entity set B, can be associated with at most one entity.
- Many-to-one − More than one entities from entity set A can be associated with at most one entity of entity set B, however an entity from entity set B can be associated with more than one entity from entity set A.
- Many-to-many − One entity from A can be associated with more than one entity from B and vice versa.
Entity Relationship Diagram:-
Q.3.Consider the E-R diagram of Question 2 and design the tables. Perform and show the Normalization till the required normal form. Implement the database using MS-Access and submit the screenshots along with your assignment response for this question.
A.3.
NORMALIZATION:-
Database Normalisation is a technique of organizing the data in the database. Normalization is a systematic approach of decomposing tables to eliminate data redundancy and undesirable characteristics like Insertion, Update and Deletion Anamolies. It is a multi-step process that puts data into tabular form by removing duplicated data from the relation tables.
Normalization is used for mainly two purpose,
- Eliminating reduntant(useless) data.
- Ensuring data dependencies make sense i.e data is logically stored.
Problem Without Normalization
Without Normalization, it becomes difficult to handle and update the database, without facing data loss. Insertion, Updation and Deletion Anamolies are very frequent if Database is not Normalized. To understand these anomalies let us take an example of Student table.
| S_id | S_Name | S_Address | Subject_opted |
|---|---|---|---|
| 401 | Adam | Noida | Bio |
| 402 | Alex | Panipat | Maths |
| 403 | Stuart | Jammu | Maths |
| 404 | Adam | Noida | Physics |
- Updation Anamoly : To update address of a student who occurs twice or more than twice in a table, we will have to update S_Address column in all the rows, else data will become inconsistent.
- Insertion Anamoly : Suppose for a new admission, we have a Student id(S_id), name and address of a student but if student has not opted for any subjects yet then we have to insert NULL there, leading to Insertion Anamoly.
- Deletion Anamoly : If (S_id) 401 has only one subject and temporarily he drops it, when we delete that row, entire student record will be deleted along with it.
Q.4.Consider a “Library Management System” which has the following tables: Book(isbn_no, book_title, author1, author2, author3, publisher, edition, year_of_copyright, cost) Book_Accession(accession_no, isbn_no, date_of_purchase) Member(m_id, m_name, m_address, m_phone) Issue_return (accession_no, m_id, expected_date_of_return, actual_date_of_return)
A.4.
(i) Find the m_id and m_name of the members who have got maximum number of un_returned books.
(i) Select m_id,m_name from members, from issue_return where member.mid=issue_return.mid and issue_return.actual_date_of_return=” ”;
Expalnation:- Select is Query Statement from where we can select all dataaor selelcted data from the table. Here the tables are Members from where we can access m_id and m_name attributes , and the table issue_return from where we can access actual return date.
member.mid=issue_return.mid, this is the way of accessing information from one(Member table) to another (issue_return)table.
(ii) List the book details whose year_of_copyright is 2014.
(ii) Select * from book where year_of_copyright=2014;
Expalnation:- Select is Query Statement from where we can select all data or selected data from the table. It is simply access all data or information from the table "book".
(iii) Find the names of all those students who have got all the books issued to him of the author named “ABC” .
(iii) Select isbn_no, book_title, author1, accession_no, isbn_no, m_id, expected_date_of_return from Book, Book_Accession, Issue_return where book.isbn_no=book_accession and book_accession.mid=issue_return.mid and book.auther1=”ABC”
Expalnation:- Select is Query Statement from where we can select all data or selected data from the table. Here the tables are Book from where we can access isbn_no, book_title, author1, accession_no, isbn_no, m_id, expected_date_of_return attributes , and the table issue_return from where we can access book.isbn_no=book_accession and book_accession.mid=issue_return.mid and book.auther1=”ABC”.
book.isbn_no=book_accession, this is the way of accessing information from one(Book table) to another (issue_return)table.
(iv) Find the books whose cost is less than Rs.500/- and date_of_purchase is 2014.
(iv) Select isbn_no, book_title, accession_no, isbn_no, date_of_purchase from book, Book_Accession where book.isbn_no= Book_Accession.isbn_no and date_of_purchase=2014
Expalnation:- Select is Query Statement from where we can select all data or selected data from the table. Here the tables are Book from where we can access isbn_no, book_title, accession_no, isbn_no, date_of_purchase attributes , and the table Book_Accession from where we can access book.isbn_no= Book_Accession.isbn_no and date_of_purchase=2014.
book.isbn_no= Book_Accession.isbn_no and date_of_purchase=2014, this is the way of accessing information from one(Member table) to another (issue_return)table.
(v) Find those members who have not got any book issued to him/her during last six months.
(v) Select * from Members , Accession where book.isbn_no= Book_Accession.isbn_no and date_of_purchase='6'
Expalnation:-
Select is Query Statement from where we can select all data or selected data from the table. It is simply access all data or information from the table "Members".
ok.isbn_no= Book_Accession.isbn_no and date_of_purchase=2014, this is the way of accessing information from one(Member table) to another (issue_return)table.
Q.5.Consider the Relation R={A, B, C, D, E, F, G, H } and the set of functional dependencies.
A→C B→CG AD→EH C→DF A→H
What is the key for R? Decompose R into 2NF, 3NF and finally in BCNF relation.
A.5. Decomposition:-
Functional decomposition corresponds to the various functional relationships as how the original complex business function was developed. It mainly focusses on how the overall functionality is developed and its interaction between various components.
Large or complex functionalities are more easily understood when broken down into pieces using functional decomposition.
Second Normal Form (2NF)
As per the Second Normal Form there must not be any partial dependency of any column on primary key. It means that for a table that has concatenated primary key, each column in the table that is not part of the primary key must depend upon the entire concatenated key for its existence. If any column depends only on one part of the concatenated key, then the table fails Second normal form.
In example of First Normal Form there are two rows for Adam, to include multiple subjects that he has opted for. While this is searchable, and follows First normal form, it is an inefficient use of space. Also in the above Table in First Normal Form, while the candidate key is {Student, Subject}, Age of Student only depends on Student column, which is incorrect as per Second Normal Form. To achieve second normal form, it would be helpful to split out the subjects into an independent table, and match them up using the student names as foreign keys.
Third Normal Form (3NF)
Third Normal form applies that every non-prime attribute of table must be dependent on primary key, or we can say that, there should not be the case that a non-prime attribute is determined by another non-prime attribute. So this transitive functional dependency should be removed from the table and also the table must be in Second Normal form. For example, consider a table with following fields.
Student_Detail Table :
| Student_id | Student_name | DOB | Street | city | State | Zip |
|---|
In this table Student_id is Primary key, but street, city and state depends upon Zip. The dependency between zip and other fields is called transitive dependency. Hence to apply 3NF, we need to move the street, city and state to new table, with Zip as primary key.
New Student_Detail Table :
| Student_id | Student_name | DOB | Zip |
|---|
Boyce and Codd Normal Form (BCNF)
Boyce and Codd Normal Form is a higher version of the Third Normal form. This form deals with certain type of anamoly that is not handled by 3NF. A 3NF table which does not have multiple overlapping candidate keys is said to be in BCNF. For a table to be in BCNF, following conditions must be satisfied:
- R must be in 3rd Normal Form
- and, for each functional dependency ( X -> Y ), X should be a super Key.
outstanding
ReplyDelete