RDBMS Answer key
Easy notes for students based on KU academics
Unit :- 2
1Q: Normalisation? Explain INF, 2NF, 3NF And BCNE.
Ans :-
Normalization :-
Database normalisation is a method for organising data in a database. Normalization is a method of decomposing tables in a systematic way to eliminate data redundancy (repetition) and undesirable characteristics such as Insertion, Update, and Deletion Anomalies. It is a multi-step process that converts data into tabular format while removing duplicates from relation tables.
Normalization is primarily used for two purposes.
• Removing unnecessary (redundant) data.
• Ensure that data dependencies make sense, i.e. that data is stored logically.
Here are the most commonly used normal forms:
• First normal form(1NF)
• Second normal form(2NF)
• Third normal form(3NF)
• Boyce & Codd normal form (BCNF)
First normal form (1NF) :-
If a relation does not have any multi-valued attributes, it is said to be in 1NF (first normal form). In other words, a relation is in 1NF if each attribute contains only an atomic (single) value.
Example :- Let’s say a company wants to store the names and contact details of its employees. It creates a table in the database that looks like this:
Two employees (Jon & Lester) have two mobile numbers that caused the Emp_Mobile field to have multiple values for these two employees.
This table is not in 1NF as the rule says “each attribute of a table must have atomic (single) values”, the Emp_Mobile values for employees Jon & Lester violates that rule.
To make the table complies with 1NF we need to create separate rows for the each mobile number in such a way so that none of the attributes contains multiple values.
Second normal form (2NF) :-
A table is said to be in 2NF if both of the following conditions are met:
- Table is in 1NF (First normal form)
- No non-prime attribute is dependent on the proper subset of any candidate key of table.
An attribute that is not part of any candidate key is known as non-prime attribute.
Third Normal form (3NF) :-
A table design is said to be in 3NF if both the following conditions hold:
- Table must be in 2NF
- Transitive functional dependency of non-prime attribute on any super key should be removed.
Boyce Codd normal form (BCNF) :-
It is an advance version of 3NF that’s why it is also referred as 3.5NF. BCNF is stricter than 3NF. A table complies with BCNF if it is in 3NF and for every functional dependency X->Y, X should be the super key of the table.
For more examples
2Q: File Organisation-Explain different file organisations.
Ans :-
File Organisations and Their Types :-
File organisation refers to the logical relationships that exist among the various records that comprise the file, particularly in terms of the means of identifying and accessing any specific record. Simply storing files in a specific order is referred to as file organisation. The format of the label and data blocks, as well as any logical control record, is referred to as file structure.
Various methods for organising files have been introduced. On the basis of access or selection, these specific methods have advantages and disadvantages. As a result, it is entirely up to the programmer to determine the best file organisation method for his specific needs.
Some examples of file organisation are:
• Sequential File Organization
• Heap File Organization
• Hash File Organization
• B+ Tree File Organization
• Clustered File Organization
Sequential File Organization :-
The Sequential method is the simplest method for organising files. The files are stored one after the other in a sequential order using this method.
Heap File Organization :-
Heap File Organization operates on data blocks. This method inserts records into data blocks at the end of the file. This method does not require any sorting or ordering. If a data block is full, the new record is stored in some other block, which does not have to be the next data block, but can be any block in memory. DBMS is in charge of storing and managing the new records.
Hash File Organization :-
Hashing is an efficient technique for searching for desired data on a disc without using an index structure. Data is stored in data blocks whose addresses are generated by the hash function.
B+ Tree File Organization :-
B+ Tree, as the name implies, stores records in a tree-like structure. It employs the key indexing concept, in which the primary key is used to sort the records. An index value is generated for each primary key and mapped to the record. The address of a record in the file is represented by its index.
Cluster File Organization :-
Two or more related tables/records are stored within the same file as clusters in cluster file organisation. These files will contain two or more tables in the same data block, and the key attributes used to connect these tables will be stored only once.
3Q: Storage of database on hard disk.
Ans :-
Storage of Databases :-
Database records are saved in file formats. The data is physically stored on a device in electromagnetic format. The electromagnetic devices that are used in database systems to store data are classified as follows:
Primary Storage :- This category includes memory storage that is directly accessible to the CPU. Because they are all located on the motherboard or CPU chipset, the CPU's internal memory (registers), fast memory (cache), and main memory (RAM) are all directly accessible to the CPU. This type of storage is typically very small, extremely fast, and volatile. Primary storage requires a constant power supply to maintain its state. In the event of a power outage, all data is lost.
Secondary Storage :- These Devices are used to store data for future use or as a backup. Memory devices that are not part of the CPU chipset or motherboard, such as magnetic discs, optical discs (DVD, CD, etc.), hard discs, flash drives, and magnetic tapes, are examples of secondary storage.
Tertiary Storage :- Tertiary storage is used to store extremely large amounts of data. Because such storage devices are separate from the computer system, they are the slowest. These storage devices are typically used to backup an entire system. Tertiary storage is commonly provided by optical discs and magnetic tapes.
4Q: Relational database integrity-Types of keys in DBMS.
Ans :-
Relational Database Integrity:-
Integrity in Relationships Constraints are a set of rules that can be used to maintain data integrity when inserting, deleting, or updating data in a table. These constraints are checked in the database before any operation on the tables, such as insertion, deletion, or updating.
Keys :- Keys are very important in relational databases.It is used to distinguish any record or row of data in the table. It is also used to establish and identify table relationships.
Types of keys :-
1. Primary key :-
It is the first key used to uniquely identify one and only one instance of an entity. An entity can have multiple keys. The most appropriate key from those lists becomes the primary key.
2. Candidate key :-
A candidate key is a feature or set of features that can be used to uniquely identify a tuple.
The remaining attributes, with the exception of the primary key, are considered candidate keys.
3. Super Key :-
A super key is a set of attributes that can be used to uniquely identify a tuple. A super key is a candidate key's superset.
4. Foreign key :-
Foreign keys are the column of the table used to point to the primary key of another table.
5. Alternate key :-
Each tuple in a relation may have one or more attributes, or a combination of attributes, that uniquely identify it. The candidate keys are these attributes or combinations of attributes.In other words, the total number of the alternate keys is the total number of candidate keys minus the primary key.
6. Composite key :-
Whenever a primary key consists of more than one attribute, it is known as a composite key. This key is also known as Concatenated Key.
7. Artificial key :-
Artificial keys are those that are generated using arbitrarily assigned data. These keys are created when a primary key is large and complex and does not have many other relationships. The data values of artificial keys are typically numbered sequentially.
If you have any questions or need any book pdf please make a message in contact form at the bottom, we will try to send you pdf text books or solutions for your questions.
No comments:
Post a Comment