MCS 023 2016-2017 session

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.

m o .c

t o p

s g lo

All the possible applications of a Database Management System in any IGNOU'S Regional Center :(i)

b . te

Database is used to store students’ details in a place

i s u

We know Database is used for storing Data and Information.In IGNOU'S Regional Center all

o n ig

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.

. w w

(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.

w

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)

m o .c

t o p

Easy to implement Security and integrity of data

s g lo

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)

b . te

i s u

Fast access of information

o n ig

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.

. w w

(ix) Data sharing in other department of IGNOU.

w

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.

m o .c

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.

t o p

s g lo

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.

b . te

i s u

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.

o n ig

. w w

Cardinality

w

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 en entity set A can be associated with more than han one entities of entity set B however an entity tity from entity set B, can be associated with at most one on entity.

m o .c

t o p

b . te

i s u

o n ig

. w w

s g lo

Many-to-one − More than one ne entit entities from entity set A can be associated with at most m one entity of entity set B, however ver an e entity from entity set B can be associated d with more m than one entity from entity set A.

w

Many-to-many − One entity y from A can be associated with more than one entity from fr B and vice versa.

m o .c

t o p

i s u

b . te

o n ig

. w w

w

Entity Relationship Diagram:-

s g lo

m o .c

t o p

i s u

b . te

o n ig

w

. w w

s g lo

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,

m o .c

Eliminating reduntant(useless) data.

t o p

Ensuring data dependencies make sense i.e data is logically stored.

Problem Without Normalization

s g lo

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

401

Adam

si

b . te

S_Address

u o n

Subject_opted

Noida

Bio

Alex

Panipat

Maths

403

Stuart

Jammu

Maths

404

Adam

Noida

Physics

402

ww

g i . w

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.

m o .c

t o p

i s u

b . te

o n ig

w

. w w

s g lo

m o .c

t o p

i s u

b . te

o n ig

w

. w w

s g lo

m o .c

t o p

i s u

b . te

o n ig

w

. w w

s g lo

m o .c

t o p

s g lo

i s u

b . te

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)

o n ig

. w w

A.4.

w

(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".

m o .c

(iii) Find the names of all those students who have got all the books issued to him of the author named “ABC” .

t o p

s g lo

(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”

i s u

b . te

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”.

o n ig

. w w

w

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'

m o .c

t o p

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".

s g lo

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.

i s u

b . te

o n ig

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.

. w w

A.5. Decomposition:-

w

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

m o .c

t o p

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.

b . te

New Student_Detail Table :

si

Student_id

Student_name

u o n

s g lo DOB

Zip

Boyce and Codd Normal Form (BCNF)

g i . w

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:

ww

R must be in 3rd Normal Form and, for each functional dependency ( X -> Y ), X should be a super Key.

m o .c

t o p

i s u

b . te

o n ig

w

. w w

s g lo

m o .c

t o p

i s u

b . te

o n ig

w

. w w

s g lo

MCS 023.pdf

... system enforces consistency and. accurate. Whoops! There was a problem loading this page. Whoops! There was a problem loading this page. MCS 023.pdf.

1MB Sizes 3 Downloads 477 Views

Recommend Documents

I mcs-041 I
MCS-041 : OPERATING SYSTEMS. Time : 3 hours ... (a) Discuss the linked and index file allocation schemes. Which allocation scheme is used in UNIX OS? 10.

mcs steel - Settrade
Sep 28, 2017 - CEO คนใหม่ คือคนเก่าที่เข้าใจธุรกิจ. เราได้มีโอกาสเข้าพบ ดร. สมพงษ์ เมธาสถิตย์สุข ประธà

2) MCS-042
COMPUTER NETWORKS. Time : 3 hours ... (a) Consider the following network with the indicated link cost. ... Explain with the help of a diagram. 10. (b) Discuss ...

I mcs-041 I
MCS-041 : OPERATING SYSTEMS. Time : 3 hours ... following types of operating systems : (i). Batch. (ii) ... ordering in a distributed system with an example. 6.

MCS pub version.pdf
Twitter is essentially cut off from the Chinese internet, accessible only by the virtual. private network (VPN) software that a small number of dedicated netizens ...

mcs-12.pdf
base of number system is 'r' then complements will be r's complement and (r-1)'s. complement. Example: 5 is written in 8 bit register as binary: 00000101 -5 ...

MCS-015 notes.pdf
Attended by a host of national and international delegates. • Display of new technologies from many countries. Page 3 of 4. MCS-015 notes.pdf. MCS-015 notes.

MCS 015.pdf
Page 1 of 17. www.ignousite.blogspot.com. MCS 015 2016-2017 session Ignou Study Helper. www.ignousite.blogspot.com. Q.1. A.1. (a)Why is India uniquely ...

MCS-012.pdf
b) Explain the basic characteristics of RAID Level 3 and 4 disks.(5). c) Give block diagram of DMA controller. How does CPU initialize the DMA. transfer? d) What ...

stereo mcs connected.pdf
Acid jazz ÑÐoачать. Ð1⁄2Ð3⁄4Ð2Ð ̧Ð1⁄2ÐoÐ ̧, mp3, Ð1⁄4узыÐoу, lossless, vinyl. Melodiesand memories on pinterest foo fighters, songsand ...

MCS-031 notes.pdf
(a) (i) Explain Dijkstra's shortest path algorithm. (ii) Find the shortest path in the following graph represented by adjacency matrix,. from vertex 'a'. (b) (i) Explain the principle of greedy algorithm. (ii) Explain Prim's algorithm for Minimum Spa

MCS-012.pdf
Page 3 of 30. UTF-16 Code : oorl5. 00 zo. o0 20. C':EI! ooa2. oo. oon3. aan3. oo6D 006l. aact ac64. 006l oo6c. OO6C. (!6 5 @a3. oo6J cD?g. oc65 ()0?3. (ro 20.

MCS New VoiceMail Reference Card.pdf
Mitel's NuPoint Messenger Flow. From Outside. Enter destination mailbox,. & record introduction ... Follow tutorial to create new passcode, record name, and ...

sunilpoonia006.blogspot.com MCS-022
was made on 27 October 2008, causing problems to the users of the Free edition because of its large size and .... Once the network drive is set up, Windows and its programs behave as if the networked directory were a local disk. ..... Backup media in

MCS Employee Handbook 2013 Final.pdf
Jul 1, 2013 - Page 2 of 32. MCS Employee Handbook. 2. About this Handbook. The Mitchell County Schools' Employee Handbook has been prepared for all ...

MCS-40 Prototype A.pdf
There was a problem previewing this document. Retrying... Download. Connect more apps... Try one of the apps below to open or edit this item. MCS-40 ...

MCS-40 Prototype B.pdf
There was a problem previewing this document. Retrying... Download. Connect more apps... Try one of the apps below to open or edit this item. MCS-40 Prototype B.pdf. MCS-40 Prototype B.pdf. Open. Extract. Open with. Sign In. Main menu. Whoops! There

MCS Question bank- group 10 -
the performance of different units or departments of a business. .... provides a guiding orientation for a broad range of Apple's business decisions such as the ...

Descargar musica de mcs
... tomel gato paraiphone.picnik editor defotos online descargar.156041149287 ... pdf.descargar libros oficiales thermomix gratis.descargar macros paraexcel ...

MCS-48 Microcomputer User's Manual.pdf
There was a problem previewing this document. Retrying... Download. Connect more apps... Try one of the apps below to open or edit this item. MCS-48 ...

2015 SFX vx MCS gender.pdf
Download. Connect more apps... Try one of the apps below to open or edit this item. 2015 SFX vx MCS gender.pdf. 2015 SFX vx MCS gender.pdf. Open. Extract.

Intel MCS-96.pdf
Page 1 of 1. Page 1 of 1. Intel MCS-96.pdf. Intel MCS-96.pdf. Open. Extract. Open with. Sign In. Main menu. Displaying Intel MCS-96.pdf.

MCS-51 Microcontroller Users Manual.pdf
There was a problem previewing this document. Retrying... Download. Connect more apps... Try one of the apps below to open or edit this item. MCS-51 ...

MCS New VoiceMail Reference Card.pdf
know when a message has been left for you in your mailbox. Page 2 of 2. MCS New VoiceMail Reference Card.pdf. MCS New VoiceMail Reference Card.pdf.