Classroom Lecture Notes MCQs With Answers Questions Answers
1.1 Introduction : Data, Information, Database and DBMS
Information is the backbone of any organization. It is the most critical resource of the organization. Different methods and techniques have been adopted to keep up-to-date information. Before the invention of the computer, people used to maintain database using different devices and used to keep records in the paper. With the development of computer system, methods of processing, techniques of keeping data and information have been completely changed. Computer has replaced millions of files and folders. It can store a large amount of data in a small place of a disk. Let us discuss some important terms of database.
Data: Data is raw facts which are composed of alphabets, digits and other symbols. It may or may not give any sense For example: 1, Rubina, 5.4, 2, Rasbina etc.
Information: When data are processed using a database program or software, they are converted to the meaningful result, called information. In other words, the output of data processing is called information. It is an organized collection of related data, which gives a complete sense. Example: Information about weight of 3 students.
Database: Database is a collection of interrelated data of objects or entities stored in tabular form Database gives very useful information for an organization during data manipulation and decision making. It provides a base or foundation for managing large volume of data in well organized manner. For example: Phone Diary, Result Sheet, Customer Records and Price List etc.
Database Management System (DBMS)
A database management system is computerized records keeping system. It is a software that defines, manipulates and manages the database. It allows to access the files, update the records and retrieve data as requested. In other words, DBMS is defined as the collection of interrelated data and set of programs to access those data.
DBMS is defined os the collection of interrelated data and set of programs to access those data. The collection of data is usually database which contains the Information about any particular organization and set of programs is special type language to manage those database.
The primary goal of a DBMS is to provide an efficient and effective environment for both data retrieval and storing data in database. Commercially database management system is called database packages or also called database engine, Some examples are FoxPro, DBase, Sybase, MS Access, My SQL, MS SQL Server, Oracle and DB2 etc.
The above figure shows the logical structure of database management system. Users may be persons or application from external environment that supplies various requests to the system. Database requests mean the various activities from users. The database system checks for the requests and performs various activities as per the need of users.
Some major database activities are:
Adding new file to the database
Inserting data into the database file
Retrieving/Viewing data from the database
Updating data in existing database file
Deleting data from the database file
Removing files from the database
Database is a collection of interrelated data of entities or objects which is stored in a computer in such a way that it can be easily accessed by user.
Assignment-1
1. Define database.
2. Compare data and database.
1.2. Field, Record, Objects, Primary Key, Alternate key, Candidate key
Field: A field is the property or attributes of a table. Examples: SN, Name, Weight are fields.
Record: A record is the collection of interrelated fields. Example: there are three records in above table.
Table: A table is the arrangements of rows and columns. Each table must have unique name and must be simple. A row defines a record and a column defines a field in a table
Objects: Objects are used in database to store or reference data.So, objects holds data for manipulation and display it in the proper format. Main components of DBMS are field, record and table. Example of components of objects are:
Primary Key: The field or fields that contain the unique value can be set as primary key. It does not permit duplicate or null values. For example, the registration number of a student is a primary key.
Here, Reg_Number is the primary key.
Alternate Key: Alternate is not a primary key but combining one or more columns it make unique. So, alternate key associated key with one or more columns whose values uniquely identify every row in the table. Each alternate key can generate a unique index or a unique constraint in a target database. For example, where the primary key for a table may be the student id, the alternate key might combine the first, middle, and last names of the student.
Candidate Key: Occasionally we may encounter a relation in which there s more than one attributes processing the unique identification property. So, all attribute combinations inside a relation that can provide the unique record in a relation are called candidate keys.
1.3 Advantages and Disadvantages of DBMS
Advantages of DBMSSharing Data: Data stored in a database can be shared. It refers to the capacity that makes data simultaneously accessible by many users without any interference.
Reduced data redundancy: The same data may be duplicated at many times or places, is called data redundancy. DBMS reduces such type of duplication of data from database.
Data backup and recovery: DBMS provides backup facilities to store data for future use. If any files or data lost in any computer, it is possible to restore them from database server.
Inconsistency avoided: When the same data is duplicated and changes are made at one site, and mot on other sites, it causes data inconsistency. DBMS avoided such type of data inconsistency.
Data integrity: Data integrity means data accuracy, consistency and up to date. A DBMS should provide capabilities for defining and enforcing constraints for data integrity.
Data Security: In database system, an unauthorized person cannot access data from database. Although various departments may share data in database, access to specific information that can be limited to selected users.
Data independence: Description of the data(Schema) is stored in one central place. Therefore, applications do not have to recompile when the format of the data changes.
Multiple user interfaces: DBMS provides variety of interfaces for various users. It provides query language interface, forms and command interfaces so that users interact easily with the database.
Process complex query: It provides different methods such as view, trigger, index etc. to process complex queries.
Disadvantages of DBMSExpensive: Database software is very expensive for large computer systems. It also requires overhead costs for maintaining and integrity functions.
Changing technology: It is fast changing technology.
Needs Technical Training: It is complex to understand and implement. So, proper training is required for staff to work properly in the database system.
Backup is needed: It needs to explicit backup. This adds costs as new storage space are needed to hold the data.
Assignment-2
1. Describe primary key, alternate key and candidate key with examples.
2. Write advantages of DBMS.
1.4 DDL(Data Definition Language) and DML(Data Manipulation Language)
SQL stands for Structured Query Language. t is an international standard database query language for accessing and managing data in the databane. SQL was introduced and developed by IBM in early 1970s. IBM was able to demonstrate SQL which could be used for communicating with database. It provides platform which allows thr user to query a database without getting depth knowledge of the design of the underlying tables SOL has statements for data definition, data manipulation and data control. A query is a request to the DBMS for the retrieval, modification, insertion and deletion of the data from the database.
SQL (Structured Query Language) Pronounced an "See"-"Quell" is made of three sub languages DDL, DML and DCL.
Basic Struchure of SQL
To create a table
CREATE table table name (field1 data_type, field2 data type...
For example:
CREATE table Student (Sid NUMBER (4), Name CHAR (25))
To insert records into a table Student
INSERT into table name VALUES (List of values):
For example:
INSERT into Student VALUES (1, 'Alex');
To display records from a table
SELECT field1, field2 FROM tables name
For example:
SELECT SId, Name form Student
1. DDL (Data Definition Language): DDL is used by the database designers and programmers to specify the content and structure of the table. It is used to define the physical characteristics of records. It includes commands that manipulate the structure of objects such as Views, Tables, and Indexes etc. Examples of these Commands are Create, Alter, Drop, Rename etc.
To create a table
CREATE table table name (field1 data type, field2 data_type. .. );
For example:
CREATE table Student (Sid NUMBER (4), Name CHAR (25);
2. DML (Data Manipulation Language): DML is related with manipulation of records such as retrieval, sorting, display and deletion of records or data. It helps user to use query and display reports of the table. So, it provides techniques for processing the database. It includes commands to manipulate the information stored in the databases. Examples of these Commands are Insert, Delete, Select and Update etc.
To insert records into a table Student
INSERT into table name VALUES (List of values);
For example:
INSERT into Student VALUES (1, 'Alex');
Assignment-3
1. Differentiate between DDL and DML.
1.5 Database Model: Network Model, Hierarchical Model, Relational database Model
There are different forms of database management system, each characterized by the way where data are defined and structured, called database model. It is the organizing principles of records in secondary storage. Following are the different architectures:
- Hierarchical Database Model
- Network Database Model
- Relational Database Model
Hierarchical Database Model
Hierarchical database model is one of the oldest type database models., In this model data is represented in the form of records. Each record has multiple fields/attributes. All records are arranged in database as tree like structure. The relationship between the records is called parent child relationship in which any child record relates to only a single parent type record. In the figure below, there are parent records at the top level with high privileges and child records at bottom Jevel. There are three types of records: students, management and department chief records.
Advantages of Hierarchical Database Model
- It is the easiest model.
- It has one or more attributes.
- The searching is fast and easy, if parent is known.
- It supports one-to-one and one-to-many relationship.
Disadvantages of Hierarchical Database Model
- It is old fashioned, outdated database model.
- It does not support many-to-many relationship.
- The dependency on parent node is not beneficial always.
- It increases redundancy because same data is to be repeated in different places.
Assignment 4
1. Draw a figure to show example of hierarchical database model.
Network Database Model
The network database model replaced hierarchical database model due to some limitations on the model. Suppose if an employee relates to two departments, then the hierarchical database model cannot able to arrange records in proper place. So network database model was emerged to arranged non- hierarchical database. The structure of database is more like graph rather than tree structure. A network database model consists of collection of records which are inter-related to each other with the help of relationship. Each record has multiple fields and each field has only one data value. In figure below, exam records relate to management and students, and similarly employee records relate to department chief as well as management. In this model, each record in the database can have multiple parents that is the relationships among data elements can have many to many relationships.
Advantages
- It accepts many-to-many relationship, so it is more flexible.
- The searching is faster because of multidirectional pointers.
- The network model is simple and easy to design.
- It reduces redundancy because data shouldn't be repeated if same data is needed.
Disadvantages
- It is difficult to handle the relationship in complex programs.
- There is less security because of sharing data.
- It increases the processing overhead due to the complex relationship.
Relational Database Model
In relational database model, the data is organized into tables which contain multiple rows and columns. These tables are called relations. A row in a table represents a relationship among a set of values. Since a table is a collection of such relationships, it is generally referred to the mathematical term relation, from which the relational database model derives its name. It is also known as RDBMS.
Examples of RDBMS are Oracle,SQL, MS Access, MySQL etc.
We notice from below table (Student and Subject), here each student has a unique roll number and has marks of Math, English and Computer. Here, roll number makes relation between these two tables.
For example, if we make relation between Student and Subject, we can get following.
Assignment-5
1. Compare between Network Database model and Relational database model with diagram.
1.6 Concept of Normalization
Normalization is a database design technique that reduces data redundancy and eliminates undesirable characteristics like Insertion, Update and Deletion Anomalies.
Normalization rules divides larger tables into smaller tables and links them using relationships. The purpose of Normalization in SQL is to eliminate redundant (repetitive) data and ensure data is stored logically.
The inventor of the relational model Edgar Codd proposed the theory of normalization of data with the introduction of the First Normal Form(1NF), and he continued to extend theory with Second(2NF) and Third Normal Form(3NF). Later he joined Raymond F. Boyce to develop the theory of Boyce-Codd Normal Form.
Advantages / Benefits of Normalization:
The dependency between the data fields is identified.
The redundancy in database is minimized.
Data model is made more flexible and easier to maintain.
It improves faster storing and index creation.
It improves the performance of the database system.
It simplifies the structures of tables.
It avoids the loss of information.
Assignment-6
1. What is normalization? Write advanatges of normalization.
Database Normalization With Examples
Database Normalization Example can be easily understood with the help of a case study. Assume, a video library maintains a database of movies rented out. Without any normalization in database, all information is stored in one table as shown below.
Let's understand Normalization database with normalization example with solution:
Movies Rented column has multiple values.
Now let's move into 1st Normal Forms:
First Normal Form (1NF)
1NF (First Normal Form) Rules
Each table cell should contain a single value.
Each record needs to be unique.
The above table in 1NF:
Before we proceed let's understand a few things:
What is a KEY in SQL?
A KEY in SQLis a value used to identify records in a table uniquely.
An SQL KEY is a single column or combination of multiple columns used to uniquely identify rows or tuples in the table.
SQL Key is used to identify duplicate information, and it also helps establish a relationship between multiple tables in the database.
Note: Columns in a table that are NOT used to identify a record uniquely are called non-key columns.
What is a Primary Key?
- A primary is a single column value used to identify a database record uniquely.It has following attributes:
A primary key cannot be NULL
A primary key value must be unique.
The primary key values should rarely be changed
The primary key must be given a value when a new record is inserted.
What is Composite Key?
- A composite key is a primary key composed of multiple columns used to identify a record uniquelyIn our database, we have two people with the same name Robert Phil, but they live in different places.
2NF (Second Normal Form) Rules
Rule 1- Be in 1NF
Rule 2- Single Column Primary Key that does not functionally dependent on any subset of candidate key relation
It is clear that we can't move forward to make our simple database in 2nd Normalization form unless we partition the
table above.
We have divided our 1NF table into two tables viz. Table 3 and Table 4. Table 3 contains member information. Table 4 contains information on movies rented.
We have introduced a new column called Membership_ID which is the primary key for table 3. Records can be uniquely identified in Table 3 using membership id. In Table 4, Membership ID is the Foreign Key.
Foreign Key in DBMS
Foreign Key references the primary key of another Table. It helps to connect your Tables.
A foreign key can have a different name from its primary key. It ensures rows in one table have corresponding rows in another table.
Unlike the Primary key, they do not have to be unique. Most often they aren't.
Foreign keys can be null even though primary keys can not
Why do you need a foreign key?
Suppose, a novice inserts a record in Table 4 such as:
Insert a record in Table 4 where Membership ID=101
But Membership ID 101 is not present in table 3.
Database will throw an ERROR This helps in referential integrity.
Now, if somebody tries to insert a value in the Membership_ID field that does not exist in the parent table, an error will be shown.
You will only be able to insert values into your foreign key that exist in the unique key in the parent table. This helps in referential integrity.
The above problem can be overcome by declaring Membership_ID from Table 4 as foreign key of Membership_ID from Table 3.
What are transitive functional dependencies
A transitive is when changing a non-key column, might cause any of the other non-key columns to change
Consider the Table 3, Changing the non-key column Full Name may change Salutation.
Change in Name may change salutation.
Third Normal Form(3NF)
3NF (Third Normal Form) Rules
Rule 1- Be in 2NF
Rule 2- Has no transitive functional dependencies
To move our 2NF table into 3NF, we again need to divide our table.
Below is a 3NF example in SQL database:
We have again divided our tables and created a new table which stores Salutations.
There are no transitive functional dependencies, and hence our table is in 3NF.
In Table 7 Salutation ID is primary key, and in Table 5 Salutation ID is foreign key to primary key in Table 7.
No comments:
Post a Comment